Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -3886,10 +3886,21 @@ case TK_VECTOR: { sqlite3ErrorMsg(pParse, "row value misused"); 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 ** Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -190,10 +190,27 @@ } } 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, // which keeps parser tables smaller. @@ -220,27 +237,10 @@ // An IDENTIFIER can be a generic identifier, or one of several // keywords. Any non-standard keyword can also be an identifier. // %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. // %token_class ids ID|STRING. Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -3152,10 +3152,12 @@ ** expressions in pEList. */ 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 */ ExprList *pEList; /* Replacement expressions */ } SubstContext; /* Forward Declarations */ static void substExprList(SubstContext*, ExprList*); @@ -3177,22 +3179,33 @@ static Expr *substExpr( SubstContext *pSubst, /* Description of the substitution */ Expr *pExpr /* Expr in which substitution occurs */ ){ if( pExpr==0 ) return 0; + if( ExprHasProperty(pExpr, EP_FromJoin) && pExpr->iRightJoinTable==pSubst->iTable ){ + pExpr->iRightJoinTable = pSubst->iNewTable; + } if( pExpr->op==TK_COLUMN && pExpr->iTable==pSubst->iTable ){ if( pExpr->iColumn<0 ){ pExpr->op = TK_NULL; }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; pNew->flags |= EP_FromJoin; } @@ -3282,12 +3295,12 @@ ** (2) The subquery is not an aggregate or (2a) the outer query is not a join ** and (2b) the outer query does not use subqueries other than the one ** 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 -** (Originally ticket #306. Strengthened by ticket #3300) +** (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. ** ** (**) At one point restrictions (4) and (5) defined a subset of DISTINCT ** sub-queries that were excluded from this optimization. Restriction @@ -3295,11 +3308,11 @@ ** ** (6) The subquery does not use aggregates or the outer query is not ** 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. ** ** (8) The subquery does not use LIMIT or the outer query is not a join. ** @@ -3401,10 +3414,12 @@ Select *pSub1; /* Pointer to the rightmost select in sub-query */ SrcList *pSrc; /* The FROM clause of the outer query */ SrcList *pSubSrc; /* The FROM clause of the subquery */ ExprList *pList; /* The result set of the outer query */ 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 i; /* Loop counter */ Expr *pWhere; /* The WHERE clause */ struct SrcList_item *pSubitem; /* The subquery */ sqlite3 *db = pParse->db; @@ -3427,11 +3442,11 @@ || (sqlite3ExprListFlags(p->pOrderBy) & EP_Subquery)!=0 ){ return 0; /* Restriction (2b) */ } } - + pSubSrc = pSub->pSrc; assert( pSubSrc ); /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants, ** not arbitrary expressions, we allowed some combining of LIMIT and OFFSET ** because they could be computed at compile-time. But when LIMIT and OFFSET @@ -3465,44 +3480,29 @@ } if( (p->selFlags & SF_Recursive) && pSub->pPrior ){ return 0; /* Restriction (23) */ } - /* OBSOLETE COMMENT 1: - ** Restriction 3: If the subquery is a join, make sure the subquery is - ** not used as the right operand of an outer join. Examples of why this - ** is not allowed: + /* + ** If the subquery is the right operand of a LEFT JOIN, then the + ** subquery may not be a join itself. Example of why this is not allowed: ** ** t1 LEFT OUTER JOIN (t2 JOIN t3) ** ** If we flatten the above, we would get ** ** (t1 LEFT OUTER JOIN t2) JOIN t3 ** ** which is not at all the same thing. ** - ** OBSOLETE COMMENT 2: - ** Restriction 12: If the subquery is the right operand of a left outer - ** join, make sure the subquery has no WHERE clause. - ** An examples of why this is not allowed: - ** - ** t1 LEFT OUTER JOIN (SELECT * FROM t2 WHERE t2.x>0) - ** - ** If we flatten the above, we would get - ** - ** (t1 LEFT OUTER JOIN t2) WHERE t2.x>0 - ** - ** But the t2.x>0 test will always fail on a NULL row of t2, which - ** effectively converts the OUTER JOIN into an INNER JOIN. - ** - ** THIS OVERRIDES OBSOLETE COMMENTS 1 AND 2 ABOVE: - ** Ticket #3300 shows that flattening the right term of a LEFT JOIN - ** is fraught with danger. Best to avoid the whole thing. If the - ** subquery is the right term of a LEFT JOIN, then do not flatten. + ** See also tickets #306, #350, and #3300. */ if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){ - return 0; + isLeftJoin = 1; + if( pSubSrc->nSrc>1 ){ + return 0; /* Restriction (3) */ + } } /* Restriction 17: If the sub-query is a compound SELECT, then it must ** use only the UNION ALL operator. And none of the simple select queries ** that make up the compound SELECT are allowed to be aggregate or distinct @@ -3707,10 +3707,11 @@ */ for(i=0; ia[i+iFrom].pUsing); assert( pSrc->a[i+iFrom].fg.isTabFunc==0 ); pSrc->a[i+iFrom] = pSubSrc->a[i]; + iNewParent = pSubSrc->a[i].iCursor; memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i])); } pSrc->a[iFrom].fg.jointype = jointype; /* Now begin substituting subquery result set expressions for @@ -3752,10 +3753,13 @@ assert( pSub->pPrior==0 ); pParent->pOrderBy = pOrderBy; pSub->pOrderBy = 0; } pWhere = sqlite3ExprDup(db, pSub->pWhere, 0); + if( isLeftJoin ){ + setJoinExpr(pWhere, iNewParent); + } if( subqueryIsAgg ){ assert( pParent->pHaving==0 ); pParent->pHaving = pParent->pWhere; pParent->pWhere = pWhere; pParent->pHaving = sqlite3ExprAnd(db, @@ -3768,10 +3772,12 @@ } if( db->mallocFailed==0 ){ SubstContext x; x.pParse = pParse; x.iTable = iParent; + x.iNewTable = iNewParent; + x.isLeftJoin = isLeftJoin; x.pEList = pSub->pEList; substSelect(&x, pParent, 0); } /* The flattened query is distinct if either the inner or the @@ -3876,10 +3882,12 @@ while( pSubq ){ SubstContext x; pNew = sqlite3ExprDup(pParse->db, pWhere, 0); 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); pSubq = pSubq->pPrior; } Index: src/treeview.c ================================================================== --- src/treeview.c +++ src/treeview.c @@ -467,10 +467,15 @@ } case TK_SELECT_COLUMN: { sqlite3TreeViewLine(pView, "SELECT-COLUMN %d", pExpr->iColumn); 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; } Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -2426,10 +2426,27 @@ if( (pIn1->flags & MEM_Null)==0 ){ goto jump_to_p2; } 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 ** ** Interpret the data that cursor P1 points to as a structure built using Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -4974,10 +4974,12 @@ assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0 || x>=0 || pWInfo->eOnePass ); }else if( pOp->opcode==OP_Rowid ){ pOp->p1 = pLevel->iIdxCur; pOp->opcode = OP_IdxRowid; + }else if( pOp->opcode==OP_IfNullRow ){ + pOp->p1 = pLevel->iIdxCur; } } } } Index: tool/addopcodes.tcl ================================================================== --- tool/addopcodes.tcl +++ tool/addopcodes.tcl @@ -37,10 +37,11 @@ UMINUS UPLUS REGISTER VECTOR SELECT_COLUMN + IF_NULL_ROW ASTERISK SPAN SPACE ILLEGAL }