# 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 test1 (a int, b int);

statement ok
create table test2 (a int, b int);

statement ok
create view test3 as select a, b, count(*) as c from test2 group by a, b;

statement ok
insert into test1 values (1, 3);

statement ok
insert into test2 values (1, 2), (1, 3);

statement error more than one record produced in subquery
select a, b, ( select c from test3 where a = test3.a and b = test3.b) from test1;

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR select a, b, ( select c from test3 where a = test3.a and b = test3.b) from test1;
----
Explained Query:
  With
    cte l0 =
      Project (#2{count}) // { arity: 1 }
        Reduce group_by=[#0{a}, #1{b}] aggregates=[count(*)] // { arity: 3 }
          Filter (#0{a} = #0{a}) AND (#1{b} = #1{b}) // { arity: 2 }
            ReadStorage materialize.public.test2 // { arity: 2 }
    cte l1 =
      Union // { arity: 1 }
        Get l0 // { arity: 1 }
        Project (#1) // { arity: 1 }
          FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
            Reduce aggregates=[count(*)] // { arity: 1 }
              Project () // { arity: 0 }
                Get l0 // { arity: 1 }
  Return // { arity: 3 }
    CrossJoin type=differential // { arity: 3 }
      implementation
        %0:test1[×] » %1[×]
      ArrangeBy keys=[[]] // { arity: 2 }
        ReadStorage materialize.public.test1 // { arity: 2 }
      ArrangeBy keys=[[]] // { arity: 1 }
        Union // { arity: 1 }
          Get l1 // { arity: 1 }
          Map (null) // { arity: 1 }
            Union // { arity: 0 }
              Negate // { arity: 0 }
                Distinct project=[] // { arity: 0 }
                  Project () // { arity: 0 }
                    Get l1 // { arity: 1 }
              Constant // { arity: 0 }
                - ()

Source materialize.public.test1
Source materialize.public.test2
  filter=((#0{a} = #0{a}) AND (#1{b} = #1{b}))

Target cluster: quickstart

EOF