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