Re: [sqlite] regression since 3.20.0
2018-01-27 22:59 GMT+09:00 Richard Hipp : > On 1/26/18, Kenichi Ishigaki 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. > > Thanks for the bug report. Now fixed on trunk > > The ticket: https://www.sqlite.org/src/info/ec32177c99ccac2b1 > The patch: > https://www.sqlite.org/src/fdiff?v1=caf0b6c9d31f22f0&v2=10c7e13c909e4dc9 > > -- > D. Richard Hipp > d...@sqlite.org Thank you! Regards, Kenichi ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] regression since 3.20.0
On 1/26/18, Kenichi Ishigaki 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. Thanks for the bug report. Now fixed on trunk The ticket: https://www.sqlite.org/src/info/ec32177c99ccac2b1 The patch: https://www.sqlite.org/src/fdiff?v1=caf0b6c9d31f22f0&v2=10c7e13c909e4dc9 -- 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
Re: [sqlite] regression since 3.20.0
Confirmed. 3.22 build with -DSQLITE_ENABLE_STAT4 remarkably produces the other answer: cdid 1 4 5 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] regression since 3.20.0
On 1/26/18, petern 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 > 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
Re: [sqlite] regression since 3.20.0
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 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
[sqlite] regression since 3.20.0
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
