SQLite

Check-in [c34fd9fe]
Login

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

Overview
Comment:Fix a weird corner case in aggregate function processing that results from the recent addition of support for index expressions on aggregate queries. Forum post bad532820c.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: c34fd9fe1b76e0a5943f014f46141cbe55d41bb1e6980adf9bcb6785a03e7883
User & Date: drh 2023-03-28 16:02:28
Context
2023-03-29
11:36
Enhance PRAGMA integrity_check so that it can detect that a NOT NULL column contains a NaN value and report that as an error. dbsqlfuzz f144b642fe6f1a1c196f258ac6e60118a0cb59b2. (check-in: 7638d975 user: drh tags: trunk)
00:05
Modify the OP_IsType opcode so that it does not need to distinguish between NaN and other floating point values. Later: This branch causes an unnecessary slowdown of PRAGMA integrity_check. Check-in [7638d9755dc90fd3] does a better job of detecting when a NaN value occurs in a NOT NULL column. (Closed-Leaf check-in: 242cb36c user: drh tags: istype-opcode-refactor)
2023-03-28
16:13
Fix a weird corner case in aggregate function processing that results from the recent addition of support for index expressions on aggregate queries. (check-in: 36fd948e user: drh tags: branch-3.41)
16:02
Fix a weird corner case in aggregate function processing that results from the recent addition of support for index expressions on aggregate queries. Forum post bad532820c. (check-in: c34fd9fe user: drh tags: trunk)
11:18
Fix multiple problems with RETURNING on a DML statement against a view, all inspired by forum post dc3b92cfa0. (1) Do not allow a RETURNING clause to trick the code generator into thinking that the view being updated has an INSTEAD OF trigger. (2) Generate all result columns for a view in a DML statement. (3) The automatic covering index for a view should cover all result columns of the view. (check-in: c8bedef0 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
6282
6283
6284
6285
6286
6287
6288

6289
6290
6291

6292
6293
6294
6295
6296
6297
6298
6299
6300
6301

6302
6303
6304
6305
6306
6307
6308
  if( ALWAYS(!ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced))
   && pExpr->pAggInfo!=0
  ){
    AggInfo *pAggInfo = pExpr->pAggInfo;
    int iAgg = pExpr->iAgg;
    Parse *pParse = pWalker->pParse;
    sqlite3 *db = pParse->db;

    if( pExpr->op!=TK_AGG_FUNCTION ){
      assert( iAgg>=0 && iAgg<pAggInfo->nColumn );
      if( pAggInfo->aCol[iAgg].pCExpr==pExpr ){

        pExpr = sqlite3ExprDup(db, pExpr, 0);
        if( pExpr ){
          pAggInfo->aCol[iAgg].pCExpr = pExpr;
          sqlite3ExprDeferredDelete(pParse, pExpr);
        }
      }
    }else{
      assert( pExpr->op==TK_AGG_FUNCTION );
      assert( iAgg>=0 && iAgg<pAggInfo->nFunc );
      if( pAggInfo->aFunc[iAgg].pFExpr==pExpr ){

        pExpr = sqlite3ExprDup(db, pExpr, 0);
        if( pExpr ){
          pAggInfo->aFunc[iAgg].pFExpr = pExpr;
          sqlite3ExprDeferredDelete(pParse, pExpr);
        }
      }
    }







>

|
|
>








|
|
>







6282
6283
6284
6285
6286
6287
6288
6289
6290
6291
6292
6293
6294
6295
6296
6297
6298
6299
6300
6301
6302
6303
6304
6305
6306
6307
6308
6309
6310
6311
  if( ALWAYS(!ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced))
   && pExpr->pAggInfo!=0
  ){
    AggInfo *pAggInfo = pExpr->pAggInfo;
    int iAgg = pExpr->iAgg;
    Parse *pParse = pWalker->pParse;
    sqlite3 *db = pParse->db;
    assert( iAgg>=0 );
    if( pExpr->op!=TK_AGG_FUNCTION ){
      if( iAgg<pAggInfo->nColumn
       && pAggInfo->aCol[iAgg].pCExpr==pExpr
      ){
        pExpr = sqlite3ExprDup(db, pExpr, 0);
        if( pExpr ){
          pAggInfo->aCol[iAgg].pCExpr = pExpr;
          sqlite3ExprDeferredDelete(pParse, pExpr);
        }
      }
    }else{
      assert( pExpr->op==TK_AGG_FUNCTION );
      if( ALWAYS(iAgg<pAggInfo->nFunc)
       && pAggInfo->aFunc[iAgg].pFExpr==pExpr
      ){
        pExpr = sqlite3ExprDup(db, pExpr, 0);
        if( pExpr ){
          pAggInfo->aFunc[iAgg].pFExpr = pExpr;
          sqlite3ExprDeferredDelete(pParse, pExpr);
        }
      }
    }
Changes to test/window1.test.
2272
2273
2274
2275
2276
2277
2278







































2279
2280
do_catchsql_test 73.6 {
  UPDATE t2 SET c=99 WHERE b=4 RETURNING *;
} {1 {cannot modify t2 because it is a view}}
do_catchsql_test 73.7 {
  UPDATE t2 SET c=nth_value(15,2) OVER() FROM (SELECT * FROM t1) WHERE b=4 RETURNING *;
} {1 {cannot modify t2 because it is a view}}









































finish_test







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


2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
do_catchsql_test 73.6 {
  UPDATE t2 SET c=99 WHERE b=4 RETURNING *;
} {1 {cannot modify t2 because it is a view}}
do_catchsql_test 73.7 {
  UPDATE t2 SET c=nth_value(15,2) OVER() FROM (SELECT * FROM t1) WHERE b=4 RETURNING *;
} {1 {cannot modify t2 because it is a view}}

# 2023-03-28 https://sqlite.org/forum/forumpost/bad532820c
#
reset_db
do_execsql_test 74.0 {
  CREATE TABLE t1 (a INT, b INT);
  CREATE TABLE t2 (c INT, d INT);
  CREATE INDEX idx ON t1(abs(a));
  INSERT INTO t1 VALUES(1,2),(3,4);
  INSERT INTO t2 VALUES(5,6),(7,8);
}
do_execsql_test 74.1 {
  SELECT (
    SELECT count( a ) FROM t2 LIMIT 1
  )
  FROM t1;
} {2}  ;# Verified using PG 14.2
do_execsql_test 74.2 {
  SELECT (
    SELECT count( a+c ) FROM t2 LIMIT 1
  )
  FROM t1;
} {2 2}  ;# verified on PG 14.2.  Crashes PG 9.6!
do_execsql_test 74.3 {
  SELECT (
    SELECT count( ( SELECT(sum(0) OVER(ORDER BY c, abs(a))) ) )
      FROM t2 GROUP BY c LIMIT 1
  ) 
  FROM t1;
} {1 1}  ;# verified on PG 14.2
do_execsql_test 74.4 {
  /* Original test case reported in https://sqlite.org/forum/forumpost/bad532820c
  CREATE TABLE v0 (c1);
  CREATE INDEX i ON v0 (c1, c1=1);
  SELECT 0 FROM v0 AS a1
   WHERE (SELECT count((SELECT(sum(0) OVER(PARTITION BY(c1), (a1.c1=1) ))))
            FROM v0
           GROUP BY hex(0))
     AND a1.c1=0;
} {}

finish_test