[GitHub] [incubator-doris] blackfox1983 opened a new issue #4762: The SQL operation returns the wrong set of data when there is a constant in select clause

[GitBox <gi...@apache.org>]

blackfox1983 opened a new issue #4762:
URL: https://github.com/apache/incubator-doris/issues/4762


   **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 include **full join** and **right 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
   ...
   ```
   2. 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 table 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)
   ```
   3. run sql as above
   


----------------------------------------------------------------
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.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org