Describe the bug
MySQL [doris]> select a.name,b.name,flag from (select name from ltable) a left join (select stuName as name,1 as flag from rtable) b on a.name = b.name;
+------+------+------+
| name | name | flag |
+------+------+------+
| m | m | 1 |
| l | NULL | 1 |
| i | NULL | 1 |
+------+------+------+
3 rows in set (0.01 sec)
But the expectation should be as below:
+------+------+------+
| name | name | flag |
+------+------+------+
| m | m | 1 |
| l | NULL | NULL |
| i | NULL | NULL |
+------+------+------+
3 rows in set (0.01 sec)
Similar scenarios includefull joinandright join.
To Reproduce
Steps to reproduce the behavior:
- create table
CREATE TABLE `ltable` (
`a` date NULL COMMENT "",
`name` varchar(96) NULL COMMENT "",
`age` int(11) NULL COMMENT "",
`adress` varchar(96) NULL COMMENT ""
) ENGINE=OLAP
...
CREATE TABLE `rtable` (
`a` date NULL COMMENT "",
`name` varchar(96) NULL COMMENT "",
`age` int(11) NULL COMMENT "",
`adress` varchar(96) NULL COMMENT ""
) ENGINE=OLAP
...
- insert some data
MySQL [doris]> insert into ltable values('2020-10-19', 'm', 100, 'here');
Query OK, 1 row affected (0.09 sec)
{'label':'insert_31aa7273c2ff424d-81f7ec9a5ebfae0f', 'status':'VISIBLE', 'txnId':'1122'}
...
MySQL [doris]> insert into rtable values('2020-10-19', 'm', 10, 11, 12);
Query OK, 1 row affected (0.04 sec)
{'label':'insert_e518b0864bba4e1c-a04713b3f2b12dd8', 'status':'VISIBLE', 'txnId':'1125'}
...
MySQL [doris]> select * from ltable;
+------------+------+------+--------+
| a | name | age | adress |
+------------+------+------+--------+
| 2020-10-19 | m | 100 | here |
| 2020-10-19 | l | 200 | here |
| 2020-10-19 | i | 300 | here |
+------------+------+------+--------+
3 rows in set (0.00 sec)
MySQL [doris]> select * from rtable;
+------------+---------+------+-------+-------+
| a_date | stuName | sex | score | grade |
+------------+---------+------+-------+-------+
| 2020-10-19 | m | 10 | 11 | 12 |
| 2020-10-19 | j | 20 | 21 | 22 |
| 2020-10-19 | k | 30 | 31 | 32 |
+------------+---------+------+-------+-------+
3 rows in set (0.00 sec)
- run sql as above
暂无答案!
目前还没有任何答案,快来回答吧!