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