Materialize/test/sqllogictest/type-promotion.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 t (a float, b int)

statement ok
INSERT INTO t VALUES (4.7, 2)

# Test that all arithmetic and comparison operators coalesce their arguments.
# The goal is not to test every possible combination of arguments, but just a
# basic sanity check. An old version of the code forgot to include modulus in
# list of operators that should coalesce their inputs.

query RRRRRBBBBBB
SELECT
    a + b,
    a - b,
    a * b,
    a / b,
    a % b,
    a < b,
    a <= b,
    a > b,
    a >= b,
    a = b,
    a <> b
FROM t
----
6.7  2.7  9.4  2.35  0.7000000000000002  false  false  true  true  false  true

# Do not allow int4 text comparisons
query error db error: ERROR: operator does not exist: text < integer
SELECT 'foo'::text < 5::int;

query error db error: ERROR: operator does not exist: integer < text
SELECT 1 < ALL(VALUES(NULL))

# But string *literals* can coerce to anything.
query T
SELECT '1' < 2
----
true

query T
SELECT 'true' OR 'false'
----
true

# Int literals <=32::MAX/>= MIN are int4
query T
SELECT pg_typeof(100)
----
integer

query T
SELECT pg_typeof(-100)
----
integer

query T
SELECT pg_typeof(2147483647)
----
integer

query T
SELECT pg_typeof(-2147483648)
----
integer

# Int literals that exceed i32 are int8
query T
SELECT pg_typeof(2147483648)
----
bigint

query T
SELECT pg_typeof(-2147483649)
----
bigint

query T
SELECT pg_typeof(9223372036854775807)
----
bigint

query T
SELECT pg_typeof(-9223372036854775808)
----
bigint

# Int literals that exceed i64 are numeric
query T
SELECT pg_typeof(9223372036854775808)
----
numeric

query T
SELECT pg_typeof(-9223372036854775809)
----
numeric

# Use comparison ops to check for type promotion

# int2 promotes to int4
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT 1::smallint > 1;
----
Map ((smallint_to_integer(integer_to_smallint(1)) > 1))
  Constant
    - ()

Target cluster: quickstart

EOF

# Check int2 promotes to numeric
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT 1::smallint > 1.1;
----
Map ((smallint_to_numeric(integer_to_smallint(1)) > 1.1))
  Constant
    - ()

Target cluster: quickstart

EOF

# Check int8 promotes to numeric
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT 1::bigint > 1.11111
----
Map ((bigint_to_numeric(integer_to_bigint(1)) > 1.11111))
  Constant
    - ()

Target cluster: quickstart

EOF

# Check int8 promotes to double precision
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT 1::bigint > 1.11111::float
----
Map ((bigint_to_double(integer_to_bigint(1)) > numeric_to_double(1.11111)))
  Constant
    - ()

Target cluster: quickstart

EOF

# Check numeric promotes to double precision
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT 1.1 > 1::float;
----
Map ((numeric_to_double(1.1) > integer_to_double(1)))
  Constant
    - ()

Target cluster: quickstart

EOF

# Check numerics do not get promoted
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT 1.1 > 1.1
----
Map ((1.1 > 1.1))
  Constant
    - ()

Target cluster: quickstart

EOF

# Check floats do not get promoted
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT 1::float > 1::float
----
Map ((integer_to_double(1) > integer_to_double(1)))
  Constant
    - ()

Target cluster: quickstart

EOF

# Checks that float8 is preferred type for int4
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT floor(1);
----
Map (floorf64(integer_to_double(1)))
  Constant
    - ()

Target cluster: quickstart

EOF

# Cannot implicitly cast int4 to string
query error db error: ERROR: function char_length\(integer\) does not exist
SELECT char_length(321);

# Cannot implicitly cast double precision to numeric
query error db error: ERROR: function round\(double precision, integer\) does not exist
SELECT round(1.23::float, 1);

# Check that float8 is the most common type
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT coalesce(1::int, 1::numeric, 1::float);
----
Map (coalesce(integer_to_double(1), numeric_to_double(integer_to_numeric(1)), integer_to_double(1)))
  Constant
    - ()

Target cluster: quickstart

EOF

# Test bool->string explicit cast
query T
SELECT true::string;
----
true

# Test bool->string implicit cast
query T
SELECT concat(true);
----
t

# Check that date promotes to tstz
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT TIMESTAMPTZ '2001 03-04' - DATE '2002 03-04';
----
Map ((text_to_timestamp_with_time_zone("2001 03-04") - date_to_timestamp_with_timezone(text_to_date("2002 03-04"))))
  Constant
    - ()

Target cluster: quickstart

EOF

# Check that ts promotes to tstz
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT TIMESTAMPTZ '2001 03-04' - TIMESTAMP '2002 03-04';
----
Map ((text_to_timestamp_with_time_zone("2001 03-04") - timestamp_to_timestamp_with_time_zone(text_to_timestamp("2002 03-04"))))
  Constant
    - ()

Target cluster: quickstart

EOF

# Check that date promotes to ts
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT TIMESTAMP '2001 03-04' - DATE '2002 03-04';
----
Map ((text_to_timestamp("2001 03-04") - date_to_timestamp(text_to_date("2002 03-04"))))
  Constant
    - ()

Target cluster: quickstart

EOF

# Check that tstz is most-common type
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT coalesce(DATE '2001', TIMESTAMP '2002', TIMESTAMPTZ '2003');
----
Map (coalesce(date_to_timestamp_with_timezone(text_to_date("2001")), timestamp_to_timestamp_with_time_zone(text_to_timestamp("2002")), text_to_timestamp_with_time_zone("2003")))
  Constant
    - ()

Target cluster: quickstart

EOF

# Check that char casts to text for string functions
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT lower('a'::char);
----
Map (lower(char_to_text(text_to_char[len=1, fail_on_len=false](text_to_char[len=unbounded]("a")))))
  Constant
    - ()

Target cluster: quickstart

EOF

# Check that "char" vs varchar promotes to text
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT 'a'::"char" < 'a'::varchar;
----
Map (("char"_to_text(text_to_"char"("a")) < varchar_to_text(text_to_varchar[len=unbounded]("a"))))
  Constant
    - ()

Target cluster: quickstart

EOF

# Check that "char" vs char promotes to text
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT 'a'::"char" < 'a'::char;
----
Map (("char"_to_text(text_to_"char"("a")) < char_to_text(text_to_char[len=1, fail_on_len=false](text_to_char[len=unbounded]("a")))))
  Constant
    - ()

Target cluster: quickstart

EOF

# Check that varchar promotes to char
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT 'a'::char < 'a'::varchar;
----
Map ((text_to_char[len=1, fail_on_len=false](text_to_char[len=unbounded]("a")) < text_to_char[len=unbounded](text_to_varchar[len=unbounded]("a"))))
  Constant
    - ()

Target cluster: quickstart

EOF


# Check that char promotes to text
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT 'a'::char < 'a'::text;
----
Map ((char_to_text(text_to_char[len=1, fail_on_len=false](text_to_char[len=unbounded]("a"))) < "a"))
  Constant
    - ()

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN FOR
    SELECT 'a'::varchar < 'a'::text;
----
Map ((varchar_to_text(text_to_varchar[len=unbounded]("a")) < "a"))
  Constant
    - ()

Target cluster: quickstart

EOF

# Check that text is most-common type
query T multiline
EXPLAIN RAW PLAN FOR
    SELECT coalesce('a'::char, 'a'::varchar, 'a'::text);
----
Map (coalesce(text_to_char[len=1, fail_on_len=false](text_to_char[len=unbounded]("a")), text_to_char[len=unbounded](text_to_varchar[len=unbounded]("a")), text_to_char[len=unbounded]("a")))
  Constant
    - ()

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN FOR
    SELECT coalesce('a'::char, 'a'::text, 'a'::varchar);
----
Map (coalesce(text_to_char[len=1, fail_on_len=false](text_to_char[len=unbounded]("a")), text_to_char[len=unbounded]("a"), text_to_char[len=unbounded](text_to_varchar[len=unbounded]("a"))))
  Constant
    - ()

Target cluster: quickstart

EOF

query T
SELECT 1::smallint::smallint;
----
1

query T
SELECT 1::smallint::bigint;
----
1

query error CAST does not support casting from boolean to smallint
SELECT TRUE::boolean::smallint

query error CAST does not support casting from date to smallint
SELECT '2001 02-03'::date::smallint

query T
SELECT 1.1::numeric::smallint;
----
1

query T
SELECT 1.2::double::smallint;
----
1

query T
SELECT 1.3::real::smallint;
----
1

query T
SELECT 2::int::smallint;
----
2

query error CAST does not support casting from interval to smallint
SELECT '1'::interval::smallint

query error cannot cast jsonb object to type smallint
SELECT '{}'::jsonb::smallint;

query R
SELECT '1'::jsonb::smallint;
----
1

query R
SELECT '1'::text::smallint
----
1

query R
SELECT '1'::char::smallint
----
1

query R
SELECT '1'::varchar::smallint
----
1

query error CAST does not support casting from time to smallint
SELECT '01:02:03'::time::smallint

query error CAST does not support casting from timestamp without time zone to smallint
SELECT '2002 03-04'::timestamp::smallint

query error CAST does not support casting from timestamp with time zone to smallint
SELECT '2003 04-05'::timestamptz::smallint

query error AST does not support casting from smallint to bool
SELECT 1::smallint::boolean;

query T
SELECT 1::bigint::smallint;
----
1

query T
SELECT 1::bigint::bigint;
----
1

query T
SELECT TRUE::boolean::bigint
----
1

query error CAST does not support casting from date to bigint
SELECT '2001 02-03'::date::bigint

query T
SELECT 1.1::numeric::bigint;
----
1

query T
SELECT 1.2::double::bigint;
----
1

query T
SELECT 1.3::real::bigint;
----
1

query T
SELECT 2::int::bigint;
----
2

query error CAST does not support casting from interval to bigint
SELECT '1'::interval::bigint

query error cannot cast jsonb object to type bigint
SELECT '{}'::jsonb::bigint;

query R
SELECT '1'::jsonb::bigint;
----
1

query R
SELECT '1'::text::bigint
----
1

query R
SELECT '1'::char::bigint
----
1

query R
SELECT '1'::varchar::bigint
----
1

query error CAST does not support casting from time to bigint
SELECT '01:02:03'::time::bigint

query error CAST does not support casting from timestamp without time zone to bigint
SELECT '2002 03-04'::timestamp::bigint

query error CAST does not support casting from timestamp with time zone to bigint
SELECT '2003 04-05'::timestamptz::bigint

query T
SELECT 1::bigint::boolean;
----
true

query T
SELECT TRUE::boolean::boolean;
----
true

query error CAST does not support casting from date to boolean
SELECT '2001 02-03'::date::boolean

query error CAST does not support casting from numeric to boolean
SELECT 1.1::numeric::boolean

query error CAST does not support casting from double precision to boolean
SELECT 1.2::double::boolean

query error CAST does not support casting from real to boolean
SELECT 1.3::real::boolean

query T
SELECT 2::int::boolean;
----
true

query error CAST does not support casting from interval to boolean
SELECT '1'::interval::boolean

query error cannot cast jsonb object to type boolean
SELECT '{}'::jsonb::boolean;

query error cannot cast jsonb number to type boolean
SELECT '1'::jsonb::boolean;

query T
SELECT 'true'::text::boolean
----
true

query T
SELECT 'true'::char(4)::boolean
----
true

query T
SELECT 'true'::varchar::boolean
----
true

query error CAST does not support casting from time to boolean
SELECT '01:02:03'::time::boolean

query error CAST does not support casting from timestamp without time zone to boolean
SELECT '2002 03-04'::timestamp::boolean

query error CAST does not support casting from timestamp with time zone to boolean
SELECT '2003 04-05'::timestamptz::boolean

query error CAST does not support casting from smallint to date
SELECT 1::smallint::date

query error CAST does not support casting from bigint to date
SELECT 1::bigint::date

query error CAST does not support casting from boolean to date
SELECT TRUE::boolean::date

query T
SELECT '2001 02-03'::date::date;
----
2001-02-03

query error CAST does not support casting from numeric to date
SELECT 1.1::numeric::date

query error CAST does not support casting from double precision to date
SELECT 1.2::double::date

query error CAST does not support casting from real to date
SELECT 1.3::real::date

query error CAST does not support casting from integer to date
SELECT 2::int::date

query error CAST does not support casting from interval to date
SELECT '1'::interval::date

query error CAST does not support casting from jsonb to date
SELECT '{}'::jsonb::date

query T
SELECT '2001 02-03'::text::date
----
2001-02-03

query T
SELECT '2001 02-03'::char(11)::date
----
2001-02-03

query T
SELECT '2001 02-03'::varchar::date
----
2001-02-03

query error CAST does not support casting from time to date
SELECT '01:02:03'::time::date

query T
SELECT '2002 03-04'::timestamp::date;
----
2002-03-04

query T
SELECT '2003 04-05'::timestamptz::date;
----
2003-04-05

query R
SELECT 1::smallint::numeric;
----
1

query R
SELECT 1::bigint::numeric;
----
1

query error CAST does not support casting from boolean to numeric
SELECT TRUE::boolean::numeric

query error CAST does not support casting from date to numeric
SELECT '2001 02-03'::date::numeric

query R
SELECT 1.1::numeric::numeric;
----
1.1

query R
SELECT 1.2::double::numeric;
----
1.2

query R
SELECT 1.3::real::numeric;
----
1.3

query R
SELECT 2::int::numeric;
----
2

query error CAST does not support casting from interval to numeric
SELECT '1'::interval::numeric

query error cannot cast jsonb object to type numeric
SELECT '{}'::jsonb::numeric;

query R
SELECT '1'::jsonb::numeric;
----
1

query R
SELECT '1'::text::numeric
----
1

query R
SELECT '1'::char::numeric
----
1

query R
SELECT '1'::varchar::numeric
----
1

query error CAST does not support casting from time to numeric
SELECT '01:02:03'::time::numeric

query error CAST does not support casting from timestamp without time zone to numeric
SELECT '2002 03-04'::timestamp::numeric

query error CAST does not support casting from timestamp with time zone to numeric
SELECT '2003 04-05'::timestamptz::numeric

query T
SELECT 1::smallint::double;
----
1.000

query T
SELECT 1::bigint::double;
----
1.000

query error CAST does not support casting from boolean to double precision
SELECT TRUE::boolean::double

query error CAST does not support casting from date to double precision
SELECT '2001 02-03'::date::double

query T
SELECT 1.1::numeric::double;
----
1.100

query T
SELECT 1.2::double::double;
----
1.200

query T
SELECT 1.3::real::double;
----
1.300

query T
SELECT 2::int::double;
----
2.000

query error CAST does not support casting from interval to double precision
SELECT '1'::interval::double

query error cannot cast jsonb object to type double precision
SELECT '{}'::jsonb::double;

query T
SELECT '1'::jsonb::double;
----
1.000

query R
SELECT '1'::text::double
----
1

query R
SELECT '1'::char::double
----
1

query R
SELECT '1'::varchar::double
----
1

query error CAST does not support casting from time to double precision
SELECT '01:02:03'::time::double

query error CAST does not support casting from timestamp without time zone to double precision
SELECT '2002 03-04'::timestamp::double

query error CAST does not support casting from timestamp with time zone to double precision
SELECT '2003 04-05'::timestamptz::double

query T
SELECT 1::smallint::real;
----
1.000

query T
SELECT 1::bigint::real;
----
1.000

query error CAST does not support casting from boolean to real
SELECT TRUE::boolean::real

query error CAST does not support casting from date to real
SELECT '2001 02-03'::date::real

query T
SELECT 1.1::numeric::real;
----
1.100

query T
SELECT 1.2::double::real;
----
1.200

query T
SELECT 1.3::real::real;
----
1.300

query T
SELECT 2::int::real;
----
2.000

query error CAST does not support casting from interval to real
SELECT '1'::interval::real

query error cannot cast jsonb object to type real
SELECT '{}'::jsonb::real;

query R
SELECT '2'::jsonb::real;
----
2

query R
SELECT '1'::text::real
----
1

query R
SELECT '1'::char::real
----
1

query R
SELECT '1'::varchar::real
----
1

query error CAST does not support casting from time to real
SELECT '01:02:03'::time::real

query error CAST does not support casting from timestamp without time zone to real
SELECT '2002 03-04'::timestamp::real

query error CAST does not support casting from timestamp with time zone to real
SELECT '2003 04-05'::timestamptz::real

query T
SELECT 1::smallint::integer;
----
1

query T
SELECT 1::bigint::integer;
----
1

query error CAST does not support casting from date to integer
SELECT '2001 02-03'::date::integer

query T
SELECT 1.1::numeric::integer;
----
1

query T
SELECT 1.2::double::integer;
----
1

query T
SELECT 1.3::real::integer;
----
1

query T
SELECT 2::int::integer;
----
2

query error CAST does not support casting from interval to integer
SELECT '1'::interval::integer

query error cannot cast jsonb object to type integer
SELECT '{}'::jsonb::integer;

query R
SELECT '1'::jsonb::integer;
----
1

query R
SELECT '1'::text::integer
----
1

query R
SELECT '1'::char::integer
----
1

query R
SELECT '1'::varchar::integer
----
1

query error CAST does not support casting from time to integer
SELECT '01:02:03'::time::integer

query error CAST does not support casting from timestamp without time zone to integer
SELECT '2002 03-04'::timestamp::integer

query error CAST does not support casting from timestamp with time zone to integer
SELECT '2003 04-05'::timestamptz::integer

query error CAST does not support casting from smallint to interval
SELECT 1::smallint::interval

query error CAST does not support casting from bigint to interval
SELECT 1::bigint::interval

query error CAST does not support casting from boolean to interval
SELECT TRUE::boolean::interval

query error CAST does not support casting from date to interval
SELECT '2001 02-03'::date::interval

query error CAST does not support casting from numeric to interval
SELECT 1.1::numeric::interval

query error CAST does not support casting from double precision to interval
SELECT 1.2::double::interval

query error CAST does not support casting from real to interval
SELECT 1.3::real::interval

query error CAST does not support casting from integer to interval
SELECT 2::int::interval

query T
SELECT '1'::interval::interval;
----
00:00:01

query error CAST does not support casting from jsonb to interval
SELECT '{}'::jsonb::interval

query T
SELECT '1'::text::interval
----
00:00:01

query T
SELECT '1'::char::interval
----
00:00:01

query T
SELECT '1'::varchar::interval
----
00:00:01

query T
SELECT '01:02:03'::time::interval;
----
01:02:03

query error CAST does not support casting from timestamp without time zone to interval
SELECT '2002 03-04'::timestamp::interval

query error CAST does not support casting from timestamp with time zone to interval
SELECT '2003 04-05'::timestamptz::interval

query error CAST does not support casting from smallint to jsonb
SELECT 1::smallint::jsonb

query error CAST does not support casting from bigint to jsonb
SELECT 1::bigint::jsonb

#pginvalid
query error CAST does not support casting from boolean to jsonb
SELECT TRUE::boolean::jsonb;

query error CAST does not support casting from date to jsonb
SELECT '2001 02-03'::date::jsonb

query error CAST does not support casting from numeric to jsonb
SELECT 1.1::numeric::jsonb

query error CAST does not support casting from double precision to jsonb
SELECT 1.2::double::jsonb;

query error CAST does not support casting from integer to jsonb
SELECT 2::int::jsonb;

query error CAST does not support casting from interval to jsonb
SELECT '1'::interval::jsonb

query T
SELECT '{}'::jsonb::jsonb;
----
{}

query T
SELECT '1'::jsonb::jsonb;
----
1

query error CAST does not support casting from time to jsonb
SELECT '01:02:03'::time::jsonb

query error CAST does not support casting from timestamp without time zone to jsonb
SELECT '2002 03-04'::timestamp::jsonb

query error CAST does not support casting from timestamp with time zone to jsonb
SELECT '2003 04-05'::timestamptz::jsonb

query T
SELECT 1::smallint::text;
----
1

query T
SELECT 1::bigint::text;
----
1

query T
SELECT TRUE::boolean::text;
----
true

query T
SELECT '2001 02-03'::date::text;
----
2001-02-03

query T
SELECT 1.1::numeric::text;
----
1.1

query T
SELECT 1.2::double::text;
----
1.2

query T
SELECT 1.3::real::text;
----
1.3

query T
SELECT 2::int::text;
----
2

query T
SELECT '1'::interval::text;
----
00:00:01

query T
SELECT '{}'::jsonb::text;
----
{}

query T
SELECT '1'::jsonb::text;
----
1

query T
SELECT 'dog'::text::text;
----
dog

query T
SELECT 'dog'::char(3)::text;
----
dog

query T
SELECT 'dog'::varchar::text;
----
dog

query T
SELECT '01:02:03'::time::text;
----
01:02:03

query T
SELECT '2002 03-04'::timestamp::text;
----
2002-03-04 00:00:00

query T
SELECT '2003 04-05'::timestamptz::text;
----
2003-04-05 00:00:00+00

query T
SELECT trim(trailing from 1::bigint::char(20));
----
1

query T
SELECT trim(trailing from TRUE::boolean::char(20));
----
true

query T
SELECT trim(trailing from '2001 02-03'::date::char(20));
----
2001-02-03

query T
SELECT trim(trailing from 1.1::numeric::char(20));
----
1.1

query T
SELECT trim(trailing from 1.2::double::char(20));
----
1.2

query T
SELECT trim(trailing from 1.3::real::char(20));
----
1.3

query T
SELECT trim(trailing from 2::int::char(20));
----
2

query T
SELECT trim(trailing from '1'::interval::char(20));
----
00:00:01

query T
SELECT trim(trailing from '{}'::jsonb::char(20));
----
{}

query T
SELECT trim(trailing from '1'::jsonb::char(20));
----
1

query T
SELECT trim(trailing from 'dog'::text::char(20));
----
dog

query T
SELECT trim(trailing from 'dog'::char(3)::char(20));
----
dog

query T
SELECT trim(trailing from 'dog'::varchar::char(20));
----
dog

query T
SELECT trim(trailing from '01:02:03'::time::char(20));
----
01:02:03

query T
SELECT trim(trailing from '2002 03-04'::timestamp::char(20));
----
2002-03-04 00:00:00

query T
SELECT trim(trailing from '2003 04-05'::timestamptz::char(20));
----
2003-04-05 00:00:00+

query T
SELECT 1::smallint::varchar;
----
1

query T
SELECT 1::bigint::varchar;
----
1

query T
SELECT TRUE::boolean::varchar;
----
true

query T
SELECT '2001 02-03'::date::varchar;
----
2001-02-03

query T
SELECT 1.1::numeric::varchar;
----
1.1

query T
SELECT 1.2::double::varchar;
----
1.2

query T
SELECT 1.3::real::varchar;
----
1.3

query T
SELECT 2::int::varchar;
----
2

query T
SELECT '1'::interval::varchar;
----
00:00:01

query T
SELECT '{}'::jsonb::varchar;
----
{}

query T
SELECT '1'::jsonb::varchar;
----
1

query T
SELECT 'dog'::text::varchar;
----
dog

query T
SELECT 'dog'::char(3)::varchar;
----
dog

query T
SELECT 'dog'::varchar::varchar;
----
dog

query T
SELECT '01:02:03'::time::varchar;
----
01:02:03

query T
SELECT '2002 03-04'::timestamp::varchar;
----
2002-03-04 00:00:00

query T
SELECT '2003 04-05'::timestamptz::varchar;
----
2003-04-05 00:00:00+00

query error CAST does not support casting from smallint to time
SELECT 1::smallint::time

query error CAST does not support casting from bigint to time
SELECT 1::bigint::time

query error CAST does not support casting from boolean to time
SELECT TRUE::boolean::time

query error CAST does not support casting from date to time
SELECT '2001 02-03'::date::time

query error CAST does not support casting from numeric to time
SELECT 1.1::numeric::time

query error CAST does not support casting from double precision to time
SELECT 1.2::double::time

query error CAST does not support casting from real to time
SELECT 1.3::real::time

query error CAST does not support casting from integer to time
SELECT 2::int::time

query T
SELECT '1'::interval::time;
----
 00:00:01

query error CAST does not support casting from jsonb to time
SELECT '{}'::jsonb::time

query T
SELECT '01:02:03'::text::time
----
01:02:03

query T
SELECT '01:02:03'::char(9)::time
----
01:02:03

query T
SELECT '01:02:03'::varchar::time
----
01:02:03

query T
SELECT '01:02:03'::time::time;
----
 01:02:03

query T
SELECT '2002 03-04'::timestamp::time
----
00:00:00

query T
SELECT '2002 03-04 05:06:07'::timestamp::time
----
05:06:07

query T
SELECT '2003 04-05'::timestamptz::time
----
00:00:00

query T
SELECT '2003 04-05 06:07:08+00'::timestamptz::time
----
06:07:08

query error CAST does not support casting from smallint to timestamp
SELECT 1::smallint::timestamp

query error CAST does not support casting from bigint to timestamp
SELECT 1::bigint::timestamp

query error CAST does not support casting from boolean to timestamp
SELECT TRUE::boolean::timestamp

query T
SELECT '2001 02-03'::date::timestamp;
----
2001-02-03 00:00:00

query error CAST does not support casting from numeric to timestamp
SELECT 1.1::numeric::timestamp

query error CAST does not support casting from double precision to timestamp
SELECT 1.2::double::timestamp

query error CAST does not support casting from real to timestamp
SELECT 1.3::real::timestamp

query error CAST does not support casting from integer to timestamp
SELECT 2::int::timestamp

query error CAST does not support casting from interval to timestamp
SELECT '1'::interval::timestamp

query error CAST does not support casting from jsonb to timestamp
SELECT '{}'::jsonb::timestamp

query T
SELECT '2001 02-03'::text::timestamp
----
2001-02-03 00:00:00

query T
SELECT '2001 02-03'::char(10)::timestamp
----
2001-02-03 00:00:00

query T
SELECT '2001 02-03'::varchar::timestamp
----
2001-02-03 00:00:00

query error CAST does not support casting from time to timestamp
SELECT '01:02:03'::time::timestamp

query T
SELECT '2002 03-04'::timestamp::timestamp;
----
2002-03-04 00:00:00

query T
SELECT '2003 04-05'::timestamptz::timestamp;
----
2003-04-05 00:00:00

query error CAST does not support casting from smallint to timestamp with time zone
SELECT 1::smallint::timestamptz

query error CAST does not support casting from bigint to timestamp with time zone
SELECT 1::bigint::timestamptz

query error CAST does not support casting from boolean to timestamp with time zone
SELECT TRUE::boolean::timestamptz

query T
SELECT '2001 02-03'::date::timestamptz;
----
2001-02-03 00:00:00+00

query error CAST does not support casting from numeric to timestamp with time zone
SELECT 1.1::numeric::timestamptz

query error CAST does not support casting from double precision to timestamp with time zone
SELECT 1.2::double::timestamptz

query error CAST does not support casting from real to timestamp with time zone
SELECT 1.3::real::timestamptz

query error CAST does not support casting from integer to timestamp with time zone
SELECT 2::int::timestamptz

query error CAST does not support casting from interval to timestamp with time zone
SELECT '1'::interval::timestamptz

query error CAST does not support casting from jsonb to timestamp with time zone
SELECT '{}'::jsonb::timestamptz

query T
SELECT '2001 02-03'::text::timestamptz
----
2001-02-03 00:00:00+00

query T
SELECT '2001 02-03'::char(10)::timestamptz
----
2001-02-03 00:00:00+00

query T
SELECT '2001 02-03'::varchar::timestamptz
----
2001-02-03 00:00:00+00

query error CAST does not support casting from time to timestamp with time zone
SELECT '01:02:03'::time::timestamptz

query T
SELECT '2002 03-04'::timestamp::timestamptz;
----
2002-03-04 00:00:00+00

query T
SELECT '2003 04-05'::timestamptz::timestamptz;
----
2003-04-05 00:00:00+00

query T
SELECT 14::smallint::oid;
----
14

query T
SELECT 14::bigint::oid;
----
14

query T
SELECT 14::oid::bigint;
----
14

query T
SELECT 14::oid = 14::bigint;
----
true

query T
SELECT 14::oid = 14::smallint;
----
true

query error "120129019392" OID out of range
SELECT 120129019392::bigint::oid;

# Check that our non-linear type promotion structure works as intended.
# Whenever you have a uint and an int, you unify their types by selecting the
# narrowest integer-like that the uint can be implicitly cast to. In the case
# of 64-bit uints, this means casting the values to numeric.

query TT
SELECT pg_typeof(1::smallint + 1::uint2), 1::smallint + 1::uint2;
----
integer  2

query TT
SELECT pg_typeof(1::smallint + 1::uint4), 1::smallint + 1::uint4;
----
bigint  2

query TT
SELECT pg_typeof(1::smallint + 1::uint8), 1::smallint + 1::uint8;
----
numeric  2

query TT
SELECT pg_typeof(1::integer + 1::uint2), 1::integer + 1::uint2;
----
integer  2

query TT
SELECT pg_typeof(1::integer + 1::uint4), 1::integer + 1::uint4;
----
bigint  2

query TT
SELECT pg_typeof(1::integer + 1::uint8), 1::integer + 1::uint8;
----
numeric  2

query TT
SELECT pg_typeof(1::bigint + 1::uint2), 1::bigint + 1::uint2;
----
bigint  2

query TT
SELECT pg_typeof(1::bigint + 1::uint4), 1::bigint + 1::uint4;
----
bigint  2

query TT
SELECT pg_typeof(1::bigint + 1::uint8), 1::bigint + 1::uint8;
----
numeric  2

query T
SELECT 9223372036854775807::bigint / 18446744073709551615::uint8;
----
0.499999999999999999972894945687862389148

query T
SELECT 18446744073709551615::uint8 / 9223372036854775807::bigint;
----
2.00000000000000000010842021724855044341

query T
select pg_typeof(coalesce(1::uint4, 1::int));
----
bigint

query T
select pg_typeof(coalesce(1::uint4, 1::int, 1::numeric));
----
numeric

query T
select pg_typeof(coalesce(1::uint8, 1::int));
----
numeric

query T
SELECT pg_typeof(coalesce(1::uint4, 1::int, 1::numeric, 1::double));
----
double precision

# These types were not converted because the function's parameters are
# polymorphic without enfocing a relationship between the arguments, and we only
# aggressively cast ints and uints to a wider type in cases where it exactly
# selects a function.

query T multiline
EXPLAIN RAW PLAN FOR
SELECT jsonb_object_agg(1::uint8, 2::int);
----
Reduce aggregates=[jsonb_object_agg[order_by=[]](row(row(uint8_to_text(integer_to_uint8(1)), coalesce(jsonbable_to_jsonb(integer_to_numeric(2)), json_null))))]
  Constant
    - ()

Target cluster: quickstart

EOF

# This would fail to plan if we blithely unified all args into the best common
# int/uint; the second argument to lag does not support numeric types.

query T
WITH t (f1, f2, f3) AS (VALUES (1::uint8, 'a', 1.0))
SELECT lag(f1, 1::int) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
----
NULL

query T
WITH t (f1, f2, f3) AS (VALUES (1::uint8, 'a', 1.0))
SELECT pg_typeof(lag(f1, 1::int) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1))
FROM t
----
uint8

# However, polymorphic type resolution can still appropriately upcast values,
# while leaving other types alone.

query T
WITH t (f1, f2, f3) AS (VALUES (1::uint8, 'a', 1.0))
SELECT lag(f1, 1::int, 0::int) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1)
FROM t
----
0

query T
WITH t (f1, f2, f3) AS (VALUES (1::uint8, 'a', 1.0))
SELECT pg_typeof(lag(f1, 1::int, 0::int) OVER (PARTITION BY f2 ORDER BY f3 DESC, f1))
FROM t
----
numeric

# implicit timestamp_to_timestamptz cast
query T
values ('2023-01-01T00:00:00.666'::timestamp(6)) union all values ('2023-01-01T00:00:00.666666'::timestamptz(3));
----
2023-01-01 00:00:00.666+00
2023-01-01 00:00:00.667+00

query error numeric field overflow
SELECT '1e-307'::float8::numeric

statement ok
CREATE TABLE t1(a varchar(3));

statement ok
CREATE TABLE t2(b varchar(4));

statement error db error: ERROR: value too long for type character varying\(3\)
INSERT INTO t1 VALUES ('123456');

# From the Postgres docs: https://www.postgresql.org/docs/current/datatype-character.html
# "An attempt to store a longer string into a column of these types will result in an error, unless the excess
# characters are all spaces, in which case the string will be truncated to the maximum length. (This somewhat bizarre
# exception is required by the SQL standard.)"
statement ok
INSERT INTO t1 VALUES ('111   ');

statement ok
INSERT INTO t1 VALUES ('123');

statement ok
INSERT INTO t2 VALUES ('1234');

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (TYPES) AS VERBOSE TEXT FOR
(SELECT * FROM t1) UNION ALL (SELECT * FROM t2);
----
Explained Query:
  Union // { types: "(varchar?)" }
    ReadStorage materialize.public.t1 // { types: "(varchar(3)?)" }
    ReadStorage materialize.public.t2 // { types: "(varchar(4)?)" }

Source materialize.public.t1
Source materialize.public.t2

Target cluster: quickstart

EOF

query T
(SELECT * FROM t1) UNION ALL (SELECT * FROM t2);
----
111
123
1234

query T
(SELECT * FROM t2) UNION ALL (SELECT * FROM t1);
----
111
123
1234

statement ok
CREATE TABLE t3(b char(2));

statement ok
INSERT INTO t3 VALUES ('ab');

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (TYPES) AS VERBOSE TEXT FOR
(SELECT * FROM t1) UNION ALL (SELECT * FROM t3);
----
Explained Query:
  Union // { types: "(varchar?)" }
    ReadStorage materialize.public.t1 // { types: "(varchar(3)?)" }
    Project (#1) // { types: "(varchar?)" }
      Map (text_to_varchar[len=unbounded](#0)) // { types: "(char(2)?, varchar?)" }
        ReadStorage materialize.public.t3 // { types: "(char(2)?)" }

Source materialize.public.t1
Source materialize.public.t3

Target cluster: quickstart

EOF

query T rowsort
(SELECT * FROM t1) UNION ALL (SELECT * FROM t3);
----
ab
111
123

statement ok
CREATE TABLE t4(b char(3));

statement ok
INSERT INTO t4 VALUES ('ab ');

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (TYPES) AS VERBOSE TEXT FOR
(SELECT * FROM t3) UNION (SELECT * FROM t4);
----
Explained Query:
  Distinct project=[#0] // { types: "(char?)" }
    Union // { types: "(char?)" }
      ReadStorage materialize.public.t3 // { types: "(char(2)?)" }
      ReadStorage materialize.public.t4 // { types: "(char(3)?)" }

Source materialize.public.t3
Source materialize.public.t4

Target cluster: quickstart

EOF

# Trailing spaces are treated as semantically insignificant in both Postgres and Materialize, so the above
# 'ab' and 'ab ' end up deduplicated.
# See also in char-varchar-distinct.td
query T
(SELECT * FROM t3) UNION (SELECT * FROM t4);
----
ab