[
https://issues.apache.org/jira/browse/CALCITE-4415?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17239449#comment-17239449
]
Julian Hyde commented on CALCITE-4415:
--------------------------------------
The following patch should solve the problem.
{noformat}
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index b61e4b8da..527467b4e 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -820,40 +820,53 @@ public SqlNode toSql(RexProgram program, RexNode rex) {
return toSql(program, (RexOver) rex);
}
- final RexCall call = (RexCall) stripCastFromString(rex, dialect);
- SqlOperator op = call.getOperator();
- switch (op.getKind()) {
- case SUM0:
- op = SqlStdOperatorTable.SUM;
- break;
- default:
- break;
+ return callToSql(program, (RexCall) rex, false);
+ }
+ }
+
+ private SqlNode callToSql(RexProgram program, RexCall rex, boolean not) {
+ final RexCall call = (RexCall) stripCastFromString(rex, dialect);
+ SqlOperator op = call.getOperator();
+ switch (op.getKind()) {
+ case SUM0:
+ op = SqlStdOperatorTable.SUM;
+ break;
+ case NOT:
+ RexNode operand = call.operands.get(0);
+ if (NOT_KIND_OPERATORS.containsKey(operand.getKind())) {
+ return callToSql(program, (RexCall) operand, !not);
}
- final List<SqlNode> nodeList = toSql(program, call.getOperands());
- switch (call.getKind()) {
- case CAST:
- // CURSOR is used inside CAST, like 'CAST ($0): CURSOR NOT NULL',
- // convert it to sql call of {@link SqlStdOperatorTable#CURSOR}.
- RelDataType dataType = rex.getType();
- if (dataType.getSqlTypeName() == SqlTypeName.CURSOR) {
- RexNode operand0 = ((RexCall) rex).operands.get(0);
- assert operand0 instanceof RexInputRef;
- int ordinal = ((RexInputRef) operand0).getIndex();
- SqlNode fieldOperand = field(ordinal);
- return SqlStdOperatorTable.CURSOR.createCall(SqlParserPos.ZERO,
fieldOperand);
- }
- if (ignoreCast) {
- assert nodeList.size() == 1;
- return nodeList.get(0);
- } else {
- nodeList.add(dialect.getCastSpec(call.getType()));
- }
- break;
- default:
- break;
+ break;
+ default:
+ break;
+ }
+ if (not) {
+ op = NOT_KIND_OPERATORS.get(op.getKind());
+ }
+ final List<SqlNode> nodeList = toSql(program, call.getOperands());
+ switch (call.getKind()) {
+ case CAST:
+ // CURSOR is used inside CAST, like 'CAST ($0): CURSOR NOT NULL',
+ // convert it to sql call of {@link SqlStdOperatorTable#CURSOR}.
+ RelDataType dataType = rex.getType();
+ if (dataType.getSqlTypeName() == SqlTypeName.CURSOR) {
+ RexNode operand0 = ((RexCall) rex).operands.get(0);
+ assert operand0 instanceof RexInputRef;
+ int ordinal = ((RexInputRef) operand0).getIndex();
+ SqlNode fieldOperand = field(ordinal);
+ return SqlStdOperatorTable.CURSOR.createCall(SqlParserPos.ZERO,
fieldOperand);
}
- return SqlUtil.createCall(op, POS, nodeList);
+ if (ignoreCast) {
+ assert nodeList.size() == 1;
+ return nodeList.get(0);
+ } else {
+ nodeList.add(dialect.getCastSpec(call.getType()));
+ }
+ break;
+ default:
+ break;
}
+ return SqlUtil.createCall(op, POS, nodeList);
}
/** Converts a Sarg to SQL, generating "operand IN (c1, c2, ...)" if the
{noformat}
> SqlStdOperatorTable.NOT_LIKE has a wrong implementor
> ----------------------------------------------------
>
> Key: CALCITE-4415
> URL: https://issues.apache.org/jira/browse/CALCITE-4415
> Project: Calcite
> Issue Type: Bug
> Reporter: Ruben Q L
> Assignee: Ruben Q L
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.27.0
>
> Time Spent: 40m
> Remaining Estimate: 0h
>
> {{SqlStdOperatorTable.NOT_LIKE}}'s implementor defined in {{RexImpTable}} is
> currently the same as {{LIKE}} (i.e. {{NOT_LIKE}} performs the same operation
> as {{LIKE}}):
> {code}
> ...
> final MethodImplementor likeImplementor =
> new MethodImplementor(BuiltInMethod.LIKE.method, NullPolicy.STRICT,
> false);
> map.put(LIKE, likeImplementor);
> map.put(NOT_LIKE, likeImplementor);
> {code}
> It should be:
> {code}
> ...
> map.put(LIKE, likeImplementor);
> map.put(NOT_LIKE, NotImplementor.of(likeImplementor));
> {code}
> Luckily, SQL queries seem to not suffer the consequences because
> {{StandardConvertletTable}} expands {{x NOT LIKE y}} into {{NOT (x LIKE y)}},
> so the issue is avoided:
> {code}
> // Expand "x NOT LIKE y" into "NOT (x LIKE y)"
> registerOp(SqlStdOperatorTable.NOT_LIKE,
> (cx, call) -> cx.convertExpression(
> SqlStdOperatorTable.NOT.createCall(SqlParserPos.ZERO,
> SqlStdOperatorTable.LIKE.createCall(SqlParserPos.ZERO,
> call.getOperandList()))));
> {code}
> However, creating a plan via RelBuilder using
> {{SqlStdOperatorTable.NOT_LIKE}} will lead to incorrect results.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
