regression since 3.20.0

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

regression since 3.20.0

kenichi ishigaki
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: regression since 3.20.0

petern
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 <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: regression since 3.20.0

Richard Hipp-3
On 1/26/18, petern <[hidden email]> 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 <[hidden email]>
> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: regression since 3.20.0

petern
Confirmed.  3.22 build with -DSQLITE_ENABLE_STAT4 remarkably produces the
other answer:
cdid
1
4
5
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: regression since 3.20.0

Richard Hipp-3
In reply to this post by kenichi ishigaki
On 1/26/18, Kenichi Ishigaki <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: regression since 3.20.0

kenichi ishigaki
2018-01-27 22:59 GMT+09:00 Richard Hipp <[hidden email]>:

> On 1/26/18, Kenichi Ishigaki <[hidden email]> 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
> [hidden email]

Thank you!

Regards,
Kenichi
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users