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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
56063ec84b130bcdb0e90bc76fabca39 |
User & Date: | drh 2007-12-13 03:45:08.000 |
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: 2f88b9b3e3 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: 56063ec84b 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: bbddf16ac9 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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. ** | | | 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.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. |
︙ | ︙ | |||
1482 1483 1484 1485 1486 1487 1488 | 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) ){ | > > > > | > < < < < < > | 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 | 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. ** |
︙ | ︙ | |||
1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 | 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; } | > > > > > > | < | < | | > > | > > > > | > | | | > > > > | | | | | | > | | | | | > > > > > > > > > > > > > | 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 | 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. |
︙ | ︙ | |||
3029 3030 3031 3032 3033 3034 3035 | 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 ){ | | > > | 3059 3060 3061 3062 3063 3064 3065 3066 3067 3068 3069 3070 3071 3072 3073 3074 3075 3076 3077 3078 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 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. ** ************************************************************************* ** Internal interface definitions for SQLite. ** | | | 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. ** ************************************************************************* ** 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 |
︙ | ︙ | |||
1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 | 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 */ }; | > | 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 | 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 | 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 | | > > > > > > | 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 | 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} |
︙ | ︙ | |||
168 169 170 171 172 173 174 175 176 177 178 179 180 181 | 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 { | > > > > > > > > > > | 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 | 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 | # 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. # | | | 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 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. # |
︙ | ︙ | |||
206 207 208 209 210 211 212 | SELECT n FROM t1 WHERE log=3 ORDER BY log; } } {5} do_test select4-4.1.2 { execsql { | | > > | > | 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 | 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; |
︙ | ︙ |