/ Check-in [4c6cd54a]
Login

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

Overview
Comment:Fix problems with refering to CTEs from within sub-selects in PARTITION BY or ORDER BY clauses of window frame definitions. Also a problem with renaming a column when the schema contains a trigger containing a correlated sub-select within a window frames PARTITION BY or ORDER BY clause.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 4c6cd54a8db78e5535912e76856bed4f797261aaca4248c69d2e2452194de297
User & Date: dan 2021-05-17 16:20:41
Context
2021-05-17
16:54
Enhance the integer-comparison optimization on the OP_Eq and similar opcodes so that it avoids a lot of useless work. (check-in: 4221f41a user: drh tags: trunk)
16:20
Fix problems with refering to CTEs from within sub-selects in PARTITION BY or ORDER BY clauses of window frame definitions. Also a problem with renaming a column when the schema contains a trigger containing a correlated sub-select within a window frames PARTITION BY or ORDER BY clause. (check-in: 4c6cd54a user: dan tags: trunk)
13:11
When deleting an SQL function that does not exist, return without doing anything at all rather than creating a tombstone function. In this way, function deletes that happen inside virtual-table destructors that are run when a database connection is closing do not create new tombstones in the function table after the function table has already been purged. forum post 726219164b. (check-in: 391c7313 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

1710
1711
1712
1713
1714
1715
1716













1717
1718
1719
1720
1721
1722
1723
      SrcItem *pItem = &p->pSrc->a[i];
      if( pItem->fg.isTabFunc
       && sqlite3ResolveExprListNames(&sNC, pItem->u1.pFuncArg) 
      ){
        return WRC_Abort;
      }
    }














    /* The ORDER BY and GROUP BY clauses may not refer to terms in
    ** outer queries 
    */
    sNC.pNext = 0;
    sNC.ncFlags |= NC_AllowAgg|NC_AllowWin;








>
>
>
>
>
>
>
>
>
>
>
>
>







1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
      SrcItem *pItem = &p->pSrc->a[i];
      if( pItem->fg.isTabFunc
       && sqlite3ResolveExprListNames(&sNC, pItem->u1.pFuncArg) 
      ){
        return WRC_Abort;
      }
    }

#ifndef SQLITE_OMIT_WINDOWFUNC
    if( IN_RENAME_OBJECT ){
      Window *pWin;
      for(pWin=p->pWinDefn; pWin; pWin=pWin->pNextWin){
        if( sqlite3ResolveExprListNames(&sNC, pWin->pOrderBy)
         || sqlite3ResolveExprListNames(&sNC, pWin->pPartition)
        ){
          return WRC_Abort;
        }
      }
    }
#endif

    /* The ORDER BY and GROUP BY clauses may not refer to terms in
    ** outer queries 
    */
    sNC.pNext = 0;
    sNC.ncFlags |= NC_AllowAgg|NC_AllowWin;

1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
          sqlite3ErrorMsg(pParse, "aggregate functions are not allowed in "
              "the GROUP BY clause");
          return WRC_Abort;
        }
      }
    }

#ifndef SQLITE_OMIT_WINDOWFUNC
    if( IN_RENAME_OBJECT ){
      Window *pWin;
      for(pWin=p->pWinDefn; pWin; pWin=pWin->pNextWin){
        if( sqlite3ResolveExprListNames(&sNC, pWin->pOrderBy)
         || sqlite3ResolveExprListNames(&sNC, pWin->pPartition)
        ){
          return WRC_Abort;
        }
      }
    }
#endif

    /* If this is part of a compound SELECT, check that it has the right
    ** number of expressions in the select list. */
    if( p->pNext && p->pEList->nExpr!=p->pNext->pEList->nExpr ){
      sqlite3SelectWrongNumTermsError(pParse, p->pNext);
      return WRC_Abort;
    }








<
<
<
<
<
<
<
<
<
<
<
<
<







1780
1781
1782
1783
1784
1785
1786













1787
1788
1789
1790
1791
1792
1793
          sqlite3ErrorMsg(pParse, "aggregate functions are not allowed in "
              "the GROUP BY clause");
          return WRC_Abort;
        }
      }
    }














    /* If this is part of a compound SELECT, check that it has the right
    ** number of expressions in the select list. */
    if( p->pNext && p->pEList->nExpr!=p->pNext->pEList->nExpr ){
      sqlite3SelectWrongNumTermsError(pParse, p->pNext);
      return WRC_Abort;
    }

Changes to src/select.c.

5209
5210
5211
5212
5213
5214
5215
5216
5217
5218
5219
5220
5221
5222
5223
5224
5225
5226
5227
5228
5229
5230
5231
5232
5233
5234
** If the SELECT passed as the second argument has an associated WITH 
** clause, pop it from the stack stored as part of the Parse object.
**
** This function is used as the xSelectCallback2() callback by
** sqlite3SelectExpand() when walking a SELECT tree to resolve table
** names and other FROM clause elements. 
*/
static void selectPopWith(Walker *pWalker, Select *p){
  Parse *pParse = pWalker->pParse;
  if( OK_IF_ALWAYS_TRUE(pParse->pWith) && p->pPrior==0 ){
    With *pWith = findRightmost(p)->pWith;
    if( pWith!=0 ){
      assert( pParse->pWith==pWith || pParse->nErr );
      pParse->pWith = pWith->pOuter;
    }
  }
}
#else
#define selectPopWith 0
#endif

/*
** The SrcList_item structure passed as the second argument represents a
** sub-query in the FROM clause of a SELECT statement. This function
** allocates and populates the SrcList_item.pTab object. If successful,
** SQLITE_OK is returned. Otherwise, if an OOM error is encountered,







|









<
<







5209
5210
5211
5212
5213
5214
5215
5216
5217
5218
5219
5220
5221
5222
5223
5224
5225


5226
5227
5228
5229
5230
5231
5232
** If the SELECT passed as the second argument has an associated WITH 
** clause, pop it from the stack stored as part of the Parse object.
**
** This function is used as the xSelectCallback2() callback by
** sqlite3SelectExpand() when walking a SELECT tree to resolve table
** names and other FROM clause elements. 
*/
void sqlite3SelectPopWith(Walker *pWalker, Select *p){
  Parse *pParse = pWalker->pParse;
  if( OK_IF_ALWAYS_TRUE(pParse->pWith) && p->pPrior==0 ){
    With *pWith = findRightmost(p)->pWith;
    if( pWith!=0 ){
      assert( pParse->pWith==pWith || pParse->nErr );
      pParse->pWith = pWith->pOuter;
    }
  }
}


#endif

/*
** The SrcList_item structure passed as the second argument represents a
** sub-query in the FROM clause of a SELECT statement. This function
** allocates and populates the SrcList_item.pTab object. If successful,
** SQLITE_OK is returned. Otherwise, if an OOM error is encountered,
5607
5608
5609
5610
5611
5612
5613
5614
5615
5616
5617
5618
5619
5620
5621
  w.pParse = pParse;
  if( OK_IF_ALWAYS_TRUE(pParse->hasCompound) ){
    w.xSelectCallback = convertCompoundSelectToSubquery;
    w.xSelectCallback2 = 0;
    sqlite3WalkSelect(&w, pSelect);
  }
  w.xSelectCallback = selectExpander;
  w.xSelectCallback2 = selectPopWith;
  w.eCode = 0;
  sqlite3WalkSelect(&w, pSelect);
}


#ifndef SQLITE_OMIT_SUBQUERY
/*







|







5605
5606
5607
5608
5609
5610
5611
5612
5613
5614
5615
5616
5617
5618
5619
  w.pParse = pParse;
  if( OK_IF_ALWAYS_TRUE(pParse->hasCompound) ){
    w.xSelectCallback = convertCompoundSelectToSubquery;
    w.xSelectCallback2 = 0;
    sqlite3WalkSelect(&w, pSelect);
  }
  w.xSelectCallback = selectExpander;
  w.xSelectCallback2 = sqlite3SelectPopWith;
  w.eCode = 0;
  sqlite3WalkSelect(&w, pSelect);
}


#ifndef SQLITE_OMIT_SUBQUERY
/*

Changes to src/sqliteInt.h.

3881
3882
3883
3884
3885
3886
3887






3888
3889
3890
3891
3892
3893
3894
int sqlite3WalkerDepthIncrease(Walker*,Select*);
void sqlite3WalkerDepthDecrease(Walker*,Select*);
void sqlite3WalkWinDefnDummyCallback(Walker*,Select*);

#ifdef SQLITE_DEBUG
void sqlite3SelectWalkAssert2(Walker*, Select*);
#endif







/*
** Return code from the parse-tree walking primitives and their
** callbacks.
*/
#define WRC_Continue    0   /* Continue down into children */
#define WRC_Prune       1   /* Omit children but continue walking siblings */







>
>
>
>
>
>







3881
3882
3883
3884
3885
3886
3887
3888
3889
3890
3891
3892
3893
3894
3895
3896
3897
3898
3899
3900
int sqlite3WalkerDepthIncrease(Walker*,Select*);
void sqlite3WalkerDepthDecrease(Walker*,Select*);
void sqlite3WalkWinDefnDummyCallback(Walker*,Select*);

#ifdef SQLITE_DEBUG
void sqlite3SelectWalkAssert2(Walker*, Select*);
#endif

#ifndef SQLITE_OMIT_CTE
void sqlite3SelectPopWith(Walker*, Select*);
#else
# define sqlite3SelectPopWith 0
#endif

/*
** Return code from the parse-tree walking primitives and their
** callbacks.
*/
#define WRC_Continue    0   /* Continue down into children */
#define WRC_Prune       1   /* Omit children but continue walking siblings */

Changes to src/walker.c.

136
137
138
139
140
141
142



143
144
145
146
147
148
149
  if( sqlite3WalkExprList(pWalker, p->pOrderBy) ) return WRC_Abort;
  if( sqlite3WalkExpr(pWalker, p->pLimit) ) return WRC_Abort;
#if !defined(SQLITE_OMIT_WINDOWFUNC)
  if( p->pWinDefn ){
    Parse *pParse;
    if( pWalker->xSelectCallback2==sqlite3WalkWinDefnDummyCallback
     || ((pParse = pWalker->pParse)!=0 && IN_RENAME_OBJECT)



    ){
      /* The following may return WRC_Abort if there are unresolvable
      ** symbols (e.g. a table that does not exist) in a window definition. */
      int rc = walkWindowList(pWalker, p->pWinDefn, 0);
      return rc;
    }
  }







>
>
>







136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
  if( sqlite3WalkExprList(pWalker, p->pOrderBy) ) return WRC_Abort;
  if( sqlite3WalkExpr(pWalker, p->pLimit) ) return WRC_Abort;
#if !defined(SQLITE_OMIT_WINDOWFUNC)
  if( p->pWinDefn ){
    Parse *pParse;
    if( pWalker->xSelectCallback2==sqlite3WalkWinDefnDummyCallback
     || ((pParse = pWalker->pParse)!=0 && IN_RENAME_OBJECT)
#ifndef SQLITE_OMIT_CTE
     || pWalker->xSelectCallback2==sqlite3SelectPopWith
#endif
    ){
      /* The following may return WRC_Abort if there are unresolvable
      ** symbols (e.g. a table that does not exist) in a window definition. */
      int rc = walkWindowList(pWalker, p->pWinDefn, 0);
      return rc;
    }
  }

Changes to test/altertab.test.

763
764
765
766
767
768
769
770































771
       SET (k,v)=((WITH cte1(a) AS (SELECT 1 FROM t2) SELECT t2.k FROM t2, cte1),1);
  END;
  ALTER TABLE t1 RENAME TO t1x;
  INSERT INTO t2_a VALUES(2,3);
  INSERT INTO t1x VALUES(98,99);
  SELECT * FROM t1x;
} {2 1}
































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
       SET (k,v)=((WITH cte1(a) AS (SELECT 1 FROM t2) SELECT t2.k FROM t2, cte1),1);
  END;
  ALTER TABLE t1 RENAME TO t1x;
  INSERT INTO t2_a VALUES(2,3);
  INSERT INTO t1x VALUES(98,99);
  SELECT * FROM t1x;
} {2 1}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 27.1 {

 create table t_sa (
 c_muyat INTEGER NOT NULL,
 c_d4u TEXT 
 );

 create table t2 ( abc );

 CREATE TRIGGER trig AFTER DELETE ON t_sa
   BEGIN
   DELETE FROM t_sa WHERE (
       SELECT 123 FROM t2
       WINDOW oamat7fzf AS ( PARTITION BY t_sa.c_d4u )
   );
   END;
}


breakpoint
do_execsql_test 27.2 {
  alter table t_sa rename column c_muyat to c_dg;
}





finish_test

Changes to test/window1.test.

2086
2087
2088
2089
2090
2091
2092







2093
2094
2095
2096
2097
2098
2099
}

do_catchsql_test 67.1 {
  SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY (             
      SELECT nth_value(a,2) OVER w1 
      WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM v1)) )
  )







} {1 {1st ORDER BY term does not match any column in the result set}}

# 2021-05-07
# Do not allow aggregate functions in the ORDER BY clause even if
# there are window functions in the result set.
# Forum: /forumpost/540fdfef77
#







>
>
>
>
>
>
>







2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
}

do_catchsql_test 67.1 {
  SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY (             
      SELECT nth_value(a,2) OVER w1 
      WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM v1)) )
  )
} {1 {no such table: v1}}

do_catchsql_test 67.2 {
  SELECT a,c,b FROM t1 INTERSECT SELECT a,b,c FROM t1 ORDER BY (             
      SELECT nth_value(a,2) OVER w1 
      WINDOW w1 AS ( ORDER BY ((SELECT 1 FROM t2)) )
  )
} {1 {1st ORDER BY term does not match any column in the result set}}

# 2021-05-07
# Do not allow aggregate functions in the ORDER BY clause even if
# there are window functions in the result set.
# Forum: /forumpost/540fdfef77
#

Changes to test/window8.tcl.

413
414
415
416
417
418
419
420

421




422

















































423
424
425
execsql_test 7.$tn.9 "
  SELECT $f (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
  );
"
}

























































finish_test










>

>
>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
execsql_test 7.$tn.9 "
  SELECT $f (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
  );
"
}

==========

execsql_test 8.0 {
  DROP TABLE IF EXISTS tx;
  CREATE TABLE tx(a INTEGER PRIMARY KEY);
  INSERT INTO tx VALUES(1), (2), (3), (4), (5), (6);

  DROP TABLE IF EXISTS map;
  CREATE TABLE map(v INTEGER PRIMARY KEY, t TEXT);
  INSERT INTO map VALUES
    (1, 'odd'), (2, 'even'), (3, 'odd'), 
    (4, 'even'), (5, 'odd'), (6, 'even');
}

execsql_test 8.1 {
  SELECT sum(a) OVER (
    PARTITION BY (
      SELECT t FROM map WHERE v=a
    ) ORDER BY a
  ) FROM tx;
}

execsql_test 8.2 {
  SELECT sum(a) OVER win FROM tx
  WINDOW win AS (
    PARTITION BY (
      SELECT t FROM map WHERE v=a
    ) ORDER BY a
  );
}

execsql_test 8.3 {
  WITH map2 AS (
    SELECT * FROM map
  )
  SELECT sum(a) OVER (
    PARTITION BY (
      SELECT t FROM map2 WHERE v=a
    ) ORDER BY a
  ) FROM tx;
}

execsql_test 8.4 {
  WITH map2 AS (
    SELECT * FROM map
  )
  SELECT sum(a) OVER win FROM tx
  WINDOW win AS (
    PARTITION BY (
      SELECT t FROM map2 WHERE v=a
    ) ORDER BY a
  );
}



finish_test


Changes to test/window8.test.

6464
6465
6466
6467
6468
6469
6470
6471























































6472

do_execsql_test 7.4.9 {
  SELECT max (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
  );
} {4   4   4   {}   {}   {}}
























































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

6464
6465
6466
6467
6468
6469
6470
6471
6472
6473
6474
6475
6476
6477
6478
6479
6480
6481
6482
6483
6484
6485
6486
6487
6488
6489
6490
6491
6492
6493
6494
6495
6496
6497
6498
6499
6500
6501
6502
6503
6504
6505
6506
6507
6508
6509
6510
6511
6512
6513
6514
6515
6516
6517
6518
6519
6520
6521
6522
6523
6524
6525
6526
6527

do_execsql_test 7.4.9 {
  SELECT max (a) OVER win FROM t2
  WINDOW win AS (
      ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
  );
} {4   4   4   {}   {}   {}}

#==========================================================================

do_execsql_test 8.0 {
  DROP TABLE IF EXISTS tx;
  CREATE TABLE tx(a INTEGER PRIMARY KEY);
  INSERT INTO tx VALUES(1), (2), (3), (4), (5), (6);

  DROP TABLE IF EXISTS map;
  CREATE TABLE map(v INTEGER PRIMARY KEY, t TEXT);
  INSERT INTO map VALUES
    (1, 'odd'), (2, 'even'), (3, 'odd'), 
    (4, 'even'), (5, 'odd'), (6, 'even');
} {}

do_execsql_test 8.1 {
  SELECT sum(a) OVER (
    PARTITION BY (
      SELECT t FROM map WHERE v=a
    ) ORDER BY a
  ) FROM tx;
} {2   6   12   1   4   9}

do_execsql_test 8.2 {
  SELECT sum(a) OVER win FROM tx
  WINDOW win AS (
    PARTITION BY (
      SELECT t FROM map WHERE v=a
    ) ORDER BY a
  );
} {2   6   12   1   4   9}

do_execsql_test 8.3 {
  WITH map2 AS (
    SELECT * FROM map
  )
  SELECT sum(a) OVER (
    PARTITION BY (
      SELECT t FROM map2 WHERE v=a
    ) ORDER BY a
  ) FROM tx;
} {2   6   12   1   4   9}

breakpoint
do_execsql_test 8.4 {
  WITH map2 AS (
    SELECT * FROM map
  )
  SELECT sum(a) OVER win FROM tx
  WINDOW win AS (
    PARTITION BY (
      SELECT t FROM map2 WHERE v=a
    ) ORDER BY a
  );
} {2   6   12   1   4   9}

finish_test