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: |
ea10f9785e3c5248dafcc297f3a2c546 |
| 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
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