SQLite

Check-in [d83e0230c0]
Login

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

Overview
Comment:Allow constant terms in the ORDER BY or GROUP BY clauses. Ticket #1768. (CVS 3173)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d83e0230c0c4909cb035e266beffc0967526d9c1
User & Date: drh 2006-04-11 14:16:21.000
Context
2006-04-16
12:05
Allocate enough memory for the worst-case UTF-16 to UTF-8 conversion. Ticket #1773. (CVS 3174) (check-in: 2a0120c0f0 user: drh tags: trunk)
2006-04-11
14:16
Allow constant terms in the ORDER BY or GROUP BY clauses. Ticket #1768. (CVS 3173) (check-in: d83e0230c0 user: drh tags: trunk)
2006-04-10
13:37
Follow-up to check-in (3164). Make sure SQLITE_NOMEM is returned after a memory allocation failure. It is not sufficent to return an "out of memory" error message. The return code needs to be SQLITE_NOMEM. (CVS 3172) (check-in: 9d95750e85 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
︙︙
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 C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.311 2006/04/07 13:50:37 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.








|








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 C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.312 2006/04/11 14:16:21 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
︙︙
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500

           "between 1 and %d", zType, iCol, pEList->nExpr);
        return 1;
      }
    }
    if( sqlite3ExprResolveNames(pNC, pE) ){
      return 1;
    }
    if( sqlite3ExprIsConstant(pE) ){
      sqlite3ErrorMsg(pParse,
          "%s BY terms must not be non-integer constants", zType);
      return 1;
    }
  }
  return 0;
}

/*
** This routine resolves any names used in the result set of the
** supplied SELECT statement. If the SELECT statement being resolved








<
<
<
<
<








2482
2483
2484
2485
2486
2487
2488





2489
2490
2491
2492
2493
2494
2495

           "between 1 and %d", zType, iCol, pEList->nExpr);
        return 1;
      }
    }
    if( sqlite3ExprResolveNames(pNC, pE) ){
      return 1;
    }





  }
  return 0;
}

/*
** This routine resolves any names used in the result set of the
** supplied SELECT statement. If the SELECT statement being resolved
︙︙
Changes to test/select1.test. 
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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.50 2006/03/26 01:21:23 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {














|








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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.51 2006/04/11 14:16:22 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
︙︙
278
279
280
281
282
283
284



285
286































287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321

  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 {misuse of aggregate function min()}}



do_test select1-4.5 {
  catchsql {































    SELECT f1 FROM test1 ORDER BY 8.4;
  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.6 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY '8.4';
  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.7.1 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY 'xyz';
  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.7.2 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY -8.4;
  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.7.3 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY +8.4;
  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.7.4 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY 4294967296; -- constant larger than 32 bits
  }
} {1 {ORDER BY terms must not be non-integer constants}}
do_test select1-4.8 {
  execsql {
    CREATE TABLE t5(a,b);
    INSERT INTO t5 VALUES(1,10);
    INSERT INTO t5 VALUES(2,9);
    SELECT * FROM t5 ORDER BY 1;
  }








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

|

|
|

<
<
|
<
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<








278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327


328

329
















330
331
332
333
334
335
336

  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 {misuse of aggregate function min()}}

# The restriction not allowing constants in the ORDER BY clause
# has been removed.  See ticket #1768
#do_test select1-4.5 {
#  catchsql {
#    SELECT f1 FROM test1 ORDER BY 8.4;
#  }
#} {1 {ORDER BY terms must not be non-integer constants}}
#do_test select1-4.6 {
#  catchsql {
#    SELECT f1 FROM test1 ORDER BY '8.4';
#  }
#} {1 {ORDER BY terms must not be non-integer constants}}
#do_test select1-4.7.1 {
#  catchsql {
#    SELECT f1 FROM test1 ORDER BY 'xyz';
#  }
#} {1 {ORDER BY terms must not be non-integer constants}}
#do_test select1-4.7.2 {
#  catchsql {
#    SELECT f1 FROM test1 ORDER BY -8.4;
#  }
#} {1 {ORDER BY terms must not be non-integer constants}}
#do_test select1-4.7.3 {
#  catchsql {
#    SELECT f1 FROM test1 ORDER BY +8.4;
#  }
#} {1 {ORDER BY terms must not be non-integer constants}}
#do_test select1-4.7.4 {
#  catchsql {
#    SELECT f1 FROM test1 ORDER BY 4294967296; -- constant larger than 32 bits
#  }
#} {1 {ORDER BY terms must not be non-integer constants}}

do_test select1-4.5 {
  execsql {
    SELECT f1 FROM test1 ORDER BY 8.4
  }
} {11 33}
do_test select1-4.6 {
  execsql {
    SELECT f1 FROM test1 ORDER BY '8.4'
  }


} {11 33}


















do_test select1-4.8 {
  execsql {
    CREATE TABLE t5(a,b);
    INSERT INTO t5 VALUES(1,10);
    INSERT INTO t5 VALUES(2,9);
    SELECT * FROM t5 ORDER BY 1;
  }
︙︙
Changes to test/select3.test.
︙︙
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

#    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.18 2005/11/14 22:29:06 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {








|








8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

#    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.19 2006/04/11 14:16:22 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
︙︙
80
81
82
83
84
85
86
87
88
89
90

91
92
93
94
95
96
97
98

  }
} {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 '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 {








|
|
|
<
>
|








80
81
82
83
84
85
86
87
88
89

90
91
92
93
94
95
96
97
98

  }
} {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 '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 {
︙︙