Revamp the USING clause processing so that it works (mostly) with RIGHT

and FULL JOIN.

FossilOrigin-Name: 719c8d920b435b0f234d47467a2abe2941ab7d55ecca886e211cfce577ae648c

1 files changed, 91 insertions, 19 deletions

diff --git a/src/resolve.c b/src/resolve.c
index ecc07b296..204732850 100644
--- a/src/resolve.c
+++ b/src/resolve.c
@@ -208,6 +208,31 @@ Bitmask sqlite3ExprColUsed(Expr *pExpr){
 }
 
 /*
+** Create a new expression term for the column specified by pMatch and
+** iColumn.  Append this new expression term to the FULL JOIN Match set
+** in *ppList.  Create a new *ppList if this is the first term in the
+** set.
+*/
+static void extendFJMatch(
+  Parse *pParse,          /* Parsing context */
+  ExprList **ppList,      /* ExprList to extend */
+  SrcItem *pMatch,        /* Source table containing the column */
+  i16 iColumn             /* The column number */
+){
+  Expr *pNew = sqlite3ExprAlloc(pParse->db, TK_COLUMN, 0, 0);
+  if( pNew ){
+    Table *pTab;
+    pNew->iTable = pMatch->iCursor;
+    assert( ExprUseYTab(pNew) );
+    pTab = pNew->y.pTab = pMatch->pTab;
+    pNew->iColumn = iColumn==pTab->iPKey ? -1 : iColumn;
+    assert( (pMatch->fg.jointype & (JT_LEFT|JT_LTORJ))!=0 );
+    ExprSetProperty(pNew, EP_CanBeNull);
+    *ppList = sqlite3ExprListAppend(pParse, *ppList, pNew);
+  }
+}
+
+/*
 ** Given the name of a column of the form X.Y.Z or Y.Z or just Z, look up
 ** that name in the set of source tables in pSrcList and make the pExpr 
 ** expression node refer back to that source column.  The following changes
@@ -252,8 +277,9 @@ static int lookupName(
   NameContext *pTopNC = pNC;        /* First namecontext in the list */
   Schema *pSchema = 0;              /* Schema of the expression */
   int eNewExprOp = TK_COLUMN;       /* New value for pExpr->op on success */
-  Table *pTab = 0;                  /* Table hold the row */
+  Table *pTab = 0;                  /* Table holding the row */
   Column *pCol;                     /* A column of pTab */
+  ExprList *pFJMatch = 0;           /* Matches for FULL JOIN .. USING */
 
   assert( pNC );     /* the name context cannot be NULL. */
   assert( zCol );    /* The Z in X.Y.Z cannot be NULL */
@@ -310,6 +336,27 @@ static int lookupName(
           pEList = pItem->pSelect->pEList;
           for(j=0; j<pEList->nExpr; j++){
             if( sqlite3MatchEName(&pEList->a[j], zCol, zTab, zDb) ){
+              if( cnt>0 ){
+                if( pItem->fg.isUsing==0
+                 || !nameInUsingClause(pItem->u3.pUsing, zCol)
+                ){
+                  sqlite3ExprListDelete(db, pFJMatch);
+                  pFJMatch = 0;
+                }else
+                if( (pItem->fg.jointype & JT_RIGHT)==0 ){
+                  /* An INNER or LEFT JOIN.  Use the left-most table */
+                  continue;
+                }else
+                if( (pItem->fg.jointype & JT_LEFT)==0 ){
+                  /* A RIGHT JOIN.  Use the right-most table */
+                  cnt = 0;
+                  sqlite3ExprListDelete(db, pFJMatch);
+                  pFJMatch = 0;
+                }else{
+                  /* For a FULL JOIN, we must construct a coalesce() func */
+                  extendFJMatch(pParse, &pFJMatch, pMatch, j);
+                }
+              }
               cnt++;
               cntTab = 2;
               pMatch = pItem;
@@ -339,15 +386,25 @@ static int lookupName(
           if( pCol->hName==hCol
            && sqlite3StrICmp(pCol->zCnName, zCol)==0
           ){
-            /* If there has been exactly one prior match and this match
-            ** is for the right-hand table of a NATURAL JOIN or is in a 
-            ** USING clause, then skip this match.
-            */
-            if( cnt==1 ){
-              if( pItem->fg.isUsing
-               && nameInUsingClause(pItem->u3.pUsing, zCol)
+            if( cnt>0 ){
+              if( pItem->fg.isUsing==0
+               || !nameInUsingClause(pItem->u3.pUsing, zCol)
               ){
+                sqlite3ExprListDelete(db, pFJMatch);
+                pFJMatch = 0;
+              }else
+              if( (pItem->fg.jointype & JT_RIGHT)==0 ){
+                /* An INNER or LEFT JOIN.  Use the left-most table */
                 continue;
+              }else
+              if( (pItem->fg.jointype & JT_LEFT)==0 ){
+                /* A RIGHT JOIN.  Use the right-most table */
+                cnt = 0;
+                sqlite3ExprListDelete(db, pFJMatch);
+                pFJMatch = 0;
+              }else{
+                /* For a FULL JOIN, we must construct a coalesce() func */
+                extendFJMatch(pParse, &pFJMatch, pMatch, j);
               }
             }
             cnt++;
@@ -607,11 +664,27 @@ static int lookupName(
   }
 
   /*
-  ** cnt==0 means there was not match.  cnt>1 means there were two or
-  ** more matches.  Either way, we have an error.
+  ** cnt==0 means there was not match.
+  ** cnt>1 means there were two or more matches.
+  **
+  ** cnt==0 is always an error.  cnt>1 is often an error, but might
+  ** be multiple matches for a NATURAL LEFT JOIN or a LEFT JOIN USING.
   */
+  assert( pFJMatch==0 || cnt>0 );
   if( cnt!=1 ){
     const char *zErr;
+    if( pFJMatch ){
+      if( pFJMatch->nExpr==cnt-1 ){
+        extendFJMatch(pParse, &pFJMatch, pMatch, pExpr->iColumn);
+        pExpr->op = TK_FUNCTION;
+        pExpr->u.zToken = "coalesce";
+        pExpr->x.pList = pFJMatch;
+        goto lookupname_end;
+      }else{
+        sqlite3ExprListDelete(db, pFJMatch);
+        pFJMatch = 0;
+      }
+    }
     zErr = cnt==0 ? "no such column" : "ambiguous column name";
     if( zDb ){
       sqlite3ErrorMsg(pParse, "%s: %s.%s.%s", zErr, zDb, zTab, zCol);
@@ -624,6 +697,7 @@ static int lookupName(
     pParse->checkSchema = 1;
     pTopNC->nNcErr++;
   }
+  assert( pFJMatch==0 );
 
   /* If a column from a table in pSrcList is referenced, then record
   ** this fact in the pSrcList.a[].colUsed bitmask.  Column 0 causes
@@ -643,19 +717,17 @@ static int lookupName(
     pMatch->colUsed |= sqlite3ExprColUsed(pExpr);
   }
 
-  /* Clean up and return
-  */
-  if( !ExprHasProperty(pExpr,(EP_TokenOnly|EP_Leaf)) ){
-    sqlite3ExprDelete(db, pExpr->pLeft);
-    pExpr->pLeft = 0;
-    sqlite3ExprDelete(db, pExpr->pRight);
-    pExpr->pRight = 0;
-  }
   pExpr->op = eNewExprOp;
-  ExprSetProperty(pExpr, EP_Leaf);
 lookupname_end:
   if( cnt==1 ){
     assert( pNC!=0 );
+    if( !ExprHasProperty(pExpr,(EP_TokenOnly|EP_Leaf)) ){
+      sqlite3ExprDelete(db, pExpr->pLeft);
+      pExpr->pLeft = 0;
+      sqlite3ExprDelete(db, pExpr->pRight);
+      pExpr->pRight = 0;
+      ExprSetProperty(pExpr, EP_Leaf);
+    }
 #ifndef SQLITE_OMIT_AUTHORIZATION
     if( pParse->db->xAuth
      && (pExpr->op==TK_COLUMN || pExpr->op==TK_TRIGGER)