peilinqian opened a new issue, #22820:
URL: https://github.com/apache/shardingsphere/issues/22820
### Which version of ShardingSphere did you use?
we find java version: java8, full_version=1.8.0_282,
full_path=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.282.b08-1.el7_9.x86_64/bin/java
ShardingSphere-5.2.2-SNAPSHOT
Commit ID: dirty-631fdf40f87223e176abe5c851a51b3287b4d6de
Commit Message: Fix wrong decide result when execute same sharding condition
subquery with sql federation (#22754)
Branch: 631fdf40f87223e176abe5c851a51b3287b4d6de
Build time: 2022-12-12T10:48:40+0800
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
```
create view select_view as select o.order_id, o.user_id from t_order o where
o.order_id > 1500 minus all select i.order_id, i.user_id from t_order_item i
where i.order_id > 1500 order by order_id;
create view select_view as select o.order_id, o.user_id from t_order o where
o.order_id > 2500 except all select i.order_id, i.user_id from t_order_item i
where i.order_id > 2000 order by order_id;
```
create view success;
### Actual behavior
create view fail;
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
```
drop table if exists t_order;
drop table if exists t_order_item;
create table t_order (order_id int primary key, user_id int not null, status
varchar(50) not null, merchant_id int not null, remark varchar(50),
creation_date date);
create table t_order_item (item_id int primary key, order_id int not null,
user_id int not null, product_id int not null, quantity int not null,
creation_date date not null);
-- insert t_order
insert into t_order values(1000, 10, 'init', 1, 'test', '2017-07-08');
insert into t_order values(1001, 10, 'init', 2, 'test', '2017-07-08');
insert into t_order values(2000, 20, 'init', 3, 'test', '2017-08-08');
insert into t_order values(2001, 20, 'init', 4, 'Test', '2017-08-08');
insert into t_order values(1100, 11, 'init', 5, 'TESt', '2017-08-08');
insert into t_order values(1101, 11, 'init', 6, 'test', '2017-08-08');
insert into t_order values(2100, 21, 'finish', 7, 'test', '2017-08-08');
insert into t_order values(2101, 21, 'finish', 8, 'TEST', '2017-08-08');
insert into t_order values(1200, 12, 'finish', 9, 'finish', '2017-08-08');
insert into t_order values(1201, 12, 'finish', 10, 'test22', '2017-08-18');
insert into t_order values(2200, 22, 'finish', 11, 'test', '2017-08-18');
insert into t_order values(2201, 22, 'finish', 12, 'test', '2017-08-18');
insert into t_order values(1300, 13, 'finish', 13, '', '2017-08-18');
insert into t_order values(1301, 13, 'finish', 14, 'TEST01', '2017-08-18');
insert into t_order values(2300, 23, 'finish ', 15, 'test', '2017-08-18');
insert into t_order values(2301, 23, 'finish', 16, 'TESt16', '2017-08-18');
insert into t_order values(1400, 14, 'init', 17, '', '2017-08-18');
insert into t_order values(1401, 14, 'init', 18, 'test', '2017-08-18');
insert into t_order values(2400, 24, 'init', 19, 'test', '2017-08-18');
insert into t_order values(2401, 24, 'init', 20, 'test', '2017-08-18');
insert into t_order values(1500, 15, 'init', 1, '', '2017-08-28');
insert into t_order values(1501, 15, 'init', 2, 'test', '2017-08-28');
insert into t_order values(2500, 25, 'init', 3, 'test', '2017-08-28');
insert into t_order values(2501, 25, 'init', 4, 'test', '2017-08-28');
insert into t_order values(1600, 16, 'init', 5, 'test', '2017-08-28');
insert into t_order values(1601, 16, 'init', 6, '', '2017-08-28');
insert into t_order values(2600, 26, 'init', 7, 'test', '2017-08-28');
insert into t_order values(2601, 26, 'init', 8);
insert into t_order values(1700, 17, 'init', 9, 'test', '2017-08-28');
insert into t_order values(1701, 17, 'finish', 10, 'test', '2017-08-18');
insert into t_order values(2700, 27, 'finish', 11, 'test', '2017-08-18');
insert into t_order values(2701, 27, 'finish', 12, 'test', '2017-08-18');
insert into t_order values(1800, 18, 'finish', 13, 'test', '2017-08-18');
insert into t_order values(1801, 18, 'finish', 14);
insert into t_order values(2800, 28, 'finish', 15, 'test', '2017-08-18');
insert into t_order values(2801, 28, 'finish', 16, 'test', '2017-08-18');
insert into t_order values(1900, 19, 'init', 17, 'test', '2017-08-18');
insert into t_order values(1901, 19, 'init', 18, 'test', '2017-08-18');
insert into t_order values(2900, 29, 'init', 19, 'test', '2017-08-18');
insert into t_order values(2901, 29, 'init', 20, 'test', '2017-08-18');
insert into t_order values(1902, 19, 'init', 17, 'test11', '2017-08-18');
insert into t_order values(1903, 19, 'init', 18, 'test12', '2017-08-18');
insert into t_order values(2902, 29, 'init', 19, 'test', '2017-08-18');
insert into t_order values(2903, 29, 'init', 20, 'test', '2017-08-18');
-- insert t_order_item
insert into t_order_item values(100001, 1000, 10, 1, 1, '2017-07-08');
insert into t_order_item values(100002, 1000, 10, 1, 1, '2017-07-08');
insert into t_order_item values(100101, 1001, 10, 2, 1, '2017-07-08');
insert into t_order_item values(100102, 1001, 10, 2, 1, '2017-07-08');
insert into t_order_item values(200001, 2000, 21, 3, 1, '2017-07-08');
insert into t_order_item values(200002, 2000, 21, 3, 1, '2017-07-08');
insert into t_order_item values(200101, 2001, 21, 4, 1, '2017-07-08');
insert into t_order_item values(200102, 2001, 21, 4, 1, '2017-07-08');
insert into t_order_item values(110001, 1100, 11, 5, 1, '2017-07-08');
insert into t_order_item values(110002, 1100, 11, 5, 1, '2017-07-08');
insert into t_order_item values(110101, 1101, 11, 6, 1, '2017-07-08');
insert into t_order_item values(110102, 1101, 11, 6, 1, '2017-07-08');
insert into t_order_item values(210001, 2100, 21, 7, 1, '2017-07-08');
insert into t_order_item values(210002, 2100, 21, 7, 1, '2017-07-08');
insert into t_order_item values(210101, 2101, 21, 8, 1, '2017-07-08');
insert into t_order_item values(210102, 2101, 21, 8, 1, '2017-07-08');
insert into t_order_item values(120001, 1200, 12, 9, 1, '2017-07-08');
insert into t_order_item values(120002, 1200, 12, 9, 1, '2017-07-08');
insert into t_order_item values(120101, 1201, 12, 10, 1, '2017-08-09');
insert into t_order_item values(120102, 1201, 12, 10, 1, '2017-08-09');
insert into t_order_item values(220001, 2200, 22, 11, 1, '2017-08-09');
insert into t_order_item values(220002, 2200, 22, 11, 1, '2017-08-09');
insert into t_order_item values(220101, 2201, 22, 12, 1, '2017-08-09');
insert into t_order_item values(220102, 2201, 22, 12, 1, '2017-08-09');
insert into t_order_item values(130001, 1300, 13, 13, 1, '2017-08-09');
insert into t_order_item values(130002, 1300, 13, 13, 1, '2017-08-09');
insert into t_order_item values(130101, 1301, 13, 14, 1, '2017-08-09');
insert into t_order_item values(130102, 1301, 13, 14, 1, '2017-08-09');
insert into t_order_item values(230001, 2300, 23, 15, 1, '2017-08-09');
insert into t_order_item values(230002, 2300, 23, 15, 1, '2017-08-09');
insert into t_order_item values(230101, 2301, 23, 16, 1, '2017-08-09');
insert into t_order_item values(230102, 2301, 23, 16, 1, '2017-08-09');
insert into t_order_item values(140001, 1400, 14, 17, 1, '2017-08-09');
insert into t_order_item values(140002, 1400, 14, 17, 1, '2017-08-09');
insert into t_order_item values(140101, 1401, 14, 18, 1, '2017-08-09');
insert into t_order_item values(140102, 1401, 14, 18, 1, '2017-08-09');
insert into t_order_item values(240001, 2400, 24, 19, 1, '2017-08-09');
insert into t_order_item values(240002, 2400, 24, 19, 1, '2017-08-09');
insert into t_order_item values(240101, 2401, 24, 20, 1, '2017-08-09');
insert into t_order_item values(240102, 2401, 24, 20, 1, '2017-08-09');
insert into t_order_item values(150001, 1500, 15, 1, 1, '2017-09-08');
insert into t_order_item values(150002, 1500, 15, 1, 1, '2017-09-08');
insert into t_order_item values(150101, 1501, 15, 2, 1, '2017-09-08');
insert into t_order_item values(150102, 1501, 15, 2, 1, '2017-09-08');
insert into t_order_item values(250001, 2500, 25, 3, 1, '2017-09-08');
insert into t_order_item values(250002, 2500, 25, 3, 1, '2017-09-08');
insert into t_order_item values(250101, 2501, 25, 4, 1, '2017-09-08');
insert into t_order_item values(250102, 2501, 25, 4, 1, '2017-09-08');
insert into t_order_item values(160001, 1600, 16, 5, 1, '2017-09-08');
insert into t_order_item values(160002, 1600, 16, 5, 1, '2017-09-08');
insert into t_order_item values(160101, 1601, 16, 6, 1, '2017-09-08');
insert into t_order_item values(160102, 1601, 16, 6, 1, '2017-09-08');
insert into t_order_item values(260001, 2600, 26, 7, 1, '2017-09-08');
insert into t_order_item values(260002, 2600, 26, 7, 1, '2017-09-08');
insert into t_order_item values(260101, 2601, 26, 8, 1, '2017-09-08');
insert into t_order_item values(260102, 2601, 26, 8, 1, '2017-09-08');
insert into t_order_item values(170001, 1700, 17, 9, 1, '2017-09-08');
insert into t_order_item values(170002, 1700, 17, 9, 1, '2017-09-08');
insert into t_order_item values(170101, 1701, 17, 10, 1, '2017-08-12');
insert into t_order_item values(170102, 1701, 17, 10, 1, '2017-08-12');
insert into t_order_item values(270001, 2700, 28, 11, 1, '2017-08-12');
insert into t_order_item values(270002, 2700, 28, 11, 1, '2017-08-12');
insert into t_order_item values(270101, 2701, 28, 12, 1, '2017-08-12');
insert into t_order_item values(270102, 2701, 28, 12, 1, '2017-08-12');
insert into t_order_item values(180001, 1800, 18, 13, 1, '2017-08-12');
insert into t_order_item values(180002, 1800, 19, 13, 1, '2017-08-12');
insert into t_order_item values(180101, 1801, 19, 14, 1, '2017-08-12');
insert into t_order_item values(180102, 1801, 19, 14, 1, '2017-08-12');
insert into t_order_item values(280001, 2800, 28, 15, 1, '2017-08-12');
insert into t_order_item values(280002, 2800, 28, 15, 1, '2017-08-12');
insert into t_order_item values(280101, 2801, 29, 16, 1, '2017-08-12');
insert into t_order_item values(280102, 2801, 29, 16, 1, '2017-08-12');
insert into t_order_item values(190001, 1900, 19, 17, 1, '2017-08-12');
insert into t_order_item values(190002, 1900, 19, 17, 1, '2017-08-12');
insert into t_order_item values(190101, 1901, 19, 18, 1, '2017-10-12');
insert into t_order_item values(190102, 1901, 19, 18, 1, '2017-10-12');
insert into t_order_item values(290001, 2900, 29, 19, 1, '2017-10-12');
insert into t_order_item values(290002, 2900, 29, 19, 1, '2017-10-12');
insert into t_order_item values(290101, 2901, 29, 20, 1, '2017-10-12');
insert into t_order_item values(290102, 2901, 29, 20, 1, '2017-10-12');
insert into t_order_item values(290003, 2903, 29, 19, 1, '2017-10-12');
insert into t_order_item values(290004, 2902, 29, 19, 1, '2017-10-12');
insert into t_order_item values(290103, 2903, 29, 20, 1, '2017-10-12');
insert into t_order_item values(290104, 2902, 29, 20, 1, '2017-10-12');
create view select_view as select o.order_id, o.user_id from t_order o where
o.order_id > 1500 minus all select i.order_id, i.user_id from t_order_item i
where i.order_id > 1500 order by order_id;
create view select_view as select o.order_id, o.user_id from t_order o where
o.order_id > 2500 except all select i.order_id, i.user_id from t_order_item i
where i.order_id > 2000 order by order_id;
```
```
test_db=> create view select_view as select o.order_id, o.user_id from
t_order o where o.order_id > 2500 except all select i.order_id, i.user_id from
t_order_item i where i.order_id > 2000 order by order_id;
ERROR: String index out of range: -7
test_db=> create view select_view as select o.order_id, o.user_id from
t_order o where o.order_id > 1500 minus all select i.order_id, i.user_id from
t_order_item i where i.order_id > 1500 order by order_id;
ERROR: String index out of range: -7
```
```
[ERROR] 2022-12-12 15:43:05.564 [Connection-39-ThreadExecutor]
o.a.s.p.f.c.CommandExecutorTask - Exception occur:
java.lang.StringIndexOutOfBoundsException: String index out of range: -7
at java.lang.String.substring(String.java:1967)
at
org.apache.shardingsphere.infra.rewrite.sql.impl.AbstractSQLBuilder.getConjunctionText(AbstractSQLBuilder.java:74)
at
org.apache.shardingsphere.infra.rewrite.sql.impl.AbstractSQLBuilder.toSQL(AbstractSQLBuilder.java:57)
at
org.apache.shardingsphere.infra.rewrite.engine.RouteSQLRewriteEngine.addSQLRewriteUnits(RouteSQLRewriteEngine.java:96)
at
org.apache.shardingsphere.infra.rewrite.engine.RouteSQLRewriteEngine.rewrite(RouteSQLRewriteEngine.java:72)
at
org.apache.shardingsphere.infra.rewrite.SQLRewriteEntry.rewrite(SQLRewriteEntry.java:79)
at
org.apache.shardingsphere.infra.context.kernel.KernelProcessor.rewrite(KernelProcessor.java:65)
at
org.apache.shardingsphere.infra.context.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:52)
at
org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:125)
at
org.apache.shardingsphere.proxy.frontend.opengauss.command.query.simple.OpenGaussComQueryExecutor.execute(OpenGaussComQueryExecutor.java:76)
at
org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:111)
at
org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:78)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
```
### Example codes for reproduce this issue (such as a github link).
```
schemaName: test_db
dataSources:
ds_0:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 260
minPoolSize: 10
password: Test@123
url: jdbc:opengauss://90.90.44.171:14000/test_db?batchMode=on
username: tpccuser
ds_1:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 260
minPoolSize: 10
password: Test@123
url: jdbc:opengauss://90.90.44.171:15000/test_db?batchMode=on
username: tpccuser
rules:
- !SHARDING
tables:
t_user:
actualDataNodes: ds_0.t_user
t_product:
actualDataNodes: ds_0.t_product
t_merchant:
actualDataNodes: ds_1.t_merchant
t_product_detail:
actualDataNodes: ds_1.t_product_detail
t_order:
actualDataNodes: ds_${0..1}.t_order
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
t_order_item1:
actualDataNodes: ds_${0..1}.t_order_item1
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
t_new_order:
actualDataNodes: ds_${0..1}.t_new_order_${0..1}
databaseStrategy:
standard:
shardingAlgorithmName: database_inline
shardingColumn: user_id
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: table_inline
bindingTables:
- t_order,t_order_item
broadcastTables:
- t_product_category
- t_country
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
allow-range-query-with-inline-sharding: true
table_inline:
type: INLINE
props:
algorithm-expression: t_new_order_${order_id % 2}
allow-range-query-with-inline-sharding: true
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: governance_ds
server-lists: 7.212.123.28:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
authority:
users:
- user: root@%
password: root
- user: sharding
password: sharding
privilege:
type: ALL_PERMITTED
rules:
- !TRANSACTION
defaultType: XA
providerType: Atomikos
props:
sql-show: true
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
