/ Check-in [ea10f978]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:ON-clause terms in a LEFT JOIN that restrict only the left table, should not really restrict the left table but instead rows that fail to meet the condition to be joined with NULL rows from the right table. (CVS 2725)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ea10f9785e3c5248dafcc297f3a2c5465b6e0dba
User & Date: drh 2005-09-19 21:05:49
Context
2005-09-20
01:36
Documentation updates. (CVS 2726) check-in: 6ec8883c user: drh tags: trunk
2005-09-19
21:05
ON-clause terms in a LEFT JOIN that restrict only the left table, should not really restrict the left table but instead rows that fail to meet the condition to be joined with NULL rows from the right table. (CVS 2725) check-in: ea10f978 user: drh tags: trunk
19:05
Set the default maximum page size to 32768, not 8192. (CVS 2724) check-in: b32e9ec2 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.271 2005/09/19 17:35:53 drh Exp $
           15  +** $Id: select.c,v 1.272 2005/09/19 21:05:49 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Allocate a new Select structure and return a pointer to that
    22     22   ** structure.
................................................................................
   175    175   */
   176    176   static void addWhereTerm(
   177    177     const char *zCol,        /* Name of the column */
   178    178     const Table *pTab1,      /* First table */
   179    179     const char *zAlias1,     /* Alias for first table.  May be NULL */
   180    180     const Table *pTab2,      /* Second table */
   181    181     const char *zAlias2,     /* Alias for second table.  May be NULL */
          182  +  int iRightJoinTable,     /* VDBE cursor for the right table */
   182    183     Expr **ppExpr            /* Add the equality term to this expression */
   183    184   ){
   184    185     Expr *pE1a, *pE1b, *pE1c;
   185    186     Expr *pE2a, *pE2b, *pE2c;
   186    187     Expr *pE;
   187    188   
   188    189     pE1a = createIdExpr(zCol);
................................................................................
   195    196       zAlias2 = pTab2->zName;
   196    197     }
   197    198     pE2b = createIdExpr(zAlias2);
   198    199     pE1c = sqlite3Expr(TK_DOT, pE1b, pE1a, 0);
   199    200     pE2c = sqlite3Expr(TK_DOT, pE2b, pE2a, 0);
   200    201     pE = sqlite3Expr(TK_EQ, pE1c, pE2c, 0);
   201    202     ExprSetProperty(pE, EP_FromJoin);
          203  +  pE->iRightJoinTable = iRightJoinTable;
   202    204     *ppExpr = sqlite3ExprAnd(*ppExpr, pE);
   203    205   }
   204    206   
   205    207   /*
   206    208   ** Set the EP_FromJoin property on all terms of the given expression.
          209  +** And set the Expr.iRightJoinTable to iTable for every term in the
          210  +** expression.
   207    211   **
   208    212   ** The EP_FromJoin property is used on terms of an expression to tell
   209    213   ** the LEFT OUTER JOIN processing logic that this term is part of the
   210    214   ** join restriction specified in the ON or USING clause and not a part
   211    215   ** of the more general WHERE clause.  These terms are moved over to the
   212    216   ** WHERE clause during join processing but we need to remember that they
   213    217   ** originated in the ON or USING clause.
          218  +**
          219  +** The Expr.iRightJoinTable tells the WHERE clause processing that the
          220  +** expression depends on table iRightJoinTable even if that table is not
          221  +** explicitly mentioned in the expression.  That information is needed
          222  +** for cases like this:
          223  +**
          224  +**    SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.b AND t1.x=5
          225  +**
          226  +** The where clause needs to defer the handling of the t1.x=5
          227  +** term until after the t2 loop of the join.  In that way, a
          228  +** NULL t2 row will be inserted whenever t1.x!=5.  If we do not
          229  +** defer the handling of t1.x=5, it will be processed immediately
          230  +** after the t1 loop and rows with t1.x!=5 will never appear in
          231  +** the output, which is incorrect.
   214    232   */
   215         -static void setJoinExpr(Expr *p){
          233  +static void setJoinExpr(Expr *p, int iTable){
   216    234     while( p ){
   217    235       ExprSetProperty(p, EP_FromJoin);
   218         -    setJoinExpr(p->pLeft);
          236  +    p->iRightJoinTable = iTable;
          237  +    setJoinExpr(p->pLeft, iTable);
   219    238       p = p->pRight;
   220    239     } 
   221    240   }
   222    241   
   223    242   /*
   224    243   ** This routine processes the join information for a SELECT statement.
   225    244   ** ON and USING clauses are converted into extra terms of the WHERE clause.
................................................................................
   258    277              "an ON or USING clause", 0);
   259    278           return 1;
   260    279         }
   261    280         for(j=0; j<pLeftTab->nCol; j++){
   262    281           char *zName = pLeftTab->aCol[j].zName;
   263    282           if( columnIndex(pRightTab, zName)>=0 ){
   264    283             addWhereTerm(zName, pLeftTab, pLeft->zAlias, 
   265         -                              pRightTab, pRight->zAlias, &p->pWhere);
          284  +                              pRightTab, pRight->zAlias,
          285  +                              pRight->iCursor, &p->pWhere);
          286  +          
   266    287           }
   267    288         }
   268    289       }
   269    290   
   270    291       /* Disallow both ON and USING clauses in the same join
   271    292       */
   272    293       if( pLeft->pOn && pLeft->pUsing ){
................................................................................
   275    296         return 1;
   276    297       }
   277    298   
   278    299       /* Add the ON clause to the end of the WHERE clause, connected by
   279    300       ** an AND operator.
   280    301       */
   281    302       if( pLeft->pOn ){
   282         -      setJoinExpr(pLeft->pOn);
          303  +      setJoinExpr(pLeft->pOn, pRight->iCursor);
   283    304         p->pWhere = sqlite3ExprAnd(p->pWhere, pLeft->pOn);
   284    305         pLeft->pOn = 0;
   285    306       }
   286    307   
   287    308       /* Create extra terms on the WHERE clause for each column named
   288    309       ** in the USING clause.  Example: If the two tables to be joined are 
   289    310       ** A and B and the USING clause names X, Y, and Z, then add this
................................................................................
   297    318           char *zName = pList->a[j].zName;
   298    319           if( columnIndex(pLeftTab, zName)<0 || columnIndex(pRightTab, zName)<0 ){
   299    320             sqlite3ErrorMsg(pParse, "cannot join using column %s - column "
   300    321               "not present in both tables", zName);
   301    322             return 1;
   302    323           }
   303    324           addWhereTerm(zName, pLeftTab, pLeft->zAlias, 
   304         -                            pRightTab, pRight->zAlias, &p->pWhere);
          325  +                            pRightTab, pRight->zAlias,
          326  +                            pRight->iCursor, &p->pWhere);
   305    327         }
   306    328       }
   307    329     }
   308    330     return 0;
   309    331   }
   310    332   
   311    333   /*

Changes to src/sqliteInt.h.

     7      7   **    May you do good and not evil.
     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** Internal interface definitions for SQLite.
    13     13   **
    14         -** @(#) $Id: sqliteInt.h,v 1.420 2005/09/19 17:35:53 drh Exp $
           14  +** @(#) $Id: sqliteInt.h,v 1.421 2005/09/19 21:05:49 drh Exp $
    15     15   */
    16     16   #ifndef _SQLITEINT_H_
    17     17   #define _SQLITEINT_H_
    18     18   
    19     19   /*
    20     20   ** Many people are failing to set -DNDEBUG=1 when compiling SQLite.
    21     21   ** Setting NDEBUG makes the code smaller and run faster.  So the following
................................................................................
   899    899                            ** or in "<expr> IN (<expr-list)" */
   900    900     Token token;           /* An operand token */
   901    901     Token span;            /* Complete text of the expression */
   902    902     int iTable, iColumn;   /* When op==TK_COLUMN, then this expr node means the
   903    903                            ** iColumn-th field of the iTable-th table. */
   904    904     AggInfo *pAggInfo;     /* Used by TK_AGG_COLUMN and TK_AGG_FUNCTION */
   905    905     int iAgg;              /* Which entry in pAggInfo->aCol[] or ->aFunc[] */
          906  +  int iRightJoinTable;   /* If EP_FromJoin, the right table of the join */
   906    907     Select *pSelect;       /* When the expression is a sub-select.  Also the
   907    908                            ** right side of "<expr> IN (<select>)" */
   908    909     Table *pTab;           /* Table for OP_Column expressions. */
   909    910   };
   910    911   
   911    912   /*
   912    913   ** The following are the meanings of bits in the Expr.flags field.

Changes to src/where.c.

    12     12   ** This module contains C code that generates VDBE code used to process
    13     13   ** the WHERE clause of SQL statements.  This module is reponsible for
    14     14   ** generating the code that loops through a table looking for applicable
    15     15   ** rows.  Indices are selected and used to speed the search when doing
    16     16   ** so is applicable.  Because this module is responsible for selecting
    17     17   ** indices, you might also think of this module as the "query optimizer".
    18     18   **
    19         -** $Id: where.c,v 1.176 2005/09/19 13:15:23 drh Exp $
           19  +** $Id: where.c,v 1.177 2005/09/19 21:05:49 drh Exp $
    20     20   */
    21     21   #include "sqliteInt.h"
    22     22   
    23     23   /*
    24     24   ** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
    25     25   */
    26     26   #define BMS  (sizeof(Bitmask)*8)
................................................................................
   552    552     if( pExpr->op==TK_IN ){
   553    553       assert( pExpr->pRight==0 );
   554    554       pTerm->prereqRight = exprListTableUsage(pMaskSet, pExpr->pList)
   555    555                             | exprSelectTableUsage(pMaskSet, pExpr->pSelect);
   556    556     }else{
   557    557       pTerm->prereqRight = exprTableUsage(pMaskSet, pExpr->pRight);
   558    558     }
   559         -  pTerm->prereqAll = prereqAll = exprTableUsage(pMaskSet, pExpr);
          559  +  prereqAll = exprTableUsage(pMaskSet, pExpr);
          560  +  if( ExprHasProperty(pExpr, EP_FromJoin) ){
          561  +    prereqAll |= getMask(pMaskSet, pExpr->iRightJoinTable);
          562  +  }
          563  +  pTerm->prereqAll = prereqAll;
   560    564     pTerm->leftCursor = -1;
   561    565     pTerm->iParent = -1;
   562    566     pTerm->operator = 0;
   563    567     if( allowedOp(pExpr->op) && (pTerm->prereqRight & prereqLeft)==0 ){
   564    568       Expr *pLeft = pExpr->pLeft;
   565    569       Expr *pRight = pExpr->pRight;
   566         -    assert( prereqAll == (pTerm->prereqRight | prereqLeft) ); /* ticket 1433 */
   567    570       if( pLeft->op==TK_COLUMN ){
   568    571         pTerm->leftCursor = pLeft->iTable;
   569    572         pTerm->leftColumn = pLeft->iColumn;
   570    573         pTerm->operator = operatorMask(pExpr->op);
   571    574       }
   572    575       if( pRight && pRight->op==TK_COLUMN ){
   573    576         WhereTerm *pNew;

Added test/join5.test.

            1  +# 2005 September 19
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +# This file implements tests for left outer joins containing ON
           14  +# clauses that restrict the scope of the left term of the join.
           15  +#
           16  +# $Id: join5.test,v 1.1 2005/09/19 21:05:50 drh Exp $
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +
           21  +
           22  +do_test join5-1.1 {
           23  +  execsql {
           24  +    BEGIN;
           25  +    CREATE TABLE t1(a integer primary key, b integer, c integer);
           26  +    CREATE TABLE t2(x integer primary key, y);
           27  +    CREATE TABLE t3(p integer primary key, q);
           28  +    INSERT INTO t3 VALUES(11,'t3-11');
           29  +    INSERT INTO t3 VALUES(12,'t3-12');
           30  +    INSERT INTO t2 VALUES(11,'t2-11');
           31  +    INSERT INTO t2 VALUES(12,'t2-12');
           32  +    INSERT INTO t1 VALUES(1, 5, 0);
           33  +    INSERT INTO t1 VALUES(2, 11, 2);
           34  +    INSERT INTO t1 VALUES(3, 12, 1);
           35  +    COMMIT;
           36  +  }
           37  +} {}
           38  +do_test join5-1.2 {
           39  +  execsql {
           40  +    select * from t1 left join t2 on t1.b=t2.x and t1.c=1
           41  +  }
           42  +} {1 5 0 {} {} 2 11 2 {} {} 3 12 1 12 t2-12}
           43  +do_test join5-1.3 {
           44  +  execsql {
           45  +    select * from t1 left join t2 on t1.b=t2.x where t1.c=1
           46  +  }
           47  +} {3 12 1 12 t2-12}
           48  +do_test join5-1.4 {
           49  +  execsql {
           50  +    select * from t1 left join t2 on t1.b=t2.x and t1.c=1
           51  +                     left join t3 on t1.b=t3.p and t1.c=2
           52  +  }
           53  +} {1 5 0 {} {} {} {} 2 11 2 {} {} 11 t3-11 3 12 1 12 t2-12 {} {}}
           54  +do_test join5-1.5 {
           55  +  execsql {
           56  +    select * from t1 left join t2 on t1.b=t2.x and t1.c=1
           57  +                     left join t3 on t1.b=t3.p where t1.c=2
           58  +  }
           59  +} {2 11 2 {} {} 11 t3-11}
           60  +
           61  +
           62  +finish_test