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