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
The following commit(s) were added to refs/heads/master by this push:
new 174a707 [CALCITE-4443] Add ILIKE operator (as LIKE, but
case-insensitive and PostgreSQL-specific) (Ondřej Štumpf)
174a707 is described below
commit 174a707e1c199c97d7cc3531f0cd2e94745f4366
Author: Ondřej Štumpf <[email protected]>
AuthorDate: Mon Dec 21 13:09:18 2020 +0100
[CALCITE-4443] Add ILIKE operator (as LIKE, but case-insensitive and
PostgreSQL-specific) (Ondřej Štumpf)
Add method SqlOperator.not(), and remove
SqlImplementor.NOT_KIND_OPERATORS map. (Julian Hyde)
In RelToSqlConverterTest, allow Set<SqlLibrary> as part of
test configuration. (Julian Hyde)
Close apache/calcite#2317
---
babel/src/main/codegen/config.fmpp | 1 +
core/src/main/codegen/default_config.fmpp | 1 +
core/src/main/codegen/templates/Parser.jj | 5 ++
.../calcite/adapter/enumerable/RexImpTable.java | 7 +-
.../apache/calcite/rel/rel2sql/SqlImplementor.java | 31 +++++----
.../java/org/apache/calcite/rex/RexSimplify.java | 55 ++++++++-------
.../org/apache/calcite/runtime/SqlFunctions.java | 12 ++++
.../main/java/org/apache/calcite/sql/SqlKind.java | 8 +++
.../java/org/apache/calcite/sql/SqlOperator.java | 15 ++++
.../org/apache/calcite/sql/SqlSpecialOperator.java | 4 +-
.../calcite/sql/dialect/PostgresqlSqlDialect.java | 14 ++++
.../calcite/sql/dialect/VerticaSqlDialect.java | 15 ++++
.../apache/calcite/sql/fun/SqlBetweenOperator.java | 21 ++++++
.../org/apache/calcite/sql/fun/SqlInOperator.java | 20 ++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 11 +++
.../apache/calcite/sql/fun/SqlLikeOperator.java | 70 ++++++++++++++++---
.../calcite/sql/fun/SqlPosixRegexOperator.java | 27 ++++++++
.../calcite/sql/fun/SqlStdOperatorTable.java | 36 +++++++---
.../apache/calcite/sql/parser/SqlParserUtil.java | 18 ++---
.../apache/calcite/sql/validate/SqlValidator.java | 5 +-
.../calcite/sql/validate/SqlValidatorImpl.java | 18 +++--
.../calcite/sql2rel/StandardConvertletTable.java | 7 ++
.../java/org/apache/calcite/tools/RelBuilder.java | 10 ++-
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../calcite/util/PrecedenceClimbingParser.java | 4 +-
.../rel/rel2sql/RelToSqlConverterStructsTest.java | 2 +-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 80 +++++++++++++++++-----
.../apache/calcite/sql/parser/SqlParserTest.java | 16 ++++-
.../apache/calcite/sql/test/SqlAdvisorTest.java | 1 +
.../calcite/sql/test/SqlOperatorBaseTest.java | 64 +++++++++++++++++
.../org/apache/calcite/test/RelBuilderTest.java | 19 ++++-
.../org/apache/calcite/test/SqlValidatorTest.java | 13 ++++
site/_docs/reference.md | 3 +
33 files changed, 507 insertions(+), 107 deletions(-)
diff --git a/babel/src/main/codegen/config.fmpp
b/babel/src/main/codegen/config.fmpp
index bb31a72..20cb864 100644
--- a/babel/src/main/codegen/config.fmpp
+++ b/babel/src/main/codegen/config.fmpp
@@ -237,6 +237,7 @@ data: {
"HOUR"
"IDENTITY"
# "IF" # not a keyword in Calcite
+ "ILIKE"
"IMMEDIATE"
"IMMEDIATELY"
"IMPORT"
diff --git a/core/src/main/codegen/default_config.fmpp
b/core/src/main/codegen/default_config.fmpp
index 00f89ed..d683329 100644
--- a/core/src/main/codegen/default_config.fmpp
+++ b/core/src/main/codegen/default_config.fmpp
@@ -130,6 +130,7 @@ parser: {
"HOP"
"HOURS"
"IGNORE"
+ "ILIKE"
"IMMEDIATE"
"IMMEDIATELY"
"IMPLEMENTATION"
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 3857911..9ea92ea 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -3588,11 +3588,15 @@ List<Object> Expression2(ExprContext exprContext) :
(
<LIKE> { op = SqlStdOperatorTable.NOT_LIKE; }
|
+ <ILIKE> { op = SqlLibraryOperators.NOT_ILIKE; }
+ |
<SIMILAR> <TO> { op =
SqlStdOperatorTable.NOT_SIMILAR_TO; }
)
|
<LIKE> { op = SqlStdOperatorTable.LIKE; }
|
+ <ILIKE> { op = SqlLibraryOperators.ILIKE; }
+ |
<SIMILAR> <TO> { op = SqlStdOperatorTable.SIMILAR_TO; }
)
<#if
(parser.includePosixOperators!default.parser.includePosixOperators)>
@@ -7447,6 +7451,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < HOURS: "HOURS" >
| < IDENTITY: "IDENTITY" >
| < IGNORE: "IGNORE" >
+| < ILIKE: "ILIKE" >
| < IMMEDIATE: "IMMEDIATE" >
| < IMMEDIATELY: "IMMEDIATELY" >
| < IMPLEMENTATION: "IMPLEMENTATION" >
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index eebac14..a39357a 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -120,6 +120,7 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.EXISTS_NODE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.EXTRACT_VALUE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.EXTRACT_XML;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.FROM_BASE64;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.ILIKE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_DEPTH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_KEYS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_LENGTH;
@@ -469,11 +470,15 @@ public class RexImpTable {
map.put(IS_FALSE, new IsFalseImplementor());
map.put(IS_NOT_FALSE, new IsNotFalseImplementor());
- // LIKE and SIMILAR
+ // LIKE, ILIKE and SIMILAR
final MethodImplementor likeImplementor =
new MethodImplementor(BuiltInMethod.LIKE.method, NullPolicy.STRICT,
false);
map.put(LIKE, likeImplementor);
+ final MethodImplementor ilikeImplementor =
+ new MethodImplementor(BuiltInMethod.ILIKE.method, NullPolicy.STRICT,
+ false);
+ map.put(ILIKE, ilikeImplementor);
final MethodImplementor similarImplementor =
new MethodImplementor(BuiltInMethod.SIMILAR.method, NullPolicy.STRICT,
false);
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 ff00cf7..8ccbbe2 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
@@ -141,16 +141,6 @@ public abstract class SqlImplementor {
final RexBuilder rexBuilder =
new RexBuilder(new SqlTypeFactoryImpl(RelDataTypeSystemImpl.DEFAULT));
- /** Maps a {@link SqlKind} to a {@link SqlOperator} that implements NOT
- * applied to that kind. */
- private static final Map<SqlKind, SqlOperator> NOT_KIND_OPERATORS =
- ImmutableMap.<SqlKind, SqlOperator>builder()
- .put(SqlKind.IN, SqlStdOperatorTable.NOT_IN)
- .put(SqlKind.NOT_IN, SqlStdOperatorTable.IN)
- .put(SqlKind.LIKE, SqlStdOperatorTable.NOT_LIKE)
- .put(SqlKind.SIMILAR, SqlStdOperatorTable.NOT_SIMILAR_TO)
- .build();
-
protected SqlImplementor(SqlDialect dialect) {
this.dialect = requireNonNull(dialect);
}
@@ -798,7 +788,7 @@ public abstract class SqlImplementor {
case NOT:
RexNode operand = ((RexCall) rex).operands.get(0);
final SqlNode node = toSql(program, operand);
- final SqlOperator inverseOperator =
NOT_KIND_OPERATORS.get(operand.getKind());
+ final SqlOperator inverseOperator = getInverseOperator(operand);
if (inverseOperator != null) {
switch (operand.getKind()) {
case IN:
@@ -832,7 +822,7 @@ public abstract class SqlImplementor {
break;
case NOT:
RexNode operand = call.operands.get(0);
- if (NOT_KIND_OPERATORS.containsKey(operand.getKind())) {
+ if (getInverseOperator(operand) != null) {
return callToSql(program, (RexCall) operand, !not);
}
break;
@@ -840,9 +830,8 @@ public abstract class SqlImplementor {
break;
}
if (not) {
- SqlKind kind = op.getKind();
- op = requireNonNull(NOT_KIND_OPERATORS.get(kind),
- () -> "unable to negate " + kind);
+ op = requireNonNull(getInverseOperator(call),
+ () -> "unable to negate " + call.getKind());
}
final List<SqlNode> nodeList = toSql(program, call.getOperands());
switch (call.getKind()) {
@@ -870,6 +859,18 @@ public abstract class SqlImplementor {
return SqlUtil.createCall(op, POS, nodeList);
}
+ /** If {@code node} is a {@link RexCall}, extracts the operator and
+ * finds the corresponding inverse operator using {@link
SqlOperator#not()}.
+ * Returns null if {@code node} is not a {@link RexCall},
+ * or if the operator has no logical inverse. */
+ private static @Nullable SqlOperator getInverseOperator(RexNode node) {
+ if (node instanceof RexCall) {
+ return ((RexCall) node).getOperator().not();
+ } else {
+ return null;
+ }
+ }
+
/** Converts a Sarg to SQL, generating "operand IN (c1, c2, ...)" if the
* ranges are all points. */
@SuppressWarnings({"BetaApi", "UnstableApiUsage"})
diff --git a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
index 9e05265..15c3d0d 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
@@ -582,10 +582,12 @@ public class RexSimplify {
private RexNode simplifyNot(RexCall call, RexUnknownAs unknownAs) {
final RexNode a = call.getOperands().get(0);
+ final List<RexNode> newOperands;
switch (a.getKind()) {
case NOT:
// NOT NOT x ==> x
return simplify(((RexCall) a).getOperands().get(0), unknownAs);
+
case SEARCH:
// NOT SEARCH(x, Sarg[(-inf, 10) OR NULL) ==> SEARCH(x, Sarg[[10, +inf)])
final RexCall call2 = (RexCall) a;
@@ -598,30 +600,17 @@ public class RexSimplify {
rexBuilder.makeLiteral(requireNonNull(sarg,
"sarg").negate(), literal.getType(),
literal.getTypeName()))),
unknownAs.negate());
+
case LITERAL:
if (a.getType().getSqlTypeName() == SqlTypeName.BOOLEAN
&& !RexLiteral.isNullLiteral(a)) {
return rexBuilder.makeLiteral(!RexLiteral.booleanValue(a));
}
break;
- default:
- break;
- }
- final SqlKind negateKind = a.getKind().negate();
- if (a.getKind() != negateKind) {
- return simplify(
- rexBuilder.makeCall(RexUtil.op(negateKind),
- ((RexCall) a).getOperands()), unknownAs);
- }
- final SqlKind negateKind2 = a.getKind().negateNullSafe();
- if (a.getKind() != negateKind2) {
- return simplify(
- rexBuilder.makeCall(RexUtil.op(negateKind2),
- ((RexCall) a).getOperands()), unknownAs);
- }
- if (a.getKind() == SqlKind.AND) {
+
+ case AND:
// NOT distributivity for AND
- final List<RexNode> newOperands = new ArrayList<>();
+ newOperands = new ArrayList<>();
for (RexNode operand : ((RexCall) a).getOperands()) {
newOperands.add(
simplify(rexBuilder.makeCall(SqlStdOperatorTable.NOT, operand),
@@ -629,10 +618,10 @@ public class RexSimplify {
}
return simplify(
rexBuilder.makeCall(SqlStdOperatorTable.OR, newOperands), unknownAs);
- }
- if (a.getKind() == SqlKind.OR) {
+
+ case OR:
// NOT distributivity for OR
- final List<RexNode> newOperands = new ArrayList<>();
+ newOperands = new ArrayList<>();
for (RexNode operand : ((RexCall) a).getOperands()) {
newOperands.add(
simplify(rexBuilder.makeCall(SqlStdOperatorTable.NOT, operand),
@@ -640,9 +629,9 @@ public class RexSimplify {
}
return simplify(
rexBuilder.makeCall(SqlStdOperatorTable.AND, newOperands),
unknownAs);
- }
- if (a.getKind() == SqlKind.CASE) {
- final List<RexNode> newOperands = new ArrayList<>();
+
+ case CASE:
+ newOperands = new ArrayList<>();
List<RexNode> operands = ((RexCall) a).getOperands();
for (int i = 0; i < operands.size(); i += 2) {
if (i + 1 == operands.size()) {
@@ -654,7 +643,27 @@ public class RexSimplify {
}
return simplify(
rexBuilder.makeCall(SqlStdOperatorTable.CASE, newOperands),
unknownAs);
+
+ case IN:
+ case NOT_IN:
+ // do not try to negate
+ break;
+
+ default:
+ final SqlKind negateKind = a.getKind().negate();
+ if (a.getKind() != negateKind) {
+ return simplify(
+ rexBuilder.makeCall(RexUtil.op(negateKind),
+ ((RexCall) a).getOperands()), unknownAs);
+ }
+ final SqlKind negateKind2 = a.getKind().negateNullSafe();
+ if (a.getKind() != negateKind2) {
+ return simplify(
+ rexBuilder.makeCall(RexUtil.op(negateKind2),
+ ((RexCall) a).getOperands()), unknownAs);
+ }
}
+
RexNode a2 = simplify(a, unknownAs.negate());
if (a == a2) {
return call;
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index eaab70b..afcff87 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -607,6 +607,18 @@ public class SqlFunctions {
return Pattern.matches(regex, s);
}
+ /** SQL {@code ILIKE} function. */
+ public static boolean ilike(String s, String pattern) {
+ final String regex = Like.sqlToRegexLike(pattern, null);
+ return Pattern.compile(regex,
Pattern.CASE_INSENSITIVE).matcher(s).matches();
+ }
+
+ /** SQL {@code ILIKE} function with escape. */
+ public static boolean ilike(String s, String pattern, String escape) {
+ final String regex = Like.sqlToRegexLike(pattern, escape);
+ return Pattern.compile(regex,
Pattern.CASE_INSENSITIVE).matcher(s).matches();
+ }
+
/** SQL {@code SIMILAR} function. */
public static boolean similar(String s, String pattern) {
final String regex = Like.sqlToRegexSimilar(pattern, null);
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index aa18bfe..d7a6493 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -1385,6 +1385,14 @@ public enum SqlKind {
return GREATER_THAN;
case GREATER_THAN_OR_EQUAL:
return LESS_THAN;
+ case IN:
+ return NOT_IN;
+ case NOT_IN:
+ return IN;
+ case DRUID_IN:
+ return DRUID_NOT_IN;
+ case DRUID_NOT_IN:
+ return DRUID_IN;
case IS_TRUE:
return IS_FALSE;
case IS_FALSE:
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlOperator.java
b/core/src/main/java/org/apache/calcite/sql/SqlOperator.java
index 3534282..b7e00d8 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlOperator.java
@@ -595,6 +595,10 @@ public abstract class SqlOperator {
argTypes, null, null, getSyntax(), getKind(),
validator.getCatalogReader().nameMatcher(), false);
+ if (sqlOperator == null) {
+ throw validator.handleUnresolvedFunction(call, this, argTypes, null);
+ }
+
((SqlBasicCall) call).setOperator(castNonNull(sqlOperator));
RelDataType type = call.getOperator().validateOperands(validator, scope,
call);
@@ -955,6 +959,17 @@ public abstract class SqlOperator {
return returnTypeInference;
}
+ /** Returns the operator that is the logical inverse of this operator.
+ *
+ * <p>For example, {@code SqlStdOperatorTable.LIKE.not()} returns
+ * {@code SqlStdOperatorTable.NOT_LIKE}, and vice versa.
+ *
+ * <p>By default, returns {@code null}, which means there is no inverse
+ * operator. */
+ public @Nullable SqlOperator not() {
+ return null;
+ }
+
/**
* Returns the {@link Strong.Policy} strategy for this operator, or null if
* there is no particular strategy, in which case this policy will be
deducted
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSpecialOperator.java
b/core/src/main/java/org/apache/calcite/sql/SqlSpecialOperator.java
index 4f9b9db..a37ec2b 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlSpecialOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlSpecialOperator.java
@@ -99,8 +99,8 @@ public class SqlSpecialOperator extends SqlOperator {
SqlOperator op(int i);
SqlParserPos pos(int i);
boolean isOp(int i);
- @Nullable SqlNode node(int i);
- void replaceSublist(int start, int end, @Nullable SqlNode e);
+ SqlNode node(int i);
+ void replaceSublist(int start, int end, SqlNode e);
/** Creates a parser whose token sequence is a copy of a subset of this
* token sequence. */
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
index cb87373..87137c8 100644
---
a/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
+++
b/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
@@ -27,6 +27,7 @@ import org.apache.calcite.sql.SqlDataTypeSpec;
import org.apache.calcite.sql.SqlDialect;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.fun.SqlFloorFunction;
import org.apache.calcite.sql.parser.SqlParserPos;
@@ -34,6 +35,8 @@ import org.apache.calcite.sql.type.SqlTypeName;
import org.checkerframework.checker.nullness.qual.Nullable;
+import java.util.List;
+
/**
* A <code>SqlDialect</code> implementation for the PostgreSQL database.
*/
@@ -94,6 +97,17 @@ public class PostgresqlSqlDialect extends SqlDialect {
SqlParserPos.ZERO);
}
+ @Override public boolean supportsFunction(SqlOperator operator,
+ RelDataType type, final List<RelDataType> paramTypes) {
+ switch (operator.kind) {
+ case LIKE:
+ // introduces support for ILIKE as well
+ return true;
+ default:
+ return super.supportsFunction(operator, type, paramTypes);
+ }
+ }
+
@Override public boolean requiresAliasForFromItems() {
return true;
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/VerticaSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/VerticaSqlDialect.java
index d1cc8f8..3c31d7f 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/VerticaSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/VerticaSqlDialect.java
@@ -17,7 +17,11 @@
package org.apache.calcite.sql.dialect;
import org.apache.calcite.avatica.util.Casing;
+import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.sql.SqlDialect;
+import org.apache.calcite.sql.SqlOperator;
+
+import java.util.List;
/**
* A <code>SqlDialect</code> implementation for the Vertica database.
@@ -38,4 +42,15 @@ public class VerticaSqlDialect extends SqlDialect {
@Override public boolean supportsNestedAggregations() {
return false;
}
+
+ @Override public boolean supportsFunction(SqlOperator operator,
+ RelDataType type, final List<RelDataType> paramTypes) {
+ switch (operator.kind) {
+ case LIKE:
+ // introduces support for ILIKE as well
+ return true;
+ default:
+ return super.supportsFunction(operator, type, paramTypes);
+ }
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlBetweenOperator.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlBetweenOperator.java
index 685a12d..1030eca 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlBetweenOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlBetweenOperator.java
@@ -117,10 +117,31 @@ public class SqlBetweenOperator extends SqlInfixOperator {
return litmus.fail("not a rex operator");
}
+ /**
+ * Returns whether this is 'NOT' variant of an operator.
+ *
+ * @see #not()
+ */
public boolean isNegated() {
return negated;
}
+ @Override public SqlOperator not() {
+ return of(negated, flag == Flag.SYMMETRIC);
+ }
+
+ private static SqlBetweenOperator of(boolean negated, boolean symmetric) {
+ if (symmetric) {
+ return negated
+ ? SqlStdOperatorTable.SYMMETRIC_BETWEEN
+ : SqlStdOperatorTable.SYMMETRIC_NOT_BETWEEN;
+ } else {
+ return negated
+ ? SqlStdOperatorTable.NOT_BETWEEN
+ : SqlStdOperatorTable.BETWEEN;
+ }
+ }
+
@Override public RelDataType inferReturnType(
SqlOperatorBinding opBinding) {
ExplicitOperatorBinding newOpBinding =
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlInOperator.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlInOperator.java
index d4d2819..8a43e70 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlInOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlInOperator.java
@@ -26,6 +26,7 @@ import org.apache.calcite.sql.SqlCallBinding;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.type.ComparableOperandTypeChecker;
import org.apache.calcite.sql.type.InferTypes;
import org.apache.calcite.sql.type.OperandTypes;
@@ -80,6 +81,25 @@ public class SqlInOperator extends SqlBinaryOperator {
return kind == SqlKind.NOT_IN;
}
+ @Override public SqlOperator not() {
+ return of(kind.negateNullSafe());
+ }
+
+ private static SqlBinaryOperator of(SqlKind kind) {
+ switch (kind) {
+ case IN:
+ return SqlStdOperatorTable.IN;
+ case NOT_IN:
+ return SqlStdOperatorTable.NOT_IN;
+ case DRUID_IN:
+ return SqlInternalOperators.DRUID_IN;
+ case DRUID_NOT_IN:
+ return SqlInternalOperators.DRUID_NOT_IN;
+ default:
+ throw new AssertionError("unexpected " + kind);
+ }
+ }
+
@Override public boolean validRexOperands(int count, Litmus litmus) {
if (count == 0) {
return litmus.fail("wrong operand count {} for {}", count, this);
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 d5cdecc..911fc21 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
@@ -25,6 +25,7 @@ import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlOperatorBinding;
import org.apache.calcite.sql.SqlOperatorTable;
+import org.apache.calcite.sql.SqlSpecialOperator;
import org.apache.calcite.sql.SqlSyntax;
import org.apache.calcite.sql.type.InferTypes;
import org.apache.calcite.sql.type.OperandTypes;
@@ -460,6 +461,16 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING_STRING,
SqlFunctionCategory.STRING);
+ /** The case-insensitive variant of the LIKE operator. */
+ @LibraryOperator(libraries = {POSTGRESQL})
+ public static final SqlSpecialOperator ILIKE =
+ new SqlLikeOperator("ILIKE", SqlKind.LIKE, false, false);
+
+ /** The case-insensitive variant of the NOT LIKE operator. */
+ @LibraryOperator(libraries = {POSTGRESQL})
+ public static final SqlSpecialOperator NOT_ILIKE =
+ new SqlLikeOperator("NOT ILIKE", SqlKind.LIKE, true, false);
+
/** The "CONCAT(arg, ...)" function that concatenates strings.
* For example, "CONCAT('a', 'bc', 'd')" returns "abcd". */
@LibraryOperator(libraries = {MYSQL, POSTGRESQL})
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlLikeOperator.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLikeOperator.java
index 754db45..ce5cf82 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLikeOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLikeOperator.java
@@ -31,10 +31,10 @@ import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.sql.type.SqlOperandCountRanges;
import org.apache.calcite.sql.type.SqlTypeUtil;
+import org.apache.calcite.sql.validate.SqlValidator;
+import org.apache.calcite.sql.validate.SqlValidatorScope;
import org.apache.calcite.util.Litmus;
-import org.checkerframework.checker.nullness.qual.Nullable;
-
/**
* An operator describing the <code>LIKE</code> and <code>SIMILAR</code>
* operators.
@@ -56,20 +56,23 @@ public class SqlLikeOperator extends SqlSpecialOperator {
//~ Instance fields --------------------------------------------------------
private final boolean negated;
+ private final boolean caseSensitive;
//~ Constructors -----------------------------------------------------------
/**
* Creates a SqlLikeOperator.
*
- * @param name Operator name
- * @param kind Kind
- * @param negated Whether this is 'NOT LIKE'
+ * @param name Operator name
+ * @param kind Kind
+ * @param negated Whether this is 'NOT LIKE'
+ * @param caseSensitive Whether this operator ignores the case of its
operands
*/
SqlLikeOperator(
String name,
SqlKind kind,
- boolean negated) {
+ boolean negated,
+ boolean caseSensitive) {
// LIKE is right-associative, because that makes it easier to capture
// dangling ESCAPE clauses: "a like b like c escape d" becomes
// "a like (b like c escape d)".
@@ -81,7 +84,13 @@ public class SqlLikeOperator extends SqlSpecialOperator {
ReturnTypes.BOOLEAN_NULLABLE,
InferTypes.FIRST_KNOWN,
OperandTypes.STRING_SAME_SAME_SAME);
+ if (!caseSensitive && kind != SqlKind.LIKE) {
+ throw new IllegalArgumentException("Only (possibly negated) "
+ + SqlKind.LIKE + " can be made case-insensitive, not " + kind);
+ }
+
this.negated = negated;
+ this.caseSensitive = caseSensitive;
}
//~ Methods ----------------------------------------------------------------
@@ -90,11 +99,49 @@ public class SqlLikeOperator extends SqlSpecialOperator {
* Returns whether this is the 'NOT LIKE' operator.
*
* @return whether this is 'NOT LIKE'
+ *
+ * @see #not()
*/
public boolean isNegated() {
return negated;
}
+ /**
+ * Returns whether this operator matches the case of its operands.
+ * For example, returns true for {@code LIKE} and false for {@code ILIKE}.
+ *
+ * @return whether this operator matches the case of its operands
+ */
+ public boolean isCaseSensitive() {
+ return caseSensitive;
+ }
+
+ @Override public SqlOperator not() {
+ return of(kind, !negated, caseSensitive);
+ }
+
+ private static SqlOperator of(SqlKind kind, boolean negated,
+ boolean caseSensitive) {
+ switch (kind) {
+ case SIMILAR:
+ return negated
+ ? SqlStdOperatorTable.NOT_SIMILAR_TO
+ : SqlStdOperatorTable.SIMILAR_TO;
+ case LIKE:
+ if (caseSensitive) {
+ return negated
+ ? SqlStdOperatorTable.NOT_LIKE
+ : SqlStdOperatorTable.LIKE;
+ } else {
+ return negated
+ ? SqlLibraryOperators.NOT_ILIKE
+ : SqlLibraryOperators.ILIKE;
+ }
+ default:
+ throw new AssertionError("unexpected " + kind);
+ }
+ }
+
@Override public SqlOperandCountRange getOperandCountRange() {
return SqlOperandCountRanges.between(2, 3);
}
@@ -131,6 +178,11 @@ public class SqlLikeOperator extends SqlSpecialOperator {
throwOnFailure);
}
+ @Override public void validateCall(SqlCall call, SqlValidator validator,
+ SqlValidatorScope scope, SqlValidatorScope operandScope) {
+ super.validateCall(call, validator, scope, operandScope);
+ }
+
@Override public boolean validRexOperands(int count, Litmus litmus) {
if (negated) {
litmus.fail("unsupported negated operator {}", this);
@@ -185,8 +237,8 @@ public class SqlLikeOperator extends SqlSpecialOperator {
}
}
}
- final @Nullable SqlNode[] operands;
- int end;
+ final SqlNode[] operands;
+ final int end;
if (exp2 != null) {
operands = new SqlNode[]{exp0, exp1, exp2};
end = opOrdinal + 4;
@@ -194,7 +246,7 @@ public class SqlLikeOperator extends SqlSpecialOperator {
operands = new SqlNode[]{exp0, exp1};
end = opOrdinal + 2;
}
- SqlCall call = createCall(SqlParserPos.ZERO, operands);
+ SqlCall call = createCall(SqlParserPos.sum(operands), operands);
return new ReduceResult(opOrdinal - 1, end, call);
}
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlPosixRegexOperator.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlPosixRegexOperator.java
index fe6c204..c3c519b 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlPosixRegexOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlPosixRegexOperator.java
@@ -22,6 +22,7 @@ import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlCallBinding;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlOperandCountRange;
+import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.type.InferTypes;
import org.apache.calcite.sql.type.OperandTypes;
@@ -78,6 +79,22 @@ public class SqlPosixRegexOperator extends SqlBinaryOperator
{
// ~ Methods ----------------------------------------------------------------
+ @Override public SqlOperator not() {
+ return of(!negated, caseSensitive);
+ }
+
+ private static SqlOperator of(boolean negated, boolean ignoreCase) {
+ if (ignoreCase) {
+ return negated
+ ? SqlStdOperatorTable.NEGATED_POSIX_REGEX_CASE_SENSITIVE
+ : SqlStdOperatorTable.POSIX_REGEX_CASE_SENSITIVE;
+ } else {
+ return negated
+ ? SqlStdOperatorTable.NEGATED_POSIX_REGEX_CASE_INSENSITIVE
+ : SqlStdOperatorTable.POSIX_REGEX_CASE_INSENSITIVE;
+ }
+ }
+
@Override public SqlOperandCountRange getOperandCountRange() {
return SqlOperandCountRanges.of(2);
}
@@ -120,10 +137,20 @@ public class SqlPosixRegexOperator extends
SqlBinaryOperator {
writer.endList(frame);
}
+ /**
+ * Returns whether this operator matches the case of its operands.
+ *
+ * @return whether this operator matches the case of its operands
+ */
public boolean isCaseSensitive() {
return caseSensitive;
}
+ /**
+ * Returns whether this is 'NOT' variant of an operator.
+ *
+ * @see #not()
+ */
public boolean isNegated() {
return negated;
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index a34098e..8521225 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -1399,22 +1399,24 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
true);
public static final SqlSpecialOperator NOT_LIKE =
- new SqlLikeOperator("NOT LIKE", SqlKind.LIKE, true);
+ new SqlLikeOperator("NOT LIKE", SqlKind.LIKE, true, true);
public static final SqlSpecialOperator LIKE =
- new SqlLikeOperator("LIKE", SqlKind.LIKE, false);
+ new SqlLikeOperator("LIKE", SqlKind.LIKE, false, true);
public static final SqlSpecialOperator NOT_SIMILAR_TO =
- new SqlLikeOperator("NOT SIMILAR TO", SqlKind.SIMILAR, true);
+ new SqlLikeOperator("NOT SIMILAR TO", SqlKind.SIMILAR, true, true);
public static final SqlSpecialOperator SIMILAR_TO =
- new SqlLikeOperator("SIMILAR TO", SqlKind.SIMILAR, false);
+ new SqlLikeOperator("SIMILAR TO", SqlKind.SIMILAR, false, true);
- public static final SqlBinaryOperator POSIX_REGEX_CASE_SENSITIVE = new
SqlPosixRegexOperator(
- "POSIX REGEX CASE SENSITIVE", SqlKind.POSIX_REGEX_CASE_SENSITIVE, true,
false);
+ public static final SqlBinaryOperator POSIX_REGEX_CASE_SENSITIVE =
+ new SqlPosixRegexOperator("POSIX REGEX CASE SENSITIVE",
+ SqlKind.POSIX_REGEX_CASE_SENSITIVE, true, false);
- public static final SqlBinaryOperator POSIX_REGEX_CASE_INSENSITIVE = new
SqlPosixRegexOperator(
- "POSIX REGEX CASE INSENSITIVE", SqlKind.POSIX_REGEX_CASE_INSENSITIVE,
false, false);
+ public static final SqlBinaryOperator POSIX_REGEX_CASE_INSENSITIVE =
+ new SqlPosixRegexOperator("POSIX REGEX CASE INSENSITIVE",
+ SqlKind.POSIX_REGEX_CASE_INSENSITIVE, false, false);
public static final SqlBinaryOperator NEGATED_POSIX_REGEX_CASE_SENSITIVE =
new SqlPosixRegexOperator("NEGATED POSIX REGEX CASE SENSITIVE",
@@ -2619,4 +2621,22 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
}
}
+ /** Returns the operator for {@code LIKE} with given case-sensitivity,
+ * optionally negated. */
+ public static SqlOperator like(boolean negated, boolean caseSensitive) {
+ if (negated) {
+ if (caseSensitive) {
+ return NOT_LIKE;
+ } else {
+ return SqlLibraryOperators.NOT_ILIKE;
+ }
+ } else {
+ if (caseSensitive) {
+ return LIKE;
+ } else {
+ return SqlLibraryOperators.ILIKE;
+ }
+ }
+ }
+
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/parser/SqlParserUtil.java
b/core/src/main/java/org/apache/calcite/sql/parser/SqlParserUtil.java
index 87d6cd2..f697205 100644
--- a/core/src/main/java/org/apache/calcite/sql/parser/SqlParserUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/parser/SqlParserUtil.java
@@ -653,7 +653,7 @@ public final class SqlParserUtil {
* we encounter a token of this kind.
* @return the root node of the tree which the list condenses into
*/
- public static @Nullable SqlNode toTreeEx(SqlSpecialOperator.TokenSequence
list,
+ public static SqlNode toTreeEx(SqlSpecialOperator.TokenSequence list,
int start, final int minPrec, final SqlKind stopperKind) {
PrecedenceClimbingParser parser = list.parser(start,
token -> {
@@ -676,10 +676,10 @@ public final class SqlParserUtil {
return node;
}
- private static @Nullable SqlNode convert(PrecedenceClimbingParser.Token
token) {
+ private static SqlNode convert(PrecedenceClimbingParser.Token token) {
switch (token.type) {
case ATOM:
- return (SqlNode) token.o;
+ return requireNonNull((SqlNode) token.o);
case CALL:
final PrecedenceClimbingParser.Call call =
(PrecedenceClimbingParser.Call) token;
@@ -865,11 +865,11 @@ public final class SqlParserUtil {
return list.get(i).o instanceof ToTreeListItem;
}
- @Override public @Nullable SqlNode node(int i) {
+ @Override public SqlNode node(int i) {
return convert(list.get(i));
}
- @Override public void replaceSublist(int start, int end, @Nullable SqlNode
e) {
+ @Override public void replaceSublist(int start, int end, SqlNode e) {
SqlParserUtil.replaceSublist(list, start, end, parser.atom(e));
}
}
@@ -918,7 +918,7 @@ public final class SqlParserUtil {
throw new AssertionError();
}
} else {
- builder.atom(o);
+ builder.atom(requireNonNull(o));
}
}
return builder.build();
@@ -946,11 +946,11 @@ public final class SqlParserUtil {
return list.get(i) instanceof ToTreeListItem;
}
- @Override public @Nullable SqlNode node(int i) {
- return (@Nullable SqlNode) list.get(i);
+ @Override public SqlNode node(int i) {
+ return requireNonNull((SqlNode) list.get(i));
}
- @Override public void replaceSublist(int start, int end, @Nullable SqlNode
e) {
+ @Override public void replaceSublist(int start, int end, SqlNode e) {
SqlParserUtil.replaceSublist(list, start, end, e);
}
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
index 17dd0c6..95e07be 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidator.java
@@ -36,6 +36,7 @@ import org.apache.calcite.sql.SqlMatchRecognize;
import org.apache.calcite.sql.SqlMerge;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlOperatorTable;
import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.SqlUpdate;
@@ -655,7 +656,7 @@ public interface SqlValidator {
List<RelDataType> argTypes);
/**
- * Handles a call to a function which cannot be resolved. Returns a an
+ * Handles a call to a function which cannot be resolved. Returns an
* appropriately descriptive error, which caller must throw.
*
* @param call Call
@@ -665,7 +666,7 @@ public interface SqlValidator {
* @param argNames Names of arguments, or null if call by position
*/
CalciteException handleUnresolvedFunction(SqlCall call,
- SqlFunction unresolvedFunction, List<RelDataType> argTypes,
+ SqlOperator unresolvedFunction, List<RelDataType> argTypes,
@Nullable List<String> argNames);
/**
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index eb9af34..7419bf1 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -1919,7 +1919,7 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
}
@Override public CalciteException handleUnresolvedFunction(SqlCall call,
- SqlFunction unresolvedFunction, List<RelDataType> argTypes,
+ SqlOperator unresolvedFunction, List<RelDataType> argTypes,
@Nullable List<String> argNames) {
// For builtins, we can give a better error message
final List<SqlOperator> overloads = new ArrayList<>();
@@ -1937,12 +1937,16 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
}
}
- AssignableOperandTypeChecker typeChecking =
- new AssignableOperandTypeChecker(argTypes, argNames);
- String signature =
- typeChecking.getAllowedSignatures(
- unresolvedFunction,
- unresolvedFunction.getName());
+ final String signature;
+ if (unresolvedFunction instanceof SqlFunction) {
+ final SqlOperandTypeChecker typeChecking =
+ new AssignableOperandTypeChecker(argTypes, argNames);
+ signature =
+ typeChecking.getAllowedSignatures(unresolvedFunction,
+ unresolvedFunction.getName());
+ } else {
+ signature = unresolvedFunction.getName();
+ }
throw newValidationError(call,
RESOURCE.validatorUnknownFunction(signature));
}
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 640b37f..6d062bd 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -172,6 +172,13 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
SqlStdOperatorTable.LIKE.createCall(SqlParserPos.ZERO,
call.getOperandList()))));
+ // Expand "x NOT ILIKE y" into "NOT (x ILIKE y)"
+ registerOp(SqlLibraryOperators.NOT_ILIKE,
+ (cx, call) -> cx.convertExpression(
+ SqlStdOperatorTable.NOT.createCall(SqlParserPos.ZERO,
+ SqlLibraryOperators.ILIKE.createCall(SqlParserPos.ZERO,
+ call.getOperandList()))));
+
// Expand "x NOT SIMILAR y" into "NOT (x SIMILAR y)"
registerOp(SqlStdOperatorTable.NOT_SIMILAR_TO,
(cx, call) -> cx.convertExpression(
diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
index 50b6bdf..de89160 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -640,13 +640,11 @@ public class RelBuilder {
private RexCall call(SqlOperator operator, List<RexNode> operandList) {
switch (operator.getKind()) {
case LIKE:
- if (((SqlLikeOperator) operator).isNegated()) {
- return (RexCall) not(call(SqlStdOperatorTable.LIKE, operandList));
- }
- break;
case SIMILAR:
- if (((SqlLikeOperator) operator).isNegated()) {
- return (RexCall) not(call(SqlStdOperatorTable.SIMILAR_TO,
operandList));
+ final SqlLikeOperator likeOperator = (SqlLikeOperator) operator;
+ if (likeOperator.isNegated()) {
+ final SqlOperator notLikeOperator = likeOperator.not();
+ return (RexCall) not(call(notLikeOperator, operandList));
}
break;
case BETWEEN:
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 38f42cd..84008a6 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -419,6 +419,7 @@ public enum BuiltInMethod {
LTRIM(SqlFunctions.class, "ltrim", String.class),
RTRIM(SqlFunctions.class, "rtrim", String.class),
LIKE(SqlFunctions.class, "like", String.class, String.class),
+ ILIKE(SqlFunctions.class, "ilike", String.class, String.class),
SIMILAR(SqlFunctions.class, "similar", String.class, String.class),
POSIX_REGEX(SqlFunctions.class, "posixRegex", String.class, String.class,
boolean.class),
REGEXP_REPLACE3(SqlFunctions.class, "regexpReplace", String.class,
diff --git
a/core/src/main/java/org/apache/calcite/util/PrecedenceClimbingParser.java
b/core/src/main/java/org/apache/calcite/util/PrecedenceClimbingParser.java
index 41f451a..6da09f6 100644
--- a/core/src/main/java/org/apache/calcite/util/PrecedenceClimbingParser.java
+++ b/core/src/main/java/org/apache/calcite/util/PrecedenceClimbingParser.java
@@ -55,7 +55,7 @@ public class PrecedenceClimbingParser {
last = p;
}
- public Token atom(@Nullable Object o) {
+ public Token atom(Object o) {
return new Token(Type.ATOM, o, -1, -1);
}
@@ -384,7 +384,7 @@ public class PrecedenceClimbingParser {
return this;
}
- public Builder atom(@Nullable Object o) {
+ public Builder atom(Object o) {
return add(dummy.atom(o));
}
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterStructsTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterStructsTest.java
index b6a2109..bbbadb8 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterStructsTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterStructsTest.java
@@ -164,7 +164,7 @@ class RelToSqlConverterStructsTest {
private RelToSqlConverterTest.Sql sql(String sql) {
return new RelToSqlConverterTest.Sql(ROOT_SCHEMA, sql,
- CalciteSqlDialect.DEFAULT, SqlParser.Config.DEFAULT,
+ CalciteSqlDialect.DEFAULT, SqlParser.Config.DEFAULT, ImmutableSet.of(),
UnaryOperator.identity(), null, ImmutableList.of());
}
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index d94d7c4..2defcaf 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -54,10 +54,13 @@ import org.apache.calcite.sql.dialect.MssqlSqlDialect;
import org.apache.calcite.sql.dialect.MysqlSqlDialect;
import org.apache.calcite.sql.dialect.OracleSqlDialect;
import org.apache.calcite.sql.dialect.PostgresqlSqlDialect;
+import org.apache.calcite.sql.fun.SqlLibrary;
+import org.apache.calcite.sql.fun.SqlLibraryOperatorTableFactory;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.type.SqlTypeFactoryImpl;
import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.util.SqlOperatorTables;
import org.apache.calcite.sql.util.SqlShuttle;
import org.apache.calcite.sql.validate.SqlConformance;
import org.apache.calcite.sql2rel.SqlToRelConverter;
@@ -77,11 +80,14 @@ import org.apache.calcite.util.Util;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
+import com.google.common.collect.ImmutableSet;
import org.junit.jupiter.api.Test;
+import java.util.Collection;
import java.util.List;
import java.util.Map;
+import java.util.Set;
import java.util.function.Function;
import java.util.function.UnaryOperator;
import java.util.stream.Collectors;
@@ -103,7 +109,7 @@ class RelToSqlConverterTest {
/** Initiates a test case with a given SQL query. */
private Sql sql(String sql) {
return new Sql(CalciteAssert.SchemaSpec.JDBC_FOODMART, sql,
- CalciteSqlDialect.DEFAULT, SqlParser.Config.DEFAULT,
+ CalciteSqlDialect.DEFAULT, SqlParser.Config.DEFAULT, ImmutableSet.of(),
UnaryOperator.identity(), null, ImmutableList.of());
}
@@ -114,9 +120,13 @@ class RelToSqlConverterTest {
private static Planner getPlanner(List<RelTraitDef> traitDefs,
SqlParser.Config parserConfig, SchemaPlus schema,
- SqlToRelConverter.Config sqlToRelConf, Program... programs) {
+ SqlToRelConverter.Config sqlToRelConf, Collection<SqlLibrary> librarySet,
+ Program... programs) {
final MockSqlOperatorTable operatorTable =
- new MockSqlOperatorTable(SqlStdOperatorTable.instance());
+ new MockSqlOperatorTable(
+ SqlOperatorTables.chain(SqlStdOperatorTable.instance(),
+ SqlLibraryOperatorTableFactory.INSTANCE
+ .getOperatorTable(librarySet)));
MockSqlOperatorTable.addRamp(operatorTable);
final FrameworkConfig config = Frameworks.newConfigBuilder()
.parserConfig(parserConfig)
@@ -3599,6 +3609,24 @@ class RelToSqlConverterTest {
sql(query).ok(expected);
}
+ @Test void testIlike() {
+ String query = "select \"product_name\" from \"product\" a "
+ + "where \"product_name\" ilike 'abC'";
+ String expected = "SELECT \"product_name\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "WHERE \"product_name\" ILIKE 'abC'";
+ sql(query).withLibrary(SqlLibrary.POSTGRESQL).ok(expected);
+ }
+
+ @Test void testNotIlike() {
+ String query = "select \"product_name\" from \"product\" a "
+ + "where \"product_name\" not ilike 'abC'";
+ String expected = "SELECT \"product_name\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "WHERE \"product_name\" NOT ILIKE 'abC'";
+ sql(query).withLibrary(SqlLibrary.POSTGRESQL).ok(expected);
+ }
+
@Test void testMatchRecognizePatternExpression() {
String sql = "select *\n"
+ " from \"product\" match_recognize\n"
@@ -5592,13 +5620,14 @@ class RelToSqlConverterTest {
private final SchemaPlus schema;
private final String sql;
private final SqlDialect dialect;
+ private final Set<SqlLibrary> librarySet;
private final Function<RelBuilder, RelNode> relFn;
private final List<Function<RelNode, RelNode>> transforms;
private final SqlParser.Config parserConfig;
private final UnaryOperator<SqlToRelConverter.Config> config;
Sql(CalciteAssert.SchemaSpec schemaSpec, String sql, SqlDialect dialect,
- SqlParser.Config parserConfig,
+ SqlParser.Config parserConfig, Set<SqlLibrary> librarySet,
UnaryOperator<SqlToRelConverter.Config> config,
Function<RelBuilder, RelNode> relFn,
List<Function<RelNode, RelNode>> transforms) {
@@ -5606,6 +5635,7 @@ class RelToSqlConverterTest {
this.schema = CalciteAssert.addSchema(rootSchema, schemaSpec);
this.sql = sql;
this.dialect = dialect;
+ this.librarySet = librarySet;
this.relFn = relFn;
this.transforms = ImmutableList.copyOf(transforms);
this.parserConfig = parserConfig;
@@ -5613,13 +5643,14 @@ class RelToSqlConverterTest {
}
Sql(SchemaPlus schema, String sql, SqlDialect dialect,
- SqlParser.Config parserConfig,
+ SqlParser.Config parserConfig, Set<SqlLibrary> librarySet,
UnaryOperator<SqlToRelConverter.Config> config,
Function<RelBuilder, RelNode> relFn,
List<Function<RelNode, RelNode>> transforms) {
this.schema = schema;
this.sql = sql;
this.dialect = dialect;
+ this.librarySet = librarySet;
this.relFn = relFn;
this.transforms = ImmutableList.copyOf(transforms);
this.parserConfig = parserConfig;
@@ -5627,13 +5658,13 @@ class RelToSqlConverterTest {
}
Sql dialect(SqlDialect dialect) {
- return new Sql(schema, sql, dialect, parserConfig, config, relFn,
- transforms);
+ return new Sql(schema, sql, dialect, parserConfig, librarySet, config,
+ relFn, transforms);
}
Sql relFn(Function<RelBuilder, RelNode> relFn) {
- return new Sql(schema, sql, dialect, parserConfig, config, relFn,
- transforms);
+ return new Sql(schema, sql, dialect, parserConfig, librarySet, config,
+ relFn, transforms);
}
Sql withCalcite() {
@@ -5755,18 +5786,27 @@ class RelToSqlConverterTest {
}
Sql parserConfig(SqlParser.Config parserConfig) {
- return new Sql(schema, sql, dialect, parserConfig, config, relFn,
- transforms);
+ return new Sql(schema, sql, dialect, parserConfig, librarySet, config,
+ relFn, transforms);
}
Sql withConfig(UnaryOperator<SqlToRelConverter.Config> config) {
- return new Sql(schema, sql, dialect, parserConfig, config, relFn,
- transforms);
+ return new Sql(schema, sql, dialect, parserConfig, librarySet, config,
+ relFn, transforms);
+ }
+
+ final Sql withLibrary(SqlLibrary library) {
+ return withLibrarySet(ImmutableSet.of(library));
+ }
+
+ Sql withLibrarySet(Iterable<? extends SqlLibrary> librarySet) {
+ return new Sql(schema, sql, dialect, parserConfig,
+ ImmutableSet.copyOf(librarySet), config, relFn, transforms);
}
Sql optimize(final RuleSet ruleSet, final RelOptPlanner relOptPlanner) {
- return new Sql(schema, sql, dialect, parserConfig, config, relFn,
- FlatLists.append(transforms, r -> {
+ final List<Function<RelNode, RelNode>> transforms =
+ FlatLists.append(this.transforms, r -> {
Program program = Programs.of(ruleSet);
final RelOptPlanner p =
Util.first(relOptPlanner,
@@ -5775,7 +5815,9 @@ class RelToSqlConverterTest {
.build()));
return program.run(p, r, r.getTraitSet(),
ImmutableList.of(), ImmutableList.of());
- }));
+ });
+ return new Sql(schema, sql, dialect, parserConfig, librarySet, config,
+ relFn, transforms);
}
Sql ok(String expectedQuery) {
@@ -5803,7 +5845,7 @@ class RelToSqlConverterTest {
final SqlToRelConverter.Config config =
this.config.apply(SqlToRelConverter.config()
.withTrimUnusedFields(false));
final Planner planner =
- getPlanner(null, parserConfig, schema, config);
+ getPlanner(null, parserConfig, schema, config, librarySet);
SqlNode parse = planner.parse(sql);
SqlNode validate = planner.validate(parse);
rel = planner.rel(validate).rel;
@@ -5818,8 +5860,8 @@ class RelToSqlConverterTest {
}
public Sql schema(CalciteAssert.SchemaSpec schemaSpec) {
- return new Sql(schemaSpec, sql, dialect, parserConfig, config, relFn,
- transforms);
+ return new Sql(schemaSpec, sql, dialect, parserConfig, librarySet,
config,
+ relFn, transforms);
}
}
}
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 c0a97c5..df07eb2 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
@@ -285,6 +285,7 @@ public class SqlParserTest {
"HOURS", "2011",
"IDENTITY", "92", "99", "2003", "2011", "2014", "c",
"IF", "92", "99", "2003",
+ "ILIKE",
"IMMEDIATE", "92", "99", "2003",
"IMMEDIATELY",
"IMPORT", "c",
@@ -1806,7 +1807,20 @@ public class SqlParserTest {
+ "WHERE (`A` LIKE `B` ESCAPE `C`)) ESCAPE `D`)))");
}
- @Test void testFoo() {
+ @Test void testIlike() {
+ // The ILIKE operator is only valid when the PostgreSQL function library is
+ // enabled ('fun=postgresql'). But the parser can always parse it.
+ final String expected = "SELECT *\n"
+ + "FROM `T`\n"
+ + "WHERE (`X` NOT ILIKE '%abc%')";
+ final String sql = "select * from t where x not ilike '%abc%'";
+ sql(sql).ok(expected);
+
+ final String sql1 = "select * from t where x ilike '%abc%'";
+ final String expected1 = "SELECT *\n"
+ + "FROM `T`\n"
+ + "WHERE (`X` ILIKE '%abc%')";
+ sql(sql1).ok(expected1);
}
@Test void testArithmeticOperators() {
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
index 04edf7e..ba70768 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
@@ -296,6 +296,7 @@ class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(CONTAINS)",
"KEYWORD(EQUALS)",
"KEYWORD(FORMAT)",
+ "KEYWORD(ILIKE)",
"KEYWORD(IMMEDIATELY)",
"KEYWORD(IN)",
"KEYWORD(IS)",
diff --git
a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
index aeb5cff..0b29fcc 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
@@ -3864,6 +3864,19 @@ public abstract class SqlOperatorBaseTest {
tester.checkBoolean("'abbc' like 'a\\%c' escape '\\'", Boolean.FALSE);
}
+ @Test void testIlikeEscape() {
+ tester.setFor(SqlLibraryOperators.ILIKE);
+ final SqlTester tester1 = libraryTester(SqlLibrary.POSTGRESQL);
+ tester1.checkBoolean("'a_c' ilike 'a#_C' escape '#'", Boolean.TRUE);
+ tester1.checkBoolean("'axc' ilike 'a#_C' escape '#'", Boolean.FALSE);
+ tester1.checkBoolean("'a_c' ilike 'a\\_C' escape '\\'", Boolean.TRUE);
+ tester1.checkBoolean("'axc' ilike 'a\\_C' escape '\\'", Boolean.FALSE);
+ tester1.checkBoolean("'a%c' ilike 'a\\%C' escape '\\'", Boolean.TRUE);
+ tester1.checkBoolean("'a%cde' ilike 'a\\%C_e' escape '\\'", Boolean.TRUE);
+ tester1.checkBoolean("'abbc' ilike 'a%C' escape '\\'", Boolean.TRUE);
+ tester1.checkBoolean("'abbc' ilike 'a\\%C' escape '\\'", Boolean.FALSE);
+ }
+
@Disabled("[CALCITE-525] Exception-handling in built-in functions")
@Test void testLikeEscape2() {
tester.checkBoolean("'x' not like 'x' escape 'x'", Boolean.TRUE);
@@ -3894,6 +3907,48 @@ public abstract class SqlOperatorBaseTest {
tester.checkBoolean("'ab\ncd\nef' like '%cde%'", Boolean.FALSE);
}
+ @Test void testIlikeOperator() {
+ tester.setFor(SqlLibraryOperators.ILIKE);
+ final String noLike = "No match found for function signature ILIKE";
+ tester.checkFails("^'a' ilike 'b'^", noLike, false);
+ tester.checkFails("^'a' ilike 'b' escape 'c'^", noLike, false);
+ final String noNotLike = "No match found for function signature NOT ILIKE";
+ tester.checkFails("^'a' not ilike 'b'^", noNotLike, false);
+ tester.checkFails("^'a' not ilike 'b' escape 'c'^", noNotLike, false);
+
+ final SqlTester tester1 = libraryTester(SqlLibrary.POSTGRESQL);
+ tester1.checkBoolean("'' ilike ''", Boolean.TRUE);
+ tester1.checkBoolean("'a' ilike 'a'", Boolean.TRUE);
+ tester1.checkBoolean("'a' ilike 'b'", Boolean.FALSE);
+ tester1.checkBoolean("'a' ilike 'A'", Boolean.TRUE);
+ tester1.checkBoolean("'a' ilike 'a_'", Boolean.FALSE);
+ tester1.checkBoolean("'a' ilike '_a'", Boolean.FALSE);
+ tester1.checkBoolean("'a' ilike '%a'", Boolean.TRUE);
+ tester1.checkBoolean("'a' ilike '%A'", Boolean.TRUE);
+ tester1.checkBoolean("'a' ilike '%a%'", Boolean.TRUE);
+ tester1.checkBoolean("'a' ilike '%A%'", Boolean.TRUE);
+ tester1.checkBoolean("'a' ilike 'a%'", Boolean.TRUE);
+ tester1.checkBoolean("'a' ilike 'A%'", Boolean.TRUE);
+ tester1.checkBoolean("'ab' ilike 'a_'", Boolean.TRUE);
+ tester1.checkBoolean("'ab' ilike 'A_'", Boolean.TRUE);
+ tester1.checkBoolean("'abc' ilike 'a_'", Boolean.FALSE);
+ tester1.checkBoolean("'abcd' ilike 'a%'", Boolean.TRUE);
+ tester1.checkBoolean("'abcd' ilike 'A%'", Boolean.TRUE);
+ tester1.checkBoolean("'ab' ilike '_b'", Boolean.TRUE);
+ tester1.checkBoolean("'ab' ilike '_B'", Boolean.TRUE);
+ tester1.checkBoolean("'abcd' ilike '_d'", Boolean.FALSE);
+ tester1.checkBoolean("'abcd' ilike '%d'", Boolean.TRUE);
+ tester1.checkBoolean("'abcd' ilike '%D'", Boolean.TRUE);
+ tester1.checkBoolean("'ab\ncd' ilike 'ab%'", Boolean.TRUE);
+ tester1.checkBoolean("'ab\ncd' ilike 'aB%'", Boolean.TRUE);
+ tester1.checkBoolean("'abc\ncd' ilike 'ab%'", Boolean.TRUE);
+ tester1.checkBoolean("'abc\ncd' ilike 'Ab%'", Boolean.TRUE);
+ tester1.checkBoolean("'123\n\n45\n' ilike '%'", Boolean.TRUE);
+ tester1.checkBoolean("'ab\ncd\nef' ilike '%cd%'", Boolean.TRUE);
+ tester1.checkBoolean("'ab\ncd\nef' ilike '%CD%'", Boolean.TRUE);
+ tester1.checkBoolean("'ab\ncd\nef' ilike '%cde%'", Boolean.FALSE);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-1898";>[CALCITE-1898]
* LIKE must match '.' (period) literally</a>. */
@@ -3903,6 +3958,15 @@ public abstract class SqlOperatorBaseTest {
tester.checkBoolean("'abc.e' like '%c.e'", Boolean.TRUE);
}
+ @Test void testIlikeDot() {
+ tester.setFor(SqlLibraryOperators.ILIKE);
+ final SqlTester tester1 = libraryTester(SqlLibrary.POSTGRESQL);
+ tester1.checkBoolean("'abc' ilike 'a.c'", Boolean.FALSE);
+ tester1.checkBoolean("'abcde' ilike '%c.e'", Boolean.FALSE);
+ tester1.checkBoolean("'abc.e' ilike '%c.e'", Boolean.TRUE);
+ tester1.checkBoolean("'abc.e' ilike '%c.E'", Boolean.TRUE);
+ }
+
@Test void testNotSimilarToOperator() {
tester.setFor(SqlStdOperatorTable.NOT_SIMILAR_TO, VM_EXPAND);
tester.checkBoolean("'ab' not similar to 'a_'", false);
diff --git a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
index 6bb8e96..5a4423e 100644
--- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
@@ -53,6 +53,7 @@ import org.apache.calcite.schema.impl.ViewTable;
import org.apache.calcite.schema.impl.ViewTableMacro;
import org.apache.calcite.sql.SqlMatchRecognize;
import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql.fun.SqlLibraryOperators;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.type.SqlTypeName;
@@ -3963,8 +3964,7 @@ public class RelBuilderTest {
RelNode root =
builder.scan("EMP")
.filter(
- builder.call(
- SqlStdOperatorTable.NOT_LIKE,
+ builder.call(SqlStdOperatorTable.NOT_LIKE,
builder.field("ENAME"),
builder.literal("a%b%c")))
.build();
@@ -3974,6 +3974,21 @@ public class RelBuilderTest {
assertThat(root, hasTree(expected));
}
+ @Test void testNotIlike() {
+ final RelBuilder builder = RelBuilder.create(config().build());
+ RelNode root =
+ builder.scan("EMP")
+ .filter(
+ builder.call(SqlLibraryOperators.NOT_ILIKE,
+ builder.field("ENAME"),
+ builder.literal("a%b%c")))
+ .build();
+ final String expected = ""
+ + "LogicalFilter(condition=[NOT(ILIKE($1, 'a%b%c'))])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(root, hasTree(expected));
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-4415";>[CALCITE-4415]
* SqlStdOperatorTable.NOT_LIKE has a wrong implementor</a>. */
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index a79d8a3..e2ca616 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -1005,6 +1005,19 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
expr("'a' similar to 'b' escape 'c'").ok();
}
+ @Test void testIlike() {
+ final Sql s = sql("?")
+ .withOperatorTable(operatorTableFor(SqlLibrary.POSTGRESQL));
+ s.expr("'a' ilike 'b'").columnType("BOOLEAN NOT NULL");
+ s.expr("'a' ilike cast(null as varchar(99))").columnType("BOOLEAN");
+ s.expr("cast(null as varchar(99)) not ilike 'b'").columnType("BOOLEAN");
+ s.expr("'a' not ilike 'b' || 'c'").columnType("BOOLEAN NOT NULL");
+
+ // ILIKE is only available in the PostgreSQL function library
+ expr("^'a' ilike 'b'^")
+ .fails("No match found for function signature ILIKE");
+ }
+
public void _testLikeAndSimilarFails() {
expr("'a' like _UTF16'b' escape 'c'")
.fails("(?s).*Operands _ISO-8859-1.a. COLLATE
ISO-8859-1.en_US.primary,"
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index bba8db9..96d9a50 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -632,6 +632,7 @@ HOP,
HOURS,
**IDENTITY**,
IGNORE,
+ILIKE,
IMMEDIATE,
IMMEDIATELY,
IMPLEMENTATION,
@@ -2505,6 +2506,8 @@ semantics.
| m | EXTRACTVALUE(xml, xpathExpr)) | Returns the text of the
first text node which is a child of the element or elements matched by the
XPath expression.
| o | GREATEST(expr [, expr ]*) | Returns the greatest of
the expressions
| b h s | IF(condition, value1, value2) | Returns *value1* if
*condition* is TRUE, *value2* otherwise
+| p | string1 ILIKE string2 [ ESCAPE string3 ] | Whether *string1*
matches pattern *string2*, ignoring case (similar to `LIKE`)
+| p | string1 NOT ILIKE string2 [ ESCAPE string3 ] | Whether *string1* does
not match pattern *string2*, ignoring case (similar to `NOT LIKE`)
| m | JSON_TYPE(jsonValue) | Returns a string value
indicating the type of *jsonValue*
| m | JSON_DEPTH(jsonValue) | Returns an integer
value indicating the depth of *jsonValue*
| m | JSON_PRETTY(jsonValue) | Returns a
pretty-printing of *jsonValue*
