Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Select collation sequences for ORDER BY expressions attached to recursive CTEs in the same way as they are selected for other compound SELECT statements. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
9554519c126c5e714421a82fd2e8aa9b |
| User & Date: | dan 2014-01-24 20:37:18 |
Context
|
2014-01-24
| ||
| 22:58 | Fixes for various clang warnings. (check-in: 87bf6063 user: drh tags: trunk) | |
| 20:37 | Select collation sequences for ORDER BY expressions attached to recursive CTEs in the same way as they are selected for other compound SELECT statements. (check-in: 9554519c user: dan tags: trunk) | |
| 17:03 | Fix harmless compiler warnings in the Tcl interface. (check-in: 35bc81f5 user: mistachkin tags: trunk) | |
Changes
Changes to src/select.c.
1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 .... 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 .... 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 .... 2621 2622 2623 2624 2625 2626 2627 2628 2629 2630 2631 2632 2633 2634 2635 2636 2637 2638 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 |
}else{
pRet = 0;
}
assert( iCol>=0 );
if( pRet==0 && iCol<p->pEList->nExpr ){
pRet = sqlite3ExprCollSeq(pParse, p->pEList->a[iCol].pExpr);
}
return pRet;
}
#endif /* SQLITE_OMIT_COMPOUND_SELECT */
#ifndef SQLITE_OMIT_CTE
/*
** This routine generates VDBE code to compute the content of a WITH RECURSIVE
................................................................................
computeLimitRegisters(pParse, p, addrBreak);
pLimit = p->pLimit;
pOffset = p->pOffset;
regLimit = p->iLimit;
regOffset = p->iOffset;
p->pLimit = p->pOffset = 0;
p->iLimit = p->iOffset = 0;
/* Locate the cursor number of the Current table */
for(i=0; ALWAYS(i<pSrc->nSrc); i++){
if( pSrc->a[i].isRecursive ){
iCurrent = pSrc->a[i].iCursor;
break;
}
}
/* Detach the ORDER BY clause from the compound SELECT */
pOrderBy = p->pOrderBy;
p->pOrderBy = 0;
/* Allocate cursors numbers for Queue and Distinct. The cursor number for
** the Distinct table must be exactly one greater than Queue in order
** for the SRT_DistTable and SRT_DistQueue destinations to work. */
iQueue = pParse->nTab++;
if( p->op==TK_UNION ){
eDest = pOrderBy ? SRT_DistQueue : SRT_DistTable;
iDistinct = pParse->nTab++;
................................................................................
}
sqlite3SelectDestInit(&destQueue, eDest, iQueue);
/* Allocate cursors for Current, Queue, and Distinct. */
regCurrent = ++pParse->nMem;
sqlite3VdbeAddOp3(v, OP_OpenPseudo, iCurrent, regCurrent, nCol);
if( pOrderBy ){
KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pOrderBy, 1);
sqlite3VdbeAddOp4(v, OP_OpenEphemeral, iQueue, pOrderBy->nExpr+2, 0,
(char*)pKeyInfo, P4_KEYINFO);
destQueue.pOrderBy = pOrderBy;
}else{
sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iQueue, nCol);
}
VdbeComment((v, "Queue table"));
if( iDistinct ){
p->addrOpenEphm[0] = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iDistinct, 0);
p->selFlags |= SF_UsesEphemeral;
}
/* Store the results of the setup-query in Queue. */
rc = sqlite3Select(pParse, pSetup, &destQueue);
if( rc ) goto end_of_recursive_query;
/* Find the next row in the Queue and output that row */
addrTop = sqlite3VdbeAddOp2(v, OP_Rewind, iQueue, addrBreak);
................................................................................
if( aPermute ){
struct ExprList_item *pItem;
for(i=0, pItem=pOrderBy->a; i<nOrderBy; i++, pItem++){
assert( pItem->u.x.iOrderByCol>0
&& pItem->u.x.iOrderByCol<=p->pEList->nExpr );
aPermute[i] = pItem->u.x.iOrderByCol - 1;
}
pKeyMerge = sqlite3KeyInfoAlloc(db, nOrderBy, 1);
if( pKeyMerge ){
for(i=0; i<nOrderBy; i++){
CollSeq *pColl;
Expr *pTerm = pOrderBy->a[i].pExpr;
if( pTerm->flags & EP_Collate ){
pColl = sqlite3ExprCollSeq(pParse, pTerm);
}else{
pColl = multiSelectCollSeq(pParse, p, aPermute[i]);
if( pColl==0 ) pColl = db->pDfltColl;
pOrderBy->a[i].pExpr =
sqlite3ExprAddCollateString(pParse, pTerm, pColl->zName);
}
assert( sqlite3KeyInfoIsWriteable(pKeyMerge) );
pKeyMerge->aColl[i] = pColl;
pKeyMerge->aSortOrder[i] = pOrderBy->a[i].sortOrder;
}
}
}else{
pKeyMerge = 0;
}
/* Reattach the ORDER BY clause to the query.
*/
p->pOrderBy = pOrderBy;
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > < < < < | > > > | < < < < < < < < < < < < < < < < < |
1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 .... 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 .... 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 .... 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 2673 |
}else{
pRet = 0;
}
assert( iCol>=0 );
if( pRet==0 && iCol<p->pEList->nExpr ){
pRet = sqlite3ExprCollSeq(pParse, p->pEList->a[iCol].pExpr);
}
return pRet;
}
/*
** The select statement passed as the second parameter is a compound SELECT
** with an ORDER BY clause. This function allocates and returns a KeyInfo
** structure suitable for implementing the ORDER BY.
**
** Space to hold the KeyInfo structure is obtained from malloc. The calling
** function is responsible for ensuring that this structure is eventually
** freed.
*/
static KeyInfo *multiSelectOrderByKeyInfo(Parse *pParse, Select *p, int nExtra){
ExprList *pOrderBy = p->pOrderBy;
int nOrderBy = p->pOrderBy->nExpr;
sqlite3 *db = pParse->db;
KeyInfo *pRet = sqlite3KeyInfoAlloc(db, nOrderBy+nExtra, 1);
if( pRet ){
int i;
for(i=0; i<nOrderBy; i++){
struct ExprList_item *pItem = &pOrderBy->a[i];
Expr *pTerm = pItem->pExpr;
CollSeq *pColl;
if( pTerm->flags & EP_Collate ){
pColl = sqlite3ExprCollSeq(pParse, pTerm);
}else{
pColl = multiSelectCollSeq(pParse, p, pItem->u.x.iOrderByCol-1);
if( pColl==0 ) pColl = db->pDfltColl;
pOrderBy->a[i].pExpr =
sqlite3ExprAddCollateString(pParse, pTerm, pColl->zName);
}
assert( sqlite3KeyInfoIsWriteable(pRet) );
pRet->aColl[i] = pColl;
pRet->aSortOrder[i] = pOrderBy->a[i].sortOrder;
}
}
return pRet;
}
#endif /* SQLITE_OMIT_COMPOUND_SELECT */
#ifndef SQLITE_OMIT_CTE
/*
** This routine generates VDBE code to compute the content of a WITH RECURSIVE
................................................................................
computeLimitRegisters(pParse, p, addrBreak);
pLimit = p->pLimit;
pOffset = p->pOffset;
regLimit = p->iLimit;
regOffset = p->iOffset;
p->pLimit = p->pOffset = 0;
p->iLimit = p->iOffset = 0;
pOrderBy = p->pOrderBy;
/* Locate the cursor number of the Current table */
for(i=0; ALWAYS(i<pSrc->nSrc); i++){
if( pSrc->a[i].isRecursive ){
iCurrent = pSrc->a[i].iCursor;
break;
}
}
/* Allocate cursors numbers for Queue and Distinct. The cursor number for
** the Distinct table must be exactly one greater than Queue in order
** for the SRT_DistTable and SRT_DistQueue destinations to work. */
iQueue = pParse->nTab++;
if( p->op==TK_UNION ){
eDest = pOrderBy ? SRT_DistQueue : SRT_DistTable;
iDistinct = pParse->nTab++;
................................................................................
}
sqlite3SelectDestInit(&destQueue, eDest, iQueue);
/* Allocate cursors for Current, Queue, and Distinct. */
regCurrent = ++pParse->nMem;
sqlite3VdbeAddOp3(v, OP_OpenPseudo, iCurrent, regCurrent, nCol);
if( pOrderBy ){
KeyInfo *pKeyInfo = multiSelectOrderByKeyInfo(pParse, p, 1);
sqlite3VdbeAddOp4(v, OP_OpenEphemeral, iQueue, pOrderBy->nExpr+2, 0,
(char*)pKeyInfo, P4_KEYINFO);
destQueue.pOrderBy = pOrderBy;
}else{
sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iQueue, nCol);
}
VdbeComment((v, "Queue table"));
if( iDistinct ){
p->addrOpenEphm[0] = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iDistinct, 0);
p->selFlags |= SF_UsesEphemeral;
}
/* Detach the ORDER BY clause from the compound SELECT */
p->pOrderBy = 0;
/* Store the results of the setup-query in Queue. */
rc = sqlite3Select(pParse, pSetup, &destQueue);
if( rc ) goto end_of_recursive_query;
/* Find the next row in the Queue and output that row */
addrTop = sqlite3VdbeAddOp2(v, OP_Rewind, iQueue, addrBreak);
................................................................................
if( aPermute ){
struct ExprList_item *pItem;
for(i=0, pItem=pOrderBy->a; i<nOrderBy; i++, pItem++){
assert( pItem->u.x.iOrderByCol>0
&& pItem->u.x.iOrderByCol<=p->pEList->nExpr );
aPermute[i] = pItem->u.x.iOrderByCol - 1;
}
pKeyMerge = multiSelectOrderByKeyInfo(pParse, p, 1);
}else{
pKeyMerge = 0;
}
/* Reattach the ORDER BY clause to the query.
*/
p->pOrderBy = pOrderBy;
|
Changes to test/with1.test.
602 603 604 605 606 607 608 609 610 611 612 613 614 615 |
} [list {*}{
/g /g/h
/a /a/d /a/d/f
/a/d/e
/a/b /a/b/c
}]
# Test cases to illustrate on the ORDER BY clause on a recursive query can be
# used to control depth-first versus breath-first search in a tree.
#
do_execsql_test 11.1 {
CREATE TABLE org(
name TEXT PRIMARY KEY,
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 |
} [list {*}{
/g /g/h
/a /a/d /a/d/f
/a/d/e
/a/b /a/b/c
}]
# Test name resolution in ORDER BY clauses.
#
do_catchsql_test 10.7.1 {
WITH t(a) AS (
SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY a
)
SELECT * FROM t
} {1 {1st ORDER BY term does not match any column in the result set}}
do_execsql_test 10.7.2 {
WITH t(a) AS (
SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY b
)
SELECT * FROM t
} {1 2 3 4 5}
do_execsql_test 10.7.3 {
WITH t(a) AS (
SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY c
)
SELECT * FROM t
} {1 2 3 4 5}
# Test COLLATE clauses attached to ORDER BY.
#
insert_into_tree {
/a/b
/a/C
/a/d
/B/e
/B/F
/B/g
/c/h
/c/I
/c/j
}
do_execsql_test 10.8.1 {
WITH flat(fid, depth, p) AS (
SELECT id, 1, '/' || payload FROM tree WHERE parentid IS NULL
UNION ALL
SELECT id, depth+1, p||'/'||payload FROM flat, tree WHERE parentid=fid
ORDER BY 2, 3 COLLATE nocase
)
SELECT p FROM flat;
} {
/a /B /c
/a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
}
do_execsql_test 10.8.2 {
WITH flat(fid, depth, p) AS (
SELECT id, 1, ('/' || payload) COLLATE nocase
FROM tree WHERE parentid IS NULL
UNION ALL
SELECT id, depth+1, (p||'/'||payload)
FROM flat, tree WHERE parentid=fid
ORDER BY 2, 3
)
SELECT p FROM flat;
} {
/a /B /c
/a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
}
do_execsql_test 10.8.3 {
WITH flat(fid, depth, p) AS (
SELECT id, 1, ('/' || payload)
FROM tree WHERE parentid IS NULL
UNION ALL
SELECT id, depth+1, (p||'/'||payload) COLLATE nocase
FROM flat, tree WHERE parentid=fid
ORDER BY 2, 3
)
SELECT p FROM flat;
} {
/a /B /c
/a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
}
do_execsql_test 10.8.4.1 {
CREATE TABLE tst(a,b);
INSERT INTO tst VALUES('a', 'A');
INSERT INTO tst VALUES('b', 'B');
INSERT INTO tst VALUES('c', 'C');
SELECT a COLLATE nocase FROM tst UNION ALL SELECT b FROM tst ORDER BY 1;
} {a A b B c C}
do_execsql_test 10.8.4.2 {
SELECT a FROM tst UNION ALL SELECT b COLLATE nocase FROM tst ORDER BY 1;
} {A B C a b c}
do_execsql_test 10.8.4.3 {
SELECT a||'' FROM tst UNION ALL SELECT b COLLATE nocase FROM tst ORDER BY 1;
} {a A b B c C}
# Test cases to illustrate on the ORDER BY clause on a recursive query can be
# used to control depth-first versus breath-first search in a tree.
#
do_execsql_test 11.1 {
CREATE TABLE org(
name TEXT PRIMARY KEY,
|