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 51af177c9ef0 [SPARK-49564][SQL] Add SQL pipe syntax for the JOIN
operator
51af177c9ef0 is described below
commit 51af177c9ef096b69995b7a9d550069f5a8b9e2e
Author: Daniel Tenedorio <[email protected]>
AuthorDate: Tue Oct 8 11:21:50 2024 +0800
[SPARK-49564][SQL] Add SQL pipe syntax for the JOIN operator
### What changes were proposed in this pull request?
This PR adds SQL pipe syntax support for the JOIN operator.
For example:
```
CREATE TEMPORARY VIEW join_test_t1
AS SELECT * FROM VALUES (1) AS grouping(a);
CREATE TEMPORARY VIEW join_test_empty_table
AS SELECT a FROM join_test_t1 WHERE FALSE;
TABLE join_test_t1
|> FULL OUTER JOIN join_test_empty_table
ON (join_test_t1.a = join_test_empty_table.a);
1 NULL
```
### Why are the changes needed?
The SQL pipe operator syntax will let users compose queries in a more
flexible fashion.
### Does this PR introduce _any_ user-facing change?
Yes, see above.
### How was this patch tested?
This PR adds a few unit test cases, but mostly relies on golden file test
coverage. I did this to make sure the answers are correct as this feature is
implemented and also so we can look at the analyzer output plans to ensure they
look right as well.
### Was this patch authored or co-authored using generative AI tooling?
No
Closes #48270 from dtenedor/pipe-join.
Authored-by: Daniel Tenedorio <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
---
.../spark/sql/catalyst/parser/SqlBaseParser.g4 | 1 +
.../spark/sql/catalyst/parser/AstBuilder.scala | 4 +-
.../analyzer-results/pipe-operators.sql.out | 721 ++++++++++++++++++++-
.../resources/sql-tests/inputs/pipe-operators.sql | 131 +++-
.../sql-tests/results/pipe-operators.sql.out | 431 +++++++++++-
.../spark/sql/execution/SparkSqlParserSuite.scala | 6 +
6 files changed, 1286 insertions(+), 8 deletions(-)
diff --git
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
index 644c7e732fbf..f0481a1a7073 100644
---
a/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
+++
b/sql/api/src/main/antlr4/org/apache/spark/sql/catalyst/parser/SqlBaseParser.g4
@@ -1510,6 +1510,7 @@ operatorPipeRightSide
| pivotClause unpivotClause?
| unpivotClause pivotClause?
| sample
+ | joinRelation
;
// When `SQL_standard_keyword_behavior=true`, there are 2 kinds of keywords in
Spark SQL.
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
index f1d211f51778..2b0443c01f6d 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
@@ -5916,7 +5916,9 @@ class AstBuilder extends DataTypeAstBuilder
withUnpivot(c, left)
}.getOrElse(Option(ctx.sample).map { c =>
withSample(c, left)
- }.get))))
+ }.getOrElse(Option(ctx.joinRelation()).map { c =>
+ withJoinRelation(c, left)
+ }.get)))))
}
/**
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/pipe-operators.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/pipe-operators.sql.out
index aee8da46aafb..4479c93f6e84 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/pipe-operators.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/pipe-operators.sql.out
@@ -130,6 +130,112 @@ CreateViewCommand `yearsWithComplexTypes`, select * from
values
+- LocalRelation [y#x, a#x, m#x, s#x]
+-- !query
+create temporary view join_test_t1 as select * from values (1) as grouping(a)
+-- !query analysis
+CreateViewCommand `join_test_t1`, select * from values (1) as grouping(a),
false, false, LocalTempView, UNSUPPORTED, true
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+create temporary view join_test_t2 as select * from values (1) as grouping(a)
+-- !query analysis
+CreateViewCommand `join_test_t2`, select * from values (1) as grouping(a),
false, false, LocalTempView, UNSUPPORTED, true
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+create temporary view join_test_t3 as select * from values (1) as grouping(a)
+-- !query analysis
+CreateViewCommand `join_test_t3`, select * from values (1) as grouping(a),
false, false, LocalTempView, UNSUPPORTED, true
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+create temporary view join_test_empty_table as select a from join_test_t2
where false
+-- !query analysis
+CreateViewCommand `join_test_empty_table`, select a from join_test_t2 where
false, false, false, LocalTempView, UNSUPPORTED, true
+ +- Project [a#x]
+ +- Filter false
+ +- SubqueryAlias join_test_t2
+ +- View (`join_test_t2`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+create temporary view lateral_test_t1(c1, c2)
+ as values (0, 1), (1, 2)
+-- !query analysis
+CreateViewCommand `lateral_test_t1`, [(c1,None), (c2,None)], values (0, 1),
(1, 2), false, false, LocalTempView, UNSUPPORTED, true
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+create temporary view lateral_test_t2(c1, c2)
+ as values (0, 2), (0, 3)
+-- !query analysis
+CreateViewCommand `lateral_test_t2`, [(c1,None), (c2,None)], values (0, 2),
(0, 3), false, false, LocalTempView, UNSUPPORTED, true
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+create temporary view lateral_test_t3(c1, c2)
+ as values (0, array(0, 1)), (1, array(2)), (2, array()), (null, array(4))
+-- !query analysis
+CreateViewCommand `lateral_test_t3`, [(c1,None), (c2,None)], values (0,
array(0, 1)), (1, array(2)), (2, array()), (null, array(4)), false, false,
LocalTempView, UNSUPPORTED, true
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+create temporary view lateral_test_t4(c1, c2)
+ as values (0, 1), (0, 2), (1, 1), (1, 3)
+-- !query analysis
+CreateViewCommand `lateral_test_t4`, [(c1,None), (c2,None)], values (0, 1),
(0, 2), (1, 1), (1, 3), false, false, LocalTempView, UNSUPPORTED, true
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+create temporary view natural_join_test_t1 as select * from values
+ ("one", 1), ("two", 2), ("three", 3) as natural_join_test_t1(k, v1)
+-- !query analysis
+CreateViewCommand `natural_join_test_t1`, select * from values
+ ("one", 1), ("two", 2), ("three", 3) as natural_join_test_t1(k, v1), false,
false, LocalTempView, UNSUPPORTED, true
+ +- Project [k#x, v1#x]
+ +- SubqueryAlias natural_join_test_t1
+ +- LocalRelation [k#x, v1#x]
+
+
+-- !query
+create temporary view natural_join_test_t2 as select * from values
+ ("one", 1), ("two", 22), ("one", 5) as natural_join_test_t2(k, v2)
+-- !query analysis
+CreateViewCommand `natural_join_test_t2`, select * from values
+ ("one", 1), ("two", 22), ("one", 5) as natural_join_test_t2(k, v2), false,
false, LocalTempView, UNSUPPORTED, true
+ +- Project [k#x, v2#x]
+ +- SubqueryAlias natural_join_test_t2
+ +- LocalRelation [k#x, v2#x]
+
+
+-- !query
+create temporary view natural_join_test_t3 as select * from values
+ ("one", 4), ("two", 5), ("one", 6) as natural_join_test_t3(k, v3)
+-- !query analysis
+CreateViewCommand `natural_join_test_t3`, select * from values
+ ("one", 4), ("two", 5), ("one", 6) as natural_join_test_t3(k, v3), false,
false, LocalTempView, UNSUPPORTED, true
+ +- Project [k#x, v3#x]
+ +- SubqueryAlias natural_join_test_t3
+ +- LocalRelation [k#x, v3#x]
+
+
-- !query
table t
|> select 1 as x
@@ -982,7 +1088,7 @@ org.apache.spark.sql.catalyst.parser.ParseException
-- !query
table t
-|> tablesample (-100 percent)
+|> tablesample (-100 percent) repeatable (0)
-- !query analysis
org.apache.spark.sql.catalyst.parser.ParseException
{
@@ -994,8 +1100,8 @@ org.apache.spark.sql.catalyst.parser.ParseException
"objectType" : "",
"objectName" : "",
"startIndex" : 12,
- "stopIndex" : 37,
- "fragment" : "tablesample (-100 percent)"
+ "stopIndex" : 52,
+ "fragment" : "tablesample (-100 percent) repeatable (0)"
} ]
}
@@ -1105,6 +1211,615 @@ org.apache.spark.sql.catalyst.parser.ParseException
}
+-- !query
+table join_test_t1
+|> inner join join_test_empty_table
+-- !query analysis
+Join Inner
+:- SubqueryAlias join_test_t1
+: +- View (`join_test_t1`, [a#x])
+: +- Project [cast(a#x as int) AS a#x]
+: +- Project [a#x]
+: +- SubqueryAlias grouping
+: +- LocalRelation [a#x]
++- SubqueryAlias join_test_empty_table
+ +- View (`join_test_empty_table`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- Filter false
+ +- SubqueryAlias join_test_t2
+ +- View (`join_test_t2`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+table join_test_t1
+|> cross join join_test_empty_table
+-- !query analysis
+Join Cross
+:- SubqueryAlias join_test_t1
+: +- View (`join_test_t1`, [a#x])
+: +- Project [cast(a#x as int) AS a#x]
+: +- Project [a#x]
+: +- SubqueryAlias grouping
+: +- LocalRelation [a#x]
++- SubqueryAlias join_test_empty_table
+ +- View (`join_test_empty_table`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- Filter false
+ +- SubqueryAlias join_test_t2
+ +- View (`join_test_t2`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+table join_test_t1
+|> left outer join join_test_empty_table
+-- !query analysis
+Join LeftOuter
+:- SubqueryAlias join_test_t1
+: +- View (`join_test_t1`, [a#x])
+: +- Project [cast(a#x as int) AS a#x]
+: +- Project [a#x]
+: +- SubqueryAlias grouping
+: +- LocalRelation [a#x]
++- SubqueryAlias join_test_empty_table
+ +- View (`join_test_empty_table`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- Filter false
+ +- SubqueryAlias join_test_t2
+ +- View (`join_test_t2`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+table join_test_t1
+|> right outer join join_test_empty_table
+-- !query analysis
+Join RightOuter
+:- SubqueryAlias join_test_t1
+: +- View (`join_test_t1`, [a#x])
+: +- Project [cast(a#x as int) AS a#x]
+: +- Project [a#x]
+: +- SubqueryAlias grouping
+: +- LocalRelation [a#x]
++- SubqueryAlias join_test_empty_table
+ +- View (`join_test_empty_table`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- Filter false
+ +- SubqueryAlias join_test_t2
+ +- View (`join_test_t2`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+table join_test_t1
+|> full outer join join_test_empty_table using (a)
+-- !query analysis
+Project [coalesce(a#x, a#x) AS a#x]
++- Join FullOuter, (a#x = a#x)
+ :- SubqueryAlias join_test_t1
+ : +- View (`join_test_t1`, [a#x])
+ : +- Project [cast(a#x as int) AS a#x]
+ : +- Project [a#x]
+ : +- SubqueryAlias grouping
+ : +- LocalRelation [a#x]
+ +- SubqueryAlias join_test_empty_table
+ +- View (`join_test_empty_table`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- Filter false
+ +- SubqueryAlias join_test_t2
+ +- View (`join_test_t2`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+table join_test_t1
+|> full outer join join_test_empty_table on (join_test_t1.a =
join_test_empty_table.a)
+-- !query analysis
+Join FullOuter, (a#x = a#x)
+:- SubqueryAlias join_test_t1
+: +- View (`join_test_t1`, [a#x])
+: +- Project [cast(a#x as int) AS a#x]
+: +- Project [a#x]
+: +- SubqueryAlias grouping
+: +- LocalRelation [a#x]
++- SubqueryAlias join_test_empty_table
+ +- View (`join_test_empty_table`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- Filter false
+ +- SubqueryAlias join_test_t2
+ +- View (`join_test_t2`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+table join_test_t1
+|> left semi join join_test_empty_table
+-- !query analysis
+Join LeftSemi
+:- SubqueryAlias join_test_t1
+: +- View (`join_test_t1`, [a#x])
+: +- Project [cast(a#x as int) AS a#x]
+: +- Project [a#x]
+: +- SubqueryAlias grouping
+: +- LocalRelation [a#x]
++- SubqueryAlias join_test_empty_table
+ +- View (`join_test_empty_table`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- Filter false
+ +- SubqueryAlias join_test_t2
+ +- View (`join_test_t2`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+table join_test_t1
+|> left anti join join_test_empty_table
+-- !query analysis
+Join LeftAnti
+:- SubqueryAlias join_test_t1
+: +- View (`join_test_t1`, [a#x])
+: +- Project [cast(a#x as int) AS a#x]
+: +- Project [a#x]
+: +- SubqueryAlias grouping
+: +- LocalRelation [a#x]
++- SubqueryAlias join_test_empty_table
+ +- View (`join_test_empty_table`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- Filter false
+ +- SubqueryAlias join_test_t2
+ +- View (`join_test_t2`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+select * from join_test_t1 where true
+|> inner join join_test_empty_table
+-- !query analysis
+Join Inner
+:- Project [a#x]
+: +- Filter true
+: +- SubqueryAlias join_test_t1
+: +- View (`join_test_t1`, [a#x])
+: +- Project [cast(a#x as int) AS a#x]
+: +- Project [a#x]
+: +- SubqueryAlias grouping
+: +- LocalRelation [a#x]
++- SubqueryAlias join_test_empty_table
+ +- View (`join_test_empty_table`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- Filter false
+ +- SubqueryAlias join_test_t2
+ +- View (`join_test_t2`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+select 1 as x, 2 as y
+|> inner join (select 1 as x, 4 as y) using (x)
+-- !query analysis
+Project [x#x, y#x, y#x]
++- Join Inner, (x#x = x#x)
+ :- Project [1 AS x#x, 2 AS y#x]
+ : +- OneRowRelation
+ +- SubqueryAlias __auto_generated_subquery_name
+ +- Project [1 AS x#x, 4 AS y#x]
+ +- OneRowRelation
+
+
+-- !query
+table join_test_t1
+|> inner join (join_test_t2 jt2 inner join join_test_t3 jt3 using (a)) using
(a)
+|> select a, join_test_t1.a, jt2.a, jt3.a
+-- !query analysis
+Project [a#x, a#x, a#x, a#x]
++- Project [a#x, a#x, a#x]
+ +- Join Inner, (a#x = a#x)
+ :- SubqueryAlias join_test_t1
+ : +- View (`join_test_t1`, [a#x])
+ : +- Project [cast(a#x as int) AS a#x]
+ : +- Project [a#x]
+ : +- SubqueryAlias grouping
+ : +- LocalRelation [a#x]
+ +- Project [a#x, a#x]
+ +- Join Inner, (a#x = a#x)
+ :- SubqueryAlias jt2
+ : +- SubqueryAlias join_test_t2
+ : +- View (`join_test_t2`, [a#x])
+ : +- Project [cast(a#x as int) AS a#x]
+ : +- Project [a#x]
+ : +- SubqueryAlias grouping
+ : +- LocalRelation [a#x]
+ +- SubqueryAlias jt3
+ +- SubqueryAlias join_test_t3
+ +- View (`join_test_t3`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+table join_test_t1
+|> inner join join_test_t2 tablesample (100 percent) repeatable (0) jt2 using
(a)
+-- !query analysis
+Project [a#x]
++- Join Inner, (a#x = a#x)
+ :- SubqueryAlias join_test_t1
+ : +- View (`join_test_t1`, [a#x])
+ : +- Project [cast(a#x as int) AS a#x]
+ : +- Project [a#x]
+ : +- SubqueryAlias grouping
+ : +- LocalRelation [a#x]
+ +- Sample 0.0, 1.0, false, 0
+ +- SubqueryAlias jt2
+ +- SubqueryAlias join_test_t2
+ +- View (`join_test_t2`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+table join_test_t1
+|> inner join (select 1 as a) tablesample (100 percent) repeatable (0) jt2
using (a)
+-- !query analysis
+Project [a#x]
++- Join Inner, (a#x = a#x)
+ :- SubqueryAlias join_test_t1
+ : +- View (`join_test_t1`, [a#x])
+ : +- Project [cast(a#x as int) AS a#x]
+ : +- Project [a#x]
+ : +- SubqueryAlias grouping
+ : +- LocalRelation [a#x]
+ +- SubqueryAlias jt2
+ +- Sample 0.0, 1.0, false, 0
+ +- Project [1 AS a#x]
+ +- OneRowRelation
+
+
+-- !query
+table join_test_t1
+|> join join_test_t1 using (a)
+-- !query analysis
+Project [a#x]
++- Join Inner, (a#x = a#x)
+ :- SubqueryAlias join_test_t1
+ : +- View (`join_test_t1`, [a#x])
+ : +- Project [cast(a#x as int) AS a#x]
+ : +- Project [a#x]
+ : +- SubqueryAlias grouping
+ : +- LocalRelation [a#x]
+ +- SubqueryAlias join_test_t1
+ +- View (`join_test_t1`, [a#x])
+ +- Project [cast(a#x as int) AS a#x]
+ +- Project [a#x]
+ +- SubqueryAlias grouping
+ +- LocalRelation [a#x]
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select c1)
+-- !query analysis
+LateralJoin lateral-subquery#x [c1#x], Inner
+: +- SubqueryAlias __auto_generated_subquery_name
+: +- Project [outer(c1#x) AS c1#x]
+: +- OneRowRelation
++- SubqueryAlias lateral_test_t1
+ +- View (`lateral_test_t1`, [c1#x, c2#x])
+ +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select c1 from lateral_test_t2)
+-- !query analysis
+LateralJoin lateral-subquery#x [], Inner
+: +- SubqueryAlias __auto_generated_subquery_name
+: +- Project [c1#x]
+: +- SubqueryAlias lateral_test_t2
+: +- View (`lateral_test_t2`, [c1#x, c2#x])
+: +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS
c2#x]
+: +- LocalRelation [col1#x, col2#x]
++- SubqueryAlias lateral_test_t1
+ +- View (`lateral_test_t1`, [c1#x, c2#x])
+ +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select lateral_test_t1.c1 from lateral_test_t2)
+-- !query analysis
+LateralJoin lateral-subquery#x [c1#x], Inner
+: +- SubqueryAlias __auto_generated_subquery_name
+: +- Project [outer(c1#x) AS c1#x]
+: +- SubqueryAlias lateral_test_t2
+: +- View (`lateral_test_t2`, [c1#x, c2#x])
+: +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS
c2#x]
+: +- LocalRelation [col1#x, col2#x]
++- SubqueryAlias lateral_test_t1
+ +- View (`lateral_test_t1`, [c1#x, c2#x])
+ +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select lateral_test_t1.c1 + t2.c1 from lateral_test_t2 t2)
+-- !query analysis
+LateralJoin lateral-subquery#x [c1#x], Inner
+: +- SubqueryAlias __auto_generated_subquery_name
+: +- Project [(outer(c1#x) + c1#x) AS (outer(lateral_test_t1.c1) + c1)#x]
+: +- SubqueryAlias t2
+: +- SubqueryAlias lateral_test_t2
+: +- View (`lateral_test_t2`, [c1#x, c2#x])
+: +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int)
AS c2#x]
+: +- LocalRelation [col1#x, col2#x]
++- SubqueryAlias lateral_test_t1
+ +- View (`lateral_test_t1`, [c1#x, c2#x])
+ +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select *)
+-- !query analysis
+LateralJoin lateral-subquery#x [], Inner
+: +- SubqueryAlias __auto_generated_subquery_name
+: +- Project
+: +- OneRowRelation
++- SubqueryAlias lateral_test_t1
+ +- View (`lateral_test_t1`, [c1#x, c2#x])
+ +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select * from lateral_test_t2)
+-- !query analysis
+LateralJoin lateral-subquery#x [], Inner
+: +- SubqueryAlias __auto_generated_subquery_name
+: +- Project [c1#x, c2#x]
+: +- SubqueryAlias lateral_test_t2
+: +- View (`lateral_test_t2`, [c1#x, c2#x])
+: +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS
c2#x]
+: +- LocalRelation [col1#x, col2#x]
++- SubqueryAlias lateral_test_t1
+ +- View (`lateral_test_t1`, [c1#x, c2#x])
+ +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select lateral_test_t1.* from lateral_test_t2)
+-- !query analysis
+LateralJoin lateral-subquery#x [c1#x && c2#x], Inner
+: +- SubqueryAlias __auto_generated_subquery_name
+: +- Project [outer(c1#x) AS c1#x, outer(c2#x) AS c2#x]
+: +- SubqueryAlias lateral_test_t2
+: +- View (`lateral_test_t2`, [c1#x, c2#x])
+: +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS
c2#x]
+: +- LocalRelation [col1#x, col2#x]
++- SubqueryAlias lateral_test_t1
+ +- View (`lateral_test_t1`, [c1#x, c2#x])
+ +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select lateral_test_t1.*, t2.* from lateral_test_t2 t2)
+-- !query analysis
+LateralJoin lateral-subquery#x [c1#x && c2#x], Inner
+: +- SubqueryAlias __auto_generated_subquery_name
+: +- Project [outer(c1#x) AS c1#x, outer(c2#x) AS c2#x, c1#x, c2#x]
+: +- SubqueryAlias t2
+: +- SubqueryAlias lateral_test_t2
+: +- View (`lateral_test_t2`, [c1#x, c2#x])
+: +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int)
AS c2#x]
+: +- LocalRelation [col1#x, col2#x]
++- SubqueryAlias lateral_test_t1
+ +- View (`lateral_test_t1`, [c1#x, c2#x])
+ +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+table lateral_test_t1
+|> join lateral_test_t2
+|> join lateral (select lateral_test_t1.c2 + lateral_test_t2.c2)
+-- !query analysis
+LateralJoin lateral-subquery#x [c2#x && c2#x], Inner
+: +- SubqueryAlias __auto_generated_subquery_name
+: +- Project [(outer(c2#x) + outer(c2#x)) AS (outer(lateral_test_t1.c2) +
outer(lateral_test_t2.c2))#x]
+: +- OneRowRelation
++- Join Inner
+ :- SubqueryAlias lateral_test_t1
+ : +- View (`lateral_test_t1`, [c1#x, c2#x])
+ : +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x]
+ : +- LocalRelation [col1#x, col2#x]
+ +- SubqueryAlias lateral_test_t2
+ +- View (`lateral_test_t2`, [c1#x, c2#x])
+ +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x]
+ +- LocalRelation [col1#x, col2#x]
+
+
+-- !query
+table natural_join_test_t1
+|> natural join natural_join_test_t2
+|> where k = "one"
+-- !query analysis
+Filter (k#x = one)
++- SubqueryAlias __auto_generated_subquery_name
+ +- Project [k#x, v1#x, v2#x]
+ +- Join Inner, (k#x = k#x)
+ :- SubqueryAlias natural_join_test_t1
+ : +- View (`natural_join_test_t1`, [k#x, v1#x])
+ : +- Project [cast(k#x as string) AS k#x, cast(v1#x as int) AS
v1#x]
+ : +- Project [k#x, v1#x]
+ : +- SubqueryAlias natural_join_test_t1
+ : +- LocalRelation [k#x, v1#x]
+ +- SubqueryAlias natural_join_test_t2
+ +- View (`natural_join_test_t2`, [k#x, v2#x])
+ +- Project [cast(k#x as string) AS k#x, cast(v2#x as int) AS
v2#x]
+ +- Project [k#x, v2#x]
+ +- SubqueryAlias natural_join_test_t2
+ +- LocalRelation [k#x, v2#x]
+
+
+-- !query
+table natural_join_test_t1
+|> natural join natural_join_test_t2 nt2
+|> select natural_join_test_t1.*
+-- !query analysis
+Project [k#x, v1#x]
++- Project [k#x, v1#x, v2#x]
+ +- Join Inner, (k#x = k#x)
+ :- SubqueryAlias natural_join_test_t1
+ : +- View (`natural_join_test_t1`, [k#x, v1#x])
+ : +- Project [cast(k#x as string) AS k#x, cast(v1#x as int) AS v1#x]
+ : +- Project [k#x, v1#x]
+ : +- SubqueryAlias natural_join_test_t1
+ : +- LocalRelation [k#x, v1#x]
+ +- SubqueryAlias nt2
+ +- SubqueryAlias natural_join_test_t2
+ +- View (`natural_join_test_t2`, [k#x, v2#x])
+ +- Project [cast(k#x as string) AS k#x, cast(v2#x as int) AS
v2#x]
+ +- Project [k#x, v2#x]
+ +- SubqueryAlias natural_join_test_t2
+ +- LocalRelation [k#x, v2#x]
+
+
+-- !query
+table natural_join_test_t1
+|> natural join natural_join_test_t2 nt2
+|> natural join natural_join_test_t3 nt3
+|> select natural_join_test_t1.*, nt2.*, nt3.*
+-- !query analysis
+Project [k#x, v1#x, k#x, v2#x, k#x, v3#x]
++- Project [k#x, v1#x, v2#x, v3#x, k#x, k#x]
+ +- Join Inner, (k#x = k#x)
+ :- Project [k#x, v1#x, v2#x, k#x]
+ : +- Join Inner, (k#x = k#x)
+ : :- SubqueryAlias natural_join_test_t1
+ : : +- View (`natural_join_test_t1`, [k#x, v1#x])
+ : : +- Project [cast(k#x as string) AS k#x, cast(v1#x as int) AS
v1#x]
+ : : +- Project [k#x, v1#x]
+ : : +- SubqueryAlias natural_join_test_t1
+ : : +- LocalRelation [k#x, v1#x]
+ : +- SubqueryAlias nt2
+ : +- SubqueryAlias natural_join_test_t2
+ : +- View (`natural_join_test_t2`, [k#x, v2#x])
+ : +- Project [cast(k#x as string) AS k#x, cast(v2#x as int)
AS v2#x]
+ : +- Project [k#x, v2#x]
+ : +- SubqueryAlias natural_join_test_t2
+ : +- LocalRelation [k#x, v2#x]
+ +- SubqueryAlias nt3
+ +- SubqueryAlias natural_join_test_t3
+ +- View (`natural_join_test_t3`, [k#x, v3#x])
+ +- Project [cast(k#x as string) AS k#x, cast(v3#x as int) AS
v3#x]
+ +- Project [k#x, v3#x]
+ +- SubqueryAlias natural_join_test_t3
+ +- LocalRelation [k#x, v3#x]
+
+
+-- !query
+table join_test_t1
+|> inner join join_test_empty_table
+ inner join join_test_empty_table
+-- !query analysis
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "PARSE_SYNTAX_ERROR",
+ "sqlState" : "42601",
+ "messageParameters" : {
+ "error" : "'inner'",
+ "hint" : ""
+ }
+}
+
+
+-- !query
+table join_test_t1
+|> select 1 + 2 as result
+|> full outer join join_test_empty_table on (join_test_t1.a =
join_test_empty_table.a)
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNRESOLVED_COLUMN.WITH_SUGGESTION",
+ "sqlState" : "42703",
+ "messageParameters" : {
+ "objectName" : "`join_test_t1`.`a`",
+ "proposal" : "`result`, `join_test_empty_table`.`a`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 91,
+ "stopIndex" : 104,
+ "fragment" : "join_test_t1.a"
+ } ]
+}
+
+
+-- !query
+table join_test_t1 jt
+|> cross join (select * from jt)
+-- !query analysis
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "PARSE_SYNTAX_ERROR",
+ "sqlState" : "42601",
+ "messageParameters" : {
+ "error" : "'jt'",
+ "hint" : ""
+ }
+}
+
+
-- !query
drop table t
-- !query analysis
diff --git a/sql/core/src/test/resources/sql-tests/inputs/pipe-operators.sql
b/sql/core/src/test/resources/sql-tests/inputs/pipe-operators.sql
index 31748fe1125a..1f8450e3507c 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/pipe-operators.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/pipe-operators.sql
@@ -36,6 +36,29 @@ create temporary view yearsWithComplexTypes as select * from
values
(2013, array(2, 2), map('2', 2), struct(2, 'b'))
as yearsWithComplexTypes(y, a, m, s);
+create temporary view join_test_t1 as select * from values (1) as grouping(a);
+create temporary view join_test_t2 as select * from values (1) as grouping(a);
+create temporary view join_test_t3 as select * from values (1) as grouping(a);
+create temporary view join_test_empty_table as select a from join_test_t2
where false;
+
+create temporary view lateral_test_t1(c1, c2)
+ as values (0, 1), (1, 2);
+create temporary view lateral_test_t2(c1, c2)
+ as values (0, 2), (0, 3);
+create temporary view lateral_test_t3(c1, c2)
+ as values (0, array(0, 1)), (1, array(2)), (2, array()), (null, array(4));
+create temporary view lateral_test_t4(c1, c2)
+ as values (0, 1), (0, 2), (1, 1), (1, 3);
+
+create temporary view natural_join_test_t1 as select * from values
+ ("one", 1), ("two", 2), ("three", 3) as natural_join_test_t1(k, v1);
+
+create temporary view natural_join_test_t2 as select * from values
+ ("one", 1), ("two", 22), ("one", 5) as natural_join_test_t2(k, v2);
+
+create temporary view natural_join_test_t3 as select * from values
+ ("one", 4), ("two", 5), ("one", 6) as natural_join_test_t3(k, v3);
+
-- SELECT operators: positive tests.
---------------------------------------
@@ -354,7 +377,7 @@ table t
-- Negative sampling options are not supported.
table t
-|> tablesample (-100 percent);
+|> tablesample (-100 percent) repeatable (0);
table t
|> tablesample (-5 rows);
@@ -375,6 +398,112 @@ table t
table t
|> tablesample (200) repeatable (0);
+-- JOIN operators: positive tests.
+----------------------------------
+
+table join_test_t1
+|> inner join join_test_empty_table;
+
+table join_test_t1
+|> cross join join_test_empty_table;
+
+table join_test_t1
+|> left outer join join_test_empty_table;
+
+table join_test_t1
+|> right outer join join_test_empty_table;
+
+table join_test_t1
+|> full outer join join_test_empty_table using (a);
+
+table join_test_t1
+|> full outer join join_test_empty_table on (join_test_t1.a =
join_test_empty_table.a);
+
+table join_test_t1
+|> left semi join join_test_empty_table;
+
+table join_test_t1
+|> left anti join join_test_empty_table;
+
+select * from join_test_t1 where true
+|> inner join join_test_empty_table;
+
+select 1 as x, 2 as y
+|> inner join (select 1 as x, 4 as y) using (x);
+
+table join_test_t1
+|> inner join (join_test_t2 jt2 inner join join_test_t3 jt3 using (a)) using
(a)
+|> select a, join_test_t1.a, jt2.a, jt3.a;
+
+table join_test_t1
+|> inner join join_test_t2 tablesample (100 percent) repeatable (0) jt2 using
(a);
+
+table join_test_t1
+|> inner join (select 1 as a) tablesample (100 percent) repeatable (0) jt2
using (a);
+
+table join_test_t1
+|> join join_test_t1 using (a);
+
+-- Lateral joins.
+table lateral_test_t1
+|> join lateral (select c1);
+
+table lateral_test_t1
+|> join lateral (select c1 from lateral_test_t2);
+
+table lateral_test_t1
+|> join lateral (select lateral_test_t1.c1 from lateral_test_t2);
+
+table lateral_test_t1
+|> join lateral (select lateral_test_t1.c1 + t2.c1 from lateral_test_t2 t2);
+
+table lateral_test_t1
+|> join lateral (select *);
+
+table lateral_test_t1
+|> join lateral (select * from lateral_test_t2);
+
+table lateral_test_t1
+|> join lateral (select lateral_test_t1.* from lateral_test_t2);
+
+table lateral_test_t1
+|> join lateral (select lateral_test_t1.*, t2.* from lateral_test_t2 t2);
+
+table lateral_test_t1
+|> join lateral_test_t2
+|> join lateral (select lateral_test_t1.c2 + lateral_test_t2.c2);
+
+-- Natural joins.
+table natural_join_test_t1
+|> natural join natural_join_test_t2
+|> where k = "one";
+
+table natural_join_test_t1
+|> natural join natural_join_test_t2 nt2
+|> select natural_join_test_t1.*;
+
+table natural_join_test_t1
+|> natural join natural_join_test_t2 nt2
+|> natural join natural_join_test_t3 nt3
+|> select natural_join_test_t1.*, nt2.*, nt3.*;
+
+-- JOIN operators: negative tests.
+----------------------------------
+
+-- Multiple joins within the same pipe operator are not supported without
parentheses.
+table join_test_t1
+|> inner join join_test_empty_table
+ inner join join_test_empty_table;
+
+-- The join pipe operator can only refer to column names from the previous
relation.
+table join_test_t1
+|> select 1 + 2 as result
+|> full outer join join_test_empty_table on (join_test_t1.a =
join_test_empty_table.a);
+
+-- The table from the pipe input is not visible as a table name in the right
side.
+table join_test_t1 jt
+|> cross join (select * from jt);
+
-- Cleanup.
-----------
drop table t;
diff --git
a/sql/core/src/test/resources/sql-tests/results/pipe-operators.sql.out
b/sql/core/src/test/resources/sql-tests/results/pipe-operators.sql.out
index 78b610b0d97c..a54e66e53f0f 100644
--- a/sql/core/src/test/resources/sql-tests/results/pipe-operators.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/pipe-operators.sql.out
@@ -119,6 +119,101 @@ struct<>
+-- !query
+create temporary view join_test_t1 as select * from values (1) as grouping(a)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+create temporary view join_test_t2 as select * from values (1) as grouping(a)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+create temporary view join_test_t3 as select * from values (1) as grouping(a)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+create temporary view join_test_empty_table as select a from join_test_t2
where false
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+create temporary view lateral_test_t1(c1, c2)
+ as values (0, 1), (1, 2)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+create temporary view lateral_test_t2(c1, c2)
+ as values (0, 2), (0, 3)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+create temporary view lateral_test_t3(c1, c2)
+ as values (0, array(0, 1)), (1, array(2)), (2, array()), (null, array(4))
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+create temporary view lateral_test_t4(c1, c2)
+ as values (0, 1), (0, 2), (1, 1), (1, 3)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+create temporary view natural_join_test_t1 as select * from values
+ ("one", 1), ("two", 2), ("three", 3) as natural_join_test_t1(k, v1)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+create temporary view natural_join_test_t2 as select * from values
+ ("one", 1), ("two", 22), ("one", 5) as natural_join_test_t2(k, v2)
+-- !query schema
+struct<>
+-- !query output
+
+
+
+-- !query
+create temporary view natural_join_test_t3 as select * from values
+ ("one", 4), ("two", 5), ("one", 6) as natural_join_test_t3(k, v3)
+-- !query schema
+struct<>
+-- !query output
+
+
+
-- !query
table t
|> select 1 as x
@@ -924,7 +1019,7 @@ org.apache.spark.sql.catalyst.parser.ParseException
-- !query
table t
-|> tablesample (-100 percent)
+|> tablesample (-100 percent) repeatable (0)
-- !query schema
struct<>
-- !query output
@@ -938,8 +1033,8 @@ org.apache.spark.sql.catalyst.parser.ParseException
"objectType" : "",
"objectName" : "",
"startIndex" : 12,
- "stopIndex" : 37,
- "fragment" : "tablesample (-100 percent)"
+ "stopIndex" : 52,
+ "fragment" : "tablesample (-100 percent) repeatable (0)"
} ]
}
@@ -1059,6 +1154,336 @@ org.apache.spark.sql.catalyst.parser.ParseException
}
+-- !query
+table join_test_t1
+|> inner join join_test_empty_table
+-- !query schema
+struct<a:int,a:int>
+-- !query output
+
+
+
+-- !query
+table join_test_t1
+|> cross join join_test_empty_table
+-- !query schema
+struct<a:int,a:int>
+-- !query output
+
+
+
+-- !query
+table join_test_t1
+|> left outer join join_test_empty_table
+-- !query schema
+struct<a:int,a:int>
+-- !query output
+1 NULL
+
+
+-- !query
+table join_test_t1
+|> right outer join join_test_empty_table
+-- !query schema
+struct<a:int,a:int>
+-- !query output
+
+
+
+-- !query
+table join_test_t1
+|> full outer join join_test_empty_table using (a)
+-- !query schema
+struct<a:int>
+-- !query output
+1
+
+
+-- !query
+table join_test_t1
+|> full outer join join_test_empty_table on (join_test_t1.a =
join_test_empty_table.a)
+-- !query schema
+struct<a:int,a:int>
+-- !query output
+1 NULL
+
+
+-- !query
+table join_test_t1
+|> left semi join join_test_empty_table
+-- !query schema
+struct<a:int>
+-- !query output
+
+
+
+-- !query
+table join_test_t1
+|> left anti join join_test_empty_table
+-- !query schema
+struct<a:int>
+-- !query output
+1
+
+
+-- !query
+select * from join_test_t1 where true
+|> inner join join_test_empty_table
+-- !query schema
+struct<a:int,a:int>
+-- !query output
+
+
+
+-- !query
+select 1 as x, 2 as y
+|> inner join (select 1 as x, 4 as y) using (x)
+-- !query schema
+struct<x:int,y:int,y:int>
+-- !query output
+1 2 4
+
+
+-- !query
+table join_test_t1
+|> inner join (join_test_t2 jt2 inner join join_test_t3 jt3 using (a)) using
(a)
+|> select a, join_test_t1.a, jt2.a, jt3.a
+-- !query schema
+struct<a:int,a:int,a:int,a:int>
+-- !query output
+1 1 1 1
+
+
+-- !query
+table join_test_t1
+|> inner join join_test_t2 tablesample (100 percent) repeatable (0) jt2 using
(a)
+-- !query schema
+struct<a:int>
+-- !query output
+1
+
+
+-- !query
+table join_test_t1
+|> inner join (select 1 as a) tablesample (100 percent) repeatable (0) jt2
using (a)
+-- !query schema
+struct<a:int>
+-- !query output
+1
+
+
+-- !query
+table join_test_t1
+|> join join_test_t1 using (a)
+-- !query schema
+struct<a:int>
+-- !query output
+1
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select c1)
+-- !query schema
+struct<c1:int,c2:int,c1:int>
+-- !query output
+0 1 0
+1 2 1
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select c1 from lateral_test_t2)
+-- !query schema
+struct<c1:int,c2:int,c1:int>
+-- !query output
+0 1 0
+0 1 0
+1 2 0
+1 2 0
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select lateral_test_t1.c1 from lateral_test_t2)
+-- !query schema
+struct<c1:int,c2:int,c1:int>
+-- !query output
+0 1 0
+0 1 0
+1 2 1
+1 2 1
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select lateral_test_t1.c1 + t2.c1 from lateral_test_t2 t2)
+-- !query schema
+struct<c1:int,c2:int,(outer(lateral_test_t1.c1) + c1):int>
+-- !query output
+0 1 0
+0 1 0
+1 2 1
+1 2 1
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select *)
+-- !query schema
+struct<c1:int,c2:int>
+-- !query output
+0 1
+1 2
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select * from lateral_test_t2)
+-- !query schema
+struct<c1:int,c2:int,c1:int,c2:int>
+-- !query output
+0 1 0 2
+0 1 0 3
+1 2 0 2
+1 2 0 3
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select lateral_test_t1.* from lateral_test_t2)
+-- !query schema
+struct<c1:int,c2:int,c1:int,c2:int>
+-- !query output
+0 1 0 1
+0 1 0 1
+1 2 1 2
+1 2 1 2
+
+
+-- !query
+table lateral_test_t1
+|> join lateral (select lateral_test_t1.*, t2.* from lateral_test_t2 t2)
+-- !query schema
+struct<c1:int,c2:int,c1:int,c2:int,c1:int,c2:int>
+-- !query output
+0 1 0 1 0 2
+0 1 0 1 0 3
+1 2 1 2 0 2
+1 2 1 2 0 3
+
+
+-- !query
+table lateral_test_t1
+|> join lateral_test_t2
+|> join lateral (select lateral_test_t1.c2 + lateral_test_t2.c2)
+-- !query schema
+struct<c1:int,c2:int,c1:int,c2:int,(outer(lateral_test_t1.c2) +
outer(lateral_test_t2.c2)):int>
+-- !query output
+0 1 0 2 3
+0 1 0 3 4
+1 2 0 2 4
+1 2 0 3 5
+
+
+-- !query
+table natural_join_test_t1
+|> natural join natural_join_test_t2
+|> where k = "one"
+-- !query schema
+struct<k:string,v1:int,v2:int>
+-- !query output
+one 1 1
+one 1 5
+
+
+-- !query
+table natural_join_test_t1
+|> natural join natural_join_test_t2 nt2
+|> select natural_join_test_t1.*
+-- !query schema
+struct<k:string,v1:int>
+-- !query output
+one 1
+one 1
+two 2
+
+
+-- !query
+table natural_join_test_t1
+|> natural join natural_join_test_t2 nt2
+|> natural join natural_join_test_t3 nt3
+|> select natural_join_test_t1.*, nt2.*, nt3.*
+-- !query schema
+struct<k:string,v1:int,k:string,v2:int,k:string,v3:int>
+-- !query output
+one 1 one 1 one 4
+one 1 one 1 one 6
+one 1 one 5 one 4
+one 1 one 5 one 6
+two 2 two 22 two 5
+
+
+-- !query
+table join_test_t1
+|> inner join join_test_empty_table
+ inner join join_test_empty_table
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "PARSE_SYNTAX_ERROR",
+ "sqlState" : "42601",
+ "messageParameters" : {
+ "error" : "'inner'",
+ "hint" : ""
+ }
+}
+
+
+-- !query
+table join_test_t1
+|> select 1 + 2 as result
+|> full outer join join_test_empty_table on (join_test_t1.a =
join_test_empty_table.a)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "UNRESOLVED_COLUMN.WITH_SUGGESTION",
+ "sqlState" : "42703",
+ "messageParameters" : {
+ "objectName" : "`join_test_t1`.`a`",
+ "proposal" : "`result`, `join_test_empty_table`.`a`"
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 91,
+ "stopIndex" : 104,
+ "fragment" : "join_test_t1.a"
+ } ]
+}
+
+
+-- !query
+table join_test_t1 jt
+|> cross join (select * from jt)
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.parser.ParseException
+{
+ "errorClass" : "PARSE_SYNTAX_ERROR",
+ "sqlState" : "42601",
+ "messageParameters" : {
+ "error" : "'jt'",
+ "hint" : ""
+ }
+}
+
+
-- !query
drop table t
-- !query schema
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/execution/SparkSqlParserSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/execution/SparkSqlParserSuite.scala
index c76d44a1b82c..20b9c9caa749 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/execution/SparkSqlParserSuite.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/execution/SparkSqlParserSuite.scala
@@ -937,6 +937,12 @@ class SparkSqlParserSuite extends AnalysisTest with
SharedSparkSession {
checkSample("TABLE t |> TABLESAMPLE (50 PERCENT)")
checkSample("TABLE t |> TABLESAMPLE (5 ROWS)")
checkSample("TABLE t |> TABLESAMPLE (BUCKET 4 OUT OF 10)")
+ // Joins.
+ def checkPipeJoin(query: String): Unit = check(query, Seq(JOIN))
+ Seq("", "INNER", "LEFT", "LEFT OUTER", "SEMI", "LEFT SEMI", "RIGHT",
"RIGHT OUTER", "FULL",
+ "FULL OUTER", "ANTI", "LEFT ANTI", "CROSS").foreach { joinType =>
+ checkPipeJoin(s"TABLE t |> $joinType JOIN other ON (t.x = other.x)")
+ }
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
