legitimate multiline query statement flagged as error

[UbuntuGeezer]

Details for the issue

What did you do? opened database through menu system "Open database";

then entered the "Execute SQL" tab; then entered the following query statement: select * from NVenAll where "situs address (Property address)" like "333 the esplanade%";

What did you expect to see?

Result: 37 rows returned in 76ms At line 1: select * from NVenAll where "situs address (Property address)" like "333 the esplanade%";

What did you see instead?

Result: near "where": syntax error At line 2: where

** When I removed the line breaks and made the query all on one line, I got the correct results. select from NVenAll where "situs address (Property address)" like "333 the esplanade%";

** When I added an "order by" clause ( order by "situs address (property address)" ) on a second line, the query also worked, reordering the results properly. select from NVenAll where "situs address (Property address)" like "333 the esplanade%" order by "situs address (property address)";

The multiline queries work fine when piped into the sqlite3 batch command with < query.sql. The multiline queries work fine when input from stdin after starting sqlite3 and using multiline prompted input.

Useful extra information

DB4S v3.11.2 [built for x86_64-little_endian-lp64] on Ubuntu 20.04.2 LTS (linux/5.4.0-73-generic) [x86_64] using SQLite Version 3.31.1 and Qt 5.12.5

Information from the SQLite Browser Help/About dialog: Version 3.11.2

Built for x86_64-little_endian-lp64, running on x86_64

Qt Version 5.12.5

SQLite Version 3.31.1

[chrisjlocke]

Can you try the latest version? v3.11.2 is two or three revisions out. I've tried the nightly, and that works OK.

As an aside, the statement is slightly incorrect. The last part should be apostrophes, not quotation marks.

select *
from NVenAll
where "situs address (Property address)"
like '333 the esplanade%';

In SQLite, you use single quotes (or apostrophes) to enclose strings. Double-quotes (or quotation marks) are used only to enclose field names*.

*However, SQLite does tend to know what you mean, so (as in this case) has just replaced them so it works, but it's best to use the right ones.

[mgrojo]

I've reproduced the problem using the "Execute line" button, being this statement the first in the buffer and placing the cursor on the "where" line. It doesn't happen in the second statement, provided that they are separated by semicolons.

The easiest workaround is to place the cursor on the first line. This happens because we fail to correctly determine the statement boundaries.

This sounds familiar, in fact, I thought it was already fixed. There must be an issue with some clarifications somewhere.

[mgrojo]

Ok, the behaviour was clearly explained here by Martin: https://github.com/sqlitebrowser/sqlitebrowser/issues/1632#issuecomment-442441526

Not sure what should be done to make the behaviour less confusing.

[UbuntuGeezer]

Thanks so much for the explanation/clarification. The workaround is more than adequate. Related to this problem may be the anomaly that if a comment line precedes the focus line for "Execute line", and the comment is not terminated with a semicolon, the focus line is flagged as an error.

For my own future reference if/when I report another bug I will try to remember including the detail of which control I clicked (e.g. "Execute line") that produced the problem. UbuntuGeezer

[chrisjlocke]

A workaround I've got used to, is to highlight the statement I want to run and press f5 or the execute button - it'll only run the highlighted statements.