From c133bab72a60fc020fffce0a329c1a4092f20aa0 Mon Sep 17 00:00:00 2001 From: drh <> Date: Mon, 11 Apr 2022 20:15:52 +0000 Subject: [PATCH] The query flattener must add TK_IF_NULL_ROW opcodes on substituted values that land on the left operand of a RIGHT JOIN, just as it already does for the right operand of a LEFT JOIN. FossilOrigin-Name: 8e02cdf5b1128f5e5b82d93903063415ec312694e5ccdd19e99fa35433f1b68a --- manifest | 12 ++++++------ manifest.uuid | 2 +- src/select.c | 52 +++++++++++++++++++++++++++++++++++++++------------ 3 files changed, 47 insertions(+), 19 deletions(-) diff --git a/manifest b/manifest index 0a663ec1c8..a2d3e3fd54 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C New\stest\scases\sadded. -D 2022-04-11T18:54:23.445 +C The\squery\sflattener\smust\sadd\sTK_IF_NULL_ROW\sopcodes\son\ssubstituted\svalues\nthat\sland\son\sthe\sleft\soperand\sof\sa\sRIGHT\sJOIN,\sjust\sas\sit\salready\sdoes\sfor\nthe\sright\soperand\sof\sa\sLEFT\sJOIN. +D 2022-04-11T20:15:52.179 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -552,7 +552,7 @@ F src/printf.c 05d8dfd2018bc4fc3ddb8b37eb97ccef7abf985643fa1caebdcf2916ca90fa32 F src/random.c 097dc8b31b8fba5a9aca1697aeb9fd82078ec91be734c16bffda620ced7ab83c F src/resolve.c 7110fc3b5a4dec5d11559141c1906c4a125349fb602f541b05db3a3d448d4b95 F src/rowset.c ba9515a922af32abe1f7d39406b9d35730ed65efab9443dc5702693b60854c92 -F src/select.c 67d793d9d3008699bc67a079de9eafc0c07d84c12fb867e5b735a79a456a77cb +F src/select.c fdec126045cf441883836756d6d949367e8a2945c53fa6e98d6c51a08d7efc81 F src/shell.c.in eb7f10d5e2c47bd014d92ec5db1def21fcc1ed56ffaaa4ee715b6c37c370b47f F src/sqlite.h.in 2a35f62185eb5e7ecc64a2f68442b538ce9be74f80f28a00abc24837edcf1c17 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 @@ -1946,8 +1946,8 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P b6e773a26c2c6ee76ea61acb059b4e676d07ea62f6db9c513638f8986557cf04 -R ea6a6206ccd15b1ede34fe0a185bc5f1 +P bdd1499c0fa4f8aadf4857a0ccc0d839c250369f29766ebef80330964905e63b +R 1528160c8ffde5ccc7d4edc8311e6b3e U drh -Z 7ac11620004b739208f6ff19e0608f85 +Z 0a6599330cad92a7a73b62ce77e2586b # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index 3f8fbe197f..bd94247551 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -bdd1499c0fa4f8aadf4857a0ccc0d839c250369f29766ebef80330964905e63b \ No newline at end of file +8e02cdf5b1128f5e5b82d93903063415ec312694e5ccdd19e99fa35433f1b68a \ No newline at end of file diff --git a/src/select.c b/src/select.c index cede26d1c7..7230ff90c9 100644 --- a/src/select.c +++ b/src/select.c @@ -3654,12 +3654,40 @@ static int multiSelectOrderBy( ** ** All references to columns in table iTable are to be replaced by corresponding ** expressions in pEList. +** +** ## About "isOuterJoin": +** +** The isOuterJoin column indicates that the replacement will occur into a +** position in the parent that NULL-able due to an OUTER JOIN. Either the +** target slot in the parent is the right operand of a LEFT JOIN, or one of +** the left operands of a RIGHT JOIN. In either case, we need to potentially +** bypass the substituted expression with OP_IfNullRow. +** +** Suppose the original expression integer constant. Even though the table +** has the nullRow flag set, because the expression is an integer constant, +** it will not be NULLed out. So instead, we insert an OP_IfNullRow opcode +** that checks to see if the nullRow flag is set on the table. If the nullRow +** flag is set, then the value in the register is set to NULL and the original +** expression is bypassed. If the nullRow flag is not set, then the original +** expression runs to populate the register. +** +** Example where this is needed: +** +** CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT); +** CREATE TABLE t2(x INT UNIQUE); +** +** SELECT a,b,m,x FROM t1 LEFT JOIN (SELECT 59 AS m,x FROM t2) ON b=x; +** +** When the subquery on the right side of the LEFT JOIN is flattened, we +** have to add OP_IfNullRow in front of the OP_Integer that implements the +** "m" value of the subquery so that a NULL will be loaded instead of 59 +** when processing a non-matched row of the left. */ typedef struct SubstContext { Parse *pParse; /* The parsing context */ int iTable; /* Replace references to this table */ int iNewTable; /* New table number */ - int isLeftJoin; /* Add TK_IF_NULL_ROW opcodes on each replacement */ + int isOuterJoin; /* Add TK_IF_NULL_ROW opcodes on each replacement */ ExprList *pEList; /* Replacement expressions */ } SubstContext; @@ -3709,7 +3737,7 @@ static Expr *substExpr( sqlite3VectorErrorMsg(pSubst->pParse, pCopy); }else{ sqlite3 *db = pSubst->pParse->db; - if( pSubst->isLeftJoin && pCopy->op!=TK_COLUMN ){ + if( pSubst->isOuterJoin && pCopy->op!=TK_COLUMN ){ memset(&ifNullRow, 0, sizeof(ifNullRow)); ifNullRow.op = TK_IF_NULL_ROW; ifNullRow.pLeft = pCopy; @@ -3723,7 +3751,7 @@ static Expr *substExpr( sqlite3ExprDelete(db, pNew); return pExpr; } - if( pSubst->isLeftJoin ){ + if( pSubst->isOuterJoin ){ ExprSetProperty(pNew, EP_CanBeNull); } if( ExprHasProperty(pExpr,EP_FromJoin|EP_InnerJoin) ){ @@ -4105,7 +4133,7 @@ static int flattenSubquery( SrcList *pSubSrc; /* The FROM clause of the subquery */ int iParent; /* VDBE cursor number of the pSub result set temp table */ int iNewParent = -1;/* Replacement table for iParent */ - int isLeftJoin = 0; /* True if pSub is the right side of a LEFT JOIN */ + int isOuterJoin = 0; /* True if pSub is the right side of a LEFT JOIN */ int i; /* Loop counter */ Expr *pWhere; /* The WHERE clause */ SrcItem *pSubitem; /* The subquery */ @@ -4178,7 +4206,7 @@ static int flattenSubquery( ** ** See also tickets #306, #350, and #3300. */ - if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){ + if( (pSubitem->fg.jointype & (JT_OUTER|JT_LTORJ))!=0 ){ if( pSubSrc->nSrc>1 /* (3a) */ || isAgg /* (3b) */ || IsVirtual(pSubSrc->a[0].pTab) /* (3c) */ @@ -4187,15 +4215,15 @@ static int flattenSubquery( ){ return 0; } - isLeftJoin = 1; + isOuterJoin = 1; } #ifdef SQLITE_EXTRA_IFNULLROW else if( iFrom>0 && !isAgg ){ - /* Setting isLeftJoin to -1 causes OP_IfNullRow opcodes to be generated for + /* Setting isOuterJoin to -1 causes OP_IfNullRow opcodes to be generated for ** every reference to any result column from subquery in a join, even ** though they are not necessary. This will stress-test the OP_IfNullRow ** opcode. */ - isLeftJoin = -1; + isOuterJoin = -1; } #endif @@ -4208,7 +4236,7 @@ static int flattenSubquery( if( pSub->pOrderBy ){ return 0; /* Restriction (20) */ } - if( isAgg || (p->selFlags & SF_Distinct)!=0 || isLeftJoin>0 ){ + if( isAgg || (p->selFlags & SF_Distinct)!=0 || isOuterJoin>0 ){ return 0; /* (17d1), (17d2), or (17f) */ } for(pSub1=pSub; pSub1; pSub1=pSub1->pPrior){ @@ -4454,7 +4482,7 @@ static int flattenSubquery( } pWhere = pSub->pWhere; pSub->pWhere = 0; - if( isLeftJoin>0 ){ + if( isOuterJoin>0 ){ sqlite3SetJoinExpr(pWhere, iNewParent, EP_FromJoin); } if( pWhere ){ @@ -4469,7 +4497,7 @@ static int flattenSubquery( x.pParse = pParse; x.iTable = iParent; x.iNewTable = iNewParent; - x.isLeftJoin = isLeftJoin; + x.isOuterJoin = isOuterJoin; x.pEList = pSub->pEList; substSelect(&x, pParent, 0); } @@ -4930,7 +4958,7 @@ static int pushDownWhereTerms( x.pParse = pParse; x.iTable = iCursor; x.iNewTable = iCursor; - x.isLeftJoin = 0; + x.isOuterJoin = 0; x.pEList = pSubq->pEList; pNew = substExpr(&x, pNew); #ifndef SQLITE_OMIT_WINDOWFUNC -- 2.47.2