v5.4.0 There may be a bug in "left join".

[Cindyxin228]

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.

[sandynz]

Hi @Cindyxin228 , what's your sharding_db configuration (dataSources and rules)?

[Cindyxin228]

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

[Cindyxin228]

Hi @Cindyxin228 , what's your sharding_db configuration (dataSources and rules)?

Hi~I copied my configration in the comment just now.

[sandynz]

Hi @strongduanmu Could you help to have a look at it?

[strongduanmu]

Hi @Cindyxin228, can you open sql-show and show actual sql?

[Cindyxin228]

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.

[Cindyxin228]

Hi @Cindyxin228, can you open sql-show and show actual sql?

Excuse me, is there any progress on this issue recently