SQLite

Check-in [4b2c08e898]
Login

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: 4b2c08e8984befb12fa2173ca89d9f54cf764d22
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
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
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.359 2009/01/07 20:58:57 drh Exp $
*/
#include "sqliteInt.h"

/*
** Trace output macros
*/
#if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)







|







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
3131
3132
3133
3134
3135
3136
3137
3138
3139






3140
3141
3142
3143
3144
3145
3146

    /* 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;
    assert( !pIdx
           || (bestPlan.plan.wsFlags&WHERE_INDEXED)==0
           || pIdx==bestPlan.plan.u.pIdx );
    if( pIdx
     && ((bestPlan.plan.wsFlags & WHERE_INDEXED)==0
         || bestPlan.plan.u.pIdx!=pIdx)
    ){
      sqlite3ErrorMsg(pParse, "cannot use index: %s", pIdx->zName);
      goto whereBeginError;






    }
  }
  WHERETRACE(("*** Optimizer Finished ***\n"));
  if( db->mallocFailed ){
    goto whereBeginError;
  }








<
<
<
|
|
<
<
|
|
>
>
>
>
>
>







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
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.1 2009/01/07 20:58:57 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 LIKE 'abc%'}
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);
  }
}














|















|







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
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.3 2008/12/30 17:55:00 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !or_opt {
  finish_test
  return













|







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