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