Materialize/test/sqllogictest/postgres/rowtypes.slt

# Copyright 1994, Regents of the University of California.
# Copyright 1996-2021 PostgreSQL Global Development Group.
# 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.
#
# This file is derived from the regression test suite in PostgreSQL.
# The original file was retrieved on February 17, 2022 from:
#
#     https://github.com/postgres/postgres/blob/c30f54ad732ca5c8762bb68bbe0f51de9137dd72/src/test/regress/sql/rowtypes.sql
#
# The original source code is subject to the terms of the PostgreSQL
# license, a copy of which can be found in the LICENSE file at the
# root of this repository.

## tests omitted from the postgres test suite:
##    * table rowtypes
##    * `create function`
##    * toasted references
##    * functional notation
##    * ordering of rowtypes with non-comparable fields
##    * binary representation operators

mode cockroach

# make a standalone composite type
statement ok
create type complex as (r float8, i float8);

# Nested composite
statement ok
create type quad as (c1 complex, c2 complex);

# and with various container types as fields
statement ok
CREATE TYPE int4_list AS LIST (ELEMENT TYPE = int4);

statement ok
CREATE TYPE int4_map AS MAP (KEY TYPE = text, VALUE TYPE = int4);

statement ok
CREATE TYPE melange AS (a int[][], b text[], c int4_list, d int4_map);

# Some simple tests of conversions and row construction
query T
select (1.1, 2.2)::complex
----
(1.1,2.2)

query T
select ROW((1.1, 2.2), ROW(3.3, NULL))::quad
----
 ("(1.1,2.2)","(3.3,)")

query TT
select (1.1,2.2)::complex, row((3.3,4.4),(5.5,null))::quad;
----
(1.1,2.2) ("(3.3,4.4)","(5.5,)")

query T
select ROW(ROW(1.1, 2.2), ROW(3.3, 4.4))::quad
----
 ("(1.1,2.2)","(3.3,4.4)")

query T
select ROW((1.1, 2.2), ROW(3.3, 4.4)::complex)::quad
----
 ("(1.1,2.2)","(3.3,4.4)")

query T
select ROW(ARRAY[[1, 2], [3, 4]], ARRAY['abc', 'def'], LIST[5, 6, 7], '{g=>8, h=>9}')::melange::text;
----
({{1,2},{3,4}},{abc,def},{5,6,7},{g=>8,h=>9})

# implicit casting of nested records to a named composite type

statement ok
create table quadtable(f1 int, q quad);

statement ok
insert into quadtable values (1, ((3.3,4.4),(5.5,6.6)));

statement ok
insert into quadtable values (2, ((null,4.4),(5.5,6.6)));

query IT rowsort
select * from quadtable;
----
1 ("(3.3,4.4)","(5.5,6.6)")
2 ("(,4.4)","(5.5,6.6)")

query error does not exist
select f1, q.c1 from quadtable;

query ITR rowsort
select f1, (q).c1, (qq.q).c1.i from quadtable qq;
----
1 (3.3,4.4) 4.4
2 (,4.4)    4.4

## TODO: support inserts and updates for named composite type fields
#statement ok
#insert into quadtable (f1, q.c1.r, q.c2.i) values(44,55,66);
#
#statement ok
#update quadtable set q.c1.r = 12 where f1 = 2;
#
#query error oh no
#update quadtable set q.c1 = 12;
#
#query IT rowsort
#select * from quadtable;
#----
#1 ("(3.3,4.4)","(5.5,6.6)")
#2 ("(12,4.4)","(5.5,6.6)"
#44 ("(55,)","(,66)")

query B
select ROW(1,2) < ROW(1,3) as true;
----
true

query B
select ROW(1,2) < ROW(1,1) as false;
----
false

query B
select ROW(1,2) < ROW(1,NULL) is null;
----
true

query B
select ROW(1,2,3) < ROW(1,3,NULL) as true;
----
true

query B
select ROW(11,'ABC') < ROW(11,'DEF') as true;
----
true

query B
select ROW(11,'ABC') > ROW(11,'DEF') as false;
----
false

query B
select ROW(12,'ABC') > ROW(11,'DEF') as true;
----
true

query B
select ROW(1,2,3) < ROW(1,NULL,4) is null;
----
true

query B
select ROW(1,2,3) = ROW(1,NULL,4) as false;
----
false

query B
select ROW(1,2,3) <> ROW(1,NULL,4) as true;
----
true

query B
select ROW(1,2) = ROW(1,2::int8);
----
true

query B
select ROW(1,2) in (ROW(3,4), ROW(1,2));
----
true

query B
select ROW(1,2) in (ROW(3,4), ROW(1,2::int8));
----
true

statement ok
CREATE TABLE tenk1 (
  unique1 int4,
  unique2 int4,
  two int4,
  four int4,
  ten int4,
  twenty int4,
  hundred int4,
  thousand int4,
  twothousand int4,
  fivethous int4,
  tenthous int4,
  odd int4,
  even int4,
  stringu1 text,
  stringu2 text,
  string4 text
)

copy tenk1 test/sqllogictest/postgres/testdata/tenk.data

# Check row comparison with a subselect

query II
select unique1, unique2 from tenk1
where (unique1, unique2) < any (select ten, ten from tenk1 where hundred < 3)
      and unique1 <= 20
order by 1;
----
0 9998
1 2838

query II
select thousand, tenthous from tenk1
where (thousand, tenthous) >= (997, 5000)
order by thousand, tenthous;
----
997 5997
997 6997
997 7997
997 8997
997 9997
998  998
998 1998
998 2998
998 3998
998 4998
998 5998
998 6998
998 7998
998 8998
998 9998
999  999
999 1999
999 2999
999 3999
999 4999
999 5999
999 6999
999 7999
999 8999
999 9999

query III
select thousand, tenthous, four from tenk1
where (thousand, tenthous, four) > (998, 5000, 3)
order by thousand, tenthous;
----
998 5998 2
998 6998 2
998 7998 2
998 8998 2
998 9998 2
999  999 3
999 1999 3
999 2999 3
999 3999 3
999 4999 3
999 5999 3
999 6999 3
999 7999 3
999 8999 3
999 9999 3

query II
select thousand, tenthous from tenk1
where (998, 5000) < (thousand, tenthous)
order by thousand, tenthous;
----
998 5998
998 6998
998 7998
998 8998
998 9998
999  999
999 1999
999 2999
999 3999
999 4999
999 5999
999 6999
999 7999
999 8999
999 9999

query II
select thousand, hundred from tenk1
where (998, 5000) < (thousand, hundred)
order by thousand, hundred;
----
999 99
999 99
999 99
999 99
999 99
999 99
999 99
999 99
999 99
999 99

## Check ability to select columns from an anonymous rowtype
query I
select (row(1, 2.0)).f1;
----
1

# TODO: there's an incompatibility here, postgres returns 2.0 (this issue is not specific to rowtypes)
query R
select (row(1, 2.0)).f2;
----
2

query error nosuch not found
select (row(1, 2.0)).nosuch;

query IR
select (row(1, 2.0)).*;
----
1 2

query I
select (r).f1 from (select row(1, 2.0) as r) ss;
----
1

query error f3 not found
select (r).f3 from (select row(1, 2.0) as r) ss;

query IR
select (r).* from (select row(1, 2.0) as r) ss;
----
1 2

## Check some corner cases involving empty rowtypes
query T
select ROW();
----
()

# TODO: empty row should be considered null
#query B
#select ROW() IS NULL;
#----
#true

query error cannot compare rows of zero length
select ROW() = ROW();

## TODO: support arrays of anonymous rowtypes
# Check ability to create arrays of anonymous rowtypes
#select array[ row(1,2), row(3,4), row(5,6) ];
# Check ability to compare an anonymous row to elements of an array
#select row(1,1.1) = any (array[ row(7,7.7), row(1,1.1), row(0,0.0) ]);
#select row(1,1.1) = any (array[ row(7,7.7), row(1,1.0), row(0,0.0) ]);


## Binary comparison functions between row types

statement ok
create type testtype1 as (a int, b int);

query B
select row(1, 2)::testtype1 < row(1, 3)::testtype1;
----
true

query B
select row(1, 2)::testtype1 <= row(1, 3)::testtype1;
----
true

query B
select row(1, 2)::testtype1 = row(1, 2)::testtype1;
----
true

query B
select row(1, 2)::testtype1 <> row(1, 3)::testtype1;
----
true

query B
select row(1, 3)::testtype1 >= row(1, 2)::testtype1;
----
true

query B
select row(1, 3)::testtype1 > row(1, 2)::testtype1;
----
true

query B
select row(1, -2)::testtype1 < row(1, -3)::testtype1;
----
false

query B
select row(1, -2)::testtype1 <= row(1, -3)::testtype1;
----
false

query B
select row(1, -2)::testtype1 = row(1, -3)::testtype1;
----
false

query B
select row(1, -2)::testtype1 <> row(1, -2)::testtype1;
----
false

query B
select row(1, -3)::testtype1 >= row(1, -2)::testtype1;
----
false

query B
select row(1, -3)::testtype1 > row(1, -2)::testtype1;
----
false

query B
select row(1, -2)::testtype1 < row(1, 3)::testtype1;
----
true

statement ok
create type testtype3 as (a int, b text);

# note: error message here differs from postgres, which is
# more specific about whether column type or count mismatched
query error no overload
select row(1, 2)::testtype1 < row(1, 'abc')::testtype3;

query error no overload
select row(1, 2)::testtype1 <> row(1, 'abc')::testtype3;

statement ok
create type testtype5 as (a int);

query error no overload
select row(1, 2)::testtype1 < row(1)::testtype5;

query error no overload
select row(1, 2)::testtype1 <> row(1)::testtype5;

# just for fun, let's try everything at once. let's combine some
# implicit + explicit casts + field access over nested structs
# that are structurally equivalent, but not the same. at the end
# of the day, postgres/we should be doing nothing more than
# field-by-field comparison of each row, regardless of named type.

statement ok
create type testtype6 as (inner testtype1);

statement ok
create type testtype8 as (f1 int, f2 int);

query B
select row(4,2)::testtype8 > (row(row(4,1))::testtype6).inner;
----
true

query B
select row(4,2)::testtype8 > (row(row(4,2))::testtype6).inner;
----
false

query B
select row(4,2)::testtype8 = (row(row(4,2))::testtype6).inner;
----
true

query error input of anonymous composite types is not implemented
select row(4,2)::testtype8 = '(1,2)';

# TODO: we don't support aliasing into a rowtype
# anonymous rowtypes in coldeflists (column definition lists)
#select q.a, q.b = row(2), q.c = array[row(3)], q.d = row(row(4)) from
#    unnest(array[row(1, row(2), array[row(3)], row(row(4))),
#                 row(2, row(3), array[row(4)], row(row(5)))])
#      as q(a int, b record, c record[], d record);

# IS [NOT] NULL should not recurse into nested composites
#select r, r is null as isnull, r is not null as isnotnull
#from (values (1,row(1,2)), (1,row(null,null)), (1,null),
#             (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b);
#
#select r, r is null as isnull, r is not null as isnotnull
#from (values (1,row(1,2)), (1,row(null,null)), (1,null),
#             (null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b);
#
#explain (verbose, costs off)
#with r(a,b) as materialized
#  (values (1,row(1,2)), (1,row(null,null)), (1,null),
#          (null,row(1,2)), (null,row(null,null)), (null,null) )
#select r, r is null as isnull, r is not null as isnotnull from r;
#
#with r(a,b) as materialized
#  (values (1,row(1,2)), (1,row(null,null)), (1,null),
#          (null,row(1,2)), (null,row(null,null)), (null,null) )
#select r, r is null as isnull, r is not null as isnotnull from r;