From 31d6fd5507ce0f7299a64eb20026ffa3e376b9f8 Mon Sep 17 00:00:00 2001 From: drh Date: Fri, 14 Apr 2017 19:03:10 +0000 Subject: [PATCH] Add the TK_IF_NULL_ROW opcode to deal with non-NULL result columns in the result set of a view or subquery on the RHS of a LEFT JOIN that gets flattened. FossilOrigin-Name: 3a5860d86fadcf924316707918bf283d26c53b1473e5e67f5cff59d18c2a7742 --- manifest | 27 ++++++++++++--------------- manifest.uuid | 2 +- src/expr.c | 11 +++++++++++ src/parse.y | 34 +++++++++++++++++----------------- src/select.c | 31 +++++++++++++++++-------------- src/treeview.c | 5 +++++ src/vdbe.c | 17 +++++++++++++++++ src/where.c | 2 ++ tool/addopcodes.tcl | 1 + 9 files changed, 83 insertions(+), 47 deletions(-) diff --git a/manifest b/manifest index a7b73eecdf..155f797a31 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C An\sinitial\sattempt\sto\soptimize\sVIEWs\sthat\soccur\sas\sthe\sright\soperand\sof\sa\nLEFT\sJOIN.\s\sThis\sparticular\scheck-in\sdoes\snot\swork\scorrectly\sbecause\sit\sdoes\nnot\sdeal\swith\sthe\scase\sof\scolumns\sin\sthe\sVIEW\sthat\sreturn\snon-NULL\seven\swhen\nall\scolumns\sin\sthe\stable\sof\sthe\sVIEW\sare\sNULL\sbecause\sof\sthe\sLEFT\sJOIN. -D 2017-04-14T17:18:45.647 +C Add\sthe\sTK_IF_NULL_ROW\sopcode\sto\sdeal\swith\snon-NULL\sresult\scolumns\sin\sthe\nresult\sset\sof\sa\sview\sor\ssubquery\son\sthe\sRHS\sof\sa\sLEFT\sJOIN\sthat\sgets\sflattened. +D 2017-04-14T19:03:10.224 F Makefile.in 1cc758ce3374a32425e4d130c2fe7b026b20de5b8843243de75f087c0a2661fb F Makefile.linux-gcc 7bc79876b875010e8c8f9502eb935ca92aa3c434 F Makefile.msc 6a8c838220f7c00820e1fc0ac1bccaaa8e5676067e1dbfa1bafa7a4ffecf8ae6 @@ -354,7 +354,7 @@ F src/ctime.c 47d91a25ad8f199a71a5b1b7b169d6dd0d6e98c5719eca801568798743d1161c F src/date.c ee676e7694dfadbdd2fde1a258a71be8360ba5ae F src/dbstat.c 19ee7a4e89979d4df8e44cfac7a8f905ec89b77d F src/delete.c 0d9d5549d42e79ce4d82ff1db1e6c81e36d2f67c -F src/expr.c f6914d6d06c9a1e488f49cd51a0ef12d8073231e9c1a25c4c821262686cadcbf +F src/expr.c f10e35dc50be4c8f82eb99bf5d8530229d1d60957cc3c9473ffe584d0444087c F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007 F src/fkey.c db65492ae549c3b548c9ef1f279ce1684f1c473b116e1c56a90878cd5dcf968d F src/func.c 9d52522cc8ae7f5cdadfe14594262f1618bc1f86083c4cd6da861b4cf5af6174 @@ -390,7 +390,7 @@ F src/os_win.c 2a6c73eef01c51a048cc4ddccd57f981afbec18a F src/os_win.h 7b073010f1451abe501be30d12f6bc599824944a F src/pager.c ff1232b3088a39806035ecfac4fffeb22717d80b F src/pager.h f2a99646c5533ffe11afa43e9e0bea74054e4efa -F src/parse.y 48b03113704ee8bd78ee6996d81de7fbee22e105 +F src/parse.y 0513387ce02fea97897d8caef82d45f347818593f24f1bdc48e0c530a8af122d F src/pcache.c 62835bed959e2914edd26afadfecce29ece0e870 F src/pcache.h 2cedcd8407eb23017d92790b112186886e179490 F src/pcache1.c 1195a21fe28e223e024f900b2011e80df53793f0356a24caace4188b098540dc @@ -401,7 +401,7 @@ F src/printf.c 8757834f1b54dae512fb25eb1acc8e94a0d15dd2290b58f2563f65973265adb2 F src/random.c 80f5d666f23feb3e6665a6ce04c7197212a88384 F src/resolve.c 3e518b962d932a997fae373366880fc028c75706 F src/rowset.c 7b7e7e479212e65b723bf40128c7b36dc5afdfac -F src/select.c 52b29bdeaa4cf317a981b6e8d0a15ec9c16372a738ede51c2a285c03fc69d036 +F src/select.c bf8ab605e49717c222136380453cfb7eda564f8e500d5ff6a01341ea59fefe80 F src/shell.c 70f4957b988572315e97c56941fdc81fd35907fee36b7b2e7be5ec4c7e9d065d F src/sqlite.h.in 40233103e3e4e10f8a63523498d0259d232e42aba478e2d3fb914799185aced6 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8 @@ -462,13 +462,13 @@ F src/test_windirent.h 5d67483a55442e31e1bde0f4a230e6e932ad5906 F src/test_wsd.c 41cadfd9d97fe8e3e4e44f61a4a8ccd6f7ca8fe9 F src/threads.c 4ae07fa022a3dc7c5beb373cf744a85d3c5c6c3c F src/tokenize.c 1003d6d90c6783206c711f0a9397656fa5b055209f4d092caa43bb3bf5215db5 -F src/treeview.c b92d57c1ac59f4a3f6b189506921a2b48098f6f4d6afd0b715bc2815ef6af092 +F src/treeview.c 6cf8d7fe9e63fae57dad1bb57f6615e14eac0c527e43d868e805042cae8ed1f7 F src/trigger.c c9f0810043b265724fdb1bdd466894f984dfc182 F src/update.c c443935c652af9365e033f756550b5032d02e1b06eb2cb890ed7511ae0c051dc F src/utf.c 699001c79f28e48e9bcdf8a463da029ea660540c F src/util.c ca8440ede81e155d15cff7c101654f60b55a9ae6 F src/vacuum.c 1fe4555cd8c9b263afb85b5b4ee3a4a4181ad569 -F src/vdbe.c 808fda3d50f544120d27c731449b524b4ec8f8b0f734b228831078f0ba53ecb9 +F src/vdbe.c ef0fd44a2b4f70e006d050a98c6f563e19293d683975a95287668340764f99f1 F src/vdbe.h f7d1456e28875c2dcb964056589b5b7149ab7edf39edeca801596a39bb3d3848 F src/vdbeInt.h c070bc5c8b913bda0ceaa995cd4d939ded5e4fc96cf7c3c1c602d41b871f8ade F src/vdbeapi.c 5b08d82592bcff4470601fe78aaabebd50837860 @@ -482,7 +482,7 @@ F src/vxworks.h d2988f4e5a61a4dfe82c6524dd3d6e4f2ce3cdb9 F src/wal.c 40c543f0a2195d1b0dc88ef12142bea690009344 F src/wal.h 06b2a0b599cc0f53ea97f497cf8c6b758c999f71 F src/walker.c b71a992b413b3a022572eccf29ef4b4890223791 -F src/where.c 1d14e18f32231fa7969e718e7b60ef749b0065e2a7e1b6b00883b20732d280f1 +F src/where.c e02cbd8a830d36e48b1640d0c7b49526322e35fa800c729c17c5d63712a01275 F src/whereInt.h 7a21ef633e26acbf46df04add2eba6e0a2100c78dc5879049e93f981fc3344df F src/wherecode.c 943e32e9dccd0af802e0683ae11071c8bd808364e5908a5fb66758bd404c8681 F src/whereexpr.c e913aaa7b73ffcce66abcea5f197e2c538d48b5df78d0b7bba8ff4d73cc2e745 @@ -1483,7 +1483,7 @@ F test/zerodamage.test e59a56443d6298ecf7435f618f0b27654f0c849e F tool/GetFile.cs a15e08acb5dd7539b75ba23501581d7c2b462cb5 F tool/GetTclKit.bat 6afa640edc7810725aec61c3076ac617c4aaf0b7 F tool/Replace.cs 02c67258801c2fb5f63231e0ac0f220b4b36ba91 -F tool/addopcodes.tcl 10c889c4a65ec6c5604e4a47306fa77ff57ae189 +F tool/addopcodes.tcl edbd53806bf20e25af2373ad0c091be4385081c1aa1813b916bf093f94ed8380 F tool/build-all-msvc.bat c12328d06c45fec8baada5949e3d5af54bf8c887 x F tool/build-shell.sh 950f47c6174f1eea171319438b93ba67ff5bf367 F tool/cg_anno.tcl f95b0006c52cf7f0496b506343415b6ee3cdcdd3 x @@ -1571,10 +1571,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 6bf673767b8e5cedef1acff795cbe524fab8db2525c06424db4e038934a33936 -R 8dfa3ca7454fc6936bedb04ccf47ee12 -T *branch * left-join-view -T *sym-left-join-view * -T -sym-trunk * +P 1838a59c8a1c151bd6fc822b0ffef661803cf0e4704c917e74a04567b81740b9 +R c4158b7e84d910d8bf443bd31845d425 U drh -Z 5ef873863ca7134987a034a627c04882 +Z 30ed19e82c986c3025ce3aa1a6102ed4 diff --git a/manifest.uuid b/manifest.uuid index f881b041a7..bf413e2eb0 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -1838a59c8a1c151bd6fc822b0ffef661803cf0e4704c917e74a04567b81740b9 \ No newline at end of file +3a5860d86fadcf924316707918bf283d26c53b1473e5e67f5cff59d18c2a7742 \ No newline at end of file diff --git a/src/expr.c b/src/expr.c index ad17c5f229..873911633f 100644 --- a/src/expr.c +++ b/src/expr.c @@ -3889,6 +3889,17 @@ int sqlite3ExprCodeTarget(Parse *pParse, Expr *pExpr, int target){ break; } + case TK_IF_NULL_ROW: { + int addrINR; + addrINR = sqlite3VdbeAddOp1(v, OP_IfNullRow, pExpr->iTable); + sqlite3ExprCachePush(pParse); + inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target); + sqlite3ExprCachePop(pParse); + sqlite3VdbeJumpHere(v, addrINR); + sqlite3VdbeChangeP3(v, addrINR, inReg); + break; + } + /* ** Form A: ** CASE x WHEN e1 THEN r1 WHEN e2 THEN r2 ... WHEN eN THEN rN ELSE y END diff --git a/src/parse.y b/src/parse.y index ef9d3dd0ec..522c805482 100644 --- a/src/parse.y +++ b/src/parse.y @@ -193,6 +193,23 @@ columnlist ::= columnlist COMMA columnname carglist. columnlist ::= columnname carglist. columnname(A) ::= nm(A) typetoken(Y). {sqlite3AddColumn(pParse,&A,&Y);} +// The following directive causes tokens ABORT, AFTER, ASC, etc. to +// fallback to ID if they will not parse as their original value. +// This obviates the need for the "id" nonterminal. +// +%fallback ID + ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW + CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR + IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN + QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW + ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT +%ifdef SQLITE_OMIT_COMPOUND_SELECT + EXCEPT INTERSECT UNION +%endif SQLITE_OMIT_COMPOUND_SELECT + REINDEX RENAME CTIME_KW IF + . +%wildcard ANY. + // Define operator precedence early so that this is the first occurrence // of the operator tokens in the grammer. Keeping the operators together // causes them to be assigned integer values that are close together, @@ -222,23 +239,6 @@ columnname(A) ::= nm(A) typetoken(Y). {sqlite3AddColumn(pParse,&A,&Y);} // %token_class id ID|INDEXED. -// The following directive causes tokens ABORT, AFTER, ASC, etc. to -// fallback to ID if they will not parse as their original value. -// This obviates the need for the "id" nonterminal. -// -%fallback ID - ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW - CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR - IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN - QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW - ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT -%ifdef SQLITE_OMIT_COMPOUND_SELECT - EXCEPT INTERSECT UNION -%endif SQLITE_OMIT_COMPOUND_SELECT - REINDEX RENAME CTIME_KW IF - . -%wildcard ANY. - // And "ids" is an identifer-or-string. // diff --git a/src/select.c b/src/select.c index 8e952d5a8a..3def13618e 100644 --- a/src/select.c +++ b/src/select.c @@ -3154,7 +3154,8 @@ static int multiSelectOrderBy( typedef struct SubstContext { Parse *pParse; /* The parsing context */ int iTable; /* Replace references to this table */ - int iNewTable; /* New table number, or -1 */ + int iNewTable; /* New table number */ + int isLeftJoin; /* Add TK_IF_NULL_ROW opcodes on each replacement */ ExprList *pEList; /* Replacement expressions */ } SubstContext; @@ -3189,12 +3190,20 @@ static Expr *substExpr( }else{ Expr *pNew; Expr *pCopy = pSubst->pEList->a[pExpr->iColumn].pExpr; + Expr ifNullRow; assert( pSubst->pEList!=0 && pExpr->iColumnpEList->nExpr ); assert( pExpr->pLeft==0 && pExpr->pRight==0 ); if( sqlite3ExprIsVector(pCopy) ){ sqlite3VectorErrorMsg(pSubst->pParse, pCopy); }else{ sqlite3 *db = pSubst->pParse->db; + if( pSubst->isLeftJoin && pCopy->op!=TK_COLUMN ){ + memset(&ifNullRow, 0, sizeof(ifNullRow)); + ifNullRow.op = TK_IF_NULL_ROW; + ifNullRow.pLeft = pCopy; + ifNullRow.iTable = pSubst->iNewTable; + pCopy = &ifNullRow; + } pNew = sqlite3ExprDup(db, pCopy, 0); if( pNew && (pExpr->flags & EP_FromJoin) ){ pNew->iRightJoinTable = pExpr->iRightJoinTable; @@ -3288,14 +3297,8 @@ static void substSelect( ** FROM-clause subquery that is a candidate for flattening. (2b is ** due to ticket [2f7170d73bf9abf80] from 2015-02-09.) ** -** (3) The subquery is not the right operand of a left outer join -** or (3b) the subquery is not a join and (3c) does not contain any -** result-set columns that could be non-NULL even if columns of the -** subquery table are NULL. -** to be more restrictive - disallowing subquery if it was the -** right operand of a left join. See tickets #306 and #3300. Relaxed -** to allow simple subqueries as the right operand of a left join -** on 2017-04-14.) +** (3) The subquery is not the right operand of a LEFT JOIN +** or the subquery is not itself a join. ** ** (4) The subquery is not DISTINCT. ** @@ -3307,7 +3310,7 @@ static void substSelect( ** DISTINCT. ** ** (7) The subquery has a FROM clause. TODO: For subqueries without -** A FROM clause, consider adding a FROM close with the special +** A FROM clause, consider adding a FROM clause with the special ** table sqlite_once that consists of a single row containing a ** single NULL. ** @@ -3491,15 +3494,13 @@ static int flattenSubquery( ** ** which is not at all the same thing. ** - ** See also tickets #306 and #3300. + ** See also tickets #306, #350, and #3300. */ if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){ isLeftJoin = 1; if( pSubSrc->nSrc>1 ){ - return 0; /* Restriction (3b) */ + return 0; /* Restriction (3) */ } - /* TBD: Deal with the case of result-set expressions that are non-NULL even - ** when the RHS table of a LEFT JOIN is NULL. */ } /* Restriction 17: If the sub-query is a compound SELECT, then it must @@ -3774,6 +3775,7 @@ static int flattenSubquery( x.pParse = pParse; x.iTable = iParent; x.iNewTable = iNewParent; + x.isLeftJoin = isLeftJoin; x.pEList = pSub->pEList; substSelect(&x, pParent, 0); } @@ -3883,6 +3885,7 @@ static int pushDownWhereTerms( x.pParse = pParse; x.iTable = iCursor; x.iNewTable = iCursor; + x.isLeftJoin = 0; x.pEList = pSubq->pEList; pNew = substExpr(&x, pNew); pSubq->pWhere = sqlite3ExprAnd(pParse->db, pSubq->pWhere, pNew); diff --git a/src/treeview.c b/src/treeview.c index e4f3d781ba..fc188256f9 100644 --- a/src/treeview.c +++ b/src/treeview.c @@ -470,6 +470,11 @@ void sqlite3TreeViewExpr(TreeView *pView, const Expr *pExpr, u8 moreToFollow){ sqlite3TreeViewSelect(pView, pExpr->pLeft->x.pSelect, 0); break; } + case TK_IF_NULL_ROW: { + sqlite3TreeViewLine(pView, "IF-NULL-ROW %d", pExpr->iTable); + sqlite3TreeViewExpr(pView, pExpr->pLeft, 0); + break; + } default: { sqlite3TreeViewLine(pView, "op=%d", pExpr->op); break; diff --git a/src/vdbe.c b/src/vdbe.c index a990afb11d..cd3d08a140 100644 --- a/src/vdbe.c +++ b/src/vdbe.c @@ -2429,6 +2429,23 @@ case OP_NotNull: { /* same as TK_NOTNULL, jump, in1 */ break; } +/* Opcode: IfNullRow P1 P2 P3 * * +** Synopsis: if P1.nullRow then r[P3]=NULL, goto P2 +** +** Check the cursor P1 to see if it is currently pointing at a NULL row. +** If it is, then set register P3 to NULL and jump immediately to P2. +** If P1 is not on a NULL row, then fall through without making any +** changes. +*/ +case OP_IfNullRow: { /* jump */ + assert( pOp->p1>=0 && pOp->p1nCursor ); + if( p->apCsr[pOp->p1]->nullRow ){ + sqlite3VdbeMemSetNull(aMem + pOp->p3); + goto jump_to_p2; + } + break; +} + /* Opcode: Column P1 P2 P3 P4 P5 ** Synopsis: r[P3]=PX ** diff --git a/src/where.c b/src/where.c index 38183d6849..5c5652f058 100644 --- a/src/where.c +++ b/src/where.c @@ -4976,6 +4976,8 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ }else if( pOp->opcode==OP_Rowid ){ pOp->p1 = pLevel->iIdxCur; pOp->opcode = OP_IdxRowid; + }else if( pOp->opcode==OP_IfNullRow ){ + pOp->p1 = pLevel->iIdxCur; } } } diff --git a/tool/addopcodes.tcl b/tool/addopcodes.tcl index a6c58f1a25..308ddcb6bd 100644 --- a/tool/addopcodes.tcl +++ b/tool/addopcodes.tcl @@ -39,6 +39,7 @@ set extras { REGISTER VECTOR SELECT_COLUMN + IF_NULL_ROW ASTERISK SPAN SPACE -- 2.39.5