# 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')))