/ Check-in [187d9c40]
Login

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

Overview
Comment:Allow double-quoted strings as string constants in the IN operator. As a side-efffect, allow the GROUP BY clause to refer to columns by their integer column number. Ticket #237. (CVS 856)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:187d9c405891e543fc706f8ddb41f3966a842214
User & Date: drh 2003-01-31 17:16:37
Context
2003-01-31
17:21
The sqlite_exec() function now returns SQLITE_AUTH when authorization fails. Ticket #231. (CVS 857) check-in: d93c1aeb user: drh tags: trunk
17:16
Allow double-quoted strings as string constants in the IN operator. As a side-efffect, allow the GROUP BY clause to refer to columns by their integer column number. Ticket #237. (CVS 856) check-in: 187d9c40 user: drh tags: trunk
2003-01-29
22:58
Additional testing and bug fixing with the non-callback API. Updated the C/C++ interface document to describe the non-callback API. (CVS 855) check-in: af1e9299 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
...
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
**    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.87 2003/01/29 18:46:52 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
int sqliteExprIsConstant(Expr *p){
  switch( p->op ){
    case TK_ID:
    case TK_COLUMN:
    case TK_DOT:
      return 0;
    case TK_STRING:
      return p->token.z[0]=='\'';
    case TK_INTEGER:
    case TK_FLOAT:
      return 1;
    default: {
      if( p->pLeft && !sqliteExprIsConstant(p->pLeft) ) return 0;
      if( p->pRight && !sqliteExprIsConstant(p->pRight) ) return 0;
      if( p->pList ){







|







 







<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
297
298
299
300
301
302
303

304
305
306
307
308
309
310
**    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.88 2003/01/31 17:16:37 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
int sqliteExprIsConstant(Expr *p){
  switch( p->op ){
    case TK_ID:
    case TK_COLUMN:
    case TK_DOT:
      return 0;
    case TK_STRING:

    case TK_INTEGER:
    case TK_FLOAT:
      return 1;
    default: {
      if( p->pLeft && !sqliteExprIsConstant(p->pLeft) ) return 0;
      if( p->pRight && !sqliteExprIsConstant(p->pRight) ) return 0;
      if( p->pList ){

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2030
2031
2032
2033
2034
2035
2036

2037










2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052








2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067

2068
2069

2070
2071
2072





2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
**    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.124 2003/01/29 14:06:09 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
    }
    if( sqliteExprCheck(pParse, pHaving, 1, &isAgg) ){
      goto select_end;
    }
  }
  if( pOrderBy ){
    for(i=0; i<pOrderBy->nExpr; i++){

      Expr *pE = pOrderBy->a[i].pExpr;










      if( sqliteExprIsConstant(pE) ){
        int iCol;
        if( sqliteExprIsInteger(pE, &iCol)==0 ){
          sqliteSetString(&pParse->zErrMsg, 
               "ORDER BY terms must not be non-integer constants", 0);
          pParse->nErr++;
          goto select_end;
        }else if( iCol<=0 || iCol>pEList->nExpr ){
          char zBuf[2000];
          sprintf(zBuf,"ORDER BY column number %d out of range - should be "
             "between 1 and %d", iCol, pEList->nExpr);
          sqliteSetString(&pParse->zErrMsg, zBuf, 0);
          pParse->nErr++;
          goto select_end;
        }








        sqliteExprDelete(pE);
        pE = pOrderBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
      }
      if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pE) ){
        goto select_end;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        goto select_end;
      }
    }
  }
  if( pGroupBy ){
    for(i=0; i<pGroupBy->nExpr; i++){
      Expr *pE = pGroupBy->a[i].pExpr;
      if( sqliteExprIsConstant(pE) ){

        sqliteSetString(&pParse->zErrMsg, 
             "GROUP BY expressions should not be constant", 0);

        pParse->nErr++;
        goto select_end;
      }





      if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pE) ){
        goto select_end;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        goto select_end;
      }
    }
  }

  /* Check for the special case of a min() or max() function by itself
  ** in the result set.
  */







|







 







>

>
>
>
>
>
>
>
>
>
>

<













>
>
>
>
>
>
>
>

|







<
<
<
<
<

>
|
<
>
|
|
<
>
>
>
>
>
|
|
|
<
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049

2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079





2080
2081
2082

2083
2084
2085

2086
2087
2088
2089
2090
2091
2092
2093


2094
2095
2096
2097
2098
2099
2100
**    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.125 2003/01/31 17:16:37 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
    }
    if( sqliteExprCheck(pParse, pHaving, 1, &isAgg) ){
      goto select_end;
    }
  }
  if( pOrderBy ){
    for(i=0; i<pOrderBy->nExpr; i++){
      int iCol;
      Expr *pE = pOrderBy->a[i].pExpr;
      if( sqliteExprIsInteger(pE, &iCol) && iCol>0 && iCol<=pEList->nExpr ){
        sqliteExprDelete(pE);
        pE = pOrderBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
      }
      if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pE) ){
        goto select_end;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        goto select_end;
      }
      if( sqliteExprIsConstant(pE) ){

        if( sqliteExprIsInteger(pE, &iCol)==0 ){
          sqliteSetString(&pParse->zErrMsg, 
               "ORDER BY terms must not be non-integer constants", 0);
          pParse->nErr++;
          goto select_end;
        }else if( iCol<=0 || iCol>pEList->nExpr ){
          char zBuf[2000];
          sprintf(zBuf,"ORDER BY column number %d out of range - should be "
             "between 1 and %d", iCol, pEList->nExpr);
          sqliteSetString(&pParse->zErrMsg, zBuf, 0);
          pParse->nErr++;
          goto select_end;
        }
      }
    }
  }
  if( pGroupBy ){
    for(i=0; i<pGroupBy->nExpr; i++){
      int iCol;
      Expr *pE = pGroupBy->a[i].pExpr;
      if( sqliteExprIsInteger(pE, &iCol) && iCol>0 && iCol<=pEList->nExpr ){
        sqliteExprDelete(pE);
        pE = pGroupBy->a[i].pExpr = sqliteExprDup(pEList->a[iCol-1].pExpr);
      }
      if( sqliteExprResolveIds(pParse, base, pTabList, pEList, pE) ){
        goto select_end;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        goto select_end;
      }





      if( sqliteExprIsConstant(pE) ){
        if( sqliteExprIsInteger(pE, &iCol)==0 ){
          sqliteSetString(&pParse->zErrMsg, 

               "GROUP BY terms must not be non-integer constants", 0);
          pParse->nErr++;
          goto select_end;

        }else if( iCol<=0 || iCol>pEList->nExpr ){
          char zBuf[2000];
          sprintf(zBuf,"GROUP BY column number %d out of range - should be "
             "between 1 and %d", iCol, pEList->nExpr);
          sqliteSetString(&pParse->zErrMsg, zBuf, 0);
          pParse->nErr++;
          goto select_end;
        }


      }
    }
  }

  /* Check for the special case of a min() or max() function by itself
  ** in the result set.
  */

Changes to test/in.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
260
261
262
263
264
265
266












267
268
269
#    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 IN and BETWEEN operator.
#
# $Id: in.test,v 1.8 2002/10/30 22:42:59 drh Exp $

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

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
................................................................................
  }
} {}
do_test in-7.7 {
  execsql {
    SELECT a FROM ta WHERE a NOT IN ();
  }
} {1 2 3 4 6 8 10}














finish_test







|







 







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



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
#    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 IN and BETWEEN operator.
#
# $Id: in.test,v 1.9 2003/01/31 17:16:37 drh Exp $

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

# Generate the test data we will need for the first squences of tests.
#
do_test in-1.0 {
................................................................................
  }
} {}
do_test in-7.7 {
  execsql {
    SELECT a FROM ta WHERE a NOT IN ();
  }
} {1 2 3 4 6 8 10}

do_test in-8.1 {
  execsql {
    SELECT b FROM t1 WHERE a IN ('hello','there')
  }
} {world}
do_test in-8.2 {
  execsql {
    SELECT b FROM t1 WHERE a IN ("hello",'there')
  }
} {world}



finish_test

Changes to test/select3.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
82
83
84
85
86
87
88
89
90
91

























92
93
94
95
96
97
98
#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.7 2002/12/03 02:34:50 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
do_test select3-2.8 {
  execsql {
    SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y)
  }
} {11 15 9 8 7 4 5 2 3 1 1 1}
do_test select3-2.9 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 8 ORDER BY log;
  }
} {1 {GROUP BY expressions should not be constant}}


























# Cannot have a HAVING without a GROUP BY
#
do_test select3-3.1 {
  set v [catch {execsql {SELECT log, count(*) FROM t1 HAVING log>=4}} msg]
  lappend v $msg
} {1 {a GROUP BY clause is required before HAVING}}







|







 







|

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







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
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
111
112
113
114
115
116
117
118
119
120
121
122
123
#    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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.8 2003/01/31 17:16:37 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
do_test select3-2.8 {
  execsql {
    SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y)
  }
} {11 15 9 8 7 4 5 2 3 1 1 1}
do_test select3-2.9 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 'x' ORDER BY log;
  }
} {1 {GROUP BY terms must not be non-integer constants}}
do_test select3-2.10 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 0 ORDER BY log;
  }
} {1 {GROUP BY column number 0 out of range - should be between 1 and 2}}
do_test select3-2.11 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 3 ORDER BY log;
  }
} {1 {GROUP BY column number 3 out of range - should be between 1 and 2}}
do_test select3-2.12 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 1 ORDER BY log;
  }
} {0 {0 1 1 1 2 2 3 4 4 8 5 15}}
#do_test select3-2.13 {
#  catchsql {
#    SELECT log, count(*) FROM t1 GROUP BY 2 ORDER BY log;
#  }
#} {0 {0 1 1 1 2 2 3 4 4 8 5 15}}
#do_test select3-2.14 {
#  catchsql {
#    SELECT log, count(*) FROM t1 GROUP BY count(*) ORDER BY log;
#  }
#} {0 {0 1 1 1 2 2 3 4 4 8 5 15}}

# Cannot have a HAVING without a GROUP BY
#
do_test select3-3.1 {
  set v [catch {execsql {SELECT log, count(*) FROM t1 HAVING log>=4}} msg]
  lappend v $msg
} {1 {a GROUP BY clause is required before HAVING}}