SQLite

View Ticket
Login
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: (text/x-fossil-wiki)
Example:

<verbatim>
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
);
</verbatim>

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: (text/x-fossil-wiki)
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.

<verbatim>
  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
  );
</verbatim>