> 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);
Please show us documentation that says you should get the same column name for each of these statements. I can understand that this is kind of counter-intuitive for you but it behaves exactly how it's documented, i.e. column name is undefined and can change from version to version. > I consider this a bug. You can consider it a bug. But until SQLite developers consider it a bug it won't be fixed. I'm not one of SQLite developers but I know how they feel about this problem because it was raised on this list numerous times. Pavel On Thu, Feb 10, 2011 at 10:22 AM, Nißl Reinhard <reinhard.ni...@fee.de> wrote: > 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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > 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 <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 > _______________________________________________ > 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
