Materialize/test/sqllogictest/transform/literal_lifting.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.

#
# Test various cases of literal lifting
#

mode cockroach

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_table_keys = true
----
COMPLETE 0

statement ok
create table t (
  a int,
  b int
)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
(select null::int, 1, 2)
union all
(select a, b, 2 from t)
union all
(select a, b, 2 from t)
----
Explained Query:
  Map (2) // { arity: 3 }
    Union // { arity: 2 }
      ReadStorage materialize.public.t // { arity: 2 }
      ReadStorage materialize.public.t // { arity: 2 }
      Constant // { arity: 2 }
        - (null, 1)

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
(select null::int, 1, 2, 3)
union all
(select a, b, 2, 3 from t)
union all
(select a, b, 2, 3 from t)
----
Explained Query:
  Map (2, 3) // { arity: 4 }
    Union // { arity: 2 }
      ReadStorage materialize.public.t // { arity: 2 }
      ReadStorage materialize.public.t // { arity: 2 }
      Constant // { arity: 2 }
        - (null, 1)

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
(select null::int, 1, 2)
union all
(select a, b, 2 from t)
union all
(select a, b, 3 from t)
----
Explained Query:
  Union // { arity: 3 }
    Map (2) // { arity: 3 }
      ReadStorage materialize.public.t // { arity: 2 }
    Map (3) // { arity: 3 }
      ReadStorage materialize.public.t // { arity: 2 }
    Constant // { arity: 3 }
      - (null, 1, 2)

Source materialize.public.t

Target cluster: quickstart

EOF

# Group key literal lifting
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select a, b from t where a = 1 group by a, b
----
Explained Query:
  Project (#1, #0{b}) // { arity: 2 }
    Map (1) // { arity: 2 }
      Distinct project=[#0{b}] // { arity: 1 }
        Project (#1{b}) // { arity: 1 }
          Filter (#0{a} = 1) // { arity: 2 }
            ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t
  filter=((#0{a} = 1))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select a, b from t where b = 1 group by a, b
----
Explained Query:
  Map (1) // { arity: 2 }
    Distinct project=[#0{a}] // { arity: 1 }
      Project (#0{a}) // { arity: 1 }
        Filter (#1{b} = 1) // { arity: 2 }
          ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t
  filter=((#1{b} = 1))

Target cluster: quickstart

EOF


# Permute literals in Map operator so they can be lifted
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select * from (select 1, a+1 from t), t;
----
Explained Query:
  Project (#3, #0..=#2{b}) // { arity: 4 }
    Map (1) // { arity: 4 }
      CrossJoin type=differential // { arity: 3 }
        implementation
          %0:t[×] » %1:t[×]
        ArrangeBy keys=[[]] // { arity: 1 }
          Project (#2) // { arity: 1 }
            Map ((#0{a} + 1)) // { arity: 3 }
              ReadStorage materialize.public.t // { arity: 2 }
        ArrangeBy keys=[[]] // { arity: 2 }
          ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select * from (select b+1, 2, 1, a+1 from t), t;
----
Explained Query:
  Project (#0, #4, #5, #1..=#3{b}) // { arity: 6 }
    Map (2, 1) // { arity: 6 }
      CrossJoin type=differential // { arity: 4 }
        implementation
          %0:t[×] » %1:t[×]
        ArrangeBy keys=[[]] // { arity: 2 }
          Project (#2, #3) // { arity: 2 }
            Map ((#1{b} + 1), (#0{a} + 1)) // { arity: 4 }
              ReadStorage materialize.public.t // { arity: 2 }
        ArrangeBy keys=[[]] // { arity: 2 }
          ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select * from (select 3, b+1, 2, a+2, 1, a+1 from t), t;
----
Explained Query:
  Project (#5, #0, #6, #1, #7, #2..=#4{b}) // { arity: 8 }
    Map (3, 2, 1) // { arity: 8 }
      CrossJoin type=differential // { arity: 5 }
        implementation
          %0:t[×] » %1:t[×]
        ArrangeBy keys=[[]] // { arity: 3 }
          Project (#2..=#4) // { arity: 3 }
            Map ((#1{b} + 1), (#0{a} + 2), (#0{a} + 1)) // { arity: 5 }
              ReadStorage materialize.public.t // { arity: 2 }
        ArrangeBy keys=[[]] // { arity: 2 }
          ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select a+1 from (select 1 as a, b from t);
----
Explained Query:
  Project (#2) // { arity: 1 }
    Map (2) // { arity: 3 }
      ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select z+1 from (select 2 as y, a, 1 as z, b from t);
----
Explained Query:
  Project (#2) // { arity: 1 }
    Map (2) // { arity: 3 }
      ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

# Extract common values in all rows in Constant operator
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select c1, c1 + a from (select 1 as c1, x as c2, 3 as c3 from generate_series(1, 3) as x union all select 1, x, 3 from generate_series(5, 8) as x), t;
----
Explained Query:
  Project (#2, #1) // { arity: 2 }
    Map ((1 + #0{a}), 1) // { arity: 3 }
      CrossJoin type=differential // { arity: 1 }
        implementation
          %0[×] » %1:t[×]
        ArrangeBy keys=[[]] // { arity: 0 }
          Constant // { arity: 0 }
            - (() x 7)
        ArrangeBy keys=[[]] // { arity: 1 }
          Project (#0{a}) // { arity: 1 }
            ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select * from (select 1 as f1, 2 as f2), generate_series(f1, f2);
----
Explained Query (fast path):
  Constant
    - (1, 2, 1)
    - (1, 2, 2)

Target cluster: quickstart

EOF

# ... check keys are updated properly
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, humanized expressions) AS VERBOSE TEXT FOR
select c.* from (select f1, f2 from (select f2, f1 from (select 1 as f1), generate_series(2, 4) as f2) group by f2, f1) as c, t;
----
Explained Query:
  Project (#1, #0) // { types: "(integer, integer)" }
    Map (1) // { types: "(integer, integer)" }
      CrossJoin type=differential // { types: "(integer)" }
        ArrangeBy keys=[[]] // { types: "(integer)" }
          Constant // { types: "(integer)" }
            - (2)
            - (3)
            - (4)
        ArrangeBy keys=[[]] // { types: "()" }
          Project () // { types: "()" }
            ReadStorage materialize.public.t // { types: "(integer?, integer?)" }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(types, humanized expressions) AS VERBOSE TEXT FOR
select c.* from (select f2, f1, f3 from (select f3, f2, f1 from generate_series(2, 4) as f2, generate_series(3, 5) as f3, (select 1 as f1)) group by f2, f3, f1) as c, t;
----
Explained Query:
  Project (#0, #2, #1) // { types: "(integer, integer, integer)" }
    Map (1) // { types: "(integer, integer, integer)" }
      CrossJoin type=differential // { types: "(integer, integer)" }
        ArrangeBy keys=[[]] // { types: "(integer, integer)" }
          Constant // { types: "(integer, integer)" }
            - (2, 3)
            - (2, 4)
            - (2, 5)
            - (3, 3)
            - (3, 4)
            - (3, 5)
            - (4, 3)
            - (4, 4)
            - (4, 5)
        ArrangeBy keys=[[]] // { types: "()" }
          Project () // { types: "()" }
            ReadStorage materialize.public.t // { types: "(integer?, integer?)" }

Source materialize.public.t

Target cluster: quickstart

EOF

# Permute the literals around the columns added by FlatMap
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select * from (select 1 as a from t), generate_series(a+1, 4);
----
Explained Query:
  Project (#1, #0) // { arity: 2 }
    Map (1) // { arity: 2 }
      FlatMap generate_series(2, 4, 1) // { arity: 1 }
        Project () // { arity: 0 }
          ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

# Make sure that grouping/distinct is handled correctly in the face of derived tables
# We want the proper interleaving between Map and Distinct to be preserved

# With literals only

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select 123 from (select 234 from t);
----
Explained Query:
  Project (#2) // { arity: 1 }
    Map (123) // { arity: 3 }
      ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select 123 from (select distinct 234 from t);
----
Explained Query:
  Map (123) // { arity: 1 }
    Distinct project=[] // { arity: 0 }
      Project () // { arity: 0 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select distinct 123 from (select 234 from t);
----
Explained Query:
  Map (123) // { arity: 1 }
    Distinct project=[] // { arity: 0 }
      Project () // { arity: 0 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select distinct 123 from (select distinct 234 from t);
----
Explained Query:
  Map (123) // { arity: 1 }
    Distinct project=[] // { arity: 0 }
      Project () // { arity: 0 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

# With a single literal

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select * from (select distinct 123 from t);
----
Explained Query:
  Map (123) // { arity: 1 }
    Distinct project=[] // { arity: 0 }
      Project () // { arity: 0 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select distinct * from (select 123 from t);
----
Explained Query:
  Map (123) // { arity: 1 }
    Distinct project=[] // { arity: 0 }
      Project () // { arity: 0 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

# With a literal in the outer query and a column in the derived table

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select 123 from (select a from t);
----
Explained Query:
  Project (#2) // { arity: 1 }
    Map (123) // { arity: 3 }
      ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select 123 from (select distinct a from t);
----
Explained Query:
  Project (#1) // { arity: 1 }
    Map (123) // { arity: 2 }
      Distinct project=[#0{a}] // { arity: 1 }
        Project (#0{a}) // { arity: 1 }
          ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select distinct 123 from (select a from t);
----
Explained Query:
  Map (123) // { arity: 1 }
    Distinct project=[] // { arity: 0 }
      Project () // { arity: 0 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select distinct 123 from (select distinct a from t);
----
Explained Query:
  Map (123) // { arity: 1 }
    Distinct project=[] // { arity: 0 }
      Project () // { arity: 0 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

# With a literal and a column in the derived table

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select distinct a1.a, a1.literal from (select a, 123 as literal from t) as a1;
----
Explained Query:
  Map (123) // { arity: 2 }
    Distinct project=[#0{a}] // { arity: 1 }
      Project (#0{a}) // { arity: 1 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select a1.a, a1.literal from (select distinct a, 123 as literal from t) as a1;
----
Explained Query:
  Map (123) // { arity: 2 }
    Distinct project=[#0{a}] // { arity: 1 }
      Project (#0{a}) // { arity: 1 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select a1.a, a1.literal from (select distinct a, 123 as literal from t) as a1;
----
Explained Query:
  Map (123) // { arity: 2 }
    Distinct project=[#0{a}] // { arity: 1 }
      Project (#0{a}) // { arity: 1 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select distinct a1.a, a1.literal from (select distinct a, 123 as literal from t) as a1;
----
Explained Query:
  Map (123) // { arity: 2 }
    Distinct project=[#0{a}] // { arity: 1 }
      Project (#0{a}) // { arity: 1 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

# With a literal and a column in the outer query

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select distinct a1.a, 123 from (select a from t) as a1;
----
Explained Query:
  Map (123) // { arity: 2 }
    Distinct project=[#0{a}] // { arity: 1 }
      Project (#0{a}) // { arity: 1 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select distinct a1.a, 123 from (select distinct a from t) as a1;
----
Explained Query:
  Map (123) // { arity: 2 }
    Distinct project=[#0{a}] // { arity: 1 }
      Project (#0{a}) // { arity: 1 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

# With expressions
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select distinct a1.a+2 from (select distinct a+1 as a, 123 as literal from t) as a1;
----
Explained Query:
  Distinct project=[((#0{a} + 1) + 2)] // { arity: 1 }
    Project (#0{a}) // { arity: 1 }
      ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select distinct a1.a, 123 from (select distinct a+1 as a, 234 as literal from t) as a1;
----
Explained Query:
  Map (123) // { arity: 2 }
    Distinct project=[(#0{a} + 1)] // { arity: 1 }
      Project (#0{a}) // { arity: 1 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select distinct a1.a+2, a1.literal from (select distinct a+1 as a, 123 as literal from t) as a1;
----
Explained Query:
  Map (123) // { arity: 2 }
    Distinct project=[((#0{a} + 1) + 2)] // { arity: 1 }
      Project (#0{a}) // { arity: 1 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF


query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select distinct a1.a, a1.literal + 1 from (select distinct a, 123 as literal from t) as a1;
----
Explained Query:
  Map (124) // { arity: 2 }
    Distinct project=[#0{a}] // { arity: 1 }
      Project (#0{a}) // { arity: 1 }
        ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

# Check that literals do not interfere with table elimination

statement ok
create table t_pk (
  a int primary key,
  b int
)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select a1.*, 123 from t_pk as a1, t_pk as a2 WHERE a1.a = a2.a;
----
Explained Query:
  Map (123) // { arity: 3 }
    ReadStorage materialize.public.t_pk // { arity: 2 }

Source materialize.public.t_pk

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select distinct a1.*, 123 from t_pk as a1, t_pk as a2 WHERE a1.a = a2.a;
----
Explained Query:
  Map (123) // { arity: 3 }
    ReadStorage materialize.public.t_pk // { arity: 2 }

Source materialize.public.t_pk

Target cluster: quickstart

EOF

# Reduce
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select a, b, max(2), count(*) from t where b = 1 group by a, b;
----
Explained Query:
  Project (#0{a}, #2, #3, #1{count}) // { arity: 4 }
    Map (1, 2) // { arity: 4 }
      Reduce group_by=[#0{a}] aggregates=[count(*)] // { arity: 2 }
        Project (#0{a}) // { arity: 1 }
          Filter (#1{b} = 1) // { arity: 2 }
            ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t
  filter=((#1{b} = 1))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select a, b, count(*), max(2) from t where b = 1 group by a, b;
----
Explained Query:
  Project (#0{a}, #2, #1{count}, #3) // { arity: 4 }
    Map (1, 2) // { arity: 4 }
      Reduce group_by=[#0{a}] aggregates=[count(*)] // { arity: 2 }
        Project (#0{a}) // { arity: 1 }
          Filter (#1{b} = 1) // { arity: 2 }
            ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t
  filter=((#1{b} = 1))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
select a, b, min(2), max(3) from t where b = 1 group by a, b;
----
Explained Query:
  Map (1, 2, 3) // { arity: 4 }
    Distinct project=[#0{a}] // { arity: 1 }
      Project (#0{a}) // { arity: 1 }
        Filter (#1{b} = 1) // { arity: 2 }
          ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t
  filter=((#1{b} = 1))

Target cluster: quickstart

EOF

# regression tests for database-issues#2159
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select min(1/x) from (select a as y, 0 as x from t);
----
Explained Query:
  With
    cte l0 =
      Distinct project=[] // { arity: 0 }
        Project () // { arity: 0 }
          ReadStorage materialize.public.t // { arity: 2 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Map (error("division by zero")) // { arity: 1 }
        Get l0 // { arity: 0 }
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Get l0 // { arity: 0 }
          Constant // { arity: 0 }
            - ()

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select sum(1/x) from (select a as y, 0 as x from t);
----
Explained Query:
  With
    cte l0 =
      Distinct project=[] // { arity: 0 }
        Project () // { arity: 0 }
          ReadStorage materialize.public.t // { arity: 2 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Map (error("division by zero")) // { arity: 1 }
        Get l0 // { arity: 0 }
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Get l0 // { arity: 0 }
          Constant // { arity: 0 }
            - ()

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select min(a) from t_pk where a between 38 and 195 and a = (select a from t where a = 1308);
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[min(#0{a})] // { arity: 1 }
        Project (#0{a}) // { arity: 1 }
          Join on=(#0{a} = #1) type=differential // { arity: 2 }
            implementation
              %0:t_pk[#0{a}]UKiif » %1[#0]Kiif
            ArrangeBy keys=[[#0{a}]] // { arity: 1 }
              Project (#0{a}) // { arity: 1 }
                Filter (#0{a} <= 195) AND (#0{a} >= 38) // { arity: 2 }
                  ReadStorage materialize.public.t_pk // { arity: 2 }
            ArrangeBy keys=[[#0]] // { arity: 1 }
              Project (#1) // { arity: 1 }
                FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
                  Reduce aggregates=[count(*)] // { arity: 1 }
                    Project () // { arity: 0 }
                      Filter (#0{a} = 1308) // { arity: 2 }
                        ReadStorage materialize.public.t // { arity: 2 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l0 // { arity: 1 }
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l0 // { arity: 1 }
          Constant // { arity: 0 }
            - ()

Source materialize.public.t
  filter=((#0{a} = 1308))
Source materialize.public.t_pk
  filter=((#0{a} <= 195) AND (#0{a} >= 38))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select min(a) from t where a between 38 and 195 and a = (select a from t where a = 1308);
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[min(#0{a})] // { arity: 1 }
        Project (#0{a}) // { arity: 1 }
          Join on=(#0{a} = #1) type=differential // { arity: 2 }
            implementation
              %0:t[#0{a}]Kiif » %1[#0]Kiif
            ArrangeBy keys=[[#0{a}]] // { arity: 1 }
              Project (#0{a}) // { arity: 1 }
                Filter (#0{a} <= 195) AND (#0{a} >= 38) // { arity: 2 }
                  ReadStorage materialize.public.t // { arity: 2 }
            ArrangeBy keys=[[#0]] // { arity: 1 }
              Project (#1) // { arity: 1 }
                FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
                  Reduce aggregates=[count(*)] // { arity: 1 }
                    Project () // { arity: 0 }
                      Filter (#0{a} = 1308) // { arity: 2 }
                        ReadStorage materialize.public.t // { arity: 2 }
  Return // { arity: 1 }
    Union // { arity: 1 }
      Get l0 // { arity: 1 }
      Map (null) // { arity: 1 }
        Union // { arity: 0 }
          Negate // { arity: 0 }
            Project () // { arity: 0 }
              Get l0 // { arity: 1 }
          Constant // { arity: 0 }
            - ()

Source materialize.public.t

Target cluster: quickstart

EOF

statement ok
insert into t_pk values (40);

# check no error is returned, but a NULL result
query I
select min(a) from t_pk where a between 38 and 195 and a = (select a from t where a = 1308);
----
NULL

statement ok
insert into t values (1308);

query I
select min(a) from t_pk where a between 38 and 195 and a = (select a from t where a = 1308);
----
NULL

statement ok
insert into t_pk values (1308);

query I
select min(a) from t_pk where a between 38 and 195 and a = (select a from t where a = 1308);
----
NULL

statement ok
insert into t values (1308);

statement error more than one record produced in subquery
select min(a) from t_pk where a between 38 and 195 and a = (select a from t where a = 1308);

# check that literal errors are not lifted beyond Joins, but also that that doesn't lead to
# a stack overflow due to LiteralLifting and JoinImplementation fighting against each other
statement ok
create table t1 (f1 double precision, f2 double precision not null);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select * from t1 as a1 join t1 as a2 on (a2.f2 = (select 6 from t1)) where a2.f2 = 9;
----
Explained Query:
  CrossJoin type=delta // { arity: 4 }
    implementation
      %0:t1 » %1:t1[×]ef » %2[×]ef
      %1:t1 » %2[×]ef » %0:t1[×]
      %2 » %1:t1[×]ef » %0:t1[×]
    ArrangeBy keys=[[]] // { arity: 2 }
      ReadStorage materialize.public.t1 // { arity: 2 }
    ArrangeBy keys=[[]] // { arity: 2 }
      Filter (#1{f2} = 9) // { arity: 2 }
        ReadStorage materialize.public.t1 // { arity: 2 }
    ArrangeBy keys=[[]] // { arity: 0 }
      Project () // { arity: 0 }
        Filter (9 = integer_to_double(#1)) // { arity: 2 }
          FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
            Reduce aggregates=[count(*)] // { arity: 1 }
              Project () // { arity: 0 }
                ReadStorage materialize.public.t1 // { arity: 2 }

Source materialize.public.t1

Target cluster: quickstart

EOF

# count(null) is always 0
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select count(null) from t;
----
Explained Query (fast path):
  Constant
    - (0)

Target cluster: quickstart

EOF

statement ok
DROP TABLE t1;

statement ok
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER);

# WITH MUTUALLY RECURSIVE support
# -------------------------------

# Single non-recursive binding under `WITH MUTUALLY RECURSIVE`
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(x INT, y INT) AS (
    SELECT * FROM c0
    UNION
    SELECT f1, 42 FROM t1
    UNION
    SELECT f2, 42 FROM t1
  )
SELECT * FROM c0
----
Explained Query:
  With Mutually Recursive
    cte l0 =
      Map (42) // { arity: 2 }
        Distinct project=[#0{f1}] // { arity: 1 }
          Union // { arity: 1 }
            Project (#0{f1}) // { arity: 1 }
              Get l0 // { arity: 2 }
            Project (#0{f1}) // { arity: 1 }
              ReadStorage materialize.public.t1 // { arity: 2 }
            Project (#1{f2}) // { arity: 1 }
              ReadStorage materialize.public.t1 // { arity: 2 }
  Return // { arity: 2 }
    Get l0 // { arity: 2 }

Source materialize.public.t1

Target cluster: quickstart

EOF

# Multiple bindings under `WITH MUTUALLY RECURSIVE`
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
WITH MUTUALLY RECURSIVE
  c0(x INT, y INT) AS (
    SELECT f1, 42 FROM t1
    UNION
    SELECT f2, 42 FROM t1
  ),
  c1(x INT, y INT) AS (
    SELECT * FROM c0
    UNION
    SELECT * FROM c1
  )
SELECT * FROM c0 UNION ALL SELECT * FROM c1
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{f1}] // { arity: 1 }
        Union // { arity: 1 }
          Project (#0{f1}) // { arity: 1 }
            ReadStorage materialize.public.t1 // { arity: 2 }
          Project (#1{f2}) // { arity: 1 }
            ReadStorage materialize.public.t1 // { arity: 2 }
  Return // { arity: 2 }
    With Mutually Recursive
      cte l1 =
        Map (42) // { arity: 2 }
          Distinct project=[#0{f1}] // { arity: 1 }
            Union // { arity: 1 }
              Get l0 // { arity: 1 }
              Project (#0{f1}) // { arity: 1 }
                Get l1 // { arity: 2 }
    Return // { arity: 2 }
      Union // { arity: 2 }
        Map (42) // { arity: 2 }
          Get l0 // { arity: 1 }
        Get l1 // { arity: 2 }

Source materialize.public.t1

Target cluster: quickstart

EOF