any_rewrite.sql

DROP TABLE IF EXISTS t;

CREATE TABLE t (a INT);
INSERT INTO t VALUES (NULL), (1), (2);

SELECT 'table with NULL';

SELECT
  i,
  i = ANY (SELECT a FROM t) AS "any",
  (SELECT count(*) > 0 AND
    (bool_or(a = i) OR (
      CASE
      WHEN bool_or(a IS NULL) THEN NULL
      ELSE (CASE WHEN i IS NULL THEN NULL ELSE false END)
      END
    ))
  FROM t) AS "rewrite"
FROM (VALUES (1), (2), (NULL)) v(i);

SELECT 'table with NULL - inequality';

SELECT
  i,
  i < ANY (SELECT a FROM t) AS "any",
  (SELECT count(*) > 0 AND
    (bool_or(a > i) OR (
      CASE
      WHEN bool_or(a IS NULL) THEN NULL
      ELSE (CASE WHEN i IS NULL THEN NULL ELSE false END)
      END
    ))
  FROM t) AS "rewrite"
FROM (VALUES (1), (2), (NULL)) v(i);

SELECT 'table with NULL - tuples';

SELECT
  i,
  (i, i) = ANY (SELECT a, a FROM t) AS "any",
  (SELECT count(*) > 0 AND
    (bool_or((a, a) = (i, i)) OR (
      CASE
      WHEN bool_or((a, a) IS NULL) THEN NULL
      ELSE (CASE WHEN (i, i) IS NULL THEN NULL ELSE false END)
      END
    ))
  FROM t) AS "rewrite"
FROM (VALUES (1), (2), (NULL)) v(i);


DELETE FROM t;
INSERT INTO t VALUES (1), (-1);

SELECT 'table with no NULLs';

SELECT
  i,
  i = ANY (SELECT a FROM t) AS "any",
  (SELECT count(*) > 0 AND
    (bool_or(a = i) OR (
      CASE
      WHEN bool_or(a IS NULL) THEN NULL
      ELSE (CASE WHEN i IS NULL THEN NULL ELSE false END)
      END
    ))
  FROM t) AS "rewrite"
FROM (VALUES (1), (2), (NULL)) v(i);


SELECT 'table with no NULLS - inequality';

SELECT
  i,
  i < ANY (SELECT a FROM t) AS "any",
  (SELECT count(*) > 0 AND
    (bool_or(a > i) OR (
      CASE
      WHEN bool_or(a IS NULL) THEN NULL
      ELSE (CASE WHEN i IS NULL THEN NULL ELSE false END)
      END
    ))
  FROM t) AS "rewrite"
FROM (VALUES (1), (2), (NULL)) v(i);


SELECT 'table with no NULLS - tuples';

SELECT
  i,
  (i, i) = ANY (SELECT a, a FROM t) AS "any",
  (SELECT count(*) > 0 AND
    (bool_or((a, a) = (i, i)) OR (
      CASE
      WHEN bool_or((a, a) IS NULL) THEN NULL
      ELSE (CASE WHEN (i, i) IS NULL THEN NULL ELSE false END)
      END
    ))
  FROM t) AS "rewrite"
FROM (VALUES (1), (2), (NULL)) v(i);


DELETE FROM t;

SELECT 'empty table';

SELECT
  i,
  i = ANY (SELECT a FROM t) AS "any",
  (SELECT count(*) > 0 AND
    (bool_or(a = i) OR (
      CASE
      WHEN bool_or(a IS NULL) THEN NULL
      ELSE (CASE WHEN i IS NULL THEN NULL ELSE false END)
      END
    ))
  FROM t) AS "rewrite"
FROM (VALUES (1), (2), (NULL)) v(i);


SELECT 'empty table - inequality';

SELECT
  i,
  i < ANY (SELECT a FROM t) AS "any",
  (SELECT count(*) > 0 AND
    (bool_or(a > i) OR (
      CASE
      WHEN bool_or(a IS NULL) THEN NULL
      ELSE (CASE WHEN i IS NULL THEN NULL ELSE false END)
      END
    ))
  FROM t) AS "rewrite"
FROM (VALUES (1), (2), (NULL)) v(i);

SELECT 'empty table - tuples';

SELECT
  i,
  (i, i) = ANY (SELECT a, a FROM t) AS "any",
  (SELECT count(*) > 0 AND
    (bool_or((a, a) = (i, i)) OR (
      CASE
      WHEN bool_or((a, a) IS NULL) THEN NULL
      ELSE (CASE WHEN (i, i) IS NULL THEN NULL ELSE false END)
      END
    ))
  FROM t) AS "rewrite"
FROM (VALUES (1), (2), (NULL)) v(i);

DROP TABLE IF EXISTS t;

output

DROP TABLE
CREATE TABLE
INSERT 0 3
    ?column?
-----------------
 table with NULL
(1 row)

  i   | any  | rewrite
------+------+---------
    1 | t    | t
    2 | t    | t
 NULL | NULL | NULL
(3 rows)

           ?column?
------------------------------
 table with NULL - inequality
(1 row)

  i   | any  | rewrite
------+------+---------
    1 | t    | t
    2 | NULL | NULL
 NULL | NULL | NULL
(3 rows)

         ?column?
--------------------------
 table with NULL - tuples
(1 row)

  i   | any  | rewrite
------+------+---------
    1 | t    | t
    2 | t    | t
 NULL | NULL | NULL
(3 rows)

DELETE 3
INSERT 0 2
      ?column?
---------------------
 table with no NULLs
(1 row)

  i   | any  | rewrite
------+------+---------
    1 | t    | t
    2 | f    | f
 NULL | NULL | NULL
(3 rows)

             ?column?
----------------------------------
 table with no NULLS - inequality
(1 row)

  i   | any  | rewrite
------+------+---------
    1 | f    | f
    2 | f    | f
 NULL | NULL | NULL
(3 rows)

           ?column?
------------------------------
 table with no NULLS - tuples
(1 row)

  i   | any  | rewrite
------+------+---------
    1 | t    | t
    2 | f    | f
 NULL | NULL | NULL
(3 rows)

DELETE 2
  ?column?
-------------
 empty table
(1 row)

  i   | any | rewrite
------+-----+---------
    1 | f   | f
    2 | f   | f
 NULL | f   | f
(3 rows)

         ?column?
--------------------------
 empty table - inequality
(1 row)

  i   | any | rewrite
------+-----+---------
    1 | f   | f
    2 | f   | f
 NULL | f   | f
(3 rows)

       ?column?
----------------------
 empty table - tuples
(1 row)

  i   | any | rewrite
------+-----+---------
    1 | f   | f
    2 | f   | f
 NULL | f   | f
(3 rows)

DROP TABLE