/ Check-in [9554519c]
Login

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: 9554519c126c5e714421a82fd2e8aa9b19e11493
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  1719   1719     }else{
  1720   1720       pRet = 0;
  1721   1721     }
  1722   1722     assert( iCol>=0 );
  1723   1723     if( pRet==0 && iCol<p->pEList->nExpr ){
  1724   1724       pRet = sqlite3ExprCollSeq(pParse, p->pEList->a[iCol].pExpr);
  1725   1725     }
         1726  +  return pRet;
         1727  +}
         1728  +
         1729  +/*
         1730  +** The select statement passed as the second parameter is a compound SELECT
         1731  +** with an ORDER BY clause. This function allocates and returns a KeyInfo
         1732  +** structure suitable for implementing the ORDER BY.
         1733  +**
         1734  +** Space to hold the KeyInfo structure is obtained from malloc. The calling
         1735  +** function is responsible for ensuring that this structure is eventually
         1736  +** freed.
         1737  +*/
         1738  +static KeyInfo *multiSelectOrderByKeyInfo(Parse *pParse, Select *p, int nExtra){
         1739  +  ExprList *pOrderBy = p->pOrderBy;
         1740  +  int nOrderBy = p->pOrderBy->nExpr;
         1741  +  sqlite3 *db = pParse->db;
         1742  +  KeyInfo *pRet = sqlite3KeyInfoAlloc(db, nOrderBy+nExtra, 1);
         1743  +  if( pRet ){
         1744  +    int i;
         1745  +    for(i=0; i<nOrderBy; i++){
         1746  +      struct ExprList_item *pItem = &pOrderBy->a[i];
         1747  +      Expr *pTerm = pItem->pExpr;
         1748  +      CollSeq *pColl;
         1749  +
         1750  +      if( pTerm->flags & EP_Collate ){
         1751  +        pColl = sqlite3ExprCollSeq(pParse, pTerm);
         1752  +      }else{
         1753  +        pColl = multiSelectCollSeq(pParse, p, pItem->u.x.iOrderByCol-1);
         1754  +        if( pColl==0 ) pColl = db->pDfltColl;
         1755  +        pOrderBy->a[i].pExpr =
         1756  +          sqlite3ExprAddCollateString(pParse, pTerm, pColl->zName);
         1757  +      }
         1758  +      assert( sqlite3KeyInfoIsWriteable(pRet) );
         1759  +      pRet->aColl[i] = pColl;
         1760  +      pRet->aSortOrder[i] = pOrderBy->a[i].sortOrder;
         1761  +    }
         1762  +  }
         1763  +
  1726   1764     return pRet;
  1727   1765   }
  1728   1766   #endif /* SQLITE_OMIT_COMPOUND_SELECT */
  1729   1767   
  1730   1768   #ifndef SQLITE_OMIT_CTE
  1731   1769   /*
  1732   1770   ** This routine generates VDBE code to compute the content of a WITH RECURSIVE
................................................................................
  1795   1833     computeLimitRegisters(pParse, p, addrBreak);
  1796   1834     pLimit = p->pLimit;
  1797   1835     pOffset = p->pOffset;
  1798   1836     regLimit = p->iLimit;
  1799   1837     regOffset = p->iOffset;
  1800   1838     p->pLimit = p->pOffset = 0;
  1801   1839     p->iLimit = p->iOffset = 0;
         1840  +  pOrderBy = p->pOrderBy;
  1802   1841   
  1803   1842     /* Locate the cursor number of the Current table */
  1804   1843     for(i=0; ALWAYS(i<pSrc->nSrc); i++){
  1805   1844       if( pSrc->a[i].isRecursive ){
  1806   1845         iCurrent = pSrc->a[i].iCursor;
  1807   1846         break;
  1808   1847       }
  1809   1848     }
  1810   1849   
  1811         -  /* Detach the ORDER BY clause from the compound SELECT */
  1812         -  pOrderBy = p->pOrderBy;
  1813         -  p->pOrderBy = 0;
  1814         -
  1815   1850     /* Allocate cursors numbers for Queue and Distinct.  The cursor number for
  1816   1851     ** the Distinct table must be exactly one greater than Queue in order
  1817   1852     ** for the SRT_DistTable and SRT_DistQueue destinations to work. */
  1818   1853     iQueue = pParse->nTab++;
  1819   1854     if( p->op==TK_UNION ){
  1820   1855       eDest = pOrderBy ? SRT_DistQueue : SRT_DistTable;
  1821   1856       iDistinct = pParse->nTab++;
................................................................................
  1824   1859     }
  1825   1860     sqlite3SelectDestInit(&destQueue, eDest, iQueue);
  1826   1861   
  1827   1862     /* Allocate cursors for Current, Queue, and Distinct. */
  1828   1863     regCurrent = ++pParse->nMem;
  1829   1864     sqlite3VdbeAddOp3(v, OP_OpenPseudo, iCurrent, regCurrent, nCol);
  1830   1865     if( pOrderBy ){
  1831         -    KeyInfo *pKeyInfo = keyInfoFromExprList(pParse, pOrderBy, 1);
         1866  +    KeyInfo *pKeyInfo = multiSelectOrderByKeyInfo(pParse, p, 1);
  1832   1867       sqlite3VdbeAddOp4(v, OP_OpenEphemeral, iQueue, pOrderBy->nExpr+2, 0,
  1833   1868                         (char*)pKeyInfo, P4_KEYINFO);
  1834   1869       destQueue.pOrderBy = pOrderBy;
  1835   1870     }else{
  1836   1871       sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iQueue, nCol);
  1837   1872     }
  1838   1873     VdbeComment((v, "Queue table"));
  1839   1874     if( iDistinct ){
  1840   1875       p->addrOpenEphm[0] = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iDistinct, 0);
  1841   1876       p->selFlags |= SF_UsesEphemeral;
  1842   1877     }
         1878  +
         1879  +  /* Detach the ORDER BY clause from the compound SELECT */
         1880  +  p->pOrderBy = 0;
  1843   1881   
  1844   1882     /* Store the results of the setup-query in Queue. */
  1845   1883     rc = sqlite3Select(pParse, pSetup, &destQueue);
  1846   1884     if( rc ) goto end_of_recursive_query;
  1847   1885   
  1848   1886     /* Find the next row in the Queue and output that row */
  1849   1887     addrTop = sqlite3VdbeAddOp2(v, OP_Rewind, iQueue, addrBreak);
................................................................................
  2621   2659     if( aPermute ){
  2622   2660       struct ExprList_item *pItem;
  2623   2661       for(i=0, pItem=pOrderBy->a; i<nOrderBy; i++, pItem++){
  2624   2662         assert( pItem->u.x.iOrderByCol>0
  2625   2663             && pItem->u.x.iOrderByCol<=p->pEList->nExpr );
  2626   2664         aPermute[i] = pItem->u.x.iOrderByCol - 1;
  2627   2665       }
  2628         -    pKeyMerge = sqlite3KeyInfoAlloc(db, nOrderBy, 1);
  2629         -    if( pKeyMerge ){
  2630         -      for(i=0; i<nOrderBy; i++){
  2631         -        CollSeq *pColl;
  2632         -        Expr *pTerm = pOrderBy->a[i].pExpr;
  2633         -        if( pTerm->flags & EP_Collate ){
  2634         -          pColl = sqlite3ExprCollSeq(pParse, pTerm);
  2635         -        }else{
  2636         -          pColl = multiSelectCollSeq(pParse, p, aPermute[i]);
  2637         -          if( pColl==0 ) pColl = db->pDfltColl;
  2638         -          pOrderBy->a[i].pExpr =
  2639         -             sqlite3ExprAddCollateString(pParse, pTerm, pColl->zName);
  2640         -        }
  2641         -        assert( sqlite3KeyInfoIsWriteable(pKeyMerge) );
  2642         -        pKeyMerge->aColl[i] = pColl;
  2643         -        pKeyMerge->aSortOrder[i] = pOrderBy->a[i].sortOrder;
  2644         -      }
  2645         -    }
         2666  +    pKeyMerge = multiSelectOrderByKeyInfo(pParse, p, 1);
  2646   2667     }else{
  2647   2668       pKeyMerge = 0;
  2648   2669     }
  2649   2670   
  2650   2671     /* Reattach the ORDER BY clause to the query.
  2651   2672     */
  2652   2673     p->pOrderBy = pOrderBy;

Changes to test/with1.test.

   602    602   } [list {*}{
   603    603     /g /g/h
   604    604     /a /a/d /a/d/f 
   605    605             /a/d/e 
   606    606        /a/b /a/b/c
   607    607   }]
   608    608   
          609  +
          610  +# Test name resolution in ORDER BY clauses.
          611  +#
          612  +do_catchsql_test 10.7.1 {
          613  +  WITH t(a) AS (
          614  +    SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY a
          615  +  ) 
          616  +  SELECT * FROM t
          617  +} {1 {1st ORDER BY term does not match any column in the result set}}
          618  +do_execsql_test 10.7.2 {
          619  +  WITH t(a) AS (
          620  +    SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY b
          621  +  ) 
          622  +  SELECT * FROM t
          623  +} {1 2 3 4 5}
          624  +do_execsql_test 10.7.3 {
          625  +  WITH t(a) AS (
          626  +    SELECT 1 AS b UNION ALL SELECT a+1 AS c FROM t WHERE a<5 ORDER BY c
          627  +  ) 
          628  +  SELECT * FROM t
          629  +} {1 2 3 4 5}
          630  +
          631  +# Test COLLATE clauses attached to ORDER BY.
          632  +#
          633  +insert_into_tree {
          634  +  /a/b
          635  +  /a/C
          636  +  /a/d
          637  +  /B/e
          638  +  /B/F
          639  +  /B/g
          640  +  /c/h
          641  +  /c/I
          642  +  /c/j
          643  +}
          644  +
          645  +do_execsql_test 10.8.1 {
          646  +  WITH flat(fid, depth, p) AS (
          647  +    SELECT id, 1, '/' || payload FROM tree WHERE parentid IS NULL
          648  +    UNION ALL
          649  +    SELECT id, depth+1, p||'/'||payload FROM flat, tree WHERE parentid=fid
          650  +    ORDER BY 2, 3 COLLATE nocase
          651  +  )
          652  +  SELECT p FROM flat;
          653  +} {
          654  +  /a /B /c
          655  +  /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
          656  +}
          657  +do_execsql_test 10.8.2 {
          658  +  WITH flat(fid, depth, p) AS (
          659  +      SELECT id, 1, ('/' || payload) COLLATE nocase 
          660  +      FROM tree WHERE parentid IS NULL
          661  +    UNION ALL
          662  +      SELECT id, depth+1, (p||'/'||payload)
          663  +      FROM flat, tree WHERE parentid=fid
          664  +    ORDER BY 2, 3
          665  +  )
          666  +  SELECT p FROM flat;
          667  +} {
          668  +  /a /B /c
          669  +  /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
          670  +}
          671  +
          672  +do_execsql_test 10.8.3 {
          673  +  WITH flat(fid, depth, p) AS (
          674  +      SELECT id, 1, ('/' || payload)
          675  +      FROM tree WHERE parentid IS NULL
          676  +    UNION ALL
          677  +      SELECT id, depth+1, (p||'/'||payload) COLLATE nocase 
          678  +      FROM flat, tree WHERE parentid=fid
          679  +    ORDER BY 2, 3
          680  +  )
          681  +  SELECT p FROM flat;
          682  +} {
          683  +  /a /B /c
          684  +  /a/b /a/C /a/d /B/e /B/F /B/g /c/h /c/I /c/j
          685  +}
          686  +
          687  +do_execsql_test 10.8.4.1 {
          688  +  CREATE TABLE tst(a,b);
          689  +  INSERT INTO tst VALUES('a', 'A');
          690  +  INSERT INTO tst VALUES('b', 'B');
          691  +  INSERT INTO tst VALUES('c', 'C');
          692  +  SELECT a COLLATE nocase FROM tst UNION ALL SELECT b FROM tst ORDER BY 1;
          693  +} {a A b B c C}
          694  +do_execsql_test 10.8.4.2 {
          695  +  SELECT a FROM tst UNION ALL SELECT b COLLATE nocase FROM tst ORDER BY 1;
          696  +} {A B C a b c}
          697  +do_execsql_test 10.8.4.3 {
          698  +  SELECT a||'' FROM tst UNION ALL SELECT b COLLATE nocase FROM tst ORDER BY 1;
          699  +} {a A b B c C}
   609    700   
   610    701   # Test cases to illustrate on the ORDER BY clause on a recursive query can be
   611    702   # used to control depth-first versus breath-first search in a tree.
   612    703   #
   613    704   do_execsql_test 11.1 {
   614    705     CREATE TABLE org(
   615    706       name TEXT PRIMARY KEY,