SQLite

Check-in [d8b7ab3712]
Login

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

Overview
Comment:Additional test cases for OR-optimization with covering indices, including one test case that currently fails.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | multi-or-covering-index
Files: files | file ages | folders
SHA1: d8b7ab37120ac20e60b6a600cd0e5b34a09cf97a
User & Date: drh 2012-08-24 15:29:03.804
Context
2012-08-24
17:52
More test cases for OR-optimization covering indices. (check-in: e5c3190c27 user: drh tags: multi-or-covering-index)
15:29
Additional test cases for OR-optimization with covering indices, including one test case that currently fails. (check-in: d8b7ab3712 user: drh tags: multi-or-covering-index)
10:52
Experimental change to support the covering index optimization for queries with OR terms in the WHERE clause that search a single index more than once. (check-in: 1dc8c7c741 user: dan tags: multi-or-covering-index)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/whereD.test.
︙︙
15
16
17
18
19
20
21
22
23

24
25
26
27
28
29
30
31
32
33


















34







35












36




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

do_execsql_test 1.1 {
  CREATE TABLE t(i,j,k);
  CREATE INDEX i ON t(i,j,k);


  INSERT INTO t VALUES(3, 3, 'three');
  INSERT INTO t VALUES(2, 2, 'two');
  INSERT INTO t VALUES(1, 1, 'one');
  INSERT INTO t VALUES(4, 4, 'four');
}

do_execsql_test 1.2 {
  SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2);
} {one two}


























finish_test






















|
|
>

|
|
|
|





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

>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>

>

15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75



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

do_execsql_test 1.1 {
  CREATE TABLE t(i,j,k,m,n);
  CREATE INDEX ijk ON t(i,j,k);
  CREATE INDEX jmn ON t(j,m,n);

  INSERT INTO t VALUES(3, 3, 'three', 3, 3);
  INSERT INTO t VALUES(2, 2, 'two', 2, 2);
  INSERT INTO t VALUES(1, 1, 'one', 1, 1);
  INSERT INTO t VALUES(4, 4, 'four', 4, 4);
}

do_execsql_test 1.2 {
  SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2);
} {one two}
do_execsql_test 1.3 {
  SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (i=3 AND j=3);
} {one two three}
do_execsql_test 1.4 {
  SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2);
} {one two}
do_execsql_test 1.5 {
  SELECT k FROM t WHERE (i=1 AND j=1) OR (i=2 AND j=2) OR (j=3 AND m=3);
} {one two three}
do_execsql_test 1.6 {
  SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND m=2) OR (i=3 AND j=3);
} {one two three}
do_execsql_test 1.7 {
  SELECT k FROM t WHERE (j=1 AND m=1) OR (i=2 AND j=2) OR (i=3 AND j=3);
} {one two three}
do_execsql_test 1.8 {
  SELECT k FROM t WHERE (i=1 AND j=1) OR (j=2 AND i=2) OR (i=3 AND j=3);
} {one two three}

do_execsql_test 2.0 {
  CREATE TABLE t1(a,b,c,d);
  CREATE INDEX t1b ON t1(b);
  CREATE INDEX t1c ON t1(c);
  CREATE INDEX t1d ON t1(d);
  CREATE TABLE t2(x,y);
  CREATE INDEX t2y ON t2(y);
  
  INSERT INTO t1 VALUES(1,2,3,4);
  INSERT INTO t1 VALUES(5,6,7,8);
  INSERT INTO t2 VALUES(1,2);
  INSERT INTO t2 VALUES(2,7);
  INSERT INTO t2 VALUES(3,4);
} {}
do_execsql_test 2.1 {
  SELECT a, x FROM t1 JOIN t2 ON +y=d OR x=7 ORDER BY a, x;
} {1 3}
do_execsql_test 2.2 {
  SELECT a, x FROM t1 JOIN t2 ON y=d OR x=7 ORDER BY a, x;
} {1 3}

finish_test