This is an automated email from the ASF dual-hosted git repository.
zhangliang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new b9c0786ffc9 support like operator with escape for postgres (#20232)
b9c0786ffc9 is described below
commit b9c0786ffc902aaa484eed3538c981b297a8d222
Author: tianhao960 <[email protected]>
AuthorDate: Wed Aug 17 22:31:12 2022 +0800
support like operator with escape for postgres (#20232)
---
.../src/main/antlr4/imports/postgresql/BaseRule.g4 | 2 +-
.../src/main/resources/case/dml/select.xml | 34 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/select.xml | 1 +
3 files changed, 36 insertions(+), 1 deletion(-)
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/BaseRule.g4
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/BaseRule.g4
index 1c0029e0e2d..e425b891592 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/BaseRule.g4
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-postgresql/src/main/antlr4/imports/postgresql/BaseRule.g4
@@ -563,8 +563,8 @@ aExpr
| aExpr qualOp
| aExpr comparisonOperator aExpr
| NOT aExpr
- | aExpr patternMatchingOperator aExpr
| aExpr patternMatchingOperator aExpr ESCAPE aExpr
+ | aExpr patternMatchingOperator aExpr
| aExpr IS NULL
| aExpr ISNULL
| aExpr IS NOT NULL
diff --git
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select.xml
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select.xml
index 4d871be1e30..6cd3bdec70b 100644
---
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select.xml
+++
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/case/dml/select.xml
@@ -787,6 +787,40 @@
</expr>
</where>
</select>
+
+ <select sql-case-id="select_count_like_escape" parameters="'a',1,0">
+ <from>
+ <simple-table name="t_order" start-index="21" stop-index="27" />
+ </from>
+ <projections start-index="7" stop-index="14">
+ <aggregation-projection type="COUNT" inner-expression="(*)"
start-index="7" stop-index="14" />
+ </projections>
+ <where start-index="29" stop-index="58" literal-stop-index="60">
+ <expr>
+ <binary-operation-expression start-index="35" stop-index="58"
literal-stop-index="60">
+ <left>
+ <column name="status" start-index="35" stop-index="40"
/>
+ </left>
+ <operator>LIKE</operator>
+ <right>
+ <list-expression start-index="47" stop-index="58"
literal-stop-index="60">
+ <items>
+ <literal-expression value="a" start-index="47"
stop-index="49" />
+ <parameter-marker-expression
parameter-index="0" start-index="47" stop-index="47" />
+ </items>
+ <items>
+ <literal-expression value="!" start-index="56"
stop-index="58" literal-start-index="58" literal-stop-index="60"/>
+ </items>
+ </list-expression>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ <limit start-index="60" stop-index="75" literal-start-index="62"
literal-stop-index="77">
+ <row-count value="1" parameter-index="1" start-index="66"
stop-index="66" literal-start-index="68" literal-stop-index="68" />
+ <offset value="0" parameter-index="2" start-index="75"
stop-index="75" literal-start-index="77" literal-stop-index="77" />
+ </limit>
+ </select>
<select sql-case-id="select_count_like_concat" parameters="'init', 1, 2,
9, 10">
<from>
diff --git
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select.xml
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select.xml
index a92914db8c3..22b19d93b5a 100644
---
a/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select.xml
+++
b/shardingsphere-test/shardingsphere-parser-test/src/main/resources/sql/supported/dml/select.xml
@@ -38,6 +38,7 @@
<sql-case id="select_in_with_same_sharding_column" value="SELECT * FROM
t_order WHERE order_id IN (?, ?) AND order_id IN (?, ?) ORDER BY order_id" />
<sql-case id="select_with_N_string_in_expression" value="SELECT * FROM
t_order WHERE is_deleted = 'N'" />
<sql-case id="select_count_like" value="SELECT COUNT(*) FROM t_order WHERE
(user_id = ? AND status LIKE ?)" />
+ <sql-case id="select_count_like_escape" value="SELECT COUNT(*) FROM
t_order WHERE status LIKE ? escape '!' limit ? offset ?" db-types="PostgreSQL"/>
<sql-case id="select_count_like_concat" value="SELECT count(0) AS
orders_count FROM t_order o WHERE o.status LIKE CONCAT('%%', ?, '%%') AND
o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?"
db-types="MySQL,SQLServer,Oracle,SQL92" />
<!--TODO combine into select_count_like_concat-->
<sql-case id="select_count_like_concat_postgres" value="SELECT count(0) AS
orders_count FROM t_order o WHERE o.status LIKE CONCAT('%%', ?, '%%') AND
o.user_id IN (?, ?) AND o.order_id BETWEEN ? AND ?"
db-types="PostgreSQL,openGauss" />
