Repository: calcite Updated Branches: refs/heads/master a377f8f28 -> 1aaa0d686
[CALCITE-1766] Allow calls to niladic functions (such as CURRENT_DATE) to take parentheses (enabled by a conformance setting) (Ankit Singhal) Close apache/calcite#438 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/1aaa0d68 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/1aaa0d68 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/1aaa0d68 Branch: refs/heads/master Commit: 1aaa0d6860c7538d62998ec13bcc83763c04720f Parents: a377f8f Author: Ankit Singhal <[email protected]> Authored: Fri Apr 28 11:53:15 2017 +0530 Committer: Julian Hyde <[email protected]> Committed: Fri Apr 28 10:57:55 2017 -0700 ---------------------------------------------------------------------- .../java/org/apache/calcite/sql/SqlSyntax.java | 3 ++ .../sql/validate/SqlAbstractConformance.java | 4 ++ .../calcite/sql/validate/SqlConformance.java | 40 ++++++++++++++-- .../sql/validate/SqlConformanceEnum.java | 18 +++++++ .../sql/validate/SqlDelegatingConformance.java | 4 ++ .../calcite/sql/validate/SqlValidatorImpl.java | 3 +- .../calcite/sql/test/SqlOperatorBaseTest.java | 50 ++++++++++++++------ .../apache/calcite/sql/test/SqlTesterImpl.java | 9 ++-- site/_docs/reference.md | 4 ++ 9 files changed, 112 insertions(+), 23 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/1aaa0d68/core/src/main/java/org/apache/calcite/sql/SqlSyntax.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSyntax.java b/core/src/main/java/org/apache/calcite/sql/SqlSyntax.java index 50d30ac..53280ba 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlSyntax.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlSyntax.java @@ -16,6 +16,7 @@ */ package org.apache.calcite.sql; +import org.apache.calcite.sql.validate.SqlConformance; import org.apache.calcite.util.Util; /** @@ -119,6 +120,8 @@ public enum SqlSyntax { /** * Function syntax which takes no parentheses if there are no arguments, for * example "CURRENTTIME". + * + * @see SqlConformance#allowNiladicParentheses() */ FUNCTION_ID { public void unparse( http://git-wip-us.apache.org/repos/asf/calcite/blob/1aaa0d68/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 52a5241..ddb4d7e 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 @@ -55,6 +55,10 @@ public abstract class SqlAbstractConformance implements SqlConformance { return SqlConformanceEnum.DEFAULT.isInsertSubsetColumnsAllowed(); } + public boolean allowNiladicParentheses() { + return SqlConformanceEnum.DEFAULT.allowNiladicParentheses(); + } + } // End SqlAbstractConformance.java http://git-wip-us.apache.org/repos/asf/calcite/blob/1aaa0d68/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 3c0ffb8..88af704 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 @@ -62,11 +62,12 @@ public interface SqlConformance { SqlConformanceEnum PRAGMATIC_2003 = SqlConformanceEnum.PRAGMATIC_2003; /** - * Whether 'order by 2' is interpreted to mean 'sort by the 2nd column in - * the select list'. + * Whether '{@code ORDER BY 2}' is interpreted to mean 'sort by the 2nd + * column in the select list'. * * <p>Among the built-in conformance levels, true in * {@link SqlConformanceEnum#DEFAULT}, + * {@link SqlConformanceEnum#LENIENT}, * {@link SqlConformanceEnum#ORACLE_10}, * {@link SqlConformanceEnum#ORACLE_12}, * {@link SqlConformanceEnum#STRICT_92}, @@ -78,11 +79,12 @@ public interface SqlConformance { boolean isSortByOrdinal(); /** - * Whether 'order by x' is interpreted to mean 'sort by the select list item - * whose alias is x' even if there is a column called x. + * Whether '{@code ORDER BY x}' is interpreted to mean 'sort by the select + * list item whose alias is x' even if there is a column called x. * * <p>Among the built-in conformance levels, true in * {@link SqlConformanceEnum#DEFAULT}, + * {@link SqlConformanceEnum#LENIENT}, * {@link SqlConformanceEnum#ORACLE_10}, * {@link SqlConformanceEnum#ORACLE_12}, * {@link SqlConformanceEnum#STRICT_92}; @@ -102,7 +104,7 @@ public interface SqlConformance { boolean isSortByAliasObscures(); /** - * Whether FROM clause is required in a SELECT statement. + * Whether {@code FROM} clause is required in a {@code SELECT} statement. * * <p>Among the built-in conformance levels, true in * {@link SqlConformanceEnum#ORACLE_10}, @@ -119,6 +121,7 @@ public interface SqlConformance { * the parser. * * <p>Among the built-in conformance levels, true in + * {@link SqlConformanceEnum#LENIENT}, * {@link SqlConformanceEnum#ORACLE_10}; * {@link SqlConformanceEnum#ORACLE_12}; * false otherwise. @@ -130,6 +133,7 @@ public interface SqlConformance { * the parser. * * <p>Among the built-in conformance levels, true in + * {@link SqlConformanceEnum#LENIENT}, * {@link SqlConformanceEnum#ORACLE_10}; * {@link SqlConformanceEnum#ORACLE_12}; * false otherwise. @@ -153,6 +157,7 @@ public interface SqlConformance { * </ul> * * <p>Among the built-in conformance levels, true in + * {@link SqlConformanceEnum#LENIENT}, * {@link SqlConformanceEnum#SQL_SERVER_2008}; * {@link SqlConformanceEnum#ORACLE_12}; * false otherwise. @@ -173,11 +178,36 @@ public interface SqlConformance { * column is not declared {@code NOT NULL}. * * <p>Among the built-in conformance levels, true in + * {@link SqlConformanceEnum#LENIENT}, * {@link SqlConformanceEnum#PRAGMATIC_99}, * {@link SqlConformanceEnum#PRAGMATIC_2003}; * false otherwise. */ boolean isInsertSubsetColumnsAllowed(); + + /** + * Whether to allow parentheses to be specified in calls to niladic functions + * and procedures (that is, functions and procedures with no parameters). + * + * <p>For example, {@code CURRENT_DATE} is a niladic system function. In + * standard SQL it must be invoked without parentheses: + * + * <blockquote><code>VALUES CURRENT_DATE</code></blockquote> + * + * <p>If {@code allowNiladicParentheses}, the following syntax is also valid: + * + * <blockquote><code>VALUES CURRENT_DATE()</code></blockquote> + * + * <p>Of the popular databases, MySQL, Apache Phoenix and VoltDB allow this + * behavior; + * Apache Hive, HSQLDB, IBM DB2, Microsoft SQL Server, Oracle, PostgreSQL do + * not. + * + * <p>Among the built-in conformance levels, true in + * {@link SqlConformanceEnum#LENIENT}; + * false otherwise. + */ + boolean allowNiladicParentheses(); } // End SqlConformance.java http://git-wip-us.apache.org/repos/asf/calcite/blob/1aaa0d68/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 63dde33..1ff0a10 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 @@ -23,6 +23,9 @@ public enum SqlConformanceEnum implements SqlConformance { /** Calcite's default SQL behavior. */ DEFAULT, + /** Conformance value that allows just about everything. */ + LENIENT, + /** Conformance value that instructs Calcite to use SQL semantics strictly * consistent with the SQL:92 standard. */ STRICT_92, @@ -62,6 +65,7 @@ public enum SqlConformanceEnum implements SqlConformance { public boolean isSortByOrdinal() { switch (this) { case DEFAULT: + case LENIENT: case ORACLE_10: case ORACLE_12: case STRICT_92: @@ -77,6 +81,7 @@ public enum SqlConformanceEnum implements SqlConformance { public boolean isSortByAlias() { switch (this) { case DEFAULT: + case LENIENT: case ORACLE_10: case ORACLE_12: case STRICT_92: @@ -106,6 +111,7 @@ public enum SqlConformanceEnum implements SqlConformance { public boolean isBangEqualAllowed() { switch (this) { + case LENIENT: case ORACLE_10: case ORACLE_12: return true; @@ -116,6 +122,7 @@ public enum SqlConformanceEnum implements SqlConformance { @Override public boolean isMinusAllowed() { switch (this) { + case LENIENT: case ORACLE_10: case ORACLE_12: return true; @@ -126,6 +133,7 @@ public enum SqlConformanceEnum implements SqlConformance { public boolean isApplyAllowed() { switch (this) { + case LENIENT: case SQL_SERVER_2008: case ORACLE_12: return true; @@ -136,6 +144,7 @@ public enum SqlConformanceEnum implements SqlConformance { public boolean isInsertSubsetColumnsAllowed() { switch (this) { + case LENIENT: case PRAGMATIC_99: case PRAGMATIC_2003: return true; @@ -144,6 +153,15 @@ public enum SqlConformanceEnum implements SqlConformance { } } + public boolean allowNiladicParentheses() { + switch (this) { + case LENIENT: + return true; + default: + return false; + } + } + } // End SqlConformanceEnum.java http://git-wip-us.apache.org/repos/asf/calcite/blob/1aaa0d68/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java index f5e02c5..e1b4b4c 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java @@ -56,6 +56,10 @@ public class SqlDelegatingConformance extends SqlAbstractConformance { @Override public boolean isInsertSubsetColumnsAllowed() { return delegate.isInsertSubsetColumnsAllowed(); } + + @Override public boolean allowNiladicParentheses() { + return delegate.allowNiladicParentheses(); + } } // End SqlDelegatingConformance.java http://git-wip-us.apache.org/repos/asf/calcite/blob/1aaa0d68/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 323bf19..c458aab 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 @@ -4639,7 +4639,8 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { final SqlOperator operator = call.getOperator(); if ((call.operandCount() == 0) && (operator.getSyntax() == SqlSyntax.FUNCTION_ID) - && !call.isExpanded()) { + && !call.isExpanded() + && !conformance.allowNiladicParentheses()) { // For example, "LOCALTIME()" is illegal. (It should be // "LOCALTIME", which would have been handled as a // SqlIdentifier.) http://git-wip-us.apache.org/repos/asf/calcite/blob/1aaa0d68/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index d3c4920..63443cb 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -2942,11 +2942,7 @@ public abstract class SqlOperatorBaseTest { false); // "!=" is allowed under ORACLE_10 SQL conformance level final SqlTester tester1 = - tester - .withConformance(SqlConformanceEnum.ORACLE_10) - .withConnectionFactory( - CalciteAssert.EMPTY_CONNECTION_FACTORY - .with("conformance", SqlConformanceEnum.ORACLE_10)); + tester.withConformance(SqlConformanceEnum.ORACLE_10); tester1 .checkBoolean("1 <> 1", Boolean.FALSE); @@ -4725,6 +4721,11 @@ public abstract class SqlOperatorBaseTest { @Test public void testCurrentDateFunc() { tester.setFor(SqlStdOperatorTable.CURRENT_DATE, VM_FENNEL); + + // A tester with a lenient conformance that allows parentheses. + final SqlTester tester1 = tester + .withConformance(SqlConformanceEnum.LENIENT); + tester.checkScalar("CURRENT_DATE", DATE_PATTERN, "DATE NOT NULL"); tester.checkScalar( "(CURRENT_DATE - CURRENT_DATE) DAY", @@ -4738,17 +4739,38 @@ public abstract class SqlOperatorBaseTest { "No match found for function signature CURRENT_DATE\\(\\)", false); + tester1.checkBoolean("CURRENT_DATE() IS NULL", false); + tester1.checkBoolean("CURRENT_DATE IS NOT NULL", true); + tester1.checkBoolean("NOT (CURRENT_DATE() IS NULL)", true); + tester1.checkType("CURRENT_DATE", "DATE NOT NULL"); + tester1.checkType("CURRENT_DATE()", "DATE NOT NULL"); + tester1.checkType("CURRENT_TIMESTAMP()", "TIMESTAMP(0) NOT NULL"); + tester1.checkType("CURRENT_TIME()", "TIME(0) NOT NULL"); + // Check the actual value. final Pair<String, Hook.Closeable> pair = currentTimeString(LOCAL_TZ); - tester.checkScalar( - "CAST(CURRENT_DATE AS VARCHAR(30))", - pair.left.substring(0, 10), - "VARCHAR(30) NOT NULL"); - tester.checkScalar( - "CURRENT_DATE", - pair.left.substring(0, 10), - "DATE NOT NULL"); - pair.right.close(); + final String dateString = pair.left; + try (Hook.Closeable ignore = pair.right) { + tester.checkScalar("CAST(CURRENT_DATE AS VARCHAR(30))", + dateString.substring(0, 10), + "VARCHAR(30) NOT NULL"); + tester.checkScalar("CURRENT_DATE", + dateString.substring(0, 10), + "DATE NOT NULL"); + + tester1.checkScalar("CAST(CURRENT_DATE AS VARCHAR(30))", + dateString.substring(0, 10), + "VARCHAR(30) NOT NULL"); + tester1.checkScalar("CAST(CURRENT_DATE() AS VARCHAR(30))", + dateString.substring(0, 10), + "VARCHAR(30) NOT NULL"); + tester1.checkScalar("CURRENT_DATE", + dateString.substring(0, 10), + "DATE NOT NULL"); + tester1.checkScalar("CURRENT_DATE()", + dateString.substring(0, 10), + "DATE NOT NULL"); + } } @Test public void testSubstringFunction() { http://git-wip-us.apache.org/repos/asf/calcite/blob/1aaa0d68/core/src/test/java/org/apache/calcite/sql/test/SqlTesterImpl.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlTesterImpl.java b/core/src/test/java/org/apache/calcite/sql/test/SqlTesterImpl.java index 663714d..f4b2fbd 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlTesterImpl.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlTesterImpl.java @@ -88,7 +88,7 @@ public class SqlTesterImpl implements SqlTester, AutoCloseable { /** * {@inheritDoc} * - * This default implementation does nothing. + * <p>This default implementation does nothing. */ public void close() { // no resources to release @@ -293,14 +293,17 @@ public class SqlTesterImpl implements SqlTester, AutoCloseable { if (conformance == null) { conformance = SqlConformanceEnum.DEFAULT; } - return with("conformance", conformance); + return with("conformance", conformance) + .withConnectionFactory( + CalciteAssert.EMPTY_CONNECTION_FACTORY + .with("conformance", conformance)); } public SqlTester withOperatorTable(SqlOperatorTable operatorTable) { return with("operatorTable", operatorTable); } - public SqlTester withConnectionFactory( + public SqlTesterImpl withConnectionFactory( CalciteAssert.ConnectionFactory connectionFactory) { return with("connectionFactory", connectionFactory); } http://git-wip-us.apache.org/repos/asf/calcite/blob/1aaa0d68/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 5e64c70..15c3c0d 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -1145,6 +1145,10 @@ Not implemented: | MINUTE(date) | Equivalent to `EXTRACT(MINUTE FROM date)`. Returns an integer between 0 and 59. | SECOND(date) | Equivalent to `EXTRACT(SECOND FROM date)`. Returns an integer between 0 and 59. +Calls to niladic functions such as `CURRENT_DATE` do not accept parentheses in +standard SQL. Calls with parentheses, such as `CURRENT_DATE()` are accepted in certain +[conformance levels]({{ site.apiRoot }}/org/apache/calcite/sql/validate/SqlConformance.html#allowNiladicParentheses--). + Not implemented: * EXTRACT(timeUnit FROM interval)
