> select [a] from (select * from x);
> You'll get the following "buggy" output:
> [a]
> 1
It's not "buggy". Name of the column in result set is not defined
unless you use "as".
> CREATE TABLE y("[a]" INT);
> I came across this issue as statements like the following failed with the
> below mentioned error due to incorrect column names in the created tables:
> create index [y.a] on y ([a]);
> Output in version 3.7.5:
> Error: table y has no column named a
Because symbols [] have special meaning - quoting identifiers. So your
statement basically looked the same as the following:
create index "y.a" on y("a");
And indeed table y doesn't have such column. The following statement
should work:
create index "y.a" on y("[a]");
But best of all use "as" clause in your queries and never use "create
... as select ..." in any application (it can be useful only in some
quick-and-dirty debugging). And also I would suggest not using
confusing names for any table, index or column (e.g. as your "y.a").
Pavel
On Thu, Feb 10, 2011 at 6:25 AM, Nißl Reinhard <reinhard.ni...@fee.de> wrote:
> Hi,
>
> identifier quotation characters ("[]) get part of column names for certain
> statements, i. e. it depends on the complexity of the statement to trigger
> this bug.
>
> To reproduce the bug, type the following in sqlite3:
>
> .headers ON
> create table x(a int);
> insert into x values (1);
> select [a] from (select * from x);
>
> You'll get the following "buggy" output:
>
> [a]
> 1
>
> The correct output is returned for this statement:
>
> select [a] from x;
>
> You'll get:
>
> a
> 1
>
> I've run into this bug after updating from 3.3.6 to 3.7.5. In 3.3.6, even the
> following statement returned an incorrect column name:
>
> select [a] from x group by a;
>
> The 3.3.6 result was:
>
> [a]
> 1
>
> The 3.7.5 correct result is:
>
> a
> 1
>
> While I knew this bug for some years already it didn't matter much in my
> software. In 3.7.5 it hurts me due to the corrected behavior for "create
> table ... as select ..." statements. In 3.3.6 the column names were
> implicitly dequoted (which in my current opinion was incorrect) so the below
> statement created the table as shown:
>
> create table y as select [a] from (select * from x);
> .schema y
>
> Output in version 3.3.6:
>
> CREATE TABLE y(a int);
>
> In 3.7.5 with corrected behavior, the output looks like that (and is correct
> according to the buggy select statement):
>
> CREATE TABLE y("[a]" INT);
>
> I came across this issue as statements like the following failed with the
> below mentioned error due to incorrect column names in the created tables:
>
> create index [y.a] on y ([a]);
>
> Output in version 3.7.5:
>
> Error: table y has no column named a
>
> I really would like to get that fixed in 3.7.6. At least a patch would be
> welcome during the next week.
>
> Attached you'll find some statements to test with and the outputs of sqlite3
> for versions 3.7.5 and 3.3.6.
>
> Bye.
> --
> Reinhard Nißl
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
