Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add test file where8.test. (CVS 6072) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
bff460ec2c3e250de034a6b34312b897 |
User & Date: | danielk1977 2008-12-29 18:33:33.000 |
Context
2008-12-29
| ||
23:45 | Always make sure WhereClause objects are initialized to zero when they are first allocated. (CVS 6073) (check-in: 7d0ae55d6b user: drh tags: trunk) | |
18:33 | Add test file where8.test. (CVS 6072) (check-in: bff460ec2c user: danielk1977 tags: trunk) | |
14:51 | Remove an old variable declaration that was commented out using a C++ comment. (CVS 6071) (check-in: 0b972f14f0 user: danielk1977 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.349 2008/12/29 18:33:33 danielk1977 Exp $ */ #include "sqliteInt.h" /* ** Trace output macros */ #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG) |
︙ | ︙ | |||
730 731 732 733 734 735 736 | ** A WhereOrTerm object is computed and attached to the term under ** analysis, regardless of the outcome of the analysis. Hence: ** ** WhereTerm.wtFlags |= TERM_ORINFO ** WhereTerm.u.pOrInfo = a dynamically allocated WhereOrTerm object ** ** The term being analyzed must have two or more of OR-connected subterms. | | | 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 | ** A WhereOrTerm object is computed and attached to the term under ** analysis, regardless of the outcome of the analysis. Hence: ** ** WhereTerm.wtFlags |= TERM_ORINFO ** WhereTerm.u.pOrInfo = a dynamically allocated WhereOrTerm object ** ** The term being analyzed must have two or more of OR-connected subterms. ** A single subterm might be a set of AND-connected sub-subterms. ** Examples of terms under analysis: ** ** (A) t1.x=t2.y OR t1.x=t2.z OR t1.y=15 OR t1.z=t3.a+5 ** (B) x=expr1 OR expr2=x OR x=expr3 ** (C) t1.x=t2.y OR (t1.x=t2.z AND t1.y=15) ** (D) x=expr1 OR (y>11 AND y<22 AND z LIKE '*hello*') ** (E) (p.a=1 AND q.b=2 AND r.c=3) OR (p.x=4 AND q.y=5 AND r.z=6) |
︙ | ︙ |
Added test/where8.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 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 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 | # 2008 December 23 # # 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 # is testing of where.c. More specifically, the focus is the optimization # of WHERE clauses that feature the OR operator. # # $Id: where8.test,v 1.1 2008/12/29 18:33:33 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Test organization: # # where8-1.*: Tests to demonstrate simple cases work with a single table # in the FROM clause. # # where8-2.*: Tests surrounding virtual tables and the OR optimization. # # where8-3.*: Tests with more than one table in the FROM clause. # proc execsql_status {sql {db db}} { set result [uplevel $db eval [list $sql]] concat $result [db status step] [db status sort] } proc execsql_status2 {sql {db db}} { set ::sqlite_search_count 0 breakpoint set result [uplevel [list execsql_status $sql $db]] concat $result $::sqlite_search_count } do_test where8-1.1 { execsql { CREATE TABLE t1(a, b, c); CREATE INDEX i1 ON t1(a); CREATE INDEX i2 ON t1(b); INSERT INTO t1 VALUES(1, 'one', 'I'); INSERT INTO t1 VALUES(2, 'two', 'II'); INSERT INTO t1 VALUES(3, 'three', 'III'); INSERT INTO t1 VALUES(4, 'four', 'IV'); INSERT INTO t1 VALUES(5, 'five', 'V'); INSERT INTO t1 VALUES(6, 'six', 'VI'); INSERT INTO t1 VALUES(7, 'seven', 'VII'); INSERT INTO t1 VALUES(8, 'eight', 'VIII'); INSERT INTO t1 VALUES(9, 'nine', 'IX'); INSERT INTO t1 VALUES(10, 'ten', 'X'); } } {} do_test where8-1.2 { execsql_status2 { SELECT c FROM t1 WHERE a = 1 OR b = 'nine' } } {I IX 0 0 6} do_test where8-1.3 { execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b = 'two' } } {II IX X 0 0 6} do_test where8-1.4 { execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' } } {II III IX X 0 0 9} do_test where8-1.5 { execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 'f*' } } {IV V IX X 0 0 9} do_test where8-1.6 { execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY rowid } } {I III 0 0} do_test where8-1.7 { execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY a } } {I III 0 1} do_test where8-1.8 { # 18 searches. 9 on the index cursor and 9 on the table cursor. execsql_status2 { SELECT c FROM t1 WHERE a > 1 AND c LIKE 'I%' } } {II III IV IX 0 0 18} do_test where8-1.9 { execsql_status2 { SELECT c FROM t1 WHERE a >= 9 OR b <= 'eight' } } {VIII IX X 0 0 6} do_test where8-1.10 { execsql_status2 { SELECT c FROM t1 WHERE (a >= 9 AND c != 'X') OR b <= 'eight' } } {VIII IX 0 0 7} do_test where8-1.11 { execsql_status2 { SELECT c FROM t1 WHERE (a >= 4 AND a <= 6) OR b = 'nine' } } {IV V VI IX 0 0 10} do_test where8-1.12.1 { execsql_status2 { SELECT c FROM t1 WHERE a IN(1, 2, 3) OR a = 5 } } {I II III V 0 0 14} do_test where8-1.12.2 { execsql_status2 { SELECT c FROM t1 WHERE +a IN(1, 2, 3) OR +a = 5 } } {I II III V 9 0 9} #-------------------------------------------------------------------------- # Tests where8-2.*: Virtual tables # if 0 { ifcapable vtab { # Register the 'echo' module used for testing virtual tables. # register_echo_module [sqlite3_connection_pointer db] do_test where8-2.1 { execsql { CREATE VIRTUAL TABLE e1 USING echo(t1); SELECT b FROM e1; } } {one two three four five six seven eight nine ten} do_test where8-2.2.1 { set echo_module "" execsql { SELECT c FROM e1 WHERE a=1 OR b='three'; } } {I III} do_test where8-2.2.2 { set echo_module } {TODO: What should this be?} } } #-------------------------------------------------------------------------- # Tests where8-3.*: Cases with multiple tables in the FROM clause. # do_test where8-3.1 { execsql { CREATE TABLE t2(d, e, f); CREATE INDEX i3 ON t2(d); CREATE INDEX i4 ON t2(e); INSERT INTO t2 VALUES(1, NULL, 'I'); INSERT INTO t2 VALUES(2, 'four', 'IV'); INSERT INTO t2 VALUES(3, NULL, 'IX'); INSERT INTO t2 VALUES(4, 'sixteen', 'XVI'); INSERT INTO t2 VALUES(5, NULL, 'XXV'); INSERT INTO t2 VALUES(6, 'thirtysix', 'XXXVI'); INSERT INTO t2 VALUES(7, 'fortynine', 'XLIX'); INSERT INTO t2 VALUES(8, 'sixtyeight', 'LXIV'); INSERT INTO t2 VALUES(9, 'eightyone', 'LXXXIX'); INSERT INTO t2 VALUES(10, NULL, 'C'); } } {} do_test where8-3.2 { execsql_status { SELECT a, d FROM t1, t2 WHERE b=e } } {4 2 9 0} do_test where8-3.3 { execsql_status { SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = 6 } } {2 6 3 6 0 0} do_test where8-3.4 { execsql_status { SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = a } } {2 2 3 3 0 0} do_test where8-3.5 { execsql_status { SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND (d = a OR e = 'sixteen') } } {2 2 2 4 3 3 3 4 0 0} do_test where8-3.6 { # The first part of the WHERE clause in this query, (a=2 OR a=3) is # transformed into "a IN (2, 3)". This is why the sort is required. # execsql_status { SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND (d = a OR e = 'sixteen') ORDER BY t1.rowid } } {2 2 2 4 3 3 3 4 0 1} do_test where8-3.7 { execsql_status { SELECT a, d FROM t1, t2 WHERE a = 2 AND (d = a OR e = 'sixteen') ORDER BY t1.rowid } } {2 2 2 4 0 0} do_test where8-3.8 { execsql_status { SELECT a, d FROM t1, t2 WHERE (a = 2 OR b = 'three') AND (d = a OR e = 'sixteen') ORDER BY t1.rowid } } {2 2 2 4 3 3 3 4 0 0} do_test where8-3.9 { execsql_status { SELECT a, d FROM t1, t2 WHERE (a = 2 OR b = 'three' OR c = 'IX') AND (d = a OR e = 'sixteen') ORDER BY t1.rowid } } {2 2 2 4 3 3 3 4 9 4 9 9 9 0} finish_test |