You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2016/05/04 17:56:43 UTC
[2/2] calcite git commit: [CALCITE-1225] UNNEST with JOIN
[CALCITE-1225] UNNEST with JOIN
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/6031b7a4
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/6031b7a4
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/6031b7a4
Branch: refs/heads/master
Commit: 6031b7a477be2e60828df586f4a3542065b97bce
Parents: 2b74c79
Author: Julian Hyde <jh...@apache.org>
Authored: Tue May 3 19:20:11 2016 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Wed May 4 10:55:58 2016 -0700
----------------------------------------------------------------------
.../calcite/sql/validate/SqlValidatorImpl.java | 1 +
.../calcite/sql2rel/SqlToRelConverter.java | 1 -
.../calcite/sql/parser/SqlParserTest.java | 12 +++++++++++
.../apache/calcite/sql/test/SqlAdvisorTest.java | 1 +
.../java/org/apache/calcite/test/JdbcTest.java | 7 +++----
.../apache/calcite/test/MockCatalogReader.java | 14 +++++++++++++
.../apache/calcite/test/SqlValidatorTest.java | 21 ++++++++++++++++++++
site/_docs/reference.md | 7 +++----
8 files changed, 55 insertions(+), 9 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/6031b7a4/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 9f46a20..709318d 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -2383,6 +2383,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
unnestNs,
forceNullable);
registerOperandSubqueries(parentScope, call, 0);
+ scopes.put(node, parentScope);
break;
case OTHER_FUNCTION:
http://git-wip-us.apache.org/repos/asf/calcite/blob/6031b7a4/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 2dc2410..e0e8325 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -3934,7 +3934,6 @@ public class SqlToRelConverter {
// converted yet. This occurs when from items are correlated,
// e.g. "select from emp as emp join emp.getDepts() as dept".
// Create a temporary expression.
- assert isParent;
DeferredLookup lookup =
new DeferredLookup(this, qualified.identifier.names.get(0));
final CorrelationId correlId = cluster.createCorrel();
http://git-wip-us.apache.org/repos/asf/calcite/blob/6031b7a4/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 2ca511e..b534e16 100644
--- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -5676,6 +5676,18 @@ public class SqlParserTest {
checkFails(
"^unnest^(x)",
"(?s)Encountered \"unnest\" at.*");
+
+ // UNNEST with more than one argument
+ final String sql = "select * from dept,\n"
+ + "unnest(dept.employees, dept.managers)";
+ final String expected = "SELECT *\n"
+ + "FROM `DEPT`,\n"
+ + "(UNNEST(`DEPT`.`EMPLOYEES`, `DEPT`.`MANAGERS`))";
+ sql(sql).ok(expected);
+
+ // LATERAL UNNEST is not valid
+ sql("select * from dept, ^lateral^ unnest(dept.employees)")
+ .fails("(?s)Encountered \"lateral unnest\" at .*");
}
@Test public void testUnnestWithOrdinality() {
http://git-wip-us.apache.org/repos/asf/calcite/blob/6031b7a4/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
index 50019e1..80a330c 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
@@ -69,6 +69,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"TABLE(CATALOG.SALES.EMP_20)",
"TABLE(CATALOG.SALES.EMP_ADDRESS)",
"TABLE(CATALOG.SALES.DEPT)",
+ "TABLE(CATALOG.SALES.DEPT_NESTED)",
"TABLE(CATALOG.SALES.BONUS)",
"TABLE(CATALOG.SALES.ORDERS)",
"TABLE(CATALOG.SALES.SALGRADE)",
http://git-wip-us.apache.org/repos/asf/calcite/blob/6031b7a4/core/src/test/java/org/apache/calcite/test/JdbcTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 8874ea5..fb5eb8a 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -2237,16 +2237,15 @@ public class JdbcTest {
}
/** Per SQL std, UNNEST is implicitly LATERAL. */
- @Ignore
@Test public void testUnnestArrayColumn() {
CalciteAssert.hr()
.query("select d.\"name\", e.*\n"
+ "from \"hr\".\"depts\" as d,\n"
+ " UNNEST(d.\"employees\") as e")
.returnsUnordered(
- "empid=100; deptno=10; name=Bill; salary=10000.0; commission=1000; deptno0=10; name0=Sales; employees=[Employee [empid: 100, deptno: 10, name: Bill], Employee [empid: 150, deptno: 10, name: Sebastian]]",
- "empid=110; deptno=10; name=Theodore; salary=11500.0; commission=250; deptno0=10; name0=Sales; employees=[Employee [empid: 100, deptno: 10, name: Bill], Employee [empid: 150, deptno: 10, name: Sebastian]]",
- "empid=150; deptno=10; name=Sebastian; salary=7000.0; commission=null; deptno0=10; name0=Sales; employees=[Employee [empid: 100, deptno: 10, name: Bill], Employee [empid: 150, deptno: 10, name: Sebastian]]");
+ "name=HR; empid=200; deptno=20; name0=Eric; salary=8000.0; commission=500",
+ "name=Sales; empid=100; deptno=10; name0=Bill; salary=10000.0; commission=1000",
+ "name=Sales; empid=150; deptno=10; name0=Sebastian; salary=7000.0; commission=null");
}
private CalciteAssert.AssertQuery withFoodMartQuery(int id)
http://git-wip-us.apache.org/repos/asf/calcite/blob/6031b7a4/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java b/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
index f8f68d0..502e64d 100644
--- a/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
+++ b/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
@@ -150,6 +150,12 @@ public class MockCatalogReader implements Prepare.CatalogReader {
typeFactory.createSqlType(SqlTypeName.BOOLEAN);
final RelDataType rectilinearCoordType =
typeFactory.builder().add("X", intType).add("Y", intType).build();
+ final RelDataType empRecordType =
+ typeFactory.builder()
+ .add("EMPNO", intType)
+ .add("ENAME", varchar10Type).build();
+ final RelDataType empListType =
+ typeFactory.createArrayType(empRecordType, -1);
// TODO jvs 12-Feb-2005: register this canonical instance with type
// factory
@@ -189,6 +195,14 @@ public class MockCatalogReader implements Prepare.CatalogReader {
deptTable.addColumn("NAME", varchar10Type);
registerTable(deptTable);
+ // Register "DEPT_NESTED" table.
+ MockTable deptNestedTable =
+ MockTable.create(this, salesSchema, "DEPT_NESTED", false, 4);
+ deptNestedTable.addColumn("DEPTNO", intType);
+ deptNestedTable.addColumn("NAME", varchar10Type);
+ deptNestedTable.addColumn("EMPLOYEES", empListType);
+ registerTable(deptNestedTable);
+
// Register "BONUS" table.
MockTable bonusTable = MockTable.create(this, salesSchema, "BONUS", false, 0);
bonusTable.addColumn("ENAME", varchar20Type);
http://git-wip-us.apache.org/repos/asf/calcite/blob/6031b7a4/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 623517b..7610fce 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -6641,6 +6641,27 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
"Column 'C1' not found in any table");
}
+ @Test public void testUnnestArrayColumn() {
+ final String sql = "select d.deptno, e.*\n"
+ + "from dept_nested as d,\n"
+ + " UNNEST(d.employees) as e";
+ final String type = "RecordType(INTEGER NOT NULL DEPTNO,"
+ + " INTEGER NOT NULL EMPNO,"
+ + " VARCHAR(10) NOT NULL ENAME) NOT NULL";
+ sql(sql).type(type);
+
+ // equivalent query using CROSS JOIN
+ final String sql2 = "select d.deptno, e.*\n"
+ + "from dept_nested as d CROSS JOIN\n"
+ + " UNNEST(d.employees) as e";
+ sql(sql2).type(type);
+
+ // LATERAL works left-to-right
+ final String sql3 = "select d.deptno, e.*\n"
+ + "from UNNEST(^d^.employees) as e, dept_nested as d";
+ sql(sql3).fails("Table 'D' not found");
+ }
+
@Test public void testUnnestWithOrdinality() {
checkResultType("select*from unnest(array[1, 2]) with ordinality",
"RecordType(INTEGER NOT NULL EXPR$0, INTEGER NOT NULL ORDINALITY) NOT NULL");
http://git-wip-us.apache.org/repos/asf/calcite/blob/6031b7a4/site/_docs/reference.md
----------------------------------------------------------------------
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 43cd16d..26ccbaf 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -86,7 +86,8 @@ delete:
[ WHERE booleanExpression ]
query:
- [ WITH withItem [ , withItem ]* query ]
+ values
+ | WITH withItem [ , withItem ]* query
| {
select
| query UNION [ ALL ] query
@@ -128,14 +129,12 @@ joinCondition:
| USING '(' column [, column ]* ')'
tableReference:
- [ LATERAL ]
tablePrimary
[ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ]
tablePrimary:
[ TABLE ] [ [ catalogName . ] schemaName . ] tableName
- | '(' query ')'
- | values
+ | [ LATERAL ] '(' query ')'
| UNNEST '(' expression ')' [ WITH ORDINALITY ]
| TABLE '(' [ SPECIFIC ] functionName '(' expression [, expression ]* ')' ')'