This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 04314987373f0d7dd84b7b4e8df3243eef7f8f68 Author: Julian Hyde <[email protected]> AuthorDate: Mon Jul 20 23:49:16 2020 -0700 [CALCITE-4080] Allow character literals as column aliases, if SqlConformance.allowCharLiteralAlias() This is for compatibility with MySQL, Microsoft SQL Server, and BigQuery dialects of SQL. Close apache/calcite#2087 --- core/src/main/codegen/templates/Parser.jj | 39 +++++- .../apache/calcite/runtime/CalciteResource.java | 3 + .../sql/validate/SqlAbstractConformance.java | 4 + .../calcite/sql/validate/SqlConformance.java | 19 +++ .../calcite/sql/validate/SqlConformanceEnum.java | 14 ++ .../calcite/runtime/CalciteResource.properties | 1 + .../apache/calcite/sql/parser/SqlParserTest.java | 142 ++++++++++++++++++++- 7 files changed, 215 insertions(+), 7 deletions(-) diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index b317cb3..1d8465e 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -1828,7 +1828,15 @@ SqlNode SelectItem() : e = SelectExpression() [ [ <AS> ] - id = SimpleIdentifier() { + ( + id = SimpleIdentifier() + | + // Mute the warning about ambiguity between alias and continued + // string literal. + LOOKAHEAD(1) + id = SimpleIdentifierFromStringLiteral() + ) + { e = SqlStdOperatorTable.AS.createCall(span().end(e), e, id); } ] @@ -4117,6 +4125,12 @@ SqlNode StringLiteral() : } } ( + // The grammar is ambiguous when a continued literals and a character + // string alias are both possible. For example, in + // SELECT x'01'\n'ab' + // we prefer that 'ab' continues the literal, and is not an alias. + // The following LOOKAHEAD mutes the warning about ambiguity. + LOOKAHEAD(1) <QUOTED_STRING> { try { @@ -4166,6 +4180,12 @@ SqlNode StringLiteral() : nfrags++; } ( + // The grammar is ambiguous when a continued literals and a character + // string alias are both possible. For example, in + // SELECT 'taxi'\n'cab' + // we prefer that 'cab' continues the literal, and is not an alias. + // The following LOOKAHEAD mutes the warning about ambiguity. + LOOKAHEAD(1) <QUOTED_STRING> { p = SqlParserUtil.parseString(token.image); @@ -4679,6 +4699,23 @@ SqlIdentifier SimpleIdentifier() : } /** + * Parses a character literal as an SqlIdentifier. + * Only valid for column aliases in certain dialects. + */ +SqlIdentifier SimpleIdentifierFromStringLiteral() : +{ +} +{ + <QUOTED_STRING> { + if (!this.conformance.allowCharLiteralAlias()) { + throw SqlUtil.newContextException(getPos(), RESOURCE.charLiteralAliasNotValid()); + } + final String s = SqlParserUtil.parseString(token.image); + return new SqlIdentifier(s, getPos()); + } +} + +/** * Parses a comma-separated list of simple identifiers. */ void SimpleIdentifierCommaList(List<SqlNode> list) : diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java index d932321..53fe755 100644 --- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java +++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java @@ -283,6 +283,9 @@ public interface CalciteResource { @BaseMessage("Table or column alias must be a simple identifier") ExInst<SqlValidatorException> aliasMustBeSimpleIdentifier(); + @BaseMessage("Expecting alias, found character literal") + ExInst<SqlValidatorException> charLiteralAliasNotValid(); + @BaseMessage("List of column aliases must have same degree as table; table has {0,number,#} columns {1}, whereas alias list has {2,number,#} columns") ExInst<SqlValidatorException> aliasListDegree(int a0, String a1, int a2); 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 842ee92..531543c 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 @@ -27,6 +27,10 @@ public abstract class SqlAbstractConformance implements SqlConformance { return SqlConformanceEnum.DEFAULT.isLiberal(); } + public boolean allowCharLiteralAlias() { + return SqlConformanceEnum.DEFAULT.allowCharLiteralAlias(); + } + public boolean isGroupByAlias() { return SqlConformanceEnum.DEFAULT.isGroupByAlias(); } 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 dc07d1b..78b7db3 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 @@ -68,6 +68,25 @@ public interface SqlConformance { boolean isLiberal(); /** + * Whether this dialect allows character literals as column aliases. + * + * <p>For example, + * + * <blockquote><pre> + * SELECT empno, sal + comm AS 'remuneration' + * FROM Emp</pre></blockquote> + * + * <p>Among the built-in conformance levels, true in + * {@link SqlConformanceEnum#BABEL}, + * {@link SqlConformanceEnum#BIG_QUERY}, + * {@link SqlConformanceEnum#LENIENT}, + * {@link SqlConformanceEnum#MYSQL_5}, + * {@link SqlConformanceEnum#SQL_SERVER_2008}; + * false otherwise. + */ + boolean allowCharLiteralAlias(); + + /** * Whether to allow aliases from the {@code SELECT} clause to be used as * column names in the {@code GROUP BY} clause. * 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 3d689a1..ffccfd7 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 @@ -88,6 +88,20 @@ public enum SqlConformanceEnum implements SqlConformance { } } + public boolean allowCharLiteralAlias() { + switch (this) { + case BABEL: + case BIG_QUERY: + case LENIENT: + case MYSQL_5: + case SQL_SERVER_2008: + return true; + default: + return false; + } + } + + public boolean isGroupByAlias() { switch (this) { case BABEL: diff --git a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties index 079ef8c..9f4b453 100644 --- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties +++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties @@ -97,6 +97,7 @@ NumberLiteralOutOfRange=Numeric literal ''{0}'' out of range DateLiteralOutOfRange=Date literal ''{0}'' out of range StringFragsOnSameLine=String literal continued on same line AliasMustBeSimpleIdentifier=Table or column alias must be a simple identifier +CharLiteralAliasNotValid=Expecting alias, found character literal AliasListDegree=List of column aliases must have same degree as table; table has {0,number,#} columns {1}, whereas alias list has {2,number,#} columns AliasListDuplicate=Duplicate name ''{0}'' in column alias list JoinRequiresCondition=INNER, LEFT, RIGHT or FULL join requires a condition (NATURAL keyword or ON or USING clause) 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 603beec..4fa3451 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 @@ -2123,6 +2123,79 @@ public class SqlParserTest { + "WHERE (`x`.`DEPTNO` IN (10, 20))"); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-4080";>[CALCITE-4080] + * Allow character literals as column aliases, if + * SqlConformance.allowCharLiteralAlias()</a>. */ + @Test void testSingleQuotedAlias() { + final String expectingAlias = "Expecting alias, found character literal"; + + final String sql1 = "select 1 as ^'a b'^ from t"; + conformance = SqlConformanceEnum.DEFAULT; + sql(sql1).fails(expectingAlias); + conformance = SqlConformanceEnum.MYSQL_5; + final String sql1b = "SELECT 1 AS `a b`\n" + + "FROM `T`"; + sql(sql1).sansCarets().ok(sql1b); + conformance = SqlConformanceEnum.BIG_QUERY; + sql(sql1).sansCarets().ok(sql1b); + conformance = SqlConformanceEnum.SQL_SERVER_2008; + sql(sql1).sansCarets().ok(sql1b); + + // valid on MSSQL (alias contains a single quote) + final String sql2 = "with t as (select 1 as ^'x''y'^)\n" + + "select [x'y] from t as [u]"; + conformance = SqlConformanceEnum.DEFAULT; + quoting = Quoting.BRACKET; + sql(sql2).fails(expectingAlias); + conformance = SqlConformanceEnum.MYSQL_5; + final String sql2b = "WITH `T` AS (SELECT 1 AS `x'y`) (SELECT `x'y`\n" + + "FROM `T` AS `u`)"; + sql(sql2).sansCarets().ok(sql2b); + conformance = SqlConformanceEnum.BIG_QUERY; + sql(sql2).sansCarets().ok(sql2b); + conformance = SqlConformanceEnum.SQL_SERVER_2008; + sql(sql2).sansCarets().ok(sql2b); + + // also valid on MSSQL + final String sql3 = "with [t] as (select 1 as [x]) select [x] from [t]"; + final String sql3b = "WITH `t` AS (SELECT 1 AS `x`) (SELECT `x`\n" + + "FROM `t`)"; + conformance = SqlConformanceEnum.DEFAULT; + quoting = Quoting.BRACKET; + sql(sql3).sansCarets().ok(sql3b); + conformance = SqlConformanceEnum.MYSQL_5; + sql(sql3).sansCarets().ok(sql3b); + conformance = SqlConformanceEnum.BIG_QUERY; + sql(sql3).sansCarets().ok(sql3b); + conformance = SqlConformanceEnum.SQL_SERVER_2008; + sql(sql3).sansCarets().ok(sql3b); + + // char literal as table alias is invalid on MSSQL (and others) + final String sql4 = "with t as (select 1 as x) select x from t as ^'u'^"; + final String sql4b = "(?s)Encountered \"\\\\'u\\\\'\" at .*"; + conformance = SqlConformanceEnum.DEFAULT; + sql(sql4).fails(sql4b); + conformance = SqlConformanceEnum.MYSQL_5; + sql(sql4).fails(sql4b); + conformance = SqlConformanceEnum.BIG_QUERY; + sql(sql4).fails(sql4b); + conformance = SqlConformanceEnum.SQL_SERVER_2008; + sql(sql4).fails(sql4b); + + // char literal as table alias (without AS) is invalid on MSSQL (and others) + final String sql5 = "with t as (select 1 as x) select x from t ^'u'^"; + final String sql5b = "(?s)Encountered \"\\\\'u\\\\'\" at .*"; + conformance = SqlConformanceEnum.DEFAULT; + sql(sql5).fails(sql5b); + conformance = SqlConformanceEnum.MYSQL_5; + sql(sql5).fails(sql5b); + conformance = SqlConformanceEnum.BIG_QUERY; + sql(sql5).fails(sql5b); + conformance = SqlConformanceEnum.SQL_SERVER_2008; + sql(sql5).fails(sql5b); + } + @Test void testInList() { sql("select * from emp where deptno in (10, 20) and gender = 'F'") .ok("SELECT *\n" @@ -2687,6 +2760,62 @@ public class SqlParserTest { .fails("Binary literal string must contain only characters '0' - '9', 'A' - 'F'"); } + /** Tests that ambiguity between extended string literals and character string + * aliases is always resolved in favor of extended string literals. */ + @Test void testContinuedLiteralAlias() { + final String expectingAlias = "Expecting alias, found character literal"; + + // Not ambiguous, because of 'as'. + final String sql0 = "select 1 an_alias,\n" + + " x'01'\n" + + " 'ab' as x\n" + + "from t"; + final String sql0b = "SELECT 1 AS `AN_ALIAS`, X'01'\n" + + "'AB' AS `X`\n" + + "FROM `T`"; + conformance = SqlConformanceEnum.DEFAULT; + sql(sql0).ok(sql0b); + conformance = SqlConformanceEnum.MYSQL_5; + sql(sql0).ok(sql0b); + conformance = SqlConformanceEnum.BIG_QUERY; + sql(sql0).ok(sql0b); + + // Is 'ab' an alias or is it part of the x'01' 'ab' continued binary string + // literal? It's ambiguous, but we prefer the latter. + final String sql1 = "select 1 ^'an alias'^,\n" + + " x'01'\n" + + " 'ab'\n" + + "from t"; + final String sql1b = "SELECT 1 AS `an alias`, X'01'\n" + + "'AB'\n" + + "FROM `T`"; + conformance = SqlConformanceEnum.DEFAULT; + sql(sql1).fails(expectingAlias); + conformance = SqlConformanceEnum.MYSQL_5; + sql(sql1).sansCarets().ok(sql1b); + conformance = SqlConformanceEnum.BIG_QUERY; + sql(sql1).sansCarets().ok(sql1b); + + // Parser prefers continued character and binary string literals over + // character string aliases, regardless of whether the dialect allows + // character string aliases. + final String sql2 = "select 'continued'\n" + + " 'char literal, not alias',\n" + + " x'01'\n" + + " 'ab'\n" + + "from t"; + final String sql2b = "SELECT 'continued'\n" + + "'char literal, not alias', X'01'\n" + + "'AB'\n" + + "FROM `T`"; + conformance = SqlConformanceEnum.DEFAULT; + sql(sql2).ok(sql2b); + conformance = SqlConformanceEnum.MYSQL_5; + sql(sql2).ok(sql2b); + conformance = SqlConformanceEnum.BIG_QUERY; + sql(sql2).ok(sql2b); + } + @Test void testMixedFrom() { // REVIEW: Is this syntax even valid? sql("select * from a join b using (x), c join d using (y)") @@ -3960,8 +4089,8 @@ public class SqlParserTest { @Test void testBitStringNotImplemented() { // Bit-string is longer part of the SQL standard. We do not support it. - sql("select B^'1011'^ || 'foobar' from (values (true))") - .fails("(?s).*Encountered \"\\\\'1011\\\\'\" at line 1, column 9.*"); + sql("select (B^'1011'^ || 'foobar') from (values (true))") + .fails("(?s).*Encountered \"\\\\'1011\\\\'\" at .*"); } @Test void testHexAndBinaryString() { @@ -4040,16 +4169,17 @@ public class SqlParserTest { } @Test void testStringLiteralFails() { - sql("select N ^'space'^") + sql("select (N ^'space'^)") .fails("(?s).*Encountered .*space.* at line 1, column ...*"); - sql("select _latin1\n^'newline'^") + sql("select (_latin1\n^'newline'^)") .fails("(?s).*Encountered.*newline.* at line 2, column ...*"); sql("select ^_unknown-charset''^ from (values(true))") .fails("Unknown character set 'unknown-charset'"); // valid syntax, but should give a validator error - sql("select N'1' '2' from t") - .ok("SELECT _ISO-8859-1'1'\n'2'\n" + sql("select (N'1' '2') from t") + .ok("SELECT _ISO-8859-1'1'\n" + + "'2'\n" + "FROM `T`"); }
