You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by li...@apache.org on 2022/03/04 13:24:23 UTC
[calcite] 19/41: [CALCITE-4980] Babel parser support MySQL NULL-safe equal operator '<=>' (xurenhe&&DuanXiong)
This is an automated email from the ASF dual-hosted git repository.
liyafan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 28f41955f4dc20304f8c3da678d0b2d3e774ba16
Author: xurenhe <xu...@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*