Materialize/test/sqllogictest/cockroach/typing.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# 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 logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/typing
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

mode cockroach

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 materialize#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 materialize#15050

statement error unsupported comparison operator: <timestamptz> < <string>
CREATE TABLE t15050a (c DECIMAL DEFAULT CASE WHEN now() < 'Not Timestamp' THEN 2 ELSE 2 END);

statement error unsupported comparison operator: <timestamptz> < <string>
CREATE TABLE t15050b (c DECIMAL DEFAULT IF(now() < 'Not Timestamp', 2, 2));

# Regression tests for materialize#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 materialize#19770

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

# The heuristic planner and the optimizer give different errors for this query.
# Accept them both.
statement error (unsupported comparison operator: <int> IN <tuple{string}>|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