Issue 1:
Which version of ShardingSphere did you use?

we find java version: java8, full_version=1.8.0_342, full_path=/home/peilq_sharding/bisheng-jdk1.8.0_342//bin/java

ShardingSphere-5.2.2-SNAPSHOT

Commit ID: dirty-753c0cee8ee6fd3db00536da55b64bc5198a3758

Commit Message: Optimize sqlFederationExecutor init logic when sqlFederationType modify dynamically (#22209)

Branch: 753c0ce

Build time: 2022-11-19T10:18:41+0800

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

In the federation scenario，the priority of ‘UNION/EXCEPT/INTERSECT ’ is correct.

Actual behavior

In the federation scenario，the priority of ‘UNION/EXCEPT/INTERSECT ’ is incorrect.

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

(select * from t_order where order_id = 1500 union select * from t_order where order_id = 1800 )INTERSECT select * from t_order where status ='finish' order by order_id;
select * from t_order where order_id = 1500 union (select * from t_order where order_id = 1800 INTERSECT select * from t_order where status ='finish' order by order_id); --incorrect
select * from t_order where order_id = 1500 union select * from t_order where order_id = 1800 INTERSECT select * from t_order where status ='finish' order by order_id; --incorrect

the result of ss

test_db=> (select * from t_order where order_id = 1500 union select * from t_order where order_id = 1800 )INTERSECT select * from t_order where status ='finish' order by order_id;
order_id | user_id | status | merchant_id | remark | creation_date
----------+---------+--------+-------------+--------+-----------------------
1800 | 18 | finish | 13 | test | 2017-08-18 00:00:00.0
(1 row)

test_db=> select * from t_order where order_id = 1500 union (select * from t_order where order_id = 1800 INTERSECT select * from t_order where status ='finish' order by order_id);
order_id | user_id | status | merchant_id | remark | creation_date
----------+---------+--------+-------------+--------+-----------------------
1800 | 18 | finish | 13 | test | 2017-08-18 00:00:00.0
(1 row)

test_db=> select * from t_order where order_id = 1500 union select * from t_order where order_id = 1800 INTERSECT select * from t_order where status ='finish' order by order_id;
order_id | user_id | status | merchant_id | remark | creation_date
----------+---------+--------+-------------+--------+-----------------------
1800 | 18 | finish | 13 | test | 2017-08-18 00:00:00.0
(1 row)

the result of og

tpccdb=# (select * from t_order where order_id = 1500 union select * from t_order where order_id = 1800 )INTERSECT select * from t_order where status ='finish' order by order_id;
order_id | user_id | status | merchant_id | remark | creation_date
----------+---------+--------+-------------+--------+---------------------
1800 | 18 | finish | 13 | test | 2017-08-18 00:00:00
(1 row)

tpccdb=# select * from t_order where order_id = 1500 union (select * from t_order where order_id = 1800 INTERSECT select * from t_order where status ='finish' order by order_id);
order_id | user_id | status | merchant_id | remark | creation_date
----------+---------+--------+-------------+--------+---------------------
1500 | 15 | init | 1 | | 2017-08-28 00:00:00
1800 | 18 | finish | 13 | test | 2017-08-18 00:00:00
(2 rows)

tpccdb=# select * from t_order where order_id = 1500 union select * from t_order where order_id = 1800 INTERSECT select * from t_order where status ='finish' order by order_id;
order_id | user_id | status | merchant_id | remark | creation_date
----------+---------+--------+-------------+--------+---------------------
1500 | 15 | init | 1 | | 2017-08-28 00:00:00
1800 | 18 | finish | 13 | test | 2017-08-18 00:00:00
(2 rows)

Example codes for reproduce this issue (such as a github link).

drop table if exists t_order;
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);
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, 'test', '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, 'test', '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');

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

Issue 2:
Which version of ShardingSphere did you use?

4.0.0

Which project did you use? Sharding-JDBC or Sharding-Proxy?

Sharding-JDBC

Expected behavior

select SQL execute successfully.

Actual behavior

java.lang.StringIndexOutOfBoundsException: String index out of range: -3

at java.lang.String.substring(String.java:1955)
at org.apache.shardingsphere.underlying.rewrite.sql.impl.AbstractSQLBuilder.getConjunctionText(AbstractSQLBuilder.java:58)
at org.apache.shardingsphere.underlying.rewrite.sql.impl.AbstractSQLBuilder.toSQL(AbstractSQLBuilder.java:50)
at org.apache.shardingsphere.underlying.rewrite.engine.impl.DefaultSQLRewriteEngine.rewrite(DefaultSQLRewriteEngine.java:34)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.EncryptPreparedStatement.getSQLUnit(EncryptPreparedStatement.java:181)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.EncryptPreparedStatement.executeQuery(EncryptPreparedStatement.java:120)

Reason analyze (If you can)

The SQL parse result like this :

It cause the rewrited SQL tokens are repeated, and the rewrite indexs are error.

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

SELECT * FROM t_query_encrypt WHERE pwd = ? and (id = ? or id =?)

Example codes for reproduce this issue (such as a github link).