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