[CALCITE-4415] SqlStdOperatorTable.NOT_LIKE has a wrong implementor

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.27.0
• Labels: pull-request-available
• Assignee: Ruben Q L
• Reporter: Ruben Q L
• Created: 20/Nov/20 17:30
• Updated: 03/Jun/21 22:31
• Resolved: 29/Nov/20 10:21

Description

SqlStdOperatorTable.NOT_LIKE's implementor defined in RexImpTable is currently the same as LIKE (i.e. NOT_LIKE performs the same operation as LIKE):

...
final MethodImplementor likeImplementor =
        new MethodImplementor(BuiltInMethod.LIKE.method, NullPolicy.STRICT, false);
map.put(LIKE, likeImplementor);
map.put(NOT_LIKE, likeImplementor);

It should be:

...
map.put(LIKE, likeImplementor);
map.put(NOT_LIKE, NotImplementor.of(likeImplementor));

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:

// 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()))));

However, creating a plan via RelBuilder using SqlStdOperatorTable.NOT_LIKE will lead to incorrect results.

Issue Links

• links to: GitHub Pull Request #2276
• links to: GitHub Pull Request #2279