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