/ Check-in [7acbf84b]
Login

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

Overview
Comment:Enhance the ORDER BY clause so that an integer term means to sort by the corresponding column. (CVS 602)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7acbf84b492202d8b5a05276a95b475027eb5f58
User & Date: drh 2002-06-02 16:09:02
Context
2002-06-02
18:19
Add the ability to parse FOREIGN KEYs. Foreign keys are still ignored, but at least they now do not cause a syntax error. (CVS 603) check-in: 6fdcee3c user: drh tags: trunk
16:09
Enhance the ORDER BY clause so that an integer term means to sort by the corresponding column. (CVS 602) check-in: 7acbf84b user: drh tags: trunk
2002-06-01
21:41
Multiplying NULL by zero gives NULL, not zero. I misread the test data and coded it wrong. This check-in fixes the problem. (CVS 601) check-in: df9cc852 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
...
330
331
332
333
334
335
336



































337
338
339
340
341
342
343
**    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.66 2002/05/31 15:51:25 drh Exp $
*/
#include "sqliteInt.h"


/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
        }
      }
      return p->pLeft!=0 || p->pRight!=0 || (p->pList && p->pList->nExpr>0);
    }
  }
  return 0;
}




































/*
** Return TRUE if the given string is a row-id column name.
*/
static int sqliteIsRowid(const char *z){
  if( sqliteStrICmp(z, "_ROWID_")==0 ) return 1;
  if( sqliteStrICmp(z, "ROWID")==0 ) return 1;







|







 







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







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
**    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.67 2002/06/02 16:09:02 drh Exp $
*/
#include "sqliteInt.h"


/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
        }
      }
      return p->pLeft!=0 || p->pRight!=0 || (p->pList && p->pList->nExpr>0);
    }
  }
  return 0;
}

/*
** If the given expression codes a constant integer, return 1 and put
** the value of the integer in *pValue.  If the expression is not an
** integer, return 0 and leave *pValue unchanged.
*/
int sqliteExprIsInteger(Expr *p, int *pValue){
  switch( p->op ){
    case TK_INTEGER: {
      *pValue = atoi(p->token.z);
      return 1;
    }
    case TK_STRING: {
      char *z = p->token.z;
      int n = p->token.n;
      if( n>0 && z=='-' ){ z++; n--; }
      while( n>0 && *z && isdigit(*z) ){ z++; n--; }
      if( n==0 ){
        *pValue = atoi(p->token.z);
        return 1;
      }
      break;
    }
    case TK_UMINUS: {
      int v;
      if( sqliteExprIsInteger(p->pLeft, &v) ){
        *pValue = -v;
        return 1;
      }
      break;
    }
    default: break;
  }
  return 0;
}

/*
** Return TRUE if the given string is a row-id column name.
*/
static int sqliteIsRowid(const char *z){
  if( sqliteStrICmp(z, "_ROWID_")==0 ) return 1;
  if( sqliteStrICmp(z, "ROWID")==0 ) return 1;

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
...
808
809
810
811
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
....
1516
1517
1518
1519
1520
1521
1522


1523
1524

1525
1526










1527
1528
1529
1530
1531
1532
1533
**    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.89 2002/05/31 15:51:25 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
** Any entry that does not match is flagged as an error.  The number
** of errors is returned.
*/
static int matchOrderbyToColumn(
  Parse *pParse,          /* A place to leave error messages */
  Select *pSelect,        /* Match to result columns of this SELECT */
  ExprList *pOrderBy,     /* The ORDER BY values to match against columns */
  int iTable,             /* Insert this this value in iTable */
  int mustComplete        /* If TRUE all ORDER BYs must match */
){
  int nErr = 0;
  int i, j;
  ExprList *pEList;

  if( pSelect==0 || pOrderBy==0 ) return 1;
................................................................................
    if( matchOrderbyToColumn(pParse, pSelect->pPrior, pOrderBy, iTable, 0) ){
      return 1;
    }
  }
  pEList = pSelect->pEList;
  for(i=0; i<pOrderBy->nExpr; i++){
    Expr *pE = pOrderBy->a[i].pExpr;
    int match = 0;
    if( pOrderBy->a[i].done ) continue;












    for(j=0; j<pEList->nExpr; j++){
      if( pEList->a[j].zName && (pE->op==TK_ID || pE->op==TK_STRING) ){
        char *zName, *zLabel;
        zName = pEList->a[j].zName;
        assert( pE->token.z );
        zLabel = sqliteStrNDup(pE->token.z, pE->token.n);
        sqliteDequote(zLabel);
        if( sqliteStrICmp(zName, zLabel)==0 ){ 
          match = 1; 
        }
        sqliteFree(zLabel);
      }
      if( match==0 && sqliteExprCompare(pE, pEList->a[j].pExpr) ){
        match = 1;
      }
      if( match ){


        pE->op = TK_COLUMN;
        pE->iColumn = j;
        pE->iTable = iTable;
        pOrderBy->a[i].done = 1;
        break;
      }
    }
    if( !match && mustComplete ){
      char zBuf[30];
      sprintf(zBuf,"%d",i+1);
      sqliteSetString(&pParse->zErrMsg, "ORDER BY term number ", zBuf, 
        " does not match any result column", 0);
      pParse->nErr++;
      nErr++;
      break;
................................................................................
      goto select_end;
    }
  }
  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++;
        goto select_end;










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







|







 







|







 







|

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







|



|
|

<
>
>
|
|
|
|
<
|
<
|







 







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







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
...
808
809
810
811
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
849

850

851
852
853
854
855
856
857
858
....
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536

1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
**    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.90 2002/06/02 16:09:02 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
................................................................................
** Any entry that does not match is flagged as an error.  The number
** of errors is returned.
*/
static int matchOrderbyToColumn(
  Parse *pParse,          /* A place to leave error messages */
  Select *pSelect,        /* Match to result columns of this SELECT */
  ExprList *pOrderBy,     /* The ORDER BY values to match against columns */
  int iTable,             /* Insert this value in iTable */
  int mustComplete        /* If TRUE all ORDER BYs must match */
){
  int nErr = 0;
  int i, j;
  ExprList *pEList;

  if( pSelect==0 || pOrderBy==0 ) return 1;
................................................................................
    if( matchOrderbyToColumn(pParse, pSelect->pPrior, pOrderBy, iTable, 0) ){
      return 1;
    }
  }
  pEList = pSelect->pEList;
  for(i=0; i<pOrderBy->nExpr; i++){
    Expr *pE = pOrderBy->a[i].pExpr;
    int iCol = -1;
    if( pOrderBy->a[i].done ) continue;
    if( sqliteExprIsInteger(pE, &iCol) ){
      if( iCol<=0 || iCol>pEList->nExpr ){
        char zBuf[200];
        sprintf(zBuf,"ORDER BY position %d should be between 1 and %d",
           iCol, pEList->nExpr);
        sqliteSetString(&pParse->zErrMsg, zBuf, 0);
        pParse->nErr++;
        nErr++;
        break;
      }
      iCol--;
    }
    for(j=0; iCol<0 && j<pEList->nExpr; j++){
      if( pEList->a[j].zName && (pE->op==TK_ID || pE->op==TK_STRING) ){
        char *zName, *zLabel;
        zName = pEList->a[j].zName;
        assert( pE->token.z );
        zLabel = sqliteStrNDup(pE->token.z, pE->token.n);
        sqliteDequote(zLabel);
        if( sqliteStrICmp(zName, zLabel)==0 ){ 
          iCol = j;
        }
        sqliteFree(zLabel);
      }
      if( iCol<0 && sqliteExprCompare(pE, pEList->a[j].pExpr) ){
        iCol = j;
      }

    }
    if( iCol>=0 ){
      pE->op = TK_COLUMN;
      pE->iColumn = iCol;
      pE->iTable = iTable;
      pOrderBy->a[i].done = 1;

    }

    if( iCol<0 && mustComplete ){
      char zBuf[30];
      sprintf(zBuf,"%d",i+1);
      sqliteSetString(&pParse->zErrMsg, "ORDER BY term number ", zBuf, 
        " does not match any result column", 0);
      pParse->nErr++;
      nErr++;
      break;
................................................................................
      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;
      }

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
837
838
839
840
841
842
843

844
845
846
847
848
849
850
**    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.118 2002/05/27 12:24:49 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
int sqliteRandomByte(void);
int sqliteRandomInteger(void);
void sqliteBeginTransaction(Parse*, int);
void sqliteCommitTransaction(Parse*);
void sqliteRollbackTransaction(Parse*);
char *sqlite_mprintf(const char *, ...);
int sqliteExprIsConstant(Expr*);

void sqliteGenerateRowDelete(Vdbe*, Table*, int, int);
void sqliteGenerateRowIndexDelete(Vdbe*, Table*, int, char*);
void sqliteGenerateConstraintChecks(Parse*,Table*,int,char*,int,int,int,int);
void sqliteCompleteInsertion(Parse*, Table*, int, char*, int, int);
void sqliteBeginWriteOperation(Parse*, int);
void sqliteEndWriteOperation(Parse*);
void sqliteExprMoveStrings(Expr*, int);







|







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
**    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.119 2002/06/02 16:09:02 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
................................................................................
int sqliteRandomByte(void);
int sqliteRandomInteger(void);
void sqliteBeginTransaction(Parse*, int);
void sqliteCommitTransaction(Parse*);
void sqliteRollbackTransaction(Parse*);
char *sqlite_mprintf(const char *, ...);
int sqliteExprIsConstant(Expr*);
int sqliteExprIsInteger(Expr*, int*);
void sqliteGenerateRowDelete(Vdbe*, Table*, int, int);
void sqliteGenerateRowIndexDelete(Vdbe*, Table*, int, char*);
void sqliteGenerateConstraintChecks(Parse*,Table*,int,char*,int,int,int,int);
void sqliteCompleteInsertion(Parse*, Table*, int, char*, int, int);
void sqliteBeginWriteOperation(Parse*, int);
void sqliteEndWriteOperation(Parse*);
void sqliteExprMoveStrings(Expr*, int);

Changes to test/select1.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
281
282
283
284
285
286
287
288













































289
290
291
292
293
294
295
#    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.29 2002/05/29 23:22: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)}} 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 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}







|







 







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







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
337
338
339
340
#    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.30 2002/06/02 16:09:03 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)}} 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 {
  catchsql {
    SELECT f1 FROM test1 ORDER BY 'xyz';
  }
} {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;
  }
} {1 10 2 9}
do_test select1-4.9 {
  execsql {
    SELECT * FROM t5 ORDER BY 2;
  }
} {2 9 1 10}
do_test select1-4.10 {
  catchsql {
    SELECT * FROM t5 ORDER BY 3;
  }
} {1 {ORDER BY column number 3 out of range - should be between 1 and 2}}
do_test select1-4.11 {
  execsql {
    INSERT INTO t5 VALUES(3,10);
    SELECT * FROM t5 ORDER BY 2, 1 DESC;
  }
} {2 9 3 10 1 10}
do_test select1-4.12 {
  execsql {
    SELECT * FROM t5 ORDER BY 1 DESC, b;
  }
} {3 10 2 9 1 10}
do_test select1-4.13 {
  execsql {
    SELECT * FROM t5 ORDER BY b DESC, 1;
  }
} {1 10 3 10 2 9}


# 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/select4.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
205
206
207
208
209
210
211
















































212
213
214
215
216
217
218
#    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 UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.9 2002/05/31 15:51:26 drh Exp $

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

# Build some test data
#
set fd [open data1.txt w]
................................................................................
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY n;
  }} msg]
  lappend v $msg
} {0 {0 1 2 3 4 5 5 6 7 8}}
















































do_test select4-5.3 {
  set v [catch {execsql {
    SELECT DISTINCT log, n FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }} msg]







|







 







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







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
#    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 UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.10 2002/06/02 16:09:03 drh Exp $

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

# Build some test data
#
set fd [open data1.txt w]
................................................................................
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY n;
  }} msg]
  lappend v $msg
} {0 {0 1 2 3 4 5 5 6 7 8}}
do_test select4-5.2f {
  catchsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {0 {0 1 2 3 4 5 5 6 7 8}}
do_test select4-5.2g {
  catchsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY 1;
  }
} {0 {0 1 2 3 4 5 5 6 7 8}}
do_test select4-5.2h {
  catchsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY 2;
  }
} {1 {ORDER BY position 2 should be between 1 and 1}}
do_test select4-5.2i {
  catchsql {
    SELECT DISTINCT 1, log FROM t1
    UNION ALL
    SELECT 2, n FROM t1 WHERE log=3
    ORDER BY 2, 1;
  }
} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
do_test select4-5.2j {
  catchsql {
    SELECT DISTINCT 1, log FROM t1
    UNION ALL
    SELECT 2, n FROM t1 WHERE log=3
    ORDER BY 1, 2 DESC;
  }
} {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}
do_test select4-5.2k {
  catchsql {
    SELECT DISTINCT 1, log FROM t1
    UNION ALL
    SELECT 2, n FROM t1 WHERE log=3
    ORDER BY n, 1;
  }
} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
do_test select4-5.3 {
  set v [catch {execsql {
    SELECT DISTINCT log, n FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }} msg]