Kenichi. Nice report. I pasted your code into my console and do see the correct output you expected:
cdid 4 5 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
