/ Check-in [28196d89]
Login

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

Overview
Comment:Ensure collation sequences and affinities work in window function queries. Fix for [9ece23d2].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 28196d894ac9fad9d8f877c7bf17ec9d299d12acdcc942f9ea0783777b14fdc5
User & Date: dan 2019-07-08 12:01:39
References
2019-07-08
13:45
Fix an assert() that [28196d89] caused to fail. check-in: 8fb0c6d5 user: dan tags: trunk
12:03 Ticket [9ece23d2] Default collation sequences and affinities lost when window function added to query status still Open with 3 other changes artifact: f5d6cde2 user: dan
Context
2019-07-08
13:45
Fix an assert() that [28196d89] caused to fail. check-in: 8fb0c6d5 user: dan tags: trunk
12:01
Ensure collation sequences and affinities work in window function queries. Fix for [9ece23d2]. check-in: 28196d89 user: dan tags: trunk
2019-07-05
15:16
Fix a valgrind problem in fts3corrupt4.test. check-in: cb3dec42 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  2092   2092     db->flags = savedFlags;
  2093   2093     if( pParse->nErr ) return 0;
  2094   2094     while( pSelect->pPrior ) pSelect = pSelect->pPrior;
  2095   2095     pTab = sqlite3DbMallocZero(db, sizeof(Table) );
  2096   2096     if( pTab==0 ){
  2097   2097       return 0;
  2098   2098     }
  2099         -  /* The sqlite3ResultSetOfSelect() is only used n contexts where lookaside
  2100         -  ** is disabled */
  2101         -  assert( db->lookaside.bDisable );
  2102   2099     pTab->nTabRef = 1;
  2103   2100     pTab->zName = 0;
  2104   2101     pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) );
  2105   2102     sqlite3ColumnsFromExprList(pParse, pSelect->pEList, &pTab->nCol, &pTab->aCol);
  2106   2103     sqlite3SelectAddColumnTypeAndCollation(pParse, pTab, pSelect);
  2107   2104     pTab->iPKey = -1;
  2108   2105     if( db->mallocFailed ){

Changes to src/window.c.

   732    732   ** selectWindowRewriteExprCb() by selectWindowRewriteEList().
   733    733   */
   734    734   typedef struct WindowRewrite WindowRewrite;
   735    735   struct WindowRewrite {
   736    736     Window *pWin;
   737    737     SrcList *pSrc;
   738    738     ExprList *pSub;
          739  +  Table *pTab;
   739    740     Select *pSubSelect;             /* Current sub-select, if any */
   740    741   };
   741    742   
   742    743   /*
   743    744   ** Callback function used by selectWindowRewriteEList(). If necessary,
   744    745   ** this function appends to the output expression-list and updates 
   745    746   ** expression (*ppExpr) in place.
................................................................................
   792    793           sqlite3ExprDelete(pParse->db, pExpr);
   793    794           ExprClearProperty(pExpr, EP_Static);
   794    795           memset(pExpr, 0, sizeof(Expr));
   795    796   
   796    797           pExpr->op = TK_COLUMN;
   797    798           pExpr->iColumn = p->pSub->nExpr-1;
   798    799           pExpr->iTable = p->pWin->iEphCsr;
          800  +        pExpr->y.pTab = p->pTab;
   799    801         }
   800    802   
   801    803         break;
   802    804       }
   803    805   
   804    806       default: /* no-op */
   805    807         break;
................................................................................
   835    837   ** appending the new one.
   836    838   */
   837    839   static void selectWindowRewriteEList(
   838    840     Parse *pParse, 
   839    841     Window *pWin,
   840    842     SrcList *pSrc,
   841    843     ExprList *pEList,               /* Rewrite expressions in this list */
          844  +  Table *pTab,
   842    845     ExprList **ppSub                /* IN/OUT: Sub-select expression-list */
   843    846   ){
   844    847     Walker sWalker;
   845    848     WindowRewrite sRewrite;
   846    849   
   847    850     memset(&sWalker, 0, sizeof(Walker));
   848    851     memset(&sRewrite, 0, sizeof(WindowRewrite));
   849    852   
   850    853     sRewrite.pSub = *ppSub;
   851    854     sRewrite.pWin = pWin;
   852    855     sRewrite.pSrc = pSrc;
          856  +  sRewrite.pTab = pTab;
   853    857   
   854    858     sWalker.pParse = pParse;
   855    859     sWalker.xExprCallback = selectWindowRewriteExprCb;
   856    860     sWalker.xSelectCallback = selectWindowRewriteSelectCb;
   857    861     sWalker.u.pRewrite = &sRewrite;
   858    862   
   859    863     (void)sqlite3WalkExprList(&sWalker, pEList);
................................................................................
   905    909       ExprList *pGroupBy = p->pGroupBy;
   906    910       Expr *pHaving = p->pHaving;
   907    911       ExprList *pSort = 0;
   908    912   
   909    913       ExprList *pSublist = 0;       /* Expression list for sub-query */
   910    914       Window *pMWin = p->pWin;      /* Master window object */
   911    915       Window *pWin;                 /* Window object iterator */
          916  +    Table *pTab;
          917  +
          918  +    pTab = sqlite3DbMallocZero(db, sizeof(Table));
          919  +    if( pTab==0 ){
          920  +      return SQLITE_NOMEM;
          921  +    }
   912    922   
   913    923       p->pSrc = 0;
   914    924       p->pWhere = 0;
   915    925       p->pGroupBy = 0;
   916    926       p->pHaving = 0;
          927  +    p->selFlags &= ~SF_Aggregate;
   917    928   
   918    929       /* Create the ORDER BY clause for the sub-select. This is the concatenation
   919    930       ** of the window PARTITION and ORDER BY clauses. Then, if this makes it
   920    931       ** redundant, remove the ORDER BY from the parent SELECT.  */
   921    932       pSort = sqlite3ExprListDup(db, pMWin->pPartition, 0);
   922    933       pSort = exprListAppendList(pParse, pSort, pMWin->pOrderBy, 1);
   923    934       if( pSort && p->pOrderBy ){
................................................................................
   929    940   
   930    941       /* Assign a cursor number for the ephemeral table used to buffer rows.
   931    942       ** The OpenEphemeral instruction is coded later, after it is known how
   932    943       ** many columns the table will have.  */
   933    944       pMWin->iEphCsr = pParse->nTab++;
   934    945       pParse->nTab += 3;
   935    946   
   936         -    selectWindowRewriteEList(pParse, pMWin, pSrc, p->pEList, &pSublist);
   937         -    selectWindowRewriteEList(pParse, pMWin, pSrc, p->pOrderBy, &pSublist);
          947  +    selectWindowRewriteEList(pParse, pMWin, pSrc, p->pEList, pTab, &pSublist);
          948  +    selectWindowRewriteEList(pParse, pMWin, pSrc, p->pOrderBy, pTab, &pSublist);
   938    949       pMWin->nBufferCol = (pSublist ? pSublist->nExpr : 0);
   939    950   
   940    951       /* Append the PARTITION BY and ORDER BY expressions to the to the 
   941    952       ** sub-select expression list. They are required to figure out where 
   942    953       ** boundaries for partitions and sets of peer rows lie.  */
   943    954       pSublist = exprListAppendList(pParse, pSublist, pMWin->pPartition, 0);
   944    955       pSublist = exprListAppendList(pParse, pSublist, pMWin->pOrderBy, 0);
................................................................................
   972    983       }
   973    984   
   974    985       pSub = sqlite3SelectNew(
   975    986           pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0
   976    987       );
   977    988       p->pSrc = sqlite3SrcListAppend(pParse, 0, 0, 0);
   978    989       if( p->pSrc ){
          990  +      Table *pTab2;
   979    991         p->pSrc->a[0].pSelect = pSub;
   980    992         sqlite3SrcListAssignCursors(pParse, p->pSrc);
   981         -      if( sqlite3ExpandSubquery(pParse, &p->pSrc->a[0]) ){
          993  +      pSub->selFlags |= SF_Expanded;
          994  +      pTab2 = sqlite3ResultSetOfSelect(pParse, pSub);
          995  +      if( pTab2==0 ){
   982    996           rc = SQLITE_NOMEM;
   983    997         }else{
   984         -        pSub->selFlags |= SF_Expanded;
   985         -        p->selFlags &= ~SF_Aggregate;
   986         -        sqlite3SelectPrep(pParse, pSub, 0);
          998  +        memcpy(pTab, pTab2, sizeof(Table));
          999  +        pTab->tabFlags |= TF_Ephemeral;
         1000  +        p->pSrc->a[0].pTab = pTab;
         1001  +        pTab = pTab2;
   987   1002         }
   988         -
   989   1003         sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pMWin->iEphCsr, pSublist->nExpr);
   990   1004         sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+1, pMWin->iEphCsr);
   991   1005         sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+2, pMWin->iEphCsr);
   992   1006         sqlite3VdbeAddOp2(v, OP_OpenDup, pMWin->iEphCsr+3, pMWin->iEphCsr);
   993   1007       }else{
   994   1008         sqlite3SelectDelete(db, pSub);
   995   1009       }
   996   1010       if( db->mallocFailed ) rc = SQLITE_NOMEM;
         1011  +    sqlite3DbFree(db, pTab);
   997   1012     }
   998   1013   
   999   1014     return rc;
  1000   1015   }
  1001   1016   
  1002   1017   /*
  1003   1018   ** Free the Window object passed as the second argument.

Added test/window9.test.

            1  +# 2019 June 8
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix window9
           17  +
           18  +ifcapable !windowfunc {
           19  +  finish_test
           20  +  return
           21  +}
           22  +
           23  +do_execsql_test 1.0 {
           24  +  CREATE TABLE fruits(
           25  +     name TEXT COLLATE NOCASE,
           26  +     color TEXT COLLATE NOCASE
           27  +  );
           28  +}
           29  +
           30  +do_execsql_test 1.1 {
           31  +  INSERT INTO fruits (name, color) VALUES ('apple', 'RED');
           32  +  INSERT INTO fruits (name, color) VALUES ('APPLE', 'yellow');
           33  +  INSERT INTO fruits (name, color) VALUES ('pear', 'YELLOW');
           34  +  INSERT INTO fruits (name, color) VALUES ('PEAR', 'green');
           35  +}
           36  +
           37  +do_execsql_test 1.2 {
           38  +  SELECT name, color, dense_rank() OVER (ORDER BY name) FROM fruits;
           39  +} {
           40  +  apple RED    1
           41  +  APPLE yellow 1
           42  +  pear  YELLOW 2
           43  +  PEAR  green  2
           44  +}
           45  +
           46  +do_execsql_test 1.3 {
           47  +  SELECT name, color,
           48  +    dense_rank() OVER (PARTITION BY name ORDER BY color)
           49  +  FROM fruits;
           50  +} {
           51  +  apple RED    1 
           52  +  APPLE yellow 2 
           53  +  PEAR green   1 
           54  +  pear YELLOW  2
           55  +}
           56  +
           57  +do_execsql_test 1.4 {
           58  +  SELECT name, color,
           59  +    dense_rank() OVER (ORDER BY name),
           60  +    dense_rank() OVER (PARTITION BY name ORDER BY color)
           61  +  FROM fruits;
           62  +} {
           63  +  apple RED    1 1 
           64  +  APPLE yellow 1 2 
           65  +  PEAR  green  2 1 
           66  +  pear  YELLOW 2 2
           67  +}
           68  +
           69  +do_execsql_test 1.5 {
           70  +  SELECT name, color,
           71  +    dense_rank() OVER (ORDER BY name),
           72  +    dense_rank() OVER (PARTITION BY name ORDER BY color)
           73  +  FROM fruits ORDER BY color;
           74  +} {
           75  +  PEAR  green  2 1 
           76  +  apple RED    1 1 
           77  +  APPLE yellow 1 2 
           78  +  pear  YELLOW 2 2
           79  +}
           80  +
           81  +do_execsql_test 2.0 {
           82  +  CREATE TABLE t1(a BLOB, b INTEGER, c COLLATE nocase);
           83  +  INSERT INTO t1 VALUES(1, 2, 'abc');
           84  +  INSERT INTO t1 VALUES(3, 4, 'ABC');
           85  +}
           86  +
           87  +do_execsql_test 2.1.1 {
           88  +  SELECT c=='Abc' FROM t1
           89  +} {1     1}
           90  +do_execsql_test 2.1.2 {
           91  +  SELECT c=='Abc', rank() OVER (ORDER BY b) FROM t1
           92  +} {1 1   1 2}
           93  +
           94  +do_execsql_test 2.2.1 {
           95  +  SELECT b=='2' FROM t1
           96  +} {1     0}
           97  +do_execsql_test 2.2.2 {
           98  +  SELECT b=='2', rank() OVER (ORDER BY a) FROM t1
           99  +} {1 1   0 2}
          100  +
          101  +finish_test
          102  +