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

statement ok
CREATE TABLE t1 (a int, b int)

statement ok
INSERT INTO t1 values (1, 2)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT (record).f2 FROM (SELECT ROW(a, a) AS record FROM t1);
----
Explained Query:
  Project (#0{a}) // { arity: 1 }
    ReadStorage materialize.public.t1 // { arity: 2 }

Source materialize.public.t1

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT record, (record).f2 FROM (SELECT ROW(a, a) AS record FROM t1);
----
Explained Query:
  Project (#2, #3) // { arity: 2 }
    Map (row(#0{a}, #0{a}), record_get[1](#2{record})) // { arity: 4 }
      ReadStorage materialize.public.t1 // { arity: 2 }

Source materialize.public.t1

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT (COALESCE(record, ROW(NULL, NULL))).f2 FROM (SELECT ROW(a, a) AS record FROM t1)
----
Explained Query:
  Project (#0{a}) // { arity: 1 }
    ReadStorage materialize.public.t1 // { arity: 2 }

Source materialize.public.t1

Target cluster: quickstart

EOF

query T
SELECT abc FROM (VALUES (1, 2, (3,4), ROW(5, 6, 7))) as abc;
----
(1,2,"(3,4)","(5,6,7)")

# MirScalarExpr::reduce() should transform
# Literal([c1, c2]) = record_create(e1, e2)
# into
# c1 = e1 AND c2 = e2
#
# If this test fails in the future, one possible reason is the canonical ordering having been changed between
# MirScalarExpr::Literal and MirScalarExpr::CallVariadic, because then the argument ordering of the `Eq` changes, so
# reduce() doesn't recognize the pattern anymore.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 WHERE (t1.a, t1.b) IN ((1,2))
----
Explained Query:
  Filter (#0{a} = 1) AND (#1{b} = 2) // { arity: 2 }
    ReadStorage materialize.public.t1 // { arity: 2 }

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

Target cluster: quickstart

EOF