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

query error DISTINCT specified, but now is not an aggregate function
SELECT now(DISTINCT)

query error DISTINCT specified, but round is not an aggregate function
SELECT round(DISTINCT 1)

# Test date_trunc()

# TODO: PostgreSQL truncates trailing zeros from seconds, we do not.
# This test should return:  2019-11-26 15:56:46.24115
query T
SELECT date_trunc('microseconds', TIMESTAMP '2019-11-26 15:56:46.241150')
----
2019-11-26 15:56:46.24115

query T
SELECT date_trunc('milliseconds', TIMESTAMP '2019-11-26 15:56:46.241150')
----
2019-11-26 15:56:46.241

query T
SELECT date_trunc('second', TIMESTAMP '2019-11-26 15:56:46.241150')
----
2019-11-26 15:56:46

query T
SELECT date_trunc('minute', TIMESTAMP '2019-11-26 15:56:46.241150')
----
2019-11-26 15:56:00

query T
SELECT date_trunc('hour', TIMESTAMP '2019-11-26 15:56:46.241150')
----
2019-11-26 15:00:00

query T
SELECT date_trunc('day', TIMESTAMP '2019-11-26 15:56:46.241150')
----
2019-11-26 00:00:00

query T
SELECT date_trunc('week', TIMESTAMP '2019-11-26 15:56:46.241150')
----
2019-11-25 00:00:00

query T
SELECT date_trunc('week', TIMESTAMP '2020-08-02 00:00:00')
----
2020-07-27 00:00:00

query error timestamp out of range
SELECT date_trunc('week', make_timestamp(-262143, 1, 1, 0, 0, 0))

query T
SELECT date_trunc('month', TIMESTAMP '2019-11-26 15:56:46.241150')
----
2019-11-01 00:00:00

query T
SELECT date_trunc('quarter', TIMESTAMP '2019-11-26 15:56:46.241150')
----
2019-10-01 00:00:00

query T
SELECT date_trunc('year', TIMESTAMP '2019-11-26 15:56:46.241150')
----
2019-01-01 00:00:00

query T
SELECT date_trunc('decade', TIMESTAMP '2019-11-26 15:56:46.241150')
----
2010-01-01 00:00:00

# Expects the first year of the century, meaning 2001 instead of 2000.
query T
SELECT date_trunc('century', TIMESTAMP '2019-11-26 15:56:46.241150')
----
2001-01-01 00:00:00

# Round down century correctly, meaning 2000 is rounded to 1901.
query T
SELECT date_trunc('century', TIMESTAMP '2000-11-26 15:56:46.241150')
----
1901-01-01 00:00:00

# Expects the first year of the millennium, meaning 2001 instead of 2000.
query T
SELECT date_trunc('millennium', TIMESTAMP '2019-11-26 15:56:46.241150')
----
2001-01-01 00:00:00

# Round down millennium correctly, meaning 2000 is rounded to 1001.
query T
SELECT date_trunc('millennium', TIMESTAMP '2000-11-26 15:56:46.241150')
----
1001-01-01 00:00:00

# TODO: Currently we do not parse BC/AD and it's erroneously considered a named time zone, so INTERVAL is used.
# Expects the decade to be rounded down for BC.
query T
SELECT date_trunc('decade', TIMESTAMP '0001-01-01 00:00:00.000000' - INTERVAL '2'YEAR)
----
0011-01-01 00:00:00 BC

# Expects the century to be rounded down for BC.
query T
SELECT date_trunc('century', TIMESTAMP '0001-01-01 00:00:00.000000' - INTERVAL '1'SECOND)
----
0100-01-01 00:00:00 BC

# Expects the millennium to be rounded down for BC.
query T
SELECT date_trunc('millennium', TIMESTAMP '0001-01-01 00:00:00.000000' - INTERVAL '1'SECOND)
----
1000-01-01 00:00:00 BC

query error unit 'bad' not recognized
SELECT date_trunc('bad', TIMESTAMP '2019-11-26 15:56:46.241150')

query T
SELECT date_trunc('minute', TIMESTAMPTZ '1999-12-31 16:16:01+02:30')
----
1999-12-31 13:46:00+00

query T
SELECT date_trunc('day', TIMESTAMPTZ '1999-12-31 16:16:01+02:30')
----
1999-12-31 00:00:00+00

query error unit 'bad' not recognized
SELECT date_trunc('bad', TIMESTAMPTZ '1999-12-31 16:16:01+02:30')

statement ok
CREATE TABLE date_trunc_fields (
    field text
)

statement ok
INSERT INTO date_trunc_fields VALUES ('day'), ('DaY'), ('month'), ('MoNTH')

query T rowsort
SELECT date_trunc(field, TIMESTAMP '2019-11-26 15:56:46.241150') FROM date_trunc_fields
----
2019-11-26 00:00:00
2019-11-26 00:00:00
2019-11-01 00:00:00
2019-11-01 00:00:00

statement ok
INSERT INTO date_trunc_fields VALUES ('bad')

query error unit 'bad' not recognized
SELECT date_trunc(field, TIMESTAMP '2019-11-26 15:56:46.241150') FROM date_trunc_fields

# date_trunc with interval
query T
SELECT date_trunc('microseconds',  INTERVAL '1234 years 11 months 23 days 23:59:12.123456789') AS t;
----
1234 years 11 months 23 days 23:59:12.123457

query T
SELECT date_trunc('milliseconds',  INTERVAL '1234 years 11 months 23 days 23:59:12.123456789') AS t;
----
1234 years 11 months 23 days 23:59:12.123

query T
SELECT date_trunc('milliseconds',  INTERVAL '1234 years 11 months 23 days 23:59:12.123456789') AS t;
----
1234 years 11 months 23 days 23:59:12.123

query T
SELECT date_trunc('seconds',  INTERVAL '1234 years 11 months 23 days 23:59:12.123456789') AS t;
----
1234 years 11 months 23 days 23:59:12

query T
SELECT date_trunc('minutes',  INTERVAL '1234 years 11 months 23 days 23:59:12.123456789') AS t;
----
1234 years 11 months 23 days 23:59:00

query T
SELECT date_trunc('hours',  INTERVAL '1234 years 11 months 23 days 23:59:12.123456789') AS t;
----
1234 years 11 months 23 days 23:00:00

query T
SELECT date_trunc('days',  INTERVAL '1234 years 11 months 23 days 23:59:12.123456789') AS t;
----
1234 years 11 months 23 days

query error unit 'weeks' not recognized
SELECT date_trunc('weeks',  INTERVAL '1234 years 11 months 23 days 23:59:12.123456789') AS t;

query T
SELECT date_trunc('months',  INTERVAL '1234 years 11 months 23 days 23:59:12.123456789') AS t;
----
1234 years 11 months

query T
SELECT date_trunc('years',  INTERVAL '1234 years 11 months 23 days 23:59:12.123456789') AS t;
----
1234 years

query T
SELECT date_trunc('decade',  INTERVAL '1234 years 11 months 23 days 23:59:12.123456789') AS t;
----
1230 years

query T
SELECT date_trunc('millennium',  INTERVAL '1234 years 11 months 23 days 23:59:12.123456789') AS t;
----
1000 years

query T
SELECT date_trunc('second', '2562047788:00:54.775807'::INTERVAL) AS t;
----
2562047788:00:54

query T
SELECT date_trunc('second', '-2562047788:00:54.775807'::INTERVAL) AS t;
----
-2562047788:00:54

mode standard

statement ok
CREATE TABLE date_trunc_timestamps (
    ts timestamp
)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT date_trunc('day', ts) FROM date_trunc_timestamps
----
Explained Query:
  Project (#1) // { arity: 1 }
    Map (date_trunc_day_ts(#0{ts})) // { arity: 2 }
      ReadStorage materialize.public.date_trunc_timestamps // { arity: 1 }

Source materialize.public.date_trunc_timestamps

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT date_trunc(field, ts) FROM date_trunc_fields, date_trunc_timestamps
----
Explained Query:
  Project (#2) // { arity: 1 }
    Map (date_truncts(#0{field}, #1{ts})) // { arity: 3 }
      CrossJoin type=differential // { arity: 2 }
        implementation
          %0:date_trunc_fields[×] » %1:date_trunc_timestamps[×]
        ArrangeBy keys=[[]] // { arity: 1 }
          ReadStorage materialize.public.date_trunc_fields // { arity: 1 }
        ArrangeBy keys=[[]] // { arity: 1 }
          ReadStorage materialize.public.date_trunc_timestamps // { arity: 1 }

Source materialize.public.date_trunc_fields
Source materialize.public.date_trunc_timestamps

Target cluster: quickstart

EOF

mode cockroach

query R
SELECT floor(CAST (1.1 AS double precision))
----
1

query R
SELECT floor(CAST (1.1 AS float))
----
1

query RRRRR
SELECT floor(1.1), floor(1.111), floor(100.1), floor(100.11), floor(-4.1)
----
1  1  100  100  -5


query R
SELECT ceil(CAST (1.1 AS double precision))
----
2

query R
SELECT ceil(CAST (1.1 AS float))
----
2

query RRRRR
SELECT ceil(1.1), ceil(1.111), ceil(100.1), ceil(100.11), ceil(-4.1)
----
2  2  101  101  -4

query RRRRR
SELECT ceiling(1.1), ceiling(1.111), ceiling(100.1), ceiling(100.11), ceiling(-4.1)
----
2  2  101  101  -4

# postgres converts ints to floats on floor/ceil
query R
SELECT floor(1)
----
1

query R
SELECT floor(1)
----
1

query R
SELECT floor(cast(1 AS bigint))
----
1

query R
SELECT ceil(cast(1 AS bigint))
----
1

query R
SELECT floor(NULL)
----
NULL

query R
SELECT ceil(NULL)
----
NULL

statement ok
CREATE TABLE null_test (
   decimal_col decimal(14, 2),
   float_col float
)

statement ok
INSERT INTO null_test VALUES (5.06, -1.59), (-12.58, NULL), (2.96, 2.2), (NULL, 23.8), (-9.4, -48.2)

query RR rowsort
SELECT floor(decimal_col), floor(float_col) FROM null_test
----
-10  -49
-13  NULL
2  2
5  -2
NULL  23

query RR rowsort
SELECT ceil(decimal_col), ceil(float_col) FROM null_test
----
-12  NULL
-9  -48
3  3
6  -1
NULL  24

# Tests for the sqrt function.
#
# The implementation of sqrt delegates to {f32,f64}::sqrt, so these tests are
# not particularly extensive.

query error cannot take square root of a negative number
SELECT sqrt(-1::float)

query error cannot take square root of a negative number
SELECT sqrt(-1::double precision)

query error cannot take square root of a negative number
SELECT sqrt(-1::decimal(15, 2))

query R
SELECT sqrt(1.23783::float)
----
1.112578087147145

query R
SELECT sqrt(1.23783::double)
----
1.112578087147145

query R
SELECT sqrt(1.23783::decimal(15, 5))
----
1.11257808714714492169319004932905215867

# Test cbrt.
query R
SELECT cbrt(NULL)
----
NULL

query R
SELECT cbrt(1.23783::float)::float4
----
1.07371

query R
SELECT cbrt(1.23783::double)::float4
----
1.07371

query R
SELECT cbrt(1.23783::decimal(15,5))::float4
----
1.07371

query R
SELECT cbrt(-8::double)::float4
----
-2

query R
SELECT cbrt(3::int)::float4
----
1.4422495

query R
SELECT cbrt(27::int)::float4
----
3

# Test coalesce.
query I
SELECT coalesce(NULL, 1, NULL)
----
1

query R
SELECT coalesce(NULL, 1, NULL)
----
1

query T
SELECT coalesce('hello', 'world', NULL)
----
hello

query T
SELECT coalesce(row(5), row(10))
----
(5)

query T
SELECT coalesce(row(4, 3), row(2, 1))
----
(4,3)

query T
select coalesce(null::char(1), 'abc');
----
abc

query T
SELECT coalesce('abc', null::char(1));
----
abc

query T
SELECT coalesce(null::char(1),'abcde','abc');
----
abcde

query T
SELECT coalesce('abcde',null::char(1),'abc');
----
abcde

# TODO(database-issues#3339)
query error coalesce could not convert type record
SELECT coalesce(row(1, 2), row(3), row(4, 5));

statement ok
CREATE TYPE custom_composite AS (i int);

query T
SELECT coalesce(row(1), row(1)::custom_composite)
----
(1)

statement ok
CREATE VIEW v AS SELECT 1 AS a

# Coalesce should reduce away errors that statically can be shown not to occur.
query T
SELECT coalesce(1, 1 / 0, a) FROM v
----
1

query T
select coalesce(null::char(1), 'abc');
----
abc

query T
SELECT coalesce('abc', null::char(1));
----
abc

query T
SELECT coalesce(null::char(1),'abcde','abc');
----
abcde

query T
SELECT coalesce('abcde',null::char(1),'abc');
----
abcde

# Test greatest.
query I
SELECT greatest(1)
----
1

query I
SELECT greatest(1, 2)
----
2

query I
SELECT greatest(3, 1)
----
3

query I
SELECT greatest(NULL)
----
NULL

query I
SELECT greatest(1, NULL, -1)
----
1

query T
SELECT greatest((3), (0), (-1));
----
3

query T
SELECT greatest(row(4, 3), row(4, 2), row(4, 4));
----
(4,4)

query T
SELECT greatest(row(2, 3), row(1, 4), row(5, 0));
----
(5,0)

query T
SELECT greatest(row(row(2, 4), 5), row(row(0, 10), 10), row(row(4, 3), 4));
----
("(4,3)",4)

# TODO(database-issues#3339)
query error greatest could not convert type record
SELECT greatest(row(1, 2), row(3), row(4, 5));

query T
SELECT greatest(row(1), row(1)::custom_composite)
----
(1)

query error greatest could not convert type record\(f1: integer,f2: integer\) to text
SELECT greatest(row(1, 2), 'hello');

query error greatest types integer and text cannot be matched
SELECT greatest(1::int, 2::text)

# Test least.
query I
SELECT least(1)
----
1

query I
SELECT least(1, 2)
----
1

query I
SELECT least(3, 1)
----
1

query I
SELECT least(NULL)
----
NULL

query I
SELECT least(1, NULL, -1)
----
-1

query T
SELECT least((3), (0), (-1));
----
-1

query T
SELECT least(row(4, 3), row(4, 2), row(4, 4));
----
(4,2)

query T
SELECT least(row(2, 3), row(1, 4), row(5, 0));
----
(1,4)

query T
SELECT least(row(row(2, 4), 5), row(row(0, 10), 10), row(row(4, 3), 4));
----
("(0,10)",10)

# TODO(database-issues#3339)
query error least could not convert type record
SELECT least(row(1, 2), row(3), row(4, 5));

query error least could not convert type record\(f1: integer,f2: integer\) to text
SELECT least(row(1, 2), 'hello');

query T
SELECT least(row(1), row(1)::custom_composite)
----
(1)

query error least types integer and text cannot be matched
SELECT least(1::int, 2::text)

# Tests issue database-issues#827, that type information for Maps are correctly constructed
# before being passed to expressions for optimization.
statement ok
CREATE VIEW bytes AS SELECT null::bytea AS data

query T
SELECT
    COALESCE(data::jsonb->>'field1', data::jsonb->>'field2')
FROM (
    SELECT CONVERT_FROM(data, 'utf8') AS data FROM bytes
)
----
NULL

query T
SELECT trim(LEADING 'xy' FROM 'yxytrimyxy');
----
trimyxy

query T
SELECT ltrim('yxytrimyxy', 'xy');
----
trimyxy

query T
SELECT rtrim('yxytrimyxy', 'xy');
----
yxytrim

query T
SELECT btrim('yxytrimyxy', 'xy');
----
trim

query T
SELECT btrim('  yxytrimyxy  ');
----
yxytrimyxy

query T
SELECT rtrim('yxytrimyxy  ');
----
yxytrimyxy

query T
SELECT ltrim('   yxytrimyxy');
----
yxytrimyxy

query T
SELECT trim('xy' FROM 'yxytrimyxy');
----
trim

query T
SELECT trim(BOTH 'xy' FROM 'yxytrimyxy');
----
trim

query T
SELECT trim(TRAILING FROM 'yxytrimyxy  ');
----
yxytrimyxy

query T
SELECT trim(FROM '  yxytrimyxy  ');
----
yxytrimyxy

query T
SELECT trim(BOTH FROM '  yxytrimyxy  ');
----
yxytrimyxy

query T
SELECT trim('   yxytrimyxy  ');
----
yxytrimyxy

query T
SELECT trim(BOTH '   yxytrimyxy  ');
----
yxytrimyxy

query T
SELECT trim(LEADING '   yxytrimyxy');
----
yxytrimyxy

statement error
SELECT trim('c' 'ccccdogcc');

# Test IS NULL reduction.

mode standard

statement ok
CREATE TABLE t (
    a int,
    b int NOT NULL
)

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

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a + a + a + a + a IS NULL FROM t
----
Explained Query:
  Project (#2) // { arity: 1 }
    Map ((#0{a}) IS NULL) // { arity: 3 }
      ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT a + b IS NULL FROM t
----
Explained Query:
  Project (#2) // { arity: 1 }
    Map ((#0{a}) IS NULL) // { arity: 3 }
      ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

# Ensure that (a AND b) IS NULL is *not* reduced, as it is not as simple as
# rewriting (A IS NULL) OR (b IS NULL). There are probably rewrite rules that
# exist, but we do not support them yet. Similarly for OR.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT (a::bool AND b::bool) IS NULL FROM t
----
Explained Query:
  Project (#2) // { arity: 1 }
    Map (((integer_to_boolean(#0{a}) AND integer_to_boolean(#1{b}))) IS NULL) // { arity: 3 }
      ReadStorage materialize.public.t // { arity: 2 }

Source materialize.public.t

Target cluster: quickstart

EOF

mode cockroach

# Test qualified function names.

query I
SELECT abs(-1)
----
1

query I
SELECT pg_catalog.abs(1)
----
1

query I
SELECT materialize.pg_catalog.abs(1)
----
1

query error db error: ERROR: function "mz_catalog\.abs" does not exist
SELECT mz_catalog.abs(1)

query error unknown database 'noexist'
SELECT noexist.pg_catalog.abs(1)

# mod is a special case for qualified function names, since it is transformed
# away by an early pass in the planner.

query I
SELECT mod(7, 4)
----
3

query I
SELECT pg_catalog.mod(7, 4)
----
3

query error db error: ERROR: function "mz_catalog\.mod" does not exist
SELECT mz_catalog.mod(7, 4)

query error unknown database 'noexist'
SELECT noexist.pg_catalog.mod(7, 4)

query T
SELECT pg_catalog.pg_encoding_to_char(6)
----
UTF8

query T
SELECT pg_catalog.pg_encoding_to_char(7)
----
NULL

# The following tests are taken from cockroach/builtin_function.slt
# todo@jldlaughlin: remove these duplicates

query I
SELECT array_lower(ARRAY['a', 'b'], 1)
----
1

query I
SELECT array_lower(ARRAY['a'], 1)
----
1

query I
SELECT array_lower(ARRAY['a'], 0)
----
NULL

query I
SELECT array_lower(ARRAY['a'], 2)
----
NULL

query I
SELECT array_lower(ARRAY[ARRAY[1, 2]], 2)
----
1

query I
SELECT array_upper(ARRAY['a', 'b'], 1)
----
2

query I
SELECT array_upper(ARRAY['a'], 1)
----
1

query I
SELECT array_upper(ARRAY['a'], 0)
----
NULL

query I
SELECT array_upper(ARRAY['a'], 2)
----
NULL

query I
SELECT array_upper(ARRAY[ARRAY[1, 2]], 2)
----
2

# Additional array_lower, array_upper tests
query I
SELECT array_lower(ARRAY[[[9]]], 2)
----
1

query I
SELECT array_lower(ARRAY[[['a', 'b']]], 3)
----
1

query I
SELECT array_lower(ARRAY[[['a', 'b']]], 4)
----
NULL

query I
SELECT array_upper(ARRAY[[[1, 2]]], 3)
----
2

query I
SELECT array_upper(ARRAY[[[1, 2]]], 4)
----
NULL

query I
SELECT array_upper(ARRAY[NULL], 1)
----
1

query I
SELECT array_upper(ARRAY[1], NULL)
----
NULL

query I
SELECT array_upper(NULL::text[], 1)
----
NULL

# TODO(fix)
query error db error: ERROR: could not determine polymorphic type because input has type unknown
SELECT array_upper(NULL, 1)

query I
SELECT array_length(ARRAY['a', 'b'], 1)
----
2

query I
SELECT array_length(ARRAY['a'], 1)
----
1

query I
SELECT array_length(ARRAY['a'], 0)
----
NULL

query I
SELECT array_length(ARRAY['a'], 2)
----
NULL

query I
SELECT array_length(ARRAY[ARRAY[1, 2]], 2)
----
2

query I
SELECT array_length(ARRAY[]::int[], 1)
----
NULL

query error could not determine polymorphic type because input has type unknown
SELECT array_length(NULL, 1);

# Test strange collapsing behavior of nested empty arrays. See database-issues#1709.

query TIII
SELECT arr, array_lower(arr, 1), array_upper(arr, 1), array_length(arr, 1) FROM
    (VALUES
        (ARRAY[]::int[]),
        (ARRAY[ARRAY[]]::int[]),
        (ARRAY[ARRAY[], ARRAY[], ARRAY[]]::int[]),
        (ARRAY[ARRAY[ARRAY[ARRAY[]]]]::int[])
    ) AS _ (arr)
----
{}  NULL  NULL  NULL
{}  NULL  NULL  NULL
{}  NULL  NULL  NULL
{}  NULL  NULL  NULL

query error could not determine polymorphic type because input has type unknown
SELECT array_upper(NULL, 1)

query error could not determine polymorphic type because input has type unknown
SELECT array_lower(NULL, 1)

query T
SELECT upper('a1Bd')
----
A1BD

query T
SELECT upper('ALREADYUP')
----
ALREADYUP

query error db error: ERROR: function upper\(numeric\) does not exist
SELECT upper(2.2)

query T
SELECT lower('a1Bd')
----
a1bd

query T
SELECT lower('alreadylow')
----
alreadylow

query error db error: ERROR: function lower\(interval\) does not exist
SELECT lower('1ms'::interval)

# Test trigonometric functions.

# Use standard mode to round floats to three digits of precision. This makes
# tests more reliable across platforms, as platforms have different
# implementations of the trigonometric functions that result in slight
# variance in the least significant digits.
mode standard

query R
SELECT sin(NULL)
----
NULL

query R
SELECT sinh(NULL)
----
NULL

query R
SELECT cos(NULL)
----
NULL

query R
SELECT cosh(NULL)
----
NULL

query R
SELECT tan(NULL)
----
NULL

query R
SELECT tanh(NULL)
----
NULL

query R
SELECT asin(NULL)
----
NULL

query R
SELECT asinh(NULL)
----
NULL

query R
SELECT acos(NULL)
----
NULL

query R
SELECT acosh(NULL)
----
NULL

query R
SELECT atan(NULL)
----
NULL

query R
SELECT atanh(NULL)
----
NULL

query R
SELECT cot(NULL)
----
NULL

query R
SELECT sin('NaN'::double)
----
NaN

query R
SELECT sinh('NaN'::double)
----
NaN

query R
SELECT cos('NaN'::double)
----
NaN

query R
SELECT cosh('NaN'::double)
----
NaN

query R
SELECT tan('NaN'::double)
----
NaN

query R
SELECT tanh('NaN'::double)
----
NaN

query R
SELECT asin('NaN'::double)
----
NaN

query R
SELECT asinh('NaN'::double)
----
NaN

query R
SELECT acos('NaN'::double)
----
NaN

query R
SELECT acosh('NaN'::double)
----
NaN

query R
SELECT atan('NaN'::double)
----
NaN

query R
SELECT atanh('NaN'::double)
----
NaN

query R
SELECT cot('NaN'::double)
----
NaN

query R
SELECT sin(0::double)
----
0.000

query R
SELECT sinh(0::double)
----
0.000

query R
SELECT cos(0::double)
----
1.000

query R
SELECT cosh(0::double)
----
1.000

query R
SELECT tan(0::double)
----
0.000

query R
SELECT tanh(0::double)
----
0.000

query R
SELECT asin(0::double)
----
0.000

query R
SELECT asinh(0::double)
----
0.000

query R
SELECT acos(0::double)
----
1.571

query error function acosh is defined for numbers greater than or equal to 1
SELECT acosh(0::double)

query R
SELECT atan(0::double)
----
0.000

query R
SELECT atanh(0::double)
----
0.000

query R
SELECT cot(0::double)
----
inf

query R
SELECT cot(-0::double)
----
-inf

query R
SELECT sin(1::double)
----
0.841

query R
SELECT sinh(1::double)
----
1.175

query R
SELECT cos(1::double)
----
0.540

query R
SELECT cosh(1::double)
----
1.543

query R
SELECT tan(1.01::double)
----
1.592

query R
SELECT tanh(1::double)
----
0.762

query R
SELECT asin(1::double)
----
1.571

query R
SELECT asinh(1::double)
----
0.881

query R
SELECT acos(1::double)
----
0.000

query R
SELECT acosh(1::double)
----
0.000

query R
SELECT atan(1::double)
----
0.785

query R
SELECT atanh(1::double)
----
inf

query R
SELECT cot(1.01::double)
----
0.628

query error function sin is only defined for finite arguments
SELECT sin('inf'::double)

query error function asin is defined for numbers between -1 and 1 inclusive
SELECT asin('inf'::double)

query R
SELECT sinh('inf'::double)
----
inf

query R
SELECT asinh('inf'::double)
----
inf

query error function cos is only defined for finite arguments
SELECT cos('inf'::double)

query error function acos is defined for numbers between -1 and 1 inclusive
SELECT acos('inf'::double)

query R
SELECT cosh('inf'::double)
----
inf

query R
SELECT acosh('inf'::double)
----
inf

query error function tan is only defined for finite arguments
SELECT tan('inf'::double)

query R
SELECT atan('inf'::double)
----
1.571

query R
SELECT tanh('inf'::double)
----
1.000

query error function atanh is defined for numbers between -1 and 1 inclusive
SELECT atanh('inf'::double)

query error function cot is only defined for finite arguments
SELECT cot('inf'::double)

query error function sin is only defined for finite arguments
SELECT sin('-inf'::double)

query error function asin is defined for numbers between -1 and 1 inclusive
SELECT asin('-inf'::double)

query R
SELECT sinh('-inf'::double)
----
-inf

query R
SELECT asinh('-inf'::double)
----
-inf

query error function cos is only defined for finite arguments
SELECT cos('-inf'::double)

query error function acos is defined for numbers between -1 and 1 inclusive
SELECT acos('-inf'::double)

query R
SELECT cosh('-inf'::double)
----
inf

query error function acosh is defined for numbers greater than or equal to 1
SELECT acosh('-inf'::double)

query error function tan is only defined for finite arguments
SELECT tan('-inf'::double)

query R
SELECT atan('-inf'::double)
----
-1.571

query R
SELECT tanh('-inf'::double)
----
-1.000

query error atanh is defined for numbers between -1 and 1 inclusive
SELECT atanh('-inf'::double)

# Use the more reasonable number representation, as the standard mode
# causes all sorts of weird representation issues with exp and log:
mode cockroach

query error function cot is only defined for finite arguments
SELECT cot('-inf'::double)

query error function log10 is not defined for zero
SELECT log10(0.0::double)

query error function log10 is not defined for zero
SELECT log10(+0.0::double)

query error function log10 is not defined for negative numbers
SELECT log10(-1.0::double)

query R
SELECT log10(10.0::double)
----
1

query R
SELECT log(10.0::decimal(15, 5))
----
1

query error function log is not defined for zero
SELECT log(0.0, 10)

query error function log is not defined for zero
SELECT log(+0.0, 10)

query error function log is not defined for negative numbers
SELECT log(-10, 10)

query error function log is not defined for zero
SELECT log(10, 0.0)

query error function log is not defined for zero
SELECT log(10, +0.0)

query error function log is not defined for negative numbers
SELECT log(10.0, -10)

query R
SELECT log(10, 10)
----
1

query R
SELECT log(400, 20)
----
0.500000000000000000000000000000000000001

query R
SELECT log10(10::decimal(15, 5))
----
1

query R
SELECT round(ln(13::float)::decimal(15, 5), 3)
----
2.565

query error function ln is not defined for negative numbers
SELECT ln(-1)

query error function ln is not defined for zero
SELECT ln(0)

query R
SELECT ln(13.0000::decimal(15, 5))
----
2.56494935746153673605348744156531860481

query R
SELECT round(exp(2)::decimal(15, 5), 3)
----
7.389

query R
SELECT exp(ln(2))
----
2

query T
SELECT exp(ln(2::decimal(15, 5)))
----
2

query error value out of range: overflow
SELECT exp(10000::float)

query error value out of range: underflow
SELECT exp(-10000::float)

query R
SELECT power(382, 5);
----
8134236862432

query T
SELECT power(9::float, 0.5);
----
3.000

query T
SELECT power(9::decimal(15, 5), 0.5::decimal(15, 5));
----
3

query error zero raised to a negative power is undefined
SELECT power(0::float, -1);

query error function pow cannot return complex numbers
SELECT power(-2.0::float, 1.5)

query R
SELECT pow(382, 5);
----
8134236862432

query T
SELECT pow(9::float, 0.5);
----
3.000

query T
SELECT pow(9::decimal(15, 5), 0.5::decimal(15, 5));
----
3

query error value out of range: overflow
SELECT pow(3::float, 10000)

query T
SELECT pow(0::float, 10000)
----
0.000

query error value out of range: underflow
SELECT pow(3::float, -10000)

query T
SELECT pg_column_size(NULL)
----
NULL

query IIII
SELECT pg_column_size(1::int4), pg_column_size(2::int8), pg_column_size('a'), pg_column_size('ab')
----
2 2 3 4

query I
SELECT pg_column_size((1, 2))
----
13

statement ok
CREATE TABLE col_size (a INT, b INT, c TEXT)

statement ok
INSERT INTO col_size VALUES (1, 2, 'some string longer than 32 chars.'), (NULL, NULL, NULL)

query IIIIII
SELECT a, b, pg_column_size(col_size.*), pg_column_size(col_size.a), pg_column_size(col_size.b), pg_column_size(col_size.c) FROM col_size ORDER BY a
----
1  2  48  2  2  35
NULL  NULL  12  NULL  NULL  NULL

statement error mz_row_size requires a record type
SELECT mz_row_size(NULL)
----
NULL

statement error mz_row_size requires a record type
SELECT mz_row_size(1)
----
NULL

query III
SELECT mz_row_size((1, 2)), mz_row_size((1, 2, 3, 4)), mz_row_size((1, 2, 3, 4, 5))
----
24  24  24

statement ok
CREATE TABLE ts_size (t TEXT)

statement ok
INSERT INTO ts_size VALUES ('2023-10-30T13:47:11Z')

query I
SELECT mz_row_size(ts_size.*) FROM ts_size
----
24

query III
SELECT a, b, mz_row_size(col_size.*) FROM col_size ORDER BY a
----
1  2  63
NULL  NULL  24

query error mz_errored
SELECT mz_unsafe.mz_error_if_null(NULL, 'mz_errored')

query I
SELECT mz_unsafe.mz_error_if_null(1, '')
----
1

# Confirm that `mz_error_if_null` can be seen to be non-null.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT mz_unsafe.mz_error_if_null(t, '') IS NULL FROM ts_size;
----
Explained Query:
  Project (#1)
    Map (false)
      ReadStorage materialize.public.ts_size

Source materialize.public.ts_size

Target cluster: quickstart

EOF

query error unexpected NULL
SELECT mz_unsafe.mz_error_if_null(NULL, NULL)

query B
SELECT pg_backend_pid() > 0
----
true

query B
SELECT pg_is_in_recovery()
----
false

query B
SELECT pg_tablespace_location(0) IS NULL
----
true

query B
SELECT pg_get_ruledef(0) IS NULL
----
true

query I
SELECT pg_relation_size('pg_views'::regclass)
----
-1

query I
SELECT pg_relation_size('pg_views'::regclass::oid)
----
-1

query I
SELECT pg_relation_size('pg_views'::regclass, 'main')
----
-1

query I
SELECT pg_relation_size('pg_views'::regclass::oid, 'main')
----
-1

query I
SELECT pg_stat_get_numscans('pg_views'::regclass::oid)
----
-1

# mz_unsafe functions can't be executed with the enable_unsafe_functions flag turned off

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_unsafe_functions = false
----
COMPLETE 0

statement error executing potentially dangerous functions is not supported
SELECT mz_unsafe.mz_sleep(10)

statement error executing potentially dangerous functions is not supported
SELECT mz_unsafe.mz_panic('hello')

statement ok
SELECT mz_internal.is_rbac_enabled()

statement ok
CREATE TABLE dangerous_table (a INT, b TEXT)

statement ok
INSERT INTO dangerous_table (a) VALUES (1)

statement error executing potentially dangerous functions is not supported
SELECT mz_unsafe.mz_any(a) FROM dangerous_table

statement error executing potentially dangerous functions is not supported
INSERT INTO dangerous_table (b) VALUES (mz_unsafe.mz_panic('hello'))

statement ok
DROP TABLE dangerous_table

statement ok
SELECT * FROM mz_internal.mz_resolve_object_name('regclass', 't');

statement ok
SELECT mz_internal.mz_resolve_object_name('regclass', 't');

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_unsafe_functions = true
----
COMPLETE 0

query error function textrecv\(\) does not exist
SELECT textrecv()

query error cannot call function with arguments of type internal
SELECT textrecv('abc')