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
.headers ON
create table x(a int);
insert into x values (1);
select [a] from x;
select [a] from x group by a;
select [a] from (select a from x);
select [a] from (select a from x) group by a;
create table y as select [a] from (select a from x) group by a;
create index [y.a] on y([a]);
select "a" from x;
select "a" from x group by a;
select "a" from (select a from x);
select "a" from (select a from x) group by a;
create table z as select "a" from (select a from x) group by a;
create index "z.a" on z("a");
.schema
drop table x;
drop table y;
drop table z;
create table x([a.b] int);
insert into x values (1);
select [a.b] from x;
select [a.b] from x group by [a.b];
select [a.b] from (select [a.b] from x);
select [a.b] from (select [a.b] from x) group by [a.b];
create table y as select [a.b] from (select [a.b] from x) group by [a.b];
create index [y.a.b] on y([a.b]);
select "a.b" from x;
select "a.b" from x group by "a.b";
select "a.b" from (select "a.b" from x);
select "a.b" from (select "a.b" from x) group by "a.b";
create table z as select "a.b" from (select "a.b" from x) group by "a.b";
create index "z.a.b" on z("a.b");
.schema
.quit
a
1
a
1
[a]
1
[a]
1
a
1
a
1
"a"
1
"a"
1
CREATE TABLE x(a int);
CREATE TABLE y("[a]" INT);
CREATE TABLE z("""a""" INT);
a.b
1
a.b
1
[a.b]
1
[a.b]
1
a.b
1
a.b
1
"a.b"
1
"a.b"
1
CREATE TABLE x([a.b] int);
CREATE TABLE y("[a.b]" INT);
CREATE TABLE z("""a.b""" INT);
Error: near line 9: table y has no column named a
Error: near line 15: table z has no column named a
Error: near line 27: table y has no column named a.b
Error: near line 33: table z has no column named a.b
a
1
[a]
1
[a]
1
[a]
1
a
1
"a"
1
"a"
1
"a"
1
CREATE TABLE x(a int);
CREATE TABLE y(a int);
CREATE TABLE z(a int);
CREATE INDEX [y.a] on y([a]);
CREATE INDEX "z.a" on z("a");
a.b
1
[a.b]
1
[a.b]
1
[a.b]
1
a.b
1
"a.b"
1
"a.b"
1
"a.b"
1
CREATE TABLE x([a.b] int);
CREATE TABLE y("a.b" int);
CREATE TABLE z("a.b" int);
CREATE INDEX [y.a.b] on y([a.b]);
CREATE INDEX "z.a.b" on z("a.b");
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
