This is an automated email from the ASF dual-hosted git repository.
wenchen pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new 0b8e0998c673 [SPARK-46446][SQL] Disable subqueries with correlated
OFFSET to fix correctness bug
0b8e0998c673 is described below
commit 0b8e0998c67331bc82428d807476584f3d21f934
Author: Jack Chen <[email protected]>
AuthorDate: Tue Dec 19 13:23:45 2023 +0800
[SPARK-46446][SQL] Disable subqueries with correlated OFFSET to fix
correctness bug
### What changes were proposed in this pull request?
Subqueries with correlation under LIMIT with OFFSET have a correctness bug,
introduced recently when support for correlation under OFFSET was enabled but
were not handled correctly. (So we went from unsupported, query throws error ->
wrong results.) This is in master branch, not yet released.
This PR first disables correlated OFFSET by adding a feature flag for it,
which is disabled. Next PR will add support for it and re-enable it. This PR
also adds a feature flag for the related LIMIT support (which is enabled).
It’s a bug in all types of correlated subqueries: scalar, lateral, IN,
EXISTS
Example repro:
```
create table x(x1 int, x2 int);
insert into x values (1, 1), (2, 2);
create table y(y1 int, y2 int);
insert into y values (1, 1), (1, 2), (2, 4);
select * from x where exists (select * from y where x1 = y1 limit 1 offset
2)
```
Correct result: empty set
Spark result: Array([2,2])
### Why are the changes needed?
Correctness bug
### Does this PR introduce _any_ user-facing change?
Disables correlated OFFSET query shape which was not handled correctly.
(This was enabled on master branch but not yet released.)
### How was this patch tested?
Add tests
### Was this patch authored or co-authored using generative AI tooling?
No
Closes #44401 from jchen5/offset-disable.
Authored-by: Jack Chen <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
---
.../sql/catalyst/analysis/CheckAnalysis.scala | 10 +-
.../org/apache/spark/sql/internal/SQLConf.scala | 16 ++
.../exists-subquery/exists-orderby-limit.sql.out | 70 +++---
.../subquery/in-subquery/in-limit.sql.out | 208 ++++++++---------
.../subquery/subquery-offset.sql.out | 253 ++++++++++++++++++++
.../sql-tests/inputs/subquery/subquery-offset.sql | 50 ++++
.../exists-subquery/exists-orderby-limit.sql.out | 48 ++--
.../results/subquery/in-subquery/in-limit.sql.out | 119 ++++++++--
.../results/subquery/subquery-offset.sql.out | 254 +++++++++++++++++++++
9 files changed, 829 insertions(+), 199 deletions(-)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
index abb2b4f1da59..1b69e933815b 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CheckAnalysis.scala
@@ -1374,11 +1374,17 @@ trait CheckAnalysis extends PredicateHelper with
LookupCatalog with QueryErrorsB
// Correlated subquery can have a LIMIT clause
case l @ Limit(_, input) =>
failOnInvalidOuterReference(l)
- checkPlan(input, aggregated, canContainOuter)
+ checkPlan(
+ input,
+ aggregated,
+ canContainOuter &&
SQLConf.get.getConf(SQLConf.DECORRELATE_LIMIT_ENABLED))
case o @ Offset(_, input) =>
failOnInvalidOuterReference(o)
- checkPlan(input, aggregated, canContainOuter)
+ checkPlan(
+ input,
+ aggregated,
+ canContainOuter &&
SQLConf.get.getConf(SQLConf.DECORRELATE_OFFSET_ENABLED))
// Category 4: Any other operators not in the above 3 categories
// cannot be on a correlation path, that is they are allowed only
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
index 448474ae2faa..6404779f30ac 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
@@ -3455,6 +3455,22 @@ object SQLConf {
.booleanConf
.createWithDefault(true)
+ val DECORRELATE_LIMIT_ENABLED =
+ buildConf("spark.sql.optimizer.decorrelateLimit.enabled")
+ .internal()
+ .doc("Decorrelate subqueries with correlation under LIMIT.")
+ .version("4.0.0")
+ .booleanConf
+ .createWithDefault(true)
+
+ val DECORRELATE_OFFSET_ENABLED =
+ buildConf("spark.sql.optimizer.decorrelateOffset.enabled")
+ .internal()
+ .doc("Decorrelate subqueries with correlation under LIMIT with OFFSET.")
+ .version("4.0.0")
+ .booleanConf
+ .createWithDefault(false) // Disabled for now, see SPARK-46446
+
val DECORRELATE_EXISTS_IN_SUBQUERY_LEGACY_INCORRECT_COUNT_HANDLING_ENABLED =
buildConf("spark.sql.optimizer.decorrelateExistsSubqueryLegacyIncorrectCountHandling.enabled")
.internal()
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/exists-subquery/exists-orderby-limit.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/exists-subquery/exists-orderby-limit.sql.out
index c291c228cbeb..e51b5c1f0716 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/exists-subquery/exists-orderby-limit.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/exists-subquery/exists-orderby-limit.sql.out
@@ -482,27 +482,21 @@ WHERE NOT EXISTS (SELECT max(dept.dept_id) a
LIMIT 2
OFFSET 1)
-- !query analysis
-Project [emp_name#x]
-+- Filter NOT exists#x [dept_id#x]
- : +- GlobalLimit 2
- : +- LocalLimit 2
- : +- Offset 1
- : +- Project [a#x]
- : +- Sort [state#x ASC NULLS FIRST], true
- : +- Aggregate [state#x], [max(dept_id#x) AS a#x, state#x]
- : +- Filter (dept_id#x = outer(dept_id#x))
- : +- SubqueryAlias dept
- : +- View (`DEPT`, [dept_id#x,dept_name#x,state#x])
- : +- Project [cast(dept_id#x as int) AS
dept_id#x, cast(dept_name#x as string) AS dept_name#x, cast(state#x as string)
AS state#x]
- : +- Project [dept_id#x, dept_name#x,
state#x]
- : +- SubqueryAlias DEPT
- : +- LocalRelation [dept_id#x,
dept_name#x, state#x]
- +- SubqueryAlias emp
- +- View (`EMP`, [id#x,emp_name#x,hiredate#x,salary#x,dept_id#x])
- +- Project [cast(id#x as int) AS id#x, cast(emp_name#x as string) AS
emp_name#x, cast(hiredate#x as date) AS hiredate#x, cast(salary#x as double) AS
salary#x, cast(dept_id#x as int) AS dept_id#x]
- +- Project [id#x, emp_name#x, hiredate#x, salary#x, dept_id#x]
- +- SubqueryAlias EMP
- +- LocalRelation [id#x, emp_name#x, hiredate#x, salary#x,
dept_id#x]
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (dept_id#x = outer(dept_id#x))\n+- SubqueryAlias
dept\n +- View (`DEPT`, [dept_id#x,dept_name#x,state#x])\n +- Project
[cast(dept_id#x as int) AS dept_id#x, cast(dept_name#x as string) AS
dept_name#x, cast(state#x as string) AS state#x]\n +- Project
[dept_id#x, dept_name#x, state#x]\n +- SubqueryAlias DEPT\n
+- LocalRelation [dept_id#x, dept_name#x, state#x]\n"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 47,
+ "stopIndex" : 191,
+ "fragment" : "SELECT max(dept.dept_id) a\n FROM dept\n
WHERE dept.dept_id = emp.dept_id\n GROUP
BY state"
+ } ]
+}
-- !query
@@ -691,25 +685,21 @@ WHERE EXISTS (SELECT dept.dept_name
LIMIT 1
OFFSET 2)
-- !query analysis
-Project [id#x, emp_name#x, hiredate#x, salary#x, dept_id#x]
-+- Filter exists#x [dept_id#x]
- : +- GlobalLimit 1
- : +- LocalLimit 1
- : +- Offset 2
- : +- Project [dept_name#x]
- : +- Filter NOT (dept_id#x = outer(dept_id#x))
- : +- SubqueryAlias dept
- : +- View (`DEPT`, [dept_id#x,dept_name#x,state#x])
- : +- Project [cast(dept_id#x as int) AS dept_id#x,
cast(dept_name#x as string) AS dept_name#x, cast(state#x as string) AS state#x]
- : +- Project [dept_id#x, dept_name#x, state#x]
- : +- SubqueryAlias DEPT
- : +- LocalRelation [dept_id#x, dept_name#x,
state#x]
- +- SubqueryAlias emp
- +- View (`EMP`, [id#x,emp_name#x,hiredate#x,salary#x,dept_id#x])
- +- Project [cast(id#x as int) AS id#x, cast(emp_name#x as string) AS
emp_name#x, cast(hiredate#x as date) AS hiredate#x, cast(salary#x as double) AS
salary#x, cast(dept_id#x as int) AS dept_id#x]
- +- Project [id#x, emp_name#x, hiredate#x, salary#x, dept_id#x]
- +- SubqueryAlias EMP
- +- LocalRelation [id#x, emp_name#x, hiredate#x, salary#x,
dept_id#x]
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter NOT (dept_id#x = outer(dept_id#x))\n+- SubqueryAlias
dept\n +- View (`DEPT`, [dept_id#x,dept_name#x,state#x])\n +- Project
[cast(dept_id#x as int) AS dept_id#x, cast(dept_name#x as string) AS
dept_name#x, cast(state#x as string) AS state#x]\n +- Project
[dept_id#x, dept_name#x, state#x]\n +- SubqueryAlias DEPT\n
+- LocalRelation [dept_id#x, dept_name#x, state#x]\n"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 36,
+ "stopIndex" : 133,
+ "fragment" : "SELECT dept.dept_name\n FROM dept\n
WHERE dept.dept_id <> emp.dept_id"
+ } ]
+}
-- !query
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-limit.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-limit.sql.out
index adfebbba394d..5215c6d82fb6 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-limit.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/in-subquery/in-limit.sql.out
@@ -570,25 +570,21 @@ WHERE t1b NOT IN (SELECT t2b
LIMIT 2
OFFSET 2)
-- !query analysis
-Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-+- Filter NOT t1b#x IN (list#x [t1b#x])
- : +- GlobalLimit 2
- : +- LocalLimit 2
- : +- Offset 2
- : +- Project [t2b#x]
- : +- Filter (t2b#x = outer(t1b#x))
- : +- SubqueryAlias t2
- : +- View (`t2`,
[t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x,
cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as
bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS
t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x,
cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x,
t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x,
t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- +- SubqueryAlias t1
- +- View (`t1`, [t1a#x,t1b#x,t1c#x,t1d#xL,t1e#x,t1f#x,t1g#x,t1h#x,t1i#x])
- +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint)
AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL,
cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as
decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date)
AS t1i#x]
- +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x,
t1h#x, t1i#x]
- +- SubqueryAlias t1
- +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x,
t1g#x, t1h#x, t1i#x]
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (t2b#x = outer(t1b#x))\n+- SubqueryAlias t2\n +-
View (`t2`, [t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])\n +-
Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x,
cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as
float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS
t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]\n
+- Project [t2a#x, t2b [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 113,
+ "fragment" : "SELECT t2b\n FROM t2\n
WHERE t2b = t1b"
+ } ]
+}
-- !query
@@ -646,31 +642,21 @@ ORDER BY t1b NULLS last
LIMIT 1
OFFSET 1
-- !query analysis
-GlobalLimit 1
-+- LocalLimit 1
- +- Offset 1
- +- Sort [t1b#x ASC NULLS LAST], true
- +- Aggregate [t1b#x], [count(distinct t1a#x) AS count(DISTINCT
t1a)#xL, t1b#x]
- +- Filter NOT t1d#xL IN (list#x [t1b#x])
- : +- GlobalLimit 1
- : +- LocalLimit 1
- : +- Offset 1
- : +- Project [t2d#xL]
- : +- Sort [t2b#x DESC NULLS FIRST, t2d#xL ASC
NULLS FIRST], true
- : +- Project [t2d#xL, t2b#x]
- : +- Filter (t2b#x > outer(t1b#x))
- : +- SubqueryAlias t2
- : +- View (`t2`,
[t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])
- : +- Project [cast(t2a#x as string)
AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x,
cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as
double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as
timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x,
t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x,
t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- +- SubqueryAlias t1
- +- View (`t1`,
[t1a#x,t1b#x,t1c#x,t1d#xL,t1e#x,t1f#x,t1g#x,t1h#x,t1i#x])
- +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as
smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS
t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x,
cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x,
cast(t1i#x as date) AS t1i#x]
- +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x,
t1g#x, t1h#x, t1i#x]
- +- SubqueryAlias t1
- +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL,
t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (t2b#x > outer(t1b#x))\n+- SubqueryAlias t2\n +-
View (`t2`, [t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])\n +-
Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x,
cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as
float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS
t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]\n
+- Project [t2a#x, t2b [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 72,
+ "stopIndex" : 145,
+ "fragment" : "SELECT t2d\n FROM t2\n
WHERE t2b > t1b"
+ } ]
+}
-- !query
@@ -710,25 +696,21 @@ WHERE t1a IN (SELECT t2a
OFFSET 2)
OFFSET 2
-- !query analysis
-Offset 2
-+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
- +- Filter t1a#x IN (list#x [t1d#xL])
- : +- Offset 2
- : +- Sort [t2a#x ASC NULLS FIRST], true
- : +- Project [t2a#x]
- : +- Filter (outer(t1d#xL) = t2d#xL)
- : +- SubqueryAlias t2
- : +- View (`t2`,
[t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x,
cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as
bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS
t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x,
cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x,
t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x,
t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- +- SubqueryAlias t1
- +- View (`t1`,
[t1a#x,t1b#x,t1c#x,t1d#xL,t1e#x,t1f#x,t1g#x,t1h#x,t1i#x])
- +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as
smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS
t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x,
cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x,
cast(t1i#x as date) AS t1i#x]
- +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x,
t1h#x, t1i#x]
- +- SubqueryAlias t1
- +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x,
t1f#x, t1g#x, t1h#x, t1i#x]
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (outer(t1d#xL) = t2d#xL)\n+- SubqueryAlias t2\n +-
View (`t2`, [t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])\n +-
Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x,
cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as
float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS
t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]\n
+- Project [t2a#x, t [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 35,
+ "stopIndex" : 101,
+ "fragment" : "SELECT t2a\n FROM t2\n WHERE
t1d = t2d"
+ } ]
+}
-- !query
@@ -770,25 +752,21 @@ WHERE t1c IN (SELECT t2c
OFFSET 2)
OFFSET 1
-- !query analysis
-Offset 1
-+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
- +- Filter t1c#x IN (list#x [t1b#x])
- : +- Offset 2
- : +- Sort [t2c#x ASC NULLS FIRST], true
- : +- Project [t2c#x]
- : +- Filter (t2b#x < outer(t1b#x))
- : +- SubqueryAlias t2
- : +- View (`t2`,
[t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x,
cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as
bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS
t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x,
cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x,
t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x,
t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- +- SubqueryAlias t1
- +- View (`t1`,
[t1a#x,t1b#x,t1c#x,t1d#xL,t1e#x,t1f#x,t1g#x,t1h#x,t1i#x])
- +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as
smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS
t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x,
cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x,
cast(t1i#x as date) AS t1i#x]
- +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x,
t1h#x, t1i#x]
- +- SubqueryAlias t1
- +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x,
t1f#x, t1g#x, t1h#x, t1i#x]
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (t2b#x < outer(t1b#x))\n+- SubqueryAlias t2\n +-
View (`t2`, [t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])\n +-
Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x,
cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as
float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS
t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]\n
+- Project [t2a#x, t2b [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 35,
+ "stopIndex" : 101,
+ "fragment" : "SELECT t2c\n FROM t2\n WHERE
t2b < t1b"
+ } ]
+}
-- !query
@@ -860,23 +838,21 @@ WHERE t1b NOT IN (SELECT t2b
WHERE t2b < t1b
OFFSET 2)
-- !query analysis
-Aggregate [count(1) AS count(1)#xL]
-+- Filter NOT t1b#x IN (list#x [t1b#x])
- : +- Offset 2
- : +- Project [t2b#x]
- : +- Filter (t2b#x < outer(t1b#x))
- : +- SubqueryAlias t2
- : +- View (`t2`,
[t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as
smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS
t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x,
cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x,
cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x,
t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL,
t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- +- SubqueryAlias t1
- +- View (`t1`, [t1a#x,t1b#x,t1c#x,t1d#xL,t1e#x,t1f#x,t1g#x,t1h#x,t1i#x])
- +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint)
AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL,
cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as
decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date)
AS t1i#x]
- +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x,
t1h#x, t1i#x]
- +- SubqueryAlias t1
- +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x,
t1g#x, t1h#x, t1i#x]
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (t2b#x < outer(t1b#x))\n+- SubqueryAlias t2\n +-
View (`t2`, [t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])\n +-
Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x,
cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as
float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS
t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]\n
+- Project [t2a#x, t2b [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 46,
+ "stopIndex" : 120,
+ "fragment" : "SELECT t2b\n FROM t2\n
WHERE t2b < t1b"
+ } ]
+}
-- !query
@@ -928,25 +904,21 @@ WHERE t1b NOT IN (SELECT t2b
LIMIT 2
OFFSET 2)
-- !query analysis
-Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-+- Filter NOT t1b#x IN (list#x [t1b#x])
- : +- GlobalLimit 2
- : +- LocalLimit 2
- : +- Offset 2
- : +- Project [t2b#x]
- : +- Filter (t2b#x = outer(t1b#x))
- : +- SubqueryAlias t2
- : +- View (`t2`,
[t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])
- : +- Project [cast(t2a#x as string) AS t2a#x,
cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as
bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS
t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x,
cast(t2i#x as date) AS t2i#x]
- : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x,
t2f#x, t2g#x, t2h#x, t2i#x]
- : +- SubqueryAlias t2
- : +- LocalRelation [t2a#x, t2b#x, t2c#x,
t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
- +- SubqueryAlias t1
- +- View (`t1`, [t1a#x,t1b#x,t1c#x,t1d#xL,t1e#x,t1f#x,t1g#x,t1h#x,t1i#x])
- +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint)
AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL,
cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as
decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date)
AS t1i#x]
- +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x,
t1h#x, t1i#x]
- +- SubqueryAlias t1
- +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x,
t1g#x, t1h#x, t1i#x]
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (t2b#x = outer(t1b#x))\n+- SubqueryAlias t2\n +-
View (`t2`, [t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])\n +-
Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x,
cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as
float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS
t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]\n
+- Project [t2a#x, t2b [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 113,
+ "fragment" : "SELECT t2b\n FROM t2\n
WHERE t2b = t1b"
+ } ]
+}
-- !query
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/subquery-offset.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/subquery-offset.sql.out
new file mode 100644
index 000000000000..cee1de55aa4a
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/subquery/subquery-offset.sql.out
@@ -0,0 +1,253 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+create table x(x1 int, x2 int)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "NOT_SUPPORTED_COMMAND_WITHOUT_HIVE_SUPPORT",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "cmd" : "CREATE Hive TABLE (AS SELECT)"
+ }
+}
+
+
+-- !query
+insert into x values (1, 1), (2, 2)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "TABLE_OR_VIEW_NOT_FOUND",
+ "sqlState" : "42P01",
+ "messageParameters" : {
+ "relationName" : "`x`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 13,
+ "stopIndex" : 13,
+ "fragment" : "x"
+ } ]
+}
+
+
+-- !query
+create table y(y1 int, y2 int)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "NOT_SUPPORTED_COMMAND_WITHOUT_HIVE_SUPPORT",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "cmd" : "CREATE Hive TABLE (AS SELECT)"
+ }
+}
+
+
+-- !query
+insert into y values (1, 1), (1, 2), (2, 4)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "TABLE_OR_VIEW_NOT_FOUND",
+ "sqlState" : "42P01",
+ "messageParameters" : {
+ "relationName" : "`y`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 13,
+ "stopIndex" : 13,
+ "fragment" : "y"
+ } ]
+}
+
+
+-- !query
+select * from x where exists (select * from y where x1 = y1 limit 1 offset 2)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "TABLE_OR_VIEW_NOT_FOUND",
+ "sqlState" : "42P01",
+ "messageParameters" : {
+ "relationName" : "`x`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 15,
+ "fragment" : "x"
+ } ]
+}
+
+
+-- !query
+select * from x join lateral (select * from y where x1 = y1 limit 1 offset 2)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "TABLE_OR_VIEW_NOT_FOUND",
+ "sqlState" : "42P01",
+ "messageParameters" : {
+ "relationName" : "`x`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 15,
+ "fragment" : "x"
+ } ]
+}
+
+
+-- !query
+select * from x where x1 in (select y1 from y limit 1 offset 2)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "TABLE_OR_VIEW_NOT_FOUND",
+ "sqlState" : "42P01",
+ "messageParameters" : {
+ "relationName" : "`x`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 15,
+ "fragment" : "x"
+ } ]
+}
+
+
+-- !query
+select * from x where (select sum(y2) from y where x1 = y1 limit 1 offset 2) >
2
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "TABLE_OR_VIEW_NOT_FOUND",
+ "sqlState" : "42P01",
+ "messageParameters" : {
+ "relationName" : "`x`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 15,
+ "fragment" : "x"
+ } ]
+}
+
+
+-- !query
+CREATE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
+ (100, "emp 1", date "2005-01-01", 100.00D, 10),
+ (100, "emp 1", date "2005-01-01", 100.00D, 10),
+ (200, "emp 2", date "2003-01-01", 200.00D, 10),
+ (300, "emp 3", date "2002-01-01", 300.00D, 20),
+ (400, "emp 4", date "2005-01-01", 400.00D, 30),
+ (500, "emp 5", date "2001-01-01", 400.00D, NULL),
+ (600, "emp 6 - no dept", date "2001-01-01", 400.00D, 100),
+ (700, "emp 7", date "2010-01-01", 400.00D, 100),
+ (800, "emp 8", date "2016-01-01", 150.00D, 70)
+AS EMP(id, emp_name, hiredate, salary, dept_id)
+-- !query analysis
+CreateViewCommand `EMP`, SELECT * FROM VALUES
+ (100, "emp 1", date "2005-01-01", 100.00D, 10),
+ (100, "emp 1", date "2005-01-01", 100.00D, 10),
+ (200, "emp 2", date "2003-01-01", 200.00D, 10),
+ (300, "emp 3", date "2002-01-01", 300.00D, 20),
+ (400, "emp 4", date "2005-01-01", 400.00D, 30),
+ (500, "emp 5", date "2001-01-01", 400.00D, NULL),
+ (600, "emp 6 - no dept", date "2001-01-01", 400.00D, 100),
+ (700, "emp 7", date "2010-01-01", 400.00D, 100),
+ (800, "emp 8", date "2016-01-01", 150.00D, 70)
+AS EMP(id, emp_name, hiredate, salary, dept_id), false, false, LocalTempView,
true
+ +- Project [id#x, emp_name#x, hiredate#x, salary#x, dept_id#x]
+ +- SubqueryAlias EMP
+ +- LocalRelation [id#x, emp_name#x, hiredate#x, salary#x, dept_id#x]
+
+
+-- !query
+CREATE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
+ (10, "dept 1", "CA"),
+ (20, "dept 2", "NY"),
+ (30, "dept 3", "TX"),
+ (40, "dept 4 - unassigned", "OR"),
+ (50, "dept 5 - unassigned", "NJ"),
+ (70, "dept 7", "FL")
+AS DEPT(dept_id, dept_name, state)
+-- !query analysis
+CreateViewCommand `DEPT`, SELECT * FROM VALUES
+ (10, "dept 1", "CA"),
+ (20, "dept 2", "NY"),
+ (30, "dept 3", "TX"),
+ (40, "dept 4 - unassigned", "OR"),
+ (50, "dept 5 - unassigned", "NJ"),
+ (70, "dept 7", "FL")
+AS DEPT(dept_id, dept_name, state), false, false, LocalTempView, true
+ +- Project [dept_id#x, dept_name#x, state#x]
+ +- SubqueryAlias DEPT
+ +- LocalRelation [dept_id#x, dept_name#x, state#x]
+
+
+-- !query
+SELECT emp_name
+FROM emp
+WHERE EXISTS (SELECT max(dept.dept_id) a
+ FROM dept
+ WHERE dept.dept_id = emp.dept_id
+ GROUP BY state
+ ORDER BY state
+ LIMIT 2
+ OFFSET 1)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (dept_id#x = outer(dept_id#x))\n+- SubqueryAlias
dept\n +- View (`DEPT`, [dept_id#x,dept_name#x,state#x])\n +- Project
[cast(dept_id#x as int) AS dept_id#x, cast(dept_name#x as string) AS
dept_name#x, cast(state#x as string) AS state#x]\n +- Project
[dept_id#x, dept_name#x, state#x]\n +- SubqueryAlias DEPT\n
+- LocalRelation [dept_id#x, dept_name#x, state#x]\n"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 42,
+ "stopIndex" : 186,
+ "fragment" : "SELECT max(dept.dept_id) a\n FROM dept\n
WHERE dept.dept_id = emp.dept_id\n GROUP
BY state"
+ } ]
+}
+
+
+-- !query
+SELECT emp_name
+FROM emp
+JOIN LATERAL (SELECT max(dept.dept_id) a
+ FROM dept
+ WHERE dept.dept_id = emp.dept_id
+ GROUP BY state
+ ORDER BY state
+ LIMIT 2
+ OFFSET 1)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (dept_id#x = outer(dept_id#x))\n+- SubqueryAlias
dept\n +- View (`DEPT`, [dept_id#x,dept_name#x,state#x])\n +- Project
[cast(dept_id#x as int) AS dept_id#x, cast(dept_name#x as string) AS
dept_name#x, cast(state#x as string) AS state#x]\n +- Project
[dept_id#x, dept_name#x, state#x]\n +- SubqueryAlias DEPT\n
+- LocalRelation [dept_id#x, dept_name#x, state#x]\n"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 42,
+ "stopIndex" : 186,
+ "fragment" : "SELECT max(dept.dept_id) a\n FROM dept\n
WHERE dept.dept_id = emp.dept_id\n GROUP
BY state"
+ } ]
+}
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/subquery/subquery-offset.sql
b/sql/core/src/test/resources/sql-tests/inputs/subquery/subquery-offset.sql
new file mode 100644
index 000000000000..80ba45a3a579
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/subquery/subquery-offset.sql
@@ -0,0 +1,50 @@
+create table x(x1 int, x2 int);
+insert into x values (1, 1), (2, 2);
+create table y(y1 int, y2 int);
+insert into y values (1, 1), (1, 2), (2, 4);
+
+select * from x where exists (select * from y where x1 = y1 limit 1 offset 2);
+select * from x join lateral (select * from y where x1 = y1 limit 1 offset 2);
+select * from x where x1 in (select y1 from y limit 1 offset 2);
+select * from x where (select sum(y2) from y where x1 = y1 limit 1 offset 2) >
2;
+
+CREATE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
+ (100, "emp 1", date "2005-01-01", 100.00D, 10),
+ (100, "emp 1", date "2005-01-01", 100.00D, 10),
+ (200, "emp 2", date "2003-01-01", 200.00D, 10),
+ (300, "emp 3", date "2002-01-01", 300.00D, 20),
+ (400, "emp 4", date "2005-01-01", 400.00D, 30),
+ (500, "emp 5", date "2001-01-01", 400.00D, NULL),
+ (600, "emp 6 - no dept", date "2001-01-01", 400.00D, 100),
+ (700, "emp 7", date "2010-01-01", 400.00D, 100),
+ (800, "emp 8", date "2016-01-01", 150.00D, 70)
+AS EMP(id, emp_name, hiredate, salary, dept_id);
+
+CREATE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
+ (10, "dept 1", "CA"),
+ (20, "dept 2", "NY"),
+ (30, "dept 3", "TX"),
+ (40, "dept 4 - unassigned", "OR"),
+ (50, "dept 5 - unassigned", "NJ"),
+ (70, "dept 7", "FL")
+AS DEPT(dept_id, dept_name, state);
+
+SELECT emp_name
+FROM emp
+WHERE EXISTS (SELECT max(dept.dept_id) a
+ FROM dept
+ WHERE dept.dept_id = emp.dept_id
+ GROUP BY state
+ ORDER BY state
+ LIMIT 2
+ OFFSET 1);
+
+SELECT emp_name
+FROM emp
+JOIN LATERAL (SELECT max(dept.dept_id) a
+ FROM dept
+ WHERE dept.dept_id = emp.dept_id
+ GROUP BY state
+ ORDER BY state
+ LIMIT 2
+ OFFSET 1);
diff --git
a/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-orderby-limit.sql.out
b/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-orderby-limit.sql.out
index 344cf54a96d8..614856915db6 100644
---
a/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-orderby-limit.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/subquery/exists-subquery/exists-orderby-limit.sql.out
@@ -305,14 +305,23 @@ WHERE NOT EXISTS (SELECT max(dept.dept_id) a
LIMIT 2
OFFSET 1)
-- !query schema
-struct<emp_name:string>
+struct<>
-- !query output
-emp 1
-emp 1
-emp 2
-emp 5
-emp 6 - no dept
-emp 7
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (dept_id#x = outer(dept_id#x))\n+- SubqueryAlias
dept\n +- View (`DEPT`, [dept_id#x,dept_name#x,state#x])\n +- Project
[cast(dept_id#x as int) AS dept_id#x, cast(dept_name#x as string) AS
dept_name#x, cast(state#x as string) AS state#x]\n +- Project
[dept_id#x, dept_name#x, state#x]\n +- SubqueryAlias DEPT\n
+- LocalRelation [dept_id#x, dept_name#x, state#x]\n"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 47,
+ "stopIndex" : 191,
+ "fragment" : "SELECT max(dept.dept_id) a\n FROM dept\n
WHERE dept.dept_id = emp.dept_id\n GROUP
BY state"
+ } ]
+}
-- !query
@@ -451,16 +460,23 @@ WHERE EXISTS (SELECT dept.dept_name
LIMIT 1
OFFSET 2)
-- !query schema
-struct<id:int,emp_name:string,hiredate:date,salary:double,dept_id:int>
+struct<>
-- !query output
-100 emp 1 2005-01-01 100.0 10
-100 emp 1 2005-01-01 100.0 10
-200 emp 2 2003-01-01 200.0 10
-300 emp 3 2002-01-01 300.0 20
-400 emp 4 2005-01-01 400.0 30
-600 emp 6 - no dept 2001-01-01 400.0 100
-700 emp 7 2010-01-01 400.0 100
-800 emp 8 2016-01-01 150.0 70
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter NOT (dept_id#x = outer(dept_id#x))\n+- SubqueryAlias
dept\n +- View (`DEPT`, [dept_id#x,dept_name#x,state#x])\n +- Project
[cast(dept_id#x as int) AS dept_id#x, cast(dept_name#x as string) AS
dept_name#x, cast(state#x as string) AS state#x]\n +- Project
[dept_id#x, dept_name#x, state#x]\n +- SubqueryAlias DEPT\n
+- LocalRelation [dept_id#x, dept_name#x, state#x]\n"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 36,
+ "stopIndex" : 133,
+ "fragment" : "SELECT dept.dept_name\n FROM dept\n
WHERE dept.dept_id <> emp.dept_id"
+ } ]
+}
-- !query
diff --git
a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-limit.sql.out
b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-limit.sql.out
index e688c44629eb..141180661261 100644
---
a/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-limit.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-limit.sql.out
@@ -304,14 +304,23 @@ WHERE t1b NOT IN (SELECT t2b
LIMIT 2
OFFSET 2)
-- !query schema
-struct<t1a:string,t1b:smallint,t1c:int,t1d:bigint,t1e:float,t1f:double,t1g:decimal(4,0),t1h:timestamp,t1i:date>
+struct<>
-- !query output
-val1a 16 12 10 15.0 20.0 2000 2014-07-04 01:01:00
2014-07-04
-val1a 16 12 21 15.0 20.0 2000 2014-06-04 01:02:00.001
2014-06-04
-val1a 6 8 10 15.0 20.0 2000 2014-04-04 01:00:00
2014-04-04
-val1a 6 8 10 15.0 20.0 2000 2014-04-04 01:02:00.001
2014-04-04
-val1d NULL 16 19 17.0 25.0 2600 2014-07-04 01:02:00.001
NULL
-val1d NULL 16 22 17.0 25.0 2600 2014-06-04 01:01:00
NULL
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (t2b#x = outer(t1b#x))\n+- SubqueryAlias t2\n +-
View (`t2`, [t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])\n +-
Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x,
cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as
float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS
t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]\n
+- Project [t2a#x, t2b [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 113,
+ "fragment" : "SELECT t2b\n FROM t2\n
WHERE t2b = t1b"
+ } ]
+}
-- !query
@@ -348,9 +357,23 @@ ORDER BY t1b NULLS last
LIMIT 1
OFFSET 1
-- !query schema
-struct<count(DISTINCT t1a):bigint,t1b:smallint>
+struct<>
-- !query output
-2 10
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (t2b#x > outer(t1b#x))\n+- SubqueryAlias t2\n +-
View (`t2`, [t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])\n +-
Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x,
cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as
float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS
t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]\n
+- Project [t2a#x, t2b [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 72,
+ "stopIndex" : 145,
+ "fragment" : "SELECT t2d\n FROM t2\n
WHERE t2b > t1b"
+ } ]
+}
-- !query
@@ -377,10 +400,23 @@ WHERE t1a IN (SELECT t2a
OFFSET 2)
OFFSET 2
-- !query schema
-struct<t1a:string,t1b:smallint,t1c:int,t1d:bigint,t1e:float,t1f:double,t1g:decimal(4,0),t1h:timestamp,t1i:date>
+struct<>
-- !query output
-val1e 10 NULL 19 17.0 25.0 2600 2014-05-04 01:01:00
2014-05-04
-val1e 10 NULL 19 17.0 25.0 2600 2014-09-04 01:02:00.001
2014-09-04
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (outer(t1d#xL) = t2d#xL)\n+- SubqueryAlias t2\n +-
View (`t2`, [t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])\n +-
Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x,
cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as
float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS
t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]\n
+- Project [t2a#x, t [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 35,
+ "stopIndex" : 101,
+ "fragment" : "SELECT t2a\n FROM t2\n WHERE
t1d = t2d"
+ } ]
+}
-- !query
@@ -408,9 +444,23 @@ WHERE t1c IN (SELECT t2c
OFFSET 2)
OFFSET 1
-- !query schema
-struct<t1a:string,t1b:smallint,t1c:int,t1d:bigint,t1e:float,t1f:double,t1g:decimal(4,0),t1h:timestamp,t1i:date>
+struct<>
-- !query output
-val1a 16 12 10 15.0 20.0 2000 2014-07-04 01:01:00
2014-07-04
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (t2b#x < outer(t1b#x))\n+- SubqueryAlias t2\n +-
View (`t2`, [t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])\n +-
Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x,
cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as
float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS
t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]\n
+- Project [t2a#x, t2b [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 35,
+ "stopIndex" : 101,
+ "fragment" : "SELECT t2c\n FROM t2\n WHERE
t2b < t1b"
+ } ]
+}
-- !query
@@ -455,9 +505,23 @@ WHERE t1b NOT IN (SELECT t2b
WHERE t2b < t1b
OFFSET 2)
-- !query schema
-struct<count(1):bigint>
+struct<>
-- !query output
-12
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (t2b#x < outer(t1b#x))\n+- SubqueryAlias t2\n +-
View (`t2`, [t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])\n +-
Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x,
cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as
float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS
t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]\n
+- Project [t2a#x, t2b [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 46,
+ "stopIndex" : 120,
+ "fragment" : "SELECT t2b\n FROM t2\n
WHERE t2b < t1b"
+ } ]
+}
-- !query
@@ -496,14 +560,23 @@ WHERE t1b NOT IN (SELECT t2b
LIMIT 2
OFFSET 2)
-- !query schema
-struct<t1a:string,t1b:smallint,t1c:int,t1d:bigint,t1e:float,t1f:double,t1g:decimal(4,0),t1h:timestamp,t1i:date>
+struct<>
-- !query output
-val1a 16 12 10 15.0 20.0 2000 2014-07-04 01:01:00
2014-07-04
-val1a 16 12 21 15.0 20.0 2000 2014-06-04 01:02:00.001
2014-06-04
-val1a 6 8 10 15.0 20.0 2000 2014-04-04 01:00:00
2014-04-04
-val1a 6 8 10 15.0 20.0 2000 2014-04-04 01:02:00.001
2014-04-04
-val1d NULL 16 19 17.0 25.0 2600 2014-07-04 01:02:00.001
NULL
-val1d NULL 16 22 17.0 25.0 2600 2014-06-04 01:01:00
NULL
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (t2b#x = outer(t1b#x))\n+- SubqueryAlias t2\n +-
View (`t2`, [t2a#x,t2b#x,t2c#x,t2d#xL,t2e#x,t2f#x,t2g#x,t2h#x,t2i#x])\n +-
Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x,
cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as
float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS
t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]\n
+- Project [t2a#x, t2b [...]
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 39,
+ "stopIndex" : 113,
+ "fragment" : "SELECT t2b\n FROM t2\n
WHERE t2b = t1b"
+ } ]
+}
-- !query
diff --git
a/sql/core/src/test/resources/sql-tests/results/subquery/subquery-offset.sql.out
b/sql/core/src/test/resources/sql-tests/results/subquery/subquery-offset.sql.out
new file mode 100644
index 000000000000..7736305b9cfa
--- /dev/null
+++
b/sql/core/src/test/resources/sql-tests/results/subquery/subquery-offset.sql.out
@@ -0,0 +1,254 @@
+-- Automatically generated by SQLQueryTestSuite
+-- !query
+create table x(x1 int, x2 int)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "NOT_SUPPORTED_COMMAND_WITHOUT_HIVE_SUPPORT",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "cmd" : "CREATE Hive TABLE (AS SELECT)"
+ }
+}
+
+
+-- !query
+insert into x values (1, 1), (2, 2)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "TABLE_OR_VIEW_NOT_FOUND",
+ "sqlState" : "42P01",
+ "messageParameters" : {
+ "relationName" : "`x`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 13,
+ "stopIndex" : 13,
+ "fragment" : "x"
+ } ]
+}
+
+
+-- !query
+create table y(y1 int, y2 int)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "NOT_SUPPORTED_COMMAND_WITHOUT_HIVE_SUPPORT",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "cmd" : "CREATE Hive TABLE (AS SELECT)"
+ }
+}
+
+
+-- !query
+insert into y values (1, 1), (1, 2), (2, 4)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "TABLE_OR_VIEW_NOT_FOUND",
+ "sqlState" : "42P01",
+ "messageParameters" : {
+ "relationName" : "`y`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 13,
+ "stopIndex" : 13,
+ "fragment" : "y"
+ } ]
+}
+
+
+-- !query
+select * from x where exists (select * from y where x1 = y1 limit 1 offset 2)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "TABLE_OR_VIEW_NOT_FOUND",
+ "sqlState" : "42P01",
+ "messageParameters" : {
+ "relationName" : "`x`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 15,
+ "fragment" : "x"
+ } ]
+}
+
+
+-- !query
+select * from x join lateral (select * from y where x1 = y1 limit 1 offset 2)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "TABLE_OR_VIEW_NOT_FOUND",
+ "sqlState" : "42P01",
+ "messageParameters" : {
+ "relationName" : "`x`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 15,
+ "fragment" : "x"
+ } ]
+}
+
+
+-- !query
+select * from x where x1 in (select y1 from y limit 1 offset 2)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "TABLE_OR_VIEW_NOT_FOUND",
+ "sqlState" : "42P01",
+ "messageParameters" : {
+ "relationName" : "`x`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 15,
+ "fragment" : "x"
+ } ]
+}
+
+
+-- !query
+select * from x where (select sum(y2) from y where x1 = y1 limit 1 offset 2) >
2
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "TABLE_OR_VIEW_NOT_FOUND",
+ "sqlState" : "42P01",
+ "messageParameters" : {
+ "relationName" : "`x`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 15,
+ "stopIndex" : 15,
+ "fragment" : "x"
+ } ]
+}
+
+
+-- !query
+CREATE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
+ (100, "emp 1", date "2005-01-01", 100.00D, 10),
+ (100, "emp 1", date "2005-01-01", 100.00D, 10),
+ (200, "emp 2", date "2003-01-01", 200.00D, 10),
+ (300, "emp 3", date "2002-01-01", 300.00D, 20),
+ (400, "emp 4", date "2005-01-01", 400.00D, 30),
+ (500, "emp 5", date "2001-01-01", 400.00D, NULL),
+ (600, "emp 6 - no dept", date "2001-01-01", 400.00D, 100),
+ (700, "emp 7", date "2010-01-01", 400.00D, 100),
+ (800, "emp 8", date "2016-01-01", 150.00D, 70)
+AS EMP(id, emp_name, hiredate, salary, dept_id)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+CREATE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
+ (10, "dept 1", "CA"),
+ (20, "dept 2", "NY"),
+ (30, "dept 3", "TX"),
+ (40, "dept 4 - unassigned", "OR"),
+ (50, "dept 5 - unassigned", "NJ"),
+ (70, "dept 7", "FL")
+AS DEPT(dept_id, dept_name, state)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+SELECT emp_name
+FROM emp
+WHERE EXISTS (SELECT max(dept.dept_id) a
+ FROM dept
+ WHERE dept.dept_id = emp.dept_id
+ GROUP BY state
+ ORDER BY state
+ LIMIT 2
+ OFFSET 1)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (dept_id#x = outer(dept_id#x))\n+- SubqueryAlias
dept\n +- View (`DEPT`, [dept_id#x,dept_name#x,state#x])\n +- Project
[cast(dept_id#x as int) AS dept_id#x, cast(dept_name#x as string) AS
dept_name#x, cast(state#x as string) AS state#x]\n +- Project
[dept_id#x, dept_name#x, state#x]\n +- SubqueryAlias DEPT\n
+- LocalRelation [dept_id#x, dept_name#x, state#x]\n"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 42,
+ "stopIndex" : 186,
+ "fragment" : "SELECT max(dept.dept_id) a\n FROM dept\n
WHERE dept.dept_id = emp.dept_id\n GROUP
BY state"
+ } ]
+}
+
+
+-- !query
+SELECT emp_name
+FROM emp
+JOIN LATERAL (SELECT max(dept.dept_id) a
+ FROM dept
+ WHERE dept.dept_id = emp.dept_id
+ GROUP BY state
+ ORDER BY state
+ LIMIT 2
+ OFFSET 1)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" :
"UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.ACCESSING_OUTER_QUERY_COLUMN_IS_NOT_ALLOWED",
+ "sqlState" : "0A000",
+ "messageParameters" : {
+ "treeNode" : "Filter (dept_id#x = outer(dept_id#x))\n+- SubqueryAlias
dept\n +- View (`DEPT`, [dept_id#x,dept_name#x,state#x])\n +- Project
[cast(dept_id#x as int) AS dept_id#x, cast(dept_name#x as string) AS
dept_name#x, cast(state#x as string) AS state#x]\n +- Project
[dept_id#x, dept_name#x, state#x]\n +- SubqueryAlias DEPT\n
+- LocalRelation [dept_id#x, dept_name#x, state#x]\n"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 42,
+ "stopIndex" : 186,
+ "fragment" : "SELECT max(dept.dept_id) a\n FROM dept\n
WHERE dept.dept_id = emp.dept_id\n GROUP
BY state"
+ } ]
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
