Materialize/test/sqllogictest/not-null-propagation.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

mode cockroach

statement ok
CREATE TABLE int_table (col_null INTEGER, col_not_null INTEGER NOT NULL);

statement ok
CREATE TABLE bool_table (col_null BOOLEAN, col_not_null BOOLEAN NOT NULL);

statement ok
CREATE TABLE str_table (col_null STRING, col_not_null STRING NOT NULL);

statement ok
CREATE TABLE ts_table (col_null TIMESTAMP, col_not_null TIMESTAMP NOT NULL);

statement ok
CREATE TABLE json_table (col_null JSONB, col_not_null JSONB NOT NULL);

statement ok
INSERT INTO json_table VALUES(null, '{}');

#
# Constants are NOT NULL
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT 1;
----
Explained Query:
  Constant // { types: "(integer)" }
    - (1)

Target cluster: mz_catalog_server

EOF

# NULL literal is NULL-able

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT NULL;
----
Explained Query:
  Constant // { types: "(text?)" }
    - (null)

Target cluster: mz_catalog_server

EOF

#
# VALUES
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM (VALUES(1), (2));
----
Explained Query:
  Constant // { types: "(integer)" }
    - (1)
    - (2)

Target cluster: quickstart

EOF


query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM (VALUES(1), (NULL));
----
Explained Query:
  Constant // { types: "(integer?)" }
    - (null)
    - (1)

Target cluster: quickstart

EOF


#
# CAST propagates NOT NULL property
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT CAST(col_null AS BIGINT), CAST(col_not_null AS BIGINT) FROM int_table;
----
Explained Query:
  Project (#2, #3) // { types: "(bigint?, bigint)" }
    Map (integer_to_bigint(#0{col_null}), integer_to_bigint(#1{col_not_null})) // { types: "(integer?, integer, bigint?, bigint)" }
      ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

#
# IS NULL and friends
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_null IS NULL, col_null IS NOT NULL FROM int_table;
----
Explained Query:
  Project (#2, #3) // { types: "(boolean, boolean)" }
    Map ((#0{col_null}) IS NULL, NOT(#2)) // { types: "(integer?, integer, boolean, boolean)" }
      ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_null IS TRUE, col_null IS NOT TRUE FROM bool_table;
----
Explained Query:
  Project (#2, #3) // { types: "(boolean, boolean)" }
    Map ((#0{col_null}) IS TRUE, NOT(#2)) // { types: "(boolean?, boolean, boolean, boolean)" }
      ReadStorage materialize.public.bool_table // { types: "(boolean?, boolean)" }

Source materialize.public.bool_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_null IS UNKNOWN, col_null IS NOT UNKNOWN FROM bool_table;
----
Explained Query:
  Project (#2, #3) // { types: "(boolean, boolean)" }
    Map ((#0{col_null}) IS NULL, NOT(#2)) // { types: "(boolean?, boolean, boolean, boolean)" }
      ReadStorage materialize.public.bool_table // { types: "(boolean?, boolean)" }

Source materialize.public.bool_table

Target cluster: quickstart

EOF

#
# Try some other operators
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_not_null + col_not_null , col_not_null + 1 , col_not_null % col_not_null , col_not_null % 2 FROM int_table;
----
Explained Query:
  Project (#2..=#5) // { types: "(integer, integer, integer, integer)" }
    Map ((#1{col_not_null} + #1{col_not_null}), (#1{col_not_null} + 1), (#1{col_not_null} % #1{col_not_null}), (#1{col_not_null} % 2)) // { types: "(integer?, integer, integer, integer, integer, integer)" }
      ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF


#
# GREATEST / LEAST / COALESCE are NOT NULL if at leat one of their arguments is NOT NULL
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT GREATEST(col_not_null), GREATEST(col_not_null, col_not_null), GREATEST(col_not_null, col_null), GREATEST(col_null, col_null) FROM int_table;
----
Explained Query:
  Project (#2..=#5) // { types: "(integer, integer, integer?, integer?)" }
    Map (greatest(#1{col_not_null}), greatest(#1{col_not_null}, #1{col_not_null}), greatest(#1{col_not_null}, #0{col_null}), greatest(#0{col_null}, #0{col_null})) // { types: "(integer?, integer, integer, integer, integer?, integer?)" }
      ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT LEAST(col_not_null), LEAST(col_not_null, col_not_null), LEAST(col_not_null, col_null), LEAST(col_null, col_null) FROM int_table;
----
Explained Query:
  Project (#2..=#5) // { types: "(integer, integer, integer?, integer?)" }
    Map (least(#1{col_not_null}), least(#1{col_not_null}, #1{col_not_null}), least(#1{col_not_null}, #0{col_null}), least(#0{col_null}, #0{col_null})) // { types: "(integer?, integer, integer, integer, integer?, integer?)" }
      ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT COALESCE(col_not_null), COALESCE(col_not_null, col_not_null), COALESCE(col_not_null, col_null), COALESCE(col_null, col_null) FROM int_table;
----
Explained Query:
  Project (#1{col_not_null}, #1{col_not_null}, #1{col_not_null}, #0{col_null}) // { types: "(integer, integer, integer, integer?)" }
    ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

#
# NULLIF is NOT NULL if first argument is NOT NULL, second argument is NULL, NULL-able otherwise
#


query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT NULLIF(col_not_null, 'a') , NULLIF(col_not_null, NULL), NULLIF(col_null, NULL) , NULLIF(col_null, col_not_null) FROM int_table;
----
Explained Query:
  Project (#3, #1{col_not_null}, #0{col_null}, #2) // { types: "(integer?, integer, integer?, integer?)" }
    Map (case when (#0{col_null} = #1{col_not_null}) then null else #0{col_null} end, error("invalid input syntax for type integer: invalid digit found in string: \"a\"")) // { types: "(integer?, integer, integer?, integer?)" }
      ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

#
# Equality, logical operators
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_not_null = 1 FROM int_table;
----
Explained Query:
  Project (#2) // { types: "(boolean)" }
    Map ((#1{col_not_null} = 1)) // { types: "(integer?, integer, boolean)" }
      ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_not_null AND col_not_null , col_not_null OR col_not_null FROM bool_table;
----
Explained Query:
  Project (#1{col_not_null}, #1{col_not_null}) // { types: "(boolean, boolean)" }
    ReadStorage materialize.public.bool_table // { types: "(boolean?, boolean)" }

Source materialize.public.bool_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_null AND col_not_null , col_null OR col_not_null FROM bool_table;
----
Explained Query:
  Project (#2, #3) // { types: "(boolean?, boolean?)" }
    Map ((#0{col_null} AND #1{col_not_null}), (#0{col_null} OR #1{col_not_null})) // { types: "(boolean?, boolean, boolean?, boolean?)" }
      ReadStorage materialize.public.bool_table // { types: "(boolean?, boolean)" }

Source materialize.public.bool_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT NOT col_null , NOT col_not_null FROM bool_table;
----
Explained Query:
  Project (#2, #3) // { types: "(boolean?, boolean)" }
    Map (NOT(#0{col_null}), NOT(#1{col_not_null})) // { types: "(boolean?, boolean, boolean?, boolean)" }
      ReadStorage materialize.public.bool_table // { types: "(boolean?, boolean)" }

Source materialize.public.bool_table

Target cluster: quickstart

EOF

#
# Meth, that is, math
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT ABS(col_not_null), LOG(col_not_null), ROUND(col_not_null), COS(col_not_null), col_not_null << col_not_null FROM int_table;
----
Explained Query:
  Project (#2, #4..=#7) // { types: "(integer, double precision, double precision, double precision, integer)" }
    Map (abs(#1{col_not_null}), integer_to_double(#1{col_not_null}), log10f64(#3), roundf64(#3), cos(#3), (#1{col_not_null} << #1{col_not_null})) // { types: "(integer?, integer, integer, double precision, double precision, double precision, double precision, integer)" }
      ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

#
# MIN/MAX/AVG/.. can be NULL even on a NOT NULL column
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT MIN(col_not_null), MAX(col_not_null), AVG(col_not_null), STDDEV(col_not_null), LIST_AGG(col_not_null) FROM int_table;
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[min(#0{col_not_null}), max(#0{col_not_null}), sum(#0{col_not_null}), count(*), sum((integer_to_numeric(#0{col_not_null}) * integer_to_numeric(#0{col_not_null}))), sum(integer_to_numeric(#0{col_not_null})), count(integer_to_numeric(#0{col_not_null})), list_agg[order_by=[]](row(list[#0{col_not_null}]))] // { types: "(integer, integer, bigint, bigint, numeric, numeric, bigint, integer list)" }
        Project (#1{col_not_null}) // { types: "(integer)" }
          ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
  Return // { types: "(integer?, integer?, numeric?, numeric?, integer list?)" }
    Project (#0{min_col_not_null}, #1{max_col_not_null}, #8, #9, #7{list_agg}) // { types: "(integer?, integer?, numeric?, numeric?, integer list?)" }
      Map ((bigint_to_numeric(#2{sum_col_not_null}) / bigint_to_numeric(case when (#3{count} = 0) then null else #3{count} end)), sqrtnumeric(case when ((#4{sum}) IS NULL OR (#5{sum}) IS NULL OR (case when (#6{count} = 0) then null else #6{count} end) IS NULL OR (case when (0 = (#6{count} - 1)) then null else (#6{count} - 1) end) IS NULL) then null else greatest(((#4{sum} - ((#5{sum} * #5{sum}) / bigint_to_numeric(case when (#6{count} = 0) then null else #6{count} end))) / bigint_to_numeric(case when (0 = (#6{count} - 1)) then null else (#6{count} - 1) end)), 0) end)) // { types: "(integer?, integer?, bigint?, bigint, numeric?, numeric?, bigint, integer list?, numeric?, numeric?)" }
        Union // { types: "(integer?, integer?, bigint?, bigint, numeric?, numeric?, bigint, integer list?)" }
          Get l0 // { types: "(integer, integer, bigint, bigint, numeric, numeric, bigint, integer list)" }
          Map (null, null, null, 0, null, null, 0, null) // { types: "(integer?, integer?, bigint?, bigint, numeric?, numeric?, bigint, integer list?)" }
            Union // { types: "()" }
              Negate // { types: "()" }
                Project () // { types: "()" }
                  Get l0 // { types: "(integer, integer, bigint, bigint, numeric, numeric, bigint, integer list)" }
              Constant // { types: "()" }
                - ()

Source materialize.public.int_table

Target cluster: quickstart

EOF

#
# COUNT preserves NOT NULL
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT COUNT(col_not_null), COUNT(DISTINCT col_not_null) FROM int_table;
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[count(*), count(distinct #0{col_not_null})] // { types: "(bigint, bigint)" }
        Project (#1{col_not_null}) // { types: "(integer)" }
          ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
  Return // { types: "(bigint, bigint)" }
    Union // { types: "(bigint, bigint)" }
      Get l0 // { types: "(bigint, bigint)" }
      Map (0, 0) // { types: "(bigint, bigint)" }
        Union // { types: "()" }
          Negate // { types: "()" }
            Project () // { types: "()" }
              Get l0 // { types: "(bigint, bigint)" }
          Constant // { types: "()" }
            - ()

Source materialize.public.int_table

Target cluster: quickstart

EOF

#
# LIKE
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_not_null LIKE col_not_null, col_null LIKE col_not_null, col_not_null LIKE col_null FROM str_table;
----
Explained Query:
  Project (#2..=#4) // { types: "(boolean, boolean?, boolean?)" }
    Map ((#1{col_not_null} like #1{col_not_null}), (#0{col_null} like #1{col_not_null}), (#1{col_not_null} like #0{col_null})) // { types: "(text?, text, boolean, boolean?, boolean?)" }
      ReadStorage materialize.public.str_table // { types: "(text?, text)" }

Source materialize.public.str_table

Target cluster: quickstart

EOF

# VARIADIC FUNCTIONS

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_not_null || col_not_null, substr(col_not_null, 3, 2), regexp_match(col_not_null, col_not_null), lpad(col_not_null, 3, col_not_null) FROM str_table;
----
Explained Query:
  Project (#2..=#5) // { types: "(text, text, text[]?, text)" }
    Map ((#1{col_not_null} || #1{col_not_null}), substr(#1{col_not_null}, 3, 2), regexp_match(#1{col_not_null}, #1{col_not_null}), lpad(#1{col_not_null}, 3, #1{col_not_null})) // { types: "(text?, text, text, text, text[]?, text)" }
      ReadStorage materialize.public.str_table // { types: "(text?, text)" }

Source materialize.public.str_table

Target cluster: quickstart

EOF

# VARIADIC FUNCTIONS that introduce nulls

query BBBBBBB
SELECT COALESCE(NULLIF('a', 'a')) IS NULL, GREATEST(NULLIF('a', 'a')) IS NULL, LEAST(NULLIF('a', 'a')) IS NULL, MAKE_TIMESTAMP(2023, 1, 1, 0, 0, 11111) IS NULL, (ARRAY[1, 2])[3] IS NULL, (LIST[1, 2])[3] IS NULL, REGEXP_MATCH('a', 'b')  IS NULL;
----
true  true  true  true  true  true  true

# BINARY FUNCTIONS that introduce nulls
# MapGetValue and ListLengthMax not covered

query BBBBBBBBB
SELECT (col_not_null -> 'x')::int IS NULL, (col_not_null -> 'y')::text IS NULL, col_not_null #> '{z}' IS NULL, ('1'::JSONB) || ('2'::JSONB) IS NULL, ('1'::jsonb) - 1 IS NULL, ('1'::jsonb) - 'x' IS NULL, ARRAY_LENGTH(ARRAY[]::INT[], 1) IS NULL, ARRAY_LOWER(ARRAY[]::INT[], 1) IS NULL, ARRAY_UPPER(ARRAY[]::INT[], 1) IS NULL FROM json_table;
----
true  true  true  true  true  true  true  true  true

#
# REGEXP returns NULL on no match, so can not be NOT NULL
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT REGEXP_MATCH(col_not_null, 'aaa'), REGEXP_MATCH('aaa', col_not_null) FROM str_table;
----
Explained Query:
  Project (#2, #3) // { types: "(text[]?, text[]?)" }
    Map (regexp_match["aaa", case_insensitive=false](#1{col_not_null}), regexp_match("aaa", #1{col_not_null})) // { types: "(text?, text, text[]?, text[]?)" }
      ReadStorage materialize.public.str_table // { types: "(text?, text)" }

Source materialize.public.str_table

Target cluster: quickstart

EOF

#
# SPLIT_PART on the other hand returns an empty string, so can be NOT NULL
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT SPLIT_PART(col_not_null, 'a', 100), SPLIT_PART('a', col_not_null, 100), SPLIT_PART('a', 'a', col_not_null::int) FROM str_table;
----
Explained Query:
  Project (#2..=#4) // { types: "(text, text, text)" }
    Map (split_string(#1{col_not_null}, "a", 100), split_string("a", #1{col_not_null}, 100), split_string("a", "a", text_to_integer(#1{col_not_null}))) // { types: "(text?, text, text, text, text)" }
      ReadStorage materialize.public.str_table // { types: "(text?, text)" }

Source materialize.public.str_table

Target cluster: quickstart

EOF

#
# IN , NOT IN
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_not_null IN (1), 1 IN (col_not_null), 1 IN (1, col_null) , 1 IN (NULL), NULL IN (1), NULL IN (col_not_null) FROM int_table;
----
Explained Query:
  Project (#2, #2..=#6) // { types: "(boolean, boolean, boolean, boolean?, boolean?, boolean?)" }
    Map ((#1{col_not_null} = 1), true, null, null, null) // { types: "(integer?, integer, boolean, boolean, boolean?, boolean?, boolean?)" }
      ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_not_null NOT IN (1), 1 not IN (col_not_null), 1 NOT IN (1, col_null) , 1 NOT IN (NULL), NULL NOT IN (1), NULL NOT IN (col_not_null) FROM int_table;
----
Explained Query:
  Project (#2, #2..=#6) // { types: "(boolean, boolean, boolean, boolean?, boolean?, boolean?)" }
    Map ((#1{col_not_null} != 1), false, null, null, null) // { types: "(integer?, integer, boolean, boolean, boolean?, boolean?, boolean?)" }
      ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

#
# SOME, ANY, ALL
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT 1 = SOME (VALUES(col_null)), 1 = SOME (VALUES(col_not_null)), col_null = SOME (VALUES(NULL::int)), col_not_null = SOME (VALUES(NULL::int)) , col_null = SOME (VALUES(col_not_null)) , col_not_null = SOME (VALUES(col_null)) FROM int_table;
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{col_null}, #1{col_not_null}] // { types: "(integer?, integer)" }
        ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
  Return // { types: "(boolean?, boolean, boolean?, boolean?, boolean?, boolean?)" }
    Project (#5, #4, #7, #8, #6, #6) // { types: "(boolean?, boolean, boolean?, boolean?, boolean?, boolean?)" }
      Map ((#0{col_null} = 1), (#0{col_null} = #1{col_not_null}), null, null) // { types: "(integer?, integer, integer?, integer, boolean, boolean?, boolean?, boolean?, boolean?)" }
        Join on=(#0{col_null} = #2{col_null} AND #1{col_not_null} = #3{col_not_null}) type=differential // { types: "(integer?, integer, integer?, integer, boolean)" }
          ArrangeBy keys=[[#0{col_null}, #1{col_not_null}]] // { types: "(integer?, integer)" }
            ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
          ArrangeBy keys=[[#0{col_null}, #1{col_not_null}]] // { types: "(integer?, integer, boolean)" }
            Union // { types: "(integer?, integer, boolean)" }
              Filter (#1{col_not_null} = 1) // { types: "(integer?, integer, boolean)" }
                Map (true) // { types: "(integer?, integer, boolean)" }
                  Get l0 // { types: "(integer?, integer)" }
              Map (false) // { types: "(integer?, integer, boolean)" }
                Union // { types: "(integer?, integer)" }
                  Negate // { types: "(integer?, integer)" }
                    Filter (#1{col_not_null} = 1) // { types: "(integer?, integer)" }
                      Get l0 // { types: "(integer?, integer)" }
                  Get l0 // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF


query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT 1 > ANY (VALUES(col_null)), 1 > ANY (VALUES(col_not_null)), col_null > ANY (VALUES(NULL::int)), col_not_null > ANY (VALUES(NULL::int)) , col_null > ANY (VALUES(col_not_null)) , col_not_null > ANY (VALUES(col_null)) FROM int_table;
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{col_null}, #1{col_not_null}] // { types: "(integer?, integer)" }
        ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
  Return // { types: "(boolean?, boolean, boolean?, boolean?, boolean?, boolean?)" }
    Project (#5, #4, #8, #9, #6, #7) // { types: "(boolean?, boolean, boolean?, boolean?, boolean?, boolean?)" }
      Map ((1 > #0{col_null}), (#0{col_null} > #1{col_not_null}), (#1{col_not_null} > #0{col_null}), null, null) // { types: "(integer?, integer, integer?, integer, boolean, boolean?, boolean?, boolean?, boolean?, boolean?)" }
        Join on=(#0{col_null} = #2{col_null} AND #1{col_not_null} = #3{col_not_null}) type=differential // { types: "(integer?, integer, integer?, integer, boolean)" }
          ArrangeBy keys=[[#0{col_null}, #1{col_not_null}]] // { types: "(integer?, integer)" }
            ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
          ArrangeBy keys=[[#0{col_null}, #1{col_not_null}]] // { types: "(integer?, integer, boolean)" }
            Union // { types: "(integer?, integer, boolean)" }
              Filter (1 > #1{col_not_null}) // { types: "(integer?, integer, boolean)" }
                Map (true) // { types: "(integer?, integer, boolean)" }
                  Get l0 // { types: "(integer?, integer)" }
              Map (false) // { types: "(integer?, integer, boolean)" }
                Union // { types: "(integer?, integer)" }
                  Negate // { types: "(integer?, integer)" }
                    Filter (1 > #1{col_not_null}) // { types: "(integer?, integer)" }
                      Get l0 // { types: "(integer?, integer)" }
                  Get l0 // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT 1 < ALL (VALUES(col_null)), 1 < ALL (VALUES(col_not_null)), col_null < ALL (VALUES(NULL::int)), col_not_null < ALL (VALUES(NULL::int)) , col_null < ALL (VALUES(col_not_null)) , col_not_null < ALL (VALUES(col_null)) FROM int_table;
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{col_null}, #1{col_not_null}] // { types: "(integer?, integer)" }
        ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
  Return // { types: "(boolean?, boolean, boolean?, boolean?, boolean?, boolean?)" }
    Project (#5, #8..=#10, #6, #7) // { types: "(boolean?, boolean, boolean?, boolean?, boolean?, boolean?)" }
      Map ((1 < #0{col_null}), (#0{col_null} < #1{col_not_null}), (#1{col_not_null} < #0{col_null}), NOT(#4), null, null) // { types: "(integer?, integer, integer?, integer, boolean, boolean?, boolean?, boolean?, boolean, boolean?, boolean?)" }
        Join on=(#0{col_null} = #2{col_null} AND #1{col_not_null} = #3{col_not_null}) type=differential // { types: "(integer?, integer, integer?, integer, boolean)" }
          ArrangeBy keys=[[#0{col_null}, #1{col_not_null}]] // { types: "(integer?, integer)" }
            ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
          ArrangeBy keys=[[#0{col_null}, #1{col_not_null}]] // { types: "(integer?, integer, boolean)" }
            Union // { types: "(integer?, integer, boolean)" }
              Filter (1 >= #1{col_not_null}) // { types: "(integer?, integer, boolean)" }
                Map (true) // { types: "(integer?, integer, boolean)" }
                  Get l0 // { types: "(integer?, integer)" }
              Map (false) // { types: "(integer?, integer, boolean)" }
                Union // { types: "(integer?, integer)" }
                  Negate // { types: "(integer?, integer)" }
                    Filter (1 >= #1{col_not_null}) // { types: "(integer?, integer)" }
                      Get l0 // { types: "(integer?, integer)" }
                  Get l0 // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT 1 = SOME(VALUES(1), (NULL::int)), 1 = ALL (VALUES(1), (NULL::int)) , 1 = ANY (VALUES(1), (NULL::int));
----
Explained Query:
  Constant // { types: "(boolean, boolean?, boolean)" }
    - (true, null, true)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT 1 = SOME(VALUES(NULL::int), (1)), 1 = ALL (VALUES(NULL::int), (1)) , 1 = ANY (VALUES(NULL::int), (1));
----
Explained Query:
  Constant // { types: "(boolean, boolean?, boolean)" }
    - (true, null, true)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT 1 = SOME(VALUES(col_not_null), (NULL::int)), 1 = ALL (VALUES(col_not_null), (NULL::int)) , 1 = ANY (VALUES(col_not_null), (NULL::int)) FROM int_table;
----
Explained Query:
  With
    cte l0 =
      Project (#1{col_not_null}) // { types: "(integer)" }
        ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
    cte l1 =
      Distinct project=[#0{col_not_null}] // { types: "(integer)" }
        Get l0 // { types: "(integer)" }
    cte l2 =
      FlatMap wrap1(#0{col_not_null}, null) // { types: "(integer, integer?)" }
        Get l1 // { types: "(integer)" }
    cte l3 =
      Reduce group_by=[#0{col_not_null}] aggregates=[any((#1{right_col0_0} = 1))] // { types: "(integer, boolean?)" }
        Get l2 // { types: "(integer, integer?)" }
    cte l4 =
      Union // { types: "(integer, boolean?)" }
        Get l3 // { types: "(integer, boolean?)" }
        Map (false) // { types: "(integer, boolean)" }
          Union // { types: "(integer)" }
            Negate // { types: "(integer)" }
              Project (#0{col_not_null}) // { types: "(integer)" }
                Get l3 // { types: "(integer, boolean?)" }
            Get l1 // { types: "(integer)" }
    cte l5 =
      Reduce group_by=[#0{col_not_null}] aggregates=[all((#1{right_col0_2} = 1))] // { types: "(integer, boolean?)" }
        Get l2 // { types: "(integer, integer?)" }
    cte l6 =
      Union // { types: "(integer, boolean?)" }
        Get l5 // { types: "(integer, boolean?)" }
        Map (true) // { types: "(integer, boolean)" }
          Union // { types: "(integer)" }
            Negate // { types: "(integer)" }
              Project (#0{col_not_null}) // { types: "(integer)" }
                Get l5 // { types: "(integer, boolean?)" }
            Get l1 // { types: "(integer)" }
  Return // { types: "(boolean?, boolean?, boolean?)" }
    Project (#2{any}, #4{all}, #2{any}) // { types: "(boolean?, boolean?, boolean?)" }
      Join on=(#0{col_not_null} = #1{col_not_null} = #3{col_not_null}) type=delta // { types: "(integer, integer, boolean?, integer, boolean?)" }
        ArrangeBy keys=[[#0{col_not_null}]] // { types: "(integer)" }
          Get l0 // { types: "(integer)" }
        ArrangeBy keys=[[#0{col_not_null}]] // { types: "(integer, boolean?)" }
          Union // { types: "(integer, boolean?)" }
            Get l4 // { types: "(integer, boolean?)" }
            Map (null) // { types: "(integer, boolean?)" }
              Union // { types: "(integer)" }
                Negate // { types: "(integer)" }
                  Project (#0{col_not_null}) // { types: "(integer)" }
                    Get l4 // { types: "(integer, boolean?)" }
                Get l1 // { types: "(integer)" }
        ArrangeBy keys=[[#0{col_not_null}]] // { types: "(integer, boolean?)" }
          Union // { types: "(integer, boolean?)" }
            Get l6 // { types: "(integer, boolean?)" }
            Map (null) // { types: "(integer, boolean?)" }
              Union // { types: "(integer)" }
                Negate // { types: "(integer)" }
                  Project (#0{col_not_null}) // { types: "(integer)" }
                    Get l6 // { types: "(integer, boolean?)" }
                Get l1 // { types: "(integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

#
# Scalar subqueries can return NULL on no rows returned by the subquery
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT (SELECT col_not_null FROM int_table) FROM int_table;
----
Explained Query:
  With
    cte l0 =
      Project () // { types: "()" }
        ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
    cte l1 =
      Union // { types: "(integer)" }
        Project (#1{col_not_null}) // { types: "(integer)" }
          ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
        Project (#1) // { types: "(integer)" }
          FlatMap guard_subquery_size(#0{count}) // { types: "(bigint, integer)" }
            Reduce aggregates=[count(*)] // { types: "(bigint)" }
              Get l0 // { types: "()" }
  Return // { types: "(integer?)" }
    CrossJoin type=differential // { types: "(integer?)" }
      ArrangeBy keys=[[]] // { types: "()" }
        Get l0 // { types: "()" }
      ArrangeBy keys=[[]] // { types: "(integer?)" }
        Union // { types: "(integer?)" }
          Get l1 // { types: "(integer)" }
          Map (null) // { types: "(integer?)" }
            Union // { types: "()" }
              Negate // { types: "()" }
                Distinct project=[] // { types: "()" }
                  Project () // { types: "()" }
                    Get l1 // { types: "(integer)" }
              Constant // { types: "()" }
                - ()

Source materialize.public.int_table

Target cluster: quickstart

EOF

#
# IN/EXISTS
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT 1 IN (SELECT col_not_null FROM int_table), 1 NOT IN (SELECT col_not_null FROM int_table) FROM int_table;
----
Explained Query:
  With
    cte l0 =
      Distinct project=[] // { types: "()" }
        Project () // { types: "()" }
          Filter (#1{col_not_null} = 1) // { types: "(integer?, integer)" }
            ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
    cte l1 =
      ArrangeBy keys=[[]] // { types: "(boolean)" }
        Union // { types: "(boolean)" }
          Map (true) // { types: "(boolean)" }
            Get l0 // { types: "()" }
          Map (false) // { types: "(boolean)" }
            Union // { types: "()" }
              Negate // { types: "()" }
                Get l0 // { types: "()" }
              Constant // { types: "()" }
                - ()
  Return // { types: "(boolean, boolean)" }
    Project (#0, #2) // { types: "(boolean, boolean)" }
      Map (NOT(#1)) // { types: "(boolean, boolean, boolean)" }
        CrossJoin type=delta // { types: "(boolean, boolean)" }
          ArrangeBy keys=[[]] // { types: "()" }
            Project () // { types: "()" }
              ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
          Get l1 // { types: "(boolean)" }
          Get l1 // { types: "(boolean)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT EXISTS (SELECT col_not_null FROM int_table), NOT EXISTS (SELECT col_not_null FROM int_table) FROM int_table;
----
Explained Query:
  With
    cte l0 =
      Project () // { types: "()" }
        ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
    cte l1 =
      Distinct project=[] // { types: "()" }
        Get l0 // { types: "()" }
  Return // { types: "(boolean, boolean)" }
    Map (NOT(#0{"?column?"})) // { types: "(boolean, boolean)" }
      CrossJoin type=differential // { types: "(boolean)" }
        ArrangeBy keys=[[]] // { types: "()" }
          Get l0 // { types: "()" }
        ArrangeBy keys=[[]] // { types: "(boolean)" }
          Union // { types: "(boolean)" }
            Map (true) // { types: "(boolean)" }
              Get l1 // { types: "()" }
            Map (false) // { types: "(boolean)" }
              Union // { types: "()" }
                Negate // { types: "()" }
                  Get l1 // { types: "()" }
                Constant // { types: "()" }
                  - ()

Source materialize.public.int_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT 1 = SOME (SELECT col_not_null FROM int_table), col_not_null = SOME (SELECT 1), col_null = SOME ( SELECT col_not_null FROM int_table ) FROM int_table;
----
Explained Query:
  With
    cte l0 =
      Distinct project=[] // { types: "()" }
        Project () // { types: "()" }
          Filter (#1{col_not_null} = 1) // { types: "(integer?, integer)" }
            ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
    cte l1 =
      Distinct project=[#0{col_null}, #1{col_not_null}] // { types: "(integer?, integer)" }
        ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
    cte l2 =
      Distinct project=[#0{col_null}] // { types: "(integer?)" }
        Project (#0{col_null}) // { types: "(integer?)" }
          ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
    cte l3 =
      Reduce group_by=[#0{col_null}] aggregates=[any((#0{col_null} = #1{col_not_null}))] // { types: "(integer?, boolean?)" }
        CrossJoin type=differential // { types: "(integer?, integer)" }
          ArrangeBy keys=[[]] // { types: "(integer?)" }
            Get l2 // { types: "(integer?)" }
          ArrangeBy keys=[[]] // { types: "(integer)" }
            Project (#1{col_not_null}) // { types: "(integer)" }
              ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
    cte l4 =
      Union // { types: "(integer?, boolean?)" }
        Get l3 // { types: "(integer?, boolean?)" }
        Map (false) // { types: "(integer?, boolean)" }
          Union // { types: "(integer?)" }
            Negate // { types: "(integer?)" }
              Project (#0{col_null}) // { types: "(integer?)" }
                Get l3 // { types: "(integer?, boolean?)" }
            Get l2 // { types: "(integer?)" }
  Return // { types: "(boolean, boolean, boolean?)" }
    Project (#2, #5, #7{any}) // { types: "(boolean, boolean, boolean?)" }
      Join on=(#0{col_null} = #3{col_null} = #6{col_null} AND #1{col_not_null} = #4{col_not_null}) type=delta // { types: "(integer?, integer, boolean, integer?, integer, boolean, integer?, boolean?)" }
        ArrangeBy keys=[[], [#0{col_null}], [#0{col_null}, #1{col_not_null}]] // { types: "(integer?, integer)" }
          ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
        ArrangeBy keys=[[]] // { types: "(boolean)" }
          Union // { types: "(boolean)" }
            Map (true) // { types: "(boolean)" }
              Get l0 // { types: "()" }
            Map (false) // { types: "(boolean)" }
              Union // { types: "()" }
                Negate // { types: "()" }
                  Get l0 // { types: "()" }
                Constant // { types: "()" }
                  - ()
        ArrangeBy keys=[[#0{col_null}, #1{col_not_null}]] // { types: "(integer?, integer, boolean)" }
          Union // { types: "(integer?, integer, boolean)" }
            Filter (#1{col_not_null} = 1) // { types: "(integer?, integer, boolean)" }
              Map (true) // { types: "(integer?, integer, boolean)" }
                Get l1 // { types: "(integer?, integer)" }
            Map (false) // { types: "(integer?, integer, boolean)" }
              Union // { types: "(integer?, integer)" }
                Negate // { types: "(integer?, integer)" }
                  Filter (#1{col_not_null} = 1) // { types: "(integer?, integer)" }
                    Get l1 // { types: "(integer?, integer)" }
                Get l1 // { types: "(integer?, integer)" }
        ArrangeBy keys=[[#0{col_null}]] // { types: "(integer?, boolean?)" }
          Union // { types: "(integer?, boolean?)" }
            Get l4 // { types: "(integer?, boolean?)" }
            Map (null) // { types: "(integer?, boolean?)" }
              Union // { types: "(integer?)" }
                Negate // { types: "(integer?)" }
                  Project (#0{col_null}) // { types: "(integer?)" }
                    Get l4 // { types: "(integer?, boolean?)" }
                Get l2 // { types: "(integer?)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

#
# DATE / TIME functions
#
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_null - INTERVAL '1 second' , col_not_null - INTERVAL '1 second' FROM ts_table;
----
Explained Query:
  Project (#2, #3) // { types: "(timestamp without time zone?, timestamp without time zone)" }
    Map ((#0{col_null} - 00:00:01), (#1{col_not_null} - 00:00:01)) // { types: "(timestamp without time zone?, timestamp without time zone, timestamp without time zone?, timestamp without time zone)" }
      ReadStorage materialize.public.ts_table // { types: "(timestamp without time zone?, timestamp without time zone)" }

Source materialize.public.ts_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_null - col_not_null, col_not_null - col_null FROM ts_table;
----
Explained Query:
  Project (#2, #3) // { types: "(interval?, interval?)" }
    Map ((#0{col_null} - #1{col_not_null}), (#1{col_not_null} - #0{col_null})) // { types: "(timestamp without time zone?, timestamp without time zone, interval?, interval?)" }
      ReadStorage materialize.public.ts_table // { types: "(timestamp without time zone?, timestamp without time zone)" }

Source materialize.public.ts_table

Target cluster: quickstart

EOF


#
# INNER JOIN preserves
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT a1.col_not_null, a2.col_not_null FROM int_table AS a1 INNER JOIN int_table AS a2 ON TRUE;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[]] // { types: "(integer)" }
        Project (#1{col_not_null}) // { types: "(integer)" }
          ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
  Return // { types: "(integer, integer)" }
    CrossJoin type=differential // { types: "(integer, integer)" }
      Get l0 // { types: "(integer)" }
      Get l0 // { types: "(integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

#
# OUTER JOIN does not for columns coming from the right side
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT a1.col_not_null, a2.col_not_null FROM int_table AS a1 LEFT JOIN int_table AS a2 ON TRUE;
----
Explained Query:
  With
    cte l0 =
      CrossJoin type=differential // { types: "(integer?, integer, integer)" }
        ArrangeBy keys=[[]] // { types: "(integer?, integer)" }
          ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
        ArrangeBy keys=[[]] // { types: "(integer)" }
          Project (#1{col_not_null}) // { types: "(integer)" }
            ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
  Return // { types: "(integer, integer?)" }
    Union // { types: "(integer, integer?)" }
      Project (#1{col_not_null}, #2{col_not_null}) // { types: "(integer, integer)" }
        Get l0 // { types: "(integer?, integer, integer)" }
      Project (#1{col_not_null}, #4) // { types: "(integer, integer?)" }
        Map (null) // { types: "(integer?, integer, integer?, integer, integer?)" }
          Join on=(#0{col_null} = #2{col_null} AND #1{col_not_null} = #3{col_not_null}) type=differential // { types: "(integer?, integer, integer?, integer)" }
            ArrangeBy keys=[[#0{col_null}, #1{col_not_null}]] // { types: "(integer?, integer)" }
              Union // { types: "(integer?, integer)" }
                Negate // { types: "(integer?, integer)" }
                  Distinct project=[#0{col_null}, #1{col_not_null}] // { types: "(integer?, integer)" }
                    Project (#0{col_null}, #1{col_not_null}) // { types: "(integer?, integer)" }
                      Get l0 // { types: "(integer?, integer, integer)" }
                Distinct project=[#0{col_null}, #1{col_not_null}] // { types: "(integer?, integer)" }
                  ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
            ArrangeBy keys=[[#0{col_null}, #1{col_not_null}]] // { types: "(integer?, integer)" }
              ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT a1.col_not_null, a2.col_not_null FROM int_table AS a1 FULL OUTER JOIN int_table AS a2 ON TRUE;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[]] // { types: "(integer?, integer)" }
        ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
    cte l1 =
      CrossJoin type=differential // { types: "(integer?, integer, integer?, integer)" }
        Get l0 // { types: "(integer?, integer)" }
        Get l0 // { types: "(integer?, integer)" }
    cte l2 =
      Distinct project=[#0{col_null}, #1{col_not_null}] // { types: "(integer?, integer)" }
        ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
    cte l3 =
      ArrangeBy keys=[[#0{col_null}, #1{col_not_null}]] // { types: "(integer?, integer)" }
        ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
  Return // { types: "(integer?, integer?)" }
    Union // { types: "(integer?, integer?)" }
      Project (#1{col_not_null}, #3{col_not_null}) // { types: "(integer, integer)" }
        Get l1 // { types: "(integer?, integer, integer?, integer)" }
      Project (#1{col_not_null}, #4) // { types: "(integer, integer?)" }
        Map (null) // { types: "(integer?, integer, integer?, integer, integer?)" }
          Join on=(#0{col_null} = #2{col_null} AND #1{col_not_null} = #3{col_not_null}) type=differential // { types: "(integer?, integer, integer?, integer)" }
            ArrangeBy keys=[[#0{col_null}, #1{col_not_null}]] // { types: "(integer?, integer)" }
              Union // { types: "(integer?, integer)" }
                Negate // { types: "(integer?, integer)" }
                  Distinct project=[#0{col_null}, #1{col_not_null}] // { types: "(integer?, integer)" }
                    Project (#0{col_null}, #1{col_not_null}) // { types: "(integer?, integer)" }
                      Get l1 // { types: "(integer?, integer, integer?, integer)" }
                Get l2 // { types: "(integer?, integer)" }
            Get l3 // { types: "(integer?, integer)" }
      Project (#4, #1{col_not_null}) // { types: "(integer?, integer)" }
        Map (null) // { types: "(integer?, integer, integer?, integer, integer?)" }
          Join on=(#0{col_null} = #2{col_null} AND #1{col_not_null} = #3{col_not_null}) type=differential // { types: "(integer?, integer, integer?, integer)" }
            ArrangeBy keys=[[#0{col_null}, #1{col_not_null}]] // { types: "(integer?, integer)" }
              Union // { types: "(integer?, integer)" }
                Negate // { types: "(integer?, integer)" }
                  Distinct project=[#0{col_null}, #1{col_not_null}] // { types: "(integer?, integer)" }
                    Project (#2{col_null}, #3{col_not_null}) // { types: "(integer?, integer)" }
                      Get l1 // { types: "(integer?, integer, integer?, integer)" }
                Get l2 // { types: "(integer?, integer)" }
            Get l3 // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

#
# UNION
#


query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_not_null FROM int_table UNION ALL SELECT col_not_null FROM int_table;
----
Explained Query:
  With
    cte l0 =
      Project (#1{col_not_null}) // { types: "(integer)" }
        ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
  Return // { types: "(integer)" }
    Union // { types: "(integer)" }
      Get l0 // { types: "(integer)" }
      Get l0 // { types: "(integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT col_not_null FROM int_table UNION ALL SELECT col_null FROM int_table;
----
Explained Query:
  Union // { types: "(integer?)" }
    Project (#1{col_not_null}) // { types: "(integer)" }
      ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }
    Project (#0{col_null}) // { types: "(integer?)" }
      ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF

#
# DERIVED TABLES
#

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, no fast path, humanized expressions) AS VERBOSE TEXT FOR SELECT f1 + 1 FROM (SELECT col_not_null + 1 AS f1 FROM int_table);
----
Explained Query:
  Project (#2) // { types: "(integer)" }
    Map (((#1{col_not_null} + 1) + 1)) // { types: "(integer?, integer, integer)" }
      ReadStorage materialize.public.int_table // { types: "(integer?, integer)" }

Source materialize.public.int_table

Target cluster: quickstart

EOF