diff options
Diffstat (limited to 'sql/core/src/test/resources/sql-tests')
2 files changed, 108 insertions, 0 deletions
diff --git a/sql/core/src/test/resources/sql-tests/inputs/subquery/negative-cases/invalid-correlation.sql b/sql/core/src/test/resources/sql-tests/inputs/subquery/negative-cases/invalid-correlation.sql new file mode 100644 index 0000000000..cf93c5a835 --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/negative-cases/invalid-correlation.sql @@ -0,0 +1,42 @@ +-- The test file contains negative test cases +-- of invalid queries where error messages are expected. + +create temporary view t1 as select * from values + (1, 2, 3) +as t1(t1a, t1b, t1c); + +create temporary view t2 as select * from values + (1, 0, 1) +as t2(t2a, t2b, t2c); + +create temporary view t3 as select * from values + (3, 1, 2) +as t3(t3a, t3b, t3c); + +-- TC 01.01 +-- The column t2b in the SELECT of the subquery is invalid +-- because it is neither an aggregate function nor a GROUP BY column. +select t1a, t2b +from t1, t2 +where t1b = t2c +and t2b = (select max(avg) + from (select t2b, avg(t2b) avg + from t2 + where t2a = t1.t1b + ) + ) +; + +-- TC 01.02 +-- Invalid due to the column t2b not part of the output from table t2. +select * +from t1 +where t1a in (select min(t2a) + from t2 + group by t2c + having t2c in (select max(t3c) + from t3 + group by t3b + having t3b > t2b )) +; + diff --git a/sql/core/src/test/resources/sql-tests/results/subquery/negative-cases/invalid-correlation.sql.out b/sql/core/src/test/resources/sql-tests/results/subquery/negative-cases/invalid-correlation.sql.out new file mode 100644 index 0000000000..50ae01e181 --- /dev/null +++ b/sql/core/src/test/resources/sql-tests/results/subquery/negative-cases/invalid-correlation.sql.out @@ -0,0 +1,66 @@ +-- Automatically generated by SQLQueryTestSuite +-- Number of queries: 5 + + +-- !query 0 +create temporary view t1 as select * from values + (1, 2, 3) +as t1(t1a, t1b, t1c) +-- !query 0 schema +struct<> +-- !query 0 output + + + +-- !query 1 +create temporary view t2 as select * from values + (1, 0, 1) +as t2(t2a, t2b, t2c) +-- !query 1 schema +struct<> +-- !query 1 output + + + +-- !query 2 +create temporary view t3 as select * from values + (3, 1, 2) +as t3(t3a, t3b, t3c) +-- !query 2 schema +struct<> +-- !query 2 output + + + +-- !query 3 +select t1a, t2b +from t1, t2 +where t1b = t2c +and t2b = (select max(avg) + from (select t2b, avg(t2b) avg + from t2 + where t2a = t1.t1b + ) + ) +-- !query 3 schema +struct<> +-- !query 3 output +org.apache.spark.sql.AnalysisException +expression 't2.`t2b`' is neither present in the group by, nor is it an aggregate function. Add to group by or wrap in first() (or first_value) if you don't care which value you get.; + + +-- !query 4 +select * +from t1 +where t1a in (select min(t2a) + from t2 + group by t2c + having t2c in (select max(t3c) + from t3 + group by t3b + having t3b > t2b )) +-- !query 4 schema +struct<> +-- !query 4 output +org.apache.spark.sql.AnalysisException +resolved attribute(s) t2b#x missing from min(t2a)#x,t2c#x in operator !Filter predicate-subquery#x [(t2c#x = max(t3c)#x) && (t3b#x > t2b#x)]; |