On 24 February 2015 at 15:02, Grisha Vinevich <grisha at negevsoft.com> 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<http://www.sqlfiddle.com/#!7/763a5/1>, 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
