Most interestingly, a case of duplicate column names check that seems to
have slipped through the net allowing a table to have three columns
named the same, but only if they're empty and the user has used a
different delimiter set each time (which by the way is discouraged and
we should stick to ""):
CREATE TABLE x ("" int, '' int, [] int);
Also interesting is that if you now create another table based on this
using the CREATE-SELECT syntax, it will have column names "", ":2", ";3"
to substitute the three empty ones, just as VALUES() seemed to do
before, although in this case there is no VALUES:
CREATE TABLE y AS SELECT * FROM x;
----- Original message -----
From: Jean Chevalier <jcheval...@gmx.net>
To: sqlite-users at mailinglists.sqlite.org
Subject: Automatic column aliasing, SELECT vs VALUES
Date: Wed, 17 Jun 2015 22:07:47 +0200
I read that VALUES(expr-list) means the same as SELECT(expr-list), but
apparently not with regards to the metadata that's sent out along with
the values.
If I issue these two commands, in one case I end up with column names
"", ":1", ":2" (sequential), and in the other I end up with "1", "2",
"1000" (representational):
CREATE TABLE a AS VALUES (1,2,1000);
CREATE TABLE b AS SELECT 1,2,1000;
I'm not advocating that someone uses this table creation mechanism and
then relies on the column names, as I reckon that that's probably
unsupported, but shouldn't the two above match?
Shouldn't they also match to do whatever the SELECT already does, since
the VALUES option is probably newer and fewer people would be relying on
its behaviour already, also because an empty column name for the first
column is kind of an irregularity, even though SQLite allows commands
such as CREATE TABLE "" (""); without complaining.
