/ Check-in [4997d8b8]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:More test cases for the OR optimization.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | or-opt
Files: files | file ages | folders
SHA1: 4997d8b81cd3ea7c708911bfece00020d11224f9
User & Date: drh 2011-10-07 16:08:28
Context
2011-10-07
17:45
Add testcase() macros to ensure good test coverage. Closed-Leaf check-in: 5c132592 user: drh tags: or-opt
16:08
More test cases for the OR optimization. check-in: 4997d8b8 user: drh tags: or-opt
14:40
Prevent infinite recursion of in the query planner for some pathological test cases by disabling OR-clause processing upon first recursion. check-in: 9fca05ea user: drh tags: or-opt
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/where7.test.

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
.....
23337
23338
23339
23340
23341
23342
23343
23344
23345
23346
23347
23348
23349
23350
#    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: where7.test,v 1.9 2009/06/07 23:45:11 drh Exp $

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

ifcapable !or_opt {
  finish_test
  return
................................................................................
    FROM t302 JOIN t301 ON t302.c8 = t301.c8
    WHERE t302.c2 = 19571
      AND t302.c3 > 1287603136
      AND (t301.c4 = 1407449685622784
           OR t301.c8 = 1407424651264000)
   ORDER BY t302.c5 LIMIT 200;
} {
  0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~10 rows)} 
  0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

finish_test







<
<







 







|






6
7
8
9
10
11
12


13
14
15
16
17
18
19
.....
23335
23336
23337
23338
23339
23340
23341
23342
23343
23344
23345
23346
23347
23348
#    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.



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

ifcapable !or_opt {
  finish_test
  return
................................................................................
    FROM t302 JOIN t301 ON t302.c8 = t301.c8
    WHERE t302.c2 = 19571
      AND t302.c3 > 1287603136
      AND (t301.c4 = 1407449685622784
           OR t301.c8 = 1407424651264000)
   ORDER BY t302.c5 LIMIT 200;
} {
  0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~5 rows)} 
  0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

finish_test

Changes to test/where9.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
...
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
...
778
779
780
781
782
783
784
785











































































786
#    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.9 2009/06/05 17:09:12 drh Exp $

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

ifcapable !or_opt {
  finish_test
  return
................................................................................
  do_execsql_test where9-3.1 {
    EXPLAIN QUERY PLAN
    SELECT t2.a FROM t1, t2
    WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
  } {
    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
  }
  do_execsql_test where9-3.2 {
    EXPLAIN QUERY PLAN
    SELECT coalesce(t2.a,9999)
    FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
    WHERE t1.a=80
  } {
    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)}
  }
} 

# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
#
do_test where9-4.1 {
................................................................................
ifcapable explain {
  # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
  # the former is an equality test which is expected to return fewer rows.
  #
  do_execsql_test where9-5.1 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
  } {
    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~10 rows)} 
    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~10 rows)}
  }

  # In contrast, b=1000 is preferred over any OR-clause.
  #
  do_execsql_test where9-5.2 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
  } {
................................................................................
  catchsql {
    UPDATE t1 INDEXED BY t1b 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)
  }
} {1 {cannot use index: t1b}}












































































finish_test







<







 







|









|







 







|
|







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

7
8
9
10
11
12
13

14
15
16
17
18
19
20
...
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
...
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
...
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
#    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.
#


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

ifcapable !or_opt {
  finish_test
  return
................................................................................
  do_execsql_test where9-3.1 {
    EXPLAIN QUERY PLAN
    SELECT t2.a FROM t1, t2
    WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
  } {
    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~5 rows)}
  }
  do_execsql_test where9-3.2 {
    EXPLAIN QUERY PLAN
    SELECT coalesce(t2.a,9999)
    FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
    WHERE t1.a=80
  } {
    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~5 rows)}
  }
} 

# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
#
do_test where9-4.1 {
................................................................................
ifcapable explain {
  # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
  # the former is an equality test which is expected to return fewer rows.
  #
  do_execsql_test where9-5.1 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
  } {
    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~2 rows)} 
    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~2 rows)}
  }

  # In contrast, b=1000 is preferred over any OR-clause.
  #
  do_execsql_test where9-5.2 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
  } {
................................................................................
  catchsql {
    UPDATE t1 INDEXED BY t1b 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)
  }
} {1 {cannot use index: t1b}}

############################################################################
# Test cases where terms inside an OR series are combined with AND terms
# external to the OR clause.  In other words, cases where
#
#              x AND (y OR z)
#
# is able to use indices on x,y and x,z, or indices y,x and z,x.
#
do_test where9-7.0 {
  execsql {
    CREATE TABLE t5(a, b, c, d, e, f, g, x, y);
    INSERT INTO t5
     SELECT a, b, c, e, d, f, g,
            CASE WHEN (a&1)!=0 THEN 'y' ELSE 'n' END,
            CASE WHEN (a&2)!=0 THEN 'y' ELSE 'n' END
       FROM t1;
    CREATE INDEX t5xb ON t5(x, b);
    CREATE INDEX t5xc ON t5(x, c);
    CREATE INDEX t5xd ON t5(x, d);
    CREATE INDEX t5xe ON t5(x, e);
    CREATE INDEX t5xf ON t5(x, f);
    CREATE INDEX t5xg ON t5(x, g);
    CREATE INDEX t5yb ON t5(y, b);
    CREATE INDEX t5yc ON t5(y, c);
    CREATE INDEX t5yd ON t5(y, d);
    CREATE INDEX t5ye ON t5(y, e);
    CREATE INDEX t5yf ON t5(y, f);
    CREATE INDEX t5yg ON t5(y, g);
    CREATE TABLE t6(a, b, c, e, d, f, g, x, y);
    INSERT INTO t6 SELECT * FROM t5;
    ANALYZE t5;
  }
} {}
do_test where9-7.1.1 {
  count_steps {
    SELECT a FROM t5 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
  }
} {79 81 83 scan 0 sort 1}
do_test where9-7.1.2 {
  execsql {
    SELECT a FROM t6 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
  }
} {79 81 83}
do_test where9-7.1.3 {
  count_steps {
    SELECT a FROM t5 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
  }
} {80 scan 0 sort 1}
do_test where9-7.1.4 {
  execsql {
    SELECT a FROM t6 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
  }
} {80}
do_test where9-7.2.1 {
  count_steps {
    SELECT a FROM t5 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
  }
} {83 scan 0 sort 1}
do_test where9-7.2.2 {
  execsql {
    SELECT a FROM t6 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
  }
} {83}
do_test where9-7.3.1 {
  count_steps {
    SELECT a FROM t5 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
  }
} {79 81 scan 0 sort 1}
do_test where9-7.3.2 {
  execsql {
    SELECT a FROM t6 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
  }
} {79 81}


finish_test