Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix the query optimizer so that it correctly handles constant expressions in the ON clause of a LEFT JOIN. Ticket #2403. (CVS 4049) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
46fdd195483787eef209a9b8ad108eba |
User & Date: | drh 2007-06-08 00:20:48.000 |
Context
2007-06-08
| ||
08:39 | Make sure rowid comparisons against NULL work correctly. Ticket #2404. (CVS 4050) (check-in: 11ee8ea43f user: drh tags: trunk) | |
00:20 | Fix the query optimizer so that it correctly handles constant expressions in the ON clause of a LEFT JOIN. Ticket #2403. (CVS 4049) (check-in: 46fdd19548 user: drh tags: trunk) | |
2007-06-07
| ||
19:08 | Make sure zeroblob does reasonable things with a negative argument or an argument that is larger than the maximum blob size. (CVS 4048) (check-in: f40218434e user: drh tags: trunk) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains routines used for analyzing expressions and ** for generating VDBE code that evaluates expressions in SQLite. ** ** $Id: expr.c,v 1.297 2007/06/08 00:20:48 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** Return the 'affinity' of the expression pExpr if any. ** |
︙ | ︙ | |||
827 828 829 830 831 832 833 834 835 836 837 | ** then do nothing. ** ** After walking the whole tree, if no nodes are found that disqualify ** the expression as constant, then we assume the whole expression ** is constant. See sqlite3ExprIsConstant() for additional information. */ static int exprNodeIsConstant(void *pArg, Expr *pExpr){ switch( pExpr->op ){ /* Consider functions to be constant if all their arguments are constant ** and *pArg==2 */ case TK_FUNCTION: | > > > > > > > > > > | | | > > > > > > > > > > > > | 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 | ** then do nothing. ** ** After walking the whole tree, if no nodes are found that disqualify ** the expression as constant, then we assume the whole expression ** is constant. See sqlite3ExprIsConstant() for additional information. */ static int exprNodeIsConstant(void *pArg, Expr *pExpr){ int *pN = (int*)pArg; /* If *pArg is 3 then any term of the expression that comes from ** the ON or USING clauses of a join disqualifies the expression ** from being considered constant. */ if( (*pN)==3 && ExprHasAnyProperty(pExpr, EP_FromJoin) ){ *pN = 0; return 2; } switch( pExpr->op ){ /* Consider functions to be constant if all their arguments are constant ** and *pArg==2 */ case TK_FUNCTION: if( (*pN)==2 ) return 0; /* Fall through */ case TK_ID: case TK_COLUMN: case TK_DOT: case TK_AGG_FUNCTION: case TK_AGG_COLUMN: #ifndef SQLITE_OMIT_SUBQUERY case TK_SELECT: case TK_EXISTS: #endif *pN = 0; return 2; case TK_IN: if( pExpr->pSelect ){ *pN = 0; return 2; } default: return 0; } } /* ** Walk an expression tree. Return 1 if the expression is constant ** and 0 if it involves variables or function calls. ** ** For the purposes of this function, a double-quoted string (ex: "abc") ** is considered a variable but a single-quoted string (ex: 'abc') is ** a constant. */ int sqlite3ExprIsConstant(Expr *p){ int isConst = 1; walkExprTree(p, exprNodeIsConstant, &isConst); return isConst; } /* ** Walk an expression tree. Return 1 if the expression is constant ** that does no originate from the ON or USING clauses of a join. ** Return 0 if it involves variables or function calls or terms from ** an ON or USING clause. */ int sqlite3ExprIsConstantNotJoin(Expr *p){ int isConst = 3; walkExprTree(p, exprNodeIsConstant, &isConst); return isConst!=0; } /* ** Walk an expression tree. Return 1 if the expression is constant ** or a function call with constant arguments. Return and 0 if there ** are any variables. ** ** For the purposes of this function, a double-quoted string (ex: "abc") |
︙ | ︙ |
Changes to src/sqliteInt.h.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** ** @(#) $Id: sqliteInt.h,v 1.571 2007/06/08 00:20:48 drh Exp $ */ #ifndef _SQLITEINT_H_ #define _SQLITEINT_H_ #include "limits.h" #if defined(SQLITE_TCL) || defined(TCLSH) |
︙ | ︙ | |||
1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 | void sqlite3Randomness(int, void*); void sqlite3RollbackAll(sqlite3*); void sqlite3CodeVerifySchema(Parse*, int); void sqlite3BeginTransaction(Parse*, int); void sqlite3CommitTransaction(Parse*); void sqlite3RollbackTransaction(Parse*); int sqlite3ExprIsConstant(Expr*); int sqlite3ExprIsConstantOrFunction(Expr*); int sqlite3ExprIsInteger(Expr*, int*); int sqlite3IsRowid(const char*); void sqlite3GenerateRowDelete(sqlite3*, Vdbe*, Table*, int, int); void sqlite3GenerateRowIndexDelete(Vdbe*, Table*, int, char*); void sqlite3GenerateIndexKey(Vdbe*, Index*, int); void sqlite3GenerateConstraintChecks(Parse*,Table*,int,char*,int,int,int,int); | > | 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 | void sqlite3Randomness(int, void*); void sqlite3RollbackAll(sqlite3*); void sqlite3CodeVerifySchema(Parse*, int); void sqlite3BeginTransaction(Parse*, int); void sqlite3CommitTransaction(Parse*); void sqlite3RollbackTransaction(Parse*); int sqlite3ExprIsConstant(Expr*); int sqlite3ExprIsConstantNotJoin(Expr*); int sqlite3ExprIsConstantOrFunction(Expr*); int sqlite3ExprIsInteger(Expr*, int*); int sqlite3IsRowid(const char*); void sqlite3GenerateRowDelete(sqlite3*, Vdbe*, Table*, int, int); void sqlite3GenerateRowIndexDelete(Vdbe*, Table*, int, char*); void sqlite3GenerateIndexKey(Vdbe*, Index*, int); void sqlite3GenerateConstraintChecks(Parse*,Table*,int,char*,int,int,int,int); |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is reponsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is reponsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** ** $Id: where.c,v 1.251 2007/06/08 00:20:48 drh Exp $ */ #include "sqliteInt.h" /* ** The number of bits in a Bitmask. "BMS" means "BitMask Size". */ #define BMS (sizeof(Bitmask)*8) |
︙ | ︙ | |||
1983 1984 1985 1986 1987 1988 1989 | pWInfo->pParse = pParse; pWInfo->pTabList = pTabList; pWInfo->iBreak = sqlite3VdbeMakeLabel(v); /* Special case: a WHERE clause that is constant. Evaluate the ** expression and either jump over all of the code or fall thru. */ | | | 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 | pWInfo->pParse = pParse; pWInfo->pTabList = pTabList; pWInfo->iBreak = sqlite3VdbeMakeLabel(v); /* Special case: a WHERE clause that is constant. Evaluate the ** expression and either jump over all of the code or fall thru. */ if( pWhere && (pTabList->nSrc==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){ sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, 1); pWhere = 0; } /* Analyze all of the subexpressions. Note that exprAnalyze() might ** add new virtual terms onto the end of the WHERE clause. We do not ** want to analyze these virtual terms, so start analyzing at the end |
︙ | ︙ |
Changes to test/join5.test.
︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for left outer joins containing ON # clauses that restrict the scope of the left term of the join. # | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for left outer joins containing ON # clauses that restrict the scope of the left term of the join. # # $Id: join5.test,v 1.2 2007/06/08 00:20:48 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test join5-1.1 { execsql { |
︙ | ︙ | |||
53 54 55 56 57 58 59 60 | } {1 5 0 {} {} {} {} 2 11 2 {} {} 11 t3-11 3 12 1 12 t2-12 {} {}} do_test join5-1.5 { execsql { select * from t1 left join t2 on t1.b=t2.x and t1.c=1 left join t3 on t1.b=t3.p where t1.c=2 } } {2 11 2 {} {} 11 t3-11} | > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 | } {1 5 0 {} {} {} {} 2 11 2 {} {} 11 t3-11 3 12 1 12 t2-12 {} {}} do_test join5-1.5 { execsql { select * from t1 left join t2 on t1.b=t2.x and t1.c=1 left join t3 on t1.b=t3.p where t1.c=2 } } {2 11 2 {} {} 11 t3-11} # Ticket #2403 # do_test join5-2.1 { execsql { CREATE TABLE ab(a,b); INSERT INTO "ab" VALUES(1,2); INSERT INTO "ab" VALUES(3,NULL); CREATE TABLE xy(x,y); INSERT INTO "xy" VALUES(2,3); INSERT INTO "xy" VALUES(NULL,1); } execsql {SELECT * FROM xy LEFT JOIN ab ON 0} } {2 3 {} {} {} 1 {} {}} do_test join5-2.2 { execsql {SELECT * FROM xy LEFT JOIN ab ON 1} } {2 3 1 2 2 3 3 {} {} 1 1 2 {} 1 3 {}} do_test join5-2.3 { execsql {SELECT * FROM xy LEFT JOIN ab ON NULL} } {2 3 {} {} {} 1 {} {}} do_test join5-2.4 { execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 0} } {} do_test join5-2.5 { execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 0} } {} do_test join5-2.6 { execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 0} } {} do_test join5-2.7 { execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE 1} } {2 3 {} {} {} 1 {} {}} do_test join5-2.8 { execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE 1} } {2 3 1 2 2 3 3 {} {} 1 1 2 {} 1 3 {}} do_test join5-2.9 { execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE 1} } {2 3 {} {} {} 1 {} {}} do_test join5-2.10 { execsql {SELECT * FROM xy LEFT JOIN ab ON 0 WHERE NULL} } {} do_test join5-2.11 { execsql {SELECT * FROM xy LEFT JOIN ab ON 1 WHERE NULL} } {} do_test join5-2.12 { execsql {SELECT * FROM xy LEFT JOIN ab ON NULL WHERE NULL} } {} finish_test |