You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/12/13 00:57:43 UTC
[GitHub] [shardingsphere] peilinqian opened a new issue, #22841: In the federation scenario，a table join a view and group by ,when 'where condition' include view's column，return error.
peilinqian opened a new issue, #22841:
URL: https://github.com/apache/shardingsphere/issues/22841
### 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 (https://github.com/apache/shardingsphere/pull/22754)
Branch: https://github.com/apache/shardingsphere/commit/631fdf40f87223e176abe5c851a51b3287b4d6de
Build time: 2022-12-12T10:48:40+0800
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
In the federation scenario，table select join view and group by a column which is not join column，return success.
### Actual behavior
In the federation scenario，table select join view and group by a column which is not join column，return error.
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
```
create view select_view as select o.order_id,o.user_id,status,remark,merchant_name from t_order o inner join t_merchant m using(merchant_id) where o.user_id > 10 and o.user_id < 15 order by o.order_id, 2 ;
select count(v.merchant_name),v.order_id from t_order o inner join select_view v on o.order_id = v.order_id where v.user_id > 10 and o.status='finish' group by v.order_id order by 2;--where condition include view's column,error
select count(v.merchant_name),v.order_id from t_order o inner join select_view v on o.order_id = v.order_id where o.user_id > 10 and o.status='finish' group by v.order_id order by 2;
```
```
test_db=> select count(v.merchant_name),v.order_id from t_order o inner join select_view v on o.order_id = v.order_id where v.user_id > 10 and o.status='finish' group by v.order_id order by 2;
ERROR: There are not enough rules to produce a node with desired properties: convention=ENUMERABLE, sort=[].
Missing conversions are LogicalCalc[convention: NONE -> ENUMERABLE] (2 cases), LogicalCalc[convention: NONE -> ENUMERABLE, sort: [] -> [0]]
There are 3 empty subsets:
Empty subset 0: rel#113660:RelSubset#8.ENUMERABLE.[0, 1], the relevant part of the original plan is as follows
113624:LogicalCalc(expr#0..4=[{inputs}], expr#5=[10], expr#6=[>($t1, $t5)], order_id=[$t0], merchant_name=[$t4], $condition=[$t6])
113622:LogicalSort(subset=[rel#113623:RelSubset#7.NONE.[0, 1]], sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
113620:LogicalCalc(subset=[rel#113621:RelSubset#6.NONE.[]], expr#0..6=[{inputs}], proj#0..2=[{exprs}], remark=[$t4], merchant_name=[$t6])
113618:LogicalJoin(subset=[rel#113619:RelSubset#5.NONE.[]], condition=[=($3, $5)], joinType=[inner])
113613:LogicalCalc(subset=[rel#113614:RelSubset#2.NONE.[]], expr#0..5=[{inputs}], expr#6=[Sarg[(10..15)]], expr#7=[SEARCH($t1, $t6)], proj#0..4=[{exprs}], $condition=[$t7])
113504:LogicalTableScan(subset=[rel#113610:RelSubset#0.NONE.[]], table=[[public, t_order]])
113616:LogicalCalc(subset=[rel#113617:RelSubset#4.NONE.[]], expr#0..5=[{inputs}], merchant_id=[$t0], merchant_name=[$t2])
113509:LogicalTableScan(subset=[rel#113615:RelSubset#3.NONE.[]], table=[[public, t_merchant]])
Empty subset 1: rel#113664:RelSubset#1.ENUMERABLE.[0], the relevant part of the original plan is as follows
113611:LogicalCalc(expr#0..5=[{inputs}], expr#6=[CAST($t2):VARCHAR], expr#7=['finish':VARCHAR], expr#8=[=($t6, $t7)], order_id=[$t0], $condition=[$t8])
113504:LogicalTableScan(subset=[rel#113610:RelSubset#0.NONE.[]], table=[[public, t_order]])
Empty subset 2: rel#113666:RelSubset#8.ENUMERABLE.[0], the relevant part of the original plan is as follows
113624:LogicalCalc(expr#0..4=[{inputs}], expr#5=[10], expr#6=[>($t1, $t5)], order_id=[$t0], merchant_name=[$t4], $condition=[$t6])
113622:LogicalSort(subset=[rel#113623:RelSubset#7.NONE.[0, 1]], sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
113620:LogicalCalc(subset=[rel#113621:RelSubset#6.NONE.[]], expr#0..6=[{inputs}], proj#0..2=[{exprs}], remark=[$t4], merchant_name=[$t6])
113618:LogicalJoin(subset=[rel#113619:RelSubset#5.NONE.[]], condition=[=($3, $5)], joinType=[inner])
113613:LogicalCalc(subset=[rel#113614:RelSubset#2.NONE.[]], expr#0..5=[{inputs}], expr#6=[Sarg[(10..15)]], expr#7=[SEARCH($t1, $t6)], proj#0..4=[{exprs}], $condition=[$t7])
113504:LogicalTableScan(subset=[rel#113610:RelSubset#0.NONE.[]], table=[[public, t_order]])
113616:LogicalCalc(subset=[rel#113617:RelSubset#4.NONE.[]], expr#0..5=[{inputs}], merchant_id=[$t0], merchant_name=[$t2])
113509:LogicalTableScan(subset=[rel#113615:RelSubset#3.NONE.[]], table=[[public, t_merchant]])
Root: rel#113636:RelSubset#13.ENUMERABLE.[]
Original rel:
```
### Example codes for reproduce this issue (such as a github link).
```
drop table if exists t_order;
drop table if exists t_merchant;
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_merchant (merchant_id int primary key, country_id int not null, merchant_name varchar(50) not null, business_code varchar(50) not null, telephone varchar(50) not null, creation_date date not null);
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 into t_merchant values(1, 86, 'tencent', '86000001', '86100000001', '2017-08-08');
insert into t_merchant values(2, 86, 'haier', '86000002', '86100000002', '2017-08-08');
insert into t_merchant values(3, 86, 'huawei', '86000003', '86100000003', '2017-08-08');
insert into t_merchant values(4, 86, 'alibaba', '86000004', '86100000004', '2017-08-08');
insert into t_merchant values(5, 86, 'lenovo', '86000005', '86100000005', '2017-08-08');
insert into t_merchant values(6, 86, 'moutai', '86000006', '86100000006', '2017-12-08');
insert into t_merchant values(7, 86, 'baidu', '86000007', '86100000007', '2017-08-08');
insert into t_merchant values(8, 86, 'xiaomi', '86000008', '86100000008', '2017-08-08');
insert into t_merchant values(9, 86, 'vivo', '86000009', '86100000009', '2017-11-08');
insert into t_merchant values(10, 86, 'oppo', '86000010', '86100000010', '2017-08-08');
insert into t_merchant values(11, 1, 'google', '01000011', '01100000011', '2017-08-08');
insert into t_merchant values(12, 1, 'walmart', '01000012', '01100000012', '2017-08-18');
insert into t_merchant values(13, 1, 'amazon', '01000013', '01100000013', '2017-08-08');
insert into t_merchant values(14, 1, 'apple', '01000014', '01100000014', '2017-07-08');
insert into t_merchant values(15, 1, 'microsoft', '01000015', '01100000015', '2017-08-08');
insert into t_merchant values(16, 1, 'dell', '01000016', '01100000016', '2017-08-08');
insert into t_merchant values(17, 1, 'johnson', '01000017', '01100000017', '2017-08-08');
insert into t_merchant values(18, 1, 'intel', '01000018', '01100000018', '2017-06-08');
insert into t_merchant values(19, 1, 'hp', '01000019', '01100000019', '2017-08-08');
insert into t_merchant values(20, 1, 'tesla', '01000020', '01100000020', '2017-08-08');
```
```
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] In the federation scenario，a table join a view and group by ,when 'where condition' include view's column，return error. [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #22841:
URL: https://github.com/apache/shardingsphere/issues/22841#issuecomment-2026017927
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] peilinqian commented on issue #22841: In the federation scenario，a table join a view and group by ,when 'where condition' include view's column，return error.
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22841:
URL: https://github.com/apache/shardingsphere/issues/22841#issuecomment-1367759681
1. TRANSACTION_RANDOM
无论是否在事务中，读请求采用 random 策略路由到多个 replica
2. TRANSACTION_ROUND_ROBIN
无论是否在事务中，读请求采用 round_robin 策略路由到多个 replica
3. TRANSACTION_WEIGHT
无论是否在事务中，读请求采用 weight 策略路由到多个 replica
4. FIXED_REPLICA_RANDOM
显式开启事务，读请求采用 random 策略路由到一个固定 replica；不开事务，每次读流量使用指
定算法路由到不同的 replica
5. FIXED_REPLICA_ROUND_ROBIN
显式开启事务，读请求采用 round_robin 策略路由到一个固定 replica；不开事务，每次读流量
使用指定算法路由到不同的 replica
FIXED_6. REPLICA_WEIGHT
显式开启事务，读请求采用 weight 策略路由到一个固定 replica；不开事务，每次读流量使用指定算
法路由到不同的 replica
7. FIXED_PRIMARY
读请求全部路由到 primary
8. RANDOM
事务内，读请求路由到 primary，事务外，采用 random 策略路由到 replica
9. ROUND_ROBIN
事务内，读请求路由到 primary，事务外，采用 round robin 策略路由到 replica
10. WEIGHT
事务内，读请求路由到 primary，事务外，采用 weight 策略路由到 replica
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] peilinqian commented on issue #22841: In the federation scenario，a table join a view and group by ,when 'where condition' include view's column，return error.
Posted by "peilinqian (via GitHub)" <gi...@apache.org>.
peilinqian commented on issue #22841:
URL: https://github.com/apache/shardingsphere/issues/22841#issuecomment-1694205427
```
sed -i "/<\\\/dependencies>/i \\\ <dependency>\\\n <groupId>org.apache.shardingsphere<\\\/groupId>\\\n <artifactId>shardingsphere-transaction-xa-narayana<\\\/artifactId>\\\n <version>\\\\\\\${project.version}<\\\/version>\\\n <\\\/dependency>" $ss_source_dir/kernel/transaction/type/xa/core/pom.xml
grep 'shardingsphere-transaction-xa-narayana' $ss_source_dir/kernel/transaction/type/xa/core/pom.xml
sed -i 's#<scope>provided</scope>#<!--<scope>provided</scope>-->#' $ss_source_dir/kernel/transaction/type/xa/provider/narayana/pom.xml
grep '<scope>provided</scope>' $ss_source_dir/kernel/transaction/type/xa/provider/narayana/pom.xml
sed -i "/<\\\/dependencies>/i \\\ <dependency>\\\n <groupId>org.apache.shardingsphere<\\\/groupId>\\\n <artifactId>shardingsphere-global-clock-tso-provider-redis<\\\/artifactId>\\\n <version>\\\\\\\${project.version}<\\\/version>\\\n <\\\/dependency>" $ss_source_dir/distribution/proxy/pom.xml
grep 'shardingsphere-global-clock-tso-provider-redis' $ss_source_dir/distribution/proxy/pom.xml
sed -i 's#<scope>provided</scope>#<!--<scope>provided</scope>-->#' $ss_source_dir/kernel/global-clock/type/tso/provider/redis/pom.xml
grep '<scope>provided</scope>' $ss_source_dir/kernel/global-clock/type/tso/provider/redis/pom.xml
```
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org