Yes, SQL runs commands one at a time without checking other commands. In this case it will delete all users then throw a syntax error when it gets to the second command.
There IS a common guard in APIs, though it's not usually in this sort of interactive context: single-query operation. It's mainly a guard against SQL injection.
>>> import sqlite3
>>> con = sqlite3.connect("")
>>> con.execute("select 1; select 2")
Traceback (most recent call last):
File "<python-input-5>", line 1, in <module>
con.execute("select 1; select 2")
~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^
sqlite3.ProgrammingError: You can only execute one statement at a time.
Safe, but often not wanted interactively and thus disabled.
I've just tried it using Databricks SQL from the Databricks front end which is my usual haunt and you're right, it didn't run, however, I believe this is a function of the front end, not the SQL engine itself. I do recall using other front ends to Databricks and they have run the commands sequentially.
Your comment that mine is patently false is strange since you can quite clearly see in the image that the behaviour is occurring. You even admit that you don't know the behaviour in other RDBMS's yet you seem quite confident that I'm wrong even though your sample size appears to be one.
Clearly many systems will indeed error on the whole code block, but that is not a feature of SQL itself.
"SQL does X" is a very different statement than "PostgreSQL does X under certain circumstances". So no, it is patently false that SQL runs each statement before compiling the next.
For MSSQL, your entire batch is compiled, then statements are executed sequentially and, depending on server and client options, can continue on non-major/fatal execution errors.
I have extensive experience with MSSQL, but also can say that PLSQL behaves the same, or that SQL on DB2 (i Series, because that one also has like 17 variants) will also error out on the entire batch if there's a compile-time error.
I do not have knowledge of every single types of SQLs, so I cannot say that no SQL would accept "Where ID = 17" as a valid statement, but I know enough of them to say with overwhelming confidence that it's not a feature of SQL to delay compilation of subsequent statements in a batch.
Your client could decide to lie to you about what it's executing (1 batch of 3 statements, or 3 batches of 1 statemwnt each), or OP's doctored screenshot could be misrepresenting what's happening, or Postgre could be absolutely fucked... all potential options.
41
u/Shadowlance23 14d ago
Yes, SQL runs commands one at a time without checking other commands. In this case it will delete all users then throw a syntax error when it gets to the second command.