Repository: calcite Updated Branches: refs/heads/master 972c54c44 -> 2a4f6c2c7
[CALCITE-2276] Allow explicit ROW value constructor in SELECT clause and elsewhere (Danny Chan) Close apache/calcite#679 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/2a4f6c2c Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/2a4f6c2c Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/2a4f6c2c Branch: refs/heads/master Commit: 2a4f6c2c7509c94b5eeb89d3925c91828f8ef1e1 Parents: 972c54c Author: yuzhao.cyz <[email protected]> Authored: Thu Apr 26 12:12:04 2018 +0800 Committer: Julian Hyde <[email protected]> Committed: Tue May 8 17:41:28 2018 -0700 ---------------------------------------------------------------------- core/src/main/codegen/templates/Parser.jj | 3 +- .../sql/validate/SqlAbstractConformance.java | 4 ++ .../calcite/sql/validate/SqlConformance.java | 18 +++++++ .../sql/validate/SqlConformanceEnum.java | 10 ++++ .../calcite/sql/parser/SqlParserTest.java | 53 ++++++++++++++++++++ core/src/test/resources/sql/misc.iq | 23 ++++++++- 6 files changed, 108 insertions(+), 3 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/2a4f6c2c/core/src/main/codegen/templates/Parser.jj ---------------------------------------------------------------------- diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index 881ab11..7970db6 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -3242,7 +3242,8 @@ SqlNode Expression3(ExprContext exprContext) : } list = ParenthesizedSimpleIdentifierList() { if (exprContext != ExprContext.ACCEPT_ALL - && exprContext != ExprContext.ACCEPT_CURSOR) + && exprContext != ExprContext.ACCEPT_CURSOR + && !this.conformance.allowExplicitRowValueConstructor()) { throw SqlUtil.newContextException(s.end(list), RESOURCE.illegalRowExpression()); http://git-wip-us.apache.org/repos/asf/calcite/blob/2a4f6c2c/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java index dbf8b6b..85465b8 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java @@ -71,6 +71,10 @@ public abstract class SqlAbstractConformance implements SqlConformance { return SqlConformanceEnum.DEFAULT.allowNiladicParentheses(); } + public boolean allowExplicitRowValueConstructor() { + return SqlConformanceEnum.DEFAULT.allowExplicitRowValueConstructor(); + } + public boolean allowExtend() { return SqlConformanceEnum.DEFAULT.allowExtend(); } http://git-wip-us.apache.org/repos/asf/calcite/blob/2a4f6c2c/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java index 76b6da9..6ba9a6f 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java @@ -261,6 +261,24 @@ public interface SqlConformance { boolean allowNiladicParentheses(); /** + * Whether to allow SQL syntax "{@code ROW(expr1, expr2, expr3)}". + * <p>The equivalent syntax in standard SQL is + * "{@code (expr1, expr2, expr3)}". + * + * <p>Standard SQL does not allow this because the type is not + * well-defined. However, PostgreSQL allows this behavior. + * + * <p>Standard SQL allows row expressions in other contexts, for instance + * inside {@code VALUES} clause. + * + * <p>Among the built-in conformance levels, true in + * {@link SqlConformanceEnum#DEFAULT}, + * {@link SqlConformanceEnum#LENIENT}; + * false otherwise. + */ + boolean allowExplicitRowValueConstructor(); + + /** * Whether to allow mixing table columns with extended columns in * {@code INSERT} (or {@code UPSERT}). * http://git-wip-us.apache.org/repos/asf/calcite/blob/2a4f6c2c/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java index 1a5752f..e24450a 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java @@ -210,6 +210,16 @@ public enum SqlConformanceEnum implements SqlConformance { } } + public boolean allowExplicitRowValueConstructor() { + switch (this) { + case DEFAULT: + case LENIENT: + return true; + default: + return false; + } + } + public boolean allowExtend() { switch (this) { case LENIENT: http://git-wip-us.apache.org/repos/asf/calcite/blob/2a4f6c2c/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 de9ca4d..d29f762 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 @@ -1016,6 +1016,46 @@ public class SqlParserTest { "SELECT `T`.`R`.`EXPR$1`.`EXPR$2`\n" + "FROM (SELECT (ROW((ROW(1, 2)), (ROW(3, 4, 5, 6)))) AS `R`\n" + "FROM `SALES`.`DEPTS`) AS `T`"); + + // Conformance DEFAULT and LENIENT support explicit row value constructor + conformance = SqlConformanceEnum.DEFAULT; + final String selectRow = "select ^row(t1a, t2a)^ from t1"; + final String expected = "SELECT (ROW(`T1A`, `T2A`))\n" + + "FROM `T1`"; + sql(selectRow).sansCarets().ok(expected); + conformance = SqlConformanceEnum.LENIENT; + sql(selectRow).sansCarets().ok(expected); + + final String pattern = "ROW expression encountered in illegal context"; + conformance = SqlConformanceEnum.MYSQL_5; + sql(selectRow).fails(pattern); + conformance = SqlConformanceEnum.ORACLE_12; + sql(selectRow).fails(pattern); + conformance = SqlConformanceEnum.STRICT_2003; + sql(selectRow).fails(pattern); + conformance = SqlConformanceEnum.SQL_SERVER_2008; + sql(selectRow).fails(pattern); + + final String whereRow = "select 1 from t2 where ^row (x, y)^ < row (a, b)"; + final String whereExpected = "SELECT 1\n" + + "FROM `T2`\n" + + "WHERE ((ROW(`X`, `Y`)) < (ROW(`A`, `B`)))"; + conformance = SqlConformanceEnum.DEFAULT; + sql(whereRow).sansCarets().ok(whereExpected); + conformance = SqlConformanceEnum.SQL_SERVER_2008; + sql(whereRow).fails(pattern); + + final String whereRow2 = "select 1 from t2 where ^(x, y)^ < (a, b)"; + conformance = SqlConformanceEnum.DEFAULT; + sql(whereRow2).sansCarets().ok(whereExpected); + if (this instanceof SqlUnParserTest) { + // After this point, SqlUnparserTest has problems. + // We generate ROW in a dialect that does not allow ROW in all contexts. + // So bail out. + return; + } + conformance = SqlConformanceEnum.SQL_SERVER_2008; + sql(whereRow2).sansCarets().ok(whereExpected); } @Test public void testPeriod() { @@ -1079,6 +1119,12 @@ public class SqlParserTest { + "WHERE (`X` IS DISTINCT FROM (ROW(4, 5, 6)))"); check( + "select * from t where x is distinct from row (4,5,6)", + "SELECT *\n" + + "FROM `T`\n" + + "WHERE (`X` IS DISTINCT FROM (ROW(4, 5, 6)))"); + + check( "select * from t where true is distinct from true", "SELECT *\n" + "FROM `T`\n" @@ -8356,6 +8402,13 @@ public class SqlParserTest { public Sql expression() { return expression ? this : new Sql(sql, true); } + + /** Removes the carets from the SQL string. Useful if you want to run + * a test once at a conformance level where it fails, then run it again + * at a conformance level where it succeeds. */ + public Sql sansCarets() { + return new Sql(sql.replace("^", ""), expression); + } } /** Runs tests on period operators such as OVERLAPS, IMMEDIATELY PRECEDES. */ http://git-wip-us.apache.org/repos/asf/calcite/blob/2a4f6c2c/core/src/test/resources/sql/misc.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/misc.iq b/core/src/test/resources/sql/misc.iq index 0aeb99d..e643b97 100644 --- a/core/src/test/resources/sql/misc.iq +++ b/core/src/test/resources/sql/misc.iq @@ -1330,8 +1330,27 @@ from "scott".emp; # Explicit ROW select deptno, row (empno, deptno) as r from "scott".emp; -ROW expression encountered in illegal context -!error ++--------+------------+ +| DEPTNO | R | ++--------+------------+ +| 10 | {7782, 10} | +| 10 | {7839, 10} | +| 10 | {7934, 10} | +| 20 | {7369, 20} | +| 20 | {7566, 20} | +| 20 | {7788, 20} | +| 20 | {7876, 20} | +| 20 | {7902, 20} | +| 30 | {7499, 30} | +| 30 | {7521, 30} | +| 30 | {7654, 30} | +| 30 | {7698, 30} | +| 30 | {7844, 30} | +| 30 | {7900, 30} | ++--------+------------+ +(14 rows) + +!ok # [CALCITE-877] Allow ROW as argument to COLLECT select deptno, collect(r) as empnos
