SQLite

Check-in [30904ef841]
Login

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

Overview
Comment:Modifications to test cases to account for new EXPLAIN QUERY PLAN output.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | experimental
Files: files | file ages | folders
SHA1: 30904ef8412348464e893e9e1551ef22cad24a3e
User & Date: dan 2010-11-11 10:36:26.000
Context
2010-11-11
11:43
Fix a bug in the EXPLAIN QUERY PLAN code. (check-in: 7ae068952f user: dan tags: experimental)
10:36
Modifications to test cases to account for new EXPLAIN QUERY PLAN output. (check-in: 30904ef841 user: dan tags: experimental)
2010-11-09
17:49
Merge with latest trunk changes. (check-in: 4b5c93bc7c user: dan tags: experimental)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to test/autoindex1.test.
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
233
234
235
236
237




































































238
239
240
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
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249

250
251







-
+
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
-
-
+
-
-
-
-
-
+
+
+
+
+
+
+
+
-
-
+
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
-








-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-


  }
} {4087}

# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
# Make sure automatic indices are not created for the RHS of an IN expression
# that is not a correlated subquery.
#
do_test autoindex1-500 {
do_execsql_test autoindex1-500 {
  db eval {
    CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
    CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
    EXPLAIN QUERY PLAN
    SELECT b FROM t501
     WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
  }
  CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
  CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
} {
  0 0 0 {SCAN TABLE t501 BY INTEGER PRIMARY KEY (rowid=?) (~25 rows)} 
  0 0 0 {EXECUTE LIST SUBQUERY 1} 
  1 0 0 {SCAN TABLE t502 (~100000 rows)}
}
} {0 0 {TABLE t501 USING PRIMARY KEY} 0 0 {TABLE t502}}
do_test autoindex1-501 {
do_execsql_test autoindex1-501 {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT b FROM t501
     WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
  }
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
  0 0 0 {SCAN TABLE t501 (~500000 rows)} 
  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
  1 0 0 {SCAN TABLE t502 BY AUTOMATIC COVERING INDEX (y=?) (~7 rows)}
}
} {0 0 {TABLE t501} 0 0 {TABLE t502 WITH AUTOMATIC INDEX}}
do_test autoindex1-502 {
do_execsql_test autoindex1-502 {
  db eval {
    EXPLAIN QUERY PLAN
    SELECT b FROM t501
     WHERE t501.a=123
       AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
  }
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a=123
     AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
  0 0 0 {SCAN TABLE t501 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
  1 0 0 {SCAN TABLE t502 (~100000 rows)}
}
} {0 0 {TABLE t501 USING PRIMARY KEY} 0 0 {TABLE t502}}


# The following code checks a performance regression reported on the
# mailing list on 2010-10-19.  The problem is that the nRowEst field
# of ephermeral tables was not being initialized correctly and so no
# automatic index was being created for the emphemeral table when it was
# used as part of a join.
#
do_test autoindex1-600 {
do_execsql_test autoindex1-600 {
  db eval {
    CREATE TABLE flock_owner(
      owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
      flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
      owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
      owner_change_date TEXT, last_changed TEXT NOT NULL,
      CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
    );
    CREATE TABLE sheep (
      Sheep_No char(7) NOT NULL,
      Date_of_Birth char(8),
      Sort_DoB text,
      Flock_Book_Vol char(2),
      Breeder_No char(6),
      Breeder_Person integer,
      Originating_Flock char(6),
      Registering_Flock char(6),
      Tag_Prefix char(9),
      Tag_No char(15),
      Sort_Tag_No integer,
      Breeders_Temp_Tag char(15),
      Sex char(1),
      Sheep_Name char(32),
      Sire_No char(7),
      Dam_No char(7),
      Register_Code char(1),
      Colour char(48),
      Colour_Code char(2),
      Pattern_Code char(8),
      Horns char(1),
      Litter_Size char(1),
      Coeff_of_Inbreeding real,
      Date_of_Registration text,
      Date_Last_Changed text,
      UNIQUE(Sheep_No));
    CREATE INDEX fo_flock_no_index  
                ON flock_owner (flock_no);
    CREATE INDEX fo_owner_change_date_index  
                ON flock_owner (owner_change_date);
    CREATE INDEX fo_owner_person_id_index  
                ON flock_owner (owner_person_id);
    CREATE INDEX sheep_org_flock_index  
             ON sheep (originating_flock);
    CREATE INDEX sheep_reg_flock_index  
             ON sheep (registering_flock);
    EXPLAIN QUERY PLAN
    SELECT x.sheep_no, x.registering_flock, x.date_of_registration
     FROM sheep x LEFT JOIN
         (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
         s.date_of_registration, prev.owner_change_date
         FROM sheep s JOIN flock_owner prev ON s.registering_flock =
     prev.flock_no
         AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
         WHERE NOT EXISTS
             (SELECT 'x' FROM flock_owner later
             WHERE prev.flock_no = later.flock_no
             AND later.owner_change_date > prev.owner_change_date
             AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
         ) y ON x.sheep_no = y.sheep_no
     WHERE y.sheep_no IS NULL
     ORDER BY x.registering_flock;
  }
  CREATE TABLE flock_owner(
    owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
    flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
    owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
    owner_change_date TEXT, last_changed TEXT NOT NULL,
    CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
  );
  CREATE TABLE sheep (
    Sheep_No char(7) NOT NULL,
    Date_of_Birth char(8),
    Sort_DoB text,
    Flock_Book_Vol char(2),
    Breeder_No char(6),
    Breeder_Person integer,
    Originating_Flock char(6),
    Registering_Flock char(6),
    Tag_Prefix char(9),
    Tag_No char(15),
    Sort_Tag_No integer,
    Breeders_Temp_Tag char(15),
    Sex char(1),
    Sheep_Name char(32),
    Sire_No char(7),
    Dam_No char(7),
    Register_Code char(1),
    Colour char(48),
    Colour_Code char(2),
    Pattern_Code char(8),
    Horns char(1),
    Litter_Size char(1),
    Coeff_of_Inbreeding real,
    Date_of_Registration text,
    Date_Last_Changed text,
    UNIQUE(Sheep_No));
  CREATE INDEX fo_flock_no_index  
              ON flock_owner (flock_no);
  CREATE INDEX fo_owner_change_date_index  
              ON flock_owner (owner_change_date);
  CREATE INDEX fo_owner_person_id_index  
              ON flock_owner (owner_person_id);
  CREATE INDEX sheep_org_flock_index  
           ON sheep (originating_flock);
  CREATE INDEX sheep_reg_flock_index  
           ON sheep (registering_flock);
  EXPLAIN QUERY PLAN
  SELECT x.sheep_no, x.registering_flock, x.date_of_registration
   FROM sheep x LEFT JOIN
       (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
       s.date_of_registration, prev.owner_change_date
       FROM sheep s JOIN flock_owner prev ON s.registering_flock =
   prev.flock_no
       AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
       WHERE NOT EXISTS
           (SELECT 'x' FROM flock_owner later
           WHERE prev.flock_no = later.flock_no
           AND later.owner_change_date > prev.owner_change_date
           AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
       ) y ON x.sheep_no = y.sheep_no
   WHERE y.sheep_no IS NULL
   ORDER BY x.registering_flock;
} {
  1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 
  1 1 1 {SCAN TABLE flock_owner AS prev BY INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND flock_no<?) (~2 rows)} 
  1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 
  2 0 0 {SCAN TABLE flock_owner AS later BY COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND flock_no>? AND flock_no<?) (~1 rows)} 
  0 0 0 {SCAN TABLE sheep AS x BY INDEX sheep_reg_flock_index (~1000000 rows)} 
  0 1 1 {SCAN SUBQUERY 1 AS y BY AUTOMATIC COVERING INDEX (sheep_no=?) (~7 rows)}
}
} {0 0 {TABLE sheep AS s} 1 1 {TABLE flock_owner AS prev WITH INDEX sqlite_autoindex_flock_owner_1} 0 0 {TABLE flock_owner AS later WITH INDEX sqlite_autoindex_flock_owner_1} 0 0 {TABLE sheep AS x WITH INDEX sheep_reg_flock_index ORDER BY} 1 1 {TABLE  AS y WITH AUTOMATIC INDEX}}

finish_test
Changes to test/e_createtable.test.
1369
1370
1371
1372
1373
1374
1375
1376

1377
1378
1379

1380
1381
1382

1383
1384
1385
1386
1387
1388
1389
1369
1370
1371
1372
1373
1374
1375

1376
1377
1378

1379
1380
1381

1382
1383
1384
1385
1386
1387
1388
1389







-
+


-
+


-
+







#
do_execsql_test 4.10.0 {
  CREATE TABLE t1(a, b PRIMARY KEY);
  CREATE TABLE t2(a, b, c, UNIQUE(b, c));
}
do_createtable_tests 4.10 {
  1    "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" 
       {0 0 {TABLE t1 WITH INDEX sqlite_autoindex_t1_1}}
       {0 0 0 {SCAN TABLE t1 BY INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}}

  2    "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
       {0 0 {TABLE t2 WITH INDEX sqlite_autoindex_t2_1 ORDER BY}}
       {0 0 0 {SCAN TABLE t2 BY INDEX sqlite_autoindex_t2_1 (~1000000 rows)}}

  3    "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
       {0 0 {TABLE t2 WITH INDEX sqlite_autoindex_t2_1}}
       {0 0 0 {SCAN TABLE t2 BY INDEX sqlite_autoindex_t2_1 (b=? AND b>?) (~3 rows)}}
}

# EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a
# column definition or specified as a table constraint. In practice it
# makes no difference.
#
#   All the tests that deal with CHECK constraints below (4.11.* and 
Changes to test/e_fkey.test.
964
965
966
967
968
969
970
971

972
973
974
975
976







977
978

979
980
981
982






983
984
985
986
987
988
989
990
964
965
966
967
968
969
970

971





972
973
974
975
976
977
978


979




980
981
982
983
984
985

986
987
988
989
990
991
992







-
+
-
-
-
-
-
+
+
+
+
+
+
+
-
-
+
-
-
-
-
+
+
+
+
+
+
-







      trackid     INTEGER, 
      trackname   TEXT, 
      trackartist INTEGER,
      FOREIGN KEY(trackartist) REFERENCES artist(artistid)
    );
  }
} {}
do_test e_fkey-25.2 {
do_execsql_test e_fkey-25.2 {
  execsql {
    PRAGMA foreign_keys = OFF;
    EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
    EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?;
  }
  PRAGMA foreign_keys = OFF;
  EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
  EXPLAIN QUERY PLAN SELECT rowid FROM track WHERE trackartist = ?;
} {
  0 0 0 {SCAN TABLE artist (~1000000 rows)} 
  0 0 0 {SCAN TABLE track (~100000 rows)}
}
} {0 0 {TABLE artist} 0 0 {TABLE track}}
do_test e_fkey-25.3 {
do_execsql_test e_fkey-25.3 {
  execsql { 
    PRAGMA foreign_keys = ON;
    EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
  }
  PRAGMA foreign_keys = ON;
  EXPLAIN QUERY PLAN DELETE FROM artist WHERE 1;
} {
  0 0 0 {SCAN TABLE artist (~1000000 rows)} 
  0 0 0 {SCAN TABLE track (~100000 rows)}
}
} {0 0 {TABLE artist} 0 0 {TABLE track}}
do_test e_fkey-25.4 {
  execsql {
    INSERT INTO artist VALUES(5, 'artist 5');
    INSERT INTO artist VALUES(6, 'artist 6');
    INSERT INTO artist VALUES(7, 'artist 7');
    INSERT INTO track VALUES(1, 'track 1', 5);
    INSERT INTO track VALUES(2, 'track 2', 6);
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101






1102
1103
1104
1105
1106
1107






1108

1109
1110
1111
1112
1113
1114
1115
1091
1092
1093
1094
1095
1096
1097






1098
1099
1100
1101
1102
1103






1104
1105
1106
1107
1108
1109

1110
1111
1112
1113
1114
1115
1116
1117







-
-
-
-
-
-
+
+
+
+
+
+
-
-
-
-
-
-
+
+
+
+
+
+
-
+







    );
    CREATE INDEX trackindex ON track(trackartist);
  }
} {}
do_test e_fkey-27.2 {
  eqp { INSERT INTO artist VALUES(?, ?) }
} {}
do_test e_fkey-27.3 {
  eqp { UPDATE artist SET artistid = ?, artistname = ? }
} [list \
  0 0 {TABLE artist} \
  0 0 {TABLE track WITH INDEX trackindex} \
  0 0 {TABLE track WITH INDEX trackindex}
do_execsql_test e_fkey-27.3 {
  EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ?
} {
  0 0 0 {SCAN TABLE artist (~1000000 rows)} 
  0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 rows)} 
  0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 rows)}
]
do_test e_fkey-27.4 {
  eqp { DELETE FROM artist }
} [list \
  0 0 {TABLE artist} \
  0 0 {TABLE track WITH INDEX trackindex}
}
do_execsql_test e_fkey-27.4 {
  EXPLAIN QUERY PLAN DELETE FROM artist
} {
  0 0 0 {SCAN TABLE artist (~1000000 rows)} 
  0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 rows)}
]
}


###########################################################################
### SECTION 4.1: Composite Foreign Key Constraints
###########################################################################

#-------------------------------------------------------------------------
Changes to test/eqp.test.
183
184
185
186
187
188
189






















190
191
192
193
194
195
196
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







+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+







  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
  2 0 0 {SCAN TABLE t2 BY INDEX t2i1 (~1000000 rows)} 
  0 0 0 {SCAN SUBQUERY 1 AS x1 (~1000000 rows)} 
  0 1 1 {SCAN SUBQUERY 2 AS x2 (~1000000 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

det 3.3.1 {
  SELECT * FROM t1 WHERE y IN (SELECT y FROM t2)
} {
  0 0 0 {SCAN TABLE t1 (~100000 rows)} 
  0 0 0 {EXECUTE LIST SUBQUERY 1} 
  1 0 0 {SCAN TABLE t2 (~1000000 rows)}
}
det 3.3.2 {
  SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x)
} {
  0 0 0 {SCAN TABLE t1 (~500000 rows)} 
  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
  1 0 0 {SCAN TABLE t2 (~500000 rows)}
}
det 3.3.3 {
  SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x)
} {
  0 0 0 {SCAN TABLE t1 (~500000 rows)} 
  0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1} 
  1 0 0 {SCAN TABLE t2 (~500000 rows)}
}

#-------------------------------------------------------------------------
# Test cases eqp-4.* - tests for composite select statements.
#
do_eqp_test 4.1.1 {
  SELECT * FROM t1 UNION ALL SELECT * FROM t2
} {
Changes to test/fts3matchinfo.test.
10
11
12
13
14
15
16



17
18
19
20
21
22
23
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26







+
+
+







#***********************************************************************
# This file implements regression tests for the FTS3 module. The focus
# of this file is tables created with the "matchinfo=fts3" option.
#

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

# If SQLITE_ENABLE_FTS3 is not defined, omit this file.
ifcapable !fts3 { finish_test ; return }

set testprefix fts3matchinfo

proc mit {blob} {
  set scan(littleEndian) i*
  set scan(bigEndian) I*
  binary scan $blob $scan($::tcl_platform(byteOrder)) r
Changes to test/indexedby.test.
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51












52
53
54
55
56
57
58
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







-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+







#
proc EQP {sql} {
  uplevel "execsql {EXPLAIN QUERY PLAN $sql}"
}

# These tests are to check that "EXPLAIN QUERY PLAN" is working as expected.
#
do_test indexedby-1.2 {
  EQP { select * from t1 WHERE a = 10; }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-1.3 {
  EQP { select * from t1 ; }
} {0 0 {TABLE t1}}
do_test indexedby-1.4 {
  EQP { select * from t1, t2 WHERE c = 10; }
} {0 1 {TABLE t2 WITH INDEX i3} 1 0 {TABLE t1}}
do_execsql_test indexedby-1.2 {
  EXPLAIN QUERY PLAN select * from t1 WHERE a = 10; 
} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-1.3 {
  EXPLAIN QUERY PLAN select * from t1 ; 
} {0 0 0 {SCAN TABLE t1 (~1000000 rows)}}
do_execsql_test indexedby-1.4 {
  EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; 
} {
  0 0 1 {SCAN TABLE t2 BY INDEX i3 (c=?) (~10 rows)} 
  0 1 0 {SCAN TABLE t1 (~1000000 rows)}
}

# Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 
# attached to a table in the FROM clause, but not to a sub-select or
# SQL view. Also test that specifying an index that does not exist or
# is attached to a different table is detected as an error.
# 
do_test indexedby-2.1 {
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
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







-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+













-
-
-
-
-
-
+
+
+
+
+
+
+
+









-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+















-
+
-
-
+
-
-
-
-
-
-
+
+
+
+
+


















-
-
-
-
-
-
+
+
+
+
+
+



-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+






-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+







} {1 {near "WHERE": syntax error}}
do_test indexedby-2.7 {
  catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' }
} {1 {no such index: i1}}

# Tests for single table cases.
#
do_test indexedby-3.1 {
  EQP { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
} {0 0 {TABLE t1}}
do_test indexedby-3.2 {
  EQP { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-3.3 {
  EQP { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'}
} {0 0 {TABLE t1 WITH INDEX i2}}
do_execsql_test indexedby-3.1 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'
} {0 0 0 {SCAN TABLE t1 (~10000 rows)}}
do_execsql_test indexedby-3.2 {
  EXPLAIN QUERY PLAN 
  SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'
} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~2 rows)}}
do_execsql_test indexedby-3.3 {
  EXPLAIN QUERY PLAN 
  SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'
} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~2 rows)}}
do_test indexedby-3.4 {
  catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' }
} {1 {cannot use index: i2}}
do_test indexedby-3.5 {
  catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a }
} {1 {cannot use index: i2}}
do_test indexedby-3.6 {
  catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' }
} {0 {}}
do_test indexedby-3.7 {
  catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }
} {0 {}}

do_test indexedby-3.8 {
  EQP { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e }
} {0 0 {TABLE t3 WITH INDEX sqlite_autoindex_t3_1 ORDER BY}}
do_test indexedby-3.9 {
  EQP { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 }
} {0 0 {TABLE t3 WITH INDEX sqlite_autoindex_t3_1}}
do_execsql_test indexedby-3.8 {
  EXPLAIN QUERY PLAN 
  SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e 
} {0 0 0 {SCAN TABLE t3 BY INDEX sqlite_autoindex_t3_1 (~1000000 rows)}}
do_execsql_test indexedby-3.9 {
  EXPLAIN QUERY PLAN 
  SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 
} {0 0 0 {SCAN TABLE t3 BY INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}}
do_test indexedby-3.10 {
  catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 }
} {1 {cannot use index: sqlite_autoindex_t3_1}}
do_test indexedby-3.11 {
  catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 }
} {1 {no such index: sqlite_autoindex_t3_2}}

# Tests for multiple table cases.
#
do_test indexedby-4.1 {
  EQP { SELECT * FROM t1, t2 WHERE a = c }
} {0 0 {TABLE t1} 1 1 {TABLE t2 WITH INDEX i3}}
do_test indexedby-4.2 {
  EQP { SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c }
} {0 1 {TABLE t2} 1 0 {TABLE t1 WITH INDEX i1}}
do_execsql_test indexedby-4.1 {
  EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c 
} {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  0 1 1 {SCAN TABLE t2 BY INDEX i3 (c=?) (~10 rows)}
}
do_execsql_test indexedby-4.2 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c 
} {
  0 0 1 {SCAN TABLE t2 (~1000000 rows)} 
  0 1 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)}
}
do_test indexedby-4.3 {
  catchsql {
    SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c
  }
} {1 {cannot use index: i1}}
do_test indexedby-4.4 {
  catchsql {
    SELECT * FROM t2 INDEXED BY i3, t1 INDEXED BY i1 WHERE a=c
  }
} {1 {cannot use index: i3}}

# Test embedding an INDEXED BY in a CREATE VIEW statement. This block
# also tests that nothing bad happens if an index refered to by
# a CREATE VIEW statement is dropped and recreated.
#
do_test indexedby-5.1 {
do_execsql_test indexedby-5.1 {
  execsql {
    CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5;
  CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5;
  }
  EQP { SELECT * FROM v2 }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-5.2 {
  EQP { SELECT * FROM v2 WHERE b = 10 }
} {0 0 {TABLE t1 WITH INDEX i1}}
  EXPLAIN QUERY PLAN SELECT * FROM v2 
} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a>?) (~330000 rows)}}
do_execsql_test indexedby-5.2 {
  EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 
} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a>?) (~33000 rows)}}
do_test indexedby-5.3 {
  execsql { DROP INDEX i1 }
  catchsql { SELECT * FROM v2 }
} {1 {no such index: i1}}
do_test indexedby-5.4 {
  # Recreate index i1 in such a way as it cannot be used by the view query.
  execsql { CREATE INDEX i1 ON t1(b) }
  catchsql { SELECT * FROM v2 }
} {1 {cannot use index: i1}}
do_test indexedby-5.5 {
  # Drop and recreate index i1 again. This time, create it so that it can
  # be used by the query.
  execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) }
  catchsql { SELECT * FROM v2 }
} {0 {}}

# Test that "NOT INDEXED" may use the rowid index, but not others.
# 
do_test indexedby-6.1 {
  EQP { SELECT * FROM t1 WHERE b = 10 ORDER BY rowid }
} {0 0 {TABLE t1 WITH INDEX i2 ORDER BY}}
do_test indexedby-6.2 {
  EQP { SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid }
} {0 0 {TABLE t1 USING PRIMARY KEY ORDER BY}}
do_execsql_test indexedby-6.1 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 
} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~10 rows)}}
do_execsql_test indexedby-6.2 {
  EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid 
} {0 0 0 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (~100000 rows)}}

# Test that "INDEXED BY" can be used in a DELETE statement.
# 
do_test indexedby-7.1 {
  EQP { DELETE FROM t1 WHERE a = 5 }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-7.2 {
  EQP { DELETE FROM t1 NOT INDEXED WHERE a = 5 }
} {0 0 {TABLE t1}}
do_test indexedby-7.3 {
  EQP { DELETE FROM t1 INDEXED BY i1 WHERE a = 5 }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-7.4 {
  EQP { DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10}
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-7.5 {
  EQP { DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10}
} {0 0 {TABLE t1 WITH INDEX i2}}
do_execsql_test indexedby-7.1 {
  EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 
} {0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-7.2 {
  EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 
} {0 0 0 {SCAN TABLE t1 (~100000 rows)}}
do_execsql_test indexedby-7.3 {
  EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 
} {0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-7.4 {
  EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10
} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~2 rows)}}
do_execsql_test indexedby-7.5 {
  EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10
} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~2 rows)}}
do_test indexedby-7.6 {
  catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5}
} {1 {cannot use index: i2}}

# Test that "INDEXED BY" can be used in an UPDATE statement.
# 
do_test indexedby-8.1 {
  EQP { UPDATE t1 SET rowid=rowid+1 WHERE a = 5 }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-8.2 {
  EQP { UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 }
} {0 0 {TABLE t1}}
do_test indexedby-8.3 {
  EQP { UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 }
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-8.4 {
  EQP { UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10}
} {0 0 {TABLE t1 WITH INDEX i1}}
do_test indexedby-8.5 {
  EQP { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10}
} {0 0 {TABLE t1 WITH INDEX i2}}
do_execsql_test indexedby-8.1 {
  EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5 
} {0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-8.2 {
  EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 
} {0 0 0 {SCAN TABLE t1 (~100000 rows)}}
do_execsql_test indexedby-8.3 {
  EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 
} {0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}}
do_execsql_test indexedby-8.4 {
  EXPLAIN QUERY PLAN 
  UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10
} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~2 rows)}}
do_execsql_test indexedby-8.5 {
  EXPLAIN QUERY PLAN 
  UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10
} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~2 rows)}}
do_test indexedby-8.6 {
  catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5}
} {1 {cannot use index: i2}}

# Test that bug #3560 is fixed.
#
do_test indexedby-9.1 {
Changes to test/tester.tcl.
345
346
347
348
349
350
351
352

353
354
355
356
357
358
359
345
346
347
348
349
350
351

352
353
354
355
356
357
358
359







-
+







  } {
    set testname "${::testprefix}-$testname"
  }
}
    
proc do_execsql_test {testname sql {result {}}} {
  fix_testname testname
  uplevel do_test $testname [list "execsql {$sql}"] [list $result]
  uplevel do_test $testname [list "execsql {$sql}"] [list [list {*}$result]]
}
proc do_catchsql_test {testname sql result} {
  fix_testname testname
  uplevel do_test $testname [list "catchsql {$sql}"] [list $result]
}

#-------------------------------------------------------------------------
Changes to test/tkt-78e04e52ea.test.
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
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







-
+












-
+





-
+


    CREATE INDEX i1 ON ""("" COLLATE nocase);
  }
} {}
do_test tkt-78e04-1.4 {
  execsql {
    EXPLAIN QUERY PLAN SELECT * FROM "" WHERE "" LIKE 'abc%';
  }
} {0 0 {TABLE }}
} {0 0 0 {SCAN TABLE  (~500000 rows)}}
do_test tkt-78e04-1.5 {
  execsql {
    DROP TABLE "";
    SELECT name FROM sqlite_master;
  }
} {t2}

do_test tkt-78e04-2.1 {
  execsql {
    CREATE INDEX "" ON t2(x);
    EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=5;
  }
} {0 0 {TABLE t2 WITH INDEX }}
} {0 0 0 {SCAN TABLE t2 BY COVERING INDEX  (x=?) (~10 rows)}}
do_test tkt-78e04-2.2 {
  execsql {
    DROP INDEX "";
    EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=2;
  }
} {0 0 {TABLE t2}}
} {0 0 0 {SCAN TABLE t2 (~100000 rows)}}

finish_test
Changes to test/tkt3442.test.
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
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







-
+


-
+








-
+







# These tests perform an EXPLAIN QUERY PLAN on both versions of the 
# SELECT referenced in ticket #3442 (both '5000' and "5000") 
# and verify that the query plan is the same.
#
ifcapable explain {
  do_test tkt3442-1.2 {
    EQP { SELECT node FROM listhash WHERE id='5000' LIMIT 1; }
  } {0 0 {TABLE listhash WITH INDEX ididx}}
  } {0 0 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}}
  do_test tkt3442-1.3 {
    EQP { SELECT node FROM listhash WHERE id="5000" LIMIT 1; }
  } {0 0 {TABLE listhash WITH INDEX ididx}}
  } {0 0 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}}
}


# Some extra tests testing other permutations of 5000.
#
ifcapable explain {
  do_test tkt3442-1.4 {
    EQP { SELECT node FROM listhash WHERE id=5000 LIMIT 1; }
  } {0 0 {TABLE listhash WITH INDEX ididx}}
  } {0 0 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}}
}
do_test tkt3442-1.5 {
  catchsql {
    SELECT node FROM listhash WHERE id=[5000] LIMIT 1;
  }
} {1 {no such column: 5000}}

Changes to test/where3.test.
213
214
215
216
217
218
219
220

221
222
223
224
225
226





227
228
229





230
231

232
233
234
235






236
237
238
239
240
241
242

243
244
245
246
247
248
249










250
251

252
253
254
255







256
257

258
259
260
261







262
263
264
265
266
267

268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285

















286
287
288
289
290
291
292
293
294
295












296
297

298
299
300
301
302
303
304
305
306












307
308

309
310
311
312
313
314
315
316
317












318
319

320
321
322
323
324
325
326
327
328












329
330
331
332
213
214
215
216
217
218
219

220






221
222
223
224
225



226
227
228
229
230


231




232
233
234
235
236
237

238
239
240
241
242

243







244
245
246
247
248
249
250
251
252
253


254




255
256
257
258
259
260
261


262




263
264
265
266
267
268
269

270
271
272
273

274


















275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291










292
293
294
295
296
297
298
299
300
301
302
303


304









305
306
307
308
309
310
311
312
313
314
315
316


317









318
319
320
321
322
323
324
325
326
327
328
329


330









331
332
333
334
335
336
337
338
339
340
341
342

343
344
345







-
+
-
-
-
-
-
-
+
+
+
+
+
-
-
-
+
+
+
+
+
-
-
+
-
-
-
-
+
+
+
+
+
+
-





-
+
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
-
-
+
-
-
-
-
+
+
+
+
+
+
+
-
-
+
-
-
-
-
+
+
+
+
+
+
+
-




-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
-
-
+
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
-
-
+
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
-
-
+
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
-



} {tB {} tC * tA * tD *}

# Ticket [13f033c865f878953]
# If the outer loop must be a full table scan, do not let ANALYZE trick
# the planner into use a table for the outer loop that might be indexable
# if held until an inner loop.
# 
do_test where3-3.0 {
do_execsql_test where3-3.0 {
  execsql {
    CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c);
    CREATE INDEX t301c ON t301(c);
    INSERT INTO t301 VALUES(1,2,3);
    CREATE TABLE t302(x, y);
    ANALYZE;
  CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c);
  CREATE INDEX t301c ON t301(c);
  INSERT INTO t301 VALUES(1,2,3);
  CREATE TABLE t302(x, y);
  ANALYZE;
    explain query plan
    SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y;
  }
  explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y;
} {
  0 0 0 {SCAN TABLE t302 (~0 rows)} 
  0 1 1 {SCAN TABLE t301 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
}
} {0 0 {TABLE t302} 1 1 {TABLE t301 USING PRIMARY KEY}}
do_test where3-3.1 {
do_execsql_test where3-3.1 {
  execsql {
    explain query plan
    SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
  }
  explain query plan
  SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
} {
  0 0 1 {SCAN TABLE t302 (~0 rows)} 
  0 1 0 {SCAN TABLE t301 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
}
} {0 1 {TABLE t302} 1 0 {TABLE t301 USING PRIMARY KEY}}

# Verify that when there are multiple tables in a join which must be
# full table scans that the query planner attempts put the table with
# the fewest number of output rows as the outer loop.
#
do_test where3-4.0 {
do_execsql_test where3-4.0 {
  execsql {
    CREATE TABLE t400(a INTEGER PRIMARY KEY, b, c);
    CREATE TABLE t401(p INTEGER PRIMARY KEY, q, r);
    CREATE TABLE t402(x INTEGER PRIMARY KEY, y, z);
    EXPLAIN QUERY PLAN
    SELECT * FROM t400, t401, t402 WHERE t402.z GLOB 'abc*';
  }
  CREATE TABLE t400(a INTEGER PRIMARY KEY, b, c);
  CREATE TABLE t401(p INTEGER PRIMARY KEY, q, r);
  CREATE TABLE t402(x INTEGER PRIMARY KEY, y, z);
  EXPLAIN QUERY PLAN
  SELECT * FROM t400, t401, t402 WHERE t402.z GLOB 'abc*';
} {
  0 0 2 {SCAN TABLE t402 (~500000 rows)} 
  0 1 0 {SCAN TABLE t400 (~1000000 rows)} 
  0 2 1 {SCAN TABLE t401 (~1000000 rows)}
}
} {0 2 {TABLE t402} 1 0 {TABLE t400} 2 1 {TABLE t401}}
do_test where3-4.1 {
do_execsql_test where3-4.1 {
  execsql {
    EXPLAIN QUERY PLAN
    SELECT * FROM t400, t401, t402 WHERE t401.r GLOB 'abc*';
  }
  EXPLAIN QUERY PLAN
  SELECT * FROM t400, t401, t402 WHERE t401.r GLOB 'abc*';
} {
  0 0 1 {SCAN TABLE t401 (~500000 rows)} 
  0 1 0 {SCAN TABLE t400 (~1000000 rows)} 
  0 2 2 {SCAN TABLE t402 (~1000000 rows)}
}
} {0 1 {TABLE t401} 1 0 {TABLE t400} 2 2 {TABLE t402}}
do_test where3-4.2 {
do_execsql_test where3-4.2 {
  execsql {
    EXPLAIN QUERY PLAN
    SELECT * FROM t400, t401, t402 WHERE t400.c GLOB 'abc*';
  }
  EXPLAIN QUERY PLAN
  SELECT * FROM t400, t401, t402 WHERE t400.c GLOB 'abc*';
} {
  0 0 0 {SCAN TABLE t400 (~500000 rows)} 
  0 1 1 {SCAN TABLE t401 (~1000000 rows)} 
  0 2 2 {SCAN TABLE t402 (~1000000 rows)}
}
} {0 0 {TABLE t400} 1 1 {TABLE t401} 2 2 {TABLE t402}}

# Verify that a performance regression encountered by firefox
# has been fixed.
#
do_test where3-5.0 {
do_execsql_test where3-5.0 {
  execsql {
     CREATE TABLE aaa (id INTEGER PRIMARY KEY, type INTEGER,
                       fk INTEGER DEFAULT NULL, parent INTEGER,
                       position INTEGER, title LONGVARCHAR,
                       keyword_id INTEGER, folder_type TEXT,
                       dateAdded INTEGER, lastModified INTEGER);
     CREATE INDEX aaa_111 ON aaa (fk, type);
     CREATE INDEX aaa_222 ON aaa (parent, position);
     CREATE INDEX aaa_333 ON aaa (fk, lastModified);
     CREATE TABLE bbb (id INTEGER PRIMARY KEY, type INTEGER,
                       fk INTEGER DEFAULT NULL, parent INTEGER,
                       position INTEGER, title LONGVARCHAR,
                       keyword_id INTEGER, folder_type TEXT,
                       dateAdded INTEGER, lastModified INTEGER);
     CREATE INDEX bbb_111 ON bbb (fk, type);
     CREATE INDEX bbb_222 ON bbb (parent, position);
     CREATE INDEX bbb_333 ON bbb (fk, lastModified);
  }
  CREATE TABLE aaa (id INTEGER PRIMARY KEY, type INTEGER,
                    fk INTEGER DEFAULT NULL, parent INTEGER,
                    position INTEGER, title LONGVARCHAR,
                    keyword_id INTEGER, folder_type TEXT,
                    dateAdded INTEGER, lastModified INTEGER);
  CREATE INDEX aaa_111 ON aaa (fk, type);
  CREATE INDEX aaa_222 ON aaa (parent, position);
  CREATE INDEX aaa_333 ON aaa (fk, lastModified);
  CREATE TABLE bbb (id INTEGER PRIMARY KEY, type INTEGER,
                    fk INTEGER DEFAULT NULL, parent INTEGER,
                    position INTEGER, title LONGVARCHAR,
                    keyword_id INTEGER, folder_type TEXT,
                    dateAdded INTEGER, lastModified INTEGER);
  CREATE INDEX bbb_111 ON bbb (fk, type);
  CREATE INDEX bbb_222 ON bbb (parent, position);
  CREATE INDEX bbb_333 ON bbb (fk, lastModified);


  execsql {
    EXPLAIN QUERY PLAN
     SELECT bbb.title AS tag_title 
       FROM aaa JOIN bbb ON bbb.id = aaa.parent  
      WHERE aaa.fk = 'constant'
        AND LENGTH(bbb.title) > 0
        AND bbb.parent = 4
      ORDER BY bbb.title COLLATE NOCASE ASC;
  }
  EXPLAIN QUERY PLAN
   SELECT bbb.title AS tag_title 
     FROM aaa JOIN bbb ON bbb.id = aaa.parent  
    WHERE aaa.fk = 'constant'
      AND LENGTH(bbb.title) > 0
      AND bbb.parent = 4
    ORDER BY bbb.title COLLATE NOCASE ASC;
} {
  0 0 0 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 
  0 1 1 {SCAN TABLE bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
} {0 0 {TABLE aaa WITH INDEX aaa_333} 1 1 {TABLE bbb USING PRIMARY KEY}}
do_test where3-5.1 {
do_execsql_test where3-5.1 {
  execsql {
    EXPLAIN QUERY PLAN
     SELECT bbb.title AS tag_title 
       FROM aaa JOIN aaa AS bbb ON bbb.id = aaa.parent  
      WHERE aaa.fk = 'constant'
        AND LENGTH(bbb.title) > 0
        AND bbb.parent = 4
      ORDER BY bbb.title COLLATE NOCASE ASC;
  }
  EXPLAIN QUERY PLAN
   SELECT bbb.title AS tag_title 
     FROM aaa JOIN aaa AS bbb ON bbb.id = aaa.parent  
    WHERE aaa.fk = 'constant'
      AND LENGTH(bbb.title) > 0
      AND bbb.parent = 4
    ORDER BY bbb.title COLLATE NOCASE ASC;
} {
  0 0 0 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 
  0 1 1 {SCAN TABLE aaa AS bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
} {0 0 {TABLE aaa WITH INDEX aaa_333} 1 1 {TABLE aaa AS bbb USING PRIMARY KEY}}
do_test where3-5.2 {
do_execsql_test where3-5.2 {
  execsql {
    EXPLAIN QUERY PLAN
     SELECT bbb.title AS tag_title 
       FROM bbb JOIN aaa ON bbb.id = aaa.parent  
      WHERE aaa.fk = 'constant'
        AND LENGTH(bbb.title) > 0
        AND bbb.parent = 4
      ORDER BY bbb.title COLLATE NOCASE ASC;
  }
  EXPLAIN QUERY PLAN
   SELECT bbb.title AS tag_title 
     FROM bbb JOIN aaa ON bbb.id = aaa.parent  
    WHERE aaa.fk = 'constant'
      AND LENGTH(bbb.title) > 0
      AND bbb.parent = 4
    ORDER BY bbb.title COLLATE NOCASE ASC;
} {
  0 0 1 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 
  0 1 0 {SCAN TABLE bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
} {0 1 {TABLE aaa WITH INDEX aaa_333} 1 0 {TABLE bbb USING PRIMARY KEY}}
do_test where3-5.3 {
do_execsql_test where3-5.3 {
  execsql {
    EXPLAIN QUERY PLAN
     SELECT bbb.title AS tag_title 
       FROM aaa AS bbb JOIN aaa ON bbb.id = aaa.parent  
      WHERE aaa.fk = 'constant'
        AND LENGTH(bbb.title) > 0
        AND bbb.parent = 4
      ORDER BY bbb.title COLLATE NOCASE ASC;
  }
  EXPLAIN QUERY PLAN
   SELECT bbb.title AS tag_title 
     FROM aaa AS bbb JOIN aaa ON bbb.id = aaa.parent  
    WHERE aaa.fk = 'constant'
      AND LENGTH(bbb.title) > 0
      AND bbb.parent = 4
    ORDER BY bbb.title COLLATE NOCASE ASC;
} {
  0 0 1 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 
  0 1 0 {SCAN TABLE aaa AS bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
} {0 1 {TABLE aaa WITH INDEX aaa_333} 1 0 {TABLE aaa AS bbb USING PRIMARY KEY}}


finish_test
Changes to test/where7.test.
23303
23304
23305
23306
23307
23308
23309
23310

23311
23312
23313
23314
23315
23316
23317
23318
23319
23320
23321
23322
23323
23324
23325
23326
23327
23328
23329
23330
23331
23332
23333
23334
23335
23336
23337
23338
23339
23340
23341
23342
23343
23344






































23345
23346
23347
23303
23304
23305
23306
23307
23308
23309

23310


































23311
23312
23313
23314
23315
23316
23317
23318
23319
23320
23321
23322
23323
23324
23325
23326
23327
23328
23329
23330
23331
23332
23333
23334
23335
23336
23337
23338
23339
23340
23341
23342
23343
23344
23345
23346
23347
23348

23349
23350







-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-


# test case for the performance regression fixed by
# check-in 28ba6255282b on 2010-10-21 02:05:06
#
# The test case that follows is code from an actual
# application with identifiers change and unused columns
# remove.
#
do_test where7-3.1 {
do_execsql_test where7-3.1 {
  db eval {
    CREATE TABLE t301 (
        c8 INTEGER PRIMARY KEY,
        c6 INTEGER,
        c4 INTEGER,
        c7 INTEGER,
        FOREIGN KEY (c4) REFERENCES series(c4)
    );
    CREATE INDEX t301_c6 on t301(c6);
    CREATE INDEX t301_c4 on t301(c4);
    CREATE INDEX t301_c7 on t301(c7);
    
    CREATE TABLE t302 (
        c1 INTEGER PRIMARY KEY,
        c8 INTEGER,
        c5 INTEGER,
        c3 INTEGER,
        c2 INTEGER,
        c4 INTEGER,
        FOREIGN KEY (c8) REFERENCES t301(c8)
    );
    CREATE INDEX t302_c3 on t302(c3);
    CREATE INDEX t302_c8_c3 on t302(c8, c3);
    CREATE INDEX t302_c5 on t302(c5);
    
    EXPLAIN QUERY PLAN
    SELECT t302.c1 
      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;
  }
  CREATE TABLE t301 (
      c8 INTEGER PRIMARY KEY,
      c6 INTEGER,
      c4 INTEGER,
      c7 INTEGER,
      FOREIGN KEY (c4) REFERENCES series(c4)
  );
  CREATE INDEX t301_c6 on t301(c6);
  CREATE INDEX t301_c4 on t301(c4);
  CREATE INDEX t301_c7 on t301(c7);
  
  CREATE TABLE t302 (
      c1 INTEGER PRIMARY KEY,
      c8 INTEGER,
      c5 INTEGER,
      c3 INTEGER,
      c2 INTEGER,
      c4 INTEGER,
      FOREIGN KEY (c8) REFERENCES t301(c8)
  );
  CREATE INDEX t302_c3 on t302(c3);
  CREATE INDEX t302_c8_c3 on t302(c8, c3);
  CREATE INDEX t302_c5 on t302(c5);
  
  EXPLAIN QUERY PLAN
  SELECT t302.c1 
    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 {SCAN TABLE t301 BY COVERING INDEX t301_c4 (c4=?) (~10 rows)} 
  0 0 1 {SCAN TABLE t301 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 1 0 {SCAN TABLE t302 BY INDEX t302_c8_c3 (c8=? AND c8>?) (~2 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
} {0 1 {TABLE t301 VIA MULTI-INDEX UNION} 1 0 {TABLE t302 WITH INDEX t302_c8_c3} 0 0 {TABLE t301 WITH INDEX t301_c4} 0 0 {TABLE t301 USING PRIMARY KEY}}

finish_test
Changes to test/where9.test.
354
355
356
357
358
359
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



385
386

387
388
389
390
391
392
393
354
355
356
357
358
359
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
385
386







-
+
-
-
-
+
+
-
-
-
+
+
-
-
-
-
+
+
+
-
-
-
+
+
-
-
-
-
-
-
+
+
+
+
+
-
-
-
-
+
+
+
-
-
+







     WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80)
    ORDER BY 1, 2, 3
  }
} {1 80 2 1 80 28 1 80 54 1 80 80 2 80 2 2 80 28 2 80 54 2 80 80 scan 1 sort 1}


ifcapable explain {
  do_test where9-3.1 {
  do_execsql_test where9-3.1 {
    set r [db eval {
      EXPLAIN QUERY PLAN
      SELECT t2.a FROM t1, t2
    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)
    }]
    WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
  } {
    set a [expr {[lsearch $r {TABLE t2 VIA MULTI-INDEX UNION}]>=0}]
    set b [expr {[lsearch $r {TABLE t2 WITH INDEX t2f}]>=0}]
    set c [expr {([lsearch $r {TABLE t2 WITH INDEX t2c}]>=0)+
                  [lsearch $r {TABLE t2 WITH INDEX t2d}]>=0}]
    0 0 0 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SCAN TABLE t2 BY INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SCAN TABLE t2 BY COVERING INDEX t2f (f=?) (~10 rows)}
    concat $a $b $c
  } {1 1 1}
  do_test where9-3.2 {
  }
  do_execsql_test where9-3.2 {
    set r [db eval {
      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
    }]
    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
  } {
    set a [expr {[lsearch $r {TABLE t2 VIA MULTI-INDEX UNION}]>=0}]
    set b [expr {[lsearch $r {TABLE t2 WITH INDEX t2f}]>=0}]
    set c [expr {([lsearch $r {TABLE t2 WITH INDEX t2c}]>=0)+
                  [lsearch $r {TABLE t2 WITH INDEX t2d}]>=0}]
    0 0 0 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SCAN TABLE t2 BY INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SCAN TABLE t2 BY COVERING INDEX t2f (f=?) (~10 rows)}
    concat $a $b $c
  } {1 1 1}
  }
} 

# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
#
do_test where9-4.1 {
  count_steps {
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
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







-
+
-
-
+
-
-
-
-
-
-
+
+
+
-
-
-
+
+


-
+
-
-
+
-
-
-
-
+
-
-
+
-
-
-
+
+



-
+
-
-
+
-
-
-
-
+
-
-
+
-
-
+







  }
} {1 {cannot use index: t1d}}

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_test where9-5.1 {
  do_execsql_test where9-5.1 {
    set r [db eval {
      EXPLAIN QUERY PLAN
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
      SELECT a FROM t1
       WHERE b>1000
         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}]
  } {
    0 0 0 {SCAN TABLE t1 BY INDEX t1c (c=?) (~10 rows)} 
    0 0 0 {SCAN TABLE t1 BY INDEX t1d (d=?) (~10 rows)}
    concat $a $b
  } {1 0}

  }

  # In contrast, b=1000 is preferred over any OR-clause.
  #
  do_test where9-5.2 {
  do_execsql_test where9-5.2 {
    set r [db eval {
      EXPLAIN QUERY PLAN
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
      SELECT a FROM t1
       WHERE b=1000
         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}]
    0 0 0 {SCAN TABLE t1 BY INDEX t1b (b=?) (~5 rows)}
    concat $a $b
  } {0 1}

  }

  # Likewise, inequalities in an AND are preferred over inequalities in
  # an OR.
  #
  do_test where9-5.3 {
  do_execsql_test where9-5.3 {
    set r [db eval {
      EXPLAIN QUERY PLAN
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL)
      SELECT a FROM t1
       WHERE b>1000
         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}]
    0 0 0 {SCAN TABLE t1 BY INDEX t1b (a>?) (~165000 rows)}
    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}