DRILL-444: Support using (NOT) LIKE and (NOT) SIMILAR TO in SQL queries
Project: http://git-wip-us.apache.org/repos/asf/incubator-drill/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-drill/commit/7dc3f724 Tree: http://git-wip-us.apache.org/repos/asf/incubator-drill/tree/7dc3f724 Diff: http://git-wip-us.apache.org/repos/asf/incubator-drill/diff/7dc3f724 Branch: refs/heads/master Commit: 7dc3f724000947abf0618c8e13e8903fef3ac967 Parents: a43db15 Author: vkorukanti <[email protected]> Authored: Tue Mar 25 13:04:39 2014 -0700 Committer: Jacques Nadeau <[email protected]> Committed: Wed Mar 26 23:39:51 2014 -0700 ---------------------------------------------------------------------- .../exec/expr/fn/impl/StringFunctions.java | 9 +----- .../drill/exec/planner/logical/DrillOptiq.java | 29 ++++++++++++++------ .../org/apache/drill/jdbc/test/JdbcAssert.java | 11 +++++--- .../apache/drill/jdbc/test/TestJdbcQuery.java | 25 +++++++++++++++-- 4 files changed, 52 insertions(+), 22 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/7dc3f724/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/StringFunctions.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/StringFunctions.java b/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/StringFunctions.java index 0780ca8..5e85012 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/StringFunctions.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/StringFunctions.java @@ -20,10 +20,6 @@ package org.apache.drill.exec.expr.fn.impl; import io.netty.buffer.ByteBuf; -import org.apache.drill.common.expression.OutputTypeDeterminer; -import org.apache.drill.common.types.TypeProtos; -import org.apache.drill.common.types.TypeProtos.MinorType; -import org.apache.drill.common.types.Types; import org.apache.drill.exec.expr.DrillSimpleFunc; import org.apache.drill.exec.expr.annotations.FunctionTemplate; import org.apache.drill.exec.expr.annotations.FunctionTemplate.FunctionScope; @@ -33,12 +29,9 @@ import org.apache.drill.exec.expr.annotations.Param; import org.apache.drill.exec.expr.annotations.Workspace; import org.apache.drill.exec.expr.holders.BigIntHolder; import org.apache.drill.exec.expr.holders.BitHolder; -import org.apache.drill.exec.expr.holders.VarBinaryHolder; import org.apache.drill.exec.expr.holders.VarCharHolder; import org.apache.drill.exec.record.RecordBatch; -import com.fasterxml.jackson.databind.ser.std.NumberSerializers.IntegerSerializer; - public class StringFunctions{ static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(StringFunctions.class); @@ -65,7 +58,7 @@ public class StringFunctions{ } } - @FunctionTemplate(name = "similar", scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) + @FunctionTemplate(names = {"similar", "similar to"}, scope = FunctionScope.SIMPLE, nulls = NullHandling.NULL_IF_NULL) public static class Similar implements DrillSimpleFunc{ @Param VarCharHolder input; @Param(constant=true) VarCharHolder pattern; http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/7dc3f724/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java index 4129145..8ef4ba3 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java @@ -18,7 +18,6 @@ package org.apache.drill.exec.planner.logical; import java.math.BigDecimal; -import java.util.Collections; import java.util.List; import org.apache.drill.common.expression.ExpressionPosition; @@ -102,17 +101,24 @@ public class DrillOptiq { return lastArg; case FUNCTION: logger.debug("Function"); - List<LogicalExpression> exprs = Lists.newArrayList(); - for(RexNode n : call.getOperands()){ - exprs.add(n.accept(this)); + return getDrillFunctionFromOptiqCall(call); + case PREFIX: + logger.debug("Prefix"); + LogicalExpression arg = call.getOperands().get(0).accept(this); + switch(call.getKind()){ + case NOT: + return FunctionCallFactory.createExpression(call.getOperator().getName().toLowerCase(), + ExpressionPosition.UNKNOWN, arg); } - return FunctionCallFactory.createExpression(call.getOperator().getName().toLowerCase(), Lists.newArrayList(exprs)); + throw new AssertionError("todo: implement syntax " + syntax + "(" + call + ")"); case SPECIAL: logger.debug("Special"); switch(call.getKind()){ - case CAST: return getDrillCastFunctionFromOptiq(call); + case LIKE: + case SIMILAR: + return getDrillFunctionFromOptiqCall(call); } if (call.getOperator() == SqlStdOperatorTable.ITEM) { @@ -183,8 +189,15 @@ public class DrillOptiq { return FunctionCallFactory.createCast(castType, ExpressionPosition.UNKNOWN, arg); } - - + + private LogicalExpression getDrillFunctionFromOptiqCall(RexCall call){ + List<LogicalExpression> args = Lists.newArrayList(); + for(RexNode n : call.getOperands()){ + args.add(n.accept(this)); + } + + return FunctionCallFactory.createExpression(call.getOperator().getName().toLowerCase(), args); + } @Override public LogicalExpression visitLiteral(RexLiteral literal) { http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/7dc3f724/sqlparser/src/test/java/org/apache/drill/jdbc/test/JdbcAssert.java ---------------------------------------------------------------------- diff --git a/sqlparser/src/test/java/org/apache/drill/jdbc/test/JdbcAssert.java b/sqlparser/src/test/java/org/apache/drill/jdbc/test/JdbcAssert.java index 1d7c7e3..198d272 100644 --- a/sqlparser/src/test/java/org/apache/drill/jdbc/test/JdbcAssert.java +++ b/sqlparser/src/test/java/org/apache/drill/jdbc/test/JdbcAssert.java @@ -59,9 +59,12 @@ public class JdbcAssert { public static ModelAndSchema withFull(String schema) { final Properties info = new Properties(); info.setProperty("schema", schema); - return new ModelAndSchema(info, false); + return new ModelAndSchema(info); } + public static ModelAndSchema withNoDefaultSchema() { + return new ModelAndSchema(); + } static String toString(ResultSet resultSet, int expectedRecordCount) throws SQLException { StringBuilder buf = new StringBuilder(); @@ -122,11 +125,11 @@ public class JdbcAssert { private final Properties info; private final ConnectionFactory connectionFactory; - public ModelAndSchema(Properties info) { - this(info, true); + public ModelAndSchema() { + this(null); } - public ModelAndSchema(Properties info, final boolean ref) { + public ModelAndSchema(Properties info) { this.info = info; this.connectionFactory = new ConnectionFactory() { public Connection createConnection() throws Exception { http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/7dc3f724/sqlparser/src/test/java/org/apache/drill/jdbc/test/TestJdbcQuery.java ---------------------------------------------------------------------- diff --git a/sqlparser/src/test/java/org/apache/drill/jdbc/test/TestJdbcQuery.java b/sqlparser/src/test/java/org/apache/drill/jdbc/test/TestJdbcQuery.java index 129591a..36bbc51 100644 --- a/sqlparser/src/test/java/org/apache/drill/jdbc/test/TestJdbcQuery.java +++ b/sqlparser/src/test/java/org/apache/drill/jdbc/test/TestJdbcQuery.java @@ -155,7 +155,28 @@ public class TestJdbcQuery { }finally{ if(!success) Thread.sleep(2000); } - - + } + + @Test + public void testLikeNotLike() throws Exception{ + JdbcAssert.withNoDefaultSchema() + .sql("SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS " + + "WHERE TABLE_NAME NOT LIKE 'C%' AND COLUMN_NAME LIKE 'TABLE_%E'") + .returns( + "TABLE_NAME=VIEWS; COLUMN_NAME=TABLE_NAME\n" + + "TABLE_NAME=TABLES; COLUMN_NAME=TABLE_NAME\n" + + "TABLE_NAME=TABLES; COLUMN_NAME=TABLE_TYPE\n" + ); + } + + @Test + public void testSimilarNotSimilar() throws Exception{ + JdbcAssert.withNoDefaultSchema() + .sql("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.`TABLES` "+ + "WHERE TABLE_NAME SIMILAR TO '%(H|I)E%' AND TABLE_NAME NOT SIMILAR TO 'C%'") + .returns( + "TABLE_NAME=VIEWS\n" + + "TABLE_NAME=SCHEMATA\n" + ); } }
