Materialize/test/sqllogictest/cockroach/subquery.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/subquery
#
# 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

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

# Tests for subqueries (SELECT statements which are part of a bigger statement).

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

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

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

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

query I
SELECT 1 + (SELECT 1)
----
2

query error db error: ERROR: Expected subselect to return 1 column, got 2 columns
SELECT 1 + (SELECT 1 AS a, 2 AS b)

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

# TODO(ggevay): scalar subquery returning multiple columns. We could add support for this.
# (same for many of the following commented out queries)
# query B
# SELECT (1, 2, 3) = (SELECT 1, 2, 3)
# ----
# true
#
# query B
# SELECT (1, 2, 3) != (SELECT 1, 2, 3)
# ----
# false
#
# query B
# SELECT (SELECT 1, 2, 3) = (SELECT 1, 2, 3)
# ----
# true

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

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

# NB: Cockroach has different behavior from Postgres on a few esoteric
# subqueries. The Cockroach behavior seems more sensical and
# supporting the specific Postgres behavior appears onerous. Fingers
# crossed this doesn't bite us down the road.

# TODO(ggevay): Materialize error msg is unclear to me.
# # Postgres cannot handle this query (but MySQL can), even though it
# # seems sensical:
# #   ERROR:  subquery must return only one column
# #   LINE 1: select (select 1, 2) IN (select 1, 2);
# #                  ^
# query B
# SELECT (SELECT 1, 2) IN (SELECT 1, 2)
# ----
# true
#
# # Postgres cannot handle this query, even though it seems sensical:
# #   ERROR:  subquery must return only one column
# #   LINE 1: select (select 1, 2) IN ((1, 2));
# #                  ^
# query B
# SELECT (SELECT 1, 2) IN ((1, 2))
# ----
# true
#
# # Postgres cannot handle this query, even though it seems sensical:
# #   ERROR:  subquery has too many columns
# #   LINE 1: select (select (1, 2)) IN (select 1, 2);
# #                                  ^
# query B
# SELECT (SELECT (1, 2)) IN (SELECT 1, 2)
# ----
# true

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

# # Postgres cannot handle this query, even though it seems sensical:
# #   ERROR:  subquery must return only one column
# #   LINE 1: select (select 1, 2) in (select (1, 2));
# #                  ^
# query B
# SELECT (SELECT 1, 2) IN (SELECT (1, 2))
# ----
# true

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

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

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

query B
SELECT 1 = SOME(SELECT 1)
----
true

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

query B
SELECT 1 = ALL(SELECT 1)
----
true

query B
SELECT (1, 2) = ALL(SELECT 1, 2)
----
true

query error pgcode 42601 db error: ERROR: Expected subselect to return 1 column, got 2 columns
SELECT (SELECT 1, 2)

query error db error: ERROR: subquery1 has 2 columns available but 1 columns specified
SELECT 1 IN (SELECT 1 AS a, 2 AS b)

query error db error: ERROR: subquery2 has 1 columns available but 2 columns specified
SELECT (1, 2) IN (SELECT 1 AS a)

statement ok
CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT)

statement ok
INSERT INTO abc VALUES (1, 2, 3), (4, 5, 6)

# statement ok
# ALTER TABLE abc SPLIT AT VALUES ((SELECT 1))

query error db error: ERROR: subquery2 has 3 columns available but 2 columns specified
SELECT (1, 2) IN (SELECT * FROM abc)

query B
SELECT (1, 2) IN (SELECT a, b FROM abc)
----
true

query B
SELECT (1, 2) IN (SELECT a, b FROM abc WHERE false)
----
false

query error db error: ERROR: Expected subselect to return 1 column, got 3 columns
SELECT (SELECT * FROM abc)

query error db error: ERROR: Evaluation error: more than one record produced in subquery
SELECT (SELECT a FROM abc)

query I
SELECT (SELECT a FROM abc WHERE false)
----
NULL

query II
VALUES (1, (SELECT (2)))
----
1 2

statement ok
INSERT INTO abc VALUES ((SELECT 7), (SELECT 8), (SELECT 9))

query III
SELECT * FROM abc WHERE a = 7
----
7 8 9

statement error db error: ERROR: column "a" is of type integer but expression is of type record\(f1: integer,f2: integer,f3: integer\)
INSERT INTO abc VALUES ((SELECT (10, 11, 12)))

statement error db error: ERROR: Expected subselect to return 1 column, got 3 columns
INSERT INTO abc VALUES ((SELECT 10, 11, 12))

statement ok
CREATE TABLE xyz (x INT PRIMARY KEY, y INT, z INT)

statement ok
INSERT INTO xyz SELECT * FROM abc

query III rowsort
SELECT * FROM xyz
----
1 2 3
4 5 6
7 8 9

statement ok
INSERT INTO xyz (x, y, z) VALUES (10, 11, 12)

# Materialize doesn't allow subqueries in `SET`.
# statement ok
# UPDATE xyz SET z = (SELECT 10) WHERE x = 7

statement ok
UPDATE xyz SET z = 10 WHERE x = 7

query III rowsort
SELECT * FROM xyz
----
1 2 3
4 5 6
7 8 10
10 11 12

# statement error value type tuple{int, int} doesn't match type int of column "z"
# UPDATE xyz SET z = (SELECT (10, 11)) WHERE x = 7
#
# statement error [subquery must return 2 columns, found 1 | number of columns (2) does not match number of values (1)]
# UPDATE xyz SET (y, z) = (SELECT (11, 12)) WHERE x = 7

#regression, see database-issues#2135
#statement ok
#UPDATE xyz SET (y, z) = (SELECT 11, 12) WHERE x = 7
#
#query III rowsort
#SELECT * FROM xyz
#----
#1 2  3
#4 5  6
#7 11 12
#10 11 12

query B
SELECT 1 IN (SELECT x FROM xyz ORDER BY x DESC)
----
true

query III
SELECT * FROM xyz WHERE x = (SELECT min(x) FROM xyz)
----
1 2 3

query III
SELECT * FROM xyz WHERE x = (SELECT max(x) FROM xyz)
----
10 11 12

query III
SELECT * FROM xyz WHERE x = (SELECT max(x) FROM xyz WHERE EXISTS(SELECT * FROM xyz WHERE z=x+3))
----
10 11 12

statement ok
CREATE TABLE kv (k INT PRIMARY KEY, v STRING)

statement ok
INSERT INTO kv VALUES (1, 'one')

query IT
SELECT * FROM kv WHERE k = (SELECT k FROM kv WHERE (k, v) = (1, 'one'))
----
1 one

query B
SELECT EXISTS(SELECT 1 FROM kv AS x WHERE x.k = 1)
----
true

query B
SELECT EXISTS(SELECT 1 FROM kv WHERE k = 2)
----
false


# Tests for subquery in the FROM part of a SELECT

query II colnames,rowsort
SELECT * FROM (VALUES (1, 2)) AS foo
----
column1 column2
1 2

query II colnames,rowsort
SELECT * FROM (VALUES (1, 2))
----
column1 column2
1 2

query IT colnames,rowsort
SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS foo
----
column1 column2
1 one
2 two
3 three

query III colnames,rowsort
SELECT * FROM (VALUES (1, 2, 3), (4, 5, 6)) AS foo
----
column1 column2 column3
1       2       3
4       5       6

query III colnames,rowsort
SELECT * FROM (VALUES (1, 2, 3), (4, 5, 6)) AS foo (foo1, foo2, foo3)
----
foo1 foo2 foo3
1    2    3
4    5    6

query III colnames,rowsort
SELECT * FROM (VALUES (1, 2, 3), (4, 5, 6)) AS foo (foo1, foo2)
----
foo1 foo2 column3
1    2    3
4    5    6

query III colnames,rowsort
SELECT * FROM (SELECT * FROM xyz) AS foo WHERE x < 7
----
x y  z
1 2  3
4 5  6

query III colnames,rowsort
SELECT * FROM (SELECT * FROM xyz) AS foo (foo1) WHERE foo1 < 7
----
foo1 y  z
1    2  3
4    5  6

query III colnames,rowsort
SELECT * FROM (SELECT * FROM xyz AS moo (moo1, moo2, moo3)) as foo (foo1) WHERE foo1 < 7
----
foo1 moo2 moo3
1    2    3
4    5    6

query III colnames,rowsort
SELECT * FROM (SELECT * FROM xyz AS moo (moo1, moo2, moo3) ORDER BY moo1) as foo (foo1) WHERE foo1 < 7
----
foo1 moo2 moo3
1    2    3
4    5    6

query III colnames
SELECT * FROM (SELECT * FROM xyz AS moo (moo1, moo2, moo3) ORDER BY moo1) as foo (foo1) WHERE foo1 < 7 ORDER BY moo2 DESC
----
foo1 moo2 moo3
4    5    6
1    2    3

query III colnames
SELECT * FROM (SELECT * FROM (VALUES (1, 2, 3), (4, 5, 6)) AS moo (moo1, moo2, moo3) WHERE moo1 = 4) as foo (foo1)
----
foo1 moo2 moo3
4    5    6

query III colnames
SELECT * FROM (SELECT * FROM (VALUES (1, 8, 8), (3, 1, 1), (2, 4, 4)) AS moo (moo1, moo2, moo3) ORDER BY moo2) as foo (foo1) ORDER BY foo1
----
foo1 moo2 moo3
1    8    8
2    4    4
3    1    1

query II colnames
SELECT a, b FROM (VALUES (1, 2, 3), (3, 4, 7), (5, 6, 10)) AS foo (a, b, c) WHERE a + b = c
----
a b
1 2
3 4

query I colnames
SELECT foo.a FROM (VALUES (1), (2), (3)) AS foo (a)
----
a
1
2
3

query IITT colnames
SELECT foo.a, a, column2, foo.column2 FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS foo (a)
----
a a column2 column2
1 1 one     one
2 2 two     two
3 3 three   three

query I
SELECT x FROM xyz WHERE x IN (SELECT x FROM xyz WHERE x = 7)
----
7

# TODO(ggevay): Materialize doesn't allow subqueries in _top-level_ LIMIT or OFFSET clauses.
# query I
# SELECT x FROM xyz WHERE x = 7 LIMIT (SELECT x FROM xyz WHERE x = 1)
# ----
# 7
#
# query I
# SELECT x FROM xyz ORDER BY x OFFSET (SELECT x FROM xyz WHERE x = 1)
# ----
# 4
# 7
# 10

# TODO(ggevay): Materialize doesn't allow subqueries in RETURNING clause.
# query B
# INSERT INTO xyz (x, y, z) VALUES (13, 11, 12) RETURNING (y IN (SELECT y FROM xyz))
# ----
# true

# This test checks that the double sub-query plan expansion caused by a
# sub-expression being shared by two or more plan nodes does not
# panic.
statement ok
CREATE TABLE tab4(col0 INTEGER, col1 FLOAT, col3 INTEGER, col4 FLOAT)

statement ok
INSERT INTO tab4 VALUES (1,1,1,1)

statement ok
CREATE INDEX idx_tab4_0 ON tab4 (col4,col0)

query I
SELECT col0 FROM tab4 WHERE (col0 <= 0 AND col4 <= 5.38) OR (col4 IN (SELECT col1 FROM tab4 WHERE col1 > 8.27)) AND (col3 <= 5 AND (col3 BETWEEN 7 AND 9))
----

statement ok
CREATE TABLE z (z INT PRIMARY KEY)

query I
SELECT * FROM z WHERE CAST(COALESCE((SELECT 'a'), (SELECT 'a')) AS bytea) < 'a'
----

statement ok
CREATE TABLE test (a INT PRIMARY KEY)

statement ok
CREATE TABLE test2(b INT PRIMARY KEY)

# Regression test for materialize#24225.
query I
SELECT * FROM test2 WHERE 0 = CASE WHEN true THEN (SELECT a FROM test LIMIT 1) ELSE 10 END
----

# Regression test for database-issues#8301.
query I
SELECT (SELECT ARRAY(SELECT 1))[1]
----
1

query B
SELECT (SELECT 123 IN (VALUES (1), (2)))
----
false

statement error db error: ERROR: unknown schema 'crdb_internal'
SELECT * FROM xyz WHERE x IN (SELECT crdb_internal.force_error('', 'subqueryfail'))

statement ok
PREPARE a AS SELECT 1 = (SELECT $1::int)

query B
EXECUTE a(1)
----
true

query B
EXECUTE a(2)
----
false

statement ok
PREPARE b AS SELECT EXISTS (SELECT $1::int)

query B
EXECUTE b(3)
----
true

# Regression test for materialize#29205 - make sure the memory account for wrapped local
# planNode within subqueries is properly hooked up.

statement ok
CREATE TABLE a (a TEXT PRIMARY KEY)

# Materialize doesn't support this non-standard [...] syntax.
# statement ok
# SELECT (SELECT repeat(a::STRING, 2) FROM [INSERT INTO a VALUES('foo') RETURNING a]);

# statement ok
# UPDATE abc SET a = 2, (b, c) = (SELECT 5, 6) WHERE a = 1;

statement ok
UPDATE abc SET a = 2, b = 5, c = 6 WHERE a = 1;

# # Failure in outer query with mutations in the subquery do not take effect.
# statement error pq: bar
# SELECT crdb_internal.force_error('foo', 'bar') FROM [INSERT INTO abc VALUES (11,12,13) RETURNING a]

query III
SELECT * FROM abc WHERE a = 11
----

# statement error pq: bar
# INSERT INTO abc VALUES (1,2, (SELECT crdb_internal.force_error('foo', 'bar')))