5 ms·

> The program I used (DBeaver) saw the empty 3rd line, and ignored the 4th line. This is weird. SQL has ; as a command separator, why treat empty lines special

by 1st1 3y ago

> The program I used (DBeaver) saw the empty 3rd line, and ignored the 4th line. This is weird. SQL has ; as a command separator, why treat empty lines specially?

crazygringo 3y ago
Yes this is extremely strange and has nothing to do with SQL. This is a bizarre client implementation. I use blank lines to separate sections of long queries all the time for readability and have never had a problem with any tool ever.
- jayknight 3y ago
  dbeaver does have an preference for blank lines being statement separators, but that being the default is baffling. This breaks lot of sql scripts that we have in our codebases.
- hiccuphippo 3y ago
  JetBrains IDEs also allows blank lines as separators but shows an outline over the query it is going to execute, which helps avoid the problem.
AntonZ234 3y ago
I don’t know why the decided for a default like that. In Editor > Editor SQL > SQL Processing There is a setting called 'Blank line is a statement delimiter' which is on by default for some reason
fbdab103 3y ago
I have been burned by this DBeaver behavior before. Nothing so destructive, but yeah not great. For "more serious" commands, I try to force myself to highlight the command, but should really investigate if there is a way to enable more strict command selection.
teraflop 3y ago
Laziness/simplicity, I guess. You can't safely use semicolons as delimeters unless you parse/tokenize the entire statement (to exclude quoted semicolons). The exact handling of quoted string literals can vary depending on the SQL dialect, or even the configuration, and getting it wrong could cause even more unpredictable behavior. For instance, on MySQL I don't think it's possible to reliably identify statement boundaries without knowing the server-side value of the NO_BACKSLASH_ESCAPES setting.
SpicyLemonZest 3y ago
If I saw this behavior in a tool I use I would honestly report it as a serious correctness bug. All sorts of things can produce spurious newlines and I’m like 90% sure the SQL standard does not permit them to be interpreted as delimiters.
ljm 3y ago
I just checked in Postico (Mac) and got a syntax error when trying to execute a statement when the one a few lines above wasn't terminated. Making newlines significant by default is... well, yeah. OP should beat themselves up a little bit less. If you wrote `DELIMITER \n` in SQL (if it was allowed) it would be a total shitshow.
Bootvis 3y ago
MS SQL Server Management Studio does the same but maybe it does so a bit more intelligently. I never had problems with it (but I'm always super careful when writing mutating queries) In any case, executing the following text select * from foo select * from bar update foo set val=1 will trigger to select statements and do an update.
xboxnolifes 3y ago
Dbeaver has two ways to run queries through it's interface: single snippet, or entire query. Snippets are separated by two newlines. They likely used the single snippets command instead of entire query command.
ktm5j 3y ago
I've actually had this happen in dbeaver. It has a feature (like some other db clients) where you can have a scratch SQL console.. you hit ctrl-enter to execute the statement the cursor is on, but it treats a newline as the end of a statement instead of continuing until it reaches a semicolon.

by 1st1 3y ago

> The program I used (DBeaver) saw the empty 3rd line, and ignored the 4th line. This is weird. SQL has ; as a command separator, why treat empty lines specially?

crazygringo 3y ago

Yes this is extremely strange and has nothing to do with SQL. This is a bizarre client implementation. I use blank lines to separate sections of long queries all the time for readability and have never had a problem with any tool ever.

jayknight 3y ago
dbeaver does have an preference for blank lines being statement separators, but that being the default is baffling. This breaks lot of sql scripts that we have in our codebases.
hiccuphippo 3y ago
JetBrains IDEs also allows blank lines as separators but shows an outline over the query it is going to execute, which helps avoid the problem.

AntonZ234 3y ago

I don’t know why the decided for a default like that. In Editor > Editor SQL > SQL Processing There is a setting called 'Blank line is a statement delimiter' which is on by default for some reason

fbdab103 3y ago

I have been burned by this DBeaver behavior before. Nothing so destructive, but yeah not great. For "more serious" commands, I try to force myself to highlight the command, but should really investigate if there is a way to enable more strict command selection.

teraflop 3y ago

Laziness/simplicity, I guess. You can't safely use semicolons as delimeters unless you parse/tokenize the entire statement (to exclude quoted semicolons). The exact handling of quoted string literals can vary depending on the SQL dialect, or even the configuration, and getting it wrong could cause even more unpredictable behavior. For instance, on MySQL I don't think it's possible to reliably identify statement boundaries without knowing the server-side value of the NO_BACKSLASH_ESCAPES setting.

SpicyLemonZest 3y ago

If I saw this behavior in a tool I use I would honestly report it as a serious correctness bug. All sorts of things can produce spurious newlines and I’m like 90% sure the SQL standard does not permit them to be interpreted as delimiters.

ljm 3y ago

I just checked in Postico (Mac) and got a syntax error when trying to execute a statement when the one a few lines above wasn't terminated. Making newlines significant by default is... well, yeah. OP should beat themselves up a little bit less. If you wrote `DELIMITER \n` in SQL (if it was allowed) it would be a total shitshow.

Bootvis 3y ago

MS SQL Server Management Studio does the same but maybe it does so a bit more intelligently. I never had problems with it (but I'm always super careful when writing mutating queries) In any case, executing the following text select * from foo select * from bar update foo set val=1 will trigger to select statements and do an update.

xboxnolifes 3y ago

Dbeaver has two ways to run queries through it's interface: single snippet, or entire query. Snippets are separated by two newlines. They likely used the single snippets command instead of entire query command.

ktm5j 3y ago

I've actually had this happen in dbeaver. It has a feature (like some other db clients) where you can have a scratch SQL console.. you hit ctrl-enter to execute the statement the cursor is on, but it treats a newline as the end of a statement instead of continuing until it reaches a semicolon.