github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/typing

# LogicTest: local

statement ok
CREATE TABLE f (x FLOAT)

statement ok
INSERT INTO f(x) VALUES (1e10000 * 1e-9999), (3/2), (1)

query R rowsort
SELECT * FROM f
----
10
1.5
1

statement ok
CREATE TABLE i (x INT)

statement error value type decimal doesn't match type int of column "x"
INSERT INTO i(x) VALUES (4.5)

statement ok
INSERT INTO i(x) VALUES (((9 / 3) * (1 / 3))), (2.0), (2.4 + 4.6)

statement error numeric constant out of int64 range
INSERT INTO i(x) VALUES (9223372036854775809)

query I rowsort
SELECT * FROM i
----
1
2
7

statement ok
CREATE TABLE d (x DECIMAL)

statement ok
INSERT INTO d(x) VALUES (((9 / 3) * (1 / 3))), (2.0), (2.4 + 4.6)

query R rowsort
SELECT * FROM d
----
1
2.0
7

statement ok
UPDATE d SET x = x + 1 WHERE x + sqrt(x) >= 2 + .1

query R rowsort
SELECT * FROM d
----
1
3.0
8

statement ok
CREATE TABLE s (x STRING)

query T
SELECT * FROM s WHERE x > b'\x00'
----

statement ok
INSERT INTO s(x) VALUES (b'qwe'), ('start' || b'end')

statement ok
INSERT INTO s(x) VALUES (b'\xfffefd')

query IT rowsort
SELECT length(x), encode(x::bytes, 'escape') from s
----
3 qwe
8 startend
5 \377fefd

statement error incompatible COALESCE expressions: could not parse "foo" as type int
INSERT INTO s VALUES (COALESCE(1, 'foo'))

statement error incompatible COALESCE expressions: could not parse "foo" as type int
INSERT INTO i VALUES (COALESCE(1, 'foo'))

query error incompatible COALESCE expressions: could not parse "foo" as type int
SELECT COALESCE(1, 'foo')

query error incompatible COALESCE expressions: could not parse "foo" as type int
SELECT COALESCE(1::INT, 'foo')

query R
SELECT greatest(-1, 1, 2.3, 123456789, 3 + 5, -(-4))
----
123456789

query T
SELECT greatest('2010-09-29', '2010-09-28'::TIMESTAMP)
----
2010-09-29 00:00:00 +0000 +0000

query T
SELECT greatest('PT12H2M', 'PT12H2M'::INTERVAL, '1s')
----
12:02:00

# This is a current limitation where a nested constant that does not get folded (eg. abs(-9))
# will not be exposed to the same constant type resolution rules as other constants, meaning that
# it may miss out on being upcast. The limitation could be addressed by either improving the
# scope of constant folding or improving homogeneous type resolution.
# TODO(nvanbenschoten) We may be able to address this by desiring the commonNumericConstantType
#     of all constants for the first resolvableExpr in typeCheckSameTypedExprs when the parent
#     expression has no desired type.
query error greatest\(\): expected -1.123 to be of type int, found type decimal
SELECT greatest(-1.123, 1.21313, 2.3, 123456789.321, 3 + 5.3213, -(-4.3213), abs(-9))

query R
SELECT greatest(-1, 1, 2.3, 123456789, 3 + 5, -(-4), abs(-9.0))
----
123456789

statement ok
CREATE TABLE untyped (b bool, n INT, f FLOAT, e DECIMAL, d DATE, ts TIMESTAMP, tz TIMESTAMPTZ, i INTERVAL)

statement ok
INSERT INTO untyped VALUES ('f', '42', '4.2', '4.20', '2010-09-28', '2010-09-28 12:00:00.1', '2010-09-29 12:00:00.1', 'PT12H2M')

query BIRRTTTT
SELECT * FROM untyped
----
false  42  4.2  4.20  2010-09-28 00:00:00 +0000 +0000  2010-09-28 12:00:00.1 +0000 +0000  2010-09-29 12:00:00.1 +0000 UTC  12:02:00

# Issue #14527: support string literal coercion during overload resolution
query T
SELECT ts FROM untyped WHERE ts != '2015-09-18 00:00:00'
----
2010-09-28 12:00:00.1 +0000 +0000

# Regression tests for #15050

statement error pq: parsing as type timestamp: could not parse "Not Timestamp"
CREATE TABLE t15050a (c DECIMAL DEFAULT CASE WHEN now() < 'Not Timestamp' THEN 2 ELSE 2 END);

statement error pq: parsing as type timestamp: could not parse "Not Timestamp"
CREATE TABLE t15050b (c DECIMAL DEFAULT IF(now() < 'Not Timestamp', 2, 2));

# Regression tests for #15632

statement error incompatible IFNULL expressions: could not parse "foo" as type bool
SELECT IFNULL('foo', false)

statement error incompatible IFNULL expressions: could not parse "foo" as type bool
SELECT IFNULL(true, 'foo')

query B
SELECT IFNULL(false, 'true')
----
false

query B
SELECT IFNULL('true', false)
----
true

# Regression tests for #19770

query B
SELECT 1 in (SELECT 1)
----
true

statement error could not parse "a" as type int
SELECT 1 IN (SELECT 'a')

statement error unsupported comparison operator: <int> IN <tuple{tuple{int, int}}>
SELECT 1 IN (SELECT (1, 2))

query B
SELECT (1, 2) IN (SELECT 1, 2)
----
true

query B
SELECT (1, 2) IN (SELECT (1, 2))
----
true

statement ok
CREATE TABLE t1 (a DATE)

statement ok
CREATE TABLE t2 (b TIMESTAMPTZ)

statement ok
INSERT INTO t1 VALUES (DATE '2018-01-01'); INSERT INTO t2 VALUES (TIMESTAMPTZ '2018-01-01');

# Make sure that we do not create invalid filters due to substituting columns
# with different types.
query TT
SELECT * FROM t1, t2 WHERE a = b AND age(b, TIMESTAMPTZ '2017-01-01') > INTERVAL '1 day'
----
2018-01-01 00:00:00 +0000 +0000  2018-01-01 00:00:00 +0000 UTC

# Regression test for #44181: allow left side of BETWEEN to be typed
# differently in the two comparisons.
query B
SELECT '' BETWEEN ''::BYTES AND '';
----
true

# Regression test for #44632: NULLIF should have the type of the first argument.
query I
SELECT NULLIF(NULL, 0) + NULLIF(NULL, 0)
----
NULL

query I
SELECT NULLIF(0, 0) + NULLIF(0, 0)
----
NULL

query I
SELECT NULLIF(0, NULL) + NULLIF(0, NULL)
----
0

# Regression test for #46196.
query T
SELECT max(t0.c0) FROM (VALUES (NULL), (NULL)) t0(c0)
----
NULL

query T
SELECT max(NULL) FROM (VALUES (NULL), (NULL)) t0(c0)
----
NULL

# Test qualified type references.
query IITR
SELECT 1::pg_catalog.int4, 1::pg_catalog.int8, 'aa'::pg_catalog.text, 4.2::pg_catalog.float4
----
1 1 aa 4.2

# Test that we error out referencing unknown types in pg_catalog.
query error pq: type "pg_catalog.special_int" does not exist
SELECT 1::pg_catalog.special_int

# Test that we error out trying to reference types in schemas that
# don't have types.
query error pq: type "crdb_internal.mytype" does not exist
SELECT 1::crdb_internal.mytype