Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | If all branches of an OR optimize scan that is the rhs of a LEFT JOIN use the same index, set the index cursor to return NULL values if there are no matches for a row on the lhs. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | leftjoin-or-fix |
| Files: | files | file ages | folders |
| SHA1: |
e7b9bc678ecb75c594d9d3ade12a99a8 |
| User & Date: | dan 2016-10-26 15:46:09 |
Context
|
2016-10-26
| ||
| 16:05 | If all branches of an OR optimize scan that is the rhs of a LEFT JOIN use the same index, set the index cursor to return NULL values if there are no matches for a row on the lhs. Fix for ticket [34a579141b2c5ac] (check-in: ec9dab80 user: dan tags: trunk) | |
| 15:46 | If all branches of an OR optimize scan that is the rhs of a LEFT JOIN use the same index, set the index cursor to return NULL values if there are no matches for a row on the lhs. (Closed-Leaf check-in: e7b9bc67 user: dan tags: leftjoin-or-fix) | |
| 13:58 | Merge the SQLITE_ENABLE_URI_00_ERROR compile-time option. (check-in: 86675ae0 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
4856 4857 4858 4859 4860 4861 4862 4863 4864 4865 4866 4867 4868 4869 4870 4871 4872 4873 4874 4875 4876 |
if( pLevel->addrLikeRep ){
sqlite3VdbeAddOp2(v, OP_DecrJumpZero, (int)(pLevel->iLikeRepCntr>>1),
pLevel->addrLikeRep);
VdbeCoverage(v);
}
#endif
if( pLevel->iLeftJoin ){
addr = sqlite3VdbeAddOp1(v, OP_IfPos, pLevel->iLeftJoin); VdbeCoverage(v);
assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0
|| (pLoop->wsFlags & WHERE_INDEXED)!=0 );
if( (pLoop->wsFlags & WHERE_IDX_ONLY)==0 ){
sqlite3VdbeAddOp1(v, OP_NullRow, pTabList->a[i].iCursor);
}
if( pLoop->wsFlags & WHERE_INDEXED ){
sqlite3VdbeAddOp1(v, OP_NullRow, pLevel->iIdxCur);
}
if( pLevel->op==OP_Return ){
sqlite3VdbeAddOp2(v, OP_Gosub, pLevel->p1, pLevel->addrFirst);
}else{
sqlite3VdbeGoto(v, pLevel->addrFirst);
}
|
> < | | | > > |
4856 4857 4858 4859 4860 4861 4862 4863 4864 4865 4866 4867 4868 4869 4870 4871 4872 4873 4874 4875 4876 4877 4878 |
if( pLevel->addrLikeRep ){
sqlite3VdbeAddOp2(v, OP_DecrJumpZero, (int)(pLevel->iLikeRepCntr>>1),
pLevel->addrLikeRep);
VdbeCoverage(v);
}
#endif
if( pLevel->iLeftJoin ){
int ws = pLoop->wsFlags;
addr = sqlite3VdbeAddOp1(v, OP_IfPos, pLevel->iLeftJoin); VdbeCoverage(v);
assert( (ws & WHERE_IDX_ONLY)==0 || (ws & WHERE_INDEXED)!=0 );
if( (ws & WHERE_IDX_ONLY)==0 ){
sqlite3VdbeAddOp1(v, OP_NullRow, pTabList->a[i].iCursor);
}
if( (ws & WHERE_INDEXED)
|| ((ws & WHERE_MULTI_OR) && pLevel->u.pCovidx)
){
sqlite3VdbeAddOp1(v, OP_NullRow, pLevel->iIdxCur);
}
if( pLevel->op==OP_Return ){
sqlite3VdbeAddOp2(v, OP_Gosub, pLevel->p1, pLevel->addrFirst);
}else{
sqlite3VdbeGoto(v, pLevel->addrFirst);
}
|
Changes to test/whereD.test.
332 333 334 335 336 337 338 339 340 |
} {3 7 11 search 7}
do_searchcount_test 6.6.3 {
SELECT c FROM x1 WHERE c=11 OR a=1 OR b=6
} {11 3 7 search 7}
do_searchcount_test 6.6.4 {
SELECT c FROM x1 WHERE b=6 OR c=11 OR a=1
} {7 11 3 search 7}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
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 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 |
} {3 7 11 search 7}
do_searchcount_test 6.6.3 {
SELECT c FROM x1 WHERE c=11 OR a=1 OR b=6
} {11 3 7 search 7}
do_searchcount_test 6.6.4 {
SELECT c FROM x1 WHERE b=6 OR c=11 OR a=1
} {7 11 3 search 7}
#-------------------------------------------------------------------------
#
do_execsql_test 7.0 {
CREATE TABLE y1(a, b);
CREATE TABLE y2(x, y);
CREATE INDEX y2xy ON y2(x, y);
INSERT INTO y1 VALUES(1, 1);
INSERT INTO y2 VALUES(3, 3);
}
do_execsql_test 7.1 {
SELECT * FROM y1 LEFT JOIN y2 ON ((x=1 AND y=b) OR (x=2 AND y=b))
} {1 1 {} {}}
do_execsql_test 7.3 {
CREATE TABLE foo (Id INTEGER PRIMARY KEY, fa INTEGER, fb INTEGER);
CREATE TABLE bar (Id INTEGER PRIMARY KEY, ba INTEGER, bb INTEGER);
INSERT INTO foo VALUES(1, 1, 1);
INSERT INTO foo VALUES(2, 1, 2);
INSERT INTO foo VALUES(3, 1, 3);
INSERT INTO foo VALUES(4, 1, 4);
INSERT INTO foo VALUES(5, 1, 5);
INSERT INTO foo VALUES(6, 1, 6);
INSERT INTO foo VALUES(7, 1, 7);
INSERT INTO foo VALUES(8, 1, 8);
INSERT INTO foo VALUES(9, 1, 9);
INSERT INTO bar VALUES(NULL, 1, 1);
INSERT INTO bar VALUES(NULL, 2, 2);
INSERT INTO bar VALUES(NULL, 3, 3);
INSERT INTO bar VALUES(NULL, 1, 4);
INSERT INTO bar VALUES(NULL, 2, 5);
INSERT INTO bar VALUES(NULL, 3, 6);
INSERT INTO bar VALUES(NULL, 1, 7);
INSERT INTO bar VALUES(NULL, 2, 8);
INSERT INTO bar VALUES(NULL, 3, 9);
}
do_execsql_test 7.4 {
SELECT
bar.Id, bar.ba, bar.bb, foo.fb
FROM foo LEFT JOIN bar
ON (bar.ba = 1 AND bar.bb = foo.fb)
OR (bar.ba = 5 AND bar.bb = foo.fb);
} {
1 1 1 1
{} {} {} 2
{} {} {} 3
4 1 4 4
{} {} {} 5
{} {} {} 6
7 1 7 7
{} {} {} 8
{} {} {} 9
}
do_execsql_test 7.5 {
CREATE INDEX idx_bar ON bar(ba, bb);
SELECT
bar.Id, bar.ba, bar.bb, foo.fb
FROM foo LEFT JOIN bar
ON (bar.ba = 1 AND bar.bb = foo.fb)
OR (bar.ba = 5 AND bar.bb = foo.fb);
} {
1 1 1 1
{} {} {} 2
{} {} {} 3
4 1 4 4
{} {} {} 5
{} {} {} 6
7 1 7 7
{} {} {} 8
{} {} {} 9
}
finish_test
|