SQLite

BUG Report on sqlite 3.6.20 "Error in SQL parser between sqlite3.3.4 and sqlite3.6.20"

classic Classic list List threaded Threaded
♦
♦
 3 messages Options
javaj1811@elxala.com
Reply | Threaded
Open this post in threaded view
♦
♦
|

BUG Report on sqlite 3.6.20 "Error in SQL parser between sqlite3.3.4 and sqlite3.6.20"

javaj1811@elxala.com
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Wilson, Ronald
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: BUG Report on sqlite 3.6.20 "Error in SQL parser between sqlite3.3.4 and sqlite3.6.20"

Wilson, Ronald
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: [hidden email] [mailto:sqlite-users-
> [hidden email]] On Behalf Of [hidden email]
> Sent: Tuesday, December 29, 2009 8:56 AM
> To: [hidden email]
> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Dan Kennedy-4
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: BUG Report on sqlite 3.6.20 "Error in SQL parser between sqlite3.3.4 and sqlite3.6.20"

Dan Kennedy-4

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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Free forum by Nabble Edit this page