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 2b3d22e Issue#9845.fix Binding tables using 'join .. on .. and
column_x=?' or 'join .. on .. and cloumn_x in (?)' can not sharding correct.
(#9929)
2b3d22e is described below
commit 2b3d22e5fc0a1f1dd626d970e2dcb4ac805189a3
Author: huanghao495430759 <[email protected]>
AuthorDate: Sun Apr 11 23:27:48 2021 -0500
Issue#9845.fix Binding tables using 'join .. on .. and column_x=?' or 'join
.. on .. and cloumn_x in (?)' can not sharding correct. (#9929)
* Add test case for PostgreSQLCommand #8439
* Add test case for UpdateResponseHeader #8440
* Add test case for PostgreSQLCommand #8439
* Add test case for UpdateResponseHeader #8440
* revert commit
* issue#9845. fix Binding tables using 'join on ... and condition in (?)'
or 'join on ... and condition =?' can not sharding correct.
* issue#9845.remove any blank line.
* issue#9845.add integration test case for select.. join.. on.. and column
in().
* issue#9845.add more test case for join.. on.. in().
* fix join on test case
* fix join on test case
Co-authored-by: huanghao-jk <[email protected]>
---
.../src/test/resources/sharding/select.xml | 15 +++++++++++
.../impl/WhereClauseShardingConditionEngine.java | 8 ++++++
.../sql/common/util/WhereSegmentExtractUtils.java | 22 +++++++++++++++-
.../dataset/db/select_in_with_force_index_join.xml | 30 ++++++++++++++++++++++
.../select_in_with_force_index_join.xml | 30 ++++++++++++++++++++++
.../select_in_with_force_index_join.xml | 30 ++++++++++++++++++++++
.../tbl/select_in_with_force_index_join.xml | 30 ++++++++++++++++++++++
.../cases/dql/dql-integration-test-cases.xml | 10 +++++++-
8 files changed, 173 insertions(+), 2 deletions(-)
diff --git
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/select.xml
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/select.xml
index ced2c36..db03f52 100644
---
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/select.xml
+++
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-rewrite/src/test/resources/sharding/select.xml
@@ -453,4 +453,19 @@
<input sql="SELECT * FROM t_account, t_account_detail WHERE
t_account.account_id = 1 AND t_account.account_id =
t_account_detail.account_id" />
<output sql="SELECT * FROM t_account_1, t_account_detail_1 WHERE
t_account_1.account_id = 1 AND t_account_1.account_id =
t_account_detail_1.account_id" />
</rewrite-assertion>
+
+ <rewrite-assertion
id="select_with_join_table_on_condition_with_qualified_columns">
+ <input sql="SELECT * FROM t_account join t_account_detail on
t_account.account_id = t_account_detail.account_id and t_account.account_id =
?" parameters="1"/>
+ <output sql="SELECT * FROM t_account_1 join t_account_detail_1 on
t_account_1.account_id = t_account_detail_1.account_id and
t_account_1.account_id = ?" parameters="1"/>
+ </rewrite-assertion>
+
+ <rewrite-assertion
id="select_with_join_table_where_condition_with_qualified_columns">
+ <input sql="SELECT * FROM t_account join t_account_detail on
t_account.account_id = t_account_detail.account_id where t_account.account_id =
?" parameters="1"/>
+ <output sql="SELECT * FROM t_account_1 join t_account_detail_1 on
t_account_1.account_id = t_account_detail_1.account_id where
t_account_1.account_id = ?" parameters="1"/>
+ </rewrite-assertion>
+
+ <rewrite-assertion
id="select_with_join_table_on_in_condition_with_qualified_columns">
+ <input sql="SELECT * FROM t_account join t_account_detail on
t_account.account_id = t_account_detail.account_id and t_account.account_id in
( ? )" parameters="1"/>
+ <output sql="SELECT * FROM t_account_1 join t_account_detail_1 on
t_account_1.account_id = t_account_detail_1.account_id and
t_account_1.account_id in ( ? )" parameters="1"/>
+ </rewrite-assertion>
</rewrite-assertions>
diff --git
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-route/src/main/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/impl/WhereClauseShardingConditionEngine.java
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-route/src/main/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/impl/WhereClauseShardingConditionEngine.java
index e172067..b7d8392 100644
---
a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-route/src/main/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/impl/WhereClauseShardingConditionEngine.java
+++
b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-route/src/main/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/impl/WhereClauseShardingConditionEngine.java
@@ -70,6 +70,14 @@ public final class WhereClauseShardingConditionEngine
implements ShardingConditi
}
List<ShardingCondition> result = new ArrayList<>();
((WhereAvailable) sqlStatementContext).getWhere().ifPresent(segment ->
result.addAll(createShardingConditions(sqlStatementContext, segment.getExpr(),
parameters)));
+ Collection<WhereSegment> joinWhereSegments =
sqlStatementContext.getSqlStatement() instanceof SelectStatement
+ ?
WhereSegmentExtractUtils.getJoinWhereSegments((SelectStatement)
sqlStatementContext.getSqlStatement()) : Collections.emptyList();
+ for (WhereSegment each : joinWhereSegments) {
+ Collection<ShardingCondition> joinShardingConditions =
createShardingConditions(sqlStatementContext, each.getExpr(), parameters);
+ if (!result.containsAll(joinShardingConditions)) {
+ result.addAll(joinShardingConditions);
+ }
+ }
Collection<WhereSegment> subqueryWhereSegments =
sqlStatementContext.getSqlStatement() instanceof SelectStatement
?
WhereSegmentExtractUtils.getSubqueryWhereSegments((SelectStatement)
sqlStatementContext.getSqlStatement()) : Collections.emptyList();
for (WhereSegment each : subqueryWhereSegments) {
diff --git
a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/util/WhereSegmentExtractUtils.java
b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/util/WhereSegmentExtractUtils.java
index 0acb3c2..36a7f70 100644
---
a/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/util/WhereSegmentExtractUtils.java
+++
b/shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/util/WhereSegmentExtractUtils.java
@@ -41,7 +41,27 @@ import java.util.LinkedList;
*/
@NoArgsConstructor(access = AccessLevel.PRIVATE)
public final class WhereSegmentExtractUtils {
-
+
+ /**
+ * Get join where segment from SelectStatement.
+ *
+ * @param selectStatement SelectStatement
+ * @return join where segment collection.
+ */
+ public static Collection<WhereSegment> getJoinWhereSegments(final
SelectStatement selectStatement) {
+ if (null == selectStatement.getFrom()) {
+ return Collections.emptyList();
+ }
+ TableSegment tableSegment = selectStatement.getFrom();
+ Collection<WhereSegment> result = new LinkedList<>();
+ if (tableSegment instanceof JoinTableSegment && null !=
((JoinTableSegment) tableSegment).getCondition()) {
+ ExpressionSegment expressionSegment = ((JoinTableSegment)
tableSegment).getCondition();
+ WhereSegment whereSegment = new
WhereSegment(expressionSegment.getStartIndex(),
expressionSegment.getStopIndex(), expressionSegment);
+ result.add(whereSegment);
+ }
+ return result;
+ }
+
/**
* Get subquery where segment from SelectStatement.
*
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/db/select_in_with_force_index_join.xml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/db/select_in_with_force_index_join.xml
new file mode 100644
index 0000000..96a9d48
--- /dev/null
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/db/select_in_with_force_index_join.xml
@@ -0,0 +1,30 @@
+<!--
+ ~ 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.
+ -->
+
+<dataset>
+ <metadata>
+ <column name="item_id" />
+ <column name="order_id" />
+ <column name="user_id" />
+ <column name="status" />
+ <column name="creation_date" />
+ </metadata>
+ <row values="100001, 1000, 10, init, 2017-08-08" />
+ <row values="100002, 1000, 10, init, 2017-08-08" />
+ <row values="100101, 1001, 10, init, 2017-08-08" />
+ <row values="100102, 1001, 10, init, 2017-08-08" />
+</dataset>
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/dbtbl_with_read_write_splitting/select_in_with_force_index_join.xml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/dbtbl_with_read_write_splitting/select_in_with_force_index_join.xml
new file mode 100644
index 0000000..52f30ba
--- /dev/null
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/dbtbl_with_read_write_splitting/select_in_with_force_index_join.xml
@@ -0,0 +1,30 @@
+<!--
+ ~ 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.
+ -->
+
+<dataset>
+ <metadata>
+ <column name="item_id" />
+ <column name="order_id" />
+ <column name="user_id" />
+ <column name="status" />
+ <column name="creation_date" />
+ </metadata>
+ <row values="100000, 1000, 10, init_read, 2017-08-08" />
+ <row values="100001, 1000, 10, init_read, 2017-08-08" />
+ <row values="100100, 1001, 10, init_read, 2017-08-08" />
+ <row values="100101, 1001, 10, init_read, 2017-08-08" />
+</dataset>
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/read_write_splitting/select_in_with_force_index_join.xml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/read_write_splitting/select_in_with_force_index_join.xml
new file mode 100644
index 0000000..6e89803
--- /dev/null
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/read_write_splitting/select_in_with_force_index_join.xml
@@ -0,0 +1,30 @@
+<!--
+ ~ 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.
+ -->
+
+<dataset>
+ <metadata>
+ <column name="item_id" />
+ <column name="order_id" />
+ <column name="user_id" />
+ <column name="status" />
+ <column name="creation_date" />
+ </metadata>
+ <row values="100001, 1000, 10, init_read, 2017-08-08" />
+ <row values="100002, 1000, 10, init_read, 2017-08-08" />
+ <row values="100101, 1001, 10, init_read, 2017-08-08" />
+ <row values="100102, 1001, 10, init_read, 2017-08-08" />
+</dataset>
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/tbl/select_in_with_force_index_join.xml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/tbl/select_in_with_force_index_join.xml
new file mode 100644
index 0000000..c227af7
--- /dev/null
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dataset/tbl/select_in_with_force_index_join.xml
@@ -0,0 +1,30 @@
+<!--
+ ~ 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.
+ -->
+
+<dataset>
+ <metadata>
+ <column name="item_id" />
+ <column name="order_id" />
+ <column name="user_id" />
+ <column name="status" />
+ <column name="creation_date" />
+ </metadata>
+ <row values="100000, 1000, 10, init, 2017-08-08" />
+ <row values="100001, 1000, 10, init, 2017-08-08" />
+ <row values="100100, 1001, 10, init, 2017-08-08" />
+ <row values="100101, 1001, 10, init, 2017-08-08" />
+</dataset>
diff --git
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
index 743a575..0e037f2 100644
---
a/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
+++
b/shardingsphere-test/shardingsphere-integration-test/shardingsphere-integration-test-suite/src/test/resources/cases/dql/dql-integration-test-cases.xml
@@ -102,7 +102,15 @@
<test-case sql="SELECT i.* FROM t_order o FORCE INDEX(order_index) JOIN
t_order_item i ON o.order_id=i.order_id WHERE o.order_id = ?" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_read_write_splitting,read_write_splitting">
<assertion parameters="1000:int"
expected-data-file="select_with_force_index_join.xml" />
</test-case>
-
+
+ <test-case sql="SELECT i.* FROM t_order o FORCE INDEX(order_index) JOIN
t_order_item i ON o.order_id=i.order_id AND o.order_id = ?" db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_read_write_splitting,read_write_splitting">
+ <assertion parameters="1000:int"
expected-data-file="select_with_force_index_join.xml" />
+ </test-case>
+
+ <test-case sql="SELECT i.* FROM t_order o FORCE INDEX(order_index) JOIN
t_order_item i ON o.order_id=i.order_id AND o.order_id in (?,?)"
db-types="MySQL"
scenario-types="db,tbl,dbtbl_with_read_write_splitting,read_write_splitting">
+ <assertion parameters="1000:int,1001:int"
expected-data-file="select_in_with_force_index_join.xml" />
+ </test-case>
+
<test-case sql="SELECT SUM(user_id) AS user_id_sum FROM t_order"
scenario-types="db,tbl,dbtbl_with_read_write_splitting,read_write_splitting">
<assertion expected-data-file="select_sum.xml" />
</test-case>
