I get the same error in 3.6.18, so probably the same solution applies in
3.6.20. I got the query to work with a sub-select.
SQLite version 3.6.18
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> BEGIN TRANSACTION;
sqlite> CREATE TABLE basica(
...> x,
...> y,
...> suma
...> );
sqlite> INSERT INTO "basica" VALUES('Austria','1996-03',5904.0);
sqlite> INSERT INTO "basica" VALUES('Austria','1996-04',21904.0);
sqlite> INSERT INTO "basica" VALUES('Germany','1996-03',10545.0);
sqlite> INSERT INTO "basica" VALUES('Germany','1996-04',13687.0);
sqlite> INSERT INTO "basica" VALUES('USA','1996-03',21814.0);
sqlite> INSERT INTO "basica" VALUES('USA','1996-04',13108.0);
sqlite>
sqlite> CREATE TABLE groupLimX(
...> x,
...> sumaXs
...> );
sqlite> INSERT INTO "groupLimX" VALUES('USA',305843.0);
sqlite> INSERT INTO "groupLimX" VALUES('Germany',258820.0);
sqlite> INSERT INTO "groupLimX" VALUES('Austria',140668.0);
sqlite>
sqlite> CREATE TABLE groupLimY(
...> y,
...> sumaYs
...> );
sqlite> INSERT INTO "groupLimY" VALUES('1996-04',113818.0);
sqlite> INSERT INTO "groupLimY" VALUES('1996-03',102947.0);
sqlite> COMMIT;
sqlite>
sqlite> .header on
sqlite> SELECT * FROM groupLimY INNER JOIN groupLimX;
y|sumaYs|x|sumaXs
1996-04|113818.0|USA|305843.0
1996-04|113818.0|Germany|258820.0
1996-04|113818.0|Austria|140668.0
1996-03|102947.0|USA|305843.0
1996-03|102947.0|Germany|258820.0
1996-03|102947.0|Austria|140668.0
sqlite> CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN groupLimX;
sqlite> SELECT * FROM mia LEFT JOIN basica USING (y, x);
y|sumaYs|x|sumaXs|suma
1996-04|113818.0|USA|305843.0|13108.0
1996-04|113818.0|Germany|258820.0|13687.0
1996-04|113818.0|Austria|140668.0|21904.0
1996-03|102947.0|USA|305843.0|21814.0
1996-03|102947.0|Germany|258820.0|10545.0
1996-03|102947.0|Austria|140668.0|5904.0
sqlite> SELECT * FROM (groupLimY INNER JOIN groupLimX) LEFT JOIN basica USING
(y,x);
SQL error: cannot join using column y - column not present in both tables
sqlite> SELECT * FROM (select x, sumaXs, y, sumaYs from groupLimY INNER JOIN
groupLimX) LEFT JOIN basica USING (y,x);
x|sumaXs|y|sumaYs|suma
USA|305843.0|1996-04|113818.0|13108.0
Germany|258820.0|1996-04|113818.0|13687.0
Austria|140668.0|1996-04|113818.0|21904.0
USA|305843.0|1996-03|102947.0|21814.0
Germany|258820.0|1996-03|102947.0|10545.0
Austria|140668.0|1996-03|102947.0|5904.0
sqlite>
sqlite> SELECT * FROM (select * from groupLimY INNER JOIN groupLimX) LEFT JOIN
basica USING (y,x);
y|sumaYs|x|sumaXs|suma
1996-04|113818.0|USA|305843.0|13108.0
1996-04|113818.0|Germany|258820.0|13687.0
1996-04|113818.0|Austria|140668.0|21904.0
1996-03|102947.0|USA|305843.0|21814.0
1996-03|102947.0|Germany|258820.0|10545.0
1996-03|102947.0|Austria|140668.0|5904.0
sqlite>
Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com
HARRIS CORPORATION | RF Communications Division
assuredcommunications(tm)
> -----Original Message-----
> From: [email protected] [mailto:sqlite-users-
> [email protected]] On Behalf Of [email protected]
> Sent: Tuesday, December 29, 2009 8:56 AM
> To: [email protected]
> Subject: [sqlite] BUG Report on sqlite 3.6.20 "Error in SQL parser between
> sqlite3.3.4 and sqlite3.6.20"
>
> 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);
> -----------------------------------------------------------
>
>
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
