/ Check-in [5627ff74]
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:Remove terms with operator TK_AS from the expression tree. Ticket #2356. (CVS 3991)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 5627ff74be9242418434a06fe5c104d1f9128cab
User & Date: drh 2007-05-14 11:34:47
Context
2007-05-14
12:12
In the windows driver, reacquire the shared lock if an exclusive lock fails. Ticket #2354. (CVS 3992) check-in: fc489b53 user: drh tags: trunk
11:34
Remove terms with operator TK_AS from the expression tree. Ticket #2356. (CVS 3991) check-in: 5627ff74 user: drh tags: trunk
2007-05-12
15:00
Make REINDEX robust in the face of malloc() errors. (CVS 3990) check-in: dbe41774 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/auth.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
**
*************************************************************************
** This file contains code used to implement the sqlite3_set_authorizer()
** API.  This facility is an optional feature of the library.  Embedded
** systems that do not need this facility may omit it by recompiling
** the library with -DSQLITE_OMIT_AUTHORIZATION=1
**
** $Id: auth.c,v 1.25 2006/06/16 08:01:03 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** All of the code in this file may be omitted by defining a single
** macro.
*/
................................................................................
  const char *zCol;     /* Name of the column of the table */
  int iSrc;             /* Index in pTabList->a[] of table being read */
  const char *zDBase;   /* Name of database being accessed */
  TriggerStack *pStack; /* The stack of current triggers */
  int iDb;              /* The index of the database the expression refers to */

  if( db->xAuth==0 ) return;
  if( pExpr->op==TK_AS ) return;
  assert( pExpr->op==TK_COLUMN );
  iDb = sqlite3SchemaToIndex(pParse->db, pExpr->pSchema);
  if( iDb<0 ){
    /* An attempt to read a column out of a subquery or other
    ** temporary table. */
    return;
  }
  for(iSrc=0; pTabList && iSrc<pTabList->nSrc; iSrc++){







|







 







|
<







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
111
112
113
114
115
116
117
118

119
120
121
122
123
124
125
**
*************************************************************************
** This file contains code used to implement the sqlite3_set_authorizer()
** API.  This facility is an optional feature of the library.  Embedded
** systems that do not need this facility may omit it by recompiling
** the library with -DSQLITE_OMIT_AUTHORIZATION=1
**
** $Id: auth.c,v 1.26 2007/05/14 11:34:47 drh Exp $
*/
#include "sqliteInt.h"

/*
** All of the code in this file may be omitted by defining a single
** macro.
*/
................................................................................
  const char *zCol;     /* Name of the column of the table */
  int iSrc;             /* Index in pTabList->a[] of table being read */
  const char *zDBase;   /* Name of database being accessed */
  TriggerStack *pStack; /* The stack of current triggers */
  int iDb;              /* The index of the database the expression refers to */

  if( db->xAuth==0 ) return;
  if( pExpr->op!=TK_COLUMN ) return;

  iDb = sqlite3SchemaToIndex(pParse->db, pExpr->pSchema);
  if( iDb<0 ){
    /* An attempt to read a column out of a subquery or other
    ** temporary table. */
    return;
  }
  for(iSrc=0; pTabList && iSrc<pTabList->nSrc; iSrc++){

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
..
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
...
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
....
1110
1111
1112
1113
1114
1115
1116

1117
1118
1119

1120






1121
1122
1123
1124
1125
1126
1127
....
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
**    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.292 2007/05/12 06:11:12 danielk1977 Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
** CREATE TABLE t1(a);
** SELECT * FROM t1 WHERE a;
** SELECT a AS b FROM t1 WHERE b;
** SELECT * FROM t1 WHERE (select a from t1);
*/
char sqlite3ExprAffinity(Expr *pExpr){
  int op = pExpr->op;
  if( op==TK_AS ){
    return sqlite3ExprAffinity(pExpr->pLeft);
  }
  if( op==TK_SELECT ){
    return sqlite3ExprAffinity(pExpr->pSelect->pEList->a[0].pExpr);
  }
#ifndef SQLITE_OMIT_CAST
  if( op==TK_CAST ){
    return sqlite3AffinityType(&pExpr->token);
  }
................................................................................
** Return the default collation sequence for the expression pExpr. If
** there is no default collation type, return 0.
*/
CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr){
  CollSeq *pColl = 0;
  if( pExpr ){
    pColl = pExpr->pColl;
    if( (pExpr->op==TK_AS || pExpr->op==TK_CAST) && !pColl ){
      return sqlite3ExprCollSeq(pParse, pExpr->pLeft);
    }
  }
  if( sqlite3CheckCollSeq(pParse, pColl) ){ 
    pColl = 0;
  }
  return pColl;
................................................................................
    assert( pNew->token.z==0 );
  }
  pNew->span.z = 0;
  pNew->pLeft = sqlite3ExprDup(p->pLeft);
  pNew->pRight = sqlite3ExprDup(p->pRight);
  pNew->pList = sqlite3ExprListDup(p->pList);
  pNew->pSelect = sqlite3SelectDup(p->pSelect);
  pNew->pTab = p->pTab;
#if SQLITE_MAX_EXPR_DEPTH>0
  pNew->nHeight = p->nHeight;
#endif
  return pNew;
}
void sqlite3TokenCopy(Token *pTo, Token *pFrom){
  if( pTo->dyn ) sqliteFree((char*)pTo->z);
  if( pFrom->z ){
    pTo->n = pFrom->n;
    pTo->z = (u8*)sqliteStrNDup((char*)pFrom->z, pFrom->n);
................................................................................
    ** Note that the expression in the result set should have already been
    ** resolved by the time the WHERE clause is resolved.
    */
    if( cnt==0 && (pEList = pNC->pEList)!=0 && zTab==0 ){
      for(j=0; j<pEList->nExpr; j++){
        char *zAs = pEList->a[j].zName;
        if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){

          assert( pExpr->pLeft==0 && pExpr->pRight==0 );
          pExpr->op = TK_AS;
          pExpr->iColumn = j;

          pExpr->pLeft = sqlite3ExprDup(pEList->a[j].pExpr);






          cnt = 1;
          assert( zTab==0 && zDb==0 );
          goto lookupname_end_2;
        }
      } 
    }

................................................................................
      pLItem++;
      pRight = pLItem->pExpr;
      sqlite3ExprCode(pParse, pRight);
      codeCompare(pParse, pLeft, pRight, OP_Le, 0, 0);
      sqlite3VdbeAddOp(v, OP_And, 0, 0);
      break;
    }
    case TK_UPLUS:
    case TK_AS: {
      sqlite3ExprCode(pParse, pExpr->pLeft);
      stackChng = 0;
      break;
    }
    case TK_CASE: {
      int expr_end_label;
      int jumpInst;







|







 







<
<
<







 







|







 







<
<
<
<







 







>

<
|
>
|
>
>
>
>
>
>







 







|
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
31
32
33
34
35
36
37



38
39
40
41
42
43
44
..
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
...
474
475
476
477
478
479
480




481
482
483
484
485
486
487
....
1103
1104
1105
1106
1107
1108
1109
1110
1111

1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
....
1956
1957
1958
1959
1960
1961
1962
1963

1964
1965
1966
1967
1968
1969
1970
**    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.293 2007/05/14 11:34:47 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
................................................................................
** CREATE TABLE t1(a);
** SELECT * FROM t1 WHERE a;
** SELECT a AS b FROM t1 WHERE b;
** SELECT * FROM t1 WHERE (select a from t1);
*/
char sqlite3ExprAffinity(Expr *pExpr){
  int op = pExpr->op;



  if( op==TK_SELECT ){
    return sqlite3ExprAffinity(pExpr->pSelect->pEList->a[0].pExpr);
  }
#ifndef SQLITE_OMIT_CAST
  if( op==TK_CAST ){
    return sqlite3AffinityType(&pExpr->token);
  }
................................................................................
** Return the default collation sequence for the expression pExpr. If
** there is no default collation type, return 0.
*/
CollSeq *sqlite3ExprCollSeq(Parse *pParse, Expr *pExpr){
  CollSeq *pColl = 0;
  if( pExpr ){
    pColl = pExpr->pColl;
    if( pExpr->op==TK_CAST && !pColl ){
      return sqlite3ExprCollSeq(pParse, pExpr->pLeft);
    }
  }
  if( sqlite3CheckCollSeq(pParse, pColl) ){ 
    pColl = 0;
  }
  return pColl;
................................................................................
    assert( pNew->token.z==0 );
  }
  pNew->span.z = 0;
  pNew->pLeft = sqlite3ExprDup(p->pLeft);
  pNew->pRight = sqlite3ExprDup(p->pRight);
  pNew->pList = sqlite3ExprListDup(p->pList);
  pNew->pSelect = sqlite3SelectDup(p->pSelect);




  return pNew;
}
void sqlite3TokenCopy(Token *pTo, Token *pFrom){
  if( pTo->dyn ) sqliteFree((char*)pTo->z);
  if( pFrom->z ){
    pTo->n = pFrom->n;
    pTo->z = (u8*)sqliteStrNDup((char*)pFrom->z, pFrom->n);
................................................................................
    ** Note that the expression in the result set should have already been
    ** resolved by the time the WHERE clause is resolved.
    */
    if( cnt==0 && (pEList = pNC->pEList)!=0 && zTab==0 ){
      for(j=0; j<pEList->nExpr; j++){
        char *zAs = pEList->a[j].zName;
        if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){
          Expr *pDup;
          assert( pExpr->pLeft==0 && pExpr->pRight==0 );

          assert( pExpr->pList==0 );
          assert( pExpr->pSelect==0 );
          pDup = sqlite3ExprDup(pEList->a[j].pExpr);
          if( pExpr->flags & EP_ExpCollate ){
            pDup->pColl = pExpr->pColl;
            pDup->flags |= EP_ExpCollate;
          }
          memcpy(pExpr, pDup, sizeof(*pExpr));
          sqliteFree(pDup);
          cnt = 1;
          assert( zTab==0 && zDb==0 );
          goto lookupname_end_2;
        }
      } 
    }

................................................................................
      pLItem++;
      pRight = pLItem->pExpr;
      sqlite3ExprCode(pParse, pRight);
      codeCompare(pParse, pLeft, pRight, OP_Le, 0, 0);
      sqlite3VdbeAddOp(v, OP_And, 0, 0);
      break;
    }
    case TK_UPLUS: {

      sqlite3ExprCode(pParse, pExpr->pLeft);
      stackChng = 0;
      break;
    }
    case TK_CASE: {
      int expr_end_label;
      int jumpInst;

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.344 2007/05/10 10:46:57 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  char const *zType = 0;
  char const *zOriginDb = 0;
  char const *zOriginTab = 0;
  char const *zOriginCol = 0;
  int j;
  if( pExpr==0 || pNC->pSrcList==0 ) return 0;

  /* The TK_AS operator can only occur in ORDER BY, GROUP BY, HAVING,
  ** and LIMIT clauses.  But pExpr originates in the result set of a
  ** SELECT.  So pExpr can never contain an AS operator.
  */
  assert( pExpr->op!=TK_AS );

  switch( pExpr->op ){
    case TK_AGG_COLUMN:
    case TK_COLUMN: {
      /* The expression is a column. Locate the table the column is being
      ** extracted from in NameContext.pSrcList. This table may be real
      ** database table or a subquery.
      */







|







 







<
<
<
<
<
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
818
819
820
821
822
823
824






825
826
827
828
829
830
831
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.345 2007/05/14 11:34:47 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  char const *zType = 0;
  char const *zOriginDb = 0;
  char const *zOriginTab = 0;
  char const *zOriginCol = 0;
  int j;
  if( pExpr==0 || pNC->pSrcList==0 ) return 0;







  switch( pExpr->op ){
    case TK_AGG_COLUMN:
    case TK_COLUMN: {
      /* The expression is a column. Locate the table the column is being
      ** extracted from in NameContext.pSrcList. This table may be real
      ** database table or a subquery.
      */

Changes to test/where.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
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
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.41 2007/02/06 23:41:34 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
}

# Verify that queries use an index.  We are using the special variable
# "sqlite_search_count" which tallys the number of executions of MoveTo
# and Next operators in the VDBE.  By verifing that the search count is
# small we can be assured that indices are being used properly.
#
do_test where-1.1 {
  count {SELECT x, y FROM t1 WHERE w=10}
} {3 121 3}
do_test where-1.1.2 {
  set sqlite_query_plan
} {t1 i1w}






do_test where-1.2 {
  count {SELECT x, y FROM t1 WHERE w=11}
} {3 144 3}



do_test where-1.3 {
  count {SELECT x, y FROM t1 WHERE 11=w}
} {3 144 3}



do_test where-1.4 {
  count {SELECT x, y FROM t1 WHERE 11=w AND x>2}
} {3 144 3}
do_test where-1.4.2 {
  set sqlite_query_plan
} {t1 i1w}






do_test where-1.5 {
  count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 3}
do_test where-1.5.2 {
  set sqlite_query_plan
} {t1 i1w}
do_test where-1.6 {







|







 







|
|
|



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



>
>
>
>
>
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
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
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the use of indices in WHERE clases.
#
# $Id: where.test,v 1.42 2007/05/14 11:34:47 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
}

# Verify that queries use an index.  We are using the special variable
# "sqlite_search_count" which tallys the number of executions of MoveTo
# and Next operators in the VDBE.  By verifing that the search count is
# small we can be assured that indices are being used properly.
#
do_test where-1.1.1 {
  count {SELECT x, y, w FROM t1 WHERE w=10}
} {3 121 10 3}
do_test where-1.1.2 {
  set sqlite_query_plan
} {t1 i1w}
do_test where-1.1.3 {
  count {SELECT x, y, w AS abc FROM t1 WHERE abc=10}
} {3 121 10 3}
do_test where-1.1.4 {
  set sqlite_query_plan
} {t1 i1w}
do_test where-1.2.1 {
  count {SELECT x, y, w FROM t1 WHERE w=11}
} {3 144 11 3}
do_test where-1.2.2 {
  count {SELECT x, y, w AS abc FROM t1 WHERE abc=11}
} {3 144 11 3}
do_test where-1.3.1 {
  count {SELECT x, y, w AS abc FROM t1 WHERE 11=w}
} {3 144 11 3}
do_test where-1.3.2 {
  count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
} {3 144 11 3}
do_test where-1.4.1 {
  count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
} {11 3 144 3}
do_test where-1.4.2 {
  set sqlite_query_plan
} {t1 i1w}
do_test where-1.4.3 {
  count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
} {11 3 144 3}
do_test where-1.4.4 {
  set sqlite_query_plan
} {t1 i1w}
do_test where-1.5 {
  count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 3}
do_test where-1.5.2 {
  set sqlite_query_plan
} {t1 i1w}
do_test where-1.6 {