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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of javaj1...@elxala.com
> Sent: Tuesday, December 29, 2009 8:56 AM
> To: sqlite-users@sqlite.org
> 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to