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]

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

(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 whitespace<sup>a</sup> 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 constructs<sup>b</sup> **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:)<br>
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 whitespace<sup>a</sup> 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]

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]

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