Materialize/test/testdrive/subquery-scalar-errors.td

# 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.
#
# Test the detection of the case where a scalar subquery returns more than 1 row
# and make sure there are no false negatives and only the expected false positives.
# Queries prefixed with "!" are expected to return an error while those with ">" are expected to succeed
#
# Some of the queries that fail in Materialize will succeed in Postgresql
#
# See https://github.com/MaterializeInc/database-issues/issues/1745 for a discussion on the remaining
# differences between the two databases
#

$ set-sql-timeout duration=125ms

> CREATE TABLE empty (f1 INTEGER, f2 INTEGER);

> CREATE TABLE one_row (f1 INTEGER, f2 INTEGER);
> INSERT INTO one_row VALUES (1, 1);

> CREATE TABLE two_rows (f1 INTEGER, f2 INTEGER);
> INSERT INTO two_rows VALUES (1, 1), (2, 1);

! SELECT (SELECT f1 FROM two_rows);
contains:Evaluation error: more than one record produced in subquery

! SELECT (SELECT f1 FROM two_rows) FROM two_rows LIMIT 0;
contains:Evaluation error: more than one record produced in subquery

! SELECT (SELECT TRUE FROM two_rows);
contains:Evaluation error: more than one record produced in subquery

! SELECT (SELECT f1 FROM two_rows) FROM two_rows;
contains:Evaluation error: more than one record produced in subquery

! SELECT (SELECT f1 FROM two_rows) FROM empty;
contains:Evaluation error: more than one record produced in subquery

! SELECT (SELECT f1 FROM one_row UNION ALL SELECT f1 FROM one_row) FROM two_rows;
contains:Evaluation error: more than one record produced in subquery

! SELECT (SELECT f1 FROM empty UNION ALL SELECT f1 FROM two_rows) FROM two_rows;
contains:Evaluation error: more than one record produced in subquery

! SELECT (SELECT DISTINCT f1 FROM two_rows) FROM two_rows;
contains:Evaluation error: more than one record produced in subquery

> SELECT (SELECT DISTINCT f2 FROM two_rows) FROM two_rows;
1
1

> SELECT (SELECT MIN(f1) FROM two_rows) FROM two_rows;
1
1

> SELECT (SELECT MIN(f2) FROM two_rows GROUP BY f1 ORDER BY MIN(f2) LIMIT 1);
1

! SELECT (SELECT f1 FROM two_rows GROUP BY f1);
contains:Evaluation error: more than one record produced in subquery

! SELECT (SELECT MIN(f1) FROM two_rows UNION ALL SELECT MIN(f2) FROM two_rows) FROM two_rows;
contains:Evaluation error: more than one record produced in subquery

> SELECT (SELECT MIN(f1) FROM two_rows UNION DISTINCT SELECT MIN(f2) FROM two_rows) FROM two_rows;
1
1

! SELECT MIN((SELECT f1 FROM two_rows)) FROM two_rows;
contains:Evaluation error: more than one record produced in subquery

! SELECT * FROM two_rows WHERE f1 > (SELECT f1 FROM two_rows);
contains:Evaluation error: more than one record produced in subquery

! SELECT * FROM two_rows WHERE f1 > (SELECT f1 FROM one_row) AND f1 > (SELECT f1 FROM two_rows);
contains:Evaluation error: more than one record produced in subquery

! SELECT * FROM two_rows WHERE f1 > (SELECT f1 FROM one_row) OR f1 > (SELECT f1 FROM two_rows);
contains:Evaluation error: more than one record produced in subquery

! SELECT * FROM two_rows WHERE (SELECT f1 FROM two_rows) > (SELECT f1 FROM one_row);
contains:Evaluation error: more than one record produced in subquery

! SELECT * FROM two_rows AS o WHERE (SELECT f1 FROM two_rows AS i WHERE o.f2 = i.f2) = 1 AND f1 = 2;
contains:Evaluation error: more than one record produced in subquery

! SELECT * FROM two_rows JOIN two_rows AS r ON (SELECT TRUE FROM two_rows);
contains:Evaluation error: more than one record produced in subquery

! SELECT * FROM two_rows LEFT JOIN two_rows AS r ON (SELECT f1 = 1 FROM two_rows);
contains:Evaluation error: more than one record produced in subquery

! SELECT f1, COUNT(*) as C FROM two_rows GROUP BY f1 HAVING COUNT(*) > (SELECT f1 FROM two_rows);
contains:Evaluation error: more than one record produced in subquery

! SELECT (SELECT f1 FROM two_rows), COUNT(*) FROM two_rows GROUP BY (SELECT f1 FROM two_rows);
contains:Evaluation error: more than one record produced in subquery

! SELECT COUNT(*) FROM two_rows GROUP BY (SELECT f1 FROM two_rows);
contains:Evaluation error: more than one record produced in subquery

! SELECT * FROM two_rows ORDER BY (SELECT f1 FROM two_rows);
contains:Evaluation error: more than one record produced in subquery

! INSERT INTO two_rows VALUES ((SELECT 1 UNION ALL SELECT 2));
contains:more than one record produced in subquery

! SELECT (SELECT f1 from two_rows) + 1 FROM two_rows;
contains:Evaluation error: more than one record produced in subquery

> SELECT (SELECT f1 FROM two_rows LIMIT 0) IS NULL;
true

> SELECT (SELECT f1 FROM two_rows LIMIT 0) IS NULL FROM two_rows;
true
true

> SELECT (SELECT f1 FROM two_rows ORDER BY 1 LIMIT 1);
1

> SELECT (SELECT f1 FROM two_rows ORDER BY 1 LIMIT 1) FROM two_rows;
1
1

> SELECT * FROM two_rows AS a1 WHERE 1 = ( SELECT 1 FROM two_rows ORDER BY 1 LIMIT 1 )
1 1
2 1

> SELECT (SELECT f1 FROM two_rows EXCEPT SELECT f1 FROM one_row) FROM two_rows;
2
2

> SELECT f1 FROM two_rows WHERE f1 = (SELECT f1 FROM two_rows EXCEPT SELECT f1 FROM one_row);
2

# Two columns returned by subquery in scalar context

! SELECT (SELECT * FROM one_row) FROM one_row;
contains:Expected subselect to return 1 column, got 2 columns

! SELECT * FROM one_row WHERE f1 = ( SELECT * FROM one_row );
contains:WHERE clause error: Expected subselect to return 1 column, got 2 columns