Materialize/test/sqllogictest/cockroach/suboperators.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/suboperators
#
# 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 abc (a INT, b INT, C INT)

statement ok
INSERT INTO abc VALUES (1, 10, 100), (2, 20, 200), (3, 30, 300), (NULL, NULL, NULL)

# ANY/SOME with arrays.

query B
SELECT 1 = ANY(ARRAY[1, 2])
----
true

query B
SELECT 1 = ANY (((ARRAY[1, 2])))
----
true

query B
SELECT 1 = SOME(ARRAY[1, 2])
----
true

query B
SELECT 1 = ANY(ARRAY[3, 4])
----
false

query B
SELECT 1 = ANY (((ARRAY[3, 4])))
----
false

query B
SELECT 1 < ANY(ARRAY[0, 5])
----
true

query B
SELECT 1 < ANY(ARRAY[0, 1])
----
false

query B
SELECT 1 = ANY(ARRAY[1.0, 1.1])
----
true

query B
SELECT 1 < ANY(ARRAY[1.0, 1.1])
----
true

query B
SELECT 1 = ANY(ARRAY[1, NULL])
----
true

query T
SELECT 1 = ANY(ARRAY[2, NULL])
----
NULL

query T
SELECT 1 = ANY(ARRAY[NULL, NULL])
----
NULL

query B
SELECT 1 = ANY(ARRAY[1,2] || 3)
----
true

query B
SELECT 1 = ANY(ARRAY[2,3] || 1)
----
true

query B
SELECT 1 = ANY(ARRAY[2,3] || 4)
----
false

query III
SELECT * FROM abc WHERE a = ANY(ARRAY[1,3]) ORDER BY a
----
1 10 100
3 30 300

query III
SELECT * FROM abc WHERE a = ANY(ARRAY[4, 5])
----

query III
SELECT * FROM abc WHERE a = ANY(ARRAY[1, NULL])
----
1 10 100

query III
SELECT * FROM abc WHERE a = ANY(ARRAY[4, NULL])
----

query III
SELECT * FROM abc WHERE a = ANY(ARRAY[NULL, NULL])
----

query error unsupported comparison operator: 1 = ANY ARRAY\['foo', 'bar'\]
SELECT 1 = ANY(ARRAY['foo', 'bar'])

query error unsupported comparison operator: <int> = ANY <string\[\]>
SELECT 1 = ANY(ARRAY['foo'] || 'bar')

# ANY/SOME with subqueries.

query B
SELECT 1 = ANY(SELECT * FROM generate_series(1,3))
----
true

query B
SELECT 1 = ANY(SELECT * FROM generate_series(2,4))
----
false

query B
SELECT 1 < ANY(SELECT * FROM generate_series(1,3))
----
true

query B
SELECT 1 < ANY(SELECT * FROM generate_series(0,1))
----
false

query B
SELECT 1 = ANY(SELECT * FROM unnest(ARRAY[1.0, 1.1]))
----
true

query B
SELECT 1 = ANY(SELECT * FROM unnest(ARRAY[1.0, 1.1]))
----
true

query B
SELECT 1.0 < ANY(SELECT * FROM unnest(ARRAY[1.0, 1.1]))
----
true

query B
SELECT 1.0 = ANY(SELECT * FROM unnest(ARRAY[1.0001, 2]))
----
false

query B
SELECT 1 = ANY(SELECT * FROM unnest(ARRAY[1, NULL]))
----
true

query T
SELECT 1 = ANY(SELECT * FROM unnest(ARRAY[2, NULL]))
----
NULL

query T
SELECT 1 = ANY(SELECT * FROM unnest(ARRAY[NULL, NULL]))
----
NULL

query III
SELECT * FROM abc WHERE a = ANY(SELECT a FROM abc WHERE b = 10)
----
1 10 100

query III
SELECT * FROM abc WHERE a < ANY(SELECT a FROM abc WHERE b = 30) ORDER BY a
----
1 10 100
2 20 200

query III
SELECT * FROM abc WHERE a > ANY(SELECT a FROM abc WHERE b = 30)
----

# ANY predicate in disjunction.
query III rowsort
SELECT * FROM abc WHERE a > ANY(SELECT a FROM abc WHERE b = 20) OR b IS NULL
----
NULL NULL NULL
3    30   300

# ALL predicate in disjunction.
query III rowsort
SELECT * FROM abc WHERE a >= ALL(SELECT a FROM abc WHERE a IS NOT NULL) OR b=10
----
1 10 100
3 30 300

# ANY predicate in NOT NULL expression.
query III rowsort
SELECT * FROM abc WHERE a > ANY(SELECT a FROM abc WHERE b = 20) IS NULL
----
NULL NULL NULL

query III
SELECT * FROM abc WHERE a = ANY(SELECT * FROM unnest(ARRAY[1, NULL]))
----
1 10 100

query III
SELECT * FROM abc WHERE a = ANY(SELECT * FROM unnest(ARRAY[4, NULL]))
----

query III
SELECT * FROM abc WHERE a = ANY(SELECT * FROM unnest(ARRAY[NULL, NULL]))
----

query error unsupported comparison operator: <int> = ANY <tuple\{string\}>
SELECT 1 = ANY(SELECT * FROM unnest(ARRAY['foo', 'bar']))

# ALL with arrays.

query B
SELECT 1 = ALL(ARRAY[1, 1, 1.0])
----
true

query B
SELECT 1 = ALL(ARRAY[1, 1.001, 1.0])
----
false

query B
SELECT 5 > ALL(ARRAY[1, 2, 3])
----
true

query B
SELECT 5 > ALL(ARRAY[6, 7, 8])
----
false

query B
SELECT 5 > ALL(ARRAY[4, 6, 7])
----
false

query B
SELECT 1 = ALL(ARRAY[2, NULL])
----
false

query T
SELECT 1 = ALL(ARRAY[1, NULL])
----
NULL

query T
SELECT 1 = ALL(ARRAY[NULL, NULL])
----
NULL

query B
SELECT 5 > ALL(ARRAY[1, 2] || 3)
----
true

query B
SELECT 5 > ALL(ARRAY[6, 7] || 8)
----
false

query III
SELECT * FROM abc WHERE a > ALL(ARRAY[0, 1]) ORDER BY a
----
2    20   200
3    30   300

query III
SELECT * FROM abc WHERE a > ALL(ARRAY[1, 4])
----

query III
SELECT * FROM abc WHERE a > ALL(ARRAY[1, NULL])
----

query III
SELECT * FROM abc WHERE a > ALL(ARRAY[NULL, NULL])
----

query error unsupported comparison operator: 1 = ALL ARRAY\['foo', 'bar'\]
SELECT 1 = ALL(ARRAY['foo', 'bar'])

query error unsupported comparison operator: <int> = ALL <string\[\]>
SELECT 1 = ALL(ARRAY['foo'] || 'bar')

# ALL with subqueries.

query B
SELECT 1 = ALL(SELECT * FROM unnest(ARRAY[1,1,1]))
----
true

query B
SELECT 1 = ALL(SELECT * FROM unnest(ARRAY[1,2,3]))
----
false

query B
SELECT 1 < ALL(SELECT * FROM generate_series(2,5))
----
true

query B
SELECT 1 < ALL(SELECT * FROM generate_series(1,3))
----
false

query B
SELECT 1 = ALL(SELECT * FROM unnest(ARRAY[2, NULL]))
----
false

query T
SELECT 1 = ALL(SELECT * FROM unnest(ARRAY[1, NULL]))
----
NULL

query T
SELECT 1 = ALL(SELECT * FROM unnest(ARRAY[NULL, NULL]))
----
NULL

query III
SELECT * FROM abc WHERE a < ALL(SELECT b FROM abc WHERE b IS NOT NULL) ORDER BY a
----
1    10   100
2    20   200
3    30   300

query III
SELECT * FROM abc WHERE a < ALL(SELECT a FROM abc WHERE a >= 2)
----
1 10 100

query III
SELECT * FROM abc WHERE a < ALL(SELECT a FROM abc)
----

query III
SELECT * FROM abc WHERE a > ALL(SELECT * FROM unnest(ARRAY[1, NULL]))
----

query III
SELECT * FROM abc WHERE a > ALL(SELECT * FROM unnest(ARRAY[NULL, NULL]))
----

query error unsupported comparison operator: <int> = ALL <tuple\{string\}>
SELECT 1 = ALL(SELECT * FROM unnest(ARRAY['foo', 'bar']))

# ANY/ALL with tuples.

query B
SELECT 1 = ANY (1, 2, 3)
----
true

query error unsupported comparison operator: <int> = <string>
SELECT 1 = ANY (1, 2, 3.3, 'foo')

query B
SELECT 1 = ANY (((1, 2, 3)))
----
true

query B
SELECT 1 = ANY (2, 3, 4)
----
false

query B
SELECT 1 = ANY (((2, 3, 4)))
----
false

query B
SELECT 1 = ANY (1, 1.1)
----
true

query B
SELECT 1::decimal = ANY (1, 1.1)
----
true

query B
SELECT 1 = ANY (1.0, 1.1)
----
true

query B
SELECT 1 = ANY (((1.0, 1.1)))
----
true

query B
SELECT 1::decimal = ANY (1.0, 1.1)
----
true

query B
SELECT 1::decimal = ANY (((1.0, 1.1)))
----
true

query error unsupported comparison operator: <int> = <string>
SELECT 1 = ANY (1, 'hello', 3)

query B
SELECT 1 = ANY ROW()
----
false