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