[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users