/ Check-in [551ce407]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improved handling of USING and NATURAL JOIN in 3-way and higher joins. Ticket [3338b3fa19ac4ab]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 551ce407bd77149865423511bd52eba2f404161a
User & Date: drh 2011-10-18 18:10:40
Context
2011-10-18
19:14
Fix an uninitialized variable in OR-clause processing. check-in: 54aecd92 user: drh tags: trunk
18:10
Improved handling of USING and NATURAL JOIN in 3-way and higher joins. Ticket [3338b3fa19ac4ab] check-in: 551ce407 user: drh tags: trunk
12:44
Fix a floating-point exception that can occur when an FTS4 query contains a large number of tokens connected by AND or NEAR operators. check-in: 3126754c user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

    93     93     ** allowing it to be repopulated by the memcpy() on the following line.
    94     94     */
    95     95     ExprSetProperty(pExpr, EP_Static);
    96     96     sqlite3ExprDelete(db, pExpr);
    97     97     memcpy(pExpr, pDup, sizeof(*pExpr));
    98     98     sqlite3DbFree(db, pDup);
    99     99   }
          100  +
          101  +
          102  +/*
          103  +** Return TRUE if the name zCol occurs anywhere in the USING clause.
          104  +**
          105  +** Return FALSE if the USING clause is NULL or if it does not contain
          106  +** zCol.
          107  +*/
          108  +static int nameInUsingClause(IdList *pUsing, const char *zCol){
          109  +  if( pUsing ){
          110  +    int k;
          111  +    for(k=0; k<pUsing->nId; k++){
          112  +      if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ) return 1;
          113  +    }
          114  +  }
          115  +  return 0;
          116  +}
          117  +
   100    118   
   101    119   /*
   102    120   ** Given the name of a column of the form X.Y.Z or Y.Z or just Z, look up
   103    121   ** that name in the set of source tables in pSrcList and make the pExpr 
   104    122   ** expression node refer back to that source column.  The following changes
   105    123   ** are made to pExpr:
   106    124   **
................................................................................
   185    203             pExpr->iTable = pItem->iCursor;
   186    204             pExpr->pTab = pTab;
   187    205             pSchema = pTab->pSchema;
   188    206             pMatch = pItem;
   189    207           }
   190    208           for(j=0, pCol=pTab->aCol; j<pTab->nCol; j++, pCol++){
   191    209             if( sqlite3StrICmp(pCol->zName, zCol)==0 ){
   192         -            IdList *pUsing;
          210  +            /* If there has been exactly one prior match and this match
          211  +            ** is for the right-hand table of a NATURAL JOIN or is in a 
          212  +            ** USING clause, then skip this match.
          213  +            */
          214  +            if( cnt==1 ){
          215  +              if( pItem->jointype & JT_NATURAL ) continue;
          216  +              if( nameInUsingClause(pItem->pUsing, zCol) ) continue;
          217  +            }
   193    218               cnt++;
   194    219               pExpr->iTable = pItem->iCursor;
   195    220               pExpr->pTab = pTab;
   196    221               pMatch = pItem;
   197    222               pSchema = pTab->pSchema;
   198    223               /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY */
   199    224               pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j;
   200         -            if( i<pSrcList->nSrc-1 ){
   201         -              if( pItem[1].jointype & JT_NATURAL ){
   202         -                /* If this match occurred in the left table of a natural join,
   203         -                ** then skip the right table to avoid a duplicate match */
   204         -                pItem++;
   205         -                i++;
   206         -              }else if( (pUsing = pItem[1].pUsing)!=0 ){
   207         -                /* If this match occurs on a column that is in the USING clause
   208         -                ** of a join, skip the search of the right table of the join
   209         -                ** to avoid a duplicate match there. */
   210         -                int k;
   211         -                for(k=0; k<pUsing->nId; k++){
   212         -                  if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ){
   213         -                    pItem++;
   214         -                    i++;
   215         -                    break;
   216         -                  }
   217         -                }
   218         -              }
   219         -            }
   220    225               break;
   221    226             }
   222    227           }
   223    228         }
   224    229       }
   225    230   
   226    231   #ifndef SQLITE_OMIT_TRIGGER

Changes to test/where3.test.

   337    337         AND bbb.parent = 4
   338    338       ORDER BY bbb.title COLLATE NOCASE ASC;
   339    339   } {
   340    340     0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 
   341    341     0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
   342    342     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   343    343   }
          344  +
          345  +# Name resolution with NATURAL JOIN and USING
          346  +#
          347  +do_test where3-6.setup {
          348  +  db eval {
          349  +    CREATE TABLE t6w(a, w);
          350  +    INSERT INTO t6w VALUES(1, 'w-one');
          351  +    INSERT INTO t6w VALUES(2, 'w-two');
          352  +    INSERT INTO t6w VALUES(9, 'w-nine');
          353  +    CREATE TABLE t6x(a, x);
          354  +    INSERT INTO t6x VALUES(1, 'x-one');
          355  +    INSERT INTO t6x VALUES(3, 'x-three');
          356  +    INSERT INTO t6x VALUES(9, 'x-nine');
          357  +    CREATE TABLE t6y(a, y);
          358  +    INSERT INTO t6y VALUES(1, 'y-one');
          359  +    INSERT INTO t6y VALUES(4, 'y-four');
          360  +    INSERT INTO t6y VALUES(9, 'y-nine');
          361  +    CREATE TABLE t6z(a, z);
          362  +    INSERT INTO t6z VALUES(1, 'z-one');
          363  +    INSERT INTO t6z VALUES(5, 'z-five');
          364  +    INSERT INTO t6z VALUES(9, 'z-nine');
          365  +  }
          366  +} {}
          367  +set cnt 0
          368  +foreach predicate {
          369  +   {}
          370  +   {ORDER BY a}
          371  +   {ORDER BY t6w.a}
          372  +   {WHERE a>0}
          373  +   {WHERE t6y.a>0}
          374  +   {WHERE a>0 ORDER BY a}
          375  +} {
          376  +  incr cnt
          377  +  do_test where3-6.$cnt.1 {
          378  +    set sql "SELECT * FROM t6w NATURAL JOIN t6x NATURAL JOIN t6y"
          379  +    append sql " NATURAL JOIN t6z "
          380  +    append sql $::predicate
          381  +    db eval $sql
          382  +  } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
          383  +  do_test where3-6.$cnt.2 {
          384  +    set sql "SELECT * FROM t6w JOIN t6x USING(a) JOIN t6y USING(a)"
          385  +    append sql " JOIN t6z USING(a) "
          386  +    append sql $::predicate
          387  +    db eval $sql
          388  +  } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
          389  +  do_test where3-6.$cnt.3 {
          390  +    set sql "SELECT * FROM t6w NATURAL JOIN t6x JOIN t6y USING(a)"
          391  +    append sql " JOIN t6z USING(a) "
          392  +    append sql $::predicate
          393  +    db eval $sql
          394  +  } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
          395  +  do_test where3-6.$cnt.4 {
          396  +    set sql "SELECT * FROM t6w JOIN t6x USING(a) NATURAL JOIN t6y"
          397  +    append sql " JOIN t6z USING(a) "
          398  +    append sql $::predicate
          399  +    db eval $sql
          400  +  } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
          401  +  do_test where3-6.$cnt.5 {
          402  +    set sql "SELECT * FROM t6w JOIN t6x USING(a) JOIN t6y USING(a)"
          403  +    append sql " NATURAL JOIN t6z "
          404  +    append sql $::predicate
          405  +    db eval $sql
          406  +  } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
          407  +  do_test where3-6.$cnt.6 {
          408  +    set sql "SELECT * FROM t6w JOIN t6x USING(a) NATURAL JOIN t6y"
          409  +    append sql " NATURAL JOIN t6z "
          410  +    append sql $::predicate
          411  +    db eval $sql
          412  +  } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
          413  +  do_test where3-6.$cnt.7 {
          414  +    set sql "SELECT * FROM t6w NATURAL JOIN t6x JOIN t6y USING(a)"
          415  +    append sql " NATURAL JOIN t6z "
          416  +    append sql $::predicate
          417  +    db eval $sql
          418  +  } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
          419  +  do_test where3-6.$cnt.8 {
          420  +    set sql "SELECT * FROM t6w NATURAL JOIN t6x NATURAL JOIN t6y"
          421  +    append sql " JOIN t6z USING(a) "
          422  +    append sql $::predicate
          423  +    db eval $sql
          424  +  } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine}
          425  +}
          426  +
   344    427   
   345    428   finish_test