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:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
