sqlite3_normalized_sql(stmt) inconsistency?
(1) By curmudgeon on 2020-10-06 10:00:38 [link] [source]
Documentation is a bit vague about sqlite3_normalized_sql fct so I'm not sure if this is a bug. select 1 as x; select 1 as [x]; select 1 as "x"; select 1 as `x`; Normalized sql for above 4 is SELECT?AS x; but for select 1 as 'x'; it's SELECT?AS?;
(2) By Richard Hipp (drh) on 2020-10-06 11:04:42 in reply to 1 [link] [source]
This is a string literal containing a single character U+0078:
'x'
These forms are all different spellings for an identifier that consists of a single character U+0078:
x
[x]
"x"
- `x`
The sqlite3_normalize_sql() interface should convert literals into "?" and convert identifiers into their simplest form. It appears to be doing the right thing in your example.
(3.1) By curmudgeon on 2020-10-06 12:04:43 edited from 3.0 in reply to 2 [source]
It was the "converting identifiers to their simplest form" that I found useful Richard but this does make it less so. If I wanted to interpret user sql and they input select "tbl" . [col] from tbl; it will be normalized to SELECT tbl.col FROM tbl; -- useful but select "tbl" . 'col' from tbl; will be normalized to SELECT tbl.?FROM tbl; -- less useful although both the original sql's will return the exact same result. I'm guessing that I'm using sqlite3_normalized_sql for something it's not intended for. Wish I knew what it was intended for though.
(4) By Richard Hipp (drh) on 2020-10-06 12:19:38 in reply to 3.1 [link] [source]
The statement:
SELECT "tbl".'col' FROM tbl;
Is not standard SQL. SQLite accepts it in a misguided effort to follow
Postel's Rule. But that does not make it right. The conversion
of the string literal 'col'
into an identifier "col"
occurs during
parsing and requires a level of understanding of the SQL language far in
excess of what is available to sqlite3_normalize_sql().
I'm not sure the automatic conversion of string-literals into identifiers is even documented. Is it?
(5) By J. King (jking) on 2020-10-06 12:31:02 in reply to 4 [link] [source]
https://sqlite.org/lang_keywords.html
If a keyword in single quotes (ex: 'key' or 'glob') is used in a context where an identifier is allowed but where a string literal is not allowed, then the token is understood to be an identifier instead of a string literal.
(6) By curmudgeon on 2020-10-06 13:07:19 in reply to 4 [link] [source]
OK Richard, thanks for the input.
(7) By curmudgeon on 2020-10-06 14:06:40 in reply to 4 [link] [source]
Does SQLITE_ENABLE_NORMALIZE have to be defined for sqlite3_normalized_sql to work? I just undefined it and it works as before yet I see #ifdef SQLITE_ENABLE_NORMALIZE throughout the sqlite3.c code.