Column name includes quotes used in RETURNING expr

(1) By Jeremy Evans (jeremyevans) on 2021-05-18 18:33:39 [source]

In normal SQLite SELECT use, quotes for identifiers are not included in column names (sqlite3_column_name). However, when used in RETURNING, quotes are included in when used in the expr in RETURNING, but not when used as the column-alias:

$ sqlite3
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .headers on
sqlite> CREATE TABLE a (t INTEGER);
sqlite> INSERT INTO a VALUES (1) RETURNING `t`;
`t`
1
sqlite> SELECT `t` FROM a;
t
1
sqlite> INSERT INTO a VALUES (1) RETURNING "t";
"t"
1
sqlite> INSERT INTO a VALUES (1) RETURNING "t" AS `t`;
t
1

(2) By Gunter Hick (gunter_hick) on 2021-05-21 08:03:48 in reply to 1 [link] [source]

It seems that SQLite is trying hard to resolve the expressions to column names in the RETURNING clause.

't' is actually a string literal, whereas "t" is a quoted identifier that sometimes gets interpreted as a string literal, and I suspect you want only t without any quotes.

Please note that column labels not set by an AS clause are generated by SQLite and are subject to change without notice.

(3) By Holger J (holgerj) on 2021-05-21 12:55:19 in reply to 1 [link] [source]

Actually 't' is a string literal, "t" is an object name (here: a column name), while `t` is not defined in SQL.

SQL doesn't use (square) brackets [], (curly) braces {}, backticks ``, because is quite reduced using special characters.

(5) By Harald Hanche-Olsen (hanche) on 2021-05-28 07:52:04 in reply to 3 [link] [source]

SQLite does allow some syntax for identifiers that is not standard SQL, such as backticks or square brackets for quoting identifiers.

(4) By Jeremy Evans (jeremyevans) on 2021-05-27 23:18:31 in reply to 1 [link] [source]

I'm fairly sure this is a bug in SQLite. Here's a patch that can fix it, though it may break something else. I don't have any familiarity with the SQLite source code. At least the patch probably needs tests to be accepted. However, hopefully this indicates how the problem could be fixed.

This patch is against the sqlite3.c file in sqlite-autoconf-3350500.tar.gz.

Index: sqlite3.c
--- sqlite3.c.orig
+++ sqlite3.c
@@ -139401,6 +139401,9 @@ static ExprList *sqlite3ExpandReturning(
       if( !db->mallocFailed && ALWAYS(pList->a[i].zEName!=0) ){
         struct ExprList_item *pItem = &pNew->a[pNew->nExpr-1];
         pItem->zEName = sqlite3DbStrDup(db, pList->a[i].zEName);
+        if ( pList->a[i].eEName!=ENAME_NAME && pOldExpr->op==TK_ID ){
+          sqlite3Dequote(pItem->zEName);
+        }
         pItem->eEName = pList->a[i].eEName;
       }
     }

Example of usage after patch:

.headers on
CREATE TABLE a (s INTEGER, t INTEGER);
INSERT INTO a VALUES (1, 2) RETURNING `t`;
t
2
SELECT `t` FROM a;
t
2
INSERT INTO a VALUES (1, 3) RETURNING "t";
t
3
INSERT INTO a VALUES (1, 4) RETURNING "t" AS `t`;
t
4