This is an automated email from the ASF dual-hosted git repository.
haonan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/iotdb.git
The following commit(s) were added to refs/heads/master by this push:
new 3bacba0 [IOTDB-1586] Support mysql-style Like clause (#3738)
3bacba0 is described below
commit 3bacba053cd363eb17ed6c94ee3cffe61adee385
Author: Superainbower <[email protected]>
AuthorDate: Thu Aug 26 13:11:21 2021 +0800
[IOTDB-1586] Support mysql-style Like clause (#3738)
Co-authored-by: Jialin Qiao <[email protected]>
---
.../antlr4/org/apache/iotdb/db/qp/sql/SqlBase.g4 | 6 +-
docs/UserGuide/Appendix/SQL-Reference.md | 39 ++++-
docs/zh/UserGuide/Appendix/SQL-Reference.md | 39 ++++-
.../iotdb/db/qp/constant/FilterConstant.java | 3 +
.../iotdb/db/qp/logical/crud/LikeOperator.java | 5 -
.../{LikeOperator.java => RegexpOperator.java} | 32 ++--
.../apache/iotdb/db/qp/sql/IoTDBSqlVisitor.java | 7 +-
.../apache/iotdb/db/integration/IoTDBLikeIT.java | 169 +++++++++++++++++++++
.../iotdb/db/integration/IoTDBQueryDemoIT.java | 10 +-
.../iotdb/db/qp/logical/LogicalPlanSmallTest.java | 4 +-
.../iotdb/db/qp/physical/PhysicalPlanTest.java | 4 +-
.../iotdb/tsfile/read/filter/ValueFilter.java | 27 ++++
.../read/filter/factory/FilterSerializeId.java | 1 +
.../iotdb/tsfile/read/filter/operator/Like.java | 50 +++++-
.../filter/operator/{Like.java => Regexp.java} | 14 +-
15 files changed, 349 insertions(+), 61 deletions(-)
diff --git a/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/SqlBase.g4
b/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/SqlBase.g4
index c6dc1f9..3f6fb5f 100644
--- a/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/SqlBase.g4
+++ b/antlr/src/main/antlr4/org/apache/iotdb/db/qp/sql/SqlBase.g4
@@ -213,7 +213,7 @@ predicate
: (TIME | TIMESTAMP | suffixPath | fullPath) comparisonOperator constant
| (TIME | TIMESTAMP | suffixPath | fullPath) inClause
| OPERATOR_NOT? LR_BRACKET orExpression RR_BRACKET
- | (suffixPath | fullPath) LIKE stringLiteral
+ | (suffixPath | fullPath) (REGEXP | LIKE) stringLiteral
;
inClause
@@ -1286,6 +1286,10 @@ LIKE
: L I K E
;
+REGEXP
+ : R E G E X P
+ ;
+
TOLERANCE
: T O L E R A N C E
;
diff --git a/docs/UserGuide/Appendix/SQL-Reference.md
b/docs/UserGuide/Appendix/SQL-Reference.md
index 3b3dea1..69f46ec 100644
--- a/docs/UserGuide/Appendix/SQL-Reference.md
+++ b/docs/UserGuide/Appendix/SQL-Reference.md
@@ -716,22 +716,45 @@ In this situation, it will throws an exception if *
corresponds to multiple sens
```
-* Like Statement
+* Regexp Statement
-Fuzzy query only supports regular expressions with data type of text and Java
standard library style when matching
+Regexp Statement only supports regular expressions with Java standard library
style on timeseries which is TEXT data type
```
SELECT <SelectClause> FROM <FromClause> WHERE <WhereClause>
Select Clause : <Path> [COMMA <Path>]*
FromClause : < PrefixPath > [COMMA < PrefixPath >]*
WhereClause : andExpression (OPERATOR_OR andExpression)*
andExpression : predicate (OPERATOR_AND predicate)*
-predicate : (suffixPath | fullPath) LIKE stringLiteral
-stringLiteral : SINGLE_QUOTE_STRING_LITERAL | DOUBLE_QUOTE_STRING_LITERAL
+predicate : (suffixPath | fullPath) REGEXP regularExpression
+regularExpression: Java standard regularexpression, like '^[a-z][0-9]$',
[details](https://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html)
+
+Eg. select s1 from root.sg.d1 where s1 regexp '^[0-9]*$'
+Eg. select s1, s2 FROM root.sg.d1 where s1 regexp
'^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$' and s2 regexp '^\d{15}|\d{18}$'
+Eg. select * from root.sg.d1 where s1 regexp '^[a-zA-Z]\w{5,17}$'
+Eg. select * from root.sg.d1 where s1 regexp '^\d{4}-\d{1,2}-\d{1,2}' and time
> 100
+```
-Eg. select s1 from root.sg.d1 where s1 like 'Regex'
-Eg. select s1, s2 FROM root.sg.d1 where s1 like 'regex' and s2 like 'Regex'
-Eg. select * from root.sg.d1 where s1 like 'Regex'
-Eg. select * from root.sg.d1 where s1 like 'Regex' and time > 100
+* Like Statement
+
+The usage of LIKE Statement similar with mysql, but only support timeseries
which is TEXT data type
+```
+SELECT <SelectClause> FROM <FromClause> WHERE <WhereClause>
+Select Clause : <Path> [COMMA <Path>]*
+FromClause : < PrefixPath > [COMMA < PrefixPath >]*
+WhereClause : andExpression (OPERATOR_OR andExpression)*
+andExpression : predicate (OPERATOR_AND predicate)*
+predicate : (suffixPath | fullPath) LIKE likeExpression
+likeExpression : string that may contains "%" or "_", while "%value" means a
string that ends with the value, "value%" means a string starts with the
value, "%value%" means string that contains values, and "_" represents any
character.
+
+Eg. select s1 from root.sg.d1 where s1 like 'abc'
+Eg. select s1, s2 from root.sg.d1 where s1 like 'a%bc'
+Eg. select * from root.sg.d1 where s1 like 'abc_'
+Eg. select * from root.sg.d1 where s1 like 'abc\%' and time > 100
+In this situation, '\%' means '%' will be escaped
+The result set will be like:
+| Time | Path | Value |
+| --- | ------------ | ----- |
+| 200 | root.sg.d1.s1| abc% |
```
## Database Management Statement
diff --git a/docs/zh/UserGuide/Appendix/SQL-Reference.md
b/docs/zh/UserGuide/Appendix/SQL-Reference.md
index 64ff231..eb8f882 100644
--- a/docs/zh/UserGuide/Appendix/SQL-Reference.md
+++ b/docs/zh/UserGuide/Appendix/SQL-Reference.md
@@ -705,22 +705,45 @@ E.g. select * as temperature from root.sg.d1
这种情况如果 * 匹配多个传感器，则无法正常显示。
```
-* Like 语句
+* Regexp 语句
-模糊查询，仅支持数据类型为 TEXT，匹配时为 Java 标准库风格的正则表达式
+Regexp语句仅支持数据类型为 TEXT的列进行过滤，传入的过滤条件为 Java 标准库风格的正则表达式
```
SELECT <SelectClause> FROM <FromClause> WHERE <WhereClause>
Select Clause : <Path> [COMMA <Path>]*
FromClause : < PrefixPath > [COMMA < PrefixPath >]*
WhereClause : andExpression (OPERATOR_OR andExpression)*
andExpression : predicate (OPERATOR_AND predicate)*
-predicate : (suffixPath | fullPath) LIKE stringLiteral
-stringLiteral : SINGLE_QUOTE_STRING_LITERAL | DOUBLE_QUOTE_STRING_LITERAL
+predicate : (suffixPath | fullPath) REGEXP regularExpression
+regularExpression: Java standard regularexpression, like '^[a-z][0-9]$',
[details](https://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html)
+
+Eg. select s1 from root.sg.d1 where s1 regexp '^[0-9]*$'
+Eg. select s1, s2 FROM root.sg.d1 where s1 regexp
'^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$' and s2 regexp '^\d{15}|\d{18}$'
+Eg. select * from root.sg.d1 where s1 regexp '^[a-zA-Z]\w{5,17}$'
+Eg. select * from root.sg.d1 where s1 regexp '^\d{4}-\d{1,2}-\d{1,2}' and time
> 100
+```
-Eg. select s1 from root.sg.d1 where s1 like 'Regex'
-Eg. select s1, s2 FROM root.sg.d1 where s1 like 'regex' and s2 like 'Regex'
-Eg. select * from root.sg.d1 where s1 like 'Regex'
-Eg. select * from root.sg.d1 where s1 like 'Regex' and time > 100
+* Like 语句
+
+Like语句的用法和mysql相同, 但是仅支持对数据类型为 TEXT的列进行过滤
+```
+SELECT <SelectClause> FROM <FromClause> WHERE <WhereClause>
+Select Clause : <Path> [COMMA <Path>]*
+FromClause : < PrefixPath > [COMMA < PrefixPath >]*
+WhereClause : andExpression (OPERATOR_OR andExpression)*
+andExpression : predicate (OPERATOR_AND predicate)*
+predicate : (suffixPath | fullPath) LIKE likeExpression
+likeExpression : string that may contains "%" or "_", while "%value" means a
string that ends with the value, "value%" means a string starts with the
value, "%value%" means string that contains values, and "_" represents any
character.
+
+Eg. select s1 from root.sg.d1 where s1 like 'abc'
+Eg. select s1, s2 from root.sg.d1 where s1 like 'abc%'
+Eg. select * from root.sg.d1 where s1 like 'abc_'
+Eg. select * from root.sg.d1 where s1 like 'abc\%'
+这种情况，'\%'表示'%'将会被转义
+结果集将显示为：
+| Time | Path | Value |
+| --- | ------------ | ----- |
+| 200 | root.sg.d1.s1| abc% |
```
## 数据库管理语句
diff --git
a/server/src/main/java/org/apache/iotdb/db/qp/constant/FilterConstant.java
b/server/src/main/java/org/apache/iotdb/db/qp/constant/FilterConstant.java
index d90dc16..9c43771 100644
--- a/server/src/main/java/org/apache/iotdb/db/qp/constant/FilterConstant.java
+++ b/server/src/main/java/org/apache/iotdb/db/qp/constant/FilterConstant.java
@@ -45,6 +45,7 @@ public class FilterConstant {
GREATERTHANOREQUALTO,
GREATERTHAN,
IN,
+ REGEXP,
LIKE
}
@@ -56,6 +57,7 @@ public class FilterConstant {
lexerToFilterType.put(SqlBaseLexer.OPERATOR_GTE,
FilterType.GREATERTHANOREQUALTO);
lexerToFilterType.put(SqlBaseLexer.OPERATOR_GT, FilterType.GREATERTHAN);
lexerToFilterType.put(SqlBaseLexer.OPERATOR_IN, FilterType.IN);
+ lexerToFilterType.put(SqlBaseLexer.REGEXP, FilterType.REGEXP);
lexerToFilterType.put(SqlBaseLexer.LIKE, FilterType.LIKE);
}
@@ -83,6 +85,7 @@ public class FilterConstant {
filterNames.put(FilterType.GREATERTHANOREQUALTO, "greaterthan_or_equalto");
filterNames.put(FilterType.GREATERTHAN, "greaterthan");
filterNames.put(FilterType.IN, "in");
+ filterNames.put(FilterType.REGEXP, "regexp");
filterNames.put(FilterType.LIKE, "like");
}
diff --git
a/server/src/main/java/org/apache/iotdb/db/qp/logical/crud/LikeOperator.java
b/server/src/main/java/org/apache/iotdb/db/qp/logical/crud/LikeOperator.java
index 10c9c6e..d552b9c 100644
--- a/server/src/main/java/org/apache/iotdb/db/qp/logical/crud/LikeOperator.java
+++ b/server/src/main/java/org/apache/iotdb/db/qp/logical/crud/LikeOperator.java
@@ -30,9 +30,6 @@ import org.apache.iotdb.tsfile.read.filter.basic.Filter;
import org.apache.iotdb.tsfile.utils.Pair;
import org.apache.iotdb.tsfile.utils.StringContainer;
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
-
import java.util.Map;
import java.util.Objects;
@@ -41,8 +38,6 @@ import static
org.apache.iotdb.tsfile.file.metadata.enums.TSDataType.TEXT;
/** fuzzy query structure LikeOperator. */
public class LikeOperator extends FunctionOperator {
- private static final Logger logger =
LoggerFactory.getLogger(LikeOperator.class);
-
protected String value;
public LikeOperator(FilterType filterType, PartialPath path, String value) {
diff --git
a/server/src/main/java/org/apache/iotdb/db/qp/logical/crud/LikeOperator.java
b/server/src/main/java/org/apache/iotdb/db/qp/logical/crud/RegexpOperator.java
similarity index 80%
copy from
server/src/main/java/org/apache/iotdb/db/qp/logical/crud/LikeOperator.java
copy to
server/src/main/java/org/apache/iotdb/db/qp/logical/crud/RegexpOperator.java
index 10c9c6e..5988d63 100644
--- a/server/src/main/java/org/apache/iotdb/db/qp/logical/crud/LikeOperator.java
+++
b/server/src/main/java/org/apache/iotdb/db/qp/logical/crud/RegexpOperator.java
@@ -30,22 +30,16 @@ import org.apache.iotdb.tsfile.read.filter.basic.Filter;
import org.apache.iotdb.tsfile.utils.Pair;
import org.apache.iotdb.tsfile.utils.StringContainer;
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
-
import java.util.Map;
import java.util.Objects;
import static org.apache.iotdb.tsfile.file.metadata.enums.TSDataType.TEXT;
-/** fuzzy query structure LikeOperator. */
-public class LikeOperator extends FunctionOperator {
-
- private static final Logger logger =
LoggerFactory.getLogger(LikeOperator.class);
+public class RegexpOperator extends FunctionOperator {
protected String value;
- public LikeOperator(FilterType filterType, PartialPath path, String value) {
+ public RegexpOperator(FilterType filterType, PartialPath path, String value)
{
super(filterType);
this.singlePath = path;
this.value = value;
@@ -64,12 +58,12 @@ public class LikeOperator extends FunctionOperator {
}
IUnaryExpression ret;
if (type != TEXT) {
- throw new LogicalOperatorException(type.toString(), "Only TEXT is
supported in 'Like'");
+ throw new LogicalOperatorException(type.toString(), "Only TEXT is
supported in 'Regexp'");
} else if (value.startsWith("\"") && value.endsWith("\"")) {
throw new LogicalOperatorException(value, "Please use single quotation
marks");
} else {
ret =
- Like.getUnaryExpression(
+ RegexpOperator.Regexp.getUnaryExpression(
singlePath,
(value.startsWith("'") && value.endsWith("'"))
? value.substring(1, value.length() - 1)
@@ -78,14 +72,14 @@ public class LikeOperator extends FunctionOperator {
return new Pair<>(ret, singlePath.getFullPath());
}
- private static class Like {
+ private static class Regexp {
public static <T extends Comparable<T>> IUnaryExpression
getUnaryExpression(
PartialPath path, String value) {
- return new SingleSeriesExpression(path, ValueFilter.like(value));
+ return new SingleSeriesExpression(path, ValueFilter.regexp(value));
}
public <T extends Comparable<T>> Filter getValueFilter(String value) {
- return ValueFilter.like(value);
+ return ValueFilter.regexp(value);
}
}
@@ -95,14 +89,14 @@ public class LikeOperator extends FunctionOperator {
for (int i = 0; i < spaceNum; i++) {
sc.addTail(" ");
}
- sc.addTail(singlePath.getFullPath(), getFilterSymbol(), value, ",
single\n");
+ sc.addTail(singlePath.getFullPath(), value, ", single\n");
return sc.toString();
}
@Override
- public LikeOperator copy() {
- LikeOperator ret =
- new LikeOperator(this.filterType, new
PartialPath(singlePath.getNodes().clone()), value);
+ public RegexpOperator copy() {
+ RegexpOperator ret =
+ new RegexpOperator(this.filterType, new
PartialPath(singlePath.getNodes().clone()), value);
ret.isLeaf = isLeaf;
ret.isSingle = isSingle;
ret.pathSet = pathSet;
@@ -114,7 +108,7 @@ public class LikeOperator extends FunctionOperator {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
if (!super.equals(o)) return false;
- LikeOperator that = (LikeOperator) o;
+ RegexpOperator that = (RegexpOperator) o;
return Objects.equals(value, that.value);
}
@@ -125,6 +119,6 @@ public class LikeOperator extends FunctionOperator {
@Override
public String toString() {
- return "[" + singlePath.getFullPath() + getFilterSymbol() + value + "]";
+ return "[" + singlePath.getFullPath() + value + "]";
}
}
diff --git
a/server/src/main/java/org/apache/iotdb/db/qp/sql/IoTDBSqlVisitor.java
b/server/src/main/java/org/apache/iotdb/db/qp/sql/IoTDBSqlVisitor.java
index 330bcad..50052f5 100644
--- a/server/src/main/java/org/apache/iotdb/db/qp/sql/IoTDBSqlVisitor.java
+++ b/server/src/main/java/org/apache/iotdb/db/qp/sql/IoTDBSqlVisitor.java
@@ -46,6 +46,7 @@ import org.apache.iotdb.db.qp.logical.crud.InsertOperator;
import org.apache.iotdb.db.qp.logical.crud.LastQueryOperator;
import org.apache.iotdb.db.qp.logical.crud.LikeOperator;
import org.apache.iotdb.db.qp.logical.crud.QueryOperator;
+import org.apache.iotdb.db.qp.logical.crud.RegexpOperator;
import org.apache.iotdb.db.qp.logical.crud.SelectComponent;
import org.apache.iotdb.db.qp.logical.crud.SelectIntoOperator;
import org.apache.iotdb.db.qp.logical.crud.SpecialClauseComponent;
@@ -2068,7 +2069,7 @@ public class IoTDBSqlVisitor extends
SqlBaseBaseVisitor<Operator> {
return notOp;
} else if (ctx.LR_BRACKET() != null && ctx.OPERATOR_NOT() == null) {
return parseOrExpression(ctx.orExpression());
- } else if (ctx.LIKE() != null) {
+ } else if (ctx.REGEXP() != null || ctx.LIKE() != null) {
if (ctx.suffixPath() != null) {
path = parseSuffixPath(ctx.suffixPath());
} else if (ctx.fullPath() != null) {
@@ -2077,7 +2078,9 @@ public class IoTDBSqlVisitor extends
SqlBaseBaseVisitor<Operator> {
if (path == null) {
throw new SQLParserException("Path is null, please check the sql.");
}
- return new LikeOperator(FilterType.LIKE, path,
ctx.stringLiteral().getText());
+ return ctx.REGEXP() != null
+ ? new RegexpOperator(FilterType.REGEXP, path,
ctx.stringLiteral().getText())
+ : new LikeOperator(FilterType.LIKE, path,
ctx.stringLiteral().getText());
} else {
if (ctx.TIME() != null || ctx.TIMESTAMP() != null) {
path = new PartialPath(SQLConstant.getSingleTimeArray());
diff --git
a/server/src/test/java/org/apache/iotdb/db/integration/IoTDBLikeIT.java
b/server/src/test/java/org/apache/iotdb/db/integration/IoTDBLikeIT.java
new file mode 100644
index 0000000..030397e
--- /dev/null
+++ b/server/src/test/java/org/apache/iotdb/db/integration/IoTDBLikeIT.java
@@ -0,0 +1,169 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.iotdb.db.integration;
+
+import org.apache.iotdb.db.utils.EnvironmentUtils;
+import org.apache.iotdb.jdbc.Config;
+
+import org.junit.AfterClass;
+import org.junit.Assert;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Objects;
+
+public class IoTDBLikeIT {
+ private static List<String> sqls = new ArrayList<>();
+ private static Connection connection;
+
+ @BeforeClass
+ public static void setUp() throws Exception {
+ EnvironmentUtils.closeStatMonitor();
+ initCreateSQLStatement();
+ EnvironmentUtils.envSetUp();
+ insertData();
+ }
+
+ @AfterClass
+ public static void tearDown() throws Exception {
+ close();
+ EnvironmentUtils.cleanEnv();
+ }
+
+ private static void close() {
+ if (Objects.nonNull(connection)) {
+ try {
+ connection.close();
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+ }
+
+ private static void initCreateSQLStatement() {
+ sqls.add("SET STORAGE GROUP TO root.t1");
+ sqls.add("CREATE TIMESERIES root.t1.wf01.wt01.status WITH DATATYPE=TEXT,
ENCODING=PLAIN");
+ sqls.add("CREATE TIMESERIES root.t1.wf01.wt01.temperature WITH
DATATYPE=FLOAT, ENCODING=RLE");
+ sqls.add(
+ "insert into root.t1.wf01.wt01 (time,status,temperature) values
(1509465600000,'1',12.1)");
+ sqls.add(
+ "insert into root.t1.wf01.wt01 (time,status,temperature) values
(1509465660000,'14',13.1)");
+ sqls.add(
+ "insert into root.t1.wf01.wt01 (time,status,temperature) values
(1509465720000,'616',5.5)");
+ sqls.add(
+ "insert into root.t1.wf01.wt01 (time,status,temperature) values
(1509465780000,'626',8.1)");
+ sqls.add(
+ "insert into root.t1.wf01.wt01 (time,status,temperature) values
(1509465840000,'6116',4.3)");
+ sqls.add(
+ "insert into root.t1.wf01.wt01 (time,status,temperature) values
(1509465900000,'6%16',10.3)");
+ sqls.add(
+ "insert into root.t1.wf01.wt01 (time,status,temperature) values
(1509465960000,'8[sS]*',11.3)");
+ sqls.add(
+ "insert into root.t1.wf01.wt01 (time,status,temperature) values
(1509466020000,'%123',18.3)");
+ sqls.add(
+ "insert into root.t1.wf01.wt01 (time,status,temperature) values
(1509466080000,'123%',18.3)");
+ sqls.add(
+ "insert into root.t1.wf01.wt01 (time,status,temperature) values
(1509466090000,'\\',10.3)");
+ }
+
+ private static void insertData() throws ClassNotFoundException, SQLException
{
+ Class.forName(Config.JDBC_DRIVER_NAME);
+ connection =
+ DriverManager.getConnection(Config.IOTDB_URL_PREFIX +
"127.0.0.1:6667/", "root", "root");
+ Statement statement = connection.createStatement();
+
+ for (String sql : sqls) {
+ statement.execute(sql);
+ }
+ statement.close();
+ }
+
+ @Test
+ public void testLike() throws SQLException {
+ Statement st0 = connection.createStatement();
+ boolean hasResultSet =
+ st0.execute("select status from root.t1.wf01.wt01 where status like
'1'");
+ Assert.assertTrue(hasResultSet);
+ Assert.assertEquals("1", outputResultStr(st0.getResultSet()));
+ hasResultSet = st0.execute("select status from root.t1.wf01.wt01 where
status like '%'");
+ Assert.assertTrue(hasResultSet);
+ Assert.assertEquals(
+ "1,14,616,626,6116,6%16,8[sS]*,%123,123%,\\",
outputResultStr(st0.getResultSet()));
+
+ hasResultSet = st0.execute("select status from root.t1.wf01.wt01 where
status like '1%'");
+ Assert.assertTrue(hasResultSet);
+ Assert.assertEquals("1,14,123%", outputResultStr(st0.getResultSet()));
+
+ hasResultSet = st0.execute("select status from root.t1.wf01.wt01 where
status like '%1%'");
+ Assert.assertTrue(hasResultSet);
+ Assert.assertEquals("1,14,616,6116,6%16,%123,123%",
outputResultStr(st0.getResultSet()));
+
+ hasResultSet = st0.execute("select status from root.t1.wf01.wt01 where
status like '6%6'");
+ Assert.assertTrue(hasResultSet);
+ Assert.assertEquals("616,626,6116,6%16",
outputResultStr(st0.getResultSet()));
+
+ hasResultSet = st0.execute("select status from root.t1.wf01.wt01 where
status like '1_'");
+ Assert.assertTrue(hasResultSet);
+ Assert.assertEquals("14", outputResultStr(st0.getResultSet()));
+
+ hasResultSet = st0.execute("select status from root.t1.wf01.wt01 where
status like '6_1%'");
+ Assert.assertTrue(hasResultSet);
+ Assert.assertEquals("6116,6%16", outputResultStr(st0.getResultSet()));
+
+ hasResultSet = st0.execute("select status from root.t1.wf01.wt01 where
status like '6\\%%'");
+ Assert.assertTrue(hasResultSet);
+ Assert.assertEquals("6%16", outputResultStr(st0.getResultSet()));
+
+ hasResultSet = st0.execute("select status from root.t1.wf01.wt01 where
status like '\\%%'");
+ Assert.assertTrue(hasResultSet);
+ Assert.assertEquals("%123", outputResultStr(st0.getResultSet()));
+
+ hasResultSet = st0.execute("select status from root.t1.wf01.wt01 where
status like '%\\%'");
+ Assert.assertTrue(hasResultSet);
+ Assert.assertEquals("123%", outputResultStr(st0.getResultSet()));
+
+ hasResultSet =
+ st0.execute("select status from root.t1.wf01.wt01 where status like
'%\\\\\\\\%'");
+ Assert.assertTrue(hasResultSet);
+ Assert.assertEquals("\\", outputResultStr(st0.getResultSet()));
+ }
+
+ @Test(expected = Exception.class)
+ public void testLikeNonTextCloumn() throws SQLException {
+ Statement st1 = connection.createStatement();
+ st1.execute("select * from root.t1.wf01.wt01 where temperature like '1'");
+ }
+
+ private String outputResultStr(ResultSet resultSet) throws SQLException {
+ StringBuilder resultBuilder = new StringBuilder();
+ while (resultSet.next()) {
+ resultBuilder.append(resultSet.getString(2)).append(",");
+ }
+ String result = resultBuilder.toString();
+ return result.substring(0, result.length() - 1);
+ }
+}
diff --git
a/server/src/test/java/org/apache/iotdb/db/integration/IoTDBQueryDemoIT.java
b/server/src/test/java/org/apache/iotdb/db/integration/IoTDBQueryDemoIT.java
index 4ab9353..217028f 100644
--- a/server/src/test/java/org/apache/iotdb/db/integration/IoTDBQueryDemoIT.java
+++ b/server/src/test/java/org/apache/iotdb/db/integration/IoTDBQueryDemoIT.java
@@ -547,7 +547,7 @@ public class IoTDBQueryDemoIT {
}
@Test
- public void LikeTest() throws ClassNotFoundException {
+ public void RegexpTest() throws ClassNotFoundException {
String[] retArray =
new String[] {
"1509465600000,v2,true,", "1509465660000,v2,true,",
"1509465720000,v1,false,",
@@ -564,7 +564,7 @@ public class IoTDBQueryDemoIT {
// Matches a string consisting of one lowercase letter and one digit.
such as: "v1","v2"
boolean hasResultSet =
statement.execute(
- "select hardware,status from root.ln.wf02.wt02 where hardware
like '^[a-z][0-9]$' and time < 1509465780000");
+ "select hardware,status from root.ln.wf02.wt02 where hardware
regexp '^[a-z][0-9]$' and time < 1509465780000");
Assert.assertTrue(hasResultSet);
try (ResultSet resultSet = statement.getResultSet()) {
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
@@ -608,7 +608,7 @@ public class IoTDBQueryDemoIT {
};
hasResultSet =
statement.execute(
- "select hardware,status from root.ln.wf02.wt02 where hardware
like 'v*' ");
+ "select hardware,status from root.ln.wf02.wt02 where hardware
regexp 'v*' ");
Assert.assertTrue(hasResultSet);
try (ResultSet resultSet = statement.getResultSet()) {
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
@@ -644,7 +644,7 @@ public class IoTDBQueryDemoIT {
}
@Test
- public void LikeNonExistTest() throws ClassNotFoundException {
+ public void RegexpNonExistTest() throws ClassNotFoundException {
// Match nonexistent string.'s.' is indicates that the starting with s and
the last is any
// single character
@@ -669,7 +669,7 @@ public class IoTDBQueryDemoIT {
boolean hasResultSet =
statement.execute(
- "select hardware,status from root.ln.wf02.wt02 where hardware
like 's.' ");
+ "select hardware,status from root.ln.wf02.wt02 where hardware
regexp 's.' ");
Assert.assertTrue(hasResultSet);
try (ResultSet resultSet = statement.getResultSet()) {
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
diff --git
a/server/src/test/java/org/apache/iotdb/db/qp/logical/LogicalPlanSmallTest.java
b/server/src/test/java/org/apache/iotdb/db/qp/logical/LogicalPlanSmallTest.java
index d158e91..b07461a 100644
---
a/server/src/test/java/org/apache/iotdb/db/qp/logical/LogicalPlanSmallTest.java
+++
b/server/src/test/java/org/apache/iotdb/db/qp/logical/LogicalPlanSmallTest.java
@@ -346,8 +346,8 @@ public class LogicalPlanSmallTest {
}
@Test
- public void testLikeQuery() {
- String sqlStr = "SELECT a FROM root.sg.* WHERE a LIKE 'string'";
+ public void testRegexpQuery() {
+ String sqlStr = "SELECT a FROM root.sg.* WHERE a REGEXP 'string'";
Operator op = LogicalGenerator.generate(sqlStr, ZoneId.systemDefault());
Assert.assertEquals(QueryOperator.class, op.getClass());
QueryOperator queryOperator = (QueryOperator) op;
diff --git
a/server/src/test/java/org/apache/iotdb/db/qp/physical/PhysicalPlanTest.java
b/server/src/test/java/org/apache/iotdb/db/qp/physical/PhysicalPlanTest.java
index 404e632..36df8d3 100644
--- a/server/src/test/java/org/apache/iotdb/db/qp/physical/PhysicalPlanTest.java
+++ b/server/src/test/java/org/apache/iotdb/db/qp/physical/PhysicalPlanTest.java
@@ -1482,7 +1482,7 @@ public class PhysicalPlanTest {
}
@Test
- public void testLikeQuery() throws QueryProcessException, MetadataException {
+ public void testRegexpQuery() throws QueryProcessException,
MetadataException {
IoTDB.metaManager.createTimeseries(
new PartialPath("root.vehicle.d5.s1"),
TSDataType.TEXT,
@@ -1490,7 +1490,7 @@ public class PhysicalPlanTest {
CompressionType.UNCOMPRESSED,
null);
- String sqlStr = "SELECT * FROM root.vehicle.d5 WHERE s1 LIKE 'string*'";
+ String sqlStr = "SELECT * FROM root.vehicle.d5 WHERE s1 REGEXP 'string*'";
PhysicalPlan plan = processor.parseSQLToPhysicalPlan(sqlStr);
IExpression queryFilter = ((RawDataQueryPlan) plan).getExpression();
IExpression expect =
diff --git
a/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/ValueFilter.java
b/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/ValueFilter.java
index 8a8e547..3dcfbe9 100644
--- a/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/ValueFilter.java
+++ b/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/ValueFilter.java
@@ -29,6 +29,7 @@ import org.apache.iotdb.tsfile.read.filter.operator.Lt;
import org.apache.iotdb.tsfile.read.filter.operator.LtEq;
import org.apache.iotdb.tsfile.read.filter.operator.NotEq;
import org.apache.iotdb.tsfile.read.filter.operator.NotFilter;
+import org.apache.iotdb.tsfile.read.filter.operator.Regexp;
import org.apache.iotdb.tsfile.utils.TsPrimitiveType;
import java.util.Set;
@@ -69,6 +70,10 @@ public class ValueFilter {
return new ValueNotEq(value);
}
+ public static <T extends Comparable<T>> ValueRegexp<T> regexp(String value) {
+ return new ValueRegexp(value);
+ }
+
public static <T extends Comparable<T>> ValueLike<T> like(String value) {
return new ValueLike(value);
}
@@ -239,6 +244,28 @@ public class ValueFilter {
}
}
+ public static class ValueRegexp<T extends Comparable<T>> extends Regexp<T> {
+
+ private ValueRegexp(String value) {
+ super(value, FilterType.VALUE_FILTER);
+ }
+ }
+
+ public static class VectorValueRegexp<T extends Comparable<T>> extends
ValueRegexp<T> {
+
+ private final int index;
+
+ private VectorValueRegexp(String value, int index) {
+ super(value);
+ this.index = index;
+ }
+
+ public boolean satisfy(long time, TsPrimitiveType[] values) {
+ Object v = filterType == FilterType.TIME_FILTER ? time :
values[index].getValue();
+ return this.value.equals(v);
+ }
+ }
+
public static class ValueLike<T extends Comparable<T>> extends Like<T> {
private ValueLike(String value) {
diff --git
a/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/factory/FilterSerializeId.java
b/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/factory/FilterSerializeId.java
index 46d9666..ec7f348 100644
---
a/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/factory/FilterSerializeId.java
+++
b/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/factory/FilterSerializeId.java
@@ -31,5 +31,6 @@ public enum FilterSerializeId {
NOT,
OR,
IN,
+ REGEXP,
LIKE
}
diff --git
a/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/operator/Like.java
b/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/operator/Like.java
index 572a0f3..7f32f7b 100644
---
a/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/operator/Like.java
+++
b/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/operator/Like.java
@@ -47,11 +47,36 @@ public class Like<T extends Comparable<T>> implements
Filter {
private Like() {}
+ /**
+ * The main idea of this part comes from
+ *
https://codereview.stackexchange.com/questions/36861/convert-sql-like-to-regex/36864
+ */
public Like(String value, FilterType filterType) {
this.value = value;
this.filterType = filterType;
try {
- this.pattern = Pattern.compile(this.value);
+ String unescapeValue = unescapeString(value);
+ String specialRegexStr = ".^$*+?{}[]|()";
+ StringBuilder patternStrBuild = new StringBuilder();
+ patternStrBuild.append("^");
+ for (int i = 0; i < unescapeValue.length(); i++) {
+ String ch = String.valueOf(unescapeValue.charAt(i));
+ if (specialRegexStr.contains(ch)) ch = "\\" + unescapeValue.charAt(i);
+ if ((i == 0)
+ || (i > 0 && !"\\".equals(String.valueOf(unescapeValue.charAt(i -
1))))
+ || (i >= 2
+ && "\\\\"
+ .equals(
+ patternStrBuild.substring(
+ patternStrBuild.length() - 2,
patternStrBuild.length())))) {
+ String replaceStr = ch.replace("%", ".*?").replace("_", ".");
+ patternStrBuild.append(replaceStr);
+ } else {
+ patternStrBuild.append(ch);
+ }
+ }
+ patternStrBuild.append("$");
+ this.pattern = Pattern.compile(patternStrBuild.toString());
} catch (PatternSyntaxException e) {
throw new PatternSyntaxException("Regular expression error",
value.toString(), e.getIndex());
}
@@ -111,4 +136,27 @@ public class Like<T extends Comparable<T>> implements
Filter {
public FilterSerializeId getSerializeId() {
return FilterSerializeId.LIKE;
}
+
+ /**
+ * This Method is for unescaping strings except '\' before special string
'%', '_', '\', because
+ * we need to use '\' to judege whether to replace this to regexp string
+ */
+ public String unescapeString(String value) {
+ String out = "";
+ for (int i = 0; i < value.length(); i++) {
+ String ch = String.valueOf(value.charAt(i));
+ if (ch.equals("\\")) {
+ if (i < value.length() - 1) {
+ String nextChar = String.valueOf(value.charAt(i + 1));
+ if (nextChar.equals("%") || nextChar.equals("_") ||
nextChar.equals("\\")) {
+ out = out + ch;
+ }
+ if (nextChar.equals("\\")) i++;
+ }
+ } else {
+ out = out + ch;
+ }
+ }
+ return out;
+ }
}
diff --git
a/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/operator/Like.java
b/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/operator/Regexp.java
similarity index 91%
copy from
tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/operator/Like.java
copy to
tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/operator/Regexp.java
index 572a0f3..5aa95bb 100644
---
a/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/operator/Like.java
+++
b/tsfile/src/main/java/org/apache/iotdb/tsfile/read/filter/operator/Regexp.java
@@ -31,13 +31,11 @@ import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;
/**
- * Like.
+ * Regexp.
*
* @param <T> comparable data type
*/
-public class Like<T extends Comparable<T>> implements Filter {
-
- private static final long serialVersionUID = 2171102599229260789L;
+public class Regexp<T extends Comparable<T>> implements Filter {
protected String value;
@@ -45,9 +43,9 @@ public class Like<T extends Comparable<T>> implements Filter {
protected Pattern pattern;
- private Like() {}
+ private Regexp() {}
- public Like(String value, FilterType filterType) {
+ public Regexp(String value, FilterType filterType) {
this.value = value;
this.filterType = filterType;
try {
@@ -82,7 +80,7 @@ public class Like<T extends Comparable<T>> implements Filter {
@Override
public Filter copy() {
- return new Like(value, filterType);
+ return new Regexp(value, filterType);
}
@Override
@@ -109,6 +107,6 @@ public class Like<T extends Comparable<T>> implements
Filter {
@Override
public FilterSerializeId getSerializeId() {
- return FilterSerializeId.LIKE;
+ return FilterSerializeId.REGEXP;
}
}
