aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--manifest14
-rw-r--r--manifest.uuid2
-rw-r--r--src/resolve.c20
-rw-r--r--src/select.c159
4 files changed, 68 insertions, 127 deletions
diff --git a/manifest b/manifest
index 07f28c368..3be550845 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C New\stest\scases,\sone\sof\swhich\sis\sfailing,\sindicating\sa\sbug\sthat\sneeds\sfixing.
-D 2022-04-16T23:38:29.234
+C Further\simprovements\sto\sUSING()\sprocessing\sfor\sRIGHT\sand\sFULL\sJOINs.\s\sAll\ncurrently\sknown\sissues\sare\snow\sresolved.\s\sPerformace\sis\simproved.
+D 2022-04-17T18:46:17.120
F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -550,9 +550,9 @@ F src/pragma.h e690a356c18e98414d2e870ea791c1be1545a714ba623719deb63f7f226d8bb7
F src/prepare.c fd940149c691684e7c1073c3787a7170e44852b02d1275d2e30a5b58e89cfcaf
F src/printf.c 05d8dfd2018bc4fc3ddb8b37eb97ccef7abf985643fa1caebdcf2916ca90fa32
F src/random.c 097dc8b31b8fba5a9aca1697aeb9fd82078ec91be734c16bffda620ced7ab83c
-F src/resolve.c 58b5c54b7e5cd7101b57901f9039dee86224b6a93699a5e8639f402aff43e7cc
+F src/resolve.c d38fc50ba853bead084c571dae2218620655dfb952eb535b8f29c9757e92fa87
F src/rowset.c ba9515a922af32abe1f7d39406b9d35730ed65efab9443dc5702693b60854c92
-F src/select.c 0af8c196a4b3fdf5fa4f6aae4045758467d143efd32711bfc4bf711e8e8a04cc
+F src/select.c 1d0c3aece7221fb1e5b10f636eed074ee1318d7bb93e1029004d84447f825562
F src/shell.c.in eb7f10d5e2c47bd014d92ec5db1def21fcc1ed56ffaaa4ee715b6c37c370b47f
F src/sqlite.h.in 2a35f62185eb5e7ecc64a2f68442b538ce9be74f80f28a00abc24837edcf1c17
F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8
@@ -1948,8 +1948,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 9ffc2b231956cde1bc90519aa174b0e2dc30ef671ed745f4f3ffa9fbb7ffab4b
-R 6e1aaa1b139d709bc01047520c7bb6df
+P bd5fd68435ff068c18d7d46b33cf7591263a03c32a917a7df7c087b08c573cc8
+R 59e0d103b473769389ca9e8aa4f28bed
U drh
-Z d4fc26ab748be0192a3f4dfcc716caef
+Z de5ba2466670ff117c1fafe3df9d0ad6
# Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index 94e1a8b48..8f1460514 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-bd5fd68435ff068c18d7d46b33cf7591263a03c32a917a7df7c087b08c573cc8 \ No newline at end of file
+9fd3f22e2228dfba127f6ffe549109f3a4e910fa124adcc9c5483931bd6d5cd7 \ No newline at end of file
diff --git a/src/resolve.c b/src/resolve.c
index 09b16f1ad..0f0b85306 100644
--- a/src/resolve.c
+++ b/src/resolve.c
@@ -115,22 +115,6 @@ static void resolveAlias(
}
}
-
-/*
-** Return TRUE if the name zCol occurs anywhere in the USING clause.
-**
-** Return FALSE if the USING clause is NULL or if it does not contain
-** zCol.
-*/
-static int nameInUsingClause(IdList *pUsing, const char *zCol){
- int k;
- assert( pUsing!=0 );
- for(k=0; k<pUsing->nId; k++){
- if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ) return 1;
- }
- return 0;
-}
-
/*
** Subqueries stores the original database, table and column names for their
** result sets in ExprList.a[].zSpan, in the form "DATABASE.TABLE.COLUMN".
@@ -338,7 +322,7 @@ static int lookupName(
if( sqlite3MatchEName(&pEList->a[j], zCol, zTab, zDb) ){
if( cnt>0 ){
if( pItem->fg.isUsing==0
- || !nameInUsingClause(pItem->u3.pUsing, zCol)
+ || sqlite3IdListIndex(pItem->u3.pUsing, zCol)<0
){
sqlite3ExprListDelete(db, pFJMatch);
pFJMatch = 0;
@@ -392,7 +376,7 @@ static int lookupName(
){
if( cnt>0 ){
if( pItem->fg.isUsing==0
- || !nameInUsingClause(pItem->u3.pUsing, zCol)
+ || sqlite3IdListIndex(pItem->u3.pUsing, zCol)<0
){
sqlite3ExprListDelete(db, pFJMatch);
pFJMatch = 0;
diff --git a/src/select.c b/src/select.c
index f6938ccab..db4117209 100644
--- a/src/select.c
+++ b/src/select.c
@@ -320,11 +320,9 @@ int sqlite3ColumnIndex(Table *pTab, const char *zCol){
}
/*
-** Search the first N tables in pSrc, looking for a
-** table that has a column named zCol.
-**
-** Search left-to-right if bRightmost is false. Search right-to-left
-** if bRightmost is true.
+** Search the tables iStart..iEnd (inclusive) in pSrc, looking for a
+** table that has a column named zCol. The search is left-to-right.
+** The first match found is returned.
**
** When found, set *piTab and *piCol to the table index and column index
** of the matching column and return TRUE.
@@ -333,21 +331,21 @@ int sqlite3ColumnIndex(Table *pTab, const char *zCol){
*/
static int tableAndColumnIndex(
SrcList *pSrc, /* Array of tables to search */
- int N, /* Number of tables in pSrc->a[] to search */
+ int iStart, /* First member of pSrc->a[] to check */
+ int iEnd, /* Last member of pSrc->a[] to check */
const char *zCol, /* Name of the column we are looking for */
int *piTab, /* Write index of pSrc->a[] here */
int *piCol, /* Write index of pSrc->a[*piTab].pTab->aCol[] here */
- int bIgnoreHidden, /* True to ignore hidden columns */
- int bRightmost /* Return the right-most match */
+ int bIgnoreHidden /* Ignore hidden columns */
){
int i; /* For looping over tables in pSrc */
int iCol; /* Index of column matching zCol */
- int rc = 0;
- assert( N<=pSrc->nSrc );
-
+ assert( iEnd<pSrc->nSrc );
+ assert( iStart>=0 );
assert( (piTab==0)==(piCol==0) ); /* Both or neither are NULL */
- for(i=0; i<N; i++){
+
+ for(i=iStart; i<=iEnd; i++){
iCol = sqlite3ColumnIndex(pSrc->a[i].pTab, zCol);
if( iCol>=0
&& (bIgnoreHidden==0 || IsHiddenColumn(&pSrc->a[i].pTab->aCol[iCol])==0)
@@ -356,58 +354,10 @@ static int tableAndColumnIndex(
*piTab = i;
*piCol = iCol;
}
- rc = 1;
- if( !bRightmost ) break;
+ return 1;
}
}
- return rc;
-}
-
-/*
-** This function is used to add terms implied by JOIN syntax to the
-** WHERE clause expression of a SELECT statement. The new term, which
-** is ANDed with the existing WHERE clause, is of the form:
-**
-** (tab1.col1 = tab2.col2)
-**
-** where tab1 is the iSrc'th table in SrcList pSrc and tab2 is the
-** (iSrc+1)'th. Column col1 is column iColLeft of tab1, and col2 is
-** column iColRight of tab2.
-*/
-static void addWhereTerm(
- Parse *pParse, /* Parsing context */
- SrcList *pSrc, /* List of tables in FROM clause */
- int iLeft, /* Index of first table to join in pSrc */
- int iColLeft, /* Index of column in first table */
- int iRight, /* Index of second table in pSrc */
- int iColRight, /* Index of column in second table */
- u32 joinType, /* EP_FromJoin or EP_InnerJoin */
- Expr **ppWhere /* IN/OUT: The WHERE clause to add to */
-){
- sqlite3 *db = pParse->db;
- Expr *pE1;
- Expr *pE2;
- Expr *pEq;
-
- assert( iLeft<iRight );
- assert( pSrc->nSrc>iRight );
- assert( pSrc->a[iLeft].pTab );
- assert( pSrc->a[iRight].pTab );
-
- pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iColLeft);
- pE2 = sqlite3CreateColumnExpr(db, pSrc, iRight, iColRight);
-
- pEq = sqlite3PExpr(pParse, TK_EQ, pE1, pE2);
- assert( pE2!=0 || pEq==0 ); /* Due to db->mallocFailed test
- ** in sqlite3DbMallocRawNN() called from
- ** sqlite3PExpr(). */
- if( pEq ){
- ExprSetProperty(pEq, joinType);
- assert( !ExprHasProperty(pEq, EP_TokenOnly|EP_Reduced) );
- ExprSetVVAProperty(pEq, EP_NoReduce);
- pEq->w.iJoin = pE2->iTable;
- }
- *ppWhere = sqlite3ExprAnd(pParse, *ppWhere, pEq);
+ return 0;
}
/*
@@ -532,7 +482,7 @@ static int sqlite3ProcessJoin(Parse *pParse, Select *p){
if( IsHiddenColumn(&pRightTab->aCol[j]) ) continue;
zName = pRightTab->aCol[j].zCnName;
- if( tableAndColumnIndex(pSrc, i+1, zName, 0, 0, 1, 0) ){
+ if( tableAndColumnIndex(pSrc, 0, i, zName, 0, 0, 1) ){
Token x;
x.z = zName;
x.n = sqlite3Strlen30(zName);
@@ -555,64 +505,71 @@ static int sqlite3ProcessJoin(Parse *pParse, Select *p){
*/
if( pRight->fg.isUsing ){
IdList *pList = pRight->u3.pUsing;
- int bRight = (pLeft->fg.jointype & JT_RIGHT)!=0;
+ sqlite3 *db = pParse->db;
assert( pList!=0 );
for(j=0; j<pList->nId; j++){
char *zName; /* Name of the term in the USING clause */
int iLeft; /* Table on the left with matching column name */
int iLeftCol; /* Column number of matching column on the left */
int iRightCol; /* Column number of matching column on the right */
- int iNxLeft, iNxLeftCol;
+ Expr *pE1; /* Reference to the column on the LEFT of the join */
+ Expr *pE2; /* Reference to the column on the RIGHT of the join */
+ Expr *pEq; /* Equality constraint. pE1 == pE2 */
zName = pList->a[j].zName;
iRightCol = sqlite3ColumnIndex(pRightTab, zName);
if( iRightCol<0
- || !tableAndColumnIndex(pSrc, i+1, zName, &iLeft, &iLeftCol,
- pRight->fg.isSynthUsing, bRight)
+ || tableAndColumnIndex(pSrc, 0, i, zName, &iLeft, &iLeftCol,
+ pRight->fg.isSynthUsing)==0
){
sqlite3ErrorMsg(pParse, "cannot join using column %s - column "
"not present in both tables", zName);
return 1;
}
- if( (pLeft->fg.jointype & (JT_RIGHT|JT_LEFT))!=(JT_LEFT|JT_RIGHT)
- || 0==tableAndColumnIndex(pSrc, iLeft, zName, &iNxLeft, &iNxLeftCol,
- pRight->fg.isSynthUsing, 1)
- ){
- addWhereTerm(pParse, pSrc, iLeft, iLeftCol, i+1, iRightCol,
- joinType, &p->pWhere);
- }else{
- /* Because the left-hand side of this join is another RIGHT or FULL
- ** JOIN with two or more tables hold zName, we need to construct
- ** a coalesce() function for left side of the ON constraint.
+ pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iLeftCol);
+ if( (pSrc->a[0].fg.jointype & JT_LTORJ)!=0 ){
+ /* This branch runs if the query contains one or more RIGHT or FULL
+ ** JOINs. If only a single table on the left side of this join
+ ** contains the zName column, then this routine is branch is
+ ** a no-op. But if there are two or more tables on the left side
+ ** of the join, construct a coalesce() function that gathers all
+ ** such tables. Raise an error if more than one of those references
+ ** to zName is not also within a prior USING clause.
+ **
+ ** We really ought to raise an error if there are two or more
+ ** non-USING references to zName on the left of an INNER or LEFT
+ ** JOIN. But older versions of SQLite do not do that, so we avoid
+ ** adding a new error so as to not break legacy applications.
*/
- ExprList *pList;
- Expr *pE;
- Expr *pE2;
- Expr *pEq;
- sqlite3 *db = pParse->db;
+ ExprList *pFuncArgs = 0; /* Arguments to the coalesce() */
static const Token tkCoalesce = { "coalesce", 8 };
- pE = sqlite3CreateColumnExpr(db, pSrc, iLeft, iLeftCol);
- pList = sqlite3ExprListAppend(pParse, 0, pE);
- pE = sqlite3CreateColumnExpr(db, pSrc, iNxLeft, iNxLeftCol);
- pList = sqlite3ExprListAppend(pParse, pList, pE);
- while( tableAndColumnIndex(pSrc, iNxLeft, zName,
- &iNxLeft, &iNxLeftCol,
- pRight->fg.isSynthUsing, 1)!=0 ){
- pE = sqlite3CreateColumnExpr(db, pSrc, iNxLeft, iNxLeftCol);
- pList = sqlite3ExprListAppend(pParse, pList, pE);
+ while( tableAndColumnIndex(pSrc, iLeft+1, i, zName, &iLeft, &iLeftCol,
+ pRight->fg.isSynthUsing)!=0 ){
+ if( pSrc->a[iLeft].fg.isUsing==0
+ || sqlite3IdListIndex(pSrc->a[iLeft].u3.pUsing, zName)<0
+ ){
+ sqlite3ErrorMsg(pParse, "ambiguous reference to %s in USING()",
+ zName);
+ break;
+ }
+ pFuncArgs = sqlite3ExprListAppend(pParse, pFuncArgs, pE1);
+ pE1 = sqlite3CreateColumnExpr(db, pSrc, iLeft, iLeftCol);
}
- pE = sqlite3ExprFunction(pParse, pList, &tkCoalesce, 0);
- pE2 = sqlite3CreateColumnExpr(db, pSrc, i+1, iRightCol);
- pEq = sqlite3PExpr(pParse, TK_EQ, pE, pE2);
- assert( pE2!=0 || pEq==0 );
- if( pEq ){
- ExprSetProperty(pEq, joinType);
- assert( !ExprHasProperty(pEq, EP_TokenOnly|EP_Reduced) );
- ExprSetVVAProperty(pEq, EP_NoReduce);
- pEq->w.iJoin = pE2->iTable;
+ if( pFuncArgs ){
+ pFuncArgs = sqlite3ExprListAppend(pParse, pFuncArgs, pE1);
+ pE1 = sqlite3ExprFunction(pParse, pFuncArgs, &tkCoalesce, 0);
}
- p->pWhere = sqlite3ExprAnd(pParse, p->pWhere, pEq);
}
+ pE2 = sqlite3CreateColumnExpr(db, pSrc, i+1, iRightCol);
+ pEq = sqlite3PExpr(pParse, TK_EQ, pE1, pE2);
+ assert( pE2!=0 || pEq==0 );
+ if( pEq ){
+ ExprSetProperty(pEq, joinType);
+ assert( !ExprHasProperty(pEq, EP_TokenOnly|EP_Reduced) );
+ ExprSetVVAProperty(pEq, EP_NoReduce);
+ pEq->w.iJoin = pE2->iTable;
+ }
+ p->pWhere = sqlite3ExprAnd(pParse, p->pWhere, pEq);
}
}
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-18 03:49:30 +0000