t/sqlmaker/limit_dialects/torture.t

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