Seems like the patch didn't get through, let's try again.
Regards
Fabrizio
-----Ursprüngliche Nachricht-----
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
Im Auftrag von Fabrizio Steiner
Gesendet: Freitag, 9. Dezember 2011 14:12
An: 'sqlite-users@sqlite.org'
Betreff: [sqlite] Unable to retrieve columns with table accessor on nested
joins.
Hello
I'm currently facing a problem with nested right hand joins. I've also reported
this to the mailing list over one month ago, but haven't received any reply. In
the meantime I've investigated the problem in the SQLite source and sorted some
things out.
Let's first start with an example which reproduces the problem:
CREATE TABLE t1(t1_id INTEGER PRIMARY KEY, t1_title TEXT);
CREATE TABLE t2(t2_id INTEGER PRIMARY KEY, t2_title TEXT);
CREATE TABLE t3(t3_id INTEGER PRIMARY KEY, t3_title TEXT);
INSERT INTO t1 (t1_id, t1_title) VALUES (888, 'data1');
INSERT INTO t2 (t2_id, t2_title) VALUES (999, 'data2');
INSERT INTO t3 (t3_id, t3_title) VALUES (999, 'data3');
Exeuting the following query works as expected and results : data1 | null | null
SELECT t1_title, t2_title, t3_title
FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2_id = t3_id) ON t1_id = t2_id;
If you now use the tablename t2 or t3 to access the columns like in the
following query :
SELECT t1_title, t2.t2_title, t3.t3_title
FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2_id = t3_id) ON t1_id = t2.t2_id;
You will receive SQL Error: no such column: t2.t2_title
I've also investigated the mailing list archive and I've seen one or two
messages regarding this problem. There the advice was given to rewrite the join
so you dont have a nested right handed join. I know this could be the solution
in some cases but it's not always possible to do that. And it's not always
possible to do the join without a table accessor on the columns, especially if
you always use the same column name for the primary key on all tables.
As I've stated, I've investigated the SQLite source and found the problem why
this doesn 't work. Right handed nested joins are currently implemented by
subqueries, because the joining is implemented as a list, where every table is
joined with the next one. The "lookupName" function which tries to search the
column in the used tables searches only through the source list. The subquery,
which represents the nested join, has an internal table name and there will
never be tried to take a look on the tables used in the subquery to resolve the
column. This is perfectly fine if it's a subquery but if the subquery
represents a nested join it has to be possible to access the tables used in the
subquery. At least it's possible with all the database systems I'm working with
in daily business.
Attached you will find a tcl test which shows the problem as well. In addition
you will find a patch which I've implemented to solve the problem. I know the
patch is not a perfect solution, but for the patch my main goal was to fix the
problem with at least changes to the original SQLite source as possible. The
patch currently breaks two of the authorizer tests, because for the patch to
work the resolving order of subqueries is changed in "resolveSelectStep" .
First subqueries in the FROM will be resolved and afterwards the result set.
The patch basically marks nested join subqueries during parsing and during
lookup it also searches in these subquries for the table.column. After a match,
this column will be remapped to correct column on the subqury result set. It
also works for TABLENAME.rowid as long as the rowid is designed in the table as
INTEGER PRIMARY KEY.
One problem still exists, it's not possible to execute a query with a selection
of all columns of a table, where table is defined in a nested join subquery,
e.g.
SELECT t2.*
FROM t1 LEFT JOIN ( t2 INNER JOIN t3 ON t2.t2_id = t3.t3_id) ON t1_id =
t2.t2_id;
I would really appreciate it if someone of the developers would reply to this
mail what they're opinion is regarding this problem, because I really think
this is a bug.
Kind Regards
Fabrizio
Index: src/parse.y
===================================================================
--- src/parse.y
+++ src/parse.y
@@ -505,10 +505,11 @@
A = F;
}else{
Select *pSubquery;
sqlite3SrcListShiftJoinType(F);
pSubquery = sqlite3SelectNew(pParse,0,F,0,0,0,0,0,0,0);
+ pSubquery->selFlags |= SF_NestedJoin;
A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,&Z,pSubquery,N,U);
}
}
// A seltablist_paren nonterminal represents anything in a FROM that
Index: src/resolve.c
===================================================================
--- src/resolve.c
+++ src/resolve.c
@@ -113,10 +113,168 @@
}
}
return 0;
}
+/*
+** 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
+** are made to pExpr:
+**
+** pExpr->iTable Set to the cursor number for the table obtained
+** from pSrcList.
+** pExpr->pTab Points to the Table structure of X.Y (even if
+** X and/or Y are implied.)
+** pExpr->iColumn Set to the column number within the table.
+**
+** The zDb variable is the name of the database (the "X"). This value may be
+** NULL meaning that name is of the form Y.Z or Z. Any available database
+** can be used. The zTable variable is the name of the table (the "Y"). This
+** value can be NULL if zDb is also NULL. If zTable is NULL it
+** means that the form of the name is Z and that columns from any table
+** can be used.
+**
+** The function returns the following information.
+** - Number of machting column names in the pSrcList (pCntOut).
+** - Number of matching table names in pSrcList (pCntTabOut).
+** - Schema of the matching expression (ppSchemaOut).
+** - The matching pSrcList item (ppMatchOut).
+*/
+void lookupNameInSrcList(
+ Parse *pParse, /* The parsing context */
+ sqlite3 *db, /* The db connection */
+ const char *zDb, /* Name of the database containing table, or NULL */
+ const char *zTab, /* Name of table containing column, or NULL */
+ const char *zCol, /* Name of the column. */
+ SrcList *pSrcList, /* Source list to search for the column */
+ Expr *pExpr, /* Make this EXPR node point to the selected column */
+ int *pCntOut, /* Returns Number of matching column names */
+ int *pCntTabOut, /* Returns Number of matching table names */
+ struct Schema **ppSchemaOut, /* Returns Schema of the expression */
+ struct SrcList_item **ppMatchOut /* Returns the matching pSrcList item */
+){
+ int i, j; /* Loop counters */
+ int cnt = 0; /* Number of matching column names */
+ int cntTab = 0; /* Number of matching table names */
+ struct SrcList_item *pItem; /* Use for looping over pSrcList items */
+ struct SrcList_item *pMatch = 0; /* The matching pSrcList item */
+ Schema *pSchema = 0; /* Schema of the expression */
+
+ for(i=0, pItem=pSrcList->a; i<pSrcList->nSrc; i++, pItem++){
+ Table *pTab;
+ int iDb;
+ Column *pCol;
+ struct Select *pSelect = pItem->pSelect;
+ pTab = pItem->pTab;
+ assert( pTab!=0 && pTab->zName!=0 );
+
+ if (zTab && pSelect && (pSelect->selFlags & SF_NestedJoin) ) {
+ /* A table name has been given for the containing column and it's a
nested join. */
+ int innerCnt = 0;
+ int innerCntTab = 0;
+ Schema *pInnerSchema = 0;
+ struct SrcList_item *pInnerMatch = 0;
+ Expr pInnerExpr; /* Make this new expression point to the column if
found. */
+ pInnerExpr.iColumn = -1;
+ pInnerExpr.pTab = 0;
+
+ lookupNameInSrcList(pParse, db, zDb, zTab, zCol, pSelect->pSrc,
&pInnerExpr, &innerCnt, &innerCntTab, &pInnerSchema, &pInnerMatch);
+
+ if (innerCnt == 0 && innerCntTab == 1 && sqlite3IsRowid(zCol)){
+ /* Exactly one table match and it was the rowid. Map this to one
column match with
+ ** iColumn = -1 whitch represents the rowid.
+ */
+ innerCnt = 1;
+ pInnerExpr.iColumn = -1;
+ }
+
+ if (innerCnt == 1) {
+ /* Exactly one match has been found in the subquery, now search for the
+ ** corresponding column in the table definition of the subquery.
+ */
+ struct ExprList_item *pColItem;
+ Table *pInnerTab = pInnerExpr.pTab;
+ assert( pInnerTab );
+
+ assert( pTab->nCol == pSelect->pEList->nExpr );
+ for(j=0, pColItem=pSelect->pEList->a; j<pSelect->pEList->nExpr; j++,
pColItem++){
+ /* For every expression in the SELECT list, check if the expression
points to
+ ** the same column which has been found in the subquery.
+ */
+ Expr *pColExpr = pColItem->pExpr;
+ if (pColExpr->op == TK_COLUMN && pColExpr->pTab == pInnerTab &&
pColExpr->iColumn == pInnerExpr.iColumn) {
+ /* Point pExpr to the corresponding column on pTab which holds the
result
+ ** of the inner columm in the subquery.
+ */
+ cntTab++;
+ cnt++;
+ pExpr->iTable = pItem->iCursor;
+ pExpr->pTab = pTab;
+ pMatch = pItem;
+ pSchema = pTab->pSchema;
+ NEVER( pTab->iPKey != -1 );
+ pExpr->iColumn = (i16)j;
+ break;
+ }
+ }
+ continue;
+ } else {
+ cnt += innerCnt;
+ cntTab += innerCntTab;
+ }
+ }
+
+ iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
+ assert( pTab->nCol>0 );
+ if( zTab ){
+ if( pItem->zAlias ){
+ char *zTabName = pItem->zAlias;
+ if( sqlite3StrICmp(zTabName, zTab)!=0 ) continue;
+ }else{
+ char *zTabName = pTab->zName;
+ if( NEVER(zTabName==0) || sqlite3StrICmp(zTabName, zTab)!=0 ){
+ continue;
+ }
+ if( zDb!=0 && sqlite3StrICmp(db->aDb[iDb].zName, zDb)!=0 ){
+ continue;
+ }
+ }
+ }
+ if( 0==(cntTab++) ){
+ pExpr->iTable = pItem->iCursor;
+ pExpr->pTab = pTab;
+ pSchema = pTab->pSchema;
+ pMatch = pItem;
+ }
+ for(j=0, pCol=pTab->aCol; j<pTab->nCol; j++, pCol++){
+ if( sqlite3StrICmp(pCol->zName, 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->jointype & JT_NATURAL ) continue;
+ if( nameInUsingClause(pItem->pUsing, zCol) ) continue;
+ }
+ cnt++;
+ pExpr->iTable = pItem->iCursor;
+ pExpr->pTab = pTab;
+ pMatch = pItem;
+ pSchema = pTab->pSchema;
+ /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY */
+ pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j;
+ break;
+ }
+ }
+ }
+
+ *ppSchemaOut = pSchema;
+ *ppMatchOut = pMatch;
+ *pCntOut += cnt;
+ *pCntTabOut += cntTab;
+}
/*
** 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
@@ -149,15 +307,14 @@
const char *zTab, /* Name of table containing column, or NULL */
const char *zCol, /* Name of the column. */
NameContext *pNC, /* The name context used to resolve the name */
Expr *pExpr /* Make this EXPR node point to the selected column */
){
- int i, j; /* Loop counters */
+ int j; /* Loop counters */
int cnt = 0; /* Number of matching column names */
int cntTab = 0; /* Number of matching table names */
sqlite3 *db = pParse->db; /* The database connection */
- struct SrcList_item *pItem; /* Use for looping over pSrcList items */
struct SrcList_item *pMatch = 0; /* The matching pSrcList item */
NameContext *pTopNC = pNC; /* First namecontext in the list */
Schema *pSchema = 0; /* Schema of the expression */
int isTrigger = 0;
@@ -174,60 +331,11 @@
while( pNC && cnt==0 ){
ExprList *pEList;
SrcList *pSrcList = pNC->pSrcList;
if( pSrcList ){
- for(i=0, pItem=pSrcList->a; i<pSrcList->nSrc; i++, pItem++){
- Table *pTab;
- int iDb;
- Column *pCol;
-
- pTab = pItem->pTab;
- assert( pTab!=0 && pTab->zName!=0 );
- iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
- assert( pTab->nCol>0 );
- if( zTab ){
- if( pItem->zAlias ){
- char *zTabName = pItem->zAlias;
- if( sqlite3StrICmp(zTabName, zTab)!=0 ) continue;
- }else{
- char *zTabName = pTab->zName;
- if( NEVER(zTabName==0) || sqlite3StrICmp(zTabName, zTab)!=0 ){
- continue;
- }
- if( zDb!=0 && sqlite3StrICmp(db->aDb[iDb].zName, zDb)!=0 ){
- continue;
- }
- }
- }
- if( 0==(cntTab++) ){
- pExpr->iTable = pItem->iCursor;
- pExpr->pTab = pTab;
- pSchema = pTab->pSchema;
- pMatch = pItem;
- }
- for(j=0, pCol=pTab->aCol; j<pTab->nCol; j++, pCol++){
- if( sqlite3StrICmp(pCol->zName, 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->jointype & JT_NATURAL ) continue;
- if( nameInUsingClause(pItem->pUsing, zCol) ) continue;
- }
- cnt++;
- pExpr->iTable = pItem->iCursor;
- pExpr->pTab = pTab;
- pMatch = pItem;
- pSchema = pTab->pSchema;
- /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY */
- pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j;
- break;
- }
- }
- }
+ lookupNameInSrcList(pParse, db, zDb, zTab, zCol, pSrcList, pExpr, &cnt,
&cntTab, &pSchema, &pMatch);
}
#ifndef SQLITE_OMIT_TRIGGER
/* If we have not already resolved the name, then maybe
** it is a new.* or old.* trigger argument reference
@@ -977,27 +1085,10 @@
if( sqlite3ResolveExprNames(&sNC, p->pLimit) ||
sqlite3ResolveExprNames(&sNC, p->pOffset) ){
return WRC_Abort;
}
- /* Set up the local name-context to pass to sqlite3ResolveExprNames() to
- ** resolve the result-set expression list.
- */
- sNC.allowAgg = 1;
- sNC.pSrcList = p->pSrc;
- sNC.pNext = pOuterNC;
-
- /* Resolve names in the result set. */
- pEList = p->pEList;
- assert( pEList!=0 );
- for(i=0; i<pEList->nExpr; i++){
- Expr *pX = pEList->a[i].pExpr;
- if( sqlite3ResolveExprNames(&sNC, pX) ){
- return WRC_Abort;
- }
- }
-
/* Recursively resolve names in all subqueries
*/
for(i=0; i<p->pSrc->nSrc; i++){
struct SrcList_item *pItem = &p->pSrc->a[i];
if( pItem->pSelect ){
@@ -1018,10 +1109,27 @@
if( pParse->nErr || db->mallocFailed ) return WRC_Abort;
for(pNC=pOuterNC; pNC; pNC=pNC->pNext) nRef -= pNC->nRef;
assert( pItem->isCorrelated==0 && nRef<=0 );
pItem->isCorrelated = (nRef!=0);
+ }
+ }
+
+ /* Set up the local name-context to pass to sqlite3ResolveExprNames() to
+ ** resolve the result-set expression list.
+ */
+ sNC.allowAgg = 1;
+ sNC.pSrcList = p->pSrc;
+ sNC.pNext = pOuterNC;
+
+ /* Resolve names in the result set. */
+ pEList = p->pEList;
+ assert( pEList!=0 );
+ for(i=0; i<pEList->nExpr; i++){
+ Expr *pX = pEList->a[i].pExpr;
+ if( sqlite3ResolveExprNames(&sNC, pX) ){
+ return WRC_Abort;
}
}
/* If there are no aggregate functions in the result-set, and no GROUP BY
** expression, do not allow aggregates in any of the other expressions.
Index: src/sqliteInt.h
===================================================================
--- src/sqliteInt.h
+++ src/sqliteInt.h
@@ -2077,10 +2077,11 @@
#define SF_Aggregate 0x04 /* Contains aggregate functions */
#define SF_UsesEphemeral 0x08 /* Uses the OpenEphemeral opcode */
#define SF_Expanded 0x10 /* sqlite3SelectExpand() called on this */
#define SF_HasTypeInfo 0x20 /* FROM subqueries have Table metadata */
#define SF_UseSorter 0x40 /* Sort using a sorter */
+#define SF_NestedJoin 0x80 /* Represents a nested Join */
/*
** The results of a select can be distributed in several ways. The
** "SRT" prefix means "SELECT Result Type".
Index: test/auth2.test
===================================================================
--- test/auth2.test
+++ test/auth2.test
@@ -129,16 +129,16 @@
db eval {
SELECT a, b FROM v2;
}
set ::authargs
} {SQLITE_SELECT {} {} {} {}
-SQLITE_READ v2 a main {}
-SQLITE_READ v2 b main {}
SQLITE_READ t2 x main v2
SQLITE_READ t2 y main v2
SQLITE_READ t2 y main v2
SQLITE_READ t2 z main v2
+SQLITE_READ v2 a main {}
+SQLITE_READ v2 b main {}
SQLITE_SELECT {} {} {} v2
}
do_test auth2-2.4 {
db2 eval {
CREATE TABLE t3(p,q,r);
@@ -147,24 +147,24 @@
db eval {
SELECT b, a FROM v2;
}
set ::authargs
} {SQLITE_SELECT {} {} {} {}
-SQLITE_READ v2 b main {}
-SQLITE_READ v2 a main {}
SQLITE_READ t2 x main v2
SQLITE_READ t2 y main v2
SQLITE_READ t2 y main v2
SQLITE_READ t2 z main v2
+SQLITE_READ v2 b main {}
+SQLITE_READ v2 a main {}
SQLITE_SELECT {} {} {} v2
SQLITE_SELECT {} {} {} {}
-SQLITE_READ v2 b main {}
-SQLITE_READ v2 a main {}
SQLITE_READ t2 x main v2
SQLITE_READ t2 y main v2
SQLITE_READ t2 y main v2
SQLITE_READ t2 z main v2
+SQLITE_READ v2 b main {}
+SQLITE_READ v2 a main {}
SQLITE_SELECT {} {} {} v2
}
db2 close
finish_test
ADDED test/tkt-nestedjoins.test_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
