SQLite

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.

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>