Materialize/test/sqllogictest/github-2969.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.

# Regression test for https://github.com/MaterializeInc/database-issues/issues/2969.

statement ok
CREATE TABLE table_f1 (f1 INTEGER);

statement ok
CREATE TAble table_f4 (f4 INTEGER);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM table_f1 , LATERAL ( SELECT * FROM (  table_f4 AS a1 LEFT JOIN table_f4 AS a2 ON a1.f4 = a2.f4 ) WHERE a1.f4 <= f1  ) WHERE  f1 IS  NULL;
----
Explained Query (fast path):
  Constant <empty>

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM table_f1 , LATERAL ( SELECT * FROM (  table_f4 AS a1 LEFT JOIN table_f4 AS a2 ON a1.f4 = a2.f4 ) WHERE a1.f4 <= f1  ) WHERE  f1 IS  NULL;
----
Explained Query (fast path):
  Constant <empty>

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM table_f1 , LATERAL ( SELECT * FROM (  table_f4 AS a1 LEFT JOIN table_f4 AS a2 ON a1.f4 = a2.f4 ) WHERE a1.f4 <= f1  ) WHERE  f1 IS  NULL;
----
Explained Query (fast path):
  Constant <empty>

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM table_f1 , LATERAL ( SELECT * FROM (  table_f4 AS a1 LEFT JOIN table_f4 AS a2 ON a1.f4 = a2.f4 ) WHERE a1.f4 <= f1  ) WHERE  f1 IS  NULL;
----
Explained Query (fast path):
  Constant <empty>

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM table_f1 , LATERAL ( SELECT * FROM (  table_f4 AS a1 LEFT JOIN table_f4 AS a2 ON a1.f4 = a2.f4 ) WHERE a1.f4 <= f1  ) WHERE  f1 IS  NULL;
----
Explained Query (fast path):
  Constant <empty>

Target cluster: quickstart

EOF

statement ok
CREATE TAble table_f4_f5_f6 (f4 INTEGER, f5 INTEGER, f6 INTEGER);

statement ok
CREATE TAble table_f5_f6 (f5 INTEGER, f6 INTEGER);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT *  FROM table_f1 , ( table_f4_f5_f6 AS a2 LEFT JOIN table_f5_f6 AS a3 USING ( f5 , f6  ) ) WHERE f5 = f6 AND  f4 = f6;
----
Explained Query:
  With
    cte l0 =
      Project (#0{f4}..=#2{f6}) // { arity: 3 }
        Join on=(#1{f5} = #3{f5}) type=differential // { arity: 4 }
          implementation
            %0:table_f4_f5_f6[#1]Kf » %1:table_f5_f6[#0{f5}]Kf
          ArrangeBy keys=[[#1{f5}]] // { arity: 3 }
            Filter (#1{f5} = #2{f6}) // { arity: 3 }
              ReadStorage materialize.public.table_f4_f5_f6 // { arity: 3 }
          ArrangeBy keys=[[#0{f5}]] // { arity: 1 }
            Project (#0{f5}) // { arity: 1 }
              Filter (#0{f5} = #1{f6}) // { arity: 2 }
                ReadStorage materialize.public.table_f5_f6 // { arity: 2 }
    cte l1 =
      Filter (#0{f4} = #1{f5}) AND (#0{f4} = #2{f6}) AND (#1{f5} = #2{f6}) // { arity: 3 }
        ReadStorage materialize.public.table_f4_f5_f6 // { arity: 3 }
  Return // { arity: 4 }
    Project (#0{f1}, #2{f5}, #3{f6}, #1{f4}) // { arity: 4 }
      CrossJoin type=differential // { arity: 4 }
        implementation
          %0:table_f1[×] » %1[×]
        ArrangeBy keys=[[]] // { arity: 1 }
          ReadStorage materialize.public.table_f1 // { arity: 1 }
        ArrangeBy keys=[[]] // { arity: 3 }
          Union // { arity: 3 }
            Negate // { arity: 3 }
              Project (#0{f4}..=#2{f6}) // { arity: 3 }
                Join on=(#0{f4} = #3{f5}) type=differential // { arity: 4 }
                  implementation
                    %0:l1[#0]Kf » %1[#0]Kf
                  ArrangeBy keys=[[#0{f4}]] // { arity: 3 }
                    Get l1 // { arity: 3 }
                  ArrangeBy keys=[[#0{f5}]] // { arity: 1 }
                    Project (#0{f5}) // { arity: 1 }
                      Distinct project=[#0{f5}, #1{f6}] // { arity: 2 }
                        Project (#1{f5}, #2{f6}) // { arity: 2 }
                          Get l0 // { arity: 3 }
            Get l1 // { arity: 3 }
            Filter (#0{f4} = #1{f5}) // { arity: 3 }
              Get l0 // { arity: 3 }

Source materialize.public.table_f1
Source materialize.public.table_f4_f5_f6
  filter=((#1{f5} = #2{f6}))
Source materialize.public.table_f5_f6
  filter=((#0{f5} = #1{f6}))

Target cluster: quickstart

EOF