On 1/26/18, petern <peter.nichvolo...@gmail.com> wrote: > Kenichi. Nice report. I pasted your code into my console and do see the > correct output you expected: > > cdid > 4 > 5
You have to build with -DSQLITE_ENABLE_STAT4, apparently. > > sqlite> .version > SQLite 3.22.0 2018-01-22 18:45:57 > 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1 > zlib version 1.2.8 > gcc-4.8.4 > > Maybe others can try it on their consoles? > Peter > > On Fri, Jan 26, 2018 at 7:18 PM, Kenichi Ishigaki <kishig...@gmail.com> > wrote: > >> Hi, >> >> I've got a regression report from a DBIx::Class perl module maintainer >> that recent SQLite (3.20.0 and onward) returns a different result from >> the previous versions. >> >> https://rt.cpan.org/Public/Bug/Display.html?id=124227 >> >> Condition: >> >> CREATE TABLE cd ( cdid INTEGER PRIMARY KEY NOT NULL, genreid integer ); >> CREATE INDEX cd_idx_genreid ON cd (genreid); >> INSERT INTO cd ( cdid, genreid ) VALUES >> ( 1, 1 ), >> ( 2, NULL ), >> ( 3, NULL ), >> ( 4, NULL ), >> ( 5, NULL ); >> >> SQL in question: >> >> SELECT cdid >> FROM cd me >> WHERE 2 > ( >> SELECT COUNT( * ) >> FROM cd rownum__emulation >> WHERE >> ( >> me.genreid IS NOT NULL >> AND >> rownum__emulation.genreid IS NULL >> ) >> OR >> ( >> me.genreid IS NOT NULL >> AND >> rownum__emulation.genreid IS NOT NULL >> AND >> rownum__emulation.genreid < me.genreid >> ) >> OR >> ( >> ( me.genreid = rownum__emulation.genreid OR ( me.genreid IS NULL >> AND rownum__emulation.genreid IS NULL ) ) >> AND >> rownum__emulation.cdid > me.cdid >> ) >> ) >> >> Expected Result (3.19.3 and prior): >> >> 4, 5 >> >> Current Result (3.20.0 and onward) >> >> 1, 4, 5 >> >> If cd_idx_genreid index is not created, SQLite 3.20.0 and onward also >> return the expected one. >> >> Best regards, >> >> Kenichi Ishigaki >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
