From f2d315d0c2f63c34c356fd1da20fab0f1b7f4c4d Mon Sep 17 00:00:00 2001 From: drh Date: Thu, 25 Jan 2007 16:56:06 +0000 Subject: [PATCH] Fix an additional problem with the IS NULL optimization on LEFT JOINs. Ticket #2189. See also ticket #2177. (CVS 3602) FossilOrigin-Name: 358dd82d3a921228155e2cf9e22aedd2b651ca9c --- manifest | 14 +++++++------- manifest.uuid | 2 +- src/where.c | 40 ++++++++++++++++++++++++---------------- test/where4.test | 38 +++++++++++++++++++++++++++++++++++++- 4 files changed, 69 insertions(+), 25 deletions(-) diff --git a/manifest b/manifest index a14c404493..ebea4e5280 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Do\snot\srun\sthe\sfts2i.test\sunless\sthe\sFTS2\smodule\sis\savailable.\s(CVS\s3601) -D 2007-01-24T03:46:35 +C Fix\san\sadditional\sproblem\swith\sthe\sIS\sNULL\soptimization\son\sLEFT\sJOINs.\nTicket\s#2189.\s\sSee\salso\sticket\s#2177.\s(CVS\s3602) +D 2007-01-25T16:56:07 F Makefile.in 7fa74bf4359aa899da5586e394d17735f221315f F Makefile.linux-gcc 2d8574d1ba75f129aba2019f0b959db380a90935 F README 9c4e2d6706bdcc3efdd773ce752a8cdab4f90028 @@ -129,7 +129,7 @@ F src/vdbeaux.c c5324d62f51529bccc5be3b04bac2e4eeae1569a F src/vdbefifo.c 9efb94c8c3f4c979ebd0028219483f88e57584f5 F src/vdbemem.c 26623176bf1c616aa478da958fac49502491a921 F src/vtab.c 7fbda947e28cbe7adb3ba752a76ca9ef29936750 -F src/where.c 46dea18bc3b0fa2d71447bb6408a6c49e6a7c187 +F src/where.c 23dc1c7535c96770d214762ed0bc3c655de5061c F tclinstaller.tcl 046e3624671962dc50f0481d7c25b38ef803eb42 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2 F test/all.test b62fcd122052efaff1b0979aefa2dd65cfc8ee52 @@ -354,7 +354,7 @@ F test/vtab_err.test 224cc80ad700797c48b9cd2c1e0bd7a8517d8609 F test/where.test 8dcc1b1a6f17b6bad2dc6a9917eafe62d4ea57eb F test/where2.test 61d5b20d9bedc8788a773bbdc5b2ef887725928e F test/where3.test 0a30fe9808b0fa01c46d0fcf4fac0bf6cf75bb30 -F test/where4.test c6b6cca0859b96f79ab47e6955fce787076e6a60 +F test/where4.test 3fcf53c5ea7af1db3980b3293c2a45b56605f26a F tool/diffdb.c 7524b1b5df217c20cd0431f6789851a4e0cb191b F tool/lemon.c 2938bec507110397c937bd8a03b0c9596a709a04 F tool/lempar.c fdc1672e97f72f72e76553038501da40fec9d251 @@ -427,7 +427,7 @@ F www/tclsqlite.tcl bb0d1357328a42b1993d78573e587c6dcbc964b9 F www/vdbe.tcl 87a31ace769f20d3627a64fa1fade7fed47b90d0 F www/version3.tcl 890248cf7b70e60c383b0e84d77d5132b3ead42b F www/whentouse.tcl 97e2b5cd296f7d8057e11f44427dea8a4c2db513 -P 810c0176f8413995a78963c453e4377f11b293b5 -R 37a170980624908c75f237e7f767c63e +P 310f68585188ae49b603af9bdef4ee7738ae37c0 +R 907eca9dc3d1d6e0a6d5d294c8ad8df3 U drh -Z 70e488812d923808b7de40f02231550c +Z 4ac8b3d88fe5dcdfe42a3ce7e4dec0ad diff --git a/manifest.uuid b/manifest.uuid index 4a729285e8..4e6bbf4d73 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -310f68585188ae49b603af9bdef4ee7738ae37c0 \ No newline at end of file +358dd82d3a921228155e2cf9e22aedd2b651ca9c \ No newline at end of file diff --git a/src/where.c b/src/where.c index 83e52cacaf..d8fe6da85f 100644 --- a/src/where.c +++ b/src/where.c @@ -16,7 +16,7 @@ ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** -** $Id: where.c,v 1.235 2007/01/19 01:06:03 drh Exp $ +** $Id: where.c,v 1.236 2007/01/25 16:56:07 drh Exp $ */ #include "sqliteInt.h" @@ -160,20 +160,28 @@ struct ExprMaskSet { #define WO_ISNULL 128 /* -** Value for flags returned by bestIndex() +** Value for flags returned by bestIndex(). +** +** The least significant byte is reserved as a mask for WO_ values above. +** The WhereLevel.flags field is usually set to WO_IN|WO_EQ|WO_ISNULL. +** But if the table is the right table of a left join, WhereLevel.flags +** is set to WO_IN|WO_EQ. The WhereLevel.flags field can then be used as +** the "op" parameter to findTerm when we are resolving equality constraints. +** ISNULL constraints will then not be used on the right table of a left +** join. Tickets #2177 and #2189. */ -#define WHERE_ROWID_EQ 0x0001 /* rowid=EXPR or rowid IN (...) */ -#define WHERE_ROWID_RANGE 0x0002 /* rowidEXPR */ -#define WHERE_COLUMN_EQ 0x0010 /* x=EXPR or x IN (...) */ -#define WHERE_COLUMN_RANGE 0x0020 /* xEXPR */ -#define WHERE_COLUMN_IN 0x0040 /* x IN (...) */ -#define WHERE_TOP_LIMIT 0x0100 /* xEXPR or x>=EXPR constraint */ -#define WHERE_IDX_ONLY 0x0800 /* Use index only - omit table */ -#define WHERE_ORDERBY 0x1000 /* Output will appear in correct order */ -#define WHERE_REVERSE 0x2000 /* Scan in reverse order */ -#define WHERE_UNIQUE 0x4000 /* Selects no more than one row */ -#define WHERE_VIRTUALTABLE 0x8000 /* Use virtual-table processing */ +#define WHERE_ROWID_EQ 0x000100 /* rowid=EXPR or rowid IN (...) */ +#define WHERE_ROWID_RANGE 0x000200 /* rowidEXPR */ +#define WHERE_COLUMN_EQ 0x001000 /* x=EXPR or x IN (...) */ +#define WHERE_COLUMN_RANGE 0x002000 /* xEXPR */ +#define WHERE_COLUMN_IN 0x004000 /* x IN (...) */ +#define WHERE_TOP_LIMIT 0x010000 /* xEXPR or x>=EXPR constraint */ +#define WHERE_IDX_ONLY 0x080000 /* Use index only - omit table */ +#define WHERE_ORDERBY 0x100000 /* Output will appear in correct order */ +#define WHERE_REVERSE 0x200000 /* Scan in reverse order */ +#define WHERE_UNIQUE 0x400000 /* Selects no more than one row */ +#define WHERE_VIRTUALTABLE 0x800000 /* Use virtual-table processing */ /* ** Initialize a preallocated WhereClause structure. @@ -1486,7 +1494,7 @@ static double bestIndex( *ppIndex = bestIdx; TRACE(("best index is %s, cost=%.9g, flags=%x, nEq=%d\n", bestIdx ? bestIdx->zName : "(none)", lowestCost, bestFlags, bestNEq)); - *pFlags = bestFlags; + *pFlags = bestFlags | eqTermMask; *pnEq = bestNEq; return lowestCost; } @@ -1651,7 +1659,7 @@ static void codeAllEqualityTerms( assert( pIdx->nColumn>=nEq ); for(j=0; jaiColumn[j]; - pTerm = findTerm(pWC, iCur, k, notReady, WO_EQ|WO_IN|WO_ISNULL, pIdx); + pTerm = findTerm(pWC, iCur, k, notReady, pLevel->flags, pIdx); if( pTerm==0 ) break; assert( (pTerm->flags & TERM_CODED)==0 ); codeEqualityTerm(pParse, pTerm, brk, pLevel); diff --git a/test/where4.test b/test/where4.test index 00afaa7767..fc616482b0 100644 --- a/test/where4.test +++ b/test/where4.test @@ -15,7 +15,7 @@ # that IS NULL phrases are correctly optimized. But you can never # have too many tests, so some other tests are thrown in as well. # -# $Id: where4.test,v 1.1 2007/01/19 01:06:03 drh Exp $ +# $Id: where4.test,v 1.2 2007/01/25 16:56:08 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -139,6 +139,42 @@ do_test where4-3.2 { } } {2 2 {} 3 {} {}} +# Ticket #2189. Probably the same bug as #2177. +# +do_test where4-4.1 { + execsql { + CREATE TABLE test(col1 TEXT PRIMARY KEY); + INSERT INTO test(col1) values('a'); + INSERT INTO test(col1) values('b'); + INSERT INTO test(col1) values('c'); + CREATE TABLE test2(col1 TEXT PRIMARY KEY); + INSERT INTO test2(col1) values('a'); + INSERT INTO test2(col1) values('b'); + INSERT INTO test2(col1) values('c'); + SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1 + WHERE +t2.col1 IS NULL; + } +} {} +do_test where4-4.2 { + execsql { + SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1 + WHERE t2.col1 IS NULL; + } +} {} +do_test where4-4.3 { + execsql { + SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1 + WHERE +t1.col1 IS NULL; + } +} {} +do_test where4-4.4 { + execsql { + SELECT * FROM test t1 LEFT OUTER JOIN test2 t2 ON t1.col1 = t2.col1 + WHERE t1.col1 IS NULL; + } +} {} + + integrity_check {where4-99.0} finish_test -- 2.47.2