/ Check-in [035984a5]
Login

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

Overview
Comment:Constant ORDER BY or GROUP BY expressions are an error. (CVS 352)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:035984a5b00b4a1a6505405f40b15c7695283c0a
User & Date: drh 2002-01-22 14:11:29
Context
2002-01-22
14:15
Version 2.2.4 (CVS 449) check-in: 16712dae user: drh tags: trunk
14:11
Constant ORDER BY or GROUP BY expressions are an error. (CVS 352) check-in: 035984a5 user: drh tags: trunk
12:39
Fix a bug in the -separator command-line option. (CVS 351) check-in: 593c986f 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
..
31
32
33
34
35
36
37
38
39
40
41
42
43




44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
...
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
**    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.38 2002/01/22 03:13:42 drh Exp $
*/
#include "sqliteInt.h"


/*
** Recursively delete an expression tree.
*/
................................................................................
  sqliteFree(p);
}

/*
** Walk an expression tree.  Return 1 if the expression is constant
** and 0 if it involves variables.
*/
static int isConstant(Expr *p){
  switch( p->op ){
    case TK_ID:
    case TK_COLUMN:
    case TK_DOT:
      return 0;




    default: {
      if( p->pLeft && !isConstant(p->pLeft) ) return 0;
      if( p->pRight && !isConstant(p->pRight) ) return 0;
      if( p->pList ){
        int i;
        for(i=0; i<p->pList->nExpr; i++){
          if( !isConstant(p->pList->a[i].pExpr) ) return 0;
        }
      }
      break;
    }
  }
  return 1;
}

/*
** Walk the expression tree and process operators of the form:
**
**       expr IN (SELECT ...)
**
................................................................................
        **
        ** Create a set to put the exprlist values in.  The Set id is stored
        ** in iTable.
        */
        int i, iSet;
        for(i=0; i<pExpr->pList->nExpr; i++){
          Expr *pE2 = pExpr->pList->a[i].pExpr;
          if( !isConstant(pE2) ){
            sqliteSetString(&pParse->zErrMsg,
              "right-hand side of IN operator must be constant", 0);
            pParse->nErr++;
            return 1;
          }
          if( sqliteExprCheck(pParse, pE2, 0, 0) ){
            return 1;







|







 







|





>
>
>
>

|
|



|


|


|







 







|







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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
...
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
**    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.39 2002/01/22 14:11:29 drh Exp $
*/
#include "sqliteInt.h"


/*
** Recursively delete an expression tree.
*/
................................................................................
  sqliteFree(p);
}

/*
** Walk an expression tree.  Return 1 if the expression is constant
** and 0 if it involves variables.
*/
int sqliteExprIsConstant(Expr *p){
  switch( p->op ){
    case TK_ID:
    case TK_COLUMN:
    case TK_DOT:
      return 0;
    case TK_INTEGER:
    case TK_FLOAT:
    case TK_STRING:
      return 1;
    default: {
      if( p->pLeft && !sqliteExprIsConstant(p->pLeft) ) return 0;
      if( p->pRight && !sqliteExprIsConstant(p->pRight) ) return 0;
      if( p->pList ){
        int i;
        for(i=0; i<p->pList->nExpr; i++){
          if( !sqliteExprIsConstant(p->pList->a[i].pExpr) ) return 0;
        }
      }
      return p->pLeft!=0 || p->pRight!=0 || (p->pList && p->pList->nExpr>0);
    }
  }
  return 0;
}

/*
** Walk the expression tree and process operators of the form:
**
**       expr IN (SELECT ...)
**
................................................................................
        **
        ** Create a set to put the exprlist values in.  The Set id is stored
        ** in iTable.
        */
        int i, iSet;
        for(i=0; i<pExpr->pList->nExpr; i++){
          Expr *pE2 = pExpr->pList->a[i].pExpr;
          if( !sqliteExprIsConstant(pE2) ){
            sqliteSetString(&pParse->zErrMsg,
              "right-hand side of IN operator must be constant", 0);
            pParse->nErr++;
            return 1;
          }
          if( sqliteExprCheck(pParse, pE2, 0, 0) ){
            return 1;

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
812
813
814
815
816
817
818






819
820
821
822
823
824
825
826
827
828
829






830
831
832
833
834
835
836
**    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.54 2002/01/22 03:13:42 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      return 1;
    }
  }
  if( pOrderBy ){
    for(i=0; i<pOrderBy->nExpr; i++){
      Expr *pE = pOrderBy->a[i].pExpr;






      if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
        return 1;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        return 1;
      }
    }
  }
  if( pGroupBy ){
    for(i=0; i<pGroupBy->nExpr; i++){
      Expr *pE = pGroupBy->a[i].pExpr;






      if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
        return 1;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        return 1;
      }
    }







|







 







>
>
>
>
>
>











>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
**    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.55 2002/01/22 14:11:29 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
    if( sqliteExprCheck(pParse, pWhere, 0, 0) ){
      return 1;
    }
  }
  if( pOrderBy ){
    for(i=0; i<pOrderBy->nExpr; i++){
      Expr *pE = pOrderBy->a[i].pExpr;
      if( sqliteExprIsConstant(pE) ){
        sqliteSetString(&pParse->zErrMsg, 
             "ORDER BY expressions should not be constant", 0);
        pParse->nErr++;
        return 1;
      }
      if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
        return 1;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        return 1;
      }
    }
  }
  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++;
        return 1;
      }
      if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){
        return 1;
      }
      if( sqliteExprCheck(pParse, pE, isAgg, 0) ){
        return 1;
      }
    }

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
524
525
526
527
528
529
530

**    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.78 2002/01/22 03:13:42 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
Vdbe *sqliteGetVdbe(Parse*);
int sqliteRandomByte(void);
int sqliteRandomInteger(void);
void sqliteBeginTransaction(Parse*);
void sqliteCommitTransaction(Parse*);
void sqliteRollbackTransaction(Parse*);
char *sqlite_mprintf(const char *, ...);








|







 







>
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
524
525
526
527
528
529
530
531
**    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.79 2002/01/22 14:11:29 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
Vdbe *sqliteGetVdbe(Parse*);
int sqliteRandomByte(void);
int sqliteRandomInteger(void);
void sqliteBeginTransaction(Parse*);
void sqliteCommitTransaction(Parse*);
void sqliteRollbackTransaction(Parse*);
char *sqlite_mprintf(const char *, ...);
int sqliteExprIsConstant(Expr*);

Changes to src/util.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
547
548
549
550
551
552
553


554
555
556
557
558
559
560
**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.35 2002/01/14 09:28:20 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** If malloc() ever fails, this global variable gets set to 1.
................................................................................
    }while( ca!=0 );
  }else{
    map = UpperToLower;
    do{
      if( (ca=map[*a++])!=(cb=map[*b++]) ) break;
      cclass = stateMachine[cclass*N_CHAR_CLASS + charClass[ca]];
    }while( ca!=0 );


  }
  switch( cclass ){
    case 0:
    case 1: {
      if( isdigit(ca) && isdigit(cb) ){
        cclass = 2;
      }







|







 







>
>







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.36 2002/01/22 14:11:30 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** If malloc() ever fails, this global variable gets set to 1.
................................................................................
    }while( ca!=0 );
  }else{
    map = UpperToLower;
    do{
      if( (ca=map[*a++])!=(cb=map[*b++]) ) break;
      cclass = stateMachine[cclass*N_CHAR_CLASS + charClass[ca]];
    }while( ca!=0 );
    if( ca>='[' && ca<='`' ) cb = b[-1];
    if( cb>='[' && cb<='`' ) ca = a[-1];
  }
  switch( cclass ){
    case 0:
    case 1: {
      if( isdigit(ca) && isdigit(cb) ){
        cclass = 2;
      }

Changes to test/select1.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
238
239
240
241
242
243
244





245
246
247
248
249
250
251
#    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 SELECT statement.
#
# $Id: select1.test,v 1.17 2002/01/22 03:13:43 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg]
  lappend v $msg
} {0 {11 33}}
do_test select1-4.4 {
  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg]
  lappend v $msg
} {1 {too few arguments to function min()}}






# ORDER BY ignored on an aggregate query
#
do_test select1-5.1 {
  set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 33}







|







 







>
>
>
>
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
#    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 SELECT statement.
#
# $Id: select1.test,v 1.18 2002/01/22 14:11:30 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg]
  lappend v $msg
} {0 {11 33}}
do_test select1-4.4 {
  set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg]
  lappend v $msg
} {1 {too few arguments to function min()}}
do_test select1-4.5 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY 8.4;
  }
} {1 {ORDER BY expressions should not be constant}}

# ORDER BY ignored on an aggregate query
#
do_test select1-5.1 {
  set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg]
  lappend v $msg
} {0 33}

Changes to test/select3.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
80
81
82
83
84
85
86





87
88
89
90
91
92
93
#    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.4 2002/01/22 03:13:43 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
  }
} {1 1 3 1 5 2 7 4 9 8 11 15}
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}






# 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
..
80
81
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.5 2002/01/22 14:11:30 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
  }
} {1 1 3 1 5 2 7 4 9 8 11 15}
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}}

Changes to test/sort.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
116
117
118
119
120
121
122
123


































124
#    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 CREATE TABLE statement.
#
# $Id: sort.test,v 1.3 2001/09/16 00:13:28 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
................................................................................
} {x-2_ x-2b x-2.15 x-3.141592653 x-123 x-4221 x1.6 x11}
do_test sort-2.3 {
  execsql {
    UPDATE t1 SET v='x ' || (-1.3+0.01*n);
    SELECT v FROM t1 ORDER BY v;
  }
} {{x -1.29} {x -1.28} {x -1.27} {x -1.26} {x -1.25} {x -1.24} {x -1.23} {x -1.22}}



































finish_test







|







 








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

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
#    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 CREATE TABLE statement.
#
# $Id: sort.test,v 1.4 2002/01/22 14:11:30 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
................................................................................
} {x-2_ x-2b x-2.15 x-3.141592653 x-123 x-4221 x1.6 x11}
do_test sort-2.3 {
  execsql {
    UPDATE t1 SET v='x ' || (-1.3+0.01*n);
    SELECT v FROM t1 ORDER BY v;
  }
} {{x -1.29} {x -1.28} {x -1.27} {x -1.26} {x -1.25} {x -1.24} {x -1.23} {x -1.22}}

# This is a bug fix for 2.2.4.
# Strings are normally mapped to upper-case for a caseless comparison.
# But this can cause problems for characters in between 'Z' and 'a'.
#
do_test sort-3.1 {
  execsql {
    CREATE TABLE t2(a,b);
    INSERT INTO t2 VALUES('AGLIENTU',1);
    INSERT INTO t2 VALUES('AGLIE`',2);
    INSERT INTO t2 VALUES('AGNA',3);
    SELECT a, b FROM t2 ORDER BY a;
  }
} {AGLIENTU 1 AGLIE` 2 AGNA 3}
do_test sort-3.2 {
  execsql {
    SELECT a, b FROM t2 ORDER BY a DESC;
  }
} {AGNA 3 AGLIE` 2 AGLIENTU 1}
do_test sort-3.3 {
  execsql {
    DELETE FROM t2;
    INSERT INTO t2 VALUES('aglientu',1);
    INSERT INTO t2 VALUES('aglie`',2);
    INSERT INTO t2 VALUES('agna',3);
    SELECT a, b FROM t2 ORDER BY a;
  }
} {aglie` 2 aglientu 1 agna 3}
do_test sort-3.4 {
  execsql {
    SELECT a, b FROM t2 ORDER BY a DESC;
  }
} {agna 3 aglientu 1 aglie` 2}


finish_test

Changes to www/changes.tcl.

13
14
15
16
17
18
19
20
21
22
23
24
25



26
27
28
29
30
31
32


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2002 Jan ?? (2.2.4)} {
<li>The label to the right of an AS in the column list of a SELECT can now
    be used as part of an expression in the WHERE, ORDER BY, GROUP BY, and/or
    HAVING clauses.</li>
<li>Fix a bug in the <b>-separator</b> command-line option to the <b>sqlite</b>
    command.</li>



}

chng {2002 Jan 16 (2.2.3)} {
<li>Fix warning messages in VC++ 7.0.  (Patches from nicolas352001)</li>
<li>Make the library thread-safe.  (The code is there and appears to work
    but has not been stressed.)</li>
<li>Added the new <b>sqlite_last_insert_rowid()</b> API function.</li>







|





>
>
>







13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35


proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2002 Jan 22 (2.2.4)} {
<li>The label to the right of an AS in the column list of a SELECT can now
    be used as part of an expression in the WHERE, ORDER BY, GROUP BY, and/or
    HAVING clauses.</li>
<li>Fix a bug in the <b>-separator</b> command-line option to the <b>sqlite</b>
    command.</li>
<li>Fix a problem with the sort order when comparing upper-case strings against
    characters greater than 'Z' but less than 'a'.</li>
<li>Report an error if an ORDER BY or GROUP BY expression is constant.</li>
}

chng {2002 Jan 16 (2.2.3)} {
<li>Fix warning messages in VC++ 7.0.  (Patches from nicolas352001)</li>
<li>Make the library thread-safe.  (The code is there and appears to work
    but has not been stressed.)</li>
<li>Added the new <b>sqlite_last_insert_rowid()</b> API function.</li>