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> |