This is an automated email from the ASF dual-hosted git repository.
panjuan 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 5309786 fix oracle & sql92 like query parse exception (#10255)
5309786 is described below
commit 53097869c17c6b571e4bb72f73f23f44859affb3
Author: Zhengqiang Duan <strongdua...@gmail.com>
AuthorDate: Thu May 6 17:18:56 2021 +0800
fix oracle & sql92 like query parse exception (#10255)
* fix oracle like query parse exception
* extract common method to SQLUtil
* modify sql parse test case db types
---
.../statement/impl/MySQLStatementSQLVisitor.java | 28 +++-----------
.../statement/impl/OracleStatementSQLVisitor.java | 10 +++--
.../statement/impl/SQL92StatementSQLVisitor.java | 12 +++---
.../impl/SQLServerStatementSQLVisitor.java | 8 ++--
.../sql/parser/sql/common/util/SQLUtil.java | 33 ++++++++++++++++
.../src/main/resources/case/dml/select.xml | 44 ++++++++++++++++++++++
.../main/resources/sql/supported/dml/select.xml | 1 +
7 files changed, 102 insertions(+), 34 deletions(-)
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java
index 186fd5e..f6acf26 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLStatementSQLVisitor.java
@@ -463,38 +463,22 @@ public abstract class MySQLStatementSQLVisitor extends
MySQLStatementBaseVisitor
return new BinaryOperationExpression(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), left, right, operator, text);
}
- private ExpressionSegment createLiteralExpression(final LiteralsContext
context) {
- ASTNode astNode = visit(context);
- if (astNode instanceof StringLiteralValue) {
- return new LiteralExpressionSegment(context.start.getStartIndex(),
context.stop.getStopIndex(), ((StringLiteralValue) astNode).getValue());
- }
- if (astNode instanceof NumberLiteralValue) {
- return new LiteralExpressionSegment(context.start.getStartIndex(),
context.stop.getStopIndex(), ((NumberLiteralValue) astNode).getValue());
- }
- if (astNode instanceof BooleanLiteralValue) {
- return new LiteralExpressionSegment(context.start.getStartIndex(),
context.stop.getStopIndex(), ((BooleanLiteralValue) astNode).getValue());
- }
- if (astNode instanceof OtherLiteralValue) {
- return new
CommonExpressionSegment(context.getStart().getStartIndex(),
context.getStop().getStopIndex(), ((OtherLiteralValue) astNode).getValue());
- }
- return new CommonExpressionSegment(context.getStart().getStartIndex(),
context.getStop().getStopIndex(),
- context.start.getInputStream().getText(new
Interval(context.start.getStartIndex(), context.stop.getStopIndex())));
- }
-
@Override
public final ASTNode visitSimpleExpr(final SimpleExprContext ctx) {
+ int startIndex = ctx.start.getStartIndex();
+ int stopIndex = ctx.stop.getStopIndex();
if (null != ctx.subquery()) {
SubquerySegment subquerySegment = new
SubquerySegment(ctx.subquery().getStart().getStartIndex(),
ctx.subquery().getStop().getStopIndex(), (MySQLSelectStatement)
visit(ctx.subquery()));
if (null != ctx.EXISTS()) {
- return new ExistsSubqueryExpression(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), subquerySegment);
+ return new ExistsSubqueryExpression(startIndex, stopIndex,
subquerySegment);
}
return new SubqueryExpressionSegment(subquerySegment);
}
if (null != ctx.parameterMarker()) {
- return new
ParameterMarkerExpressionSegment(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), ((ParameterMarkerValue)
visit(ctx.parameterMarker())).getValue());
+ return new ParameterMarkerExpressionSegment(startIndex, stopIndex,
((ParameterMarkerValue) visit(ctx.parameterMarker())).getValue());
}
if (null != ctx.literals()) {
- return createLiteralExpression(ctx.literals());
+ return SQLUtil.createLiteralExpression(visit(ctx.literals()),
startIndex, stopIndex, ctx.literals().start.getInputStream().getText(new
Interval(startIndex, stopIndex)));
}
if (null != ctx.intervalExpression()) {
return visit(ctx.intervalExpression());
@@ -514,7 +498,7 @@ public abstract class MySQLStatementSQLVisitor extends
MySQLStatementBaseVisitor
((ExistsSubqueryExpression) expression).setNot(true);
return expression;
}
- return new NotExpression(ctx.start.getStartIndex(),
ctx.stop.getStopIndex(), (ExpressionSegment) expression);
+ return new NotExpression(startIndex, stopIndex,
(ExpressionSegment) expression);
}
if (null != ctx.LP_() && 1 == ctx.expr().size()) {
return visit(ctx.expr(0));
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java
index 50d99ba..9095244 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/impl/OracleStatementSQLVisitor.java
@@ -385,14 +385,16 @@ public abstract class OracleStatementSQLVisitor extends
OracleStatementBaseVisit
@Override
public final ASTNode visitSimpleExpr(final SimpleExprContext ctx) {
+ int startIndex = ctx.getStart().getStartIndex();
+ int stopIndex = ctx.getStop().getStopIndex();
if (null != ctx.subquery()) {
- return new SubquerySegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), (OracleSelectStatement) visit(ctx.subquery()));
+ return new SubquerySegment(startIndex, stopIndex,
(OracleSelectStatement) visit(ctx.subquery()));
}
if (null != ctx.parameterMarker()) {
- return visit(ctx.parameterMarker());
+ return new ParameterMarkerExpressionSegment(startIndex, stopIndex,
((ParameterMarkerValue) visit(ctx.parameterMarker())).getValue());
}
if (null != ctx.literals()) {
- return visit(ctx.literals());
+ return SQLUtil.createLiteralExpression(visit(ctx.literals()),
startIndex, stopIndex, ctx.literals().start.getInputStream().getText(new
Interval(startIndex, stopIndex)));
}
if (null != ctx.functionCall()) {
return visit(ctx.functionCall());
@@ -400,7 +402,7 @@ public abstract class OracleStatementSQLVisitor extends
OracleStatementBaseVisit
if (null != ctx.columnName()) {
return visit(ctx.columnName());
}
- return new CommonExpressionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), ctx.getText());
+ return new CommonExpressionSegment(startIndex, stopIndex,
ctx.getText());
}
@Override
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sql92/src/main/java/org/apache/shardingsphere/sql/parser/sql92/visitor/statement/impl/SQL92StatementSQLVisitor.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sql92/src/main/java/org/apache/shardingsphere/sql/parser/sql92/visitor/statement/impl/SQL92StatementSQLVisitor.java
index aaa2372..7fbeccb 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sql92/src/main/java/org/apache/shardingsphere/sql/parser/sql92/visitor/statement/impl/SQL92StatementSQLVisitor.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sql92/src/main/java/org/apache/shardingsphere/sql/parser/sql92/visitor/statement/impl/SQL92StatementSQLVisitor.java
@@ -360,14 +360,16 @@ public abstract class SQL92StatementSQLVisitor extends
SQL92StatementBaseVisitor
@Override
public final ASTNode visitSimpleExpr(final SimpleExprContext ctx) {
+ int startIndex = ctx.getStart().getStartIndex();
+ int stopIndex = ctx.getStop().getStopIndex();
if (null != ctx.subquery()) {
- return new SubquerySegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), (SQL92SelectStatement) visit(ctx.subquery()));
+ return new SubquerySegment(startIndex, stopIndex,
(SQL92SelectStatement) visit(ctx.subquery()));
}
if (null != ctx.parameterMarker()) {
- return visit(ctx.parameterMarker());
+ return new ParameterMarkerExpressionSegment(startIndex, stopIndex,
((ParameterMarkerValue) visit(ctx.parameterMarker())).getValue());
}
if (null != ctx.literals()) {
- return visit(ctx.literals());
+ return SQLUtil.createLiteralExpression(visit(ctx.literals()),
startIndex, stopIndex, ctx.literals().start.getInputStream().getText(new
Interval(startIndex, stopIndex)));
}
if (null != ctx.functionCall()) {
return visit(ctx.functionCall());
@@ -375,9 +377,9 @@ public abstract class SQL92StatementSQLVisitor extends
SQL92StatementBaseVisitor
if (null != ctx.columnName()) {
return visit(ctx.columnName());
}
- return new CommonExpressionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), ctx.getText());
+ return new CommonExpressionSegment(startIndex, stopIndex,
ctx.getText());
}
-
+
@Override
public final ASTNode visitIntervalExpression(final
IntervalExpressionContext ctx) {
calculateParameterCount(Collections.singleton(ctx.expr()));
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerStatementSQLVisitor.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerStatementSQLVisitor.java
index 6d59833..dd3f0be 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerStatementSQLVisitor.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-sqlserver/src/main/java/org/apache/shardingsphere/sql/parser/sqlserver/visitor/statement/impl/SQLServerStatementSQLVisitor.java
@@ -374,14 +374,16 @@ public abstract class SQLServerStatementSQLVisitor
extends SQLServerStatementBas
@Override
public final ASTNode visitSimpleExpr(final SimpleExprContext ctx) {
+ int startIndex = ctx.getStart().getStartIndex();
+ int stopIndex = ctx.getStop().getStopIndex();
if (null != ctx.subquery()) {
- return new SubquerySegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), (SQLServerSelectStatement) visit(ctx.subquery()));
+ return new SubquerySegment(startIndex, stopIndex,
(SQLServerSelectStatement) visit(ctx.subquery()));
}
if (null != ctx.parameterMarker()) {
- return visit(ctx.parameterMarker());
+ return new ParameterMarkerExpressionSegment(startIndex, stopIndex,
((ParameterMarkerValue) visit(ctx.parameterMarker())).getValue());
}
if (null != ctx.literals()) {
- return visit(ctx.literals());
+ return SQLUtil.createLiteralExpression(visit(ctx.literals()),
startIndex, stopIndex, ctx.literals().start.getInputStream().getText(new
Interval(startIndex, stopIndex)));
}
if (null != ctx.functionCall()) {
return visit(ctx.functionCall());
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/util/SQLUtil.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/util/SQLUtil.java
index 5a0343d..bf32fd8 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/util/SQLUtil.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/util/SQLUtil.java
@@ -21,7 +21,11 @@ import com.google.common.base.CharMatcher;
import com.google.common.base.Strings;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
+import org.apache.shardingsphere.sql.parser.api.visitor.ASTNode;
import org.apache.shardingsphere.sql.parser.sql.common.constant.Paren;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.ExpressionSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.complex.CommonExpressionSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.LiteralExpressionSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.JoinTableSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SubqueryTableSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.TableSegment;
@@ -35,6 +39,10 @@ import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.DeleteState
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.InsertStatement;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.SelectStatement;
import
org.apache.shardingsphere.sql.parser.sql.common.statement.dml.UpdateStatement;
+import
org.apache.shardingsphere.sql.parser.sql.common.value.literal.impl.BooleanLiteralValue;
+import
org.apache.shardingsphere.sql.parser.sql.common.value.literal.impl.NumberLiteralValue;
+import
org.apache.shardingsphere.sql.parser.sql.common.value.literal.impl.OtherLiteralValue;
+import
org.apache.shardingsphere.sql.parser.sql.common.value.literal.impl.StringLiteralValue;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dal.MySQLCacheIndexStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dal.MySQLChecksumTableStatement;
import
org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dal.MySQLFlushStatement;
@@ -211,4 +219,29 @@ public final class SQLUtil {
}
return true;
}
+
+ /**
+ * Create literal expression.
+ *
+ * @param astNode ast node
+ * @param startIndex start index
+ * @param stopIndex stop index
+ * @param text text
+ * @return literal expression segment
+ */
+ public static ExpressionSegment createLiteralExpression(final ASTNode
astNode, final int startIndex, final int stopIndex, final String text) {
+ if (astNode instanceof StringLiteralValue) {
+ return new LiteralExpressionSegment(startIndex, stopIndex,
((StringLiteralValue) astNode).getValue());
+ }
+ if (astNode instanceof NumberLiteralValue) {
+ return new LiteralExpressionSegment(startIndex, stopIndex,
((NumberLiteralValue) astNode).getValue());
+ }
+ if (astNode instanceof BooleanLiteralValue) {
+ return new LiteralExpressionSegment(startIndex, stopIndex,
((BooleanLiteralValue) astNode).getValue());
+ }
+ if (astNode instanceof OtherLiteralValue) {
+ return new CommonExpressionSegment(startIndex, stopIndex,
((OtherLiteralValue) astNode).getValue());
+ }
+ return new CommonExpressionSegment(startIndex, stopIndex, text);
+ }
}
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select.xml
b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select.xml
index 6c4e969..3c7cda5 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select.xml
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/case/dml/select.xml
@@ -687,6 +687,50 @@
</where>
</select>
+ <select sql-case-id="select_count_like" parameters="1, 'init'">
+ <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="65" literal-stop-index="70">
+ <expr>
+ <binary-operation-expression start-index="36" stop-index="64"
literal-stop-index="69">
+ <left>
+ <binary-operation-expression start-index="36"
stop-index="46">
+ <left>
+ <column name="user_id" start-index="36"
stop-index="42" />
+ </left>
+ <operator>=</operator>
+ <right>
+ <literal-expression value="1" start-index="46"
stop-index="46" />
+ <parameter-marker-expression value="0"
start-index="46" stop-index="46" />
+ </right>
+ </binary-operation-expression>
+ </left>
+ <operator>AND</operator>
+ <right>
+ <binary-operation-expression start-index="52"
stop-index="64" literal-stop-index="69">
+ <left>
+ <column name="status" start-index="52"
stop-index="57" />
+ </left>
+ <operator>LIKE</operator>
+ <right>
+ <list-expression>
+ <items>
+ <literal-expression value="init"
start-index="64" stop-index="69" />
+ <parameter-marker-expression value="1"
start-index="64" stop-index="64" />
+ </items>
+ </list-expression>
+ </right>
+ </binary-operation-expression>
+ </right>
+ </binary-operation-expression>
+ </expr>
+ </where>
+ </select>
+
<select sql-case-id="select_count_like_concat" parameters="'init', 1, 2,
9, 10">
<from>
<simple-table name="t_order" alias="o" start-index="37"
stop-index="45" />
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select.xml
b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select.xml
index da21c14..4cb09bf 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select.xml
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-test/src/main/resources/sql/supported/dml/select.xml
@@ -37,6 +37,7 @@
<sql-case id="select_equal_with_same_sharding_column" value="SELECT * FROM
t_order WHERE order_id = ? AND order_id = ?" />
<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 ?)" db-types="MySQL, Oracle, SQL92, SQLServer" />
<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, H2,
PostgreSQL, SQL92" />
<sql-case id="select_count_like_concat_oracle_sqlserver" 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="Oracle,
SQLServer" />
<sql-case id="select_like_with_single_quotes" value="select id from admin
where fullname like 'a%'" db-types="MySQL"/>
