I was hoping someone could shed light on what is actually going on in the
VALUE clause.
Is there some sort of easter egg there? Is there a way for VALUE to take
the first row exclusively as column names? Perhaps there is a trick with
other special characters?
Trying the obvious only produces the unusable. The first row becomes both
column names and first row:
sqlite> SELECT * FROM (VALUES ("c1","c2"),("1",2));
c1,c2
c1,c2
1,2
Not working at all are these variants of directly supplying column names:
sqlite> SELECT * FROM (VALUES ((1)c1,(2)c2),(3,4));
Error: near "c1": syntax error
sqlite> SELECT * FROM (VALUES ((1)"c1",(2)"c2"),(3,4));
Error: near ""c1"": syntax error
sqlite> SELECT * FROM (VALUES ((1) AS "c1",(2) AS "c2"),(3,4));
Error: near "AS": syntax error
Now regarding the AS clause. Were the merits of the supporting an ordinary
full anonymous table VALUE syntax ever considered? For example:
sqlite> VALUES (1,2),(3,4) AS (c1,c2);
Error: near "AS": syntax error
sqlite> SELECT * FROM (VALUES (1,2),(3,4) AS (c1,c2));
Error: near "AS": syntax error
The desired output in both cases would of course be:
c1,c2
1,2
3,4
On Sat, Jul 8, 2017 at 2:20 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
>
> On 8 Jul 2017, at 8:36pm, petern <peter.nichvolo...@gmail.com> wrote:
>
> > Why does the choice of data value quotation mark influence the output
> > column name of the inline VALUES clause?
>
> I admire your set of examples, which show the behaviour well.
>
> Column names in SQLite are not dependable unless you have set them using
> an "AS" clause. Not only can they vary with the type of value, as you have
> demonstrated, but they have changed with different versions of SQLite and
> may change again. There are also PRAGMAs which change column names, though
> they are deprecated.
>
> If your code needs to depend on column names for anything other than a
> simple SELECT of TABLE columns, set them explicitly using "AS" for each
> column.
>
> > sqlite> SELECT * FROM (VALUES ("1",2),(3,4));
>
> This is not a recommended syntax for use with SQLite. Strings should be
> enclosed in single quotes (apostrophes) as you did in another example.
> Double quotes are expected to refer to entity names, though they are rarely
> needed even for that. Although SQLite does not complain when you use
> double quotes for values in the above way, its interpretation can be
> inconsistent and unhelpful.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
