aboutsummaryrefslogtreecommitdiff
path: root/src/select.c
diff options
context:
space:
mode:
Diffstat (limited to 'src/select.c')
-rw-r--r--src/select.c129
1 files changed, 83 insertions, 46 deletions
diff --git a/src/select.c b/src/select.c
index 28934ffd9..2e5c1ac97 100644
--- a/src/select.c
+++ b/src/select.c
@@ -298,13 +298,9 @@ int sqlite3JoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){
const char *zSp2 = " ";
if( pB==0 ){ zSp1++; }
if( pC==0 ){ zSp2++; }
- sqlite3ErrorMsg(pParse, "unknown or unsupported join type: "
+ sqlite3ErrorMsg(pParse, "unknown join type: "
"%T%s%T%s%T", pA, zSp1, pB, zSp2, pC);
jointype = JT_INNER;
- }else if( (jointype & JT_RIGHT)!=0 ){
- sqlite3ErrorMsg(pParse,
- "RIGHT and FULL OUTER JOINs are not currently supported");
- jointype = JT_INNER;
}
return jointype;
}
@@ -377,7 +373,7 @@ static void addWhereTerm(
int iColLeft, /* Index of column in first table */
int iRight, /* Index of second table in pSrc */
int iColRight, /* Index of column in second table */
- int isOuterJoin, /* True if this is an OUTER join */
+ u32 joinType, /* EP_FromJoin or EP_InnerJoin */
Expr **ppWhere /* IN/OUT: The WHERE clause to add to */
){
sqlite3 *db = pParse->db;
@@ -397,8 +393,8 @@ static void addWhereTerm(
assert( pE2!=0 || pEq==0 ); /* Due to db->mallocFailed test
** in sqlite3DbMallocRawNN() called from
** sqlite3PExpr(). */
- if( pEq && isOuterJoin ){
- ExprSetProperty(pEq, EP_FromJoin);
+ if( pEq ){
+ ExprSetProperty(pEq, joinType);
assert( !ExprHasProperty(pEq, EP_TokenOnly|EP_Reduced) );
ExprSetVVAProperty(pEq, EP_NoReduce);
pEq->w.iJoin = pE2->iTable;
@@ -412,7 +408,7 @@ static void addWhereTerm(
** expression.
**
** The EP_FromJoin property is used on terms of an expression to tell
-** the LEFT OUTER JOIN processing logic that this term is part of the
+** the OUTER JOIN processing logic that this term is part of the
** join restriction specified in the ON or USING clause and not a part
** of the more general WHERE clause. These terms are moved over to the
** WHERE clause during join processing but we need to remember that they
@@ -432,9 +428,10 @@ static void addWhereTerm(
** after the t1 loop and rows with t1.x!=5 will never appear in
** the output, which is incorrect.
*/
-void sqlite3SetJoinExpr(Expr *p, int iTable){
+void sqlite3SetJoinExpr(Expr *p, int iTable, u32 joinFlag){
+ assert( joinFlag==EP_FromJoin || joinFlag==EP_InnerJoin );
while( p ){
- ExprSetProperty(p, EP_FromJoin);
+ ExprSetProperty(p, joinFlag);
assert( !ExprHasProperty(p, EP_TokenOnly|EP_Reduced) );
ExprSetVVAProperty(p, EP_NoReduce);
p->w.iJoin = iTable;
@@ -443,11 +440,11 @@ void sqlite3SetJoinExpr(Expr *p, int iTable){
if( p->x.pList ){
int i;
for(i=0; i<p->x.pList->nExpr; i++){
- sqlite3SetJoinExpr(p->x.pList->a[i].pExpr, iTable);
+ sqlite3SetJoinExpr(p->x.pList->a[i].pExpr, iTable, joinFlag);
}
}
}
- sqlite3SetJoinExpr(p->pLeft, iTable);
+ sqlite3SetJoinExpr(p->pLeft, iTable, joinFlag);
p = p->pRight;
}
}
@@ -463,6 +460,7 @@ static void unsetJoinExpr(Expr *p, int iTable){
if( ExprHasProperty(p, EP_FromJoin)
&& (iTable<0 || p->w.iJoin==iTable) ){
ExprClearProperty(p, EP_FromJoin);
+ ExprSetProperty(p, EP_InnerJoin);
}
if( p->op==TK_COLUMN && p->iTable==iTable ){
ExprClearProperty(p, EP_CanBeNull);
@@ -506,10 +504,10 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){
pRight = &pLeft[1];
for(i=0; i<pSrc->nSrc-1; i++, pRight++, pLeft++){
Table *pRightTab = pRight->pTab;
- int isOuter;
+ u32 joinType;
if( NEVER(pLeft->pTab==0 || pRightTab==0) ) continue;
- isOuter = (pRight->fg.jointype & JT_OUTER)!=0;
+ joinType = (pRight->fg.jointype & JT_OUTER)!=0 ? EP_FromJoin : EP_InnerJoin;
/* When the NATURAL keyword is present, add WHERE clause terms for
** every column that the two tables have in common.
@@ -529,7 +527,7 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){
zName = pRightTab->aCol[j].zCnName;
if( tableAndColumnIndex(pSrc, i+1, zName, &iLeft, &iLeftCol, 1) ){
addWhereTerm(pParse, pSrc, iLeft, iLeftCol, i+1, j,
- isOuter, &p->pWhere);
+ joinType, &p->pWhere);
}
}
}
@@ -560,7 +558,7 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){
return 1;
}
addWhereTerm(pParse, pSrc, iLeft, iLeftCol, i+1, iRightCol,
- isOuter, &p->pWhere);
+ joinType, &p->pWhere);
}
}
@@ -568,7 +566,7 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){
** an AND operator.
*/
else if( pRight->u3.pOn ){
- if( isOuter ) sqlite3SetJoinExpr(pRight->u3.pOn, pRight->iCursor);
+ sqlite3SetJoinExpr(pRight->u3.pOn, pRight->iCursor, joinType);
p->pWhere = sqlite3ExprAnd(pParse, p->pWhere, pRight->u3.pOn);
pRight->u3.pOn = 0;
}
@@ -3656,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;
@@ -3711,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;
@@ -3725,11 +3751,12 @@ static Expr *substExpr(
sqlite3ExprDelete(db, pNew);
return pExpr;
}
- if( pSubst->isLeftJoin ){
+ if( pSubst->isOuterJoin ){
ExprSetProperty(pNew, EP_CanBeNull);
}
- if( ExprHasProperty(pExpr,EP_FromJoin) ){
- sqlite3SetJoinExpr(pNew, pExpr->w.iJoin);
+ if( ExprHasProperty(pExpr,EP_FromJoin|EP_InnerJoin) ){
+ sqlite3SetJoinExpr(pNew, pExpr->w.iJoin,
+ pExpr->flags & (EP_FromJoin|EP_InnerJoin));
}
sqlite3ExprDelete(db, pExpr);
pExpr = pNew;
@@ -3979,6 +4006,7 @@ static void renumberCursors(
** table and
** (3c) the outer query may not be an aggregate.
** (3d) the outer query may not be DISTINCT.
+** See also (26) for restrictions on RIGHT JOIN.
**
** (4) The subquery can not be DISTINCT.
**
@@ -4077,6 +4105,9 @@ static void renumberCursors(
** function in the select list or ORDER BY clause, flattening
** is not attempted.
**
+** (26) The subquery may not be the right operand of a RIGHT JOIN.
+** See also (3) for restrictions on LEFT JOIN.
+**
**
** In this routine, the "p" parameter is a pointer to the outer query.
** The subquery is p->pSrc->a[iFrom]. isAgg is true if the outer query
@@ -4102,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 */
@@ -4175,23 +4206,24 @@ static int flattenSubquery(
**
** See also tickets #306, #350, and #3300.
*/
- if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){
- isLeftJoin = 1;
- if( pSubSrc->nSrc>1 /* (3a) */
- || isAgg /* (3b) */
- || IsVirtual(pSubSrc->a[0].pTab) /* (3c) */
- || (p->selFlags & SF_Distinct)!=0 /* (3d) */
+ if( (pSubitem->fg.jointype & (JT_OUTER|JT_LTORJ))!=0 ){
+ if( pSubSrc->nSrc>1 /* (3a) */
+ || isAgg /* (3b) */
+ || IsVirtual(pSubSrc->a[0].pTab) /* (3c) */
+ || (p->selFlags & SF_Distinct)!=0 /* (3d) */
+ || (pSubitem->fg.jointype & JT_RIGHT)!=0 /* (26) */
){
return 0;
}
+ 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
@@ -4204,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){
@@ -4372,6 +4404,7 @@ static int flattenSubquery(
for(pParent=p; pParent; pParent=pParent->pPrior, pSub=pSub->pPrior){
int nSubSrc;
u8 jointype = 0;
+ u8 ltorj = pSrc->a[iFrom].fg.jointype & JT_LTORJ;
assert( pSub!=0 );
pSubSrc = pSub->pSrc; /* FROM clause of subquery */
nSubSrc = pSubSrc->nSrc; /* Number of terms in subquery FROM clause */
@@ -4410,10 +4443,12 @@ static int flattenSubquery(
if( pItem->fg.isUsing ) sqlite3IdListDelete(db, pItem->u3.pUsing);
assert( pItem->fg.isTabFunc==0 );
*pItem = pSubSrc->a[i];
+ pItem->fg.jointype |= ltorj;
iNewParent = pSubSrc->a[i].iCursor;
memset(&pSubSrc->a[i], 0, sizeof(pSubSrc->a[i]));
}
- pSrc->a[iFrom].fg.jointype = jointype;
+ pSrc->a[iFrom].fg.jointype &= JT_LTORJ;
+ pSrc->a[iFrom].fg.jointype |= jointype | ltorj;
/* Now begin substituting subquery result set expressions for
** references to the iParent in the outer query.
@@ -4448,8 +4483,8 @@ static int flattenSubquery(
}
pWhere = pSub->pWhere;
pSub->pWhere = 0;
- if( isLeftJoin>0 ){
- sqlite3SetJoinExpr(pWhere, iNewParent);
+ if( isOuterJoin>0 ){
+ sqlite3SetJoinExpr(pWhere, iNewParent, EP_FromJoin);
}
if( pWhere ){
if( pParent->pWhere ){
@@ -4463,7 +4498,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);
}
@@ -4924,7 +4959,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
@@ -6503,7 +6538,7 @@ int sqlite3Select(
/* Convert LEFT JOIN into JOIN if there are terms of the right table
** of the LEFT JOIN used in the WHERE clause.
*/
- if( (pItem->fg.jointype & JT_LEFT)!=0
+ if( (pItem->fg.jointype & (JT_LEFT|JT_RIGHT))==JT_LEFT
&& sqlite3ExprImpliesNonNullRow(p->pWhere, pItem->iCursor)
&& OptimizationEnabled(db, SQLITE_SimplifyJoin)
){
@@ -6589,7 +6624,7 @@ int sqlite3Select(
&& i==0
&& (p->selFlags & SF_ComplexResult)!=0
&& (pTabList->nSrc==1
- || (pTabList->a[1].fg.jointype&(JT_LEFT|JT_CROSS))!=0)
+ || (pTabList->a[1].fg.jointype&(JT_OUTER|JT_CROSS))!=0)
){
continue;
}
@@ -6711,6 +6746,7 @@ int sqlite3Select(
if( OptimizationEnabled(db, SQLITE_PushDown)
&& (pItem->fg.isCte==0
|| (pItem->u2.pCteUse->eM10d!=M10d_Yes && pItem->u2.pCteUse->nUse<2))
+ && (pItem->fg.jointype & JT_RIGHT)==0
&& pushDownWhereTerms(pParse, pSub, p->pWhere, pItem->iCursor,
(pItem->fg.jointype & JT_OUTER)!=0)
){
@@ -6731,18 +6767,19 @@ int sqlite3Select(
/* Generate code to implement the subquery
**
- ** The subquery is implemented as a co-routine if:
+ ** The subquery is implemented as a co-routine all of the following are
+ ** true:
+ **
** (1) the subquery is guaranteed to be the outer loop (so that
** it does not need to be computed more than once), and
** (2) the subquery is not a CTE that should be materialized
- **
- ** TODO: Are there other reasons beside (1) and (2) to use a co-routine
- ** implementation?
+ ** (3) the subquery is not part of a left operand for a RIGHT JOIN
*/
if( i==0
&& (pTabList->nSrc==1
- || (pTabList->a[1].fg.jointype&(JT_LEFT|JT_CROSS))!=0) /* (1) */
- && (pItem->fg.isCte==0 || pItem->u2.pCteUse->eM10d!=M10d_Yes) /* (2) */
+ || (pTabList->a[1].fg.jointype&(JT_OUTER|JT_CROSS))!=0) /* (1) */
+ && (pItem->fg.isCte==0 || pItem->u2.pCteUse->eM10d!=M10d_Yes) /* (2) */
+ && (pTabList->a[0].fg.jointype & JT_LTORJ)==0 /* (3) */
){
/* Implement a co-routine that will return a single row of the result
** set on each invocation.
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-18 10:43:33 +0000