Re: Fold NOT IN / <> ALL expressions containing NULL to FALSE - Mailing list pgsql-hackers

Hi folks,
sharing some output of my saturday session with an agent. 
hope this will somehow help.

Tested v4 on master (3c5d9d9). The issues from v2 review are
fixed, but It found some new ones.

The CASE WHEN folding in eval_const_expressions_mutator also runs at
DDL time via expression_planner(). Because of that, a partition key
that master accepts is now rejected as a constant:

  CREATE TABLE pk (a int, b int) PARTITION BY LIST
    ((CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END));

  master: CREATE TABLE
  v4:     ERROR:  cannot use constant expression as partition key

So a cluster that has such a table can't be moved to v4.

pg_dump from master and restore into v4 fails:

  ERROR:  cannot use constant expression as partition key
  ERROR:  relation "public.pk" does not exist

pg_upgrade from master to v4 fails in "Restoring database schemas in
the new cluster":

  pg_restore: error: could not execute query: ERROR:  cannot use
  constant expression as partition key
  ...
  CREATE TABLE "public"."pk" (
      "a" integer,
      "b" integer
  )
  PARTITION BY LIST ((
  CASE
      WHEN ("a" <> ALL (ARRAY[42, NULL::integer])) THEN 1
      ELSE 0
  END));

Regards,
--
Rustam Allakov