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 ? |
27a7c422 |
510 | FETCH FIRST 7 ROWS ONLY |
511 | ) me |
27a7c422 |
512 | FETCH FIRST 4 ROWS ONLY |
513 | )', |
514 | [ |
515 | @select_bind, |
516 | @where_bind, |
517 | @group_bind, |
518 | @having_bind, |
519 | ], |
520 | ], |
521 | ordered_limit => [ |
522 | '( |
4b8da207 |
523 | SELECT me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
524 | FROM books me |
4b8da207 |
525 | JOIN owners owner |
526 | ON owner.id = me.owner |
27a7c422 |
527 | WHERE source != ? AND me.title = ? AND source = ? |
28e58e9b |
528 | GROUP BY (me.id / ?), owner.id |
27a7c422 |
529 | HAVING ? |
530 | ORDER BY ? / ?, ? |
531 | FETCH FIRST 4 ROWS ONLY |
532 | )', |
533 | [ |
534 | @select_bind, |
535 | @where_bind, |
536 | @group_bind, |
537 | @having_bind, |
538 | @order_bind, |
539 | ], |
540 | ], |
541 | ordered_limit_offset => [ |
542 | '( |
4b8da207 |
543 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
544 | FROM ( |
08a1eaad |
545 | SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002 |
27a7c422 |
546 | FROM ( |
08a1eaad |
547 | 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 |
548 | FROM books me |
4b8da207 |
549 | JOIN owners owner |
550 | ON owner.id = me.owner |
27a7c422 |
551 | WHERE source != ? AND me.title = ? AND source = ? |
28e58e9b |
552 | GROUP BY (me.id / ?), owner.id |
27a7c422 |
553 | HAVING ? |
554 | ORDER BY ? / ?, ? |
555 | FETCH FIRST 7 ROWS ONLY |
556 | ) me |
08a1eaad |
557 | ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC |
27a7c422 |
558 | FETCH FIRST 4 ROWS ONLY |
559 | ) me |
08a1eaad |
560 | ORDER BY ORDER__BY__001, ORDER__BY__002 |
27a7c422 |
561 | )', |
562 | [ |
563 | @select_bind, |
564 | @order_bind, |
565 | @where_bind, |
566 | @group_bind, |
567 | @having_bind, |
1b5ddf23 |
568 | @{ dclone \@order_bind }, # without this is_deeply throws a fit |
27a7c422 |
569 | ], |
570 | ], |
66458d5d |
571 | limit_offset_prefetch => [ |
572 | '( |
573 | SELECT me.name, books.id, books.source, books.owner, books.title, books.price |
574 | FROM ( |
575 | SELECT me.name, me.id |
576 | FROM ( |
577 | SELECT me.name, me.id |
578 | FROM owners me |
579 | ORDER BY me.id |
580 | FETCH FIRST 4 ROWS ONLY |
581 | ) me |
582 | ORDER BY me.id DESC |
583 | FETCH FIRST 3 ROWS ONLY |
584 | ) me |
585 | LEFT JOIN books books |
586 | ON books.owner = me.id |
66458d5d |
587 | )', |
588 | [], |
589 | ], |
27a7c422 |
590 | }, |
591 | |
592 | Top => { |
593 | limit => [ |
594 | '( |
4b8da207 |
595 | SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
596 | FROM books me |
4b8da207 |
597 | JOIN owners owner |
598 | ON owner.id = me.owner |
27a7c422 |
599 | WHERE source != ? AND me.title = ? AND source = ? |
28e58e9b |
600 | GROUP BY (me.id / ?), owner.id |
27a7c422 |
601 | HAVING ? |
602 | )', |
603 | [ |
604 | @select_bind, |
605 | @where_bind, |
606 | @group_bind, |
607 | @having_bind, |
608 | ], |
609 | ], |
610 | limit_offset => [ |
611 | '( |
4b8da207 |
612 | SELECT TOP 4 me.id, owner__id, owner__name, bar, baz |
27a7c422 |
613 | FROM ( |
4b8da207 |
614 | SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
615 | FROM books me |
4b8da207 |
616 | JOIN owners owner |
617 | ON owner.id = me.owner |
27a7c422 |
618 | WHERE source != ? AND me.title = ? AND source = ? |
28e58e9b |
619 | GROUP BY (me.id / ?), owner.id |
27a7c422 |
620 | HAVING ? |
621 | ORDER BY me.id |
622 | ) me |
623 | ORDER BY me.id DESC |
624 | )', |
625 | [ |
626 | @select_bind, |
627 | @where_bind, |
628 | @group_bind, |
629 | @having_bind, |
630 | ], |
631 | ], |
632 | ordered_limit => [ |
633 | '( |
4b8da207 |
634 | SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
635 | FROM books me |
4b8da207 |
636 | JOIN owners owner |
637 | ON owner.id = me.owner |
27a7c422 |
638 | WHERE source != ? AND me.title = ? AND source = ? |
28e58e9b |
639 | GROUP BY (me.id / ?), owner.id |
27a7c422 |
640 | HAVING ? |
641 | ORDER BY ? / ?, ? |
642 | )', |
643 | [ |
644 | @select_bind, |
645 | @where_bind, |
646 | @group_bind, |
647 | @having_bind, |
648 | @order_bind, |
649 | ], |
650 | ], |
651 | ordered_limit_offset => [ |
652 | '( |
4b8da207 |
653 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
654 | FROM ( |
08a1eaad |
655 | SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002 |
27a7c422 |
656 | FROM ( |
08a1eaad |
657 | 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 |
658 | FROM books me |
4b8da207 |
659 | JOIN owners owner |
660 | ON owner.id = me.owner |
27a7c422 |
661 | WHERE source != ? AND me.title = ? AND source = ? |
28e58e9b |
662 | GROUP BY (me.id / ?), owner.id |
27a7c422 |
663 | HAVING ? |
664 | ORDER BY ? / ?, ? |
665 | ) me |
08a1eaad |
666 | ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC |
27a7c422 |
667 | ) me |
08a1eaad |
668 | ORDER BY ORDER__BY__001, ORDER__BY__002 |
27a7c422 |
669 | )', |
670 | [ |
671 | @select_bind, |
672 | @order_bind, |
673 | @where_bind, |
674 | @group_bind, |
675 | @having_bind, |
1b5ddf23 |
676 | @{ dclone \@order_bind }, # without this is_deeply throws a fit |
27a7c422 |
677 | ], |
678 | ], |
66458d5d |
679 | limit_offset_prefetch => [ |
680 | '( |
681 | SELECT me.name, books.id, books.source, books.owner, books.title, books.price |
682 | FROM ( |
683 | SELECT TOP 3 me.name, me.id |
684 | FROM ( |
685 | SELECT TOP 4 me.name, me.id |
686 | FROM owners me |
687 | ORDER BY me.id |
688 | ) me |
689 | ORDER BY me.id DESC |
690 | ) me |
691 | LEFT JOIN books books |
692 | ON books.owner = me.id |
66458d5d |
693 | )', |
694 | [], |
695 | ], |
27a7c422 |
696 | }, |
697 | |
27a7c422 |
698 | GenericSubQ => { |
318e3d94 |
699 | ordered_limit => [ |
27a7c422 |
700 | '( |
4b8da207 |
701 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
702 | FROM ( |
318e3d94 |
703 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price |
27a7c422 |
704 | FROM books me |
4b8da207 |
705 | JOIN owners owner |
706 | ON owner.id = me.owner |
27a7c422 |
707 | WHERE source != ? AND me.title = ? AND source = ? |
28e58e9b |
708 | GROUP BY (me.id / ?), owner.id |
27a7c422 |
709 | HAVING ? |
710 | ) me |
711 | WHERE ( |
712 | SELECT COUNT( * ) |
713 | FROM books rownum__emulation |
318e3d94 |
714 | WHERE |
715 | ( me.price IS NULL AND rownum__emulation.price IS NOT NULL ) |
716 | OR |
717 | ( |
718 | rownum__emulation.price > me.price |
719 | AND |
720 | me.price IS NOT NULL |
721 | AND |
722 | rownum__emulation.price IS NOT NULL |
723 | ) |
724 | OR |
725 | ( |
726 | ( |
727 | me.price = rownum__emulation.price |
728 | OR |
729 | ( me.price IS NULL AND rownum__emulation.price IS NULL ) |
730 | ) |
731 | AND |
732 | rownum__emulation.id < me.id |
733 | ) |
734 | ) < ? |
735 | ORDER BY me.price DESC, me.id ASC |
27a7c422 |
736 | )', |
737 | [ |
738 | @select_bind, |
739 | @where_bind, |
740 | @group_bind, |
741 | @having_bind, |
742 | [ { sqlt_datatype => 'integer' } => 4 ], |
743 | ], |
744 | ], |
318e3d94 |
745 | ordered_limit_offset => [ |
27a7c422 |
746 | '( |
4b8da207 |
747 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
748 | FROM ( |
318e3d94 |
749 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, me.price |
27a7c422 |
750 | FROM books me |
4b8da207 |
751 | JOIN owners owner |
752 | ON owner.id = me.owner |
27a7c422 |
753 | WHERE source != ? AND me.title = ? AND source = ? |
28e58e9b |
754 | GROUP BY (me.id / ?), owner.id |
27a7c422 |
755 | HAVING ? |
756 | ) me |
757 | WHERE ( |
758 | SELECT COUNT( * ) |
759 | FROM books rownum__emulation |
318e3d94 |
760 | WHERE |
761 | ( me.price IS NULL AND rownum__emulation.price IS NOT NULL ) |
762 | OR |
763 | ( |
764 | rownum__emulation.price > me.price |
765 | AND |
766 | me.price IS NOT NULL |
767 | AND |
768 | rownum__emulation.price IS NOT NULL |
769 | ) |
770 | OR |
771 | ( |
772 | ( |
773 | me.price = rownum__emulation.price |
774 | OR |
775 | ( me.price IS NULL AND rownum__emulation.price IS NULL ) |
776 | ) |
777 | AND |
778 | rownum__emulation.id < me.id |
779 | ) |
780 | ) BETWEEN ? AND ? |
781 | ORDER BY me.price DESC, me.id ASC |
27a7c422 |
782 | )', |
783 | [ |
784 | @select_bind, |
785 | @where_bind, |
786 | @group_bind, |
787 | @having_bind, |
788 | [ { sqlt_datatype => 'integer' } => 3 ], |
789 | [ { sqlt_datatype => 'integer' } => 6 ], |
790 | ], |
791 | ], |
66458d5d |
792 | limit_offset_prefetch => [ |
793 | '( |
794 | SELECT me.name, books.id, books.source, books.owner, books.title, books.price |
795 | FROM ( |
796 | SELECT me.name, me.id |
797 | FROM ( |
318e3d94 |
798 | SELECT me.name, me.id |
799 | FROM owners me |
66458d5d |
800 | ) me |
318e3d94 |
801 | WHERE |
802 | ( |
803 | SELECT COUNT(*) |
804 | FROM owners rownum__emulation |
805 | WHERE ( |
806 | rownum__emulation.name < me.name |
807 | OR |
808 | ( |
809 | me.name = rownum__emulation.name |
810 | AND |
811 | rownum__emulation.id > me.id |
812 | ) |
813 | ) |
814 | ) BETWEEN ? AND ? |
815 | ORDER BY me.name ASC, me.id DESC |
66458d5d |
816 | ) me |
817 | LEFT JOIN books books |
818 | ON books.owner = me.id |
318e3d94 |
819 | ORDER BY me.name ASC, me.id DESC |
66458d5d |
820 | )', |
821 | [ |
822 | [ { sqlt_datatype => 'integer' } => 1 ], |
823 | [ { sqlt_datatype => 'integer' } => 3 ], |
824 | ], |
825 | ], |
27a7c422 |
826 | } |
827 | }; |
828 | |
829 | for my $limtype (sort keys %$tests) { |
830 | |
4b8da207 |
831 | Test::Builder->new->is_passing or exit; |
832 | |
27a7c422 |
833 | delete $schema->storage->_sql_maker->{_cached_syntax}; |
834 | $schema->storage->_sql_maker->limit_dialect ($limtype); |
835 | |
28e58e9b |
836 | my $can_run = ($limtype eq $native_limit_dialect or $limtype eq 'GenericSubQ'); |
837 | |
27a7c422 |
838 | # chained search is necessary to exercise the recursive {where} parser |
1b5ddf23 |
839 | my $rs = $schema->resultset('BooksInLibrary')->search( |
840 | { 'me.title' => { '=' => \[ '?', 'kama sutra' ] } } |
841 | )->search( |
842 | { source => { '!=', \[ '?', [ {} => 'Study' ] ] } }, |
843 | { |
844 | columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :) |
845 | join => 'owner', # single-rel manual prefetch |
846 | rows => 4, |
847 | '+columns' => { bar => \['? * ?', [ \ 'numeric' => 11 ], 12 ], baz => \[ '?', [ 'me.id' => 13 ] ] }, |
848 | group_by => \[ '(me.id / ?), owner.id', 21 ], |
849 | having => \[ '?', 31 ], |
850 | } |
851 | ); |
27a7c422 |
852 | |
853 | # |
854 | # not all tests run on all dialects (somewhere impossible, somewhere makes no sense) |
855 | # |
856 | |
857 | # only limit, no offset, no order |
28e58e9b |
858 | if ($tests->{$limtype}{limit}) { |
1b5ddf23 |
859 | lives_ok { |
860 | is_same_sql_bind( |
861 | $rs->as_query, |
862 | @{$tests->{$limtype}{limit}}, |
863 | "$limtype: Unordered limit with select/group/having", |
864 | ); |
865 | |
866 | $rs->all if $can_run; |
867 | } "Grouped limit under $limtype"; |
28e58e9b |
868 | } |
27a7c422 |
869 | |
870 | # limit + offset, no order |
28e58e9b |
871 | if ($tests->{$limtype}{limit_offset}) { |
1b5ddf23 |
872 | |
873 | lives_ok { |
874 | my $subrs = $rs->search({}, { offset => 3 }); |
875 | |
876 | is_same_sql_bind( |
877 | $subrs->as_query, |
878 | @{$tests->{$limtype}{limit_offset}}, |
879 | "$limtype: Unordered limit+offset with select/group/having", |
880 | ); |
881 | |
882 | $subrs->all if $can_run; |
883 | } "Grouped limit+offset runs under $limtype"; |
28e58e9b |
884 | } |
27a7c422 |
885 | |
886 | # order + limit, no offset |
887 | $rs = $rs->search(undef, { |
318e3d94 |
888 | order_by => ( $limtype =~ /GenericSubQ/ |
1b5ddf23 |
889 | ? [ { -desc => 'price' }, 'me.id', \[ 'owner.name + ?', 'bah' ] ] # needs a same-table stable order to be happy |
890 | : [ \['? / ?', [ \ 'int' => 1 ], [ name => 2 ]], \[ '?', 3 ] ] |
318e3d94 |
891 | ), |
27a7c422 |
892 | }); |
893 | |
28e58e9b |
894 | if ($tests->{$limtype}{ordered_limit}) { |
28e58e9b |
895 | |
1b5ddf23 |
896 | lives_ok { |
897 | is_same_sql_bind( |
898 | $rs->as_query, |
899 | @{$tests->{$limtype}{ordered_limit}}, |
900 | "$limtype: Ordered limit with select/group/having", |
901 | ); |
902 | |
903 | $rs->all if $can_run; |
904 | } "Grouped ordered limit runs under $limtype" |
28e58e9b |
905 | } |
27a7c422 |
906 | |
907 | # order + limit + offset |
28e58e9b |
908 | if ($tests->{$limtype}{ordered_limit_offset}) { |
1b5ddf23 |
909 | lives_ok { |
910 | my $subrs = $rs->search({}, { offset => 3 }); |
911 | |
912 | is_same_sql_bind( |
913 | $subrs->as_query, |
914 | @{$tests->{$limtype}{ordered_limit_offset}}, |
915 | "$limtype: Ordered limit+offset with select/group/having", |
916 | ); |
917 | |
918 | $subrs->all if $can_run; |
919 | } "Grouped ordered limit+offset runs under $limtype"; |
28e58e9b |
920 | } |
66458d5d |
921 | |
922 | # complex prefetch on partial-fetch root with limit |
923 | my $pref_rs = $schema->resultset('Owners')->search({}, { |
924 | rows => 3, |
925 | offset => 1, |
926 | columns => 'name', # only the owner name, still prefetch all the books |
927 | prefetch => 'books', |
318e3d94 |
928 | ($limtype !~ /GenericSubQ/ ? () : ( |
929 | # needs a same-table stable order to be happy |
1b5ddf23 |
930 | order_by => [ { -asc => 'me.name' }, \ 'me.id DESC' ] |
318e3d94 |
931 | )), |
66458d5d |
932 | }); |
933 | |
1b5ddf23 |
934 | lives_ok { |
935 | is_same_sql_bind ( |
936 | $pref_rs->as_query, |
937 | @{$tests->{$limtype}{limit_offset_prefetch}}, |
938 | "$limtype: Prefetch with limit+offset", |
939 | ) if $tests->{$limtype}{limit_offset_prefetch}; |
66458d5d |
940 | |
1b5ddf23 |
941 | is ($pref_rs->all, 1, 'Expected count of objects on limited prefetch') |
942 | if $can_run; |
943 | } "Complex limited prefetch runs under $limtype"; |
27a7c422 |
944 | } |
945 | |
946 | done_testing; |