| Subject: | SQL-level regression ( likely libsqlite ) |
Using latest DBI, the attached script gives different results depending on which DBD::SQLite trial I am using. This breaks a ( much more convoluted ) DBIC test.
This is *NOT* related to previous DBIC-reported breakages, this one is brand new.
3.17.0 ( DBD::SQLite@1.55_03 ) results as expected
$VAR1 = {
'1.55_03' => [
[
4
],
[
5
]
]
};
3.21.0 ( DBD::SQLite@1.55_04 ) gets one more result than I would expect
$VAR1 = {
'1.55_04' => [
[
1
],
[
4
],
[
5
]
]
};
| Subject: | sqlite_indexing_convoluted.txt |
use warnings;
use strict;
use DBI;
use Data::Dumper;
my $dbh = DBI->connect( 'dbi:SQLite::memory:', undef, undef, { RaiseError => 1 } );
$dbh->do($_) for (
'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 )
',
);
warn Dumper { DBD::SQLite->VERSION => $dbh->selectall_arrayref(<<'EOS') };
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
)
)
EOS