SQLite Forum

[bug?] Unexpected CLI shell comment handling

[bug?] Unexpected CLI shell comment handling

(1) By Kees Nuyt (knu) on 2021-10-02 12:29:54 [link] [source]

In recent versions of SQLite, I noticed single line comments in the sqlite3 command line tool behave weird.


knu@f7p3:~ $ sqlite3 test.db # a database with one table, t1
SQLite version 3.37.0 2021-10-01 21:01:07
Enter ".help" for usage hints.
sqlite> .tables
sqlite> -- .tables
   ...> ;
sqlite> SELECT name FROM sqlite_schema;
sqlite> -- SELECT name FROM sqlite_schema;
   ...> ;

Observed behavior: Line starting with -- requires continuation, and a ';' to complete.

Expected behavior: No continuation prompt.

Same behavior when processing redirected scripts with single line comments, as in:

sqlite3 test.db <script.sql

I didn't bisect it, but it must be a recent change, because I recompile from trunk almost weekly and just over a week ago one of my scripts suddenly failed by this peculiarity.

Kind regards, Kees Nuyt

(2.1) By Larry Brasfield (larrybr) on 2021-10-02 14:44:06 edited from 2.0 in reply to 1 [source]

(Edited to describe a change under consideration which addresses this behavior.)

The CLI's line collection process applies just a few rules, which can be summarized as:

  1. If the line begins with '.' and is the first since the last SQL or meta-command was processed, then it is a single-line meta-command and processed as such.

  2. If the line ends with ';' followed by nothing but whitespacea and is not within an SQL construct within which that character is considered part of the construct rather than a statement terminator, then the line together with its predecessors (that were not meta-commands) is submitted for SQL execution. (prepare, step with output, finalize)

In your example, the "--" which begins the line, per all SQL standards, makes that and the remainder of the line, whatever it may be, a comment.

It would have to be a special cased handler that would treat ';' as a SQL terminator only if it appears at the end and outside of all lexically delimited constructsb except end-of-line comments.

I do not see how the present behavior is weird. Should an end-of-line semicolon within a block comment also terminate the SQL to avoid this weirdness?

(Added via edit:)
This additional summarized rule would cure your script problem and should be harmless in all circumstances I am imagining:

3. As non-meta-command lines are accumulated, at any time that they constitute a complete SQL construct which is nothing but whitespacea the accumulation is discarded, the primary prompt is issued (if in interactive mode), and line processing reverts to the state where either meta-commands or SQL (finally ending with ';') are accepted.

Comments on any difficulties arising from this approach are more than welcome.

a. In this context, "whitespace" includes the usual not-dark characters and SQL comments.

b. The "lexically delimited constructs" are quoted strings and identifiers, and end-of-line or block comments.

(3) By Larry Brasfield (larrybr) on 2021-10-02 15:44:24 in reply to 1 [link] [source]

Kees: Please disregard my earlier comments about a behavior-changing revision. The behavior to which you were accustomed and the intended current behavior were identical. (This can be seen in the code, with some study.) The issue is that I allowed a simple bug to creep in during a recent revision made to avoid an O(N^2) performance degradation with huge block comments.

The current trunk version should behave more satisfactorily with that script of yours which recently failed.

(4) By Kees Nuyt (knu) on 2021-10-02 16:49:49 in reply to 3 [link] [source]

Hi Larry,

Thanks for your quick reply and thorough analysis.

I can confirm that the current trunk behaves as expected.

Next time I report a possible bug, I will bisect it beforehand, so you can pinpoint it more easily.

Thank you for your efforts!

Kind regards,
Kees Nuyt