Hello,
I detect this problem because a program using sqlite command line works
on sqlite.3.3.4 but
it does not anymore using sqlite3.6.20
PROBLEM TITLE: Unjustified Error joining tables in sqlite3.6.20 (in
previous version sqlite3.3.4 OK)
OR Error in SQL parser between
sqlite3.3.4 and sqlite3.6.20
TESTED WITH: sqlite3.6.20 and sqlite3.3.4 Windows command line exes
HOW TO REPRODUCE IT:
Execute following batch on both versions of sqlite
sqlite3 < Fails3.6.20.sql
when using 3.6.20 we get the "unjustified error"
Best regards,
Alejandro
------------Fails3.6.20.sql------------------
BEGIN TRANSACTION;
CREATE TABLE basica(
x,
y,
suma
);
INSERT INTO "basica" VALUES('Austria','1996-03',5904.0);
INSERT INTO "basica" VALUES('Austria','1996-04',21904.0);
INSERT INTO "basica" VALUES('Germany','1996-03',10545.0);
INSERT INTO "basica" VALUES('Germany','1996-04',13687.0);
INSERT INTO "basica" VALUES('USA','1996-03',21814.0);
INSERT INTO "basica" VALUES('USA','1996-04',13108.0);
CREATE TABLE groupLimX(
x,
sumaXs
);
INSERT INTO "groupLimX" VALUES('USA',305843.0);
INSERT INTO "groupLimX" VALUES('Germany',258820.0);
INSERT INTO "groupLimX" VALUES('Austria',140668.0);
CREATE TABLE groupLimY(
y,
sumaYs
);
INSERT INTO "groupLimY" VALUES('1996-04',113818.0);
INSERT INTO "groupLimY" VALUES('1996-03',102947.0);
COMMIT;
.header on
SELECT * FROM groupLimY INNER JOIN groupLimX;
CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN groupLimX;
SELECT * FROM mia LEFT JOIN basica USING (y, x);
/* IN THIS LAST SELECT sqlite3 (3.6.20) FAILS!! WHILE sqlite 3.3.4
SUCCESSED */
SELECT * FROM (groupLimY INNER JOIN groupLimX) LEFT JOIN basica USING
(y, x);
-----------------------------------------------------------
BEGIN TRANSACTION;
CREATE TABLE basica(
x,
y,
suma
);
INSERT INTO "basica" VALUES('Austria','1996-03',5904.0);
INSERT INTO "basica" VALUES('Austria','1996-04',21904.0);
INSERT INTO "basica" VALUES('Germany','1996-03',10545.0);
INSERT INTO "basica" VALUES('Germany','1996-04',13687.0);
INSERT INTO "basica" VALUES('USA','1996-03',21814.0);
INSERT INTO "basica" VALUES('USA','1996-04',13108.0);
CREATE TABLE groupLimX(
x,
sumaXs
);
INSERT INTO "groupLimX" VALUES('USA',305843.0);
INSERT INTO "groupLimX" VALUES('Germany',258820.0);
INSERT INTO "groupLimX" VALUES('Austria',140668.0);
CREATE TABLE groupLimY(
y,
sumaYs
);
INSERT INTO "groupLimY" VALUES('1996-04',113818.0);
INSERT INTO "groupLimY" VALUES('1996-03',102947.0);
COMMIT;
.header on
SELECT * FROM groupLimY INNER JOIN groupLimX;
CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN groupLimX;
SELECT * FROM mia LEFT JOIN basica USING (y, x);
/* IN THIS LAST SELECT sqlite3 (3.6.20) FAILS!! WHILE sqlite 3.3.4 SUCCESSED */
SELECT * FROM (groupLimY INNER JOIN groupLimX) LEFT JOIN basica USING (y, x);
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
