Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Improve the omit-left-join optimization so that it works in some cases when the RHS is subject to a UNIQUE but not NOT NULL constraint. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
02ba8a7ba7ba71cd7abd5dd3093ea486 |
| User & Date: | drh 2018-01-31 16:50:27 |
References
|
2018-09-08
| ||
| 03:22 | • New ticket [9936b2fa] Infinite loop due to the ORDER BY LIMIT optimization. artifact: 9b89fdd3 user: drh | |
Context
|
2018-01-31
| ||
| 20:18 | When creating a new archive entry, have zipfile store UTC instead of local time in the legacy MS-DOS format timestamp field. check-in: b730d187 user: dan tags: trunk | |
| 19:13 | When creating a new archive entry, have zipfile store UTC instead of local time in the legacy MS-DOS format timestamp field. check-in: e2114df1 user: dan tags: zipfile-timestamp-fix | |
| 16:50 | Improve the omit-left-join optimization so that it works in some cases when the RHS is subject to a UNIQUE but not NOT NULL constraint. check-in: 02ba8a7b user: drh tags: trunk | |
| 14:07 | Fix a failing assert() in the new code on this branch. Closed-Leaf check-in: 74d857d1 user: dan tags: omit-left-join-fix | |
| 01:38 | Changes to the autoconf tarball so that it does not try to use system() when building on iOS. check-in: 32ed9c10 user: drh tags: trunk | |
Changes
Changes to src/where.c.
2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 |
}else if( eOp & (WO_EQ|WO_IS) ){
int iCol = pProbe->aiColumn[saved_nEq];
pNew->wsFlags |= WHERE_COLUMN_EQ;
assert( saved_nEq==pNew->u.btree.nEq );
if( iCol==XN_ROWID
|| (iCol>=0 && nInMul==0 && saved_nEq==pProbe->nKeyCol-1)
){
if( iCol>=0 && pProbe->uniqNotNull==0 ){
pNew->wsFlags |= WHERE_UNQ_WANTED;
}else{
pNew->wsFlags |= WHERE_ONEROW;
}
}
}else if( eOp & WO_ISNULL ){
pNew->wsFlags |= WHERE_COLUMN_NULL;
}else if( eOp & (WO_GT|WO_GE) ){
testcase( eOp & WO_GT );
testcase( eOp & WO_GE );
|
| > > | | |
2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 |
}else if( eOp & (WO_EQ|WO_IS) ){
int iCol = pProbe->aiColumn[saved_nEq];
pNew->wsFlags |= WHERE_COLUMN_EQ;
assert( saved_nEq==pNew->u.btree.nEq );
if( iCol==XN_ROWID
|| (iCol>=0 && nInMul==0 && saved_nEq==pProbe->nKeyCol-1)
){
if( iCol==XN_ROWID || pProbe->uniqNotNull
|| (pProbe->nKeyCol==1 && pProbe->onError && eOp==WO_EQ)
){
pNew->wsFlags |= WHERE_ONEROW;
}else{
pNew->wsFlags |= WHERE_UNQ_WANTED;
}
}
}else if( eOp & WO_ISNULL ){
pNew->wsFlags |= WHERE_COLUMN_NULL;
}else if( eOp & (WO_GT|WO_GE) ){
testcase( eOp & WO_GT );
testcase( eOp & WO_GE );
|
Changes to test/join2.test.
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
...
187
188
189
190
191
192
193
194
195
|
SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
} {2 v3 1112 {}}
do_execsql_test 4.1.4 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
} {2 v3 2 v3 1112 {} 1112 {}}
do_eqp_test 4.2.1 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
} {
0 0 0 {SCAN TABLE c1}
0 1 1 {SEARCH TABLE c2 USING INTEGER PRIMARY KEY (rowid=?)}
0 2 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)}
}
do_eqp_test 4.2.2 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
} {
0 0 0 {SCAN TABLE c1}
0 1 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)}
}
# 2017-11-23 (Thanksgiving day)
# OSSFuzz found an assertion fault in the new LEFT JOIN eliminator code.
#
do_execsql_test 4.3.0 {
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
................................................................................
INSERT INTO t1(x) SELECT x FROM c;
INSERT INTO t2(x) SELECT x+9 FROM t1;
SELECT a.x, c.x
FROM t1 AS a
LEFT JOIN t1 AS b ON (a.x=b.x)
LEFT JOIN t2 AS c ON (a.x=c.x);
} {1 {} 2 {} 3 {} 4 {} 5 {} 6 {} 7 {} 8 {} 9 {} 10 10}
finish_test
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
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
...
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
|
SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
} {2 v3 1112 {}}
do_execsql_test 4.1.4 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
} {2 v3 2 v3 1112 {} 1112 {}}
do_eqp_test 4.1.5 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
} {
0 0 0 {SCAN TABLE c1}
0 1 1 {SEARCH TABLE c2 USING INTEGER PRIMARY KEY (rowid=?)}
0 2 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)}
}
do_eqp_test 4.1.6 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
} {
0 0 0 {SCAN TABLE c1}
0 1 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)}
}
do_execsql_test 4.2.0 {
DROP TABLE c1;
DROP TABLE c2;
DROP TABLE c3;
CREATE TABLE c1(k UNIQUE, v1);
CREATE TABLE c2(k UNIQUE, v2);
CREATE TABLE c3(k UNIQUE, v3);
INSERT INTO c1 VALUES(1, 2);
INSERT INTO c2 VALUES(2, 3);
INSERT INTO c3 VALUES(3, 'v3');
INSERT INTO c1 VALUES(111, 1112);
INSERT INTO c2 VALUES(112, 1113);
INSERT INTO c3 VALUES(113, 'v1113');
}
do_execsql_test 4.2.1 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
} {2 v3 1112 {}}
do_execsql_test 4.2.2 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
} {2 v3 1112 {}}
do_execsql_test 4.2.3 {
SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
} {2 v3 1112 {}}
do_execsql_test 4.2.4 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
} {2 v3 2 v3 1112 {} 1112 {}}
do_eqp_test 4.2.5 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
} {
0 0 0 {SCAN TABLE c1}
0 1 1 {SEARCH TABLE c2 USING INDEX sqlite_autoindex_c2_1 (k=?)}
0 2 2 {SEARCH TABLE c3 USING INDEX sqlite_autoindex_c3_1 (k=?)}
}
do_eqp_test 4.2.6 {
SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
} {
0 0 0 {SCAN TABLE c1}
0 1 2 {SEARCH TABLE c3 USING INDEX sqlite_autoindex_c3_1 (k=?)}
}
# 2017-11-23 (Thanksgiving day)
# OSSFuzz found an assertion fault in the new LEFT JOIN eliminator code.
#
do_execsql_test 4.3.0 {
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
................................................................................
INSERT INTO t1(x) SELECT x FROM c;
INSERT INTO t2(x) SELECT x+9 FROM t1;
SELECT a.x, c.x
FROM t1 AS a
LEFT JOIN t1 AS b ON (a.x=b.x)
LEFT JOIN t2 AS c ON (a.x=c.x);
} {1 {} 2 {} 3 {} 4 {} 5 {} 6 {} 7 {} 8 {} 9 {} 10 10}
do_execsql_test 5.0 {
CREATE TABLE s1 (a INTEGER PRIMARY KEY);
CREATE TABLE s2 (a INTEGER PRIMARY KEY);
CREATE TABLE s3 (a INTEGER);
CREATE UNIQUE INDEX ndx on s3(a);
}
do_eqp_test 5.1 {
SELECT s1.a FROM s1 left join s2 using (a);
} {
0 0 0 {SCAN TABLE s1}
}
do_eqp_test 5.2 {
SELECT s1.a FROM s1 left join s3 using (a);
} {
0 0 0 {SCAN TABLE s1}
}
do_execsql_test 6.0 {
CREATE TABLE u1(a INTEGER PRIMARY KEY, b, c);
CREATE TABLE u2(a INTEGER PRIMARY KEY, b, c);
CREATE INDEX u1ab ON u1(b, c);
}
do_eqp_test 6.1 {
SELECT u2.* FROM u2 LEFT JOIN u1 ON( u1.a=u2.a AND u1.b=u2.b AND u1.c=u2.c );
} {
0 0 0 {SCAN TABLE u2}
}
finish_test
|