/ Check-in [46fdd195]
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: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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 46fdd195483787eef209a9b8ad108eba147be6fa
User & Date: drh 2007-06-08 00:20:48
Context
2007-06-08
08:39
Make sure rowid comparisons against NULL work correctly. Ticket #2404. (CVS 4050) check-in: 11ee8ea4 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: 46fdd195 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: f4021843 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
...
867
868
869
870
871
872
873












874
875
876
877
878
879
880
**    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.296 2007/05/30 10:36:47 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
** 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:
      if( *((int*)pArg)==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
      *((int*)pArg) = 0;
      return 2;
    case TK_IN:
      if( pExpr->pSelect ){
        *((int*)pArg) = 0;
        return 2;
      }
    default:
      return 0;
  }
}

................................................................................
** 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
** 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")







|







 







>
>
>
>
>
>
>
>
>
>




|










|



|







 







>
>
>
>
>
>
>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
...
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
**    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.
**
................................................................................
** 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;
  }
}

................................................................................
** 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.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1738
1739
1740
1741
1742
1743
1744

1745
1746
1747
1748
1749
1750
1751
**    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.570 2007/05/29 12:11:30 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_
#include "limits.h"


#if defined(SQLITE_TCL) || defined(TCLSH)
................................................................................
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);







|







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
**    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)
................................................................................
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
19
20
21
22
23
24
25
26
....
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
** 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.250 2007/06/02 07:54:38 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
  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 || sqlite3ExprIsConstant(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







|







 







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
** 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)
................................................................................
  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
16
17
18
19
20
21
22
23
..
53
54
55
56
57
58
59
60

61















































62
#
#***********************************************************************
# 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.1 2005/09/19 21:05:50 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl


do_test join5-1.1 {
  execsql {
................................................................................
} {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}


















































finish_test







|







 








>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
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
#
#***********************************************************************
# 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 {
................................................................................
} {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