/ Check-in [62a78d21]
Login

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

Overview
Comment:Better resolution of ORDER BY terms in compound queries. Candidate solution for ticket #2822. Needs more testing and documentation before going final. (CVS 4602)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 62a78d212c53a9cb1759d03134653a75f3a086b6
User & Date: drh 2007-12-08 21:10:20
Context
2007-12-10
05:03
Fix compilation/testing with OMIT_SUBQUERY defined. Ticket #2828. (CVS 4603) check-in: 0b34a186 user: danielk1977 tags: trunk
2007-12-08
21:10
Better resolution of ORDER BY terms in compound queries. Candidate solution for ticket #2822. Needs more testing and documentation before going final. (CVS 4602) check-in: 62a78d21 user: drh tags: trunk
18:01
Strengthen the tests for aggregate functions in GROUP BY clauses. Changes to test cases only. No changes to code. (CVS 4601) check-in: 4be8e676 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
....
1408
1409
1410
1411
1412
1413
1414









1415
1416
1417
1418
1419
1420
1421
....
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439

1440
1441
1442


1443

1444
1445
1446
1447
1448
1449
1450
1451


1452



1453

1454
1455
1456
1457
1458

1459
1460


1461
1462
1463
1464
1465
1466
1467
1468
1469
1470

1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483

1484

1485
1486
1487
1488
1489
1490



1491
1492
1493
1494






1495
1496

1497
1498
1499
1500
1501
1502
1503
....
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727


2728
2729
2730
2731
2732
2733
2734
**    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.363 2007/11/23 13:42:52 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  }
  if( db->mallocFailed ){
    rc = SQLITE_NOMEM;
  }
  return rc;
}










#ifndef SQLITE_OMIT_COMPOUND_SELECT
/*
** This routine associates entries in an ORDER BY expression list with
** columns in a result.  For each ORDER BY expression, the opcode of
** the top-level node is changed to TK_COLUMN and the iColumn value of
** the top-level node is filled in with column number and the iTable
** value of the top-level node is filled with iTable parameter.
................................................................................
** 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;
  sqlite3 *db = pParse->db;


  if( pSelect==0 || pOrderBy==0 ) return 1;
  if( mustComplete ){


    for(i=0; i<pOrderBy->nExpr; i++){ pOrderBy->a[i].done = 0; }

  }
  if( prepSelectStmt(pParse, pSelect) ){
    return 1;
  }
  if( pSelect->pPrior ){
    if( matchOrderbyToColumn(pParse, pSelect->pPrior, pOrderBy, iTable, 0) ){
      return 1;
    }


  }



  pEList = pSelect->pEList;

  for(i=0; i<pOrderBy->nExpr; i++){
    struct ExprList_item *pItem;
    Expr *pE = pOrderBy->a[i].pExpr;
    int iCol = -1;
    char *zLabel;


    if( pOrderBy->a[i].done ) continue;


    if( sqlite3ExprIsInteger(pE, &iCol) ){
      if( iCol<=0 || iCol>pEList->nExpr ){
        sqlite3ErrorMsg(pParse,
          "ORDER BY position %d should be between 1 and %d",
          iCol, pEList->nExpr);
        nErr++;
        break;
      }
      if( !mustComplete ) continue;
      iCol--;

    }
    if( iCol<0 && (zLabel = sqlite3NameFromToken(db, &pE->token))!=0 ){
      for(j=0, pItem=pEList->a; j<pEList->nExpr; j++, pItem++){
        char *zName;
        int isMatch;
        if( pItem->zName ){
          zName = sqlite3DbStrDup(db, pItem->zName);
        }else{
          zName = sqlite3NameFromToken(db, &pItem->pExpr->token);
        }
        isMatch = zName && sqlite3StrICmp(zName, zLabel)==0;
        sqlite3_free(zName);
        if( isMatch ){

          iCol = j;

          break;
        }
      }
      sqlite3_free(zLabel);
    }
    if( iCol>=0 ){



      pE->op = TK_COLUMN;
      pE->iColumn = iCol;
      pE->iTable = iTable;
      pE->iAgg = -1;






      pOrderBy->a[i].done = 1;
    }else if( mustComplete ){

      sqlite3ErrorMsg(pParse,
        "ORDER BY term number %d does not match any result column", i+1);
      nErr++;
      break;
    }
  }
  return nErr;  
................................................................................
  ** re-evaluated for each reference to it.
  */
  sNC.pEList = p->pEList;
  if( sqlite3ExprResolveNames(&sNC, p->pWhere) ||
     sqlite3ExprResolveNames(&sNC, p->pHaving) ){
    return SQLITE_ERROR;
  }
  if( p->pPrior==0 ){
    if( processOrderGroupBy(&sNC, p->pOrderBy, "ORDER") ||
        processOrderGroupBy(&sNC, pGroupBy, "GROUP") ){
      return SQLITE_ERROR;
    }


  }

  if( pParse->db->mallocFailed ){
    return SQLITE_NOMEM;
  }

  /* Make sure the GROUP BY clause does not contain aggregate functions.







|







 







>
>
>
>
>
>
>
>
>







 







|





>


<
>
>
|
>
|
<
<

|
|
|
|
>
>

>
>
>
|
>
|



|
>

|
>
>








|

>

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

<


>
>
>
>
>
>
|
<
>







 







<
|
<
|
|
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
....
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451

1452
1453
1454
1455
1456


1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491

1492
1493
1494
1495
1496
1497
1498




1499
1500
1501
1502
1503
1504

1505

1506
1507
1508
1509

1510
1511
1512
1513
1514
1515
1516
1517
1518

1519
1520
1521
1522
1523
1524
1525
1526
....
2739
2740
2741
2742
2743
2744
2745

2746

2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
**    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.364 2007/12/08 21:10:20 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  }
  if( db->mallocFailed ){
    rc = SQLITE_NOMEM;
  }
  return rc;
}

/*
** During the process of matching ORDER BY terms to columns of the 
** result set, the Exprlist.a[].done flag can be set to one of the
** following values:
*/
#define ORDERBY_MATCH_NONE     0   /* No match found */
#define ORDERBY_MATCH_PARTIAL  1   /* A good match, but not perfect */
#define ORDERBY_MATCH_EXACT    2   /* An exact match seen */

#ifndef SQLITE_OMIT_COMPOUND_SELECT
/*
** This routine associates entries in an ORDER BY expression list with
** columns in a result.  For each ORDER BY expression, the opcode of
** the top-level node is changed to TK_COLUMN and the iColumn value of
** the top-level node is filled in with column number and the iTable
** value of the top-level node is filled with iTable parameter.
................................................................................
** 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 rightMost           /* TRUE for outermost recursive invocation */
){
  int nErr = 0;
  int i, j;
  ExprList *pEList;
  sqlite3 *db = pParse->db;
  NameContext nc;

  if( pSelect==0 || pOrderBy==0 ) return 1;

  if( rightMost ){
    assert( pSelect->pOrderBy==pOrderBy );
    for(i=0; i<pOrderBy->nExpr; i++){
      pOrderBy->a[i].done = ORDERBY_MATCH_NONE;
    }


  }
  if( pSelect->pPrior
      && matchOrderbyToColumn(pParse, pSelect->pPrior, pOrderBy, iTable, 0) ){
    return 1;
  }
  if( sqlite3SelectResolve(pParse, pSelect, 0) ){
    return 1;
  }
  memset(&nc, 0, sizeof(nc));
  nc.pParse = pParse;
  nc.pSrcList = pSelect->pSrc;
  nc.pEList = pEList = pSelect->pEList;
  nc.allowAgg = 1;
  for(i=0; nErr==0 && i<pOrderBy->nExpr; i++){
    struct ExprList_item *pItem;
    Expr *pE = pOrderBy->a[i].pExpr;
    int iCol = -1;
    int match = ORDERBY_MATCH_NONE;
    Expr *pDup;

    if( pOrderBy->a[i].done==ORDERBY_MATCH_EXACT ){
      continue;
    }
    if( sqlite3ExprIsInteger(pE, &iCol) ){
      if( iCol<=0 || iCol>pEList->nExpr ){
        sqlite3ErrorMsg(pParse,
          "ORDER BY position %d should be between 1 and %d",
          iCol, pEList->nExpr);
        nErr++;
        break;
      }
      if( !rightMost ) continue;
      iCol--;
      match = ORDERBY_MATCH_EXACT;
    }

    if( !match && pParse->nErr==0 && (pDup = sqlite3ExprDup(db, pE))!=0 ){
      nc.nErr = 0;
      assert( pParse->zErrMsg==0 );
      if( sqlite3ExprResolveNames(&nc, pDup) ){
        sqlite3ErrorClear(pParse);
      }else{
        for(j=0, pItem=pEList->a; j<pEList->nExpr; j++, pItem++){




          if( sqlite3ExprCompare(pItem->pExpr, pDup) ){
            iCol = j;
            match = ORDERBY_MATCH_PARTIAL;
            break;
          }
        }

      }

      sqlite3ExprDelete(pDup);
    }
    if( match ){
      pE->op = TK_COLUMN;

      pE->iTable = iTable;
      pE->iAgg = -1;
      if( pOrderBy->a[i].done!=ORDERBY_MATCH_NONE && pE->iColumn!=iCol ){
        sqlite3ErrorMsg(pParse,
          "ORDER BY term number %d is ambiguous", i+1);
        nErr++;
      }
      pE->iColumn = iCol;
      pOrderBy->a[i].done = match;

    }else if( rightMost && pOrderBy->a[i].done==ORDERBY_MATCH_NONE ){
      sqlite3ErrorMsg(pParse,
        "ORDER BY term number %d does not match any result column", i+1);
      nErr++;
      break;
    }
  }
  return nErr;  
................................................................................
  ** re-evaluated for each reference to it.
  */
  sNC.pEList = p->pEList;
  if( sqlite3ExprResolveNames(&sNC, p->pWhere) ||
     sqlite3ExprResolveNames(&sNC, p->pHaving) ){
    return SQLITE_ERROR;
  }

  if( p->pPrior==0 && processOrderGroupBy(&sNC, p->pOrderBy, "ORDER") ){

    return SQLITE_ERROR;
  }
  if( processOrderGroupBy(&sNC, pGroupBy, "GROUP") ){
    return SQLITE_ERROR;
  }

  if( pParse->db->mallocFailed ){
    return SQLITE_NOMEM;
  }

  /* Make sure the GROUP BY clause does not contain aggregate functions.

Changes to test/select1.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
#    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.54 2007/07/23 22:51:15 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
    ORDER BY f2;
  }} msg]
  lappend v $msg
} {0 {f1 11 f1 22 f1 33 f1 44}}
do_test select1-6.11 {
  set v [catch {execsql2 {
    SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
    ORDER BY f2+100;
  }} msg]
  lappend v $msg
} {1 {ORDER BY term number 1 does not match any result column}}

# Ticket #2296
do_test select1-6.20 {
   execsql {







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
#    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.55 2007/12/08 21:10:20 drh Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
................................................................................
    ORDER BY f2;
  }} msg]
  lappend v $msg
} {0 {f1 11 f1 22 f1 33 f1 44}}
do_test select1-6.11 {
  set v [catch {execsql2 {
    SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
    ORDER BY f2+101;
  }} msg]
  lappend v $msg
} {1 {ORDER BY term number 1 does not match any result column}}

# Ticket #2296
do_test select1-6.20 {
   execsql {

Changes to test/select4.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
#    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.20 2006/06/20 11:01:09 danielk1977 Exp $

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

# Most tests in this file depend on compound-select. But there are a couple
# right at the end that test DISTINCT, so we cannot omit the entire file.
#
................................................................................
  lappend v $msg
} {0 {0 1 2 3 4 5 5 6 7 8}}
do_test select4-5.2b {
  set v [catch {execsql {
    SELECT DISTINCT log AS xyzzy FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY 'xyzzy';
  }} msg]
  lappend v $msg
} {0 {0 1 2 3 4 5 5 6 7 8}}
do_test select4-5.2c {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY 'xyzzy';
  }} msg]
  lappend v $msg
} {1 {ORDER BY term number 1 does not match any result column}}
do_test select4-5.2d {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY 'xyzzy';
  }} msg]
  lappend v $msg
} {1 {ORDER BY term number 1 does not match any result column}}
do_test select4-5.2e {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL







|







 







|








|








|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
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
#    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.21 2007/12/08 21:10:20 drh Exp $

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

# Most tests in this file depend on compound-select. But there are a couple
# right at the end that test DISTINCT, so we cannot omit the entire file.
#
................................................................................
  lappend v $msg
} {0 {0 1 2 3 4 5 5 6 7 8}}
do_test select4-5.2b {
  set v [catch {execsql {
    SELECT DISTINCT log AS xyzzy FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY "xyzzy";
  }} msg]
  lappend v $msg
} {0 {0 1 2 3 4 5 5 6 7 8}}
do_test select4-5.2c {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT n FROM t1 WHERE log=3
    ORDER BY "xyzzy";
  }} msg]
  lappend v $msg
} {1 {ORDER BY term number 1 does not match any result column}}
do_test select4-5.2d {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY "xyzzy";
  }} msg]
  lappend v $msg
} {1 {ORDER BY term number 1 does not match any result column}}
do_test select4-5.2e {
  set v [catch {execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL