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