incubator-doris The SQL operation returns the wrong set of data when there is a constant in select clause

kxeu7u2r  于 2022-04-22  发布在  Java
关注(0)|答案(0)|浏览(85)

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:

  1. 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
...
  1. 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)
  1. run sql as above

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题