From c3b328da6e5b8da8dedee131daad4bfdf367f660 Mon Sep 17 00:00:00 2001 From: dan Date: Wed, 18 Mar 2015 20:03:27 +0000 Subject: [PATCH] Allow whereShortCut() to use the PRIMARY KEY index of a WITHOUT ROWID table to optimize a vector of "IS" operators in a WHERE clause. FossilOrigin-Name: 52e73eeca063bb30092ce600068bf487641399a0 --- ext/ota/ota1.test | 26 +++++++++++++++++++ manifest | 17 +++++++------ manifest.uuid | 2 +- src/where.c | 12 ++++++--- src/whereInt.h | 3 ++- test/whereK.test | 63 +++++++++++++++++++++++++++++++++++++++++++++++ 6 files changed, 109 insertions(+), 14 deletions(-) create mode 100644 test/whereK.test diff --git a/ext/ota/ota1.test b/ext/ota/ota1.test index db52bdb7ab..9903d9216e 100644 --- a/ext/ota/ota1.test +++ b/ext/ota/ota1.test @@ -579,6 +579,32 @@ foreach {tn3 create_vfs destroy_vfs} { do_test $tn3.7 { list [catch { run_ota test.db ota.db } msg] $msg } {0 SQLITE_DONE} + + # Test that OTA can update indexes containing NULL values. + # + reset_db + forcedelete ota.db + do_execsql_test $tn3.8.1 { + CREATE TABLE t1(a PRIMARY KEY, b, c); + CREATE INDEX i1 ON t1(b, c); + INSERT INTO t1 VALUES(1, 1, NULL); + INSERT INTO t1 VALUES(2, NULL, 2); + INSERT INTO t1 VALUES(3, NULL, NULL); + + ATTACH 'ota.db' AS ota; + CREATE TABLE ota.data_t1(a, b, c, ota_control); + INSERT INTO data_t1 VALUES(1, NULL, NULL, 1); + INSERT INTO data_t1 VALUES(3, NULL, NULL, 1); + } {} + + do_test $tn3.8.2 { + list [catch { run_ota test.db ota.db } msg] $msg + } {0 SQLITE_DONE} + + do_execsql_test $tn3.8.3 { + SELECT * FROM t1 + } {2 {} 2} + do_execsql_test $tn3.8.4 { PRAGMA integrity_check } {ok} catch { db close } eval $destroy_vfs diff --git a/manifest b/manifest index c17d156249..16f1de919e 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Clarify\sthat\sOTA\sis\sunable\sto\supdate\sor\sdelete\srows\swith\sNULL\svalues\sin\sprimary\skey\sfields. -D 2015-03-18T19:04:40.018 +C Allow\swhereShortCut()\sto\suse\sthe\sPRIMARY\sKEY\sindex\sof\sa\sWITHOUT\sROWID\stable\sto\soptimize\sa\svector\sof\s"IS"\soperators\sin\sa\sWHERE\sclause. +D 2015-03-18T20:03:27.026 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in 2f643d6968dfc0b82d2e546a0525a39079f9e928 F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -124,7 +124,7 @@ F ext/misc/vfslog.c fe40fab5c077a40477f7e5eba994309ecac6cc95 F ext/misc/vtshim.c babb0dc2bf116029e3e7c9a618b8a1377045303e F ext/misc/wholenumber.c 784b12543d60702ebdd47da936e278aa03076212 F ext/ota/ota.c feb0a11f720a8f30d2bebb835bba3c131a725685 -F ext/ota/ota1.test 1684d4a1c4137190368ee5fd31c91b223172ed89 +F ext/ota/ota1.test 960418e4171a989426f8b1ad8ee31770e0f94fb8 F ext/ota/ota10.test 85e0f6e7964db5007590c1b299e75211ed4240d4 F ext/ota/ota11.test 2f606cd2b4af260a86b549e91b9f395450fc75cb F ext/ota/ota12.test 0dff44474de448fb4b0b28c20da63273a4149abb @@ -325,8 +325,8 @@ F src/vxworks.h c18586c8edc1bddbc15c004fa16aeb1e1342b4fb F src/wal.c 39303f2c9db02a4e422cd8eb2c8760420c6a51fe F src/wal.h df01efe09c5cb8c8e391ff1715cca294f89668a4 F src/walker.c c253b95b4ee44b21c406e2a1052636c31ea27804 -F src/where.c eb141b075776e9864d38f279333e2472a8653202 -F src/whereInt.h cbe4aa57326998d89e7698ca65bb7c28541d483c +F src/where.c 67c115096fac6c286ab517939d368cfb12ed0d2d +F src/whereInt.h c1fd5690f91d9551c0c42aa44205248bbd3f7650 F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2 F test/aggerror.test a867e273ef9e3d7919f03ef4f0e8c0d2767944f2 F test/aggnested.test b35b4cd69fc913f90d39a575e171e1116c3a4bb7 @@ -1191,6 +1191,7 @@ F test/whereG.test 69f5ec4b15760a8c860f80e2d55525669390aab3 F test/whereH.test e4b07f7a3c2f5d31195cd33710054c78667573b2 F test/whereI.test 1d89199697919d4930be05a71e7fe620f114e622 F test/whereJ.test 55a3221706a7ab706293f17cc8f96da563bf0767 +F test/whereK.test 424caa7391ca5c41484a37cc4a13dc9ed6243bee F test/wherelimit.test 5e9fd41e79bb2b2d588ed999d641d9c965619b31 F test/wild001.test bca33f499866f04c24510d74baf1e578d4e44b1c F test/win32heap.test ea19770974795cff26e11575e12d422dbd16893c @@ -1260,7 +1261,7 @@ F tool/vdbe_profile.tcl 67746953071a9f8f2f668b73fe899074e2c6d8c1 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh 0abfd78ceb09b7f7c27c688c8e3fe93268a13b32 F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P 5489cb68921f62f10d832adbc4d19ea8c6c5da50 -R ca30e53e82893df5e57d16fcd6b31c80 +P 2e7c1e0a0d128d9bac119692b2505b5ed0abe87a +R fd8b7ec489c2b24931575fb551b9bcda U dan -Z c06907a568937b6ba3fe4165dc01a3f0 +Z 501dc9071dd532eb126e9e20f313175e diff --git a/manifest.uuid b/manifest.uuid index 6324a8a7da..7e8cc7ca9c 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -2e7c1e0a0d128d9bac119692b2505b5ed0abe87a \ No newline at end of file +52e73eeca063bb30092ce600068bf487641399a0 \ No newline at end of file diff --git a/src/where.c b/src/where.c index 8b9060b0a7..e95fd57f34 100644 --- a/src/where.c +++ b/src/where.c @@ -362,7 +362,7 @@ static int allowedOp(int op){ assert( TK_LT>TK_EQ && TK_LTTK_EQ && TK_LE=TK_EQ && op<=TK_GE) || op==TK_ISNULL; + return op==TK_IN || (op>=TK_EQ && op<=TK_GE) || op==TK_ISNULL || op==TK_IS; } /* @@ -415,6 +415,8 @@ static u16 operatorMask(int op){ c = WO_IN; }else if( op==TK_ISNULL ){ c = WO_ISNULL; + }else if( op==TK_IS ){ + c = WO_IS; }else{ assert( (WO_EQ<<(op-TK_EQ)) < 0x7fff ); c = (u16)(WO_EQ<<(op-TK_EQ)); @@ -2596,7 +2598,7 @@ static int codeEqualityTerm( int iReg; /* Register holding results */ assert( iTarget>0 ); - if( pX->op==TK_EQ ){ + if( pX->op==TK_EQ || pX->op==TK_IS ){ iReg = sqlite3ExprCodeTarget(pParse, pX->pRight, iTarget); }else if( pX->op==TK_ISNULL ){ iReg = iTarget; @@ -2781,7 +2783,7 @@ static int codeAllEqualityTerms( testcase( pTerm->eOperator & WO_IN ); if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){ Expr *pRight = pTerm->pExpr->pRight; - if( sqlite3ExprCanBeNull(pRight) ){ + if( sqlite3ExprCanBeNull(pRight) && pTerm->eOperator!=WO_IS ){ sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk); VdbeCoverage(v); } @@ -6112,13 +6114,15 @@ static int whereShortCut(WhereLoopBuilder *pBuilder){ pLoop->rRun = 33; /* 33==sqlite3LogEst(10) */ }else{ for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ + u32 mask = WO_EQ; assert( pLoop->aLTermSpace==pLoop->aLTerm ); if( !IsUniqueIndex(pIdx) || pIdx->pPartIdxWhere!=0 || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) ) continue; + if( HasRowid(pTab)==0 && IsPrimaryKeyIndex(pIdx) ) mask |= WO_IS; for(j=0; jnKeyCol; j++){ - pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx); + pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, mask, pIdx); if( pTerm==0 ) break; pLoop->aLTerm[j] = pTerm; } diff --git a/src/whereInt.h b/src/whereInt.h index 04cc2029d8..ee310f90a0 100644 --- a/src/whereInt.h +++ b/src/whereInt.h @@ -440,8 +440,9 @@ struct WhereInfo { #define WO_AND 0x200 /* Two or more AND-connected terms */ #define WO_EQUIV 0x400 /* Of the form A==B, both columns */ #define WO_NOOP 0x800 /* This term does not restrict search space */ +#define WO_IS 0x1000 /* The IS operator */ -#define WO_ALL 0xfff /* Mask of all possible WO_* values */ +#define WO_ALL 0x1fff /* Mask of all possible WO_* values */ #define WO_SINGLE 0x0ff /* Mask of all non-compound WO_* values */ /* diff --git a/test/whereK.test b/test/whereK.test new file mode 100644 index 0000000000..10dd65e01f --- /dev/null +++ b/test/whereK.test @@ -0,0 +1,63 @@ +# 2015-03-19 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix whereK + +do_execsql_test 1.0 { + CREATE TABLE t1(v, w TEXT, x, PRIMARY KEY(w, x)) WITHOUT ROWID; + + INSERT INTO t1 VALUES('a', 1, 1); + INSERT INTO t1 VALUES('B', 2, 2); + INSERT INTO t1 VALUES('C', 3, 3); + INSERT INTO t1 VALUES('d', 4, 4); + + CREATE TABLE t2(v, w TEXT, x, PRIMARY KEY(w, x)); + + CREATE TABLE t3(a, b, c); + CREATE UNIQUE INDEX t3a ON t3(a); +} + +do_eqp_test 1.1.1 { + SELECT v FROM t1 WHERE w IS ? AND x IS ? +} {/SEARCH TABLE t1.*(w=. AND x=.)/} + +do_eqp_test 1.1.2 { + SELECT v FROM t2 WHERE w IS ? AND x IS ? +} {/SCAN TABLE t2/} + +do_execsql_test 1.2.1 { SELECT v FROM t1 WHERE w IS 1 AND x IS 1 } {a} +do_execsql_test 1.2.2 { SELECT v FROM t1 WHERE w IS '2' AND x IS 2 } {B} +do_execsql_test 1.2.3 { SELECT v FROM t1 WHERE w IS 3 AND x IS '3' } {} +do_execsql_test 1.2.4 { SELECT v FROM t1 WHERE w IS '4' AND x IS '4' } {} + + +# IS constraints may only be used if all fields of the index are +# constrained by either "IS" or "=". +# +do_eqp_test 1.3.1 { + SELECT v FROM t1 WHERE w IS ? +} {/SCAN TABLE t1/} +do_eqp_test 1.3.2 { + SELECT v FROM t1 WHERE w IS ? AND x > ? +} {/SCAN TABLE t1/} + +do_execsql_test 1.4.1 { SELECT v FROM t1 WHERE w IS 3 AND x = 3 } {C} +do_execsql_test 1.4.2 { SELECT v FROM t1 WHERE w = '4' AND x IS 4 } {d} + +do_eqp_test 1.5.1 { SELECT b FROM t3 WHERE a IS ? } {/SCAN TABLE/} +do_eqp_test 1.5.2 { SELECT b FROM t3 WHERE a = ? } {/SEARCH TABLE/} + +finish_test + + -- 2.47.3