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/11/28 07:58:40 UTC
[GitHub] [shardingsphere] peilinqian opened a new issue, #22480: In the federation scenario, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is incorrect
peilinqian opened a new issue, #22480:
URL: https://github.com/apache/shardingsphere/issues/22480
### Which version of ShardingSphere did you use?
ShardingSphere-5.2.2-SNAPSHOT
Commit ID: dirty-753c0cee8ee6fd3db00536da55b64bc5198a3758
Commit Message: Optimize sqlFederationExecutor init logic when sqlFederationType modify dynamically (https://github.com/apache/shardingsphere/pull/22209)
Branch: https://github.com/apache/shardingsphere/commit/753c0cee8ee6fd3db00536da55b64bc5198a3758
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, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is correct
### Actual behavior
In the federation scenario, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is correct
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
```
--create table
drop table if exists t_merchant; --single table
drop table if exists t_order; --sharding db tb
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);
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);
-- 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(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');
-- t_merchant
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');
--create view
create view left_join_view as select o.order_id, o.user_id, m.merchant_name from t_new_order o left join t_merchant m on o.merchant_id = m.merchant_id where o.user_id > 20 order by 1,2,3;
--select view
select * from left_join_view;
--select limit 4,5
select * from left_join_view limit 4,5;
```
result
```
test_db=> --select view
test_db=> select * from left_join_view;
order_id | user_id | merchant_name
----------+---------+---------------
2100 | 21 | baidu
2101 | 21 | xiaomi
2200 | 22 | google
2201 | 22 | walmart
2300 | 23 | microsoft
2301 | 23 | dell
2400 | 24 | hp
2401 | 24 | tesla
2500 | 25 | huawei
2501 | 25 | alibaba
2600 | 26 | baidu
2601 | 26 | xiaomi
2700 | 27 | google
2701 | 27 | walmart
2800 | 28 | microsoft
2801 | 28 | dell
2900 | 29 | hp
2901 | 29 | tesla
2902 | 29 | hp
2903 | 29 | tesla
(20 rows)
test_db=> --select limit 4,5 ，the order of result is wrong
test_db=> select * from left_join_view limit 4,5;
order_id | user_id | merchant_name
----------+---------+---------------
2201 | 22 | walmart
2401 | 24 | tesla
2601 | 26 | xiaomi
2801 | 28 | dell
2100 | 21 | baidu
(5 rows)
```
**expect result**
```
test=# --select limit 4,5
test=# select * from left_join_view limit 4,5;
order_id | user_id | merchant_name
----------+---------+---------------
2300 | 23 | microsoft
2301 | 23 | dell
2400 | 24 | hp
2401 | 24 | tesla
2500 | 25 | huawei
(5 rows)
```
### Example codes for reproduce this issue (such as a github link).
--
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
[GitHub] [shardingsphere] strongduanmu commented on issue #22480: In the federation scenario, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is incorrect
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22480:
URL: https://github.com/apache/shardingsphere/issues/22480#issuecomment-1328695350
@peilinqian Thank you for your feedback, I will check this issue.
--
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] strongduanmu commented on issue #22480: In the federation scenario, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is incorrect
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22480:
URL: https://github.com/apache/shardingsphere/issues/22480#issuecomment-1328742748
I have found that view expander has lost the order by segment in view definition.
<img width="708" alt="image" src="https://user-images.githubusercontent.com/10829171/204235141-8f4a0eb0-c3e8-4613-8ac1-1c5a2becba0f.png">
The following is the detailed logic.
```java
/**
* Implementation of {@link #convertSelect(SqlSelect, boolean)};
* derived class may override.
*/
protected void convertSelectImpl(
final Blackboard bb,
SqlSelect select) {
convertFrom(
bb,
select.getFrom());
// We would like to remove ORDER BY clause from an expanded view, except if
// it is top-level or affects semantics.
//
// Top-level example. Given the view definition
// CREATE VIEW v AS SELECT * FROM t ORDER BY x
// we would retain the view's ORDER BY in
// SELECT * FROM v
// or
// SELECT * FROM v WHERE y = 5
// but remove the view's ORDER BY in
// SELECT * FROM v ORDER BY z
// and
// SELECT deptno, COUNT(*) FROM v GROUP BY deptno
// because the ORDER BY and GROUP BY mean that the view is not 'top level' in
// the query.
//
// Semantics example. Given the view definition
// CREATE VIEW v2 AS SELECT * FROM t ORDER BY x LIMIT 10
// we would never remove the ORDER BY, because "ORDER BY ... LIMIT" is about
// semantics. It is not a 'pure order'.
if (RelOptUtil.isPureOrder(castNonNull(bb.root))
&& config.isRemoveSortInSubQuery()) {
// Remove the Sort if the view is at the top level. Also remove the Sort
// if there are other nodes, which will cause the view to be in the
// sub-query.
if (!bb.top
|| validator().isAggregate(select)
|| select.isDistinct()
|| select.hasOrderBy()
|| select.getFetch() != null
|| select.getOffset() != null) {
bb.setRoot(castNonNull(bb.root).getInput(0), 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
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #22480: In the federation scenario, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is incorrect
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22480:
URL: https://github.com/apache/shardingsphere/issues/22480#issuecomment-1328822025
According to the above logic, when we execute `select * from left_join_view limit 4,5;`, calcite will remove the order by in the view, which affects the sql semantics. To solve this problem, we temporarily configure `withRemoveSortInSubQuery` to false, and then fix this bug in calcite.
--
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] wsm12138 commented on issue #22480: In the federation scenario, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is incorrect
Posted by GitBox <gi...@apache.org>.
wsm12138 commented on issue #22480:
URL: https://github.com/apache/shardingsphere/issues/22480#issuecomment-1330032534
710f53e45aef175ad9b6c33121b8b0e1a90e6157
proxy
<img width="1630" alt="image" src="https://user-images.githubusercontent.com/86462784/204432709-3560f742-6b54-4d90-aa35-c17e9ad17047.png">
openGauss
<img width="882" alt="image" src="https://user-images.githubusercontent.com/86462784/204432821-6a9ce9ed-3f53-413e-a03a-699cde417be7.png">
--
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] yx9o closed issue #22480: In the federation scenario, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is incorrect
Posted by GitBox <gi...@apache.org>.
yx9o closed issue #22480: In the federation scenario, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is incorrect
URL: https://github.com/apache/shardingsphere/issues/22480
--
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 #22480: In the federation scenario, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is incorrect
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22480:
URL: https://github.com/apache/shardingsphere/issues/22480#issuecomment-1348135908
**version**
```
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
```
**result ：pass**
```
test_db=> --create view
test_db=> create view left_join_view as select o.order_id, o.user_id, m.merchant_name from t_new_order o left join t_merchant m on o.merchant_id = m.merchant_id where o.user_id > 20 order by 1,2,3;
CREATE VIEW
test_db=> --select view
test_db=> select * from left_join_view;
order_id | user_id | merchant_name
----------+---------+---------------
2100 | 21 | baidu
2101 | 21 | xiaomi
2200 | 22 | google
2201 | 22 | walmart
2300 | 23 | microsoft
2301 | 23 | dell
2400 | 24 | hp
2401 | 24 | tesla
2500 | 25 | huawei
2501 | 25 | alibaba
2600 | 26 | baidu
2601 | 26 | xiaomi
2700 | 27 | google
2701 | 27 | walmart
2800 | 28 | microsoft
2801 | 28 | dell
2900 | 29 | hp
2901 | 29 | tesla
2902 | 29 | hp
2903 | 29 | tesla
(20 rows)
test_db=> --select limit 4,5
test_db=> select * from left_join_view limit 4,5;
order_id | user_id | merchant_name
----------+---------+---------------
2300 | 23 | microsoft
2301 | 23 | dell
2400 | 24 | hp
2401 | 24 | tesla
2500 | 25 | huawei
(5 rows)
test_db=>
```
--
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] strongduanmu commented on issue #22480: In the federation scenario, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is incorrect
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #22480:
URL: https://github.com/apache/shardingsphere/issues/22480#issuecomment-1328707505
When I execute create view statement, the following exception occurs.
```
sharding_db=> create view left_join_view as select o.order_id, o.user_id, m.merchant_name from t_new_order o left join t_merchant m on o.merchant_id = m.merchant_id where o.user_id > 20 order by 1,2,3;
ERROR: relation "t_new_order_0" does not exist on gaussdb
LINE 1: ...elect o.order_id, o.user_id, m.merchant_name from t_new_orde...
^
```
--
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 #22480: In the federation scenario, use ‘order by’ when creating a view, and use the ‘limit 10,5 ’clause when select the view, and the order of the result data is incorrect
Posted by GitBox <gi...@apache.org>.
peilinqian commented on issue #22480:
URL: https://github.com/apache/shardingsphere/issues/22480#issuecomment-1338632006
listen_addresses = '*'
max_connections = 4096
remote_read_mode = non_authentication
allow_concurrent_tuple_update = true
audit_enabled = off
cstore_buffers = 16MB
enable_alarm = off
enable_codegen = false
enable_data_replicate = off
full_page_writes = off
max_files_per_process = 100000
max_prepared_transactions = 2048
shared_buffers = 500GB
use_workload_manager = off
wal_buffers = 1GB
work_mem = 1MB
transaction_isolation = 'read committed'
default_transaction_isolation = 'read committed'
synchronous_commit = off
fsync = on
maintenance_work_mem = 2GB
vacuum_cost_limit = 10000
autovacuum = off
autovacuum_mode = vacuum
autovacuum_max_workers = 20
autovacuum_naptime = 5s
autovacuum_vacuum_cost_delay = 10
update_lockwait_timeout = 20min
enable_mergejoin = off
enable_nestloop = off
enable_hashjoin = off
enable_material = off
wal_log_hints = off
log_duration = off
checkpoint_timeout = 15min
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.02
enable_save_datachanged_timestamp = false
enable_double_write = on
enable_incremental_checkpoint = on
enable_opfusion = on
advance_xlog_file_num = 100
track_activities = off
enable_instr_track_wait = off
enable_instr_rt_percentile = off
track_counts = on
track_sql_count = off
enable_instr_cpu_timer = off
plog_merge_age = 0
session_timeout = 0
enable_instance_metric_persistent = off
enable_logical_io_statistics = off
enable_page_lsn_check = off
enable_user_metric_persistent = off
enable_xlog_prune = off
enable_resource_track = off
instr_unique_sql_count=0
remote_read_mode=non_authentication
wal_level = archive
hot_standby = off
hot_standby_feedback = off
client_min_messages = ERROR
log_min_messages = FATAL
enable_asp = off
enable_bbox_dump = off
bgwriter_flush_after = 32
wal_keep_segments = 1025
enable_bitmapscan = off
enable_seqscan = off
enable_beta_opfusion=on
checkpoint_segments=8000
enable_stmt_track=false
bgwriter_delay = 5s
incremental_checkpoint_timeout = 5min
xloginsert_locks = 16
walwriter_cpu_bind =0
wal_file_init_num = 20
pagewriter_sleep = 10ms
walwriter_sleep_threshold = 50000
gs_clean_timeout = 0
undo_zone_count = 0
pagewriter_thread_num = 2
max_redo_log_size = 400GB
max_io_capacity = 1GB
local_syscache_threshold = 32MB
segment_buffers = 1GB
numa_distribute_mode = 'all'
enable_thread_pool = on
thread_pool_attr = '400,4,(cpubind:0-31,48-127)'
#!/bin/bash
irq_list=`cat /proc/interrupts | grep enp135s0f0 | awk {'print $1'} | tr -d ":"`
irq_array_net=($irq_list)
cpu_array_irq=(32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47)
queue_num=16
for (( i=0;i<16;i++ ))
do
echo ${cpu_array_irq[$i]} > /proc/irq/${irq_array_net[$i]}/smp_affinity_list
done
for j in ${irq_array_net[@]}
do
cat /proc/irq/$j/smp_affinity_list
done
--
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