SQLite Forum

use of "notnull" and "unique" in column names

use of "notnull" and "unique" in column names

(1) By Rick Gumpertz (rgumpertz) on 2022-01-14 04:01:54 [link] [source]

Some genius decided to name one of the columns returned by pragma_table_info as "notnull" which is a reserved key word in SQLite3.  Similarly, pragma_index_list returns a column named "unique" which is also a reserved key word.

When I explicitly listed those two column names (unquoted) in SELECT requests from C code, I got "out of memory" failures, which implies a serious problem with the SQL parser.  It would be nice if somebody could fix the parser to at least diagnose the situation rather than run out of memory! (That's the "hard" part of my request.)

In the meantime, all documentation of those two table functions (as well as the related pragma_table_xinfo) should be modified to have warnings to the naive reader that those two column names MUST be quoted to be successfully used as column names!  I doubt there are many places on the SQLite website that mention those table functions, so it shouldn't be very hard to find them.  (That's the "easy" part of my request.)

It wouldn't hurt to check whether any other SQLite built-in column names similarly conflict with reserved keywords.  I just happened upon these two.

(2) By Larry Brasfield (larrybr) on 2022-01-14 14:33:32 in reply to 1 [link] [source]

Given that pragma_table_info(...) and pragma_index_list(...) are generally for more advanced users, and that the column names are descriptive, and even good first choices among alternatives for those unfamiliar with the docs, I would have to say that no loss of genius status should hinge upon those choices.

As for your bug report: I was unable to repro OOM failures for unquoted use of those column names in the SQLite shell. (Only syntax error messages ensued.) And I could not guess what might be different in your "C code" from the shell's C code that was run during my repro attempts. Hence, I find it more than hard to proceed upon a fix, assuming such is possible. What version of the library were you using?

Regarding documentation of how to build queries for those eponymous virtual table pragmas: The keywords are well documented already, as are the identifier quoting rules. I am disinclined to expand the docs to provide helpful little hints wherever more than one SQL language rule might apply. Such hints might help individually for those who come across them. But they would collectively detract from the utility of the docs by increasing the effort needed to read them and inducing "skip mode", where repetitious matter must be recognized and skimmed over.

Can you please post an excerpt of your code suffering an OOM failure, or a short function which evinces that failure? I'll be happy to reproduce the failure, or attempt it again, given a more specific inducing sequence.

(3) By Ryan Smith (cuz) on 2022-01-14 14:34:47 in reply to 1 [source]

Do you mind clarifying exactly which version of SQLite is doing this?

I cannot reproduce the problem on my side - here is the same query you mentioned, first with the good quoted column name and second with the unquoted column name.

The second one fails as it should, but it does so immediately and with a sane error and error report - nothing about being "out of memory":

  -- SQLite version 3.35.4  [ Release: 2021-04-02 ]  on  SQLitespeed version
  -- ================================================================================================

SELECT cid, name, type, "notnull", dflt_value, pk
  FROM pragma_table_info('language_list');

  -- cid|name           |type     |notnull|dflt_value       | pk
  -- ---|---------------|---------|-------|-----------------|---
  --  0 |id             |INTEGER  |   0   |NULL             | 1 
  --  1 |iso639_1_code  |TEXT     |   1   |NULL             | 0 
  --  2 |language_key   |TEXT     |   1   |NULL             | 0 
  --  3 |change_cookie  |INT      |   1   |0                | 0 
  --  4 |language       |TEXT     |   1   |NULL             | 0 
  --  5 |native_name    |TEXT     |   1   |NULL             | 0 
  --  6 |added          |NUMERIC  |   1   |datetime('now')  | 0 

SELECT cid, name, type, notnull, dflt_value, pk
  FROM pragma_table_info('language_list');

  -- ------------------------------------------------------------------------------------------------

  -- 2022-01-14 16:26:21.790  |  [Info]       Script failed - Rolling back...
  -- 2022-01-14 16:26:21.790  |  [Success]    Transaction Rolled back.
  -- 2022-01-14 16:26:21.790  |  [ERROR]      Failed to complete: 
  -- -------  DB-Engine Logs (Contains logged information from all DB connections during run)  ------
  -- [2022-01-14 16:26:21.744] APPLICATION : Script D:\Documents\SQLiteAutoScript.sql started at 16:26:21.744 on 14 January.
  -- [2022-01-14 16:26:21.789] ERROR (1)   : near "notnull": syntax error in "SELECT cid, name, type, notnull, dflt_value, pk
  FROM pragma_table_info('language_list');"
  -- [2022-01-14 16:26:21.789] QUERY       : Failed Query: SELECT cid, name, type, notnull, dflt_value, pk    FROM pragma_table_info('language_list');  
  -- [2022-01-14 16:26:21.789] INTERFACE   : DB Error: near "notnull": syntax error
  -- ================================================================================================

(4) By Rick Gumpertz (rgumpertz) on 2022-01-18 22:56:00 in reply to 3 [link] [source]

After fixing my code to NOT use sqlite3_db_handle(...) as the parameter to sqlite3_errmsg(...), I got a much more reasonable error message: "near "notnull": syntax error".

Silly me, it didn't occur to me when I wrote the original code that sqlite3_db_handle(...) wouldn't work after a failure in sqlite3_prepare_v3. Sigh.

I still think the documentation should be updated to warn people about keywords being used as column names.

(5) By Keith Medcalf (kmedcalf) on 2022-01-19 01:03:25 in reply to 4 [link] [source]

sqlite3_db_handle() takes a pointer to a prepared (successfully compiled) statement as an argument and returns a pointer to the owning connection.

The sqlite3_prepare*() returned something other than SQLITE_OK, indicating a failure to prepare (compile) the statement source. As such there is no "prepared statement" from which the owning connection can be retrieved.

This is conceptually simillar to attempting to read byte 407f of the executable produced by a failed compliation. The compilation failure resulted in no executable output, so attempting to find the value of byte 407f of the executable will fail (there is no executable produced).