You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "Cindyxin228 (via GitHub)" <gi...@apache.org> on 2023/08/10 07:07:32 UTC
[GitHub] [shardingsphere] Cindyxin228 opened a new issue, #28014: v5.4.0 There may be a bug in "left join".
Cindyxin228 opened a new issue, #28014:
URL: https://github.com/apache/shardingsphere/issues/28014
# There's something wrong with "left join"
My version is 5.4.0.
When I tried to use "left join", I found that a column which should be in the result is missing. Here's the more specific explanation.
I execute the following sql statements in mariaDB and in sharding_db, the virtual database processed through shardingsphere-proxy.
```
DROP TABLE IF EXISTS students,
student_skill;
create table students
(
user_id INT NOT NULL,
order_id INT NOT NULL,
st_id varchar(20),
name varchar(10),
age INT(2),
tol_score INT(3)
);
insert into students values(1, 1, '973231','wangbindu',22,501);
insert into students values(1, 2, '973232','zhuzhijing',21,538);
insert into students values(1, 3, '973233','gaojing',21,576);
create table student_skill
(
user_id INT NOT NULL,
order_item_id INT NOT NULL,
st_id varchar(20),
skill varchar(20)
);
insert into student_skill values(2, 1, '973231','basketball');
insert into student_skill values(2, 2, '973232', null);
insert into student_skill values(2, 3, '973233','football');
SELECT s.*, IFNULL(ss.skill, '') AS skill
FROM students s
LEFT JOIN student_skill ss ON s.st_id = ss.st_id
order by s.tol_score;
```
And the result in mariaDB is as follows.
```
+---------+----------+--------+------------+------+-----------+------------+
| user_id | order_id | st_id | name | age | tol_score | skill |
+---------+----------+--------+------------+------+-----------+------------+
| 1 | 1 | 973231 | wangbindu | 22 | 501 | basketball |
| 1 | 2 | 973232 | zhuzhijing | 21 | 538 | |
| 1 | 3 | 973233 | gaojing | 21 | 576 | football |
+---------+----------+--------+------------+------+-----------+------------+
```
The result in sharding_db is as follows.
```
+---------+----------+--------+------------+------+-----------+-------+
| user_id | order_id | st_id | name | age | tol_score | skill |
+---------+----------+--------+------------+------+-----------+-------+
| 1 | 1 | 973231 | wangbindu | 22 | 501 | |
| 1 | 2 | 973232 | zhuzhijing | 21 | 538 | |
| 1 | 3 | 973233 | gaojing | 21 | 576 | |
+---------+----------+--------+------------+------+-----------+-------+
```
The result in mariaDB is correct, and we can see the results have a column missing in sharding_db. Therefore, I think there's maybe a bug here.
--
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] Cindyxin228 commented on issue #28014: v5.4.0 There may be a bug in "left join".
Posted by "Cindyxin228 (via GitHub)" <gi...@apache.org>.
Cindyxin228 commented on issue #28014:
URL: https://github.com/apache/shardingsphere/issues/28014#issuecomment-1676303558
> Hi @Cindyxin228, can you open `sql-show` and show actual sql?
Hi~
I'm sorry. I didn't find how to open sql-show in shardingsphere-proxy. and by using ```setprofiling= 1```, I get the result
```
MySQL [sharding_db]> explain SELECT s.*, IFNULL(ss.skill, '') AS skill
-> FROM students s
-> LEFT JOIN student_skill ss ON s.st_id = ss.st_id
-> order by s.tol_score;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| 1 | SIMPLE | s | ALL | NULL | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | ss | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
2 rows in set (0.010 sec)
```
and
```
MySQL [sharding_db]> show profiles;
Empty set (0.004 sec)
```
I'm sorry very much that it seems I didn't find valuable information. But I can share how it leads to the question above.
The version is 5.4.0.
First, copy the configuration I comment above(config-sharding.yaml), and sever.yaml is
```
mode:
type: Standalone
repository:
type: JDBC
authority:
users:
- user: root
password: root
- user: sharding
password: sharding
privilege:
type: ALL_PERMITTED
```.
After configuration, copy the sql I give in the first comment and then execute. Then the question arises.
And if you still need that I give information about the actual sql. May you please tell me how to open that and get the actual sql. Thanks very much.
--
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] Cindyxin228 commented on issue #28014: v5.4.0 There may be a bug in "left join".
Posted by "Cindyxin228 (via GitHub)" <gi...@apache.org>.
Cindyxin228 commented on issue #28014:
URL: https://github.com/apache/shardingsphere/issues/28014#issuecomment-1673109634
> Hi @Cindyxin228 , what's your sharding_db configuration (dataSources and rules)?
'''
databaseName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
students:
actualDataNodes: ds_${0..1}.students_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: students_inline
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
student_skill:
actualDataNodes: ds_${0..1}.student_skill_${0..1}
tableStrategy:
standard:
shardingColumn: order_item_id
shardingAlgorithmName: student_skill_inline
keyGenerateStrategy:
column: order_item_id
keyGeneratorName: snowflake
website:
actualDataNodes: ds_${0..1}.website_${0..1}
tableStrategy:
standard:
shardingColumn: web_id
shardingAlgorithmName: website_inline
keyGenerateStrategy:
column: web_id
keyGeneratorName: snowflake
new_web:
actualDataNodes: ds_${0..1}.new_web_${0..1}
tableStrategy:
standard:
shardingColumn: new_web_id
shardingAlgorithmName: new_web_inline
keyGenerateStrategy:
column: new_web_id
keyGeneratorName: snowflake
bindingTables:
- students,student_skill,website,new_web
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
students_inline:
type: INLINE
props:
algorithm-expression: students_${order_id % 2}
student_skill_inline:
type: INLINE
props:
algorithm-expression: student_skill_${order_item_id % 2}
website_inline:
type: INLINE
props:
algorithm-expression: website_${web_id % 2}
new_web_inline:
type: INLINE
props:
algorithm-expression: new_web_${new_web_id % 2}
keyGenerators:
snowflake:
type: SNOWFLAKE
'''
--
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] Cindyxin228 commented on issue #28014: v5.4.0 There may be a bug in "left join".
Posted by "Cindyxin228 (via GitHub)" <gi...@apache.org>.
Cindyxin228 commented on issue #28014:
URL: https://github.com/apache/shardingsphere/issues/28014#issuecomment-1690970672
> Hi @Cindyxin228, can you open `sql-show` and show actual sql?
Excuse me, is there any progress on this issue recently
--
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] sandynz commented on issue #28014: v5.4.0 There may be a bug in "left join".
Posted by "sandynz (via GitHub)" <gi...@apache.org>.
sandynz commented on issue #28014:
URL: https://github.com/apache/shardingsphere/issues/28014#issuecomment-1674124560
Hi @strongduanmu Could you help to have a look at it?
--
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
Re: [I] v5.4.0 There may be a bug in "left join". [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #28014: v5.4.0 There may be a bug in "left join".
URL: https://github.com/apache/shardingsphere/issues/28014
--
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] sandynz commented on issue #28014: v5.4.0 There may be a bug in "left join".
Posted by "sandynz (via GitHub)" <gi...@apache.org>.
sandynz commented on issue #28014:
URL: https://github.com/apache/shardingsphere/issues/28014#issuecomment-1673082489
Hi @Cindyxin228 , what's your sharding_db configuration (dataSources and rules)?
--
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] Cindyxin228 commented on issue #28014: v5.4.0 There may be a bug in "left join".
Posted by "Cindyxin228 (via GitHub)" <gi...@apache.org>.
Cindyxin228 commented on issue #28014:
URL: https://github.com/apache/shardingsphere/issues/28014#issuecomment-1673117494
> Hi @Cindyxin228 , what's your sharding_db configuration (dataSources and rules)?
Hi~I copied my configration in the comment just now.
--
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] github-actions[bot] commented on issue #28014: v5.4.0 There may be a bug in "left join".
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #28014:
URL: https://github.com/apache/shardingsphere/issues/28014#issuecomment-1732399404
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
Re: [I] v5.4.0 There may be a bug in "left join". [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #28014:
URL: https://github.com/apache/shardingsphere/issues/28014#issuecomment-3636832166
## Issue Understanding
- On Proxy 5.4.0 with sharding, a LEFT JOIN between students and student_skill (join on st_id, select IFNULL(ss.skill,'')) returns skill as empty for all rows in sharding_db, while native MariaDB shows correct skill values.
- Config: databases sharded by user_id (ds_${user_id % 2}); students sharded by order_id; student_skill sharded by order_item_id; the two tables are declared as bindingTables. Sample data uses user_id=1 for students and user_id=2 for student_skill.
## Root Cause
- Binding tables require the same sharding key/algorithm and co-located sharding key values for joined rows. Here the DB sharding key is the same (user_id), but the sample data has different user_id values, so students rows are on ds_1 while student_skill rows are on
ds_0. Binding still forces routing to a single shard, so the join sees no matching right-table data and skill is empty.
- The table sharding keys also differ (order_id vs order_item_id), which violates the binding-table requirement of identical sharding keys/algorithms, increasing misrouting risk.
## Analysis
- With no sharding key condition in the join, binding makes the join reuse the main table’s routing; because the two tables’ data are not co-located, the routed shard lacks matching student_skill rows, yielding empty skill.
- Removing binding or ensuring co-location would make the join hit the correct shards and match native results. This is a configuration/data misalignment, not a LEFT JOIN execution bug.
## Conclusion
- The missing skill values are caused by sharding rule/data not satisfying binding-table constraints; it is not a Proxy LEFT JOIN bug.
- Recommendations:
1. Only declare bindingTables when both tables use the same sharding key/algorithm and business data keeps the key values aligned;
2. Fix rules/data so user_id aligns across both tables; if co-location cannot be guaranteed, remove binding or mark the right table as broadcast;
3. Enable sql-show: true to inspect actual routed SQL and target shards to verify routing.
--
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] Cindyxin228 commented on issue #28014: v5.4.0 There may be a bug in "left join".
Posted by "Cindyxin228 (via GitHub)" <gi...@apache.org>.
Cindyxin228 commented on issue #28014:
URL: https://github.com/apache/shardingsphere/issues/28014#issuecomment-1673106933
databaseName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
students:
actualDataNodes: ds_${0..1}.students_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: students_inline
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
student_skill:
actualDataNodes: ds_${0..1}.student_skill_${0..1}
tableStrategy:
standard:
shardingColumn: order_item_id
shardingAlgorithmName: student_skill_inline
keyGenerateStrategy:
column: order_item_id
keyGeneratorName: snowflake
bindingTables:
- students,student_skill
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
students_inline:
type: INLINE
props:
algorithm-expression: students_${order_id % 2}
student_skill_inline:
type: INLINE
props:
algorithm-expression: student_skill_${order_item_id % 2}
keyGenerators:
snowflake:
type: SNOWFLAKE
--
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] Cindyxin228 commented on issue #28014: v5.4.0 There may be a bug in "left join".
Posted by "Cindyxin228 (via GitHub)" <gi...@apache.org>.
Cindyxin228 commented on issue #28014:
URL: https://github.com/apache/shardingsphere/issues/28014#issuecomment-1673114481
```
databaseName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
students:
actualDataNodes: ds_${0..1}.students_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: students_inline
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
student_skill:
actualDataNodes: ds_${0..1}.student_skill_${0..1}
tableStrategy:
standard:
shardingColumn: order_item_id
shardingAlgorithmName: student_skill_inline
keyGenerateStrategy:
column: order_item_id
keyGeneratorName: snowflake
bindingTables:
- students,student_skill
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
students_inline:
type: INLINE
props:
algorithm-expression: students_${order_id % 2}
student_skill_inline:
type: INLINE
props:
algorithm-expression: student_skill_${order_item_id % 2}
keyGenerators:
snowflake:
type: SNOWFLAKE
```
--
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 #28014: v5.4.0 There may be a bug in "left join".
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #28014:
URL: https://github.com/apache/shardingsphere/issues/28014#issuecomment-1674234635
Hi @Cindyxin228, can you show sql-show?
--
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] Cindyxin228 commented on issue #28014: v5.4.0 There may be a bug in "left join".
Posted by "Cindyxin228 (via GitHub)" <gi...@apache.org>.
Cindyxin228 commented on issue #28014:
URL: https://github.com/apache/shardingsphere/issues/28014#issuecomment-1676304961
> Hi @Cindyxin228, can you open `sql-show` and show actual sql?
Hi~
I'm sorry that I didn't find how to open sql-show in shardingsphere-proxy. and by using ```set profiling= 1```, I get the result
```
MySQL [sharding_db]> explain SELECT s.*, IFNULL(ss.skill, '') AS skill
-> FROM students s
-> LEFT JOIN student_skill ss ON s.st_id = ss.st_id
-> order by s.tol_score;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| 1 | SIMPLE | s | ALL | NULL | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | ss | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
2 rows in set (0.010 sec)
```
and
```
MySQL [sharding_db]> show profiles;
Empty set (0.004 sec)
```
I'm sorry very much that it seems I didn't find valuable information. But I can share how it leads to the question above.
The version is 5.4.0.
First, copy the configuration I comment above(config-sharding.yaml), and sever.yaml is
```
mode:
type: Standalone
repository:
type: JDBC
authority:
users:
- user: root
password: root
- user: sharding
password: sharding
privilege:
type: ALL_PERMITTED
```
After configuration, copy the sql I give in the first comment and then execute. Then the question arises.
And if you still need that I give information about the actual sql. Could you please tell me how to open that and get the actual sql. Thanks very much.
--
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