On Dec 30, 2009, at 4:52 AM, Wilson, Ronald wrote:
> 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.
Changed between 3.6.6 and 3.6.7 from the looks of things.
>
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
