1#!/usr/bin/perl 2 3use strict; 4use warnings; 5use List::Util qw(sum); 6 7use Test::More; 8 9# equivalent to $Module::Install::AUTHOR 10my $author = ( 11 ( not -d './inc' ) 12 or 13 ( -e ($^O eq 'VMS' ? './inc/_author' : './inc/.author') ) 14); 15 16if (not $author and not $ENV{SQLATEST_TESTER} and not $ENV{AUTOMATED_TESTING}) { 17 plan skip_all => 'Skipping resource intensive self-tests, use SQLATEST_TESTER=1 to run'; 18} 19 20 21my @sql_tests = ( 22 # WHERE condition - equal 23 { 24 equal => 1, 25 statements => [ 26 q/SELECT foo FROM bar WHERE a = 1/, 27 q/SELECT foo FROM bar WHERE a=1/, 28 q/SELECT foo FROM bar WHERE (a = 1)/, 29 q/SELECT foo FROM bar WHERE (a=1)/, 30 q/SELECT foo FROM bar WHERE ( a = 1 )/, 31 q/ 32 SELECT 33 foo 34 FROM 35 bar 36 WHERE 37 a = 1 38 /, 39 q/ 40 SELECT 41 foo 42 FROM 43 bar 44 WHERE 45 (a = 1) 46 /, 47 q/ 48 SELECT 49 foo 50 FROM 51 bar 52 WHERE 53 ( a = 1 ) 54 /, 55 q/SELECT foo FROM bar WHERE ((a = 1))/, 56 q/SELECT foo FROM bar WHERE ( (a = 1) )/, 57 q/SELECT foo FROM bar WHERE ( ( a = 1 ) )/, 58 ] 59 }, 60 { 61 equal => 1, 62 statements => [ 63 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/, 64 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1)/, 65 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 1))/, 66 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1)/, 67 q/SELECT foo FROM bar WHERE ((a = 1 AND b = 1))/, 68 q/SELECT foo FROM bar WHERE (((a = 1) AND (b = 1)))/, 69 q/ 70 SELECT 71 foo 72 FROM 73 bar 74 WHERE 75 a = 1 76 AND 77 b = 1 78 /, 79 q/ 80 SELECT 81 foo 82 FROM 83 bar 84 WHERE 85 (a = 1 86 AND 87 b = 1) 88 /, 89 q/ 90 SELECT 91 foo 92 FROM 93 bar 94 WHERE 95 (a = 1) 96 AND 97 (b = 1) 98 /, 99 q/ 100 SELECT 101 foo 102 FROM 103 bar 104 WHERE 105 ((a = 1) 106 AND 107 (b = 1)) 108 /, 109 ] 110 }, 111 { 112 equal => 1, 113 statements => [ 114 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/, 115 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/, 116 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/, 117 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/, 118 q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/, 119 ] 120 }, 121 { 122 equal => 1, 123 statements => [ 124 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/, 125 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/, 126 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/, 127 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/, 128 ] 129 }, 130 { 131 equal => 1, 132 statements => [ 133 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/, 134 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/, 135 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /, 136 ] 137 }, 138 { 139 equal => 1, 140 statements => [ 141 q/SELECT foo FROM bar WHERE (a) AND (b = 2)/, 142 q/SELECT foo FROM bar WHERE (a AND b = 2)/, 143 q/SELECT foo FROM bar WHERE (a AND (b = 2))/, 144 q/SELECT foo FROM bar WHERE a AND (b = 2)/, 145 ] 146 }, 147 { 148 equal => 1, 149 statements => [ 150 q/SELECT foo FROM bar WHERE ((NOT a) AND b = 2)/, 151 q/SELECT foo FROM bar WHERE (NOT a) AND (b = 2)/, 152 q/SELECT foo FROM bar WHERE (NOT (a)) AND b = 2/, 153 ], 154 }, 155 { 156 equal => 0, 157 statements => [ 158 q/SELECT foo FROM bar WHERE NOT a AND (b = 2)/, 159 q/SELECT foo FROM bar WHERE (NOT a) AND (b = 2)/, 160 ] 161 }, 162 { 163 equal => 0, 164 parenthesis_significant => 1, 165 statements => [ 166 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/, 167 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/, 168 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) AND c = 1/, 169 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 AND c = 1)/, 170 q/SELECT foo FROM bar WHERE ((((a = 1))) AND (b = 1 AND c = 1))/, 171 ] 172 }, 173 { 174 equal => 0, 175 parenthesis_significant => 1, 176 statements => [ 177 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/, 178 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/, 179 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 OR c = 1)/, 180 q/SELECT foo FROM bar WHERE a = 1 OR ((b = 1 OR (c = 1)))/, 181 ] 182 }, 183 { 184 equal => 0, 185 parenthesis_significant => 1, 186 statements => [ 187 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/, 188 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/, 189 q/SELECT foo FROM bar WHERE ( ((a = 1) AND ( b = 1 OR (c = 1 OR d = 1) )) AND ((e = 1)) AND f = 1) /, 190 ] 191 }, 192 193 # WHERE condition - different 194 { 195 equal => 0, 196 statements => [ 197 q/SELECT foo FROM bar WHERE a = 1/, 198 q/SELECT quux FROM bar WHERE a = 1/, 199 q/SELECT foo FROM quux WHERE a = 1/, 200 q/FOOBAR foo FROM bar WHERE a = 1/, 201 202 q/SELECT foo FROM bar WHERE a = 2/, 203 q/SELECT foo FROM bar WHERE a < 1/, 204 q/SELECT foo FROM bar WHERE b = 1/, 205 q/SELECT foo FROM bar WHERE (c = 1)/, 206 q/SELECT foo FROM bar WHERE (d = 1)/, 207 208 q/SELECT foo FROM bar WHERE a = 1 AND quux/, 209 q/SELECT foo FROM bar WHERE a = 1 GROUP BY foo/, 210 q/SELECT foo FROM bar WHERE a = 1 ORDER BY foo/, 211 q/SELECT foo FROM bar WHERE a = 1 LIMIT 1/, 212 q/SELECT foo FROM bar WHERE a = 1 OFFSET 1/, 213 q/SELECT foo FROM bar JOIN quux WHERE a = 1/, 214 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1/, 215 ] 216 }, 217 { 218 equal => 0, 219 statements => [ 220 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/, 221 q/SELECT quux FROM bar WHERE a = 1 AND b = 1/, 222 q/SELECT foo FROM quux WHERE a = 1 AND b = 1/, 223 q/FOOBAR foo FROM bar WHERE a = 1 AND b = 1/, 224 225 q/SELECT foo FROM bar WHERE a = 2 AND b = 1/, 226 q/SELECT foo FROM bar WHERE a = 3 AND (b = 1)/, 227 q/SELECT foo FROM bar WHERE (a = 4) AND b = 1/, 228 q/SELECT foo FROM bar WHERE (a = 5) AND (b = 1)/, 229 q/SELECT foo FROM bar WHERE ((a = 6) AND (b = 1))/, 230 q/SELECT foo FROM bar WHERE ((a = 7) AND (b = 1))/, 231 232 q/SELECT foo FROM bar WHERE a = 1 AND b = 2/, 233 q/SELECT foo FROM bar WHERE a = 1 AND (b = 3)/, 234 q/SELECT foo FROM bar WHERE (a = 1) AND b = 4/, 235 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 5)/, 236 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 6))/, 237 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 7))/, 238 239 q/SELECT foo FROM bar WHERE a < 1 AND b = 1/, 240 q/SELECT foo FROM bar WHERE b = 1 AND b = 1/, 241 q/SELECT foo FROM bar WHERE (c = 1) AND b = 1/, 242 q/SELECT foo FROM bar WHERE (d = 1) AND b = 1/, 243 244 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND quux/, 245 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 GROUP BY foo/, 246 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 ORDER BY foo/, 247 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 LIMIT 1/, 248 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OFFSET 1/, 249 q/SELECT foo FROM bar JOIN quux WHERE a = 1 AND b = 1/, 250 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1 AND b = 1/, 251 ] 252 }, 253 { 254 equal => 0, 255 statements => [ 256 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OR c = 1/, 257 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1) OR c = 1/, 258 q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1)/, 259 ] 260 }, 261 { 262 equal => 0, 263 statements => [ 264 q/SELECT foo FROM bar WHERE a = 1 OR b = 1 AND c = 1/, 265 q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) AND c = 1/, 266 q/SELECT foo FROM bar WHERE a = 1 OR (b = 1 AND c = 1)/, 267 ] 268 }, 269 { 270 equal => 0, 271 parenthesis_significant => 1, 272 statements => [ 273 q/SELECT foo FROM bar WHERE a IN (1,2,3)/, 274 q/SELECT foo FROM bar WHERE a IN (1,3,2)/, 275 q/SELECT foo FROM bar WHERE a IN ((1,2,3))/, 276 ] 277 }, 278 { 279 equal => 0, 280 statements => [ 281 # BETWEEN with/without parenthesis around itself/RHS is a sticky business 282 # if I made a mistake here, simply rewrite the special BETWEEN handling in 283 # _recurse_parse() 284 # 285 # by RIBASUSHI 286 q/SELECT foo FROM bar WHERE ( completion_date BETWEEN ? AND ? AND status = ? )/, 287 q/SELECT foo FROM bar WHERE completion_date BETWEEN (? AND ?) AND status = ?/, 288 q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ?) ) AND status = ? )/, 289 q/SELECT foo FROM bar WHERE ( (completion_date BETWEEN (? AND ? AND status = ?) ) )/, 290 ] 291 }, 292 293 # JOIN condition - equal 294 { 295 equal => 1, 296 statements => [ 297 q/SELECT foo FROM bar JOIN baz ON a = 1 WHERE x = 1/, 298 q/SELECT foo FROM bar JOIN baz ON a=1 WHERE x = 1/, 299 q/SELECT foo FROM bar JOIN baz ON (a = 1) WHERE x = 1/, 300 q/SELECT foo FROM bar JOIN baz ON (a=1) WHERE x = 1/, 301 q/SELECT foo FROM bar JOIN baz ON ( a = 1 ) WHERE x = 1/, 302 q/ 303 SELECT 304 foo 305 FROM 306 bar 307 JOIN 308 baz 309 ON 310 a = 1 311 WHERE 312 x = 1 313 /, 314 q/ 315 SELECT 316 foo 317 FROM 318 bar 319 JOIN 320 baz 321 ON 322 (a = 1) 323 WHERE 324 x = 1 325 /, 326 q/ 327 SELECT 328 foo 329 FROM 330 bar 331 JOIN 332 baz 333 ON 334 ( a = 1 ) 335 WHERE 336 x = 1 337 /, 338 q/SELECT foo FROM bar JOIN baz ON ((a = 1)) WHERE x = 1/, 339 q/SELECT foo FROM bar JOIN baz ON ( (a = 1) ) WHERE x = 1/, 340 q/SELECT foo FROM bar JOIN baz ON ( ( a = 1 ) ) WHERE x = 1/, 341 ] 342 }, 343 { 344 equal => 1, 345 statements => [ 346 q/SELECT foo FROM bar JOIN baz ON a = 1 AND b = 1 WHERE x = 1/, 347 q/SELECT foo FROM bar JOIN baz ON (a = 1) AND (b = 1) WHERE x = 1/, 348 q/SELECT foo FROM bar JOIN baz ON ((a = 1) AND (b = 1)) WHERE x = 1/, 349 q/SELECT foo FROM bar JOIN baz ON (a = 1 AND b = 1) WHERE x = 1/, 350 q/SELECT foo FROM bar JOIN baz ON ((a = 1 AND b = 1)) WHERE x = 1/, 351 q/SELECT foo FROM bar JOIN baz ON (((a = 1) AND (b = 1))) WHERE x = 1/, 352 q/ 353 SELECT 354 foo 355 FROM 356 bar 357 JOIN 358 baz 359 ON 360 a = 1 361 AND 362 b = 1 363 WHERE 364 x = 1 365 /, 366 q/ 367 SELECT 368 foo 369 FROM 370 bar 371 JOIN 372 baz 373 ON 374 (a = 1 375 AND 376 b = 1) 377 WHERE 378 x = 1 379 /, 380 q/ 381 SELECT 382 foo 383 FROM 384 bar 385 JOIN 386 baz 387 ON 388 (a = 1) 389 AND 390 (b = 1) 391 WHERE 392 x = 1 393 /, 394 q/ 395 SELECT 396 foo 397 FROM 398 bar 399 JOIN 400 baz 401 ON 402 ((a = 1) 403 AND 404 (b = 1)) 405 WHERE 406 x = 1 407 /, 408 ] 409 }, 410 411 # JOIN condition - different 412 { 413 equal => 0, 414 statements => [ 415 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE quuux/, 416 q/SELECT quux FROM bar JOIN quux ON a = 1 WHERE quuux/, 417 q/SELECT foo FROM quux JOIN quux ON a = 1 WHERE quuux/, 418 q/FOOBAR foo FROM bar JOIN quux ON a = 1 WHERE quuux/, 419 420 q/SELECT foo FROM bar JOIN quux ON a = 2 WHERE quuux/, 421 q/SELECT foo FROM bar JOIN quux ON a < 1 WHERE quuux/, 422 q/SELECT foo FROM bar JOIN quux ON b = 1 WHERE quuux/, 423 q/SELECT foo FROM bar JOIN quux ON (c = 1) WHERE quuux/, 424 q/SELECT foo FROM bar JOIN quux ON (d = 1) WHERE quuux/, 425 426 q/SELECT foo FROM bar JOIN quux ON a = 1 AND quuux/, 427 q/SELECT foo FROM bar JOIN quux ON a = 1 GROUP BY foo/, 428 q/SELECT foo FROM bar JOIN quux ON a = 1 ORDER BY foo/, 429 q/SELECT foo FROM bar JOIN quux ON a = 1 LIMIT 1/, 430 q/SELECT foo FROM bar JOIN quux ON a = 1 OFFSET 1/, 431 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux/, 432 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1/, 433 ] 434 }, 435 { 436 equal => 0, 437 statements => [ 438 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/, 439 q/SELECT quux FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/, 440 q/SELECT foo FROM quux JOIN quux ON a = 1 AND b = 1 WHERE quuux/, 441 q/FOOBAR foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/, 442 443 q/SELECT foo FROM bar JOIN quux ON a = 2 AND b = 1 WHERE quuux/, 444 q/SELECT foo FROM bar JOIN quux ON a = 3 AND (b = 1) WHERE quuux/, 445 q/SELECT foo FROM bar JOIN quux ON (a = 4) AND b = 1 WHERE quuux/, 446 q/SELECT foo FROM bar JOIN quux ON (a = 5) AND (b = 1) WHERE quuux/, 447 q/SELECT foo FROM bar JOIN quux ON ((a = 6) AND (b = 1)) WHERE quuux/, 448 q/SELECT foo FROM bar JOIN quux ON ((a = 7) AND (b = 1)) WHERE quuux/, 449 450 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 2 WHERE quuux/, 451 q/SELECT foo FROM bar JOIN quux ON a = 1 AND (b = 3) WHERE quuux/, 452 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND b = 4 WHERE quuux/, 453 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND (b = 5) WHERE quuux/, 454 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 6)) WHERE quuux/, 455 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 7)) WHERE quuux/, 456 457 q/SELECT foo FROM bar JOIN quux ON a < 1 AND b = 1 WHERE quuux/, 458 q/SELECT foo FROM bar JOIN quux ON b = 1 AND b = 1 WHERE quuux/, 459 q/SELECT foo FROM bar JOIN quux ON (c = 1) AND b = 1 WHERE quuux/, 460 q/SELECT foo FROM bar JOIN quux ON (d = 1) AND b = 1 WHERE quuux/, 461 462 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 AND quuux/, 463 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 GROUP BY foo/, 464 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 ORDER BY foo/, 465 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 LIMIT 1/, 466 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 OFFSET 1/, 467 q/SELECT foo FROM bar JOIN quux JOIN quuux ON a = 1 AND b = 1/, 468 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1 AND b = 1/, 469 ] 470 }, 471 472 # DISTINCT ON (...) not confused with JOIN ON (...) 473 { 474 equal => 1, 475 statements => [ 476 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a = 1/, 477 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a=1/, 478 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a = 1)/, 479 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a=1)/, 480 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( a = 1 )/, 481 q/ 482 SELECT DISTINCT ON (foo, quux) 483 foo, 484 quux 485 FROM 486 bar 487 WHERE 488 a = 1 489 /, 490 q/ 491 SELECT DISTINCT ON (foo, quux) 492 foo, 493 quux 494 FROM 495 bar 496 WHERE 497 (a = 1) 498 /, 499 q/ 500 SELECT DISTINCT ON (foo, quux) 501 foo, 502 quux 503 FROM 504 bar 505 WHERE 506 ( a = 1 ) 507 /, 508 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ((a = 1))/, 509 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( (a = 1) )/, 510 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( ( a = 1 ) )/, 511 ] 512 }, 513 514 # subselects - equal 515 { 516 equal => 1, 517 statements => [ 518 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/, 519 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 1)/, 520 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 1/, 521 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 1)/, 522 ] 523 }, 524 { 525 equal => 1, 526 statements => [ 527 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/, 528 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE a = 1/, 529 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE a = 1/, 530 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE a = 1/, 531 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE a = 1/, 532 533 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE (a = 1)/, 534 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE (a = 1)/, 535 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE (a = 1)/, 536 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE (a = 1)/, 537 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE (a = 1)/, 538 ] 539 }, 540 541 # subselects - different 542 { 543 equal => 0, 544 statements => [ 545 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/, 546 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/, 547 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/, 548 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/, 549 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/, 550 q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/, 551 q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/, 552 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/, 553 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/, 554 ] 555 }, 556 { 557 equal => 0, 558 statements => [ 559 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/, 560 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/, 561 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/, 562 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/, 563 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/, 564 565 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/, 566 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/, 567 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/, 568 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/, 569 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/, 570 571 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/, 572 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/, 573 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/, 574 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/, 575 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/, 576 577 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/, 578 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/, 579 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/, 580 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/, 581 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/, 582 ] 583 }, 584 { 585 equal => 0, 586 statements => [ 587 'SELECT a,b,c FROM foo', 588 'SELECT a,c,b FROM foo', 589 'SELECT b,a,c FROM foo', 590 'SELECT b,c,a FROM foo', 591 'SELECT c,a,b FROM foo', 592 'SELECT c,b,a FROM foo', 593 ], 594 }, 595 { 596 equal => 0, 597 statements => [ 598 'SELECT * FROM foo WHERE a IN (1,2,3)', 599 'SELECT * FROM foo WHERE a IN (1,3,2)', 600 'SELECT * FROM foo WHERE a IN (2,1,3)', 601 'SELECT * FROM foo WHERE a IN (2,3,1)', 602 'SELECT * FROM foo WHERE a IN (3,1,2)', 603 'SELECT * FROM foo WHERE a IN (3,2,1)', 604 ] 605 }, 606 { 607 equal => 0, 608 statements => [ 609 'SELECT count(*) FROM foo', 610 'SELECT count(*) AS bar FROM foo', 611 'SELECT count(*) AS "bar" FROM foo', 612 'SELECT count(a) FROM foo', 613 'SELECT count(1) FROM foo', 614 ] 615 }, 616); 617 618my @bind_tests = ( 619 # scalar - equal 620 { 621 equal => 1, 622 bindvals => [ 623 undef, 624 undef, 625 ] 626 }, 627 { 628 equal => 1, 629 bindvals => [ 630 'foo', 631 'foo', 632 ] 633 }, 634 { 635 equal => 1, 636 bindvals => [ 637 42, 638 42, 639 '42', 640 ] 641 }, 642 643 # scalarref - equal 644 { 645 equal => 1, 646 bindvals => [ 647 \'foo', 648 \'foo', 649 ] 650 }, 651 { 652 equal => 1, 653 bindvals => [ 654 \42, 655 \42, 656 \'42', 657 ] 658 }, 659 660 # arrayref - equal 661 { 662 equal => 1, 663 bindvals => [ 664 [], 665 [] 666 ] 667 }, 668 { 669 equal => 1, 670 bindvals => [ 671 [42], 672 [42], 673 ['42'], 674 ] 675 }, 676 { 677 equal => 1, 678 bindvals => [ 679 [1, 42], 680 [1, 42], 681 ['1', 42], 682 [1, '42'], 683 ['1', '42'], 684 ] 685 }, 686 687 # hashref - equal 688 { 689 equal => 1, 690 bindvals => [ 691 { foo => 42 }, 692 { foo => 42 }, 693 { foo => '42' }, 694 ] 695 }, 696 { 697 equal => 1, 698 bindvals => [ 699 { foo => 42, bar => 1 }, 700 { foo => 42, bar => 1 }, 701 { foo => '42', bar => 1 }, 702 ] 703 }, 704 705 # blessed object - equal 706 { 707 equal => 1, 708 bindvals => [ 709 bless(\(local $_ = 42), 'Life::Universe::Everything'), 710 bless(\(local $_ = 42), 'Life::Universe::Everything'), 711 ] 712 }, 713 { 714 equal => 1, 715 bindvals => [ 716 bless([42], 'Life::Universe::Everything'), 717 bless([42], 'Life::Universe::Everything'), 718 ] 719 }, 720 { 721 equal => 1, 722 bindvals => [ 723 bless({ answer => 42 }, 'Life::Universe::Everything'), 724 bless({ answer => 42 }, 'Life::Universe::Everything'), 725 ] 726 }, 727 728 # complex data structure - equal 729 { 730 equal => 1, 731 bindvals => [ 732 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], 733 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], 734 ] 735 }, 736 737 738 # scalar - different 739 { 740 equal => 0, 741 bindvals => [ 742 undef, 743 'foo', 744 42, 745 ] 746 }, 747 748 # scalarref - different 749 { 750 equal => 0, 751 bindvals => [ 752 \undef, 753 \'foo', 754 \42, 755 ] 756 }, 757 758 # arrayref - different 759 { 760 equal => 0, 761 bindvals => [ 762 [undef], 763 ['foo'], 764 [42], 765 ] 766 }, 767 768 # hashref - different 769 { 770 equal => 0, 771 bindvals => [ 772 { foo => undef }, 773 { foo => 'bar' }, 774 { foo => 42 }, 775 ] 776 }, 777 778 # different types 779 { 780 equal => 0, 781 bindvals => [ 782 'foo', 783 \'foo', 784 ['foo'], 785 { foo => 'bar' }, 786 ] 787 }, 788 789 # complex data structure - different 790 { 791 equal => 0, 792 bindvals => [ 793 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], 794 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], 795 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], 796 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], 797 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ], 798 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ], 799 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ], 800 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ], 801 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ], 802 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ], 803 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ], 804 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ], 805 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ], 806 ] 807 }, 808); 809 810plan tests => 1 + 811 sum( 812 map { $_ * ($_ - 1) / 2 } 813 map { scalar @{$_->{statements}} } 814 @sql_tests 815 ) + 816 sum( 817 map { $_ * ($_ - 1) / 2 } 818 map { scalar @{$_->{bindvals}} } 819 @bind_tests 820 ) + 821 3; 822 823use_ok('SQL::Abstract::Test', import => [qw( 824 eq_sql_bind eq_sql eq_bind is_same_sql_bind 825)]); 826 827for my $test (@sql_tests) { 828 my $statements = $test->{statements}; 829 while (@$statements) { 830 my $sql1 = shift @$statements; 831 foreach my $sql2 (@$statements) { 832 833 no warnings qw/once/; # perl 5.10 is dumb 834 local $SQL::Abstract::Test::parenthesis_significant = $test->{parenthesis_significant} 835 if $test->{parenthesis_significant}; 836 my $equal = eq_sql($sql1, $sql2); 837 838 TODO: { 839 local $TODO = $test->{todo} if $test->{todo}; 840 841 if ($test->{equal}) { 842 ok($equal, "equal SQL expressions should have been considered equal"); 843 } else { 844 ok(!$equal, "different SQL expressions should have been considered not equal"); 845 } 846 847 if ($equal ^ $test->{equal}) { 848 diag("sql1: $sql1"); 849 diag("sql2: $sql2"); 850 } 851 } 852 } 853 } 854} 855 856for my $test (@bind_tests) { 857 my $bindvals = $test->{bindvals}; 858 while (@$bindvals) { 859 my $bind1 = shift @$bindvals; 860 foreach my $bind2 (@$bindvals) { 861 my $equal = eq_bind($bind1, $bind2); 862 if ($test->{equal}) { 863 ok($equal, "equal bind values considered equal"); 864 } else { 865 ok(!$equal, "different bind values considered not equal"); 866 } 867 868 if ($equal ^ $test->{equal}) { 869 diag("bind1: " . Dumper($bind1)); 870 diag("bind2: " . Dumper($bind2)); 871 } 872 } 873 } 874} 875 876ok(eq_sql_bind( 877 "SELECT * FROM foo WHERE id = ?", [42], 878 "SELECT * FROM foo WHERE (id = ?)", [42], 879 ), 880 "eq_sql_bind considers equal SQL expressions and bind values equal" 881); 882 883 884ok(!eq_sql_bind( 885 "SELECT * FROM foo WHERE id = ?", [42], 886 "SELECT * FROM foo WHERE (id = ?)", [0], 887 ), 888 "eq_sql_bind considers equal SQL expressions and different bind values different" 889); 890 891ok(!eq_sql_bind( 892 "SELECT * FROM foo WHERE id = ?", [42], 893 "SELECT * FROM bar WHERE (id = ?)", [42], 894 ), 895 "eq_sql_bind considers different SQL expressions and equal bind values different" 896); 897