On 2017/07/08 9:36 PM, petern wrote:
Why does the choice of data value quotation mark influence the output
column name of the inline VALUES clause? [This quirk was the origin of a
recent bug in a current project.]
As to the "Why" question: It is because Double-Quotes denote
Identifiers, not strings/values. This is well documented and stems from
the SQL standard, not SQLite per se.
sqlite> .version
SQLite 3.19.3 2017-06-08 14:26:16
0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b
sqlite> .header on
sqlite> .mode csv
sqlite> SELECT * FROM (VALUES (1,2),(3,4));
"",:1
1,2
3,4
Here the standard SQLite thing happens: The first Column has a header of
"Empty String" as an IDENTIFIER, and every next column has a :1, :2,
:3... :n placeholder since the Empty column name can only appear once,
and every next item identifier avoids duplication by increasing the
placeholder header value.
sqlite> SELECT * FROM (VALUES ("1",2),(3,4));
1,""
1,2
3,4
Here the first column is regarded as an IDENTIFIER since the QP assumes
you are referring to an identifier thanks to the double-quotes (but
which is clear upon execution that there is no such identifier so SQLite
helps by reverting to the string value, which is the root of the
confusion), so it puts a 1, the second column is now no longer a
duplication so it is free to use the Empty string, and the third+
columns would again have to avoid duplication by using the :1, :2, etc.
sqlite> SELECT * FROM (VALUES ('1',2),(3,4));
"",:1
1,2
3,4
Here the first column is back to a normal value (non-identifier exactly
like in the 1st example) and so it can show the empty string and the
rest playing ball...
I am aware that a less quirky "column<i>" output column naming is available
if the VALUES clause is evaluated directly. See below. However, this form
is not applicable for task at hand, specifying inline constant tables
within a query.
Doesn't matter - Just use single quotes for values (in stead of double
quotes which are reserved for identifier names as per the SQL standard)
and you can count on the returned headers being consistent - at least
for any single release of SQLite. If you need column names to be
specific forever across multiple releases with some modicum of surety,
then (as Simon noted) you need to specify the names exactly in either a
CTE or a named sub-query.
Suggested Examples:
SELECT 1 AS AX, 2 AS BX, 3 AS CX UNION ALL
SELECT * FROM (VALUES (2,3,4),(5,6,7),(10,11,12));
-- AX | BX | CX
-- ------------ | ------------ | ------------
-- 1 | 2 | 3
-- 2 | 3 | 4
-- 5 | 6 | 7
-- 10 | 11 | 12
WITH C(AX,BX,CX) AS (VALUES (1,2,3),(2,3,4),(5,6,7),(10,11,12))
SELECT * FROM C;
-- AX | BX | CX
-- ------------ | ------------ | ------------
-- 1 | 2 | 3
-- 2 | 3 | 4
-- 5 | 6 | 7
-- 10 | 11 | 12
Good luck!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
