SQLite Forum

sqlite3_normalized_sql(stmt) inconsistency?
Login

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 [link] [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 [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.