/ Check-in [56063ec8]
Login

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

Overview
Comment:ORDER BY in a compound SELECT will first match against the left-most SELECT. If there is no match there, it begins working its way to the right. (CVS 4621)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 56063ec84b130bcdb0e90bc76fabca394d0d867f
User & Date: drh 2007-12-13 03:45:08
Context
2007-12-13
07:58
Adjust the test suite to account for recent changes related to #2822. Most changes are related to English language error messages only. (CVS 4622) check-in: 2f88b9b3 user: danielk1977 tags: trunk
03:45
ORDER BY in a compound SELECT will first match against the left-most SELECT. If there is no match there, it begins working its way to the right. (CVS 4621) check-in: 56063ec8 user: drh tags: trunk
02:45
Towards getting ORDER BY to match against the correctin columns. This version only looks at the left-most column in a compound SELECT. That is the correct thing to do, but not what SQLite has historically done. (CVS 4620) check-in: bbddf16a 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
....
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
....
1589
1590
1591
1592
1593
1594
1595


1596
1597
1598
1599
1600
1601
1602




1603
1604
1605
1606
1607
1608
1609


1610
1611


1612




1613

1614
1615
1616




1617
1618
1619
1620
1621
1622

1623
1624
1625
1626
1627













1628
1629
1630
1631
1632
1633
1634
....
3029
3030
3031
3032
3033
3034
3035
3036
3037
3038
3039


3040
3041
3042
3043
3044
3045
3046
**    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.366 2007/12/13 02:45:31 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  memset(&nc, 0, sizeof(nc));
  nc.pParse = pParse;
  nc.pSrcList = pSelect->pSrc;
  nc.pEList = pEList;
  nc.allowAgg = 1;
  nc.nErr = 0;
  if( sqlite3ExprResolveNames(&nc, pE) ){




    return -1;

  }
  if( nc.hasAgg && pHasAgg ){
    *pHasAgg = 1;
  }

  /* For a compound SELECT, we need to try to match the ORDER BY
  ** expression against an expression in the result set
................................................................................
  */
  if( isCompound ){
    for(i=0; i<pEList->nExpr; i++){
      if( sqlite3ExprCompare(pEList->a[i].pExpr, pE) ){
        return i+1;
      }
    }
    sqlite3ErrorMsg(pParse, "%r ORDER BY term does not match any "
       "column in the result set of the left-most SELECT", idx);
    return -1;
  }else{
    return 0;
  }

}


/*
** Analyze and ORDER BY or GROUP BY clause in a simple SELECT statement.
** Return the number of errors seen.
**
................................................................................
  Parse *pParse,        /* Parsing context.  Leave error messages here */
  Select *pSelect,      /* The SELECT statement containing the ORDER BY */
  int iTable            /* Output table for compound SELECT statements */
){
  int i;
  ExprList *pOrderBy;
  ExprList *pEList;



  pOrderBy = pSelect->pOrderBy;
  if( pOrderBy==0 ) return 0;
  if( pOrderBy->nExpr>SQLITE_MAX_COLUMN ){
    sqlite3ErrorMsg(pParse, "too many terms in ORDER BY clause");
    return 1;
  }




  while( pSelect->pPrior ){
    pSelect = pSelect->pPrior;
  }
  pEList = pSelect->pEList;
  if( pEList==0 ){
    return 1;
  }


  for(i=0; i<pOrderBy->nExpr; i++){
    int iCol;


    Expr *pE = pOrderBy->a[i].pExpr;




    iCol = matchOrderByTermToExprList(pParse, pSelect, pE, i+1, 1, 0);

    if( iCol<0 ){
      return 1;
    }




    if( iCol>pEList->nExpr ){
      sqlite3ErrorMsg(pParse, 
         "%r ORDER BY term out of range - should be "
         "between 1 and %d", i+1, pEList->nExpr);
      return 1;
    }

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













  }
  return 0;
}

/*
** Get a VDBE for the given parser context.  Create a new one if necessary.
** If an error occurs, return NULL and leave a message in pParse.
................................................................................
  p->pOrderBy = pOrderBy;

#ifndef SQLITE_OMIT_COMPOUND_SELECT
  /* If there is are a sequence of queries, do the earlier ones first.
  */
  if( p->pPrior ){
    if( p->pRightmost==0 ){
      Select *pLoop;
      int cnt = 0;
      for(pLoop=p; pLoop; pLoop=pLoop->pPrior, cnt++){
        pLoop->pRightmost = p;


      }
      if( SQLITE_MAX_COMPOUND_SELECT>0 && cnt>SQLITE_MAX_COMPOUND_SELECT ){
        sqlite3ErrorMsg(pParse, "too many terms in compound SELECT");
        return 1;
      }
    }
    return multiSelect(pParse, p, eDest, iParm, aff);







|







 







>
>
>
>
|
>







 







<
<
<
<
<

>







 







>
>







>
>
>
>



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







 







|



>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
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
....
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612




1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
....
3059
3060
3061
3062
3063
3064
3065
3066
3067
3068
3069
3070
3071
3072
3073
3074
3075
3076
3077
3078
**    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.367 2007/12/13 03:45:08 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  memset(&nc, 0, sizeof(nc));
  nc.pParse = pParse;
  nc.pSrcList = pSelect->pSrc;
  nc.pEList = pEList;
  nc.allowAgg = 1;
  nc.nErr = 0;
  if( sqlite3ExprResolveNames(&nc, pE) ){
    if( isCompound ){
      sqlite3ErrorClear(pParse);
      return 0;
    }else{
      return -1;
    }
  }
  if( nc.hasAgg && pHasAgg ){
    *pHasAgg = 1;
  }

  /* For a compound SELECT, we need to try to match the ORDER BY
  ** expression against an expression in the result set
................................................................................
  */
  if( isCompound ){
    for(i=0; i<pEList->nExpr; i++){
      if( sqlite3ExprCompare(pEList->a[i].pExpr, pE) ){
        return i+1;
      }
    }





  }
  return 0;
}


/*
** Analyze and ORDER BY or GROUP BY clause in a simple SELECT statement.
** Return the number of errors seen.
**
................................................................................
  Parse *pParse,        /* Parsing context.  Leave error messages here */
  Select *pSelect,      /* The SELECT statement containing the ORDER BY */
  int iTable            /* Output table for compound SELECT statements */
){
  int i;
  ExprList *pOrderBy;
  ExprList *pEList;
  sqlite3 *db;
  int moreToDo = 1;

  pOrderBy = pSelect->pOrderBy;
  if( pOrderBy==0 ) return 0;
  if( pOrderBy->nExpr>SQLITE_MAX_COLUMN ){
    sqlite3ErrorMsg(pParse, "too many terms in ORDER BY clause");
    return 1;
  }
  db = pParse->db;
  for(i=0; i<pOrderBy->nExpr; i++){
    pOrderBy->a[i].done = 0;
  }
  while( pSelect->pPrior ){
    pSelect = pSelect->pPrior;
  }




  while( pSelect && moreToDo ){
    moreToDo = 0;
    for(i=0; i<pOrderBy->nExpr; i++){
      int iCol;
      Expr *pE;
      if( pOrderBy->a[i].done ) continue;
      pE = pOrderBy->a[i].pExpr;
      Expr *pDup = sqlite3ExprDup(db, pE);
      if( pDup==0 ){
        return 1;
      }
      iCol = matchOrderByTermToExprList(pParse, pSelect, pDup, i+1, 1, 0);
      sqlite3ExprDelete(pDup);
      if( iCol<0 ){
        return 1;
      }
      pEList = pSelect->pEList;
      if( pEList==0 ){
        return 1;
      }
      if( iCol>pEList->nExpr ){
        sqlite3ErrorMsg(pParse, 
           "%r ORDER BY term out of range - should be "
           "between 1 and %d", i+1, pEList->nExpr);
        return 1;
      }
      if( iCol>0 ){
        pE->op = TK_COLUMN;
        pE->iTable = iTable;
        pE->iAgg = -1;
        pE->iColumn = iCol-1;
        pE->pTab = 0;
        pOrderBy->a[i].done = 1;
      }else{
        moreToDo = 1;
      }
    }
    pSelect = pSelect->pNext;
  }
  for(i=0; i<pOrderBy->nExpr; i++){
    if( pOrderBy->a[i].done==0 ){
      sqlite3ErrorMsg(pParse, "%r ORDER BY term does not match any "
            "column in the result set", i+1);
      return 1;
    }
  }
  return 0;
}

/*
** Get a VDBE for the given parser context.  Create a new one if necessary.
** If an error occurs, return NULL and leave a message in pParse.
................................................................................
  p->pOrderBy = pOrderBy;

#ifndef SQLITE_OMIT_COMPOUND_SELECT
  /* If there is are a sequence of queries, do the earlier ones first.
  */
  if( p->pPrior ){
    if( p->pRightmost==0 ){
      Select *pLoop, *pRight = 0;
      int cnt = 0;
      for(pLoop=p; pLoop; pLoop=pLoop->pPrior, cnt++){
        pLoop->pRightmost = p;
        pLoop->pNext = pRight;
        pRight = pLoop;
      }
      if( SQLITE_MAX_COMPOUND_SELECT>0 && cnt>SQLITE_MAX_COMPOUND_SELECT ){
        sqlite3ErrorMsg(pParse, "too many terms in compound SELECT");
        return 1;
      }
    }
    return multiSelect(pParse, p, eDest, iParm, aff);

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1313
1314
1315
1316
1317
1318
1319

1320
1321
1322
1323
1324
1325
1326
**    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.625 2007/12/11 19:34:45 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** The macro unlikely() is a hint that surrounds a boolean
** expression that is usually false.  Macro likely() surrounds
................................................................................
  char affinity;         /* MakeRecord with this affinity for SRT_Set */
  SrcList *pSrc;         /* The FROM clause */
  Expr *pWhere;          /* The WHERE clause */
  ExprList *pGroupBy;    /* The GROUP BY clause */
  Expr *pHaving;         /* The HAVING clause */
  ExprList *pOrderBy;    /* The ORDER BY clause */
  Select *pPrior;        /* Prior select in a compound select statement */

  Select *pRightmost;    /* Right-most select in a compound select statement */
  Expr *pLimit;          /* LIMIT expression. NULL means not used. */
  Expr *pOffset;         /* OFFSET expression. NULL means not used. */
  int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  int addrOpenEphm[3];   /* OP_OpenEphem opcodes related to this select */
};








|







 







>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
....
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
**    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.626 2007/12/13 03:45:08 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** The macro unlikely() is a hint that surrounds a boolean
** expression that is usually false.  Macro likely() surrounds
................................................................................
  char affinity;         /* MakeRecord with this affinity for SRT_Set */
  SrcList *pSrc;         /* The FROM clause */
  Expr *pWhere;          /* The WHERE clause */
  ExprList *pGroupBy;    /* The GROUP BY clause */
  Expr *pHaving;         /* The HAVING clause */
  ExprList *pOrderBy;    /* The ORDER BY clause */
  Select *pPrior;        /* Prior select in a compound select statement */
  Select *pNext;         /* Next select to the left in a compound */
  Select *pRightmost;    /* Right-most select in a compound select statement */
  Expr *pLimit;          /* LIMIT expression. NULL means not used. */
  Expr *pOffset;         /* OFFSET expression. NULL means not used. */
  int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  int addrOpenEphm[3];   /* OP_OpenEphem opcodes related to this select */
};

Changes to test/null.test.

146
147
148
149
150
151
152
153






154
155
156
157
158
159
160
...
168
169
170
171
172
173
174










175
176
177
178
179
180
181
do_test null-5.1 {
  execsql {
    select distinct b from t1 order by b;
  }
} {{} 0 1}

# A UNION to two queries should treat NULL values
# as distinct






#
ifcapable compound {
  do_test null-6.1 {
    execsql {
      select b from t1 union select c from t1 order by b;
    }
  } {{} 0 1}
................................................................................
      select b from t1 union select c from t1 order by t1.b;
    }
  } {{} 0 1}
  do_test null-6.4 {
    execsql {
      select b from t1 union select c from t1 order by main.t1.b;
    }










  } {{} 0 1}
} ;# ifcapable compound

# The UNIQUE constraint only applies to non-null values
#
ifcapable conflict {
do_test null-7.1 {







|
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>







146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
...
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
do_test null-5.1 {
  execsql {
    select distinct b from t1 order by b;
  }
} {{} 0 1}

# A UNION to two queries should treat NULL values
# as distinct.
#
# (Later:)  We also take this opportunity to test the ability
# of an ORDER BY clause to bind to either SELECT of a UNION.
# The left-most SELECT is preferred.  In standard SQL, only
# the left SELECT can be used.  The ability to match an ORDER
# BY term to the right SELECT is an SQLite extension.
#
ifcapable compound {
  do_test null-6.1 {
    execsql {
      select b from t1 union select c from t1 order by b;
    }
  } {{} 0 1}
................................................................................
      select b from t1 union select c from t1 order by t1.b;
    }
  } {{} 0 1}
  do_test null-6.4 {
    execsql {
      select b from t1 union select c from t1 order by main.t1.b;
    }
  } {{} 0 1}
  do_test null-6.5 {
    execsql {
      select b from t1 union select c from t1 order by t1.a;
    }
  } {{} 0 1}
  do_test null-6.6 {
    execsql {
      select b from t1 union select c from t1 order by main.t1.a;
    }
  } {{} 0 1}
} ;# ifcapable compound

# The UNIQUE constraint only applies to non-null values
#
ifcapable conflict {
do_test null-7.1 {

Changes to test/select4.test.

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


214
215
216
217
218

219
220
221
222
223
224
225
#    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.22 2007/12/10 18:51:48 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.
#
................................................................................
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {5}

do_test select4-4.1.2 {
  execsql {
    SELECT DISTINCT log FROM t1 UNION ALL SELECT 6


    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {5 6}

do_test select4-4.1.3 {
  execsql {
    CREATE TABLE t2 AS
      SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
      INTERSECT
      SELECT n FROM t1 WHERE log=3
      ORDER BY log;







|







 







|
>
>


|


>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
#    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.23 2007/12/13 03:45:08 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.
#
................................................................................
    SELECT n FROM t1 WHERE log=3
    ORDER BY log;
  }
} {5}

do_test select4-4.1.2 {
  execsql {
    SELECT DISTINCT log FROM t1
    UNION ALL
    SELECT 6
    INTERSECT
    SELECT n FROM t1 WHERE log=3
    ORDER BY t1.log;
  }
} {5 6}

do_test select4-4.1.3 {
  execsql {
    CREATE TABLE t2 AS
      SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
      INTERSECT
      SELECT n FROM t1 WHERE log=3
      ORDER BY log;