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