From 15ccce1c0d3c5ed8604201795361559c3cb22023 Mon Sep 17 00:00:00 2001 From: drh Date: Mon, 23 May 2005 15:06:39 +0000 Subject: [PATCH] Make sure that the use of a double-quoted string literal does not trick the optimizer into using a correlated subquery when a static subquery would suffice. (CVS 2477) FossilOrigin-Name: ef4059e3afa1a61a9e59df00cdfedc57d8df9fec --- manifest | 16 ++++++++-------- manifest.uuid | 2 +- src/expr.c | 25 +++++++++++++++++++------ test/subquery.test | 42 ++++++++++++++++++++++++++++++++++++++++-- 4 files changed, 68 insertions(+), 17 deletions(-) diff --git a/manifest b/manifest index a553b39c66..b3cc33eed5 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Retain\sthe\serror\sstring\sif\san\serror\sis\sgenerated\sby\sSSE\sduring\sa\sVACUUM.\s(CVS\s2476) -D 2005-05-23T13:00:58 +C Make\ssure\sthat\sthe\suse\sof\sa\sdouble-quoted\sstring\sliteral\sdoes\snot\strick\nthe\soptimizer\sinto\susing\sa\scorrelated\ssubquery\swhen\sa\sstatic\nsubquery\swould\ssuffice.\s(CVS\s2477) +D 2005-05-23T15:06:39 F Makefile.in 5c00d0037104de2a50ac7647a5f12769795957a3 F Makefile.linux-gcc 06be33b2a9ad4f005a5f42b22c4a19dab3cbb5c7 F README 9c4e2d6706bdcc3efdd773ce752a8cdab4f90028 @@ -36,7 +36,7 @@ F src/build.c 6770abc63df9f3ed9f44c50411bb63692cb4589f F src/date.c 2134ef4388256e8247405178df8a61bd60dc180a F src/delete.c 75b53db21aa1879d3655bbbc208007db31d58a63 F src/experimental.c 50c1e3b34f752f4ac10c36f287db095c2b61766d -F src/expr.c bef1589dbd9481b88f26d7908fc70bad9576e541 +F src/expr.c f2ff12d9970d844564ec8b3ce4ea702bf6730b6a F src/func.c d09df82e35ef988cd28a3ffd63cd772271b7def9 F src/hash.c 2b1b13f7400e179631c83a1be0c664608c8f021f F src/hash.h 1b0c445e1c89ff2aaad9b4605ba61375af001e84 @@ -189,7 +189,7 @@ F test/select5.test 2d414f712bff8e590091e08f9b7287600731be00 F test/select6.test 6e5a1a70a788cdbe515d1252dd0917d7e9d1d71e F test/select7.test 1bf795b948c133a15a2a5e99d3270e652ec58ce6 F test/sort.test 312eade533cb3c7667110ccfa6e818db1078fd6c -F test/subquery.test 6274cce7617bc7f625490759cbe4f0c0eef24895 +F test/subquery.test 0e37f0f032799c28aa8fcc0dc04ee28a78e5ce8b F test/subselect.test 3f3f7a940dc3195c3139f4d530385cb54665d614 F test/table.test e87fb2211b97c6a3a367fbc116e8572091b53160 F test/tableapi.test 6a66d58b37d46dc0f2b3c7d4bd2617d209399bd1 @@ -279,7 +279,7 @@ F www/tclsqlite.tcl 425be741b8ae664f55cb1ef2371aab0a75109cf9 F www/vdbe.tcl 87a31ace769f20d3627a64fa1fade7fed47b90d0 F www/version3.tcl a99cf5f6d8bd4d5537584a2b342f0fb9fa601d8b F www/whentouse.tcl 528299b8316726dbcc5548e9aa0648c8b1bd055b -P 2a8ac869671b627d4d01655bbce7a781bc74af44 -R b1031030db9f7c66e26c25de4a342f36 -U danielk1977 -Z ceb8d3cc11da91ad4514aeaf74501d5d +P f7b76d02e003faf0310b87949d3cb0f38062853f +R 7f9d3157fe368f24bc8a9f1b20f7fe23 +U drh +Z b4157a0f7dc132bb0373ae1877998e51 diff --git a/manifest.uuid b/manifest.uuid index ad7b209bae..f9adbff574 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -f7b76d02e003faf0310b87949d3cb0f38062853f \ No newline at end of file +ef4059e3afa1a61a9e59df00cdfedc57d8df9fec \ No newline at end of file diff --git a/src/expr.c b/src/expr.c index 79c7b59e2a..6237e75d21 100644 --- a/src/expr.c +++ b/src/expr.c @@ -12,7 +12,7 @@ ** This file contains routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions in SQLite. ** -** $Id: expr.c,v 1.198 2005/04/22 02:38:38 drh Exp $ +** $Id: expr.c,v 1.199 2005/05/23 15:06:39 drh Exp $ */ #include "sqliteInt.h" #include @@ -790,7 +790,6 @@ static int lookupName( SrcList *pSrcList = pNC->pSrcList; ExprList *pEList = pNC->pEList; - pNC->nRef++; /* assert( zTab==0 || pEList==0 ); */ if( pSrcList ){ for(i=0, pItem=pSrcList->a; inSrc; i++, pItem++){ @@ -898,9 +897,9 @@ static int lookupName( pExpr->op = TK_AS; pExpr->iColumn = j; pExpr->pLeft = sqlite3ExprDup(pEList->a[j].pExpr); - sqliteFree(zCol); + cnt = 1; assert( zTab==0 && zDb==0 ); - return 0; + goto lookupname_end_2; } } } @@ -919,6 +918,9 @@ static int lookupName( ** Z is a string literal if it doesn't match any column names. In that ** case, we need to return right away and not make any changes to ** pExpr. + ** + ** Because no reference was made to outer contexts, the pNC->nRef + ** fields are not changed in any context. */ if( cnt==0 && zTab==0 && pColumnToken->z[0]=='"' ){ sqliteFree(zCol); @@ -965,20 +967,31 @@ lookupname_end: */ sqliteFree(zDb); sqliteFree(zTab); - sqliteFree(zCol); sqlite3ExprDelete(pExpr->pLeft); pExpr->pLeft = 0; sqlite3ExprDelete(pExpr->pRight); pExpr->pRight = 0; pExpr->op = TK_COLUMN; +lookupname_end_2: + sqliteFree(zCol); if( cnt==1 ){ assert( pNC!=0 ); sqlite3AuthRead(pParse, pExpr, pNC->pSrcList); if( pMatch && !pMatch->pSelect ){ pExpr->pTab = pMatch->pTab; } + /* Increment the nRef value on all name contexts from TopNC up to + ** the point where the name matched. */ + for(;;){ + assert( pTopNC!=0 ); + pTopNC->nRef++; + if( pTopNC==pNC ) break; + pTopNC = pTopNC->pNext; + } + return 0; + } else { + return 1; } - return cnt!=1; } /* diff --git a/test/subquery.test b/test/subquery.test index fb98f7ce37..4b626ed83a 100644 --- a/test/subquery.test +++ b/test/subquery.test @@ -11,7 +11,7 @@ # This file implements regression tests for SQLite library. The # focus of this script is testing correlated subqueries # -# $Id: subquery.test,v 1.8 2005/02/14 06:38:40 danielk1977 Exp $ +# $Id: subquery.test,v 1.9 2005/05/23 15:06:39 drh Exp $ # set testdir [file dirname $argv0] @@ -335,8 +335,46 @@ do_test subquery-4.2.2 { execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1)} } {} +#------------------------------------------------------------------ +# The subquery-5.* tests make sure string literals in double-quotes +# are handled efficiently. Double-quote literals are first checked +# to see if they match any column names. If there is not column name +# match then those literals are used a string constants. When a +# double-quoted string appears, we want to make sure that the search +# for a matching column name did not cause an otherwise static subquery +# to become a dynamic (correlated) subquery. +# +do_test subquery-5.1 { + proc callcntproc {n} { + incr ::callcnt + return $n + } + set callcnt 0 + db function callcnt callcntproc + execsql { + CREATE TABLE t4(x,y); + INSERT INTO t4 VALUES('one',1); + INSERT INTO t4 VALUES('two',2); + INSERT INTO t4 VALUES('three',3); + INSERT INTO t4 VALUES('four',4); + CREATE TABLE t5(a,b); + INSERT INTO t5 VALUES(1,11); + INSERT INTO t5 VALUES(2,22); + INSERT INTO t5 VALUES(3,33); + INSERT INTO t5 VALUES(4,44); + SELECT b FROM t5 WHERE a IN + (SELECT callcnt(y)+0 FROM t4 WHERE x="two") + } +} {22} +do_test subquery-5.2 { + # This is the key test. The subquery should have only run once. If + # The double-quoted identifier "two" were causing the subquery to be + # processed as a correlated subquery, then it would have run 4 times. + set callcnt +} {1} + -finish_test +finish_test -- 2.47.3