SQLite

Check-in [3cc2b570]
Login

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

Overview
Comment:When computing dependencies on WHERE-clause terms, be sure to take into account the FILTER clause of aggregate functions. Problem reported by Manuel Rigger.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 3cc2b5709e66ef605c3b85ed13ae6cc9c3090e864ebc18859db36284b8f715b6
User & Date: drh 2019-12-20 15:35:56
Context
2019-12-20
17:25
Debugging improvement: when tracing the VDBE, output the register value for OP_Cast after the cast is completed. (check-in: dc5f1d28 user: drh tags: trunk)
15:35
When computing dependencies on WHERE-clause terms, be sure to take into account the FILTER clause of aggregate functions. Problem reported by Manuel Rigger. (check-in: 3cc2b570 user: drh tags: trunk)
14:37
Improved assert() statements on the OP_Column opcode. (check-in: c538601e user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/whereexpr.c.

︙︙
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521

1522
1523
1524
1525
1526
1527
1528

  }else if( ExprHasProperty(p, EP_xIsSelect) ){
    if( ExprHasProperty(p, EP_VarSelect) ) pMaskSet->bVarSelect = 1;
    mask |= exprSelectUsage(pMaskSet, p->x.pSelect);
  }else if( p->x.pList ){
    mask |= sqlite3WhereExprListUsage(pMaskSet, p->x.pList);
  }
#ifndef SQLITE_OMIT_WINDOWFUNC
  if( p->op==TK_FUNCTION && p->y.pWin ){
    mask |= sqlite3WhereExprListUsage(pMaskSet, p->y.pWin->pPartition);
    mask |= sqlite3WhereExprListUsage(pMaskSet, p->y.pWin->pOrderBy);

  }
#endif
  return mask;
}
Bitmask sqlite3WhereExprUsage(WhereMaskSet *pMaskSet, Expr *p){
  return p ? sqlite3WhereExprUsageNN(pMaskSet,p) : 0;
}








|


>








1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529

  }else if( ExprHasProperty(p, EP_xIsSelect) ){
    if( ExprHasProperty(p, EP_VarSelect) ) pMaskSet->bVarSelect = 1;
    mask |= exprSelectUsage(pMaskSet, p->x.pSelect);
  }else if( p->x.pList ){
    mask |= sqlite3WhereExprListUsage(pMaskSet, p->x.pList);
  }
#ifndef SQLITE_OMIT_WINDOWFUNC
  if( (p->op==TK_FUNCTION || p->op==TK_AGG_FUNCTION) && p->y.pWin ){
    mask |= sqlite3WhereExprListUsage(pMaskSet, p->y.pWin->pPartition);
    mask |= sqlite3WhereExprListUsage(pMaskSet, p->y.pWin->pOrderBy);
    mask |= sqlite3WhereExprUsage(pMaskSet, p->y.pWin->pFilter);
  }
#endif
  return mask;
}
Bitmask sqlite3WhereExprUsage(WhereMaskSet *pMaskSet, Expr *p){
  return p ? sqlite3WhereExprUsageNN(pMaskSet,p) : 0;
}
︙︙

Changes to test/window1.test.

︙︙
1325
1326
1327
1328
1329
1330
1331


1332














1333
1334

} {}
do_execsql_test 37.20 {
  DROP VIEW v0;
  CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1234))) FROM t0;
  SELECT c FROM v0 WHERE c BETWEEN -10 AND 20;
} {}



















finish_test








>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>



1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350

} {}
do_execsql_test 37.20 {
  DROP VIEW v0;
  CREATE VIEW v0(c) AS SELECT max((SELECT count(a)OVER(ORDER BY 1234))) FROM t0;
  SELECT c FROM v0 WHERE c BETWEEN -10 AND 20;
} {}

# 2019-20-20 mrigger reported problem with a FILTER clause on an aggregate
# in a join.
#
reset_db
do_execsql_test 38.10 {
  CREATE TABLE t0(c0);
  CREATE TABLE t1(c0, c1 UNIQUE);
  INSERT INTO t0(c0) VALUES(1);
  INSERT INTO t1(c0,c1) VALUES(2,3);
  SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(0) FILTER(WHERE t1.c1));
} {0}
do_execsql_test 38.20 {
  SELECT COUNT(*), AVG(1) FILTER(WHERE t1.c1) FROM t0, t1;
} {1 1.0}
do_execsql_test 38.30 {
  SELECT COUNT(*) FROM t0, t1 WHERE (SELECT AVG(1) FILTER(WHERE t1.c1));
} {1}

finish_test