# 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/5126
mode cockroach

# reset
statement ok
DROP TABLE IF EXISTS t1 CASCADE

statement ok
DROP TABLE IF EXISTS t2 CASCADE

# schema
statement ok
CREATE TABLE t1(f1 INT, f2 INT, f3 INT)

statement ok
CREATE TABLE t2(f2 INT, f3 INT)

# data
statement ok
INSERT INTO t1 VALUES (1, 2, 3)

statement ok
INSERT INTO t2 VALUES (2, 3)

query T multiline
EXPLAIN DECORRELATED PLAN WITH(arity) FOR
SELECT
  sq1.*
FROM
  (
    SELECT * FROM t1
  ) AS sq0,
  LATERAL(
    SELECT
      ( sq0.f2 * f2 ) as c0,
      ( SELECT 3 ) as c1,
      ( sq0.f2 * f2 ) * 2 as c2
    FROM t2
  ) AS sq1
----
With
  cte l0 =
    CrossJoin // { arity: 3 }
      Constant // { arity: 0 }
        - ()
      Get materialize.public.t1 // { arity: 3 }
  cte l1 =
    CrossJoin // { arity: 3 }
      Distinct project=[#1] // { arity: 1 }
        Get l0 // { arity: 3 }
      Get materialize.public.t2 // { arity: 2 }
  cte l2 =
    Distinct project=[#0..=#2] // { arity: 3 }
      Get l1 // { arity: 3 }
  cte l3 =
    Project (#0..=#3) // { arity: 4 }
      Map (3) // { arity: 4 }
        Get l2 // { arity: 3 }
  cte l4 =
    Union // { arity: 4 }
      Get l3 // { arity: 4 }
      Project (#0..=#2, #4) // { arity: 4 }
        FlatMap guard_subquery_size(#3) // { arity: 5 }
          Reduce group_by=[#0..=#2] aggregates=[count(*)] // { arity: 4 }
            Get l3 // { arity: 4 }
Return // { arity: 3 }
  Project (#3..=#5) // { arity: 3 }
    Project (#0..=#2, #4..=#6) // { arity: 6 }
      Join on=(#1 = #3) // { arity: 7 }
        Get l0 // { arity: 3 }
        Project (#0, #3..=#5) // { arity: 4 }
          Map ((#3{"?column?"} * 2)) // { arity: 6 }
            Project (#0..=#2, #7, #8) // { arity: 5 }
              Map ((#0{f2} * #1{f2}), #6) // { arity: 9 }
                Join on=(#0 = #3 AND #1 = #4 AND #2 = #5) // { arity: 7 }
                  Get l1 // { arity: 3 }
                  Union // { arity: 4 }
                    Get l4 // { arity: 4 }
                    CrossJoin // { arity: 4 }
                      Project (#0..=#2) // { arity: 3 }
                        Join on=(#0 = #3 AND #1 = #4 AND #2 = #5) // { arity: 6 }
                          Union // { arity: 3 }
                            Negate // { arity: 3 }
                              Distinct project=[#0..=#2] // { arity: 3 }
                                Get l4 // { arity: 4 }
                            Distinct project=[#0..=#2] // { arity: 3 }
                              Get l2 // { arity: 3 }
                          Get l2 // { arity: 3 }
                      Constant // { arity: 1 }
                        - (null)

Target cluster: quickstart

EOF



query III rowsort
SELECT
  sq1.*
FROM
  (
    SELECT * FROM t1
  ) AS sq0,
  LATERAL(
    SELECT
      ( sq0.f2 * f2 ) as c0,
      ( SELECT 3 ) as c1,
      ( sq0.f2 * f2 ) * 2 as c2
    FROM t2
  ) AS sq1
----
4  3  8