/ Check-in [579b66ea]
Login

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

Overview
Comment:Internally, remove all references to a Window object that belongs to an expression in an ORDER BY clause if that expression is converted to an alias of a result-set expression. Fix for [4feb3159c6].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 579b66eaa0816561c6e47ea116b46f229188f0fc84c1173bfe0d21df2dff9a9a
User & Date: dan 2019-02-22 19:24:16
Context
2019-02-25
15:55
Internally, remove all references to a Window object that belongs to an expression in an ORDER BY clause if that expression is converted to an alias of a result-set expression. check-in: a21ffcd8 user: drh tags: branch-3.27
2019-02-23
00:21
Check-in [fa792714ae62fa98] is incorrect. Add a test case to refute it and also a fix to make it right. Then add an alternative fix to ticket [df46dfb631f75694] in which all ephemeral tables used as the RHS of an IN operator be index btrees and never table btrees so that they can always be reused. check-in: d3915230 user: drh tags: trunk
2019-02-22
21:33
Check-in [fa792714ae62fa980] is not a valid fix for ticket [df46dfb631f75694], as the new test case in this check-in demonstrates. The fix here causes test cases for the [df46dfb631f75694] bug to fail again, so this check-in is on a branch. A new fix is needed for [df46dfb631f75694]. check-in: 0d456456 user: drh tags: tkt-df46dfb631
19:24
Internally, remove all references to a Window object that belongs to an expression in an ORDER BY clause if that expression is converted to an alias of a result-set expression. Fix for [4feb3159c6]. check-in: 579b66ea user: dan tags: trunk
16:18
In sqlite3NestedParse() be sure to detect all SQLITE_NOMEM and SQLITE_TOOBIG errors and to distinguish between them. check-in: 73056b31 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

  1239   1239         resolveAlias(pParse, pEList, pItem->u.x.iOrderByCol-1, pItem->pExpr,
  1240   1240                      zType,0);
  1241   1241       }
  1242   1242     }
  1243   1243     return 0;
  1244   1244   }
  1245   1245   
         1246  +#ifndef SQLITE_OMIT_WINDOWFUNC
         1247  +/*
         1248  +** Walker callback for resolveRemoveWindows().
         1249  +*/
         1250  +static int resolveRemoveWindowsCb(Walker *pWalker, Expr *pExpr){
         1251  +  if( ExprHasProperty(pExpr, EP_WinFunc) ){
         1252  +    Window **pp;
         1253  +    for(pp=&pWalker->u.pSelect->pWin; *pp; pp=&(*pp)->pNextWin){
         1254  +      if( *pp==pExpr->y.pWin ){
         1255  +        *pp = (*pp)->pNextWin;
         1256  +        break;
         1257  +      }    
         1258  +    }
         1259  +  }
         1260  +  return WRC_Continue;
         1261  +}
         1262  +
         1263  +/*
         1264  +** Remove any Window objects owned by the expression pExpr from the
         1265  +** Select.pWin list of Select object pSelect.
         1266  +*/
         1267  +static void resolveRemoveWindows(Select *pSelect, Expr *pExpr){
         1268  +  Walker sWalker;
         1269  +  memset(&sWalker, 0, sizeof(Walker));
         1270  +  sWalker.xExprCallback = resolveRemoveWindowsCb;
         1271  +  sWalker.u.pSelect = pSelect;
         1272  +  sqlite3WalkExpr(&sWalker, pExpr);
         1273  +}
         1274  +#else
         1275  +# define resolveRemoveWindows(x,y)
         1276  +#endif
         1277  +
  1246   1278   /*
  1247   1279   ** pOrderBy is an ORDER BY or GROUP BY clause in SELECT statement pSelect.
  1248   1280   ** The Name context of the SELECT statement is pNC.  zType is either
  1249   1281   ** "ORDER" or "GROUP" depending on which type of clause pOrderBy is.
  1250   1282   **
  1251   1283   ** This routine resolves each term of the clause into an expression.
  1252   1284   ** If the order-by term is an integer I between 1 and N (where N is the
................................................................................
  1305   1337       /* Otherwise, treat the ORDER BY term as an ordinary expression */
  1306   1338       pItem->u.x.iOrderByCol = 0;
  1307   1339       if( sqlite3ResolveExprNames(pNC, pE) ){
  1308   1340         return 1;
  1309   1341       }
  1310   1342       for(j=0; j<pSelect->pEList->nExpr; j++){
  1311   1343         if( sqlite3ExprCompare(0, pE, pSelect->pEList->a[j].pExpr, -1)==0 ){
  1312         -#ifndef SQLITE_OMIT_WINDOWFUNC
  1313         -        if( ExprHasProperty(pE, EP_WinFunc) ){
  1314         -          /* Since this window function is being changed into a reference
  1315         -          ** to the same window function the result set, remove the instance
  1316         -          ** of this window function from the Select.pWin list. */
  1317         -          Window **pp;
  1318         -          for(pp=&pSelect->pWin; *pp; pp=&(*pp)->pNextWin){
  1319         -            if( *pp==pE->y.pWin ){
  1320         -              *pp = (*pp)->pNextWin;
  1321         -            }    
  1322         -          }
  1323         -        }
  1324         -#endif
         1344  +        /* Since this expresion is being changed into a reference
         1345  +        ** to an identical expression in the result set, remove all Window
         1346  +        ** objects belonging to the expression from the Select.pWin list. */
         1347  +        resolveRemoveWindows(pSelect, pE);
  1325   1348           pItem->u.x.iOrderByCol = j+1;
  1326   1349         }
  1327   1350       }
  1328   1351     }
  1329   1352     return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType);
  1330   1353   }
  1331   1354   

Changes to test/window1.test.

   695    695     SELECT rowid, sum(a) OVER w1 FROM t7 
   696    696     WINDOW w1 AS (PARTITION BY b IN (SELECT rowid FROM t7));
   697    697   } {
   698    698     2 10
   699    699     1 101
   700    700     3 101
   701    701   }
          702  +
          703  +#-------------------------------------------------------------------------
          704  +do_execsql_test 17.0 {
          705  +  CREATE TABLE t8(a);
          706  +  INSERT INTO t8 VALUES(1), (2), (3);
          707  +}
          708  +
          709  +do_execsql_test 17.1 {
          710  +  SELECT +sum(0) OVER () ORDER BY +sum(0) OVER ();
          711  +} {0}
          712  +
          713  +do_execsql_test 17.2 {
          714  +  select +sum(a) OVER () FROM t8 ORDER BY +sum(a) OVER () DESC;
          715  +} {6 6 6}
          716  +
          717  +do_execsql_test 17.3 {
          718  +  SELECT 10+sum(a) OVER (ORDER BY a) 
          719  +  FROM t8 
          720  +  ORDER BY 10+sum(a) OVER (ORDER BY a) DESC;
          721  +} {16 13 11}
   702    722   
   703    723   
   704    724   finish_test