/ Check-in [0387cb3a]
Login

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

Overview
Comment:Fix problems with sub-selects in WINDOW definitions. Also rename-column operations when the column being renamed appears in a WINDOW definition that is part of a VIEW or TRIGGER.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 0387cb3add992b2028efe4f2100188d8f9fdfdcb233329857aa4b46a293cfc97
User & Date: dan 2019-01-23 16:59:24
Context
2019-01-23
19:17
Fix another fts5 crash that can occur if the database is corrupted. check-in: 44ce8baa user: dan tags: trunk
16:59
Fix problems with sub-selects in WINDOW definitions. Also rename-column operations when the column being renamed appears in a WINDOW definition that is part of a VIEW or TRIGGER. check-in: 0387cb3a user: dan tags: trunk
12:19
Fix a buffer overwrite triggered by a prefix query on a corrupt fts5 table. check-in: 1d8172a9 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  5362   5362   void sqlite3ExprAnalyzeAggregates(NameContext *pNC, Expr *pExpr){
  5363   5363     Walker w;
  5364   5364     w.xExprCallback = analyzeAggregate;
  5365   5365     w.xSelectCallback = analyzeAggregatesInSelect;
  5366   5366     w.xSelectCallback2 = analyzeAggregatesInSelectEnd;
  5367   5367     w.walkerDepth = 0;
  5368   5368     w.u.pNC = pNC;
         5369  +  w.pParse = 0;
  5369   5370     assert( pNC->pSrcList!=0 );
  5370   5371     sqlite3WalkExpr(&w, pExpr);
  5371   5372   }
  5372   5373   
  5373   5374   /*
  5374   5375   ** Call sqlite3ExprAnalyzeAggregates() for every expression in an
  5375   5376   ** expression list.  Return the number of errors.

Changes to src/resolve.c.

   854    854           }
   855    855         }
   856    856         sqlite3WalkExprList(pWalker, pList);
   857    857         if( is_agg ){
   858    858   #ifndef SQLITE_OMIT_WINDOWFUNC
   859    859           if( pExpr->y.pWin ){
   860    860             Select *pSel = pNC->pWinSelect;
          861  +          sqlite3WindowUpdate(pParse, pSel->pWinDefn, pExpr->y.pWin, pDef);
   861    862             sqlite3WalkExprList(pWalker, pExpr->y.pWin->pPartition);
   862    863             sqlite3WalkExprList(pWalker, pExpr->y.pWin->pOrderBy);
   863    864             sqlite3WalkExpr(pWalker, pExpr->y.pWin->pFilter);
   864         -          sqlite3WindowUpdate(pParse, pSel->pWinDefn, pExpr->y.pWin, pDef);
   865    865             if( 0==pSel->pWin 
   866    866              || 0==sqlite3WindowCompare(pParse, pSel->pWin, pExpr->y.pWin) 
   867    867             ){
   868    868               pExpr->y.pWin->pNextWin = pSel->pWin;
   869    869               pSel->pWin = pExpr->y.pWin;
   870    870             }
   871    871             pNC->ncFlags |= NC_AllowWin;
................................................................................
  1531   1531           if( ExprHasProperty(pItem->pExpr, EP_Agg) ){
  1532   1532             sqlite3ErrorMsg(pParse, "aggregate functions are not allowed in "
  1533   1533                 "the GROUP BY clause");
  1534   1534             return WRC_Abort;
  1535   1535           }
  1536   1536         }
  1537   1537       }
         1538  +
         1539  +    if( IN_RENAME_OBJECT ){
         1540  +      Window *pWin;
         1541  +      for(pWin=p->pWinDefn; pWin; pWin=pWin->pNextWin){
         1542  +        if( sqlite3ResolveExprListNames(&sNC, pWin->pOrderBy)
         1543  +         || sqlite3ResolveExprListNames(&sNC, pWin->pPartition)
         1544  +        ){
         1545  +          return WRC_Abort;
         1546  +        }
         1547  +      }
         1548  +    }
  1538   1549   
  1539   1550       /* If this is part of a compound SELECT, check that it has the right
  1540   1551       ** number of expressions in the select list. */
  1541   1552       if( p->pNext && p->pEList->nExpr!=p->pNext->pEList->nExpr ){
  1542   1553         sqlite3SelectWrongNumTermsError(pParse, p->pNext);
  1543   1554         return WRC_Abort;
  1544   1555       }

Changes to src/walker.c.

    13     13   ** an SQL statement.
    14     14   */
    15     15   #include "sqliteInt.h"
    16     16   #include <stdlib.h>
    17     17   #include <string.h>
    18     18   
    19     19   
           20  +#if !defined(SQLITE_OMIT_WINDOWFUNC)
           21  +/*
           22  +** Walk all expressions linked into the list of Window objects passed
           23  +** as the second argument.
           24  +*/
           25  +static int walkWindowList(Walker *pWalker, Window *pList){
           26  +  Window *pWin;
           27  +  for(pWin=pList; pWin; pWin=pWin->pNextWin){
           28  +    if( sqlite3WalkExprList(pWalker, pWin->pOrderBy) ) return WRC_Abort;
           29  +    if( sqlite3WalkExprList(pWalker, pWin->pPartition) ) return WRC_Abort;
           30  +    if( sqlite3WalkExpr(pWalker, pWin->pFilter) ) return WRC_Abort;
           31  +  }
           32  +  return WRC_Continue;
           33  +}
           34  +#endif
           35  +
    20     36   /*
    21     37   ** Walk an expression tree.  Invoke the callback once for each node
    22     38   ** of the expression, while descending.  (In other words, the callback
    23     39   ** is invoked before visiting children.)
    24     40   **
    25     41   ** The return value from the callback should be one of the WRC_*
    26     42   ** constants to specify how to proceed with the walk.
................................................................................
    52     68         }else if( ExprHasProperty(pExpr, EP_xIsSelect) ){
    53     69           if( sqlite3WalkSelect(pWalker, pExpr->x.pSelect) ) return WRC_Abort;
    54     70         }else if( pExpr->x.pList ){
    55     71           if( sqlite3WalkExprList(pWalker, pExpr->x.pList) ) return WRC_Abort;
    56     72         }
    57     73   #ifndef SQLITE_OMIT_WINDOWFUNC
    58     74         if( ExprHasProperty(pExpr, EP_WinFunc) ){
    59         -        Window *pWin = pExpr->y.pWin;
    60         -        if( sqlite3WalkExprList(pWalker, pWin->pPartition) ) return WRC_Abort;
    61         -        if( sqlite3WalkExprList(pWalker, pWin->pOrderBy) ) return WRC_Abort;
    62         -        if( sqlite3WalkExpr(pWalker, pWin->pFilter) ) return WRC_Abort;
           75  +        if( walkWindowList(pWalker, pExpr->y.pWin) ) return WRC_Abort;
    63     76         }
    64     77   #endif
    65     78       }
    66     79       break;
    67     80     }
    68     81     return WRC_Continue;
    69     82   }
................................................................................
    95    108   int sqlite3WalkSelectExpr(Walker *pWalker, Select *p){
    96    109     if( sqlite3WalkExprList(pWalker, p->pEList) ) return WRC_Abort;
    97    110     if( sqlite3WalkExpr(pWalker, p->pWhere) ) return WRC_Abort;
    98    111     if( sqlite3WalkExprList(pWalker, p->pGroupBy) ) return WRC_Abort;
    99    112     if( sqlite3WalkExpr(pWalker, p->pHaving) ) return WRC_Abort;
   100    113     if( sqlite3WalkExprList(pWalker, p->pOrderBy) ) return WRC_Abort;
   101    114     if( sqlite3WalkExpr(pWalker, p->pLimit) ) return WRC_Abort;
          115  +#if !defined(SQLITE_OMIT_WINDOWFUNC) && !defined(SQLITE_OMIT_ALTERTABLE)
          116  +  {
          117  +    Parse *pParse = pWalker->pParse;
          118  +    if( pParse && IN_RENAME_OBJECT ){
          119  +      if( walkWindowList(pWalker, p->pWinDefn) ) return WRC_Abort;
          120  +    }
          121  +  }
          122  +#endif
   102    123     return WRC_Continue;
   103    124   }
   104    125   
   105    126   /*
   106    127   ** Walk the parse trees associated with all subqueries in the
   107    128   ** FROM clause of SELECT statement p.  Do not invoke the select
   108    129   ** callback on p, but do invoke it on each FROM clause subquery

Changes to test/altertab2.test.

   178    178     END}
   179    179   }
   180    180   
   181    181   #-------------------------------------------------------------------------
   182    182   do_execsql_test 5.0 {
   183    183     CREATE TABLE t2(a);
   184    184     CREATE TRIGGER r2 AFTER INSERT ON t2 WHEN new.a NOT NULL BEGIN
   185         -    SELECT a, rank() OVER w1 FROM t2
          185  +    SELECT a, sum(a) OVER w1 FROM t2
   186    186         WINDOW w1 AS (
   187         -        PARTITION BY b ORDER BY d ROWS BETWEEN 2 PRECEDING AND a FOLLOWING
          187  +        PARTITION BY a ORDER BY a 
          188  +        ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
   188    189         ),
   189    190         w2 AS (
   190         -        PARTITION BY b 
   191         -        ORDER BY d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
          191  +        PARTITION BY a
          192  +        ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   192    193         );
   193    194     END;
   194    195   } {}
   195    196   
   196         -do_catchsql_test 5.0.1 {
          197  +do_execsql_test 5.0.1 {
   197    198     INSERT INTO t2 VALUES(1);
   198         -} {1 {no such column: b}}
          199  +} {}
   199    200   
   200    201   do_execsql_test 5.1 {
   201    202     ALTER TABLE t2 RENAME TO t2x;
   202    203     SELECT sql FROM sqlite_master WHERE name = 'r2';
   203    204   } {
   204    205     {CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.a NOT NULL BEGIN
   205         -    SELECT a, rank() OVER w1 FROM "t2x"
          206  +    SELECT a, sum(a) OVER w1 FROM "t2x"
   206    207         WINDOW w1 AS (
   207         -        PARTITION BY b ORDER BY d ROWS BETWEEN 2 PRECEDING AND a FOLLOWING
          208  +        PARTITION BY a ORDER BY a 
          209  +        ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
   208    210         ),
   209    211         w2 AS (
   210         -        PARTITION BY b 
   211         -        ORDER BY d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
          212  +        PARTITION BY a
          213  +        ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   212    214         );
   213    215     END}
   214    216   }
   215    217   
   216    218   do_execsql_test 5.2 {
   217    219     ALTER TABLE t2x RENAME a TO aaaa;
   218    220     SELECT sql FROM sqlite_master WHERE name = 'r2';
   219    221   } {
   220    222     {CREATE TRIGGER r2 AFTER INSERT ON "t2x" WHEN new.aaaa NOT NULL BEGIN
   221         -    SELECT aaaa, rank() OVER w1 FROM "t2x"
          223  +    SELECT aaaa, sum(aaaa) OVER w1 FROM "t2x"
   222    224         WINDOW w1 AS (
   223         -        PARTITION BY b ORDER BY d ROWS BETWEEN 2 PRECEDING AND a FOLLOWING
          225  +        PARTITION BY aaaa ORDER BY aaaa 
          226  +        ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING
   224    227         ),
   225    228         w2 AS (
   226         -        PARTITION BY b 
   227         -        ORDER BY d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
          229  +        PARTITION BY aaaa
          230  +        ORDER BY rowid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   228    231         );
   229    232     END}
   230    233   }
   231    234   
   232         -do_catchsql_test 5.3 {
          235  +do_execsql_test 5.3 {
   233    236     INSERT INTO t2x VALUES(1);
   234         -} {1 {no such column: b}}
          237  +} {}
   235    238   
   236    239   #-------------------------------------------------------------------------
   237    240   
   238    241   do_execsql_test 6.0 {
   239    242     CREATE TABLE t3(a,b,c,d);
   240    243     CREATE TRIGGER r3 AFTER INSERT ON t3 WHEN new.a NOT NULL BEGIN
   241    244       SELECT a,b,c FROM t3 EXCEPT SELECT a,b,c FROM t3 ORDER BY a;

Added test/altertab3.test.

            1  +# 2019 January 23
            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  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +set testprefix altertab3
           16  +
           17  +# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
           18  +ifcapable !altertable {
           19  +  finish_test
           20  +  return
           21  +}
           22  +
           23  +do_execsql_test 1.0 {
           24  +  CREATE TABLE t1(a, b);
           25  +  CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
           26  +    SELECT sum(b) OVER w FROM t1 WINDOW w AS (ORDER BY a);
           27  +  END;
           28  +}
           29  +
           30  +do_execsql_test 1.1 {
           31  +  ALTER TABLE t1 RENAME a TO aaa;
           32  +}
           33  +
           34  +do_execsql_test 1.2 {
           35  +  SELECT sql FROM sqlite_master WHERE name='tr1'
           36  +} {{CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
           37  +    SELECT sum(b) OVER w FROM t1 WINDOW w AS (ORDER BY aaa);
           38  +  END}}
           39  +
           40  +do_execsql_test 1.3 {
           41  +  INSERT INTO t1 VALUES(1, 2);
           42  +}
           43  +
           44  +
           45  +finish_test
           46  +
           47  +

Changes to test/window1.test.

   668    668   do_execsql_test 15.2 {
   669    669     SELECT(
   670    670       WITH c AS(
   671    671         VALUES(1)
   672    672       ) SELECT '' FROM c,c
   673    673     ) x WHERE x+x;
   674    674   } {}
          675  +
          676  +#-------------------------------------------------------------------------
          677  +
          678  +do_execsql_test 16.0 {
          679  +  CREATE TABLE t7(a,b); 
          680  +  INSERT INTO t7(rowid, a, b) VALUES
          681  +      (1, 1, 3),
          682  +      (2, 10, 4),
          683  +      (3, 100, 2);
          684  +}
          685  +
          686  +do_execsql_test 16.1 {
          687  +  SELECT rowid, sum(a) OVER (PARTITION BY b IN (SELECT rowid FROM t7)) FROM t7;
          688  +} {
          689  +  2 10
          690  +  1 101
          691  +  3 101
          692  +}
          693  +
          694  +do_execsql_test 16.2 {
          695  +  SELECT rowid, sum(a) OVER w1 FROM t7 
          696  +  WINDOW w1 AS (PARTITION BY b IN (SELECT rowid FROM t7));
          697  +} {
          698  +  2 10
          699  +  1 101
          700  +  3 101
          701  +}
          702  +
   675    703   
   676    704   finish_test