Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Increase test coverage of where.c. Make sure OR-optimization works on UPDATE and DELETE in addition to SELECT. (Bug found by coverage tests.) (CVS 6139) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
4b2c08e8984befb12fa2173ca89d9f54 |
User & Date: | drh 2009-01-08 03:11:19.000 |
Context
2009-01-08
| ||
12:05 | Clean up test_journal.c to make it easier to follow. (CVS 6140) (check-in: 5a28c7c7b4 user: danielk1977 tags: trunk) | |
03:11 | Increase test coverage of where.c. Make sure OR-optimization works on UPDATE and DELETE in addition to SELECT. (Bug found by coverage tests.) (CVS 6139) (check-in: 4b2c08e898 user: drh tags: trunk) | |
2009-01-07
| ||
20:58 | Add new test cases to increase coverage of where.c. (CVS 6138) (check-in: 2e1ab51f05 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is responsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is responsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** ** $Id: where.c,v 1.360 2009/01/08 03:11:19 drh Exp $ */ #include "sqliteInt.h" /* ** Trace output macros */ #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG) |
︙ | ︙ | |||
2715 2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 | addrCont = sqlite3VdbeAddOp3(v, OP_RowSetRead, regOrRowset,addrBrk,regNextRowid); sqlite3VdbeAddOp2(v, OP_Seek, iCur, regNextRowid); sqlite3ReleaseTempReg(pParse, regNextRowid); /* sqlite3ReleaseTempReg(pParse, regOrRowset); // Preserve the RowSet */ pLevel->op = OP_Goto; pLevel->p2 = addrCont; } disableTerm(pLevel, pTerm); }else #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ { /* Case 5: There is no usable index. We must do a complete | > > | 2715 2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 | addrCont = sqlite3VdbeAddOp3(v, OP_RowSetRead, regOrRowset,addrBrk,regNextRowid); sqlite3VdbeAddOp2(v, OP_Seek, iCur, regNextRowid); sqlite3ReleaseTempReg(pParse, regNextRowid); /* sqlite3ReleaseTempReg(pParse, regOrRowset); // Preserve the RowSet */ pLevel->op = OP_Goto; pLevel->p2 = addrCont; }else{ pLevel->op = OP_Noop; } disableTerm(pLevel, pTerm); }else #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ { /* Case 5: There is no usable index. We must do a complete |
︙ | ︙ | |||
3124 3125 3126 3127 3128 3129 3130 | /* Check that if the table scanned by this loop iteration had an ** INDEXED BY clause attached to it, that the named index is being ** used for the scan. If not, then query compilation has failed. ** Return an error. */ pIdx = pTabList->a[bestJ].pIndex; | < < < | | < < | | > > > > > > | 3126 3127 3128 3129 3130 3131 3132 3133 3134 3135 3136 3137 3138 3139 3140 3141 3142 3143 3144 3145 3146 3147 3148 3149 | /* Check that if the table scanned by this loop iteration had an ** INDEXED BY clause attached to it, that the named index is being ** used for the scan. If not, then query compilation has failed. ** Return an error. */ pIdx = pTabList->a[bestJ].pIndex; if( pIdx ){ if( (bestPlan.plan.wsFlags & WHERE_INDEXED)==0 ){ sqlite3ErrorMsg(pParse, "cannot use index: %s", pIdx->zName); goto whereBeginError; }else{ /* If an INDEXED BY clause is used, the bestIndex() function is ** guaranteed to find the index specified in the INDEXED BY clause ** if it find an index at all. */ assert( bestPlan.plan.u.pIdx==pIdx ); } } } WHERETRACE(("*** Optimizer Finished ***\n")); if( db->mallocFailed ){ goto whereBeginError; } |
︙ | ︙ |
Changes to test/mallocK.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2008 August 01 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # This test script checks malloc failures in WHERE clause analysis. # | | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | # 2008 August 01 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # This test script checks malloc failures in WHERE clause analysis. # # $Id: mallocK.test,v 1.2 2009/01/08 03:11:19 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl source $testdir/malloc_common.tcl set sql {SELECT * FROM t1, t2 WHERE (a=1 OR a=2)} for {set x 1} {$x<5} {incr x} { append sql " AND b=y" do_malloc_test mallocK-1.$x -sqlbody $sql -sqlprep { CREATE TABLE t1(a,b); CREATE TABLE t2(x,y); } } set sql {SELECT * FROM t1 WHERE a GLOB 'xyz*'} for {set x 1} {$x<5} {incr x} { append sql " AND b!=$x" do_malloc_test mallocK-2.$x -sqlbody $sql -sqlprep { CREATE TABLE t1(a,b); } } |
︙ | ︙ |
Changes to test/where9.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2008 December 30 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the multi-index OR clause optimizer. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2008 December 30 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the multi-index OR clause optimizer. # # $Id: where9.test,v 1.4 2009/01/08 03:11:19 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !or_opt { finish_test return |
︙ | ︙ | |||
447 448 449 450 451 452 453 454 455 | AND (c>=31031 OR d IS NULL) }] set a [expr {[lsearch $r {TABLE t1 VIA MULTI-INDEX UNION}]>=0}] set b [expr {[lsearch $r {TABLE t1 WITH INDEX t1b}]>=0}] concat $a $b } {0 1} } finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 | AND (c>=31031 OR d IS NULL) }] set a [expr {[lsearch $r {TABLE t1 VIA MULTI-INDEX UNION}]>=0}] set b [expr {[lsearch $r {TABLE t1 WITH INDEX t1b}]>=0}] concat $a $b } {0 1} } ############################################################################ # Make sure OR-clauses work correctly on UPDATE and DELETE statements. do_test where9-6.2.1 { db eval {SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85} } {99 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99} do_test where9-6.2.2 { ;# Deletes entries 90 91 92 96 97 99 count_steps { BEGIN; DELETE FROM t1 WHERE b IS NULL OR c IS NULL OR d IS NULL } } {scan 0 sort 0} do_test where9-6.2.3 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85; ROLLBACK; } } {93 85 86 87 88 89 93 94 95 98} do_test where9-6.2.4 { ;# Deletes entries 90 91 92 96 97 99 count_steps { BEGIN; DELETE FROM t1 WHERE +b IS NULL OR c IS NULL OR d IS NULL } } {scan 98 sort 0} do_test where9-6.2.5 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85; ROLLBACK; } } {93 85 86 87 88 89 93 94 95 98} do_test where9-6.2.6 { count_steps { BEGIN; UPDATE t1 SET a=a+100 WHERE (b IS NULL OR c IS NULL OR d IS NULL) AND a!=92 AND a!=97 } } {scan 0 sort 0} ;# Add 100 to entries 90 91 96 99 do_test where9-6.2.7 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85; ROLLBACK } } {99 85 86 87 88 89 92 93 94 95 97 98 190 191 196 199} do_test where9-6.2.8 { ;# Deletes entries 90 91 92 97 99 count_steps { BEGIN; DELETE FROM t1 WHERE (b IS NULL OR c IS NULL OR d IS NULL) AND a!=96 } } {scan 0 sort 0} do_test where9-6.2.9 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85; ROLLBACK; } } {94 85 86 87 88 89 93 94 95 96 98} do_test where9-6.3.1 { count_steps { BEGIN; DELETE FROM t1 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {scan 0 sort 0} ;# DELETEs rows 90 91 92 97 do_test where9-6.3.2 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; ROLLBACK; } } {95 85 86 87 88 89 93 94 95 96 98 99} do_test where9-6.3.3 { count_steps { BEGIN; UPDATE t1 SET a=a+100 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {scan 0 sort 0} ;# Add 100 to rowids 90 91 92 97 do_test where9-6.3.4 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a BETWEEN 85 AND 200; ROLLBACK; } } {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197} do_test where9-6.3.5 { count_steps { BEGIN; DELETE FROM t1 WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {scan 98 sort 0} ;# DELETEs rows 90 91 92 97 do_test where9-6.3.6 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; ROLLBACK; } } {95 85 86 87 88 89 93 94 95 96 98 99} do_test where9-6.3.7 { count_steps { BEGIN; UPDATE t1 SET a=a+100 WHERE (b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND +c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) } } {scan 98 sort 0} ;# Add 100 to rowids 90 91 92 97 do_test where9-6.3.8 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; ROLLBACK; } } {99 85 86 87 88 89 93 94 95 96 98 99} do_test where9-6.4.1 { count_steps { BEGIN; DELETE FROM t1 WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL) } } {scan 0 sort 0} ;# DELETE rows 87 88 89 90 91 do_test where9-6.4.2 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; ROLLBACK; } } {94 85 86 92 93 94 95 96 97 98 99} do_test where9-6.4.3 { count_steps { BEGIN; UPDATE t1 SET a=a+100 WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL) } } {scan 0 sort 0} ;# Add 100 to rowids 87 88 89 90 91 do_test where9-6.4.4 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a BETWEEN 85 AND 100; ROLLBACK; } } {99 85 86 92 93 94 95 96 97 98 99} do_test where9-6.5.1 { count_steps { BEGIN; DELETE FROM t1 WHERE a=83 OR b=913 OR c=28028 OR (d>=82 AND d<83) OR (e>2802 AND e<2803) OR f='fghijklmn' OR g='hgfedcb' } } {scan 0 sort 0} ;# DELETE rows 5 31 57 82 83 84 85 86 87 do_test where9-6.5.2 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a IN (5,31,57,82,83,84,85,86,87); ROLLBACK; } } {90} do_test where9-6.5.3 { count_steps { BEGIN; UPDATE t1 SET a=a+100 WHERE a=83 OR b=913 OR c=28028 OR (d>=82 AND d<83) OR (e>2802 AND e<2803) OR f='fghijklmn' OR g='hgfedcb' } } {scan 0 sort 0} ;# Add 100 to rowids 5 31 57 82 83 84 85 86 87 do_test where9-6.5.4 { db eval { SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87); ROLLBACK; } } {99 105 131 157 182 183 184 185 186 187} finish_test |