SQLite

Check-in [bff460ec2c]
Login

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: bff460ec2c3e250de034a6b34312b897bb2f3749
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
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.348 2008/12/29 14:51:06 danielk1977 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.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
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 subterms 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)







|







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