On 24 February 2015 at 15:20, Simon Davies <simon.james.davies at gmail.com> wrote: > 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: >>
. . . >> 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 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
