SQLite

Check-in [dab5e52948]
Login

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

Overview
Comment:More test case updates. Tests are all running now.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | rework-EQP
Files: files | file ages | folders
SHA3-256: dab5e5294813891469660cceb211ac1a1e526715bb57dcdbb1ab90321e6a4dad
User & Date: drh 2018-05-02 19:42:33.079
Context
2018-05-03
01:37
Enhance EXPLAIN QUERY PLAN to report the generation of constant rows using VALUES or just a SELECT without FROM. (check-in: c75eee69fa user: drh tags: rework-EQP)
2018-05-02
19:42
More test case updates. Tests are all running now. (check-in: dab5e52948 user: drh tags: rework-EQP)
18:00
Fix test cases so that they work with the new EXPLAIN QUERY PLAN output format. Only some of the cases have been fixed. This is an incremental check-in. (check-in: 5f0e803e33 user: drh tags: rework-EQP)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/expert/expert1.test.
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
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
  eval $setup


do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } {
  SELECT * FROM t1
} {
  (no new indexes)
  0|0|0|SCAN TABLE t1
}

do_setup_rec_test $tn.2 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 WHERE b>?;
} {
  CREATE INDEX t1_idx_00000062 ON t1(b);
  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?)
}

do_setup_rec_test $tn.3 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
} {
  CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE);
  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?)
}

do_setup_rec_test $tn.4 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT a FROM t1 ORDER BY b;
} {
  CREATE INDEX t1_idx_00000062 ON t1(b);
  0|0|0|SCAN TABLE t1 USING INDEX t1_idx_00000062
}

do_setup_rec_test $tn.5 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT a FROM t1 WHERE a=? ORDER BY b;
} {
  CREATE INDEX t1_idx_000123a7 ON t1(a, b);
  0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?)
}

do_setup_rec_test $tn.6 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT min(a) FROM t1
} {
  CREATE INDEX t1_idx_00000061 ON t1(a);
  0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061
}

do_setup_rec_test $tn.7 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 ORDER BY a, b, c;
} {
  CREATE INDEX t1_idx_033e95fe ON t1(a, b, c);
  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe
}

#do_setup_rec_test $tn.1.8 {
#  CREATE TABLE t1(a, b, c);
#} {
#  SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
#} {
#  CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c);
#  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222
#}

do_setup_rec_test $tn.8.1 {
  CREATE TABLE t1(a COLLATE NOCase, b, c);
} {
  SELECT * FROM t1 WHERE a=?
} {
  CREATE INDEX t1_idx_00000061 ON t1(a);
  0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?)
}
do_setup_rec_test $tn.8.2 {
  CREATE TABLE t1(a, b COLLATE nocase, c);
} {
  SELECT * FROM t1 ORDER BY a ASC, b DESC, c ASC;
} {
  CREATE INDEX t1_idx_5cb97285 ON t1(a, b DESC, c);
  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5cb97285
}


# Tables with names that require quotes.
#
do_setup_rec_test $tn.9.1 {
  CREATE TABLE "t t"(a, b, c);
} {
  SELECT * FROM "t t" WHERE a=?
} {
  CREATE INDEX 't t_idx_00000061' ON 't t'(a);
  0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?) 
}

do_setup_rec_test $tn.9.2 {
  CREATE TABLE "t t"(a, b, c);
} {
  SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
} {
  CREATE INDEX 't t_idx_00000062' ON 't t'(b);
  0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?)
}

# Columns with names that require quotes.
#
do_setup_rec_test $tn.10.1 {
  CREATE TABLE t3(a, "b b", c);
} {
  SELECT * FROM t3 WHERE "b b" = ?
} {
  CREATE INDEX t3_idx_00050c52 ON t3('b b');
  0|0|0|SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?)
}

do_setup_rec_test $tn.10.2 {
  CREATE TABLE t3(a, "b b", c);
} {
  SELECT * FROM t3 ORDER BY "b b"
} {
  CREATE INDEX t3_idx_00050c52 ON t3('b b');
  0|0|0|SCAN TABLE t3 USING INDEX t3_idx_00050c52
}

# Transitive constraints
#
do_setup_rec_test $tn.11.1 {
  CREATE TABLE t5(a, b);
  CREATE TABLE t6(c, d);
} {
  SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
} {
  CREATE INDEX t5_idx_000123a7 ON t5(a, b);
  CREATE INDEX t6_idx_00000063 ON t6(c);
  0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?) 
  0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
}

# OR terms.
#
do_setup_rec_test $tn.12.1 {
  CREATE TABLE t7(a, b);
} {
  SELECT * FROM t7 WHERE a=? OR b=?
} {
  CREATE INDEX t7_idx_00000062 ON t7(b);
  CREATE INDEX t7_idx_00000061 ON t7(a);
  0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?) 
  0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
}

# rowid terms.
#
do_setup_rec_test $tn.13.1 {
  CREATE TABLE t8(a, b);
} {
  SELECT * FROM t8 WHERE rowid=?
} {
  (no new indexes)
  0|0|0|SEARCH TABLE t8 USING INTEGER PRIMARY KEY (rowid=?)
}
do_setup_rec_test $tn.13.2 {
  CREATE TABLE t8(a, b);
} {
  SELECT * FROM t8 ORDER BY rowid
} {
  (no new indexes)
  0|0|0|SCAN TABLE t8
}
do_setup_rec_test $tn.13.3 {
  CREATE TABLE t8(a, b);
} {
  SELECT * FROM t8 WHERE a=? ORDER BY rowid
} {
  CREATE INDEX t8_idx_00000061 ON t8(a); 
  0|0|0|SEARCH TABLE t8 USING INDEX t8_idx_00000061 (a=?)
}

# Triggers
#
do_setup_rec_test $tn.14 {
  CREATE TABLE t9(a, b, c);
  CREATE TABLE t10(a, b, c);
  CREATE TRIGGER t9t AFTER INSERT ON t9 BEGIN
    UPDATE t10 SET a=new.a WHERE b = new.b;
  END;
} {
  INSERT INTO t9 VALUES(?, ?, ?);
} {
  CREATE INDEX t10_idx_00000062 ON t10(b); 
  0|1|0|-- TRIGGER t9t
  0|0|0|SEARCH TABLE t10 USING INDEX t10_idx_00000062 (b=?)
}

do_setup_rec_test $tn.15 {
  CREATE TABLE t1(a, b);
  CREATE TABLE t2(c, d);

  WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
  INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s;

  WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
  INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s;
} {
  SELECT * FROM t2, t1 WHERE b=? AND d=? AND t2.rowid=t1.rowid
} {
  CREATE INDEX t2_idx_00000064 ON t2(d);
  0|0|0|SEARCH TABLE t2 USING INDEX t2_idx_00000064 (d=?) 
  0|1|1|SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
}

do_setup_rec_test $tn.16 {
  CREATE TABLE t1(a, b);
} {
  SELECT * FROM t1 WHERE b IS NOT NULL;
} {
  (no new indexes)
  0|0|0|SCAN TABLE t1
}

}

proc do_candidates_test {tn sql res} {
  set res [squish [string trim $res]]








|








|








|








|








|








|








|

















|







|











|








|










|








|












|
|











|
|










|







|







|














|
|















|
|








|







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
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
  eval $setup


do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } {
  SELECT * FROM t1
} {
  (no new indexes)
  SCAN TABLE t1
}

do_setup_rec_test $tn.2 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 WHERE b>?;
} {
  CREATE INDEX t1_idx_00000062 ON t1(b);
  SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?)
}

do_setup_rec_test $tn.3 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
} {
  CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE);
  SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?)
}

do_setup_rec_test $tn.4 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT a FROM t1 ORDER BY b;
} {
  CREATE INDEX t1_idx_00000062 ON t1(b);
  SCAN TABLE t1 USING INDEX t1_idx_00000062
}

do_setup_rec_test $tn.5 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT a FROM t1 WHERE a=? ORDER BY b;
} {
  CREATE INDEX t1_idx_000123a7 ON t1(a, b);
  SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?)
}

do_setup_rec_test $tn.6 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT min(a) FROM t1
} {
  CREATE INDEX t1_idx_00000061 ON t1(a);
  SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061
}

do_setup_rec_test $tn.7 {
  CREATE TABLE t1(a, b, c);
} {
  SELECT * FROM t1 ORDER BY a, b, c;
} {
  CREATE INDEX t1_idx_033e95fe ON t1(a, b, c);
  SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe
}

#do_setup_rec_test $tn.1.8 {
#  CREATE TABLE t1(a, b, c);
#} {
#  SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
#} {
#  CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c);
#  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222
#}

do_setup_rec_test $tn.8.1 {
  CREATE TABLE t1(a COLLATE NOCase, b, c);
} {
  SELECT * FROM t1 WHERE a=?
} {
  CREATE INDEX t1_idx_00000061 ON t1(a);
  SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?)
}
do_setup_rec_test $tn.8.2 {
  CREATE TABLE t1(a, b COLLATE nocase, c);
} {
  SELECT * FROM t1 ORDER BY a ASC, b DESC, c ASC;
} {
  CREATE INDEX t1_idx_5cb97285 ON t1(a, b DESC, c);
  SCAN TABLE t1 USING COVERING INDEX t1_idx_5cb97285
}


# Tables with names that require quotes.
#
do_setup_rec_test $tn.9.1 {
  CREATE TABLE "t t"(a, b, c);
} {
  SELECT * FROM "t t" WHERE a=?
} {
  CREATE INDEX 't t_idx_00000061' ON 't t'(a);
  SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?) 
}

do_setup_rec_test $tn.9.2 {
  CREATE TABLE "t t"(a, b, c);
} {
  SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
} {
  CREATE INDEX 't t_idx_00000062' ON 't t'(b);
  SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?)
}

# Columns with names that require quotes.
#
do_setup_rec_test $tn.10.1 {
  CREATE TABLE t3(a, "b b", c);
} {
  SELECT * FROM t3 WHERE "b b" = ?
} {
  CREATE INDEX t3_idx_00050c52 ON t3('b b');
  SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?)
}

do_setup_rec_test $tn.10.2 {
  CREATE TABLE t3(a, "b b", c);
} {
  SELECT * FROM t3 ORDER BY "b b"
} {
  CREATE INDEX t3_idx_00050c52 ON t3('b b');
  SCAN TABLE t3 USING INDEX t3_idx_00050c52
}

# Transitive constraints
#
do_setup_rec_test $tn.11.1 {
  CREATE TABLE t5(a, b);
  CREATE TABLE t6(c, d);
} {
  SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
} {
  CREATE INDEX t5_idx_000123a7 ON t5(a, b);
  CREATE INDEX t6_idx_00000063 ON t6(c);
  SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?) 
  SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
}

# OR terms.
#
do_setup_rec_test $tn.12.1 {
  CREATE TABLE t7(a, b);
} {
  SELECT * FROM t7 WHERE a=? OR b=?
} {
  CREATE INDEX t7_idx_00000062 ON t7(b);
  CREATE INDEX t7_idx_00000061 ON t7(a);
  SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?) 
  SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
}

# rowid terms.
#
do_setup_rec_test $tn.13.1 {
  CREATE TABLE t8(a, b);
} {
  SELECT * FROM t8 WHERE rowid=?
} {
  (no new indexes)
  SEARCH TABLE t8 USING INTEGER PRIMARY KEY (rowid=?)
}
do_setup_rec_test $tn.13.2 {
  CREATE TABLE t8(a, b);
} {
  SELECT * FROM t8 ORDER BY rowid
} {
  (no new indexes)
  SCAN TABLE t8
}
do_setup_rec_test $tn.13.3 {
  CREATE TABLE t8(a, b);
} {
  SELECT * FROM t8 WHERE a=? ORDER BY rowid
} {
  CREATE INDEX t8_idx_00000061 ON t8(a); 
  SEARCH TABLE t8 USING INDEX t8_idx_00000061 (a=?)
}

# Triggers
#
do_setup_rec_test $tn.14 {
  CREATE TABLE t9(a, b, c);
  CREATE TABLE t10(a, b, c);
  CREATE TRIGGER t9t AFTER INSERT ON t9 BEGIN
    UPDATE t10 SET a=new.a WHERE b = new.b;
  END;
} {
  INSERT INTO t9 VALUES(?, ?, ?);
} {
  CREATE INDEX t10_idx_00000062 ON t10(b); 
  -- TRIGGER t9t
  SEARCH TABLE t10 USING INDEX t10_idx_00000062 (b=?)
}

do_setup_rec_test $tn.15 {
  CREATE TABLE t1(a, b);
  CREATE TABLE t2(c, d);

  WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
  INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s;

  WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
  INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s;
} {
  SELECT * FROM t2, t1 WHERE b=? AND d=? AND t2.rowid=t1.rowid
} {
  CREATE INDEX t2_idx_00000064 ON t2(d);
  SEARCH TABLE t2 USING INDEX t2_idx_00000064 (d=?) 
  SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
}

do_setup_rec_test $tn.16 {
  CREATE TABLE t1(a, b);
} {
  SELECT * FROM t1 WHERE b IS NOT NULL;
} {
  (no new indexes)
  SCAN TABLE t1
}

}

proc do_candidates_test {tn sql res} {
  set res [squish [string trim $res]]

Changes to ext/expert/sqlite3expert.c.
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
    IdxHashEntry *pEntry;
    sqlite3_stmt *pExplain = 0;
    idxHashClear(&hIdx);
    rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr,
        "EXPLAIN QUERY PLAN %s", pStmt->zSql
    );
    while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
      int iSelectid = sqlite3_column_int(pExplain, 0);
      int iOrder = sqlite3_column_int(pExplain, 1);
      int iFrom = sqlite3_column_int(pExplain, 2);
      const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
      int nDetail = STRLEN(zDetail);
      int i;

      for(i=0; i<nDetail; i++){
        const char *zIdx = 0;
        if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){







|
|
|







1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
    IdxHashEntry *pEntry;
    sqlite3_stmt *pExplain = 0;
    idxHashClear(&hIdx);
    rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr,
        "EXPLAIN QUERY PLAN %s", pStmt->zSql
    );
    while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
      /* int iId = sqlite3_column_int(pExplain, 0); */
      /* int iParent = sqlite3_column_int(pExplain, 1); */
      /* int iNotUsed = sqlite3_column_int(pExplain, 2); */
      const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
      int nDetail = STRLEN(zDetail);
      int i;

      for(i=0; i<nDetail; i++){
        const char *zIdx = 0;
        if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
            idxHashAdd(&rc, &hIdx, zSql, 0);
            if( rc ) goto find_indexes_out;
          }
          break;
        }
      }

      pStmt->zEQP = idxAppendText(&rc, pStmt->zEQP, "%d|%d|%d|%s\n", 
          iSelectid, iOrder, iFrom, zDetail
      );
    }

    for(pEntry=hIdx.pFirst; pEntry; pEntry=pEntry->pNext){
      pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "%s;\n", pEntry->zKey);
    }

    idxFinalize(&rc, pExplain);







|
<
<







1148
1149
1150
1151
1152
1153
1154
1155


1156
1157
1158
1159
1160
1161
1162
            idxHashAdd(&rc, &hIdx, zSql, 0);
            if( rc ) goto find_indexes_out;
          }
          break;
        }
      }

      pStmt->zEQP = idxAppendText(&rc, pStmt->zEQP, "%s\n", zDetail);


    }

    for(pEntry=hIdx.pFirst; pEntry; pEntry=pEntry->pNext){
      pStmt->zIdx = idxAppendText(&rc, pStmt->zIdx, "%s;\n", pEntry->zKey);
    }

    idxFinalize(&rc, pExplain);
Changes to ext/fts5/test/fts5plan.test.
56
57
58
59
60
61
62
63
64
65
  QUERY PLAN
  |--SCAN TABLE f1 VIRTUAL TABLE INDEX 0:
  `--USE TEMP B-TREE FOR ORDER BY
}

do_eqp_test 1.5 {
  SELECT * FROM f1 WHERE rank MATCH ?
} {SCAN TABLE f1 VIRTUAL TABLE INDEX 2:

finish_test







|


56
57
58
59
60
61
62
63
64
65
  QUERY PLAN
  |--SCAN TABLE f1 VIRTUAL TABLE INDEX 0:
  `--USE TEMP B-TREE FOR ORDER BY
}

do_eqp_test 1.5 {
  SELECT * FROM f1 WHERE rank MATCH ?
} {SCAN TABLE f1 VIRTUAL TABLE INDEX 2:}

finish_test
Changes to ext/rtree/rtree6.test.
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
do_test rtree6-1.5 {
  rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10}
} {C0}

do_eqp_test rtree6.2.1 {
  SELECT * FROM t1,t2 WHERE k=+ii AND x1<10
} {

  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0} 
  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
}

do_eqp_test rtree6.2.2 {
  SELECT * FROM t1,t2 WHERE k=ii AND x1<10
} {

  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0} 
  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
}

do_eqp_test rtree6.2.3 {
  SELECT * FROM t1,t2 WHERE k=ii
} {

  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:} 
  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
}

do_eqp_test rtree6.2.4.1 {
  SELECT * FROM t1,t2 WHERE v=+ii and x1<10 and x2>10
} {

  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1} 
  0 1 1 {SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)}
}
do_eqp_test rtree6.2.4.2 {
  SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
} {

  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1} 
  0 1 1 {SEARCH TABLE t2 USING AUTOMATIC PARTIAL COVERING INDEX (v=?)}
}

do_eqp_test rtree6.2.5 {
  SELECT * FROM t1,t2 WHERE k=ii AND x1<v
} {

  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:} 
  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
}

do_execsql_test rtree6-3.1 {
  CREATE VIRTUAL TABLE t3 USING rtree(id, x1, x2, y1, y2);
  INSERT INTO t3 VALUES(NULL, 1, 1, 2, 2);
  SELECT * FROM t3 WHERE 
    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 







>
|
|





>
|
|





>
|
|





>
|
|




>
|
|





>
|
|







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
do_test rtree6-1.5 {
  rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10}
} {C0}

do_eqp_test rtree6.2.1 {
  SELECT * FROM t1,t2 WHERE k=+ii AND x1<10
} {
  QUERY PLAN
  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0
  `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
}

do_eqp_test rtree6.2.2 {
  SELECT * FROM t1,t2 WHERE k=ii AND x1<10
} {
  QUERY PLAN
  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0
  `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
}

do_eqp_test rtree6.2.3 {
  SELECT * FROM t1,t2 WHERE k=ii
} {
  QUERY PLAN
  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:
  `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
}

do_eqp_test rtree6.2.4.1 {
  SELECT * FROM t1,t2 WHERE v=+ii and x1<10 and x2>10
} {
  QUERY PLAN
  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1
  `--SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)
}
do_eqp_test rtree6.2.4.2 {
  SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
} {
  QUERY PLAN
  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1
  `--SEARCH TABLE t2 USING AUTOMATIC PARTIAL COVERING INDEX (v=?)
}

do_eqp_test rtree6.2.5 {
  SELECT * FROM t1,t2 WHERE k=ii AND x1<v
} {
  QUERY PLAN
  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 2:
  `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
}

do_execsql_test rtree6-3.1 {
  CREATE VIRTUAL TABLE t3 USING rtree(id, x1, x2, y1, y2);
  INSERT INTO t3 VALUES(NULL, 1, 1, 2, 2);
  SELECT * FROM t3 WHERE 
    x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND x1>0.5 AND 
Changes to ext/rtree/rtreeC.test.
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
  CREATE TABLE t(x, y);
}

do_eqp_test 1.1 {
  SELECT * FROM r_tree, t 
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
} {

  0 0 1 {SCAN TABLE t}
  0 1 0 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0}
}

do_eqp_test 1.2 {
  SELECT * FROM t, r_tree
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
} {

  0 0 0 {SCAN TABLE t}
  0 1 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0}
}

do_eqp_test 1.3 {
  SELECT * FROM t, r_tree
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND ?<=max_y
} {

  0 0 0 {SCAN TABLE t}
  0 1 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0}
}

do_eqp_test 1.5 {
  SELECT * FROM t, r_tree
} {

  0 0 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:}
  0 1 0 {SCAN TABLE t} 
}

do_execsql_test 2.0 {
  INSERT INTO t VALUES(0, 0);
  INSERT INTO t VALUES(0, 1);
  INSERT INTO t VALUES(0, 2);
  INSERT INTO t VALUES(0, 3);







>
|
|






>
|
|






>
|
|





>
|
|







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
  CREATE TABLE t(x, y);
}

do_eqp_test 1.1 {
  SELECT * FROM r_tree, t 
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
} {
  QUERY PLAN
  |--SCAN TABLE t
  `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0
}

do_eqp_test 1.2 {
  SELECT * FROM t, r_tree
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
} {
  QUERY PLAN
  |--SCAN TABLE t
  `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0
}

do_eqp_test 1.3 {
  SELECT * FROM t, r_tree
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND ?<=max_y
} {
  QUERY PLAN
  |--SCAN TABLE t
  `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0
}

do_eqp_test 1.5 {
  SELECT * FROM t, r_tree
} {
  QUERY PLAN
  |--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:
  `--SCAN TABLE t
}

do_execsql_test 2.0 {
  INSERT INTO t VALUES(0, 0);
  INSERT INTO t VALUES(0, 1);
  INSERT INTO t VALUES(0, 2);
  INSERT INTO t VALUES(0, 3);
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
db close
sqlite3 db test.db

do_eqp_test 2.1 {
  SELECT * FROM r_tree, t 
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
} {

  0 0 1 {SCAN TABLE t}
  0 1 0 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0}
}

do_eqp_test 2.2 {
  SELECT * FROM t, r_tree
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
} {

  0 0 0 {SCAN TABLE t}
  0 1 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0}
}

do_eqp_test 2.3 {
  SELECT * FROM t, r_tree
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND ?<=max_y
} {

  0 0 0 {SCAN TABLE t}
  0 1 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0}
}

do_eqp_test 2.5 {
  SELECT * FROM t, r_tree
} {

  0 0 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:}
  0 1 0 {SCAN TABLE t} 
}

#-------------------------------------------------------------------------
# Test that the special CROSS JOIN handling works with rtree tables.
#
do_execsql_test 3.1 {
  CREATE TABLE t1(x);
  CREATE TABLE t2(y);
  CREATE VIRTUAL TABLE t3 USING rtree(z, x1,x2, y1,y2);
}

do_eqp_test 3.2.1 { SELECT * FROM t1 CROSS JOIN t2 } {

  0 0 0 {SCAN TABLE t1} 
  0 1 1 {SCAN TABLE t2}
}
do_eqp_test 3.2.2 { SELECT * FROM t2 CROSS JOIN t1 } {

  0 0 0 {SCAN TABLE t2} 0 1 1 {SCAN TABLE t1}

}

do_eqp_test 3.3.1 { SELECT * FROM t1 CROSS JOIN t3 } {

  0 0 0 {SCAN TABLE t1}
  0 1 1 {SCAN TABLE t3 VIRTUAL TABLE INDEX 2:} 
}
do_eqp_test 3.3.2 { SELECT * FROM t3 CROSS JOIN t1 } {

  0 0 0 {SCAN TABLE t3 VIRTUAL TABLE INDEX 2:} 
  0 1 1 {SCAN TABLE t1}
}

#--------------------------------------------------------------------
# Test that LEFT JOINs are not reordered if the right-hand-side is
# a virtual table.
#
reset_db







>
|
|






>
|
|






>
|
|





>
|
|












>
|
|


>
|
>



>
|
|


>
|
|







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
db close
sqlite3 db test.db

do_eqp_test 2.1 {
  SELECT * FROM r_tree, t 
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
} {
  QUERY PLAN
  |--SCAN TABLE t
  `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0
}

do_eqp_test 2.2 {
  SELECT * FROM t, r_tree
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y
} {
  QUERY PLAN
  |--SCAN TABLE t
  `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0
}

do_eqp_test 2.3 {
  SELECT * FROM t, r_tree
  WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND ?<=max_y
} {
  QUERY PLAN
  |--SCAN TABLE t
  `--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:D3B2D1B0
}

do_eqp_test 2.5 {
  SELECT * FROM t, r_tree
} {
  QUERY PLAN
  |--SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:
  `--SCAN TABLE t
}

#-------------------------------------------------------------------------
# Test that the special CROSS JOIN handling works with rtree tables.
#
do_execsql_test 3.1 {
  CREATE TABLE t1(x);
  CREATE TABLE t2(y);
  CREATE VIRTUAL TABLE t3 USING rtree(z, x1,x2, y1,y2);
}

do_eqp_test 3.2.1 { SELECT * FROM t1 CROSS JOIN t2 } {
  QUERY PLAN
  |--SCAN TABLE t1
  `--SCAN TABLE t2
}
do_eqp_test 3.2.2 { SELECT * FROM t2 CROSS JOIN t1 } {
  QUERY PLAN
  |--SCAN TABLE t2
  `--SCAN TABLE t1
}

do_eqp_test 3.3.1 { SELECT * FROM t1 CROSS JOIN t3 } {
  QUERY PLAN
  |--SCAN TABLE t1
  `--SCAN TABLE t3 VIRTUAL TABLE INDEX 2:
}
do_eqp_test 3.3.2 { SELECT * FROM t3 CROSS JOIN t1 } {
  QUERY PLAN
  |--SCAN TABLE t3 VIRTUAL TABLE INDEX 2:
  `--SCAN TABLE t1
}

#--------------------------------------------------------------------
# Test that LEFT JOINs are not reordered if the right-hand-side is
# a virtual table.
#
reset_db
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

# First test a query with no ANALYZE data at all. The outer loop is
# real table "t1".
#
do_eqp_test 5.2 {
  SELECT * FROM t1, rt WHERE x==id;
} {

  0 0 0 {SCAN TABLE t1} 
  0 1 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 1:}
}

# Now create enough ANALYZE data to tell SQLite that virtual table "rt"
# contains very few rows. This causes it to move "rt" to the outer loop.
#
do_execsql_test 5.3 {
  ANALYZE;
  DELETE FROM sqlite_stat1 WHERE tbl='t1';
}
db close
sqlite3 db test.db
do_eqp_test 5.4 {
  SELECT * FROM t1, rt WHERE x==id;
} {

  0 0 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 2:} 
  0 1 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (x=?)}
}

# Delete the ANALYZE data. "t1" should be the outer loop again.
#
do_execsql_test 5.5 { DROP TABLE sqlite_stat1; }
db close
sqlite3 db test.db
do_eqp_test 5.6 {
  SELECT * FROM t1, rt WHERE x==id;
} {

  0 0 0 {SCAN TABLE t1} 
  0 1 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 1:}
}

# This time create and attach a database that contains ANALYZE data for
# tables of the same names as those used internally by virtual table
# "rt". Check that the rtree module is not fooled into using this data.
# Table "t1" should remain the outer loop.
#







>
|
|














>
|
|










>
|
|







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

# First test a query with no ANALYZE data at all. The outer loop is
# real table "t1".
#
do_eqp_test 5.2 {
  SELECT * FROM t1, rt WHERE x==id;
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--SCAN TABLE rt VIRTUAL TABLE INDEX 1:
}

# Now create enough ANALYZE data to tell SQLite that virtual table "rt"
# contains very few rows. This causes it to move "rt" to the outer loop.
#
do_execsql_test 5.3 {
  ANALYZE;
  DELETE FROM sqlite_stat1 WHERE tbl='t1';
}
db close
sqlite3 db test.db
do_eqp_test 5.4 {
  SELECT * FROM t1, rt WHERE x==id;
} {
  QUERY PLAN
  |--SCAN TABLE rt VIRTUAL TABLE INDEX 2:
  `--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (x=?)
}

# Delete the ANALYZE data. "t1" should be the outer loop again.
#
do_execsql_test 5.5 { DROP TABLE sqlite_stat1; }
db close
sqlite3 db test.db
do_eqp_test 5.6 {
  SELECT * FROM t1, rt WHERE x==id;
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--SCAN TABLE rt VIRTUAL TABLE INDEX 1:
}

# This time create and attach a database that contains ANALYZE data for
# tables of the same names as those used internally by virtual table
# "rt". Check that the rtree module is not fooled into using this data.
# Table "t1" should remain the outer loop.
#
237
238
239
240
241
242
243

244
245
246
247
248
249
250
251
252
  db close
  sqlite3 db test.db
  execsql { ATTACH 'test.db2' AS aux; }
} {}
do_eqp_test 5.8 {
  SELECT * FROM t1, rt WHERE x==id;
} {

  0 0 0 {SCAN TABLE t1} 
  0 1 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 1:}
}

#--------------------------------------------------------------------
# Test that having a second connection drop the sqlite_stat1 table
# before it is required by rtreeConnect() does not cause problems.
#
ifcapable rtree {







>
|
|







253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
  db close
  sqlite3 db test.db
  execsql { ATTACH 'test.db2' AS aux; }
} {}
do_eqp_test 5.8 {
  SELECT * FROM t1, rt WHERE x==id;
} {
  QUERY PLAN
  |--SCAN TABLE t1
  `--SCAN TABLE rt VIRTUAL TABLE INDEX 1:
}

#--------------------------------------------------------------------
# Test that having a second connection drop the sqlite_stat1 table
# before it is required by rtreeConnect() does not cause problems.
#
ifcapable rtree {
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
346
347

348
349
350

351
352
353
354
  INSERT INTO rt VALUES(1, 2, 7, 12, 14);      -- Not a hit
  INSERT INTO rt VALUES(2, 2, 7, 8, 12);       -- A hit!
  INSERT INTO rt VALUES(3, 7, 11, 8, 12);      -- Not a hit!
  INSERT INTO rt VALUES(4, 5, 5, 10, 10);      -- A hit!

}

proc do_eqp_execsql_test {tn sql res} {
  set query "EXPLAIN QUERY PLAN $sql ; $sql "
  uplevel [list do_execsql_test $tn $query $res]
}

do_eqp_execsql_test 7.1 {
  SELECT id FROM xdir, rt, ydir 
  ON (y1 BETWEEN ymin AND ymax)
  WHERE (x1 BETWEEN xmin AND xmax);
} {

  0 0 0 {SCAN TABLE xdir} 
  0 1 2 {SCAN TABLE ydir} 
  0 2 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 2:B2D3B0D1}

  2 4
}

do_eqp_execsql_test 7.2 {
  SELECT * FROM xdir, rt LEFT JOIN ydir 
  ON (y1 BETWEEN ymin AND ymax)
  WHERE (x1 BETWEEN xmin AND xmax);
} {

  0 0 0 {SCAN TABLE xdir} 
  0 1 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1}
  0 2 2 {SCAN TABLE ydir} 

  5 1 2 7 12 14 {}
  5 2 2 7  8 12 10
  5 4 5 5 10 10 10
}

do_eqp_execsql_test 7.3 {
  SELECT id FROM xdir, rt CROSS JOIN ydir 
  ON (y1 BETWEEN ymin AND ymax)
  WHERE (x1 BETWEEN xmin AND xmax);
} {

  0 0 0 {SCAN TABLE xdir} 
  0 1 1 {SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1}
  0 2 2 {SCAN TABLE ydir} 

  2 4
}

do_eqp_execsql_test 7.4 {
  SELECT id FROM rt, xdir CROSS JOIN ydir 
  ON (y1 BETWEEN ymin AND ymax)
  WHERE (x1 BETWEEN xmin AND xmax);
} {

  0 0 1 {SCAN TABLE xdir} 
  0 1 0 {SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1}
  0 2 2 {SCAN TABLE ydir} 

  2 4
}

finish_test







|
|
|







>
|
|
|
>








>
|
|
|
|










>
|
|
|
>








>
|
|
|
>




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
346
347
348
349
350
351
352
353
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
  INSERT INTO rt VALUES(1, 2, 7, 12, 14);      -- Not a hit
  INSERT INTO rt VALUES(2, 2, 7, 8, 12);       -- A hit!
  INSERT INTO rt VALUES(3, 7, 11, 8, 12);      -- Not a hit!
  INSERT INTO rt VALUES(4, 5, 5, 10, 10);      -- A hit!

}

proc do_eqp_execsql_test {tn sql res1 res2} {
  do_eqp_test $tn.1 $sql $res1
  do_execsql_test $tn.2 $sql $res2
}

do_eqp_execsql_test 7.1 {
  SELECT id FROM xdir, rt, ydir 
  ON (y1 BETWEEN ymin AND ymax)
  WHERE (x1 BETWEEN xmin AND xmax);
} {
  QUERY PLAN
  |--SCAN TABLE xdir
  |--SCAN TABLE ydir
  `--SCAN TABLE rt VIRTUAL TABLE INDEX 2:B2D3B0D1
} {
  2 4
}

do_eqp_execsql_test 7.2 {
  SELECT * FROM xdir, rt LEFT JOIN ydir 
  ON (y1 BETWEEN ymin AND ymax)
  WHERE (x1 BETWEEN xmin AND xmax);
} {
  QUERY PLAN
  |--SCAN TABLE xdir
  |--SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1
  `--SCAN TABLE ydir
} {
  5 1 2 7 12 14 {}
  5 2 2 7  8 12 10
  5 4 5 5 10 10 10
}

do_eqp_execsql_test 7.3 {
  SELECT id FROM xdir, rt CROSS JOIN ydir 
  ON (y1 BETWEEN ymin AND ymax)
  WHERE (x1 BETWEEN xmin AND xmax);
} {
  QUERY PLAN
  |--SCAN TABLE xdir
  |--SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1
  `--SCAN TABLE ydir
} {
  2 4
}

do_eqp_execsql_test 7.4 {
  SELECT id FROM rt, xdir CROSS JOIN ydir 
  ON (y1 BETWEEN ymin AND ymax)
  WHERE (x1 BETWEEN xmin AND xmax);
} {
  QUERY PLAN
  |--SCAN TABLE xdir
  |--SCAN TABLE rt VIRTUAL TABLE INDEX 2:B0D1
  `--SCAN TABLE ydir
} {
  2 4
}

finish_test
Changes to test/analyze4.test.
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
    INSERT INTO t1 SELECT a+32, b FROM t1;
    INSERT INTO t1 SELECT a+64, b FROM t1;
    ANALYZE;
  }

  # Should choose the t1a index since it is more specific than t1b.
  db eval {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=5 AND b IS NULL}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}

# Verify that the t1b index shows that it does not narrow down the
# search any at all.
#
do_test analyze4-1.1 {
  db eval {
    SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;







|







34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
    INSERT INTO t1 SELECT a+32, b FROM t1;
    INSERT INTO t1 SELECT a+64, b FROM t1;
    ANALYZE;
  }

  # Should choose the t1a index since it is more specific than t1b.
  db eval {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=5 AND b IS NULL}
} {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/}

# Verify that the t1b index shows that it does not narrow down the
# search any at all.
#
do_test analyze4-1.1 {
  db eval {
    SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
Changes to test/analyze6.test.
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
# The lowest cost plan is to scan CAT and for each integer there, do a single
# lookup of the first corresponding entry in EV then read off the equal values
# in EV.  (Prior to the 2011-03-04 enhancement to where.c, this query would
# have used EV for the outer loop instead of CAT - which was about 3x slower.)
#
do_test analyze6-1.1 {
  eqp {SELECT count(*) FROM ev, cat WHERE x=y}
} {0 0 1 {SCAN TABLE cat USING COVERING INDEX catx} 0 1 0 {SEARCH TABLE ev USING COVERING INDEX evy (y=?)}}

# The same plan is chosen regardless of the order of the tables in the
# FROM clause.
#
do_test analyze6-1.2 {
  eqp {SELECT count(*) FROM cat, ev WHERE x=y}



} {0 0 0 {SCAN TABLE cat USING COVERING INDEX catx} 0 1 1 {SEARCH TABLE ev USING COVERING INDEX evy (y=?)}}



# Ticket [83ea97620bd3101645138b7b0e71c12c5498fe3d] 2011-03-30
# If ANALYZE is run on an empty table, make sure indices are used
# on the table.
#
do_test analyze6-2.1 {
  execsql {
    CREATE TABLE t201(x INTEGER PRIMARY KEY, y UNIQUE, z);
    CREATE INDEX t201z ON t201(z);
    ANALYZE;
  }
  eqp {SELECT * FROM t201 WHERE z=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?)}}
do_test analyze6-2.2 {
  eqp {SELECT * FROM t201 WHERE y=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)}}
do_test analyze6-2.3 {
  eqp {SELECT * FROM t201 WHERE x=5}
} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)}}
do_test analyze6-2.4 {
  execsql {
    INSERT INTO t201 VALUES(1,2,3),(2,3,4),(3,4,5);
    ANALYZE t201;
  }
  eqp {SELECT * FROM t201 WHERE z=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?)}}
do_test analyze6-2.5 {
  eqp {SELECT * FROM t201 WHERE y=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)}}
do_test analyze6-2.6 {
  eqp {SELECT * FROM t201 WHERE x=5}
} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)}}
do_test analyze6-2.7 {
  execsql {
    INSERT INTO t201 VALUES(4,5,7);
    INSERT INTO t201 SELECT x+100, y+100, z+100 FROM t201;
    INSERT INTO t201 SELECT x+200, y+200, z+200 FROM t201;
    INSERT INTO t201 SELECT x+400, y+400, z+400 FROM t201;
    ANALYZE t201;
  }
  eqp {SELECT * FROM t201 WHERE z=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX t201z (z=?)}}
do_test analyze6-2.8 {
  eqp {SELECT * FROM t201 WHERE y=5}
} {0 0 0 {SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)}}
do_test analyze6-2.9 {
  eqp {SELECT * FROM t201 WHERE x=5}
} {0 0 0 {SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)}}

finish_test







|




|
|
>
>
>
|
>













|


|


|






|


|


|









|


|


|


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
# The lowest cost plan is to scan CAT and for each integer there, do a single
# lookup of the first corresponding entry in EV then read off the equal values
# in EV.  (Prior to the 2011-03-04 enhancement to where.c, this query would
# have used EV for the outer loop instead of CAT - which was about 3x slower.)
#
do_test analyze6-1.1 {
  eqp {SELECT count(*) FROM ev, cat WHERE x=y}
} {/*SCAN TABLE cat USING COVERING INDEX catx*SEARCH TABLE ev USING COVERING INDEX evy (y=?)*/}

# The same plan is chosen regardless of the order of the tables in the
# FROM clause.
#
do_eqp_test analyze6-1.2 {
  SELECT count(*) FROM cat, ev WHERE x=y
} {
  QUERY PLAN
  |--SCAN TABLE cat USING COVERING INDEX catx
  `--SEARCH TABLE ev USING COVERING INDEX evy (y=?)
}


# Ticket [83ea97620bd3101645138b7b0e71c12c5498fe3d] 2011-03-30
# If ANALYZE is run on an empty table, make sure indices are used
# on the table.
#
do_test analyze6-2.1 {
  execsql {
    CREATE TABLE t201(x INTEGER PRIMARY KEY, y UNIQUE, z);
    CREATE INDEX t201z ON t201(z);
    ANALYZE;
  }
  eqp {SELECT * FROM t201 WHERE z=5}
} {/*SEARCH TABLE t201 USING INDEX t201z (z=?)*/}
do_test analyze6-2.2 {
  eqp {SELECT * FROM t201 WHERE y=5}
} {/*SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)*/}
do_test analyze6-2.3 {
  eqp {SELECT * FROM t201 WHERE x=5}
} {/*SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)*/}
do_test analyze6-2.4 {
  execsql {
    INSERT INTO t201 VALUES(1,2,3),(2,3,4),(3,4,5);
    ANALYZE t201;
  }
  eqp {SELECT * FROM t201 WHERE z=5}
} {/*SEARCH TABLE t201 USING INDEX t201z (z=?)*/}
do_test analyze6-2.5 {
  eqp {SELECT * FROM t201 WHERE y=5}
} {/*SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)*/}
do_test analyze6-2.6 {
  eqp {SELECT * FROM t201 WHERE x=5}
} {/*SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)*/}
do_test analyze6-2.7 {
  execsql {
    INSERT INTO t201 VALUES(4,5,7);
    INSERT INTO t201 SELECT x+100, y+100, z+100 FROM t201;
    INSERT INTO t201 SELECT x+200, y+200, z+200 FROM t201;
    INSERT INTO t201 SELECT x+400, y+400, z+400 FROM t201;
    ANALYZE t201;
  }
  eqp {SELECT * FROM t201 WHERE z=5}
} {/*SEARCH TABLE t201 USING INDEX t201z (z=?)*/}
do_test analyze6-2.8 {
  eqp {SELECT * FROM t201 WHERE y=5}
} {/*SEARCH TABLE t201 USING INDEX sqlite_autoindex_t201_1 (y=?)*/}
do_test analyze6-2.9 {
  eqp {SELECT * FROM t201 WHERE x=5}
} {/*SEARCH TABLE t201 USING INTEGER PRIMARY KEY (rowid=?)*/}

finish_test
Changes to test/analyze7.test.
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
    CREATE INDEX t1b ON t1(b);
    CREATE INDEX t1cd ON t1(c,d);
    CREATE VIRTUAL TABLE nums USING wholenumber;
    INSERT INTO t1 SELECT value, value, value/100, value FROM nums
                    WHERE value BETWEEN 1 AND 256;
    EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;
  }
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
do_test analyze7-1.1 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
do_test analyze7-1.2 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}

# Run an analyze on one of the three indices.  Verify that this
# effects the row-count estimate on the one query that uses that
# one index.
#
do_test analyze7-2.0 {
  execsql {ANALYZE t1a;}
  db cache flush
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
do_test analyze7-2.1 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
do_test analyze7-2.2 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}

# Verify that since the query planner now things that t1a is more
# selective than t1b, it prefers to use t1a.
#
do_test analyze7-2.3 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}

# Run an analysis on another of the three indices.  Verify  that this
# new analysis works and does not disrupt the previous analysis.
#
do_test analyze7-3.0 {
  execsql {ANALYZE t1cd;}
  db cache flush;
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
do_test analyze7-3.1 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
do_test analyze7-3.2.1 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
ifcapable stat4||stat3 {
  # If ENABLE_STAT4 is defined, SQLite comes up with a different estimated
  # row count for (c=2) than it does for (c=?).
  do_test analyze7-3.2.2 {
    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
} else {
  # If ENABLE_STAT4 is not defined, the expected row count for (c=2) is the
  # same as that for (c=?).
  do_test analyze7-3.2.3 {
    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?)}}
}
do_test analyze7-3.3 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}

ifcapable {!stat4 && !stat3} {
  do_test analyze7-3.4 {
    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}
  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
  do_test analyze7-3.5 {
    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123}
  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
}
do_test analyze7-3.6 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?)}}

finish_test







|


|


|









|


|


|






|








|


|


|





|





|



|




|


|



|


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
    CREATE INDEX t1b ON t1(b);
    CREATE INDEX t1cd ON t1(c,d);
    CREATE VIRTUAL TABLE nums USING wholenumber;
    INSERT INTO t1 SELECT value, value, value/100, value FROM nums
                    WHERE value BETWEEN 1 AND 256;
    EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;
  }
} {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/}
do_test analyze7-1.1 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
} {/*SEARCH TABLE t1 USING INDEX t1b (b=?)*/}
do_test analyze7-1.2 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
} {/*SEARCH TABLE t1 USING INDEX t1cd (c=?)*/}

# Run an analyze on one of the three indices.  Verify that this
# effects the row-count estimate on the one query that uses that
# one index.
#
do_test analyze7-2.0 {
  execsql {ANALYZE t1a;}
  db cache flush
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}
} {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/}
do_test analyze7-2.1 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
} {/*SEARCH TABLE t1 USING INDEX t1b (b=?)*/}
do_test analyze7-2.2 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
} {/*SEARCH TABLE t1 USING INDEX t1cd (c=?)*/}

# Verify that since the query planner now things that t1a is more
# selective than t1b, it prefers to use t1a.
#
do_test analyze7-2.3 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
} {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/}

# Run an analysis on another of the three indices.  Verify  that this
# new analysis works and does not disrupt the previous analysis.
#
do_test analyze7-3.0 {
  execsql {ANALYZE t1cd;}
  db cache flush;
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}
} {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/}
do_test analyze7-3.1 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
} {/*SEARCH TABLE t1 USING INDEX t1b (b=?)*/}
do_test analyze7-3.2.1 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;}
} {/*SEARCH TABLE t1 USING INDEX t1cd (c=?)*/}
ifcapable stat4||stat3 {
  # If ENABLE_STAT4 is defined, SQLite comes up with a different estimated
  # row count for (c=2) than it does for (c=?).
  do_test analyze7-3.2.2 {
    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
  } {/*SEARCH TABLE t1 USING INDEX t1cd (c=?)*/}
} else {
  # If ENABLE_STAT4 is not defined, the expected row count for (c=2) is the
  # same as that for (c=?).
  do_test analyze7-3.2.3 {
    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
  } {/*SEARCH TABLE t1 USING INDEX t1cd (c=?)*/}
}
do_test analyze7-3.3 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
} {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/}

ifcapable {!stat4 && !stat3} {
  do_test analyze7-3.4 {
    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}
  } {/*SEARCH TABLE t1 USING INDEX t1b (b=?)*/}
  do_test analyze7-3.5 {
    execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123}
  } {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/}
}
do_test analyze7-3.6 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123}
} {/*SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?)*/}

finish_test
Changes to test/analyze8.test.
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
# with a==100.  And so for those cases, choose the t1b index.
#
# Buf ro a==99 and a==101, there are far fewer rows so choose
# the t1a index.
#
do_test 1.1 {
  eqp {SELECT * FROM t1 WHERE a=100 AND b=55}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
do_test 1.2 {
  eqp {SELECT * FROM t1 WHERE a=99 AND b=55}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
do_test 1.3 {
  eqp {SELECT * FROM t1 WHERE a=101 AND b=55}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
do_test 1.4 {
  eqp {SELECT * FROM t1 WHERE a=100 AND b=56}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
do_test 1.5 {
  eqp {SELECT * FROM t1 WHERE a=99 AND b=56}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
do_test 1.6 {
  eqp {SELECT * FROM t1 WHERE a=101 AND b=56}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
do_test 2.1 {
  eqp {SELECT * FROM t1 WHERE a=100 AND b BETWEEN 50 AND 54}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}}

# There are many more values of c between 0 and 100000 than there are
# between 800000 and 900000.  So t1c is more selective for the latter
# range.
# 
# Test 3.2 is a little unstable. It depends on the planner estimating
# that (b BETWEEN 30 AND 34) will match more rows than (c BETWEEN
# 800000 AND 900000). Which is a pretty close call (50 vs. 32), so
# the planner could get it wrong with an unlucky set of samples. This
# case happens to work, but others ("b BETWEEN 40 AND 44" for example) 
# will fail.
#
do_execsql_test 3.0 {
  SELECT count(*) FROM t1 WHERE b BETWEEN 30 AND 34;
  SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 100000;
  SELECT count(*) FROM t1 WHERE c BETWEEN 800000 AND 900000;
} {50 376 32}
do_test 3.1 {
  eqp {SELECT * FROM t1 WHERE b BETWEEN 30 AND 34 AND c BETWEEN 0 AND 100000}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}}
do_test 3.2 {
  eqp {SELECT * FROM t1
       WHERE b BETWEEN 30 AND 34 AND c BETWEEN 800000 AND 900000}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}}
do_test 3.3 {
  eqp {SELECT * FROM t1 WHERE a=100 AND c BETWEEN 0 AND 100000}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?)}}
do_test 3.4 {
  eqp {SELECT * FROM t1
       WHERE a=100 AND c BETWEEN 800000 AND 900000}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}}

finish_test







|


|


|


|


|


|


|



















|



|


|



|


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
# with a==100.  And so for those cases, choose the t1b index.
#
# Buf ro a==99 and a==101, there are far fewer rows so choose
# the t1a index.
#
do_test 1.1 {
  eqp {SELECT * FROM t1 WHERE a=100 AND b=55}
} {/*SEARCH TABLE t1 USING INDEX t1b (b=?)*/}
do_test 1.2 {
  eqp {SELECT * FROM t1 WHERE a=99 AND b=55}
} {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/}
do_test 1.3 {
  eqp {SELECT * FROM t1 WHERE a=101 AND b=55}
} {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/}
do_test 1.4 {
  eqp {SELECT * FROM t1 WHERE a=100 AND b=56}
} {/*SEARCH TABLE t1 USING INDEX t1b (b=?)*/}
do_test 1.5 {
  eqp {SELECT * FROM t1 WHERE a=99 AND b=56}
} {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/}
do_test 1.6 {
  eqp {SELECT * FROM t1 WHERE a=101 AND b=56}
} {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/}
do_test 2.1 {
  eqp {SELECT * FROM t1 WHERE a=100 AND b BETWEEN 50 AND 54}
} {/*SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)*/}

# There are many more values of c between 0 and 100000 than there are
# between 800000 and 900000.  So t1c is more selective for the latter
# range.
# 
# Test 3.2 is a little unstable. It depends on the planner estimating
# that (b BETWEEN 30 AND 34) will match more rows than (c BETWEEN
# 800000 AND 900000). Which is a pretty close call (50 vs. 32), so
# the planner could get it wrong with an unlucky set of samples. This
# case happens to work, but others ("b BETWEEN 40 AND 44" for example) 
# will fail.
#
do_execsql_test 3.0 {
  SELECT count(*) FROM t1 WHERE b BETWEEN 30 AND 34;
  SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 100000;
  SELECT count(*) FROM t1 WHERE c BETWEEN 800000 AND 900000;
} {50 376 32}
do_test 3.1 {
  eqp {SELECT * FROM t1 WHERE b BETWEEN 30 AND 34 AND c BETWEEN 0 AND 100000}
} {/*SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)*/}
do_test 3.2 {
  eqp {SELECT * FROM t1
       WHERE b BETWEEN 30 AND 34 AND c BETWEEN 800000 AND 900000}
} {/*SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)*/}
do_test 3.3 {
  eqp {SELECT * FROM t1 WHERE a=100 AND c BETWEEN 0 AND 100000}
} {/*SEARCH TABLE t1 USING INDEX t1a (a=?)*/}
do_test 3.4 {
  eqp {SELECT * FROM t1
       WHERE a=100 AND c BETWEEN 800000 AND 900000}
} {/*SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)*/}

finish_test
Changes to test/autoindex1.test.
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
#
do_execsql_test autoindex1-500 {
  CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
  CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
  INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501',null,'1000000');
  INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502',null,'1000');
  ANALYZE sqlite_master;
  EXPLAIN QUERY PLAN


  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
} {

  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} 
  0 0 0 {EXECUTE LIST SUBQUERY 1} 
  1 0 0 {SCAN TABLE t502}
}
do_execsql_test autoindex1-501 {
  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} 
  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
  1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?)}
}
do_execsql_test autoindex1-502 {
  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 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} 
  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
  1 0 0 {SCAN 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.
#







<
>
>



>
|
|
|

|
<



>
|
|
|

|
<




>
|
|
|

<







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
#
do_execsql_test autoindex1-500 {
  CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
  CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
  INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501',null,'1000000');
  INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502',null,'1000');
  ANALYZE sqlite_master;

}
do_eqp_test autoindex1-500.1 {
  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
} {
  QUERY PLAN
  |--SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)
  `--LIST SUBQUERY
     `--SCAN TABLE t502
}
do_eqp_test autoindex1-501 {

  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
  QUERY PLAN
  |--SCAN TABLE t501
  `--CORRELATED LIST SUBQUERY
     `--SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?)
}
do_eqp_test autoindex1-502 {

  SELECT b FROM t501
   WHERE t501.a=123
     AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
  QUERY PLAN
  |--SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)
  `--CORRELATED LIST SUBQUERY
     `--SCAN 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.
#
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
              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} 
  1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)} 
  1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 
  2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)} 
  0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index} 
  0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)}
}


do_execsql_test autoindex1-700 {
  CREATE TABLE t5(a, b, c);


  EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c;
} {

  0 0 0 {SCAN TABLE t5} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

# The following checks a performance issue reported on the sqlite-dev
# mailing list on 2013-01-10
#
do_execsql_test autoindex1-800 {
  CREATE TABLE accounts(







<
>
>
















>
>
|
|
|
|
|
|





>
>
|

>
|
|







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
              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);

}
do_eqp_test autoindex1-600a {
  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;
} {
  QUERY PLAN
  |--MATERIALIZE xxxxxx
  |  |--SCAN TABLE sheep AS s
  |  |--SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)
  |  `--CORRELATED SCALAR SUBQUERY
  |     `--SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)
  |--SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index
  `--SEARCH SUBQUERY xxxxxx AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)
}


do_execsql_test autoindex1-700 {
  CREATE TABLE t5(a, b, c);
}
do_eqp_test autoindex1-700a {
  SELECT a FROM t5 WHERE b=10 ORDER BY c;
} {
  QUERY PLAN
  |--SCAN TABLE t5
  `--USE TEMP B-TREE FOR ORDER BY
}

# The following checks a performance issue reported on the sqlite-dev
# mailing list on 2013-01-10
#
do_execsql_test autoindex1-800 {
  CREATE TABLE accounts(
Changes to test/autoindex5.test.
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
              AND debian_bugs.note = package_notes.id
              ORDER BY debian_bugs.bug;
} {}

# The following query should use an automatic index for the view
# in FROM clause of the subquery of the second result column.
#
do_execsql_test autoindex5-1.1 {
  EXPLAIN QUERY PLAN
  SELECT
    st.bug_name,
    (SELECT ALL debian_cve.bug FROM debian_cve
      WHERE debian_cve.bug_name = st.bug_name
      ORDER BY debian_cve.bug),
    sp.release
  FROM
     source_package_status AS st,
     source_packages AS sp,
     bugs
  WHERE
     sp.rowid = st.package
     AND st.bug_name = bugs.name
     AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )
     AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release = 'jessie'
            OR sp.release = 'wheezy' OR sp.release = 'squeeze' )
  ORDER BY sp.name, st.bug_name, sp.release, sp.subrelease;
} {/SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX .bug_name=/}

#-------------------------------------------------------------------------
# Test that ticket [8a2adec1] has been fixed.
#
do_execsql_test 2.1 {
  CREATE TABLE one(o);
  INSERT INTO one DEFAULT VALUES;







|
<

















|







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
              AND debian_bugs.note = package_notes.id
              ORDER BY debian_bugs.bug;
} {}

# The following query should use an automatic index for the view
# in FROM clause of the subquery of the second result column.
#
do_eqp_test autoindex5-1.1 {

  SELECT
    st.bug_name,
    (SELECT ALL debian_cve.bug FROM debian_cve
      WHERE debian_cve.bug_name = st.bug_name
      ORDER BY debian_cve.bug),
    sp.release
  FROM
     source_package_status AS st,
     source_packages AS sp,
     bugs
  WHERE
     sp.rowid = st.package
     AND st.bug_name = bugs.name
     AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )
     AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release = 'jessie'
            OR sp.release = 'wheezy' OR sp.release = 'squeeze' )
  ORDER BY sp.name, st.bug_name, sp.release, sp.subrelease;
} {SEARCH SUBQUERY * USING AUTOMATIC COVERING INDEX (bug_name=?)}

#-------------------------------------------------------------------------
# Test that ticket [8a2adec1] has been fixed.
#
do_execsql_test 2.1 {
  CREATE TABLE one(o);
  INSERT INTO one DEFAULT VALUES;
Changes to test/bestindex3.test.
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
    CREATE TABLE t2(x TEXT COLLATE nocase, y TEXT);
    CREATE INDEX t2x ON t2(x COLLATE nocase);
    CREATE INDEX t2y ON t2(y);
  }

  do_eqp_test 2.2 {
    SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def'
  } {
  QUERY PLAN
  |--SEARCH TABLE t2 USING INDEX t2x (x>? AND x<?)
  `--SEARCH TABLE t2 USING INDEX t2y (y=?)
  }
}

#-------------------------------------------------------------------------
# Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE 
# statement is currently ignored.
#
proc vvv_command {method args} {







|
|
|
|
|







141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
    CREATE TABLE t2(x TEXT COLLATE nocase, y TEXT);
    CREATE INDEX t2x ON t2(x COLLATE nocase);
    CREATE INDEX t2y ON t2(y);
  }

  do_eqp_test 2.2 {
    SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def'
  } [string map {"\n  " \n} {
    QUERY PLAN
    |--SEARCH TABLE t2 USING INDEX t2x (x>? AND x<?)
    `--SEARCH TABLE t2 USING INDEX t2y (y=?)
  }]
}

#-------------------------------------------------------------------------
# Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE 
# statement is currently ignored.
#
proc vvv_command {method args} {
Changes to test/e_createtable.test.
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
#
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 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?)}}

  2    "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
       {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1}}

  3    "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
       {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?)}}
}

# 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 







|


|


|







1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
#
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" 
       {/*SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?)*/}

  2    "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
       {/*SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1*/}

  3    "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
       {/*SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?)*/}
}

# 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/fts3aux1.test.
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
db func rec rec

# Use EQP to show that the WHERE expression "term='braid'" uses a different
# index number (1) than "+term='braid'" (0).
#
do_execsql_test 2.1.1.1 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid'
} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} }
do_execsql_test 2.1.1.2 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid'
} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:}}

# Now show that using "term='braid'" means the virtual table returns
# only 1 row to SQLite, but "+term='braid'" means all 19 are returned.
#
do_test 2.1.2.1 {
  set cnt 0
  execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' }







|


|







101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
db func rec rec

# Use EQP to show that the WHERE expression "term='braid'" uses a different
# index number (1) than "+term='braid'" (0).
#
do_execsql_test 2.1.1.1 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid'
} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 1:*/}
do_execsql_test 2.1.1.2 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid'
} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/}

# Now show that using "term='braid'" means the virtual table returns
# only 1 row to SQLite, but "+term='braid'" means all 19 are returned.
#
do_test 2.1.2.1 {
  set cnt 0
  execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' }
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

# Special case: term=NULL
#
do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {}

do_execsql_test 2.2.1.1 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain'
} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2:} }
do_execsql_test 2.2.1.2 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain'
} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} }

do_execsql_test 2.2.1.3 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain'
} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4:} }
do_execsql_test 2.2.1.4 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain'
} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} }

do_execsql_test 2.2.1.5 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain'
} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6:} }
do_execsql_test 2.2.1.6 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain'
} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} }

do_test 2.2.2.1 {
  set cnt 0
  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' }
  set cnt
} {18}
do_test 2.2.2.2 {







|


|



|


|



|


|







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

# Special case: term=NULL
#
do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {}

do_execsql_test 2.2.1.1 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain'
} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 2:*/}
do_execsql_test 2.2.1.2 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain'
} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/}

do_execsql_test 2.2.1.3 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain'
} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 4:*/}
do_execsql_test 2.2.1.4 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain'
} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/}

do_execsql_test 2.2.1.5 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain'
} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 6:*/}
do_execsql_test 2.2.1.6 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain'
} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/}

do_test 2.2.2.1 {
  set cnt 0
  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' }
  set cnt
} {18}
do_test 2.2.2.2 {
331
332
333
334
335
336
337
338
339

340
341
342
343
344
345
346
  5    1    "ORDER BY documents"
  6    1    "ORDER BY documents DESC"
  7    1    "ORDER BY occurrences ASC"
  8    1    "ORDER BY occurrences"
  9    1    "ORDER BY occurrences DESC"
} {

  set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:}]
  if {$sort} { lappend res 0 0 0 {USE TEMP B-TREE FOR ORDER BY} }


  set sql "SELECT * FROM terms $orderby"
  do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res
}

#-------------------------------------------------------------------------
# The next set of tests, fts3aux1-3.*, test error conditions in the 







|
|
>







331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
  5    1    "ORDER BY documents"
  6    1    "ORDER BY documents DESC"
  7    1    "ORDER BY occurrences ASC"
  8    1    "ORDER BY occurrences"
  9    1    "ORDER BY occurrences DESC"
} {

  set res {SCAN TABLE terms VIRTUAL TABLE INDEX 0:}
  if {$sort} { append res {*USE TEMP B-TREE FOR ORDER BY} }
  set res "/*$res*/"

  set sql "SELECT * FROM terms $orderby"
  do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res
}

#-------------------------------------------------------------------------
# The next set of tests, fts3aux1-3.*, test error conditions in the 
399
400
401
402
403
404
405
406

407
408
409
410
411
412

413
414

415
416
417
418
419
420

421
422

423
424
425
426
427
428

429
430

431
432
433
434
435
436

437
438

439
440
441
442
443
444
445
  INSERT INTO x1 VALUES('f g h i j');
  INSERT INTO x1 VALUES('k k l l a');

  INSERT INTO x2 SELECT term FROM terms WHERE col = '*';
  INSERT INTO x3 SELECT term FROM terms WHERE col = '*';
}

proc do_plansql_test {tn sql r} {

  uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r]
}

do_plansql_test 4.2 {
  SELECT y FROM x2, terms WHERE y = term AND col = '*'
} {

  0 0 0 {SCAN TABLE x2} 
  0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} 

  a b c d e f g h i j k l
}

do_plansql_test 4.3 {
  SELECT y FROM terms, x2 WHERE y = term AND col = '*'
} {

  0 0 1 {SCAN TABLE x2} 
  0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} 

  a b c d e f g h i j k l
}

do_plansql_test 4.4 {
  SELECT y FROM x3, terms WHERE y = term AND col = '*'
} {

  0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} 
  0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)}

  a b c d e f g h i j k l
}

do_plansql_test 4.5 {
  SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*'
} {

  0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} 
  0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)}

  a k l
}

#-------------------------------------------------------------------------
# The following tests check that fts4aux can handle an fts table with an
# odd name (one that requires quoting for use in SQL statements). And that
# the argument to the fts4aux constructor is properly dequoted before use.







|
>
|





>
|
|
>






>
|
|
>






>
|
|
>






>
|
|
>







400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
  INSERT INTO x1 VALUES('f g h i j');
  INSERT INTO x1 VALUES('k k l l a');

  INSERT INTO x2 SELECT term FROM terms WHERE col = '*';
  INSERT INTO x3 SELECT term FROM terms WHERE col = '*';
}

proc do_plansql_test {tn sql r1 r2} {
  do_eqp_test $tn.eqp $sql $r1
  do_execsql_test $tn $sql $r2
}

do_plansql_test 4.2 {
  SELECT y FROM x2, terms WHERE y = term AND col = '*'
} {
  QUERY PLAN
  |--SCAN TABLE x2
  `--SCAN TABLE terms VIRTUAL TABLE INDEX 1:
} {
  a b c d e f g h i j k l
}

do_plansql_test 4.3 {
  SELECT y FROM terms, x2 WHERE y = term AND col = '*'
} {
  QUERY PLAN
  |--SCAN TABLE x2
  `--SCAN TABLE terms VIRTUAL TABLE INDEX 1:
} {
  a b c d e f g h i j k l
}

do_plansql_test 4.4 {
  SELECT y FROM x3, terms WHERE y = term AND col = '*'
} {
  QUERY PLAN
  |--SCAN TABLE terms VIRTUAL TABLE INDEX 0:
  `--SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)
} {
  a b c d e f g h i j k l
}

do_plansql_test 4.5 {
  SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*'
} {
  QUERY PLAN
  |--SCAN TABLE terms VIRTUAL TABLE INDEX 0:
  `--SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)
} {
  a k l
}

#-------------------------------------------------------------------------
# The following tests check that fts4aux can handle an fts table with an
# odd name (one that requires quoting for use in SQL statements). And that
# the argument to the fts4aux constructor is properly dequoted before use.
Changes to test/selectD.test.
165
166
167
168
169
170
171
172
173
174
  SELECT * 
   FROM t41
   LEFT JOIN (SELECT count(*) AS cnt, x1.d
                FROM (t42 INNER JOIN t43 ON d=g) AS x1
               WHERE x1.d>5
               GROUP BY x1.d) AS x2
                  ON t41.b=x2.d;
} {/.*SEARCH SUBQUERY 1 AS x2 USING AUTOMATIC.*/}

finish_test







|


165
166
167
168
169
170
171
172
173
174
  SELECT * 
   FROM t41
   LEFT JOIN (SELECT count(*) AS cnt, x1.d
                FROM (t42 INNER JOIN t43 ON d=g) AS x1
               WHERE x1.d>5
               GROUP BY x1.d) AS x2
                  ON t41.b=x2.d;
} {/*SEARCH SUBQUERY 0x* AS x2 USING AUTOMATIC*/}

finish_test
Changes to test/tkt-78e04e52ea.test.
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
} {0 {} {} 0 {} 0 1 x CHAR(100) 0 {} 0}
do_test tkt-78e04-1.3 {
  execsql {
    CREATE INDEX i1 ON ""("" COLLATE nocase);
  }
} {}
do_test tkt-78e04-1.4 {
  execsql {
    EXPLAIN QUERY PLAN SELECT "" FROM "" WHERE "" LIKE '1abc%';
  }
} {0 0 0 {SCAN TABLE  USING COVERING INDEX i1}}
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 0 {SEARCH TABLE t2 USING COVERING INDEX  (x=?)}}
do_test tkt-78e04-2.2 {
  execsql {
    DROP INDEX "";
    EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=2;
  }
} {0 0 0 {SCAN TABLE t2}}

finish_test







<
|
<
|












|





|


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
} {0 {} {} 0 {} 0 1 x CHAR(100) 0 {} 0}
do_test tkt-78e04-1.3 {
  execsql {
    CREATE INDEX i1 ON ""("" COLLATE nocase);
  }
} {}
do_test tkt-78e04-1.4 {

 db eval {EXPLAIN QUERY PLAN SELECT "" FROM "" WHERE "" LIKE '1abc%';}

} {/*SCAN TABLE  USING COVERING INDEX i1*/}
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;
  }
} {/*SEARCH TABLE t2 USING COVERING INDEX  (x=?)*/}
do_test tkt-78e04-2.2 {
  execsql {
    DROP INDEX "";
    EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=2;
  }
} {/*SCAN TABLE t2*/}

finish_test
Changes to test/tkt3442.test.
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
       id TEXT,
       node INTEGER
     );
     CREATE UNIQUE INDEX ididx ON listhash(id);
  }
} {}


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


# 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 0 {SEARCH TABLE listhash USING INDEX ididx (id=?)}}
  do_test tkt3442-1.3 {
    EQP { SELECT node FROM listhash WHERE id="5000" LIMIT 1; }
  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?)}}
}


# 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 0 {SEARCH TABLE listhash USING INDEX ididx (id=?)}}
}
do_test tkt3442-1.5 {
  catchsql {
    SELECT node FROM listhash WHERE id=[5000] LIMIT 1;
  }
} {1 {no such column: 5000}}

finish_test







<
<
<
<
<
<
<
<




<
|
|
|
|
|
|
<




<
|
|
|
|







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
       id TEXT,
       node INTEGER
     );
     CREATE UNIQUE INDEX ididx ON listhash(id);
  }
} {}









# 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.
#

do_eqp_test tkt3442-1.2 {
  SELECT node FROM listhash WHERE id='5000' LIMIT 1;
} {SEARCH TABLE listhash USING INDEX ididx (id=?)}
do_eqp_test tkt3442-1.3 {
  SELECT node FROM listhash WHERE id="5000" LIMIT 1;
} {SEARCH TABLE listhash USING INDEX ididx (id=?)}



# Some extra tests testing other permutations of 5000.
#

do_eqp_test tkt3442-1.4 {
  SELECT node FROM listhash WHERE id=5000 LIMIT 1;
} {SEARCH TABLE listhash USING INDEX ididx (id=?)}

do_test tkt3442-1.5 {
  catchsql {
    SELECT node FROM listhash WHERE id=[5000] LIMIT 1;
  }
} {1 {no such column: 5000}}

finish_test
Changes to test/where3.test.
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
  CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c);
  CREATE INDEX t301c ON t301(c);
  INSERT INTO t301 VALUES(1,2,3);
  INSERT INTO t301 VALUES(2,2,3);
  CREATE TABLE t302(x, y);
  INSERT INTO t302 VALUES(4,5);
  ANALYZE;


  explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y;
} {

  0 0 0 {SCAN TABLE t302} 
  0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)}
}
do_execsql_test where3-3.1 {
  explain query plan
  SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
} {

  0 0 1 {SCAN TABLE t302} 
  0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)}
}
do_execsql_test where3-3.2 {
  SELECT * FROM t301 WHERE c=3 AND a IS NULL;
} {}
do_execsql_test where3-3.3 {
  SELECT * FROM t301 WHERE c=3 AND a IS NOT NULL;
} {1 2 3 2 2 3}







>
>
|

>
|
|

|
<


>
|
|







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
  CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c);
  CREATE INDEX t301c ON t301(c);
  INSERT INTO t301 VALUES(1,2,3);
  INSERT INTO t301 VALUES(2,2,3);
  CREATE TABLE t302(x, y);
  INSERT INTO t302 VALUES(4,5);
  ANALYZE;
}
do_eqp_test where3-3.0a {
  SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y;
} {
  QUERY PLAN
  |--SCAN TABLE t302
  `--SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)
}
do_eqp_test where3-3.1 {

  SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
} {
  QUERY PLAN
  |--SCAN TABLE t302
  `--SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)
}
do_execsql_test where3-3.2 {
  SELECT * FROM t301 WHERE c=3 AND a IS NULL;
} {}
do_execsql_test where3-3.3 {
  SELECT * FROM t301 WHERE c=3 AND a IS NOT NULL;
} {1 2 3 2 2 3}
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

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

359
360
361
362
363
364
365
366
367
368
                    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);

  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 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} 
  0 1 1 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_execsql_test where3-5.1 {
  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 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} 
  0 1 1 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_execsql_test where3-5.2 {
  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 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} 
  0 1 0 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_execsql_test where3-5.3 {
  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 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)} 
  0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

# Name resolution with NATURAL JOIN and USING
#
do_test where3-6.setup {
  db eval {
    CREATE TABLE t6w(a, w);







|
|







>
|
|
|

|
<







>
|
|
|

|
<







>
|
|
|

|
<







>
|
|
|







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
346
347
348
349
350
351
352
353
354

355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
                    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);
}
do_eqp_test where3-5.0a {
   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;
} {
  QUERY PLAN
  |--SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)
  |--SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?)
  `--USE TEMP B-TREE FOR ORDER BY
}
do_eqp_test where3-5.1 {

   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;
} {
  QUERY PLAN
  |--SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)
  |--SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?)
  `--USE TEMP B-TREE FOR ORDER BY
}
do_eqp_test where3-5.2 {

   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;
} {
  QUERY PLAN
  |--SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)
  |--SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?)
  `--USE TEMP B-TREE FOR ORDER BY
}
do_eqp_test where3-5.3 {

   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;
} {
  QUERY PLAN
  |--SEARCH TABLE aaa USING INDEX aaa_333 (fk=?)
  |--SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?)
  `--USE TEMP B-TREE FOR ORDER BY
}

# Name resolution with NATURAL JOIN and USING
#
do_test where3-6.setup {
  db eval {
    CREATE TABLE t6w(a, w);