Commit | Line | Data |
27a7c422 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
66458d5d |
5 | use Test::Exception; |
27a7c422 |
6 | use lib qw(t/lib); |
7 | use DBICTest; |
8 | use DBIC::SqlMakerTest; |
9 | |
10 | my $schema = DBICTest->init_schema; |
66458d5d |
11 | my $native_limit_dialect = $schema->storage->sql_maker->{limit_dialect}; |
27a7c422 |
12 | |
13 | my $attr = {}; |
14 | my @where_bind = ( |
15 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Study' ], |
16 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.title' } => 'kama sutra' ], |
17 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
18 | ); |
19 | my @select_bind = ( |
20 | [ $attr => 11 ], [ $attr => 12 ], [ $attr => 13 ], |
21 | ); |
22 | my @group_bind = ( |
23 | [ $attr => 21 ], |
24 | ); |
25 | my @having_bind = ( |
26 | [ $attr => 31 ], |
27 | ); |
28 | my @order_bind = ( |
29 | [ $attr => 1 ], [ $attr => 2 ], [ $attr => 3 ], |
30 | ); |
31 | |
32 | my $tests = { |
4b8da207 |
33 | |
27a7c422 |
34 | LimitOffset => { |
35 | ordered_limit_offset => [ |
36 | '( |
4b8da207 |
37 | SELECT me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
38 | FROM books me |
4b8da207 |
39 | JOIN owners owner |
40 | ON owner.id = me.owner |
27a7c422 |
41 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
42 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
43 | HAVING ? |
44 | ORDER BY ? / ?, ? |
45 | LIMIT ? |
46 | OFFSET ? |
47 | )', |
48 | [ |
49 | @select_bind, |
50 | @where_bind, |
51 | @group_bind, |
52 | @having_bind, |
53 | @order_bind, |
54 | [ { sqlt_datatype => 'integer' } => 4 ], |
55 | [ { sqlt_datatype => 'integer' } => 3 ], |
56 | ], |
57 | ], |
66458d5d |
58 | limit_offset_prefetch => [ |
59 | '( |
60 | SELECT me.name, books.id, books.source, books.owner, books.title, books.price |
61 | FROM ( |
62 | SELECT me.name, me.id |
63 | FROM owners me |
64 | LIMIT ? OFFSET ? |
65 | ) me |
66 | LEFT JOIN books books |
67 | ON books.owner = me.id |
66458d5d |
68 | )', |
69 | [ |
70 | [ { sqlt_datatype => 'integer' } => 3 ], |
71 | [ { sqlt_datatype => 'integer' } => 1 ], |
72 | ] |
73 | ], |
27a7c422 |
74 | }, |
75 | |
76 | LimitXY => { |
77 | ordered_limit_offset => [ |
78 | '( |
4b8da207 |
79 | SELECT me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
80 | FROM books me |
4b8da207 |
81 | JOIN owners owner |
82 | ON owner.id = me.owner |
27a7c422 |
83 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
84 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
85 | HAVING ? |
86 | ORDER BY ? / ?, ? |
87 | LIMIT ?, ? |
88 | )', |
89 | [ |
90 | @select_bind, |
91 | @where_bind, |
92 | @group_bind, |
93 | @having_bind, |
94 | @order_bind, |
95 | [ { sqlt_datatype => 'integer' } => 3 ], |
96 | [ { sqlt_datatype => 'integer' } => 4 ], |
97 | ], |
98 | ], |
66458d5d |
99 | limit_offset_prefetch => [ |
100 | '( |
101 | SELECT me.name, books.id, books.source, books.owner, books.title, books.price |
102 | FROM ( |
103 | SELECT me.name, me.id |
104 | FROM owners me |
105 | LIMIT ?,? |
106 | ) me |
107 | LEFT JOIN books books |
108 | ON books.owner = me.id |
66458d5d |
109 | )', |
110 | [ |
111 | [ { sqlt_datatype => 'integer' } => 1 ], |
112 | [ { sqlt_datatype => 'integer' } => 3 ], |
113 | ] |
114 | ], |
27a7c422 |
115 | }, |
116 | |
117 | SkipFirst => { |
118 | ordered_limit_offset => [ |
119 | '( |
4b8da207 |
120 | SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
121 | FROM books me |
4b8da207 |
122 | JOIN owners owner |
123 | ON owner.id = me.owner |
27a7c422 |
124 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
125 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
126 | HAVING ? |
127 | ORDER BY ? / ?, ? |
128 | )', |
129 | [ |
130 | [ { sqlt_datatype => 'integer' } => 3 ], |
131 | [ { sqlt_datatype => 'integer' } => 4 ], |
132 | @select_bind, |
133 | @where_bind, |
134 | @group_bind, |
135 | @having_bind, |
136 | @order_bind, |
137 | ], |
138 | ], |
66458d5d |
139 | limit_offset_prefetch => [ |
140 | '( |
141 | SELECT me.name, books.id, books.source, books.owner, books.title, books.price |
142 | FROM ( |
143 | SELECT SKIP ? FIRST ? me.name, me.id |
144 | FROM owners me |
145 | ) me |
146 | LEFT JOIN books books |
147 | ON books.owner = me.id |
66458d5d |
148 | )', |
149 | [ |
150 | [ { sqlt_datatype => 'integer' } => 1 ], |
151 | [ { sqlt_datatype => 'integer' } => 3 ], |
152 | ] |
153 | ], |
27a7c422 |
154 | }, |
155 | |
156 | FirstSkip => { |
157 | ordered_limit_offset => [ |
158 | '( |
4b8da207 |
159 | SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
160 | FROM books me |
4b8da207 |
161 | JOIN owners owner |
162 | ON owner.id = me.owner |
27a7c422 |
163 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
164 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
165 | HAVING ? |
166 | ORDER BY ? / ?, ? |
167 | )', |
168 | [ |
169 | [ { sqlt_datatype => 'integer' } => 4 ], |
170 | [ { sqlt_datatype => 'integer' } => 3 ], |
171 | @select_bind, |
172 | @where_bind, |
173 | @group_bind, |
174 | @having_bind, |
175 | @order_bind, |
176 | ], |
177 | ], |
66458d5d |
178 | limit_offset_prefetch => [ |
179 | '( |
180 | SELECT me.name, books.id, books.source, books.owner, books.title, books.price |
181 | FROM ( |
182 | SELECT FIRST ? SKIP ? me.name, me.id |
183 | FROM owners me |
184 | ) me |
185 | LEFT JOIN books books |
186 | ON books.owner = me.id |
66458d5d |
187 | )', |
188 | [ |
189 | [ { sqlt_datatype => 'integer' } => 3 ], |
190 | [ { sqlt_datatype => 'integer' } => 1 ], |
191 | ] |
192 | ], |
27a7c422 |
193 | }, |
194 | |
195 | RowNumberOver => do { |
196 | my $unordered_sql = '( |
4b8da207 |
197 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
198 | FROM ( |
4b8da207 |
199 | SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index |
27a7c422 |
200 | FROM ( |
4b8da207 |
201 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
202 | FROM books me |
4b8da207 |
203 | JOIN owners owner |
204 | ON owner.id = me.owner |
27a7c422 |
205 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
206 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
207 | HAVING ? |
208 | ) me |
209 | ) me |
210 | WHERE rno__row__index >= ? AND rno__row__index <= ? |
211 | )'; |
212 | |
213 | my $ordered_sql = '( |
4b8da207 |
214 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
215 | FROM ( |
08a1eaad |
216 | SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER( ORDER BY ORDER__BY__001, ORDER__BY__002 ) AS rno__row__index |
27a7c422 |
217 | FROM ( |
4b8da207 |
218 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, |
08a1eaad |
219 | ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002 |
27a7c422 |
220 | FROM books me |
4b8da207 |
221 | JOIN owners owner |
222 | ON owner.id = me.owner |
27a7c422 |
223 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
224 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
225 | HAVING ? |
226 | ) me |
227 | ) me |
228 | WHERE rno__row__index >= ? AND rno__row__index <= ? |
229 | )'; |
230 | |
231 | { |
232 | limit => [$unordered_sql, |
233 | [ |
234 | @select_bind, |
235 | @where_bind, |
236 | @group_bind, |
237 | @having_bind, |
238 | [ { sqlt_datatype => 'integer' } => 1 ], |
239 | [ { sqlt_datatype => 'integer' } => 4 ], |
240 | ], |
241 | ], |
242 | limit_offset => [$unordered_sql, |
243 | [ |
244 | @select_bind, |
245 | @where_bind, |
246 | @group_bind, |
247 | @having_bind, |
248 | [ { sqlt_datatype => 'integer' } => 4 ], |
249 | [ { sqlt_datatype => 'integer' } => 7 ], |
250 | ], |
251 | ], |
252 | ordered_limit => [$ordered_sql, |
253 | [ |
254 | @select_bind, |
255 | @order_bind, |
256 | @where_bind, |
257 | @group_bind, |
258 | @having_bind, |
259 | [ { sqlt_datatype => 'integer' } => 1 ], |
260 | [ { sqlt_datatype => 'integer' } => 4 ], |
261 | ], |
262 | ], |
263 | ordered_limit_offset => [$ordered_sql, |
264 | [ |
265 | @select_bind, |
266 | @order_bind, |
267 | @where_bind, |
268 | @group_bind, |
269 | @having_bind, |
270 | [ { sqlt_datatype => 'integer' } => 4 ], |
271 | [ { sqlt_datatype => 'integer' } => 7 ], |
272 | ], |
273 | ], |
66458d5d |
274 | limit_offset_prefetch => [ |
275 | '( |
276 | SELECT me.name, books.id, books.source, books.owner, books.title, books.price |
277 | FROM ( |
278 | SELECT me.name, me.id |
279 | FROM ( |
280 | SELECT me.name, me.id, ROW_NUMBER() OVER() AS rno__row__index |
281 | FROM ( |
282 | SELECT me.name, me.id FROM owners me |
283 | ) me |
284 | ) me |
285 | WHERE rno__row__index >= ? AND rno__row__index <= ? |
286 | ) me |
287 | LEFT JOIN books books |
288 | ON books.owner = me.id |
66458d5d |
289 | )', |
290 | [ |
291 | [ { sqlt_datatype => 'integer' } => 2 ], |
292 | [ { sqlt_datatype => 'integer' } => 4 ], |
293 | ] |
294 | ], |
27a7c422 |
295 | }; |
296 | }, |
297 | |
298 | RowNum => do { |
299 | my $limit_sql = sub { |
300 | sprintf '( |
4b8da207 |
301 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
302 | FROM ( |
4b8da207 |
303 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
304 | FROM books me |
4b8da207 |
305 | JOIN owners owner |
306 | ON owner.id = me.owner |
27a7c422 |
307 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
308 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
309 | HAVING ? |
310 | %s |
311 | ) me |
312 | WHERE ROWNUM <= ? |
313 | )', $_[0] || ''; |
314 | }; |
315 | |
316 | { |
317 | limit => [ $limit_sql->(), |
318 | [ |
319 | @select_bind, |
320 | @where_bind, |
321 | @group_bind, |
322 | @having_bind, |
323 | [ { sqlt_datatype => 'integer' } => 4 ], |
324 | ], |
325 | ], |
326 | limit_offset => [ |
327 | '( |
4b8da207 |
328 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
329 | FROM ( |
4b8da207 |
330 | SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index |
27a7c422 |
331 | FROM ( |
4b8da207 |
332 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
333 | FROM books me |
4b8da207 |
334 | JOIN owners owner |
335 | ON owner.id = me.owner |
27a7c422 |
336 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
337 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
338 | HAVING ? |
339 | ) me |
340 | ) me |
341 | WHERE rownum__index BETWEEN ? AND ? |
342 | )', |
343 | [ |
344 | @select_bind, |
345 | @where_bind, |
346 | @group_bind, |
347 | @having_bind, |
348 | [ { sqlt_datatype => 'integer' } => 4 ], |
349 | [ { sqlt_datatype => 'integer' } => 7 ], |
350 | ], |
351 | ], |
352 | ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'), |
353 | [ |
354 | @select_bind, |
355 | @where_bind, |
356 | @group_bind, |
357 | @having_bind, |
358 | @order_bind, |
359 | [ { sqlt_datatype => 'integer' } => 4 ], |
360 | ], |
361 | ], |
362 | ordered_limit_offset => [ |
363 | '( |
4b8da207 |
364 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
365 | FROM ( |
4b8da207 |
366 | SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index |
27a7c422 |
367 | FROM ( |
4b8da207 |
368 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
369 | FROM books me |
4b8da207 |
370 | JOIN owners owner |
371 | ON owner.id = me.owner |
27a7c422 |
372 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
373 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
374 | HAVING ? |
375 | ORDER BY ? / ?, ? |
376 | ) me |
377 | WHERE ROWNUM <= ? |
378 | ) me |
379 | WHERE rownum__index >= ? |
380 | )', |
381 | [ |
382 | @select_bind, |
383 | @where_bind, |
384 | @group_bind, |
385 | @having_bind, |
386 | @order_bind, |
387 | [ { sqlt_datatype => 'integer' } => 7 ], |
388 | [ { sqlt_datatype => 'integer' } => 4 ], |
389 | ], |
390 | ], |
66458d5d |
391 | limit_offset_prefetch => [ |
392 | '( |
393 | SELECT me.name, books.id, books.source, books.owner, books.title, books.price |
394 | FROM ( |
395 | SELECT me.name, me.id |
396 | FROM ( |
397 | SELECT me.name, me.id, ROWNUM rownum__index |
398 | FROM ( |
399 | SELECT me.name, me.id |
400 | FROM owners me |
401 | ) me |
402 | ) me WHERE rownum__index BETWEEN ? AND ? |
403 | ) me |
404 | LEFT JOIN books books |
405 | ON books.owner = me.id |
66458d5d |
406 | )', |
407 | [ |
408 | [ { sqlt_datatype => 'integer' } => 2 ], |
409 | [ { sqlt_datatype => 'integer' } => 4 ], |
410 | ] |
411 | ], |
27a7c422 |
412 | }; |
413 | }, |
414 | |
27a7c422 |
415 | FetchFirst => { |
416 | limit => [ |
417 | '( |
4b8da207 |
418 | SELECT me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
419 | FROM books me |
4b8da207 |
420 | JOIN owners owner |
421 | ON owner.id = me.owner |
27a7c422 |
422 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
423 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
424 | HAVING ? |
425 | FETCH FIRST 4 ROWS ONLY |
426 | )', |
427 | [ |
428 | @select_bind, |
429 | @where_bind, |
430 | @group_bind, |
431 | @having_bind, |
432 | ], |
433 | ], |
434 | limit_offset => [ |
435 | '( |
4b8da207 |
436 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
437 | FROM ( |
4b8da207 |
438 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
439 | FROM books me |
4b8da207 |
440 | JOIN owners owner |
441 | ON owner.id = me.owner |
27a7c422 |
442 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
443 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
444 | HAVING ? |
445 | ORDER BY me.id |
446 | FETCH FIRST 7 ROWS ONLY |
447 | ) me |
448 | ORDER BY me.id DESC |
449 | FETCH FIRST 4 ROWS ONLY |
450 | )', |
451 | [ |
452 | @select_bind, |
453 | @where_bind, |
454 | @group_bind, |
455 | @having_bind, |
456 | ], |
457 | ], |
458 | ordered_limit => [ |
459 | '( |
4b8da207 |
460 | SELECT me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
461 | FROM books me |
4b8da207 |
462 | JOIN owners owner |
463 | ON owner.id = me.owner |
27a7c422 |
464 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
465 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
466 | HAVING ? |
467 | ORDER BY ? / ?, ? |
468 | FETCH FIRST 4 ROWS ONLY |
469 | )', |
470 | [ |
471 | @select_bind, |
472 | @where_bind, |
473 | @group_bind, |
474 | @having_bind, |
475 | @order_bind, |
476 | ], |
477 | ], |
478 | ordered_limit_offset => [ |
479 | '( |
4b8da207 |
480 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
481 | FROM ( |
08a1eaad |
482 | SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002 |
27a7c422 |
483 | FROM ( |
08a1eaad |
484 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002 |
27a7c422 |
485 | FROM books me |
4b8da207 |
486 | JOIN owners owner |
487 | ON owner.id = me.owner |
27a7c422 |
488 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
489 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
490 | HAVING ? |
491 | ORDER BY ? / ?, ? |
492 | FETCH FIRST 7 ROWS ONLY |
493 | ) me |
08a1eaad |
494 | ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC |
27a7c422 |
495 | FETCH FIRST 4 ROWS ONLY |
496 | ) me |
08a1eaad |
497 | ORDER BY ORDER__BY__001, ORDER__BY__002 |
27a7c422 |
498 | )', |
499 | [ |
500 | @select_bind, |
501 | @order_bind, |
502 | @where_bind, |
503 | @group_bind, |
504 | @having_bind, |
505 | (map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit |
506 | ], |
507 | ], |
66458d5d |
508 | limit_offset_prefetch => [ |
509 | '( |
510 | SELECT me.name, books.id, books.source, books.owner, books.title, books.price |
511 | FROM ( |
512 | SELECT me.name, me.id |
513 | FROM ( |
514 | SELECT me.name, me.id |
515 | FROM owners me |
516 | ORDER BY me.id |
517 | FETCH FIRST 4 ROWS ONLY |
518 | ) me |
519 | ORDER BY me.id DESC |
520 | FETCH FIRST 3 ROWS ONLY |
521 | ) me |
522 | LEFT JOIN books books |
523 | ON books.owner = me.id |
66458d5d |
524 | )', |
525 | [], |
526 | ], |
27a7c422 |
527 | }, |
528 | |
529 | Top => { |
530 | limit => [ |
531 | '( |
4b8da207 |
532 | SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
533 | FROM books me |
4b8da207 |
534 | JOIN owners owner |
535 | ON owner.id = me.owner |
27a7c422 |
536 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
537 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
538 | HAVING ? |
539 | )', |
540 | [ |
541 | @select_bind, |
542 | @where_bind, |
543 | @group_bind, |
544 | @having_bind, |
545 | ], |
546 | ], |
547 | limit_offset => [ |
548 | '( |
4b8da207 |
549 | SELECT TOP 4 me.id, owner__id, owner__name, bar, baz |
27a7c422 |
550 | FROM ( |
4b8da207 |
551 | SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
552 | FROM books me |
4b8da207 |
553 | JOIN owners owner |
554 | ON owner.id = me.owner |
27a7c422 |
555 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
556 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
557 | HAVING ? |
558 | ORDER BY me.id |
559 | ) me |
560 | ORDER BY me.id DESC |
561 | )', |
562 | [ |
563 | @select_bind, |
564 | @where_bind, |
565 | @group_bind, |
566 | @having_bind, |
567 | ], |
568 | ], |
569 | ordered_limit => [ |
570 | '( |
4b8da207 |
571 | SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
572 | FROM books me |
4b8da207 |
573 | JOIN owners owner |
574 | ON owner.id = me.owner |
27a7c422 |
575 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
576 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
577 | HAVING ? |
578 | ORDER BY ? / ?, ? |
579 | )', |
580 | [ |
581 | @select_bind, |
582 | @where_bind, |
583 | @group_bind, |
584 | @having_bind, |
585 | @order_bind, |
586 | ], |
587 | ], |
588 | ordered_limit_offset => [ |
589 | '( |
4b8da207 |
590 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
591 | FROM ( |
08a1eaad |
592 | SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002 |
27a7c422 |
593 | FROM ( |
08a1eaad |
594 | SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002 |
27a7c422 |
595 | FROM books me |
4b8da207 |
596 | JOIN owners owner |
597 | ON owner.id = me.owner |
27a7c422 |
598 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
599 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
600 | HAVING ? |
601 | ORDER BY ? / ?, ? |
602 | ) me |
08a1eaad |
603 | ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC |
27a7c422 |
604 | ) me |
08a1eaad |
605 | ORDER BY ORDER__BY__001, ORDER__BY__002 |
27a7c422 |
606 | )', |
607 | [ |
608 | @select_bind, |
609 | @order_bind, |
610 | @where_bind, |
611 | @group_bind, |
612 | @having_bind, |
613 | (map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit |
614 | ], |
615 | ], |
66458d5d |
616 | limit_offset_prefetch => [ |
617 | '( |
618 | SELECT me.name, books.id, books.source, books.owner, books.title, books.price |
619 | FROM ( |
620 | SELECT TOP 3 me.name, me.id |
621 | FROM ( |
622 | SELECT TOP 4 me.name, me.id |
623 | FROM owners me |
624 | ORDER BY me.id |
625 | ) me |
626 | ORDER BY me.id DESC |
627 | ) me |
628 | LEFT JOIN books books |
629 | ON books.owner = me.id |
66458d5d |
630 | )', |
631 | [], |
632 | ], |
27a7c422 |
633 | }, |
634 | |
27a7c422 |
635 | GenericSubQ => { |
636 | limit => [ |
637 | '( |
4b8da207 |
638 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
639 | FROM ( |
4b8da207 |
640 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
641 | FROM books me |
4b8da207 |
642 | JOIN owners owner |
643 | ON owner.id = me.owner |
27a7c422 |
644 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
645 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
646 | HAVING ? |
647 | ) me |
648 | WHERE ( |
649 | SELECT COUNT( * ) |
650 | FROM books rownum__emulation |
651 | WHERE rownum__emulation.id < me.id |
652 | ) < ? |
653 | ORDER BY me.id |
654 | )', |
655 | [ |
656 | @select_bind, |
657 | @where_bind, |
658 | @group_bind, |
659 | @having_bind, |
660 | [ { sqlt_datatype => 'integer' } => 4 ], |
661 | ], |
662 | ], |
663 | limit_offset => [ |
664 | '( |
4b8da207 |
665 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
666 | FROM ( |
4b8da207 |
667 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
668 | FROM books me |
4b8da207 |
669 | JOIN owners owner |
670 | ON owner.id = me.owner |
27a7c422 |
671 | WHERE source != ? AND me.title = ? AND source = ? |
1e4f9fb3 |
672 | GROUP BY AVG(me.id / ?), MAX(owner.id) |
27a7c422 |
673 | HAVING ? |
674 | ) me |
675 | WHERE ( |
676 | SELECT COUNT( * ) |
677 | FROM books rownum__emulation |
678 | WHERE rownum__emulation.id < me.id |
679 | ) BETWEEN ? AND ? |
680 | ORDER BY me.id |
681 | )', |
682 | [ |
683 | @select_bind, |
684 | @where_bind, |
685 | @group_bind, |
686 | @having_bind, |
687 | [ { sqlt_datatype => 'integer' } => 3 ], |
688 | [ { sqlt_datatype => 'integer' } => 6 ], |
689 | ], |
690 | ], |
66458d5d |
691 | limit_offset_prefetch => [ |
692 | '( |
693 | SELECT me.name, books.id, books.source, books.owner, books.title, books.price |
694 | FROM ( |
695 | SELECT me.name, me.id |
696 | FROM ( |
697 | SELECT me.name, me.id FROM owners me |
698 | ) me |
699 | WHERE ( |
700 | SELECT COUNT(*) |
701 | FROM owners rownum__emulation |
702 | WHERE rownum__emulation.id < me.id |
703 | ) BETWEEN ? AND ? |
704 | ORDER BY me.id |
705 | ) me |
706 | LEFT JOIN books books |
707 | ON books.owner = me.id |
0077982b |
708 | ORDER BY me.id |
66458d5d |
709 | )', |
710 | [ |
711 | [ { sqlt_datatype => 'integer' } => 1 ], |
712 | [ { sqlt_datatype => 'integer' } => 3 ], |
713 | ], |
714 | ], |
27a7c422 |
715 | } |
716 | }; |
717 | |
718 | for my $limtype (sort keys %$tests) { |
719 | |
4b8da207 |
720 | Test::Builder->new->is_passing or exit; |
721 | |
27a7c422 |
722 | delete $schema->storage->_sql_maker->{_cached_syntax}; |
723 | $schema->storage->_sql_maker->limit_dialect ($limtype); |
724 | |
725 | # chained search is necessary to exercise the recursive {where} parser |
726 | my $rs = $schema->resultset('BooksInLibrary')->search({ 'me.title' => { '=' => 'kama sutra' } })->search({ source => { '!=', 'Study' } }, { |
4b8da207 |
727 | columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :) |
728 | join => 'owner', # single-rel manual prefetch |
27a7c422 |
729 | rows => 4, |
730 | '+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] }, |
1e4f9fb3 |
731 | group_by => \[ 'AVG(me.id / ?), MAX(owner.id)', [ $attr => 21 ] ], |
27a7c422 |
732 | having => \[ '?', [ $attr => 31 ] ], |
733 | ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ), # needs a simple-column stable order to be happy |
734 | }); |
735 | |
736 | # |
737 | # not all tests run on all dialects (somewhere impossible, somewhere makes no sense) |
738 | # |
739 | |
740 | # only limit, no offset, no order |
741 | is_same_sql_bind( |
742 | $rs->as_query, |
743 | @{$tests->{$limtype}{limit}}, |
744 | "$limtype: Unordered limit with select/group/having", |
745 | ) if $tests->{$limtype}{limit}; |
746 | |
747 | # limit + offset, no order |
748 | is_same_sql_bind( |
749 | $rs->search({}, { offset => 3 })->as_query, |
750 | @{$tests->{$limtype}{limit_offset}}, |
751 | "$limtype: Unordered limit+offset with select/group/having", |
752 | ) if $tests->{$limtype}{limit_offset}; |
753 | |
754 | # order + limit, no offset |
755 | $rs = $rs->search(undef, { |
756 | order_by => [ \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ], |
757 | }); |
758 | |
759 | is_same_sql_bind( |
760 | $rs->as_query, |
761 | @{$tests->{$limtype}{ordered_limit}}, |
762 | "$limtype: Ordered limit with select/group/having", |
763 | ) if $tests->{$limtype}{ordered_limit}; |
764 | |
765 | # order + limit + offset |
766 | is_same_sql_bind( |
767 | $rs->search({}, { offset => 3 })->as_query, |
768 | @{$tests->{$limtype}{ordered_limit_offset}}, |
769 | "$limtype: Ordered limit+offset with select/group/having", |
770 | ) if $tests->{$limtype}{ordered_limit_offset}; |
66458d5d |
771 | |
772 | # complex prefetch on partial-fetch root with limit |
773 | my $pref_rs = $schema->resultset('Owners')->search({}, { |
774 | rows => 3, |
775 | offset => 1, |
776 | columns => 'name', # only the owner name, still prefetch all the books |
777 | prefetch => 'books', |
778 | ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ), # needs a simple-column stable order to be happy |
779 | }); |
780 | |
781 | is_same_sql_bind ( |
782 | $pref_rs->as_query, |
783 | @{$tests->{$limtype}{limit_offset_prefetch}}, |
784 | "$limtype: Prefetch with limit+offset", |
785 | ) if $tests->{$limtype}{limit_offset_prefetch}; |
786 | |
787 | # we can actually run the query |
788 | if ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ') { |
789 | lives_ok { is ($pref_rs->all, 1, 'Expected count of objects on limtied prefetch') } |
790 | "Complex limited prefetch works with supported limit $limtype" |
791 | } |
27a7c422 |
792 | } |
793 | |
794 | done_testing; |