failing test for prefetch bug where prefetched values of 0 might break inflation
[dbsrgits/DBIx-Class-Historic.git] / t / prefetch / with_limit.t
CommitLineData
a04af85f 1# Test to ensure we get a consistent result set wether or not we use the
2# prefetch option in combination rows (LIMIT).
3use strict;
4use warnings;
5
6use Test::More;
61fd5dfc 7use Test::Exception;
a04af85f 8use lib qw(t/lib);
9use DBICTest;
53c29913 10use DBIC::SqlMakerTest;
fcb7fcbb 11use DBIx::Class::SQLMaker::LimitDialects;
12
13my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
a04af85f 14
a04af85f 15my $schema = DBICTest->init_schema();
51a296b4 16
17
a04af85f 18my $no_prefetch = $schema->resultset('Artist')->search(
51a296b4 19 [ # search deliberately contrived
20 { 'artwork.cd_id' => undef },
21 { 'tracks.title' => { '!=' => 'blah-blah-1234568' }}
22 ],
96faafb4 23 { rows => 3, join => { cds => [qw/artwork tracks/] },
24 }
25);
26
27my $use_prefetch = $no_prefetch->search(
28 {},
25cac750 29 {
c9d29bb2 30 select => ['me.artistid', 'me.name'],
31 as => ['artistid', 'name'],
25cac750 32 prefetch => 'cds',
51a296b4 33 order_by => { -desc => 'name' },
25cac750 34 }
a04af85f 35);
36
f7f53a89 37# add an extra +select to make sure it does not throw things off
36fd7f07 38# we also expect it to appear in both selectors, as we can not know
39# for sure which part of the query it applies to (may be order_by,
40# maybe something else)
41#
42# we use a reference to the same array in bind vals, because
43# is_deeply picks up this difference too (not sure if bug or
44# feature)
36fd7f07 45$use_prefetch = $use_prefetch->search({}, {
f7f53a89 46 '+columns' => { monkeywrench => \[ 'me.artistid + ?', [ \ 'inTEger' => 1 ] ] },
36fd7f07 47});
48
0e773352 49my $bind_int_resolved = sub { [ { sqlt_datatype => 'inTEger' } => 1 ] };
50my $bind_vc_resolved = sub { [
51 { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'tracks.title' }
52 => 'blah-blah-1234568'
53] };
36fd7f07 54is_same_sql_bind (
55 $use_prefetch->as_query,
56 '(
f7f53a89 57 SELECT me.artistid + ?,
58 me.artistid, me.name,
59 cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track
36fd7f07 60 FROM (
f7f53a89 61 SELECT me.artistid + ?,
62 me.artistid, me.name
36fd7f07 63 FROM artist me
64 LEFT JOIN cd cds
65 ON cds.artist = me.artistid
66 LEFT JOIN cd_artwork artwork
67 ON artwork.cd_id = cds.cdid
68 LEFT JOIN track tracks
69 ON tracks.cd = cds.cdid
70 WHERE artwork.cd_id IS NULL
71 OR tracks.title != ?
f7f53a89 72 GROUP BY me.artistid + ?, me.artistid, me.name
fcb7fcbb 73 ORDER BY name DESC LIMIT ?
36fd7f07 74 ) me
75 LEFT JOIN cd cds
76 ON cds.artist = me.artistid
77 LEFT JOIN cd_artwork artwork
78 ON artwork.cd_id = cds.cdid
79 LEFT JOIN track tracks
80 ON tracks.cd = cds.cdid
81 WHERE artwork.cd_id IS NULL
82 OR tracks.title != ?
f7f53a89 83 GROUP BY me.artistid + ?, me.artistid, me.name, cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track
908aa1bb 84 ORDER BY name DESC
36fd7f07 85 )',
86 [
0e773352 87 $bind_int_resolved->(), # outer select
88 $bind_int_resolved->(), # inner select
89 $bind_vc_resolved->(), # inner where
90 $bind_int_resolved->(), # inner group_by
fcb7fcbb 91 [ $ROWS => 3 ],
0e773352 92 $bind_vc_resolved->(), # outer where
93 $bind_int_resolved->(), # outer group_by
36fd7f07 94 ],
95 'Expected SQL on complex limited prefetch'
96);
97
a04af85f 98is($no_prefetch->count, $use_prefetch->count, '$no_prefetch->count == $use_prefetch->count');
5624ba1f 99is(
9117ccfb 100 scalar ($no_prefetch->all),
101 scalar ($use_prefetch->all),
102 "Amount of returned rows is right"
5624ba1f 103);
104
9117ccfb 105my $artist_many_cds = $schema->resultset('Artist')->search ( {}, {
106 join => 'cds',
107 group_by => 'me.artistid',
108 having => \ 'count(cds.cdid) > 1',
109})->first;
110
111
5624ba1f 112$no_prefetch = $schema->resultset('Artist')->search(
9117ccfb 113 { artistid => $artist_many_cds->id },
5624ba1f 114 { rows => 1 }
115);
116
61fd5dfc 117$use_prefetch = $no_prefetch->search ({}, { prefetch => 'cds' });
5624ba1f 118
61fd5dfc 119my $normal_artist = $no_prefetch->single;
120my $prefetch_artist = $use_prefetch->find({ name => $artist_many_cds->name });
121my $prefetch2_artist = $use_prefetch->first;
5624ba1f 122
123is(
124 $prefetch_artist->cds->count,
125 $normal_artist->cds->count,
61fd5dfc 126 "Count of child rel with prefetch + rows => 1 is right (find)"
127);
128is(
129 $prefetch2_artist->cds->count,
130 $normal_artist->cds->count,
131 "Count of child rel with prefetch + rows => 1 is right (first)"
5624ba1f 132);
61fd5dfc 133
9117ccfb 134is (
135 scalar ($prefetch_artist->cds->all),
136 scalar ($normal_artist->cds->all),
61fd5dfc 137 "Amount of child rel rows with prefetch + rows => 1 is right (find)"
138);
139is (
140 scalar ($prefetch2_artist->cds->all),
141 scalar ($normal_artist->cds->all),
142 "Amount of child rel rows with prefetch + rows => 1 is right (first)"
143);
144
145throws_ok (
146 sub { $use_prefetch->single },
147 qr/resultsets prefetching has_many/,
148 'single() with multiprefetch is illegal',
9117ccfb 149);
01c781fe 150
14e26c5f 151throws_ok (
152 sub {
153 $use_prefetch->search(
154 {'tracks.title' => { '!=' => 'foo' }},
155 { order_by => \ 'some oddball literal sql', join => { cds => 'tracks' } }
156 )->next
157 }, qr/A required group_by clause could not be constructed automatically/,
158);
159
09707a31 160my $artist = $use_prefetch->search({'cds.title' => $artist_many_cds->cds->first->title })->next;
01c781fe 161is($artist->cds->count, 1, "count on search limiting prefetched has_many");
162
a3683eae 163# try with double limit
164my $artist2 = $use_prefetch->search({'cds.title' => { '!=' => $artist_many_cds->cds->first->title } })->slice (0,0)->next;
165is($artist2->cds->count, 2, "count on search limiting prefetched has_many");
166
53c29913 167# make sure 1:1 joins do not force a subquery (no point to exercise the optimizer, if at all available)
168# get cd's that have any tracks and their artists
169my $single_prefetch_rs = $schema->resultset ('CD')->search (
170 { 'me.year' => 2010, 'artist.name' => 'foo' },
171 { prefetch => ['tracks', 'artist'], rows => 15 },
172);
173is_same_sql_bind (
174 $single_prefetch_rs->as_query,
175 '(
176 SELECT
177 me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
3d98c75e 178 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at,
53c29913 179 artist.artistid, artist.name, artist.rank, artist.charfield
180 FROM (
181 SELECT
182 me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
183 FROM cd me
184 JOIN artist artist ON artist.artistid = me.artist
185 WHERE ( ( artist.name = ? AND me.year = ? ) )
fcb7fcbb 186 LIMIT ?
53c29913 187 ) me
188 LEFT JOIN track tracks
189 ON tracks.cd = me.cdid
190 JOIN artist artist
191 ON artist.artistid = me.artist
192 WHERE ( ( artist.name = ? AND me.year = ? ) )
4e9fc3f3 193 ORDER BY me.cdid
53c29913 194 )',
fcb7fcbb 195 [
196 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' } => 'foo' ],
197 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.year' } => 2010 ],
198 [ $ROWS => 15 ],
199 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' } => 'foo' ],
200 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.year' } => 2010 ],
201 ],
53c29913 202 'No grouping of non-multiplying resultsets',
203);
204
c9d29bb2 205done_testing;