Hi,
I'm sorry Pavel, I think you've got me wrong.
> It's not "buggy". Name of the column in result set is not defined
> unless you use "as".
But why do I get different column names but the same result for these
statements?
select a from (select a from x);
select [a] from (select a from x);
select "a" from (select a from x);
For all three statements the column name should be just >>a<<, as it is for
these statements:
select a from x;
select [a] from x;
select "a" from x;
Why should I have to write to the statements like below to get what I want?
select a a from (select a from x);
select a [a] from (select a from x);
select a "a" from (select a from x);
select [a] a from (select a from x);
select [a] [a] from (select a from x);
select [a] "a" from (select a from x);
select "a" a from (select a from x);
select "a" [a] from (select a from x);
select "a" "a" from (select a from x);
I consider this a bug.
Bye.
--
Reinhard Nißl
-----Ursprüngliche Nachricht-----
Von: [email protected] [mailto:[email protected]]
Im Auftrag von Pavel Ivanov
Gesendet: Donnerstag, 10. Februar 2011 13:48
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of
column names for certain statements
> 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 <[email protected]> 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
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
