This is an automated email from the ASF dual-hosted git repository.
neuyilan pushed a commit to branch rel/0.12
in repository https://gitbox.apache.org/repos/asf/iotdb.git
The following commit(s) were added to refs/heads/rel/0.12 by this push:
new b271060 [To rel/0.12][IOTDB-1586]Cherry Pick: Support mysql-style
Like clause (#3893)
b271060 is described below
commit b271060d345756deb43800dfc0304060968a8571
Author: Hang Ji <[email protected]>
AuthorDate: Fri Sep 3 09:08:07 2021 +0800
[To rel/0.12][IOTDB-1586]Cherry Pick: Support mysql-style Like clause
(#3893)
---
.../antlr4/org/apache/iotdb/db/qp/sql/SqlBase.g4 | 6 +-
docs/UserGuide/Appendix/SQL-Reference.md | 39 ++++-
.../DML-Data-Manipulation-Language.md | 74 +++++++++
docs/zh/UserGuide/Appendix/SQL-Reference.md | 39 ++++-
.../DML-Data-Manipulation-Language.md | 78 +++++++++-
.../org/apache/iotdb/db/qp/logical/Operator.java | 3 +-
.../iotdb/db/qp/logical/crud/LikeOperator.java | 5 -
.../{LikeOperator.java => RegexpOperator.java} | 35 ++---
.../apache/iotdb/db/qp/sql/IoTDBSqlVisitor.java | 9 +-
.../apache/iotdb/db/integration/IoTDBLikeIT.java | 169 +++++++++++++++++++++
.../iotdb/db/integration/IoTDBQueryDemoIT.java | 10 +-
.../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 +-
16 files changed, 500 insertions(+), 63 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 345ba94..a0c49c6 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
@@ -240,7 +240,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
@@ -1327,6 +1327,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 af0989e..6bf4e6d 100644
--- a/docs/UserGuide/Appendix/SQL-Reference.md
+++ b/docs/UserGuide/Appendix/SQL-Reference.md
@@ -694,22 +694,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/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
b/docs/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
index f76345d..c39909f 100644
--- a/docs/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
+++ b/docs/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
@@ -873,7 +873,81 @@ Total line number = 2
It costs 0.002s
```
+### Fuzzy query
+Fuzzy query is divided into Like statement and Regexp statement, both of which
can support fuzzy matching of TEXT type data.
+
+Like statement:
+
+Example 1: Query data containing `'cc'` in `value` under `root.sg.device`.
+The percentage (`%`) wildcard matches any string of zero or more characters.
+
+
+```
+IoTDB> select * from root.sg.device where value like '%cc%'
++-----------------------------+--------------------+
+| Time|root.sg.device.value|
++-----------------------------+--------------------+
+|2017-11-07T23:59:00.000+08:00| aabbccdd|
+|2017-11-07T23:59:00.000+08:00| cc|
++-----------------------------+--------------------+
+Total line number = 2
+It costs 0.002s
+```
+
+Example 2: Query data that consists of 3 characters and the second character
is `'b'` in `value` under `root.sg.device`.
+The underscore (`_`) wildcard matches any single character.
+```
+IoTDB> select * from root.sg.device where value like '_b_'
++-----------------------------+--------------------+
+| Time|root.sg.device.value|
++-----------------------------+--------------------+
+|2017-11-07T23:59:00.000+08:00| abc|
++-----------------------------+--------------------+
+Total line number = 1
+It costs 0.002s
+```
+
+Regexp statement：
+
+The filter conditions that need to be passed in are regular expressions in the
Java standard library style
+
+Example 1: Query a string composed of 26 English characters for the value
under root.sg.device
+```
+IoTDB> select * from root.sg.device where value regexp '^[A-Za-z]+$'
++-----------------------------+--------------------+
+| Time|root.sg.device.value|
++-----------------------------+--------------------+
+|2017-11-07T23:59:00.000+08:00| aabbccdd|
+|2017-11-07T23:59:00.000+08:00| cc|
++-----------------------------+--------------------+
+Total line number = 2
+It costs 0.002s
+```
+
+Example 2: Query root.sg.device where the value value is a string composed of
26 lowercase English characters and the time is greater than 100
+```
+IoTDB> select * from root.sg.device where value regexp '^[a-z]+$' and time >
100
++-----------------------------+--------------------+
+| Time|root.sg.device.value|
++-----------------------------+--------------------+
+|2017-11-07T23:59:00.000+08:00| aabbccdd|
+|2017-11-07T23:59:00.000+08:00| cc|
++-----------------------------+--------------------+
+Total line number = 2
+It costs 0.002s
+```
+
+Examples of common regular matching:
+
+```
+All characters with a length of 3-20: ^.{3,20}$
+Uppercase english characters: ^[A-Z]+$
+Numbers and English characters: ^[A-Za-z0-9]+$
+Beginning with a: ^a.*
+```
+
+For more syntax description, please read [SQL
Reference](../Appendix/SQL-Reference.md).
### Automated Fill
In the actual use of IoTDB, when doing the query operation of timeseries,
situations where the value is null at some time points may appear, which will
obstruct the further analysis by users. In order to better reflect the degree
of data change, users expect missing values to be automatically filled.
Therefore, the IoTDB system introduces the function of Automated Fill.
diff --git a/docs/zh/UserGuide/Appendix/SQL-Reference.md
b/docs/zh/UserGuide/Appendix/SQL-Reference.md
index 0bbb98c..7cfffb4 100644
--- a/docs/zh/UserGuide/Appendix/SQL-Reference.md
+++ b/docs/zh/UserGuide/Appendix/SQL-Reference.md
@@ -683,22 +683,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/docs/zh/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
b/docs/zh/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
index 674b2e5..0a6eba2 100644
--- a/docs/zh/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
+++ b/docs/zh/UserGuide/IoTDB-SQL-Language/DML-Data-Manipulation-Language.md
@@ -695,8 +695,84 @@ Total line number = 2
It costs 0.002s
```
+### 模糊查询
-#### 空值填充
+模糊查询分为Like语句和Regexp语句，都可以支持对Text类型的数据进行模糊匹配
+
+Like语句：
+
+示例 1：查询 `root.sg.device` 下 `value` 含有`'cc'`的数据。
+`%` 表示任意0个或多个字符。
+
+```
+IoTDB> select * from root.sg.device where value like '%cc%'
++-----------------------------+--------------------+
+| Time|root.sg.device.value|
++-----------------------------+--------------------+
+|2017-11-07T23:59:00.000+08:00| aabbccdd|
+|2017-11-07T23:59:00.000+08:00| cc|
++-----------------------------+--------------------+
+Total line number = 2
+It costs 0.002s
+```
+
+示例 2：查询 `root.sg.device` 下 `value` 中间为 `'b'`、前后为任意单个字符的数据。
+`_` 表示任意单个字符。
+
+```
+IoTDB> select * from root.sg.device where value like '_b_'
++-----------------------------+--------------------+
+| Time|root.sg.device.value|
++-----------------------------+--------------------+
+|2017-11-07T23:59:00.000+08:00| abc|
++-----------------------------+--------------------+
+Total line number = 1
+It costs 0.002s
+```
+
+Regexp语句：
+
+需要传入的过滤条件为 Java 标准库风格的正则表达式
+
+示例 1：查询 root.sg.device 下 value 值为26个英文字符组成的字符串
+
+```
+IoTDB> select * from root.sg.device where value regexp '^[A-Za-z]+$'
++-----------------------------+--------------------+
+| Time|root.sg.device.value|
++-----------------------------+--------------------+
+|2017-11-07T23:59:00.000+08:00| aabbccdd|
+|2017-11-07T23:59:00.000+08:00| cc|
++-----------------------------+--------------------+
+Total line number = 2
+It costs 0.002s
+```
+
+示例 2：查询 root.sg.device 下 value 值为26个小写英文字符组成的字符串 且时间大于100的
+
+```
+IoTDB> select * from root.sg.device where value regexp '^[a-z]+$' and time >
100
++-----------------------------+--------------------+
+| Time|root.sg.device.value|
++-----------------------------+--------------------+
+|2017-11-07T23:59:00.000+08:00| aabbccdd|
+|2017-11-07T23:59:00.000+08:00| cc|
++-----------------------------+--------------------+
+Total line number = 2
+It costs 0.002s
+```
+
+常见的正则匹配举例：
+
+```
+长度为3-20的所有字符：^.{3,20}$
+大写英文字符：^[A-Z]+$
+数字和英文字符：^[A-Za-z0-9]+$
+以a开头的：^a.*
+```
+
+更多语法请参照 [SQL REFERENCE](../Appendix/SQL-Reference.md).
+### 空值填充
在IoTDB的实际使用中，当进行时间序列的查询操作时，可能会出现在某些时间点值为null的情况，这会妨碍用户进行进一步的分析。
为了更好地反映数据更改的程度，用户希望可以自动填充缺失值。 因此，IoTDB系统引入了自动填充功能。
diff --git a/server/src/main/java/org/apache/iotdb/db/qp/logical/Operator.java
b/server/src/main/java/org/apache/iotdb/db/qp/logical/Operator.java
index 95ffa6c..00eefb3 100644
--- a/server/src/main/java/org/apache/iotdb/db/qp/logical/Operator.java
+++ b/server/src/main/java/org/apache/iotdb/db/qp/logical/Operator.java
@@ -157,6 +157,7 @@ public abstract class Operator {
SET_USING_DEVICE_TEMPLATE,
AUTO_CREATE_DEVICE_MNODE,
LIKE,
- SET_SYSTEM_MODE
+ SET_SYSTEM_MODE,
+ REGEXP
}
}
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 0ba17a2..0ac51bc 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(int tokenIntType, 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 79%
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 0ba17a2..9703aae 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,24 +30,18 @@ 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(int tokenIntType, PartialPath path, String value) {
+ public RegexpOperator(int tokenIntType, PartialPath path, String value) {
super(tokenIntType);
- operatorType = Operator.OperatorType.LIKE;
+ operatorType = Operator.OperatorType.REGEXP;
this.singlePath = path;
this.value = value;
isLeaf = true;
@@ -65,12 +59,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)
@@ -79,14 +73,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);
}
}
@@ -96,14 +90,15 @@ public class LikeOperator extends FunctionOperator {
for (int i = 0; i < spaceNum; i++) {
sc.addTail(" ");
}
- sc.addTail(singlePath.getFullPath(), this.tokenSymbol, value, ",
single\n");
+ sc.addTail(singlePath.getFullPath(), value, ", single\n");
return sc.toString();
}
@Override
- public LikeOperator copy() {
- LikeOperator ret =
- new LikeOperator(this.tokenIntType, new
PartialPath(singlePath.getNodes().clone()), value);
+ public RegexpOperator copy() {
+ RegexpOperator ret =
+ new RegexpOperator(
+ this.tokenIntType, new PartialPath(singlePath.getNodes().clone()),
value);
ret.isLeaf = isLeaf;
ret.isSingle = isSingle;
ret.pathSet = pathSet;
@@ -115,7 +110,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);
}
@@ -126,6 +121,6 @@ public class LikeOperator extends FunctionOperator {
@Override
public String toString() {
- return "[" + singlePath.getFullPath() + tokenSymbol + 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 10cd93b..89fdf2e 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
@@ -36,6 +36,7 @@ import org.apache.iotdb.db.qp.logical.crud.InOperator;
import org.apache.iotdb.db.qp.logical.crud.InsertOperator;
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.SelectOperator;
import org.apache.iotdb.db.qp.logical.sys.AlterTimeSeriesOperator;
import org.apache.iotdb.db.qp.logical.sys.AlterTimeSeriesOperator.AlterType;
@@ -1814,7 +1815,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) {
@@ -1823,8 +1824,10 @@ public class IoTDBSqlVisitor extends
SqlBaseBaseVisitor<Operator> {
if (path == null) {
throw new SQLParserException("Path is null, please check the sql.");
}
- return new LikeOperator(
- ctx.LIKE().getSymbol().getType(), path,
ctx.stringLiteral().getText());
+ return ctx.REGEXP() != null
+ ? new RegexpOperator(
+ ctx.REGEXP().getSymbol().getType(), path,
ctx.stringLiteral().getText())
+ : new LikeOperator(ctx.LIKE().getSymbol().getType(), 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/physical/PhysicalPlanTest.java
b/server/src/test/java/org/apache/iotdb/db/qp/physical/PhysicalPlanTest.java
index 5013eff..80f26ae 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
@@ -1259,7 +1259,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,
@@ -1267,7 +1267,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 15cf9c6..3b7d321 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);
}
@@ -149,6 +154,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;
}
}
