Commit | Line | Data |
27a7c422 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | use lib qw(t/lib); |
6 | use DBICTest; |
7 | use DBIC::SqlMakerTest; |
8 | |
9 | my $schema = DBICTest->init_schema; |
10 | |
11 | my $attr = {}; |
12 | my @where_bind = ( |
13 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Study' ], |
14 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.title' } => 'kama sutra' ], |
15 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
16 | ); |
17 | my @select_bind = ( |
18 | [ $attr => 11 ], [ $attr => 12 ], [ $attr => 13 ], |
19 | ); |
20 | my @group_bind = ( |
21 | [ $attr => 21 ], |
22 | ); |
23 | my @having_bind = ( |
24 | [ $attr => 31 ], |
25 | ); |
26 | my @order_bind = ( |
27 | [ $attr => 1 ], [ $attr => 2 ], [ $attr => 3 ], |
28 | ); |
29 | |
30 | my $tests = { |
4b8da207 |
31 | |
27a7c422 |
32 | LimitOffset => { |
33 | ordered_limit_offset => [ |
34 | '( |
4b8da207 |
35 | SELECT me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
36 | FROM books me |
4b8da207 |
37 | JOIN owners owner |
38 | ON owner.id = me.owner |
27a7c422 |
39 | WHERE source != ? AND me.title = ? AND source = ? |
40 | GROUP BY avg(me.id / ?) |
41 | HAVING ? |
42 | ORDER BY ? / ?, ? |
43 | LIMIT ? |
44 | OFFSET ? |
45 | )', |
46 | [ |
47 | @select_bind, |
48 | @where_bind, |
49 | @group_bind, |
50 | @having_bind, |
51 | @order_bind, |
52 | [ { sqlt_datatype => 'integer' } => 4 ], |
53 | [ { sqlt_datatype => 'integer' } => 3 ], |
54 | ], |
55 | ], |
56 | }, |
57 | |
58 | LimitXY => { |
59 | ordered_limit_offset => [ |
60 | '( |
4b8da207 |
61 | SELECT me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
62 | FROM books me |
4b8da207 |
63 | JOIN owners owner |
64 | ON owner.id = me.owner |
27a7c422 |
65 | WHERE source != ? AND me.title = ? AND source = ? |
66 | GROUP BY avg(me.id / ?) |
67 | HAVING ? |
68 | ORDER BY ? / ?, ? |
69 | LIMIT ?, ? |
70 | )', |
71 | [ |
72 | @select_bind, |
73 | @where_bind, |
74 | @group_bind, |
75 | @having_bind, |
76 | @order_bind, |
77 | [ { sqlt_datatype => 'integer' } => 3 ], |
78 | [ { sqlt_datatype => 'integer' } => 4 ], |
79 | ], |
80 | ], |
81 | }, |
82 | |
83 | SkipFirst => { |
84 | ordered_limit_offset => [ |
85 | '( |
4b8da207 |
86 | SELECT SKIP ? FIRST ? me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
87 | FROM books me |
4b8da207 |
88 | JOIN owners owner |
89 | ON owner.id = me.owner |
27a7c422 |
90 | WHERE source != ? AND me.title = ? AND source = ? |
91 | GROUP BY avg(me.id / ?) |
92 | HAVING ? |
93 | ORDER BY ? / ?, ? |
94 | )', |
95 | [ |
96 | [ { sqlt_datatype => 'integer' } => 3 ], |
97 | [ { sqlt_datatype => 'integer' } => 4 ], |
98 | @select_bind, |
99 | @where_bind, |
100 | @group_bind, |
101 | @having_bind, |
102 | @order_bind, |
103 | ], |
104 | ], |
105 | }, |
106 | |
107 | FirstSkip => { |
108 | ordered_limit_offset => [ |
109 | '( |
4b8da207 |
110 | SELECT FIRST ? SKIP ? me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
111 | FROM books me |
4b8da207 |
112 | JOIN owners owner |
113 | ON owner.id = me.owner |
27a7c422 |
114 | WHERE source != ? AND me.title = ? AND source = ? |
115 | GROUP BY avg(me.id / ?) |
116 | HAVING ? |
117 | ORDER BY ? / ?, ? |
118 | )', |
119 | [ |
120 | [ { sqlt_datatype => 'integer' } => 4 ], |
121 | [ { sqlt_datatype => 'integer' } => 3 ], |
122 | @select_bind, |
123 | @where_bind, |
124 | @group_bind, |
125 | @having_bind, |
126 | @order_bind, |
127 | ], |
128 | ], |
129 | }, |
130 | |
131 | RowNumberOver => do { |
132 | my $unordered_sql = '( |
4b8da207 |
133 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
134 | FROM ( |
4b8da207 |
135 | SELECT me.id, owner__id, owner__name, bar, baz, ROW_NUMBER() OVER() AS rno__row__index |
27a7c422 |
136 | FROM ( |
4b8da207 |
137 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
138 | FROM books me |
4b8da207 |
139 | JOIN owners owner |
140 | ON owner.id = me.owner |
27a7c422 |
141 | WHERE source != ? AND me.title = ? AND source = ? |
142 | GROUP BY avg(me.id / ?) |
143 | HAVING ? |
144 | ) me |
145 | ) me |
146 | WHERE rno__row__index >= ? AND rno__row__index <= ? |
147 | )'; |
148 | |
149 | my $ordered_sql = '( |
4b8da207 |
150 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
151 | FROM ( |
08a1eaad |
152 | 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 |
153 | FROM ( |
4b8da207 |
154 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz, |
08a1eaad |
155 | ? / ? AS ORDER__BY__001, ? AS ORDER__BY__002 |
27a7c422 |
156 | FROM books me |
4b8da207 |
157 | JOIN owners owner |
158 | ON owner.id = me.owner |
27a7c422 |
159 | WHERE source != ? AND me.title = ? AND source = ? |
160 | GROUP BY avg(me.id / ?) |
161 | HAVING ? |
162 | ) me |
163 | ) me |
164 | WHERE rno__row__index >= ? AND rno__row__index <= ? |
165 | )'; |
166 | |
167 | { |
168 | limit => [$unordered_sql, |
169 | [ |
170 | @select_bind, |
171 | @where_bind, |
172 | @group_bind, |
173 | @having_bind, |
174 | [ { sqlt_datatype => 'integer' } => 1 ], |
175 | [ { sqlt_datatype => 'integer' } => 4 ], |
176 | ], |
177 | ], |
178 | limit_offset => [$unordered_sql, |
179 | [ |
180 | @select_bind, |
181 | @where_bind, |
182 | @group_bind, |
183 | @having_bind, |
184 | [ { sqlt_datatype => 'integer' } => 4 ], |
185 | [ { sqlt_datatype => 'integer' } => 7 ], |
186 | ], |
187 | ], |
188 | ordered_limit => [$ordered_sql, |
189 | [ |
190 | @select_bind, |
191 | @order_bind, |
192 | @where_bind, |
193 | @group_bind, |
194 | @having_bind, |
195 | [ { sqlt_datatype => 'integer' } => 1 ], |
196 | [ { sqlt_datatype => 'integer' } => 4 ], |
197 | ], |
198 | ], |
199 | ordered_limit_offset => [$ordered_sql, |
200 | [ |
201 | @select_bind, |
202 | @order_bind, |
203 | @where_bind, |
204 | @group_bind, |
205 | @having_bind, |
206 | [ { sqlt_datatype => 'integer' } => 4 ], |
207 | [ { sqlt_datatype => 'integer' } => 7 ], |
208 | ], |
209 | ], |
210 | }; |
211 | }, |
212 | |
213 | RowNum => do { |
214 | my $limit_sql = sub { |
215 | sprintf '( |
4b8da207 |
216 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
217 | FROM ( |
4b8da207 |
218 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
219 | FROM books me |
4b8da207 |
220 | JOIN owners owner |
221 | ON owner.id = me.owner |
27a7c422 |
222 | WHERE source != ? AND me.title = ? AND source = ? |
223 | GROUP BY avg(me.id / ?) |
224 | HAVING ? |
225 | %s |
226 | ) me |
227 | WHERE ROWNUM <= ? |
228 | )', $_[0] || ''; |
229 | }; |
230 | |
231 | { |
232 | limit => [ $limit_sql->(), |
233 | [ |
234 | @select_bind, |
235 | @where_bind, |
236 | @group_bind, |
237 | @having_bind, |
238 | [ { sqlt_datatype => 'integer' } => 4 ], |
239 | ], |
240 | ], |
241 | limit_offset => [ |
242 | '( |
4b8da207 |
243 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
244 | FROM ( |
4b8da207 |
245 | SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index |
27a7c422 |
246 | FROM ( |
4b8da207 |
247 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
248 | FROM books me |
4b8da207 |
249 | JOIN owners owner |
250 | ON owner.id = me.owner |
27a7c422 |
251 | WHERE source != ? AND me.title = ? AND source = ? |
252 | GROUP BY avg(me.id / ?) |
253 | HAVING ? |
254 | ) me |
255 | ) me |
256 | WHERE rownum__index BETWEEN ? AND ? |
257 | )', |
258 | [ |
259 | @select_bind, |
260 | @where_bind, |
261 | @group_bind, |
262 | @having_bind, |
263 | [ { sqlt_datatype => 'integer' } => 4 ], |
264 | [ { sqlt_datatype => 'integer' } => 7 ], |
265 | ], |
266 | ], |
267 | ordered_limit => [ $limit_sql->('ORDER BY ? / ?, ?'), |
268 | [ |
269 | @select_bind, |
270 | @where_bind, |
271 | @group_bind, |
272 | @having_bind, |
273 | @order_bind, |
274 | [ { sqlt_datatype => 'integer' } => 4 ], |
275 | ], |
276 | ], |
277 | ordered_limit_offset => [ |
278 | '( |
4b8da207 |
279 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
280 | FROM ( |
4b8da207 |
281 | SELECT me.id, owner__id, owner__name, bar, baz, ROWNUM rownum__index |
27a7c422 |
282 | FROM ( |
4b8da207 |
283 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
284 | FROM books me |
4b8da207 |
285 | JOIN owners owner |
286 | ON owner.id = me.owner |
27a7c422 |
287 | WHERE source != ? AND me.title = ? AND source = ? |
288 | GROUP BY avg(me.id / ?) |
289 | HAVING ? |
290 | ORDER BY ? / ?, ? |
291 | ) me |
292 | WHERE ROWNUM <= ? |
293 | ) me |
294 | WHERE rownum__index >= ? |
295 | )', |
296 | [ |
297 | @select_bind, |
298 | @where_bind, |
299 | @group_bind, |
300 | @having_bind, |
301 | @order_bind, |
302 | [ { sqlt_datatype => 'integer' } => 7 ], |
303 | [ { sqlt_datatype => 'integer' } => 4 ], |
304 | ], |
305 | ], |
306 | }; |
307 | }, |
308 | |
27a7c422 |
309 | FetchFirst => { |
310 | limit => [ |
311 | '( |
4b8da207 |
312 | SELECT me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
313 | FROM books me |
4b8da207 |
314 | JOIN owners owner |
315 | ON owner.id = me.owner |
27a7c422 |
316 | WHERE source != ? AND me.title = ? AND source = ? |
317 | GROUP BY avg(me.id / ?) |
318 | HAVING ? |
319 | FETCH FIRST 4 ROWS ONLY |
320 | )', |
321 | [ |
322 | @select_bind, |
323 | @where_bind, |
324 | @group_bind, |
325 | @having_bind, |
326 | ], |
327 | ], |
328 | limit_offset => [ |
329 | '( |
4b8da207 |
330 | SELECT me.id, owner__id, owner__name, bar, baz |
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 = ? |
337 | GROUP BY avg(me.id / ?) |
338 | HAVING ? |
339 | ORDER BY me.id |
340 | FETCH FIRST 7 ROWS ONLY |
341 | ) me |
342 | ORDER BY me.id DESC |
343 | FETCH FIRST 4 ROWS ONLY |
344 | )', |
345 | [ |
346 | @select_bind, |
347 | @where_bind, |
348 | @group_bind, |
349 | @having_bind, |
350 | ], |
351 | ], |
352 | ordered_limit => [ |
353 | '( |
4b8da207 |
354 | SELECT me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
355 | FROM books me |
4b8da207 |
356 | JOIN owners owner |
357 | ON owner.id = me.owner |
27a7c422 |
358 | WHERE source != ? AND me.title = ? AND source = ? |
359 | GROUP BY avg(me.id / ?) |
360 | HAVING ? |
361 | ORDER BY ? / ?, ? |
362 | FETCH FIRST 4 ROWS ONLY |
363 | )', |
364 | [ |
365 | @select_bind, |
366 | @where_bind, |
367 | @group_bind, |
368 | @having_bind, |
369 | @order_bind, |
370 | ], |
371 | ], |
372 | ordered_limit_offset => [ |
373 | '( |
4b8da207 |
374 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
375 | FROM ( |
08a1eaad |
376 | SELECT me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002 |
27a7c422 |
377 | FROM ( |
08a1eaad |
378 | 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 |
379 | FROM books me |
4b8da207 |
380 | JOIN owners owner |
381 | ON owner.id = me.owner |
27a7c422 |
382 | WHERE source != ? AND me.title = ? AND source = ? |
383 | GROUP BY avg(me.id / ?) |
384 | HAVING ? |
385 | ORDER BY ? / ?, ? |
386 | FETCH FIRST 7 ROWS ONLY |
387 | ) me |
08a1eaad |
388 | ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC |
27a7c422 |
389 | FETCH FIRST 4 ROWS ONLY |
390 | ) me |
08a1eaad |
391 | ORDER BY ORDER__BY__001, ORDER__BY__002 |
27a7c422 |
392 | )', |
393 | [ |
394 | @select_bind, |
395 | @order_bind, |
396 | @where_bind, |
397 | @group_bind, |
398 | @having_bind, |
399 | (map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit |
400 | ], |
401 | ], |
402 | }, |
403 | |
404 | Top => { |
405 | limit => [ |
406 | '( |
4b8da207 |
407 | SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
408 | FROM books me |
4b8da207 |
409 | JOIN owners owner |
410 | ON owner.id = me.owner |
27a7c422 |
411 | WHERE source != ? AND me.title = ? AND source = ? |
412 | GROUP BY avg(me.id / ?) |
413 | HAVING ? |
414 | )', |
415 | [ |
416 | @select_bind, |
417 | @where_bind, |
418 | @group_bind, |
419 | @having_bind, |
420 | ], |
421 | ], |
422 | limit_offset => [ |
423 | '( |
4b8da207 |
424 | SELECT TOP 4 me.id, owner__id, owner__name, bar, baz |
27a7c422 |
425 | FROM ( |
4b8da207 |
426 | SELECT TOP 7 me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
427 | FROM books me |
4b8da207 |
428 | JOIN owners owner |
429 | ON owner.id = me.owner |
27a7c422 |
430 | WHERE source != ? AND me.title = ? AND source = ? |
431 | GROUP BY avg(me.id / ?) |
432 | HAVING ? |
433 | ORDER BY me.id |
434 | ) me |
435 | ORDER BY me.id DESC |
436 | )', |
437 | [ |
438 | @select_bind, |
439 | @where_bind, |
440 | @group_bind, |
441 | @having_bind, |
442 | ], |
443 | ], |
444 | ordered_limit => [ |
445 | '( |
4b8da207 |
446 | SELECT TOP 4 me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
447 | FROM books me |
4b8da207 |
448 | JOIN owners owner |
449 | ON owner.id = me.owner |
27a7c422 |
450 | WHERE source != ? AND me.title = ? AND source = ? |
451 | GROUP BY avg(me.id / ?) |
452 | HAVING ? |
453 | ORDER BY ? / ?, ? |
454 | )', |
455 | [ |
456 | @select_bind, |
457 | @where_bind, |
458 | @group_bind, |
459 | @having_bind, |
460 | @order_bind, |
461 | ], |
462 | ], |
463 | ordered_limit_offset => [ |
464 | '( |
4b8da207 |
465 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
466 | FROM ( |
08a1eaad |
467 | SELECT TOP 4 me.id, owner__id, owner__name, bar, baz, ORDER__BY__001, ORDER__BY__002 |
27a7c422 |
468 | FROM ( |
08a1eaad |
469 | 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 |
470 | FROM books me |
4b8da207 |
471 | JOIN owners owner |
472 | ON owner.id = me.owner |
27a7c422 |
473 | WHERE source != ? AND me.title = ? AND source = ? |
474 | GROUP BY avg(me.id / ?) |
475 | HAVING ? |
476 | ORDER BY ? / ?, ? |
477 | ) me |
08a1eaad |
478 | ORDER BY ORDER__BY__001 DESC, ORDER__BY__002 DESC |
27a7c422 |
479 | ) me |
08a1eaad |
480 | ORDER BY ORDER__BY__001, ORDER__BY__002 |
27a7c422 |
481 | )', |
482 | [ |
483 | @select_bind, |
484 | @order_bind, |
485 | @where_bind, |
486 | @group_bind, |
487 | @having_bind, |
488 | (map { [ @$_ ] } @order_bind), # without this is_deeply throws a fit |
489 | ], |
490 | ], |
491 | }, |
492 | |
493 | RowCountOrGenericSubQ => { |
494 | limit => [ |
495 | '( |
496 | SET ROWCOUNT 4 |
4b8da207 |
497 | SELECT me.id, owner.id, owner.name, ? * ?, ? |
27a7c422 |
498 | FROM books me |
4b8da207 |
499 | JOIN owners owner |
500 | ON owner.id = me.owner |
27a7c422 |
501 | WHERE source != ? AND me.title = ? AND source = ? |
502 | GROUP BY avg(me.id / ?) |
503 | HAVING ? |
504 | ORDER BY me.id |
505 | SET ROWCOUNT 0 |
506 | )', |
507 | [ |
508 | @select_bind, |
509 | @where_bind, |
510 | @group_bind, |
511 | @having_bind, |
512 | ], |
513 | ], |
514 | limit_offset => [ |
515 | '( |
4b8da207 |
516 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
517 | FROM ( |
4b8da207 |
518 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
519 | FROM books me |
4b8da207 |
520 | JOIN owners owner |
521 | ON owner.id = me.owner |
27a7c422 |
522 | WHERE source != ? AND me.title = ? AND source = ? |
523 | GROUP BY avg( me.id / ? ) |
524 | HAVING ? |
525 | ) me |
526 | WHERE ( |
527 | SELECT COUNT( * ) |
528 | FROM books rownum__emulation |
529 | WHERE rownum__emulation.id < me.id |
530 | ) BETWEEN ? AND ? |
531 | ORDER BY me.id |
532 | )', |
533 | [ |
534 | @select_bind, |
535 | @where_bind, |
536 | @group_bind, |
537 | @having_bind, |
538 | [ { sqlt_datatype => 'integer' } => 3 ], |
539 | [ { sqlt_datatype => 'integer' } => 6 ], |
540 | ], |
541 | ], |
542 | }, |
543 | |
544 | GenericSubQ => { |
545 | limit => [ |
546 | '( |
4b8da207 |
547 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
548 | FROM ( |
4b8da207 |
549 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
550 | FROM books me |
4b8da207 |
551 | JOIN owners owner |
552 | ON owner.id = me.owner |
27a7c422 |
553 | WHERE source != ? AND me.title = ? AND source = ? |
554 | GROUP BY avg( me.id / ? ) |
555 | HAVING ? |
556 | ) me |
557 | WHERE ( |
558 | SELECT COUNT( * ) |
559 | FROM books rownum__emulation |
560 | WHERE rownum__emulation.id < me.id |
561 | ) < ? |
562 | ORDER BY me.id |
563 | )', |
564 | [ |
565 | @select_bind, |
566 | @where_bind, |
567 | @group_bind, |
568 | @having_bind, |
569 | [ { sqlt_datatype => 'integer' } => 4 ], |
570 | ], |
571 | ], |
572 | limit_offset => [ |
573 | '( |
4b8da207 |
574 | SELECT me.id, owner__id, owner__name, bar, baz |
27a7c422 |
575 | FROM ( |
4b8da207 |
576 | SELECT me.id, owner.id AS owner__id, owner.name AS owner__name, ? * ? AS bar, ? AS baz |
27a7c422 |
577 | FROM books me |
4b8da207 |
578 | JOIN owners owner |
579 | ON owner.id = me.owner |
27a7c422 |
580 | WHERE source != ? AND me.title = ? AND source = ? |
581 | GROUP BY avg( me.id / ? ) |
582 | HAVING ? |
583 | ) me |
584 | WHERE ( |
585 | SELECT COUNT( * ) |
586 | FROM books rownum__emulation |
587 | WHERE rownum__emulation.id < me.id |
588 | ) BETWEEN ? AND ? |
589 | ORDER BY me.id |
590 | )', |
591 | [ |
592 | @select_bind, |
593 | @where_bind, |
594 | @group_bind, |
595 | @having_bind, |
596 | [ { sqlt_datatype => 'integer' } => 3 ], |
597 | [ { sqlt_datatype => 'integer' } => 6 ], |
598 | ], |
599 | ], |
600 | } |
601 | }; |
602 | |
603 | for my $limtype (sort keys %$tests) { |
604 | |
4b8da207 |
605 | Test::Builder->new->is_passing or exit; |
606 | |
27a7c422 |
607 | delete $schema->storage->_sql_maker->{_cached_syntax}; |
608 | $schema->storage->_sql_maker->limit_dialect ($limtype); |
609 | |
610 | # chained search is necessary to exercise the recursive {where} parser |
611 | my $rs = $schema->resultset('BooksInLibrary')->search({ 'me.title' => { '=' => 'kama sutra' } })->search({ source => { '!=', 'Study' } }, { |
4b8da207 |
612 | columns => [ { identifier => 'me.id' }, 'owner.id', 'owner.name' ], # people actually do that. BLEH!!! :) |
613 | join => 'owner', # single-rel manual prefetch |
27a7c422 |
614 | rows => 4, |
615 | '+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] }, |
616 | group_by => \[ 'avg(me.id / ?)', [ $attr => 21 ] ], |
617 | having => \[ '?', [ $attr => 31 ] ], |
618 | ($limtype =~ /GenericSubQ/ ? ( order_by => 'me.id' ) : () ), # needs a simple-column stable order to be happy |
619 | }); |
620 | |
621 | # |
622 | # not all tests run on all dialects (somewhere impossible, somewhere makes no sense) |
623 | # |
624 | |
625 | # only limit, no offset, no order |
626 | is_same_sql_bind( |
627 | $rs->as_query, |
628 | @{$tests->{$limtype}{limit}}, |
629 | "$limtype: Unordered limit with select/group/having", |
630 | ) if $tests->{$limtype}{limit}; |
631 | |
632 | # limit + offset, no order |
633 | is_same_sql_bind( |
634 | $rs->search({}, { offset => 3 })->as_query, |
635 | @{$tests->{$limtype}{limit_offset}}, |
636 | "$limtype: Unordered limit+offset with select/group/having", |
637 | ) if $tests->{$limtype}{limit_offset}; |
638 | |
639 | # order + limit, no offset |
640 | $rs = $rs->search(undef, { |
641 | order_by => [ \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ], |
642 | }); |
643 | |
644 | is_same_sql_bind( |
645 | $rs->as_query, |
646 | @{$tests->{$limtype}{ordered_limit}}, |
647 | "$limtype: Ordered limit with select/group/having", |
648 | ) if $tests->{$limtype}{ordered_limit}; |
649 | |
650 | # order + limit + offset |
651 | is_same_sql_bind( |
652 | $rs->search({}, { offset => 3 })->as_query, |
653 | @{$tests->{$limtype}{ordered_limit_offset}}, |
654 | "$limtype: Ordered limit+offset with select/group/having", |
655 | ) if $tests->{$limtype}{ordered_limit_offset}; |
656 | } |
657 | |
658 | done_testing; |