github-14116.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 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 with(arity, join_impls) select a, b, ( select c from test3 where a = test3.a and b = test3.b) from test1;
----
Explained Query:
  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 // { arity: 0 }
                  Project () // { arity: 0 }
                    Get l1 // { arity: 1 }
              Constant // { arity: 0 }
                - ()
  With
    cte l1 =
      Union // { arity: 1 }
        Get l0 // { arity: 1 }
        Map (error("more than one record produced in subquery")) // { arity: 1 }
          Project () // { arity: 0 }
            Filter (#0 > 1) // { arity: 1 }
              Reduce aggregates=[count(*)] // { arity: 1 }
                Project () // { arity: 0 }
                  Get l0 // { arity: 1 }
    cte l0 =
      Project (#2) // { arity: 1 }
        Reduce group_by=[#0, #1] aggregates=[count(*)] // { arity: 3 }
          Filter (#0 = #0) AND (#1 = #1) // { arity: 2 }
            ReadStorage materialize.public.test2 // { arity: 2 }

Source materialize.public.test2
  filter=((#0 = #0) AND (#1 = #1))

EOF