[sqlite] outer join/order by bug?

[Simon Davies]

On 24 February 2015 at 15:20, Simon Davies  
wrote:
> On 24 February 2015 at 15:02, Grisha Vinevich  wrote:
>> There seems to be some problem with left outer join in Windows version of 
>> sqlite3.
>> I try to use the following (simplified) schema:
>>

. 
. 
. 
>> When I run this on SQLFiddle, It 
>> returns (correctly) 2 records.
>> But when I run this on Windows (using sqlite3.exe command-line utility, for 
>> example), no records are returned. Note that if I remove "order by" clause 
>> or one of the joins or even dummy condition inside the first join, the query 
>> works OK.
>> The same happens on Windows Phone using sqlite.net.
>> Any suggestions would be appreciated...
>> Thanks in advance,
>> Grisha.
>
> Perhaps you could advise what version you are using.
>
> When I try:
>
> Microsoft Windows [Version 6.1.7601]
> Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
>
> C:\>sqlite3
> SQLite version 3.7.15.1 2012-12-19 20:39:10
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite>
> sqlite> CREATE TABLE "A" ( "Name" text);
> sqlite> CREATE TABLE "Items" ( "ItemName" text , "Name" text );
> sqlite> INSERT INTO "Items" VALUES('Item1','Parent');
> sqlite> INSERT INTO "Items" VALUES('Item2','Parent');
> sqlite> CREATE TABLE "B" ( "Name" text );
> sqlite>
> sqlite> select Items.ItemName
>...> from Items
>...> left outer join A on (A.Name =
> Items.ItemName and Items.ItemName = 'dummy')
>...> left outer join B on (B.Name = Items.ItemName)
>...> where Items.Name = 'Parent';
> Item1
> Item2
> sqlite>
>
> I get the same result with v 3.8.8.2 from the download page
>
> Regards,
> Simon

Sorry - missed the order by clause.

Confirm your observation using 3.8.8.2, but get your expected result
using 3.7.15.1

Regards,
Simon

[sqlite] outer join/order by bug?

[Simon Davies]

On 24 February 2015 at 15:02, Grisha Vinevich  wrote:
> There seems to be some problem with left outer join in Windows version of 
> sqlite3.
> I try to use the following (simplified) schema:
>
> CREATE TABLE "A" ( "Name" text);
> CREATE TABLE "Items" ( "ItemName" text , "Name" text );
> INSERT INTO "Items" VALUES('Item1','Parent');
> INSERT INTO "Items" VALUES('Item2','Parent');
> CREATE TABLE "B" ( "Name" text );
>
> Now I try to run the following query (again, simplified version of my real 
> query):
>
> select Items.ItemName
> from Items
> left outer join A on (A.Name = Items.ItemName and 
> Items.ItemName = 'dummy')
> left outer join B on (B.Name = Items.ItemName)
> where Items.Name = 'Parent'
> order by Items.ItemName;
> When I run this on SQLFiddle, It 
> returns (correctly) 2 records.
> But when I run this on Windows (using sqlite3.exe command-line utility, for 
> example), no records are returned. Note that if I remove "order by" clause or 
> one of the joins or even dummy condition inside the first join, the query 
> works OK.
> The same happens on Windows Phone using sqlite.net.
> Any suggestions would be appreciated...
> Thanks in advance,
> Grisha.

Perhaps you could advise what version you are using.

When I try:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\>sqlite3
SQLite version 3.7.15.1 2012-12-19 20:39:10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> CREATE TABLE "A" ( "Name" text);
sqlite> CREATE TABLE "Items" ( "ItemName" text , "Name" text );
sqlite> INSERT INTO "Items" VALUES('Item1','Parent');
sqlite> INSERT INTO "Items" VALUES('Item2','Parent');
sqlite> CREATE TABLE "B" ( "Name" text );
sqlite>
sqlite> select Items.ItemName
   ...> from Items
   ...> left outer join A on (A.Name =
Items.ItemName and Items.ItemName = 'dummy')
   ...> left outer join B on (B.Name = Items.ItemName)
   ...> where Items.Name = 'Parent';
Item1
Item2
sqlite>

I get the same result with v 3.8.8.2 from the download page

Regards,
Simon

[sqlite] outer join/order by bug?

[Grisha Vinevich]

There seems to be some problem with left outer join in Windows version of 
sqlite3.
I try to use the following (simplified) schema:

CREATE TABLE "A" ( "Name" text);
CREATE TABLE "Items" ( "ItemName" text , "Name" text );
INSERT INTO "Items" VALUES('Item1','Parent');
INSERT INTO "Items" VALUES('Item2','Parent');
CREATE TABLE "B" ( "Name" text );

Now I try to run the following query (again, simplified version of my real 
query):

select Items.ItemName
from Items
left outer join A on (A.Name = Items.ItemName and 
Items.ItemName = 'dummy')
left outer join B on (B.Name = Items.ItemName)
where Items.Name = 'Parent'
order by Items.ItemName;
When I run this on SQLFiddle, It returns 
(correctly) 2 records.
But when I run this on Windows (using sqlite3.exe command-line utility, for 
example), no records are returned. Note that if I remove "order by" clause or 
one of the joins or even dummy condition inside the first join, the query works 
OK.
The same happens on Windows Phone using sqlite.net.
Any suggestions would be appreciated...
Thanks in advance,
Grisha.

[sqlite] outer join/order by bug?

[Richard Hipp]

On 2/24/15, Grisha Vinevich  wrote:
> There seems to be some problem with left outer join in Windows version of
> sqlite3.

Thanks for the report.  This appears to be a query planner bug
introduced for SQLite version 3.8.8 by the check-in at
https://www.sqlite.org/src/timeline?c=d95d03

We'll probably have a fix out soon.

> I try to use the following (simplified) schema:
>
> CREATE TABLE "A" ( "Name" text);
> CREATE TABLE "Items" ( "ItemName" text , "Name" text );
> INSERT INTO "Items" VALUES('Item1','Parent');
> INSERT INTO "Items" VALUES('Item2','Parent');
> CREATE TABLE "B" ( "Name" text );
>
> Now I try to run the following query (again, simplified version of my real
> query):
>
> select Items.ItemName
> from Items
> left outer join A on (A.Name = Items.ItemName and
> Items.ItemName = 'dummy')
> left outer join B on (B.Name = Items.ItemName)
> where Items.Name = 'Parent'
> order by Items.ItemName;
> When I run this on SQLFiddle, It
> returns (correctly) 2 records.
> But when I run this on Windows (using sqlite3.exe command-line utility, for
> example), no records are returned. Note that if I remove "order by" clause
> or one of the joins or even dummy condition inside the first join, the query
> works OK.
> The same happens on Windows Phone using sqlite.net.
> Any suggestions would be appreciated...
> Thanks in advance,
> Grisha.
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org