[Bug] After left join, there are two rows of the same key after group by

[gj-zhang]

Search before asking

• I had searched in the issues and found no similar issues.

Version

1.0.0-rc03

What's Wrong?

What You Expected?

result one row

How to Reproduce?

CREATE TABLE `left_table`
(
 `id` text NULL COMMENT "",
 `device_id` text NULL COMMENT ""
) ENGINE = OLAP UNIQUE KEY(`id`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`id`) BUCKETS 4
PROPERTIES (
"replication_allocation" = "tag.location.default: 2",
"in_memory" = "false",
"storage_format" = "V2"
);

insert into left_table values ('1', '1'),('2', '2'), ('3', '3'), ('4', '4');

CREATE TABLE `right_table`
(
 `device_id` text NULL COMMENT "",
 `device_name` text NULL COMMENT ""
) ENGINE = OLAP UNIQUE KEY(`device_id`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`device_id`) BUCKETS 4
PROPERTIES (
"replication_allocation" = "tag.location.default: 2",
"in_memory" = "false",
"storage_format" = "V2"
);

insert into right_table values ('1', 'device_name1'), ('3', null);

select count(left_table.device_id), rt.device_name
from left_table
 left join right_table rt
 on left_table.device_id = rt.device_id
where rt.device_name is null
group by rt.device_name
order by rt.device_name nulls first

Anything Else?

merge #8822 code

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[LOVEGISER]

1. i am run this sql command in Doris version 1.0.0-rc03-8b9a70c , the result is correct,try to update to this version.

mysql> select count(left_table.device_id), rt.device_name from left_table left join right_table rt
on left_table.device_id = rt.device_id where rt.device_name is null group by rt.device_name order by rt.device_name ;
+---------------------------------+-------------+
| count(left_table.device_id) | device_name |
+---------------------------------+-------------+
| 3 | NULL |
+---------------------------------+-------------+
1 row in set (0.03 sec)

[gj-zhang]

how to get the code or binary file?

[LOVEGISER]

1. I am test in version of 1.0.0-rc03-8b9a70c
2. use follow command to get 8b9a70c commit and rebuild

git checkout 8b9a70c -b 1.0.0-rc03-8b9a70c

[gj-zhang]

Hello, 1.0.0-rc03-8b9a70c what I built with this tag. and i merge #8822 code. Does it matter?

[cambyzju]

reproduce it with version(1.0.0-rc03-8b9a70c) while set enable_vectorized_engine=true;.