/ Check-in [d83e0230]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:d83e0230c0c4909cb035e266beffc0967526d9c1
User & Date: drh 2006-04-11 14:16:21
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: 2a0120c0 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: d83e0230 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: 9d95750e user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
**    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.
................................................................................
           "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







|







 







<
<
<
<
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2482
2483
2484
2485
2486
2487
2488





2489
2490
2491
2492
2493
2494
2495
**    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.
................................................................................
           "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.

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







|







 







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







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







|







 







|
|
|
<
>
|







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