# 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;