1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
|
use strict;
use warnings;
use lib "t/lib";
use SQLiteTest;
use Test::More;
use Test::NoWarnings;
plan tests => 5;
my $sql_in_question = <<'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
)
)
ORDER BY cdid
EOS
{ # With an index
my $dbh = connect_ok();
$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 )
',
);
my $res = $dbh->selectall_arrayref($sql_in_question);
is_deeply $res => [[4], [5]], "got the expected result with the index" or note explain $res;
}
{ # Without the index
my $dbh = connect_ok();
$dbh->do($_) for (
'CREATE TABLE cd ( cdid INTEGER PRIMARY KEY NOT NULL, genreid integer )',
'INSERT INTO cd ( cdid, genreid ) VALUES
( 1, 1 ),
( 2, NULL ),
( 3, NULL ),
( 4, NULL ),
( 5, NULL )
',
);
my $res = $dbh->selectall_arrayref($sql_in_question);
is_deeply $res => [[4], [5]], "got the expected result without the index" or note explain $res;
}
|
