SQLite

View Ticket
Login
Plaintext
2019-10-10
15:48
Be sure to rewrite column references inside FILTER clauses and window frame definitions when flattening queries. Fix for [1079ad19]. (check-in: df2060f3 user: drh tags: branch-3.30)
15:17 • Closed ticket [1079ad19]: Crash in query with FILTER clause on normal aggregate that refers to a column of a subquery plus 3 other changes (artifact: c408da84 user: dan)
15:17
Be sure to rewrite column references inside FILTER clauses and window frame definitions when flattening queries. Fix for [1079ad19]. (check-in: ccba7202 user: dan tags: trunk)
14:51 • Ticket [1079ad19] Crash in query with FILTER clause on normal aggregate that refers to a column of a subquery status still Open with 6 other changes (artifact: 322e6e80 user: dan)
13:58 • New ticket [1079ad19]. (artifact: 70a97bc0 user: dan)
Ticket Hash: 1079ad19993d13fa11ba5ac635ce007ca7f69015
Title: Crash in query with FILTER clause on normal aggregate that refers to a column of a subquery
Status: Closed Type: Code_Defect
Severity: Critical Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-10-10 15:17:23
Version Found In: 3.30
User Comments:
dan added on 2019-10-10 13:58:01:

Example: 

CREATE TABLE item (id int, price int);
INSERT INTO item (id, price) VALUES (1, 1);
SELECT COUNT(id) FILTER (WHERE double_price > 42) FROM (
  SELECT id, (price * 2) as double_price FROM item
);

This only affects 3.30.0, the only version to date that supports FILTER on normal (not window) aggregate function invocations.

dan added on 2019-10-10 14:51:32:

The following has a similar underlying cause, but requires a window function, not just a normal aggregate. This variant goes back to version 3.25.0. 

  CREATE TABLE t1(a, b);
  CREATE TABLE t2(c, d);
  CREATE TABLE t3(e, f);

  INSERT INTO t1 VALUES(1, 1);
  INSERT INTO t2 VALUES(1, 1);
  INSERT INTO t3 VALUES(1, 1);

  SELECT d IN (SELECT sum(c) OVER (ORDER BY e+c) FROM t3) FROM (
    SELECT * FROM t2
  );