SQLite

Check-in [1ba22631a7]
Login

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

Overview
Comment:Fix a code-generator issue associated with very unusual use of window functions. Both the expr.c or the window.c changes will each independently fix the problem. They are both included in this patch for defense in depth. Forum post 0d48347967.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 1ba22631a7831e3562eda0eb6a5edf7f009c85c7ab4451d9eacd13ef0fb6036a
User & Date: drh 2023-04-13 14:50:50.896
Context
2023-04-13
15:11
Remove an ALWAYS() that can sometimes be false. And fix a code-generator issue associated with very unusual use of window functions. (check-in: fc12743763 user: drh tags: branch-3.41)
14:53
Add extra test cases to window1.test. (check-in: ebc844fbfb user: dan tags: trunk)
14:50
Fix a code-generator issue associated with very unusual use of window functions. Both the expr.c or the window.c changes will each independently fix the problem. They are both included in this patch for defense in depth. Forum post 0d48347967. (check-in: 1ba22631a7 user: drh tags: trunk)
2023-04-12
20:23
Unwrap the loop in the WAL hash function. (check-in: eb94ae1320 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
︙︙
6489
6490
6491
6492
6493
6494
6495
6496
6497
6498
6499
6500
6501
6502
6503

          assert( !ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced) );
          if( pExpr->iTable==pItem->iCursor ){
            findOrCreateAggInfoColumn(pParse, pAggInfo, pExpr);
            break;
          } /* endif pExpr->iTable==pItem->iCursor */
        } /* end loop over pSrcList */
      }
      return WRC_Prune;
    }
    case TK_AGG_FUNCTION: {
      if( (pNC->ncFlags & NC_InAggFunc)==0
       && pWalker->walkerDepth==pExpr->op2
      ){
        /* Check to see if pExpr is a duplicate of another aggregate 
        ** function that is already in the pAggInfo structure








|








6489
6490
6491
6492
6493
6494
6495
6496
6497
6498
6499
6500
6501
6502
6503

          assert( !ExprHasProperty(pExpr, EP_TokenOnly|EP_Reduced) );
          if( pExpr->iTable==pItem->iCursor ){
            findOrCreateAggInfoColumn(pParse, pAggInfo, pExpr);
            break;
          } /* endif pExpr->iTable==pItem->iCursor */
        } /* end loop over pSrcList */
      }
      return WRC_Continue;
    }
    case TK_AGG_FUNCTION: {
      if( (pNC->ncFlags & NC_InAggFunc)==0
       && pWalker->walkerDepth==pExpr->op2
      ){
        /* Check to see if pExpr is a duplicate of another aggregate 
        ** function that is already in the pAggInfo structure
︙︙
Changes to src/window.c.
︙︙
781
782
783
784
785
786
787

788
789
790
791
792
793
794

            assert( pWin->pOwner==pExpr );
            return WRC_Prune;
          }
        }
      }
      /* no break */ deliberate_fall_through


    case TK_AGG_FUNCTION:
    case TK_COLUMN: {
      int iCol = -1;
      if( pParse->db->mallocFailed ) return WRC_Abort;
      if( p->pSub ){
        int i;
        for(i=0; i<p->pSub->nExpr; i++){








>








781
782
783
784
785
786
787
788
789
790
791
792
793
794
795

            assert( pWin->pOwner==pExpr );
            return WRC_Prune;
          }
        }
      }
      /* no break */ deliberate_fall_through

    case TK_IF_NULL_ROW:
    case TK_AGG_FUNCTION:
    case TK_COLUMN: {
      int iCol = -1;
      if( pParse->db->mallocFailed ) return WRC_Abort;
      if( p->pSub ){
        int i;
        for(i=0; i<p->pSub->nExpr; i++){
︙︙
Changes to test/window1.test.
︙︙
2325
2326
2327
2328
2329
2330
2331

















2332
2333

  CREATE INDEX t1x ON t1(a+b);
}
do_catchsql_test 75.1 {
  SELECT count((SELECT count(a0.a+a0.b) ORDER BY sum(0) OVER (PARTITION BY 0)))
    FROM t1 AS a0 JOIN t1 AS a1
   GROUP BY a1.a;
} {1 {misuse of aggregate: count()}}


















finish_test








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



2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350

  CREATE INDEX t1x ON t1(a+b);
}
do_catchsql_test 75.1 {
  SELECT count((SELECT count(a0.a+a0.b) ORDER BY sum(0) OVER (PARTITION BY 0)))
    FROM t1 AS a0 JOIN t1 AS a1
   GROUP BY a1.a;
} {1 {misuse of aggregate: count()}}

# 2023-04-13 https://sqlite.org/forum/forumpost/0d48347967
reset_db
do_execsql_test 76.0 {
  CREATE TABLE t1(a INT, b INT);
  INSERT INTO t1(a,b) VALUES (111,222),(111,223),(118,229);
  CREATE INDEX t1a ON t1(a);
  CREATE TABLE t2(x INT);
  INSERT INTO t2 VALUES (333),(444),(555);
}
do_execsql_test 76.1 {
  SELECT c, (SELECT c + sum(1) OVER ()) AS "res"
    FROM t2 LEFT JOIN (SELECT +a AS c FROM t1) AS v1 ON true
   GROUP BY c
   ORDER by c;
} {111 112 118 119}
# ^^^^^^^^^^^^^^^^^-- results verified against PG 14.2

finish_test