This is an automated email from the ASF dual-hosted git repository. xiong pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push: new 10e8534 [CALCITE-4980] Babel parser support MySQL NULL-safe equal operator '<=>' (xurenhe&&DuanXiong) 10e8534 is described below commit 10e8534b2d8a052370e928aed323b71079ace752 Author: xurenhe <xurenhe19910...@gmail.com> AuthorDate: Tue Jan 25 16:29:43 2022 +0800 [CALCITE-4980] Babel parser support MySQL NULL-safe equal operator '<=>' (xurenhe&&DuanXiong) --- babel/src/main/codegen/config.fmpp | 2 ++ babel/src/main/codegen/includes/parserImpls.ftl | 12 +++++++++ .../org/apache/calcite/test/BabelParserTest.java | 31 ++++++++++++++++++++++ .../java/org/apache/calcite/test/BabelTest.java | 31 ++++++++++++++++++++++ .../calcite/sql/fun/SqlLibraryOperators.java | 14 ++++++++++ .../calcite/sql2rel/StandardConvertletTable.java | 1 + site/_docs/reference.md | 7 +++-- 7 files changed, 96 insertions(+), 2 deletions(-) diff --git a/babel/src/main/codegen/config.fmpp b/babel/src/main/codegen/config.fmpp index 772a394..f75631e 100644 --- a/babel/src/main/codegen/config.fmpp +++ b/babel/src/main/codegen/config.fmpp @@ -545,12 +545,14 @@ data: { # Example: "< INFIX_CAST: \"::\" >". binaryOperatorsTokens: [ "< INFIX_CAST: \"::\" >" + "< NULL_SAFE_EQUAL: \"<=>\" >" ] # Binary operators initialization. # Example: "InfixCast". extraBinaryExpressions: [ "InfixCast" + "NullSafeEqual" ] # List of files in @includes directory that have parser method diff --git a/babel/src/main/codegen/includes/parserImpls.ftl b/babel/src/main/codegen/includes/parserImpls.ftl index d4a5bb3..2e91347 100644 --- a/babel/src/main/codegen/includes/parserImpls.ftl +++ b/babel/src/main/codegen/includes/parserImpls.ftl @@ -194,3 +194,15 @@ void InfixCast(List<Object> list, ExprContext exprContext, Span s) : list.add(dt); } } + +/** Parses the NULL-safe "<=>" equal operator used in MySQL. */ +void NullSafeEqual(List<Object> list, ExprContext exprContext, Span s) : +{ +} +{ + <NULL_SAFE_EQUAL> { + checkNonQueryExpression(exprContext); + list.add(new SqlParserUtil.ToTreeListItem(SqlLibraryOperators.NULL_SAFE_EQUAL, getPos())); + } + Expression2b(ExprContext.ACCEPT_SUB_QUERY, list) +} diff --git a/babel/src/test/java/org/apache/calcite/test/BabelParserTest.java b/babel/src/test/java/org/apache/calcite/test/BabelParserTest.java index 377b71b..777e34a 100644 --- a/babel/src/test/java/org/apache/calcite/test/BabelParserTest.java +++ b/babel/src/test/java/org/apache/calcite/test/BabelParserTest.java @@ -221,6 +221,37 @@ class BabelParserTest extends SqlParserTest { sql(sql).ok(expected); } + /** Tests parsing MySQL-style "<=>" equal operator. */ + @Test void testParseNullSafeEqual() { + // x <=> y + final String projectSql = "SELECT x <=> 3 FROM (VALUES (1, 2)) as tbl(x,y)"; + sql(projectSql).ok("SELECT (`X` <=> 3)\n" + + "FROM (VALUES (ROW(1, 2))) AS `TBL` (`X`, `Y`)"); + final String filterSql = "SELECT y FROM (VALUES (1, 2)) as tbl(x,y) WHERE x <=> null"; + sql(filterSql).ok("SELECT `Y`\n" + + "FROM (VALUES (ROW(1, 2))) AS `TBL` (`X`, `Y`)\n" + + "WHERE (`X` <=> NULL)"); + final String joinConditionSql = "SELECT tbl1.y FROM (VALUES (1, 2)) as tbl1(x,y)\n" + + "LEFT JOIN (VALUES (null, 3)) as tbl2(x,y) ON tbl1.x <=> tbl2.x"; + sql(joinConditionSql).ok("SELECT `TBL1`.`Y`\n" + + "FROM (VALUES (ROW(1, 2))) AS `TBL1` (`X`, `Y`)\n" + + "LEFT JOIN (VALUES (ROW(NULL, 3))) AS `TBL2` (`X`, `Y`) ON (`TBL1`.`X` <=> `TBL2`.`X`)"); + // (a, b) <=> (x, y) + final String rowComparisonSql = "SELECT y\n" + + "FROM (VALUES (1, 2)) as tbl(x,y) WHERE (x,y) <=> (null,2)"; + sql(rowComparisonSql).ok("SELECT `Y`\n" + + "FROM (VALUES (ROW(1, 2))) AS `TBL` (`X`, `Y`)\n" + + "WHERE ((ROW(`X`, `Y`)) <=> (ROW(NULL, 2)))"); + // the higher precedence + final String highPrecedenceSql = "SELECT x <=> 3 + 3 FROM (VALUES (1, 2)) as tbl(x,y)"; + sql(highPrecedenceSql).ok("SELECT (`X` <=> (3 + 3))\n" + + "FROM (VALUES (ROW(1, 2))) AS `TBL` (`X`, `Y`)"); + // the lower precedence + final String lowPrecedenceSql = "SELECT NOT x <=> 3 FROM (VALUES (1, 2)) as tbl(x,y)"; + sql(lowPrecedenceSql).ok("SELECT (NOT (`X` <=> 3))\n" + + "FROM (VALUES (ROW(1, 2))) AS `TBL` (`X`, `Y`)"); + } + @Test void testCreateTableWithNoCollectionTypeSpecified() { final String sql = "create table foo (bar integer not null, baz varchar(30))"; final String expected = "CREATE TABLE `FOO` (`BAR` INTEGER NOT NULL, `BAZ` VARCHAR(30))"; diff --git a/babel/src/test/java/org/apache/calcite/test/BabelTest.java b/babel/src/test/java/org/apache/calcite/test/BabelTest.java index 0d686f8..64301cf 100644 --- a/babel/src/test/java/org/apache/calcite/test/BabelTest.java +++ b/babel/src/test/java/org/apache/calcite/test/BabelTest.java @@ -129,4 +129,35 @@ class BabelTest { p.sql("select 1 ^:^: integer as x") .fails("(?s).*Encountered \":\" at .*"); } + + @Test void testNullSafeEqual() { + // x <=> y + checkSqlResult("mysql", "SELECT 1 <=> NULL", "EXPR$0=false\n"); + checkSqlResult("mysql", "SELECT NULL <=> NULL", "EXPR$0=true\n"); + // (a, b) <=> (x, y) + checkSqlResult("mysql", + "SELECT (CAST(NULL AS Integer), 1) <=> (1, CAST(NULL AS Integer))", + "EXPR$0=false\n"); + checkSqlResult("mysql", + "SELECT (CAST(NULL AS Integer), CAST(NULL AS Integer))\n" + + "<=> (CAST(NULL AS Integer), CAST(NULL AS Integer))", + "EXPR$0=true\n"); + // the higher precedence + checkSqlResult("mysql", + "SELECT x <=> 1 + 3 FROM (VALUES (1, 2)) as tbl(x,y)", + "EXPR$0=false\n"); + // the lower precedence + checkSqlResult("mysql", + "SELECT NOT x <=> 1 FROM (VALUES (1, 2)) as tbl(x,y)", + "EXPR$0=false\n"); + } + + private void checkSqlResult(String funLibrary, String query, String result) { + CalciteAssert.that() + .with(CalciteConnectionProperty.PARSER_FACTORY, + SqlBabelParserImpl.class.getName() + "#FACTORY") + .with(CalciteConnectionProperty.FUN, funLibrary) + .query(query) + .returns(result); + } } diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java index 0df5f8c..acec95f 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java @@ -19,6 +19,7 @@ package org.apache.calcite.sql.fun; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; import org.apache.calcite.sql.SqlAggFunction; +import org.apache.calcite.sql.SqlBinaryOperator; import org.apache.calcite.sql.SqlFunction; import org.apache.calcite.sql.SqlFunctionCategory; import org.apache.calcite.sql.SqlKind; @@ -706,4 +707,17 @@ public abstract class SqlLibraryOperators { @LibraryOperator(libraries = { POSTGRESQL }) public static final SqlOperator INFIX_CAST = new SqlCastOperator(); + + /** NULL-safe "<=>" equal operator used by MySQL, for example + * {@code 1<=>NULL}. */ + @LibraryOperator(libraries = { MYSQL }) + public static final SqlOperator NULL_SAFE_EQUAL = + new SqlBinaryOperator( + "<=>", + SqlKind.IS_NOT_DISTINCT_FROM, + 30, + true, + ReturnTypes.BOOLEAN, + InferTypes.FIRST_KNOWN, + OperandTypes.COMPARABLE_UNORDERED_COMPARABLE_UNORDERED); } diff --git a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java index a899677..686df63 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java @@ -117,6 +117,7 @@ public class StandardConvertletTable extends ReflectiveConvertletTable { SqlStdOperatorTable.IS_NULL); addAlias(SqlStdOperatorTable.IS_NOT_UNKNOWN, SqlStdOperatorTable.IS_NOT_NULL); + addAlias(SqlLibraryOperators.NULL_SAFE_EQUAL, SqlStdOperatorTable.IS_NOT_DISTINCT_FROM); addAlias(SqlStdOperatorTable.PERCENT_REMAINDER, SqlStdOperatorTable.MOD); // Register convertlets for specific objects. diff --git a/site/_docs/reference.md b/site/_docs/reference.md index 6a2a5d1..6ac180b 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -1203,13 +1203,13 @@ The operator precedence and associativity, highest to lowest. | * / % || | left | + - | left | BETWEEN, IN, LIKE, SIMILAR, OVERLAPS, CONTAINS etc. | - -| < > = <= >= <> != | left +| < > = <= >= <> != <=> | left | IS NULL, IS FALSE, IS NOT TRUE etc. | - | NOT | right | AND | left | OR | left -Note that `::` is dialect-specific, but is shown in this table for +Note that `::`,`<=>` is dialect-specific, but is shown in this table for completeness. ### Comparison operators @@ -1223,6 +1223,7 @@ completeness. | value1 >= value2 | Greater than or equal | value1 < value2 | Less than | value1 <= value2 | Less than or equal +| value1 <=> value2 | Whether two values are equal, treating null values as the same | value IS NULL | Whether *value* is null | value IS NOT NULL | Whether *value* is not null | value1 IS DISTINCT FROM value2 | Whether two values are not equal, treating null values as the same @@ -1251,6 +1252,7 @@ comp: | >= | < | <= + | <=> {% endhighlight %} ### Logical operators @@ -2513,6 +2515,7 @@ semantics. | C | Operator syntax | Description |:- |:-----------------------------------------------|:----------- | p | expr :: type | Casts *expr* to *type* +| m | expr1 <=> expr2 | Whether two values are equal, treating null values as the same, and it's similar to `IS NOT DISTINCT FROM` | b | ARRAY_CONCAT(array [, array ]*) | Concatenates one or more arrays. If any input argument is `NULL` the function returns `NULL` | b | ARRAY_LENGTH(array) | Synonym for `CARDINALITY` | b | ARRAY_REVERSE(array) | Reverses elements of *array*