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