Richard has checked in fixes for this on trunk which will likely appear in the
next release of SQLite. Note this is a change to the underlying SQLite engine
(SQLite3.dll) and not the command line shell. Column names from "values" are
predictable and the same even in subqueries. Note that shell simply outputs
what is returned by executing the SQL.
Note that presently if you use a double-quote for the first value row, and that
quoted-identified is not an identified but treated as a quoted-string, it still
becomes the column name in the subquery.
SQLite version 3.20.0 2017-07-09 18:55:29
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode col
sqlite> .header on
sqlite> values (1,2), (2,3);
column1 column2
---------- ----------
1 2
2 3
sqlite> select * from (values (1,2), (2,3));
column1 column2
---------- ----------
1 2
2 3
sqlite> create table x as values (1,2), (3,4);
sqlite> select * from x;
column1 column2
---------- ----------
1 2
3 4
sqlite> create table y as select * from (values (1,2), (2,3));
sqlite> select * from y;
column1 column2
---------- ----------
1 2
2 3
sqlite> create view v as values (1,2), (2,3);
sqlite> select * from v;
column1 column2
---------- ----------
1 2
2 3
sqlite> create view w as select * from (values (1,2), (2,3));
sqlite> select * from w;
column1 column2
---------- ----------
1 2
2 3
sqlite> with x as (values (1,2), (2,3)) select * from x;
column1 column2
---------- ----------
1 2
2 3
sqlite> with x as (select * from (values (1,2), (2,3))) select * from x;
column1 column2
---------- ----------
1 2
2 3
sqlite> with
...> x as (values (1,2), (2,3)),
...> y as (values (4,5), (5,6))
...> select x.column1*y.column2, x.column2*y.column1 from x,y;
x.column1*y.column2 x.column2*y.column1
------------------- -------------------
5 8
6 10
10 12
12 15
sqlite> select x.column1*y.column2, x.column2*y.column1
...> from (values (1,2), (2,3)) as x,
...> (values (4,5), (5,6)) as y;
x.column1*y.column2 x.column2*y.column1
------------------- -------------------
5 8
6 10
10 12
12 15
sqlite>
And since the values now return consistent names, you can "rename" them in the
select more easily if you so desire.
sqlite> select column1 as idx, column2 as value from (values (1,2), (2,3));
idx value
---------- ----------
1 2
2 3
sqlite>
However:
sqlite> values ("1", 2), ('3', 4);
column1 column2
---------- ----------
1 2
3 4
sqlite> values ('1', 2), ("3", 4);
column1 column2
---------- ----------
1 2
3 4
sqlite>
sqlite> select * from (values ("1", 2), ('3', 4));
1 column2
---------- ----------
1 2
3 4
sqlite> select * from (values ('1', 2), ("3", 4));
column1 column2
---------- ----------
1 2
3 4
sqlite>
> -----Original Message-----
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of petern
> Sent: Saturday, 8 July, 2017 20:51
> To: SQLite mailing list
> Subject: Re: [sqlite] VALUES clause quirk or bug?
>
> A lot of replies. To be clear, this is not a quirk of the shell and I'm
> not just monkeying around looking for haphazard guesses about why I'm
> "trying to change the SQL standard". The following query sent through
> sqlite3_exec() produces the following output:
>
> SELECT max([],[:1]) FROM (VALUES (1,2),(3,4));
> max([],[:1])
> 2
> 4
>
> This is what SQLite 3.19.3 VALUES clause presently does independently of
> shell.c. My question would be, is this feature going to produce stable
> column names going forward caveat the strange behavior of double quotes.
>
> Simon, your rhetorical question asking how having an inconsistent number
> of
> columns in the VALUES list makes sense is non-sequitur. I am not
> proposing
> some illogical bizarre syntax. I hope you see how your remark is out of
> line and amounts to noise on this thread.
>
> The bug here is how the VALUES logic can't have it both ways. If double
> quotes are invalid for column value literals they should be rejected or at
> least ignored. They should not suddenly be injected into the column
> name(s) observed by the outer scope of the VALUES clause. That is of
> course unless there is a software easter egg here that, depending on as
> yet
> undisclosed well crafted literal values, allows the column names to be
> elegantly specified in addition to the row values.
>
> Also sent through sqlite3_exec():
>
> SELECT * FROM (VALUES ("1",2),(3,4));
> 1,
> 1,2
> 3,4
>
>
>
>
> On Sat, Jul 8, 2017 at 6:01 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> >
> >
> > On 9 Jul 2017, at 1:44am, Keith Medcalf <kmedc...@dessus.com> wrote:
> >
> > > I can't visualize what you mean. Something like:
> > >
> > > create view constants (col1, col2) as values (1,1), (1,2);
> > >
> > > seems the most straightforward to me, and allows you to assign column
> > names to the data.
> >
> > But that’s not SQL syntax, is it ? (Read that in a puzzled voice. I
> > genuinely don’t know.) You’re just using the word VALUE as if it does
> what
> > you want.
> >
> > I was thinking of a CREATE TABLE command, and a SELECT command with "AS"
> > clauses. Or of something similar with a VIEW.
> >
> > 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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
