[Bug] NOT IN didn't get expected result

[TingYu404]

Search before asking

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

Version

Doris 1.2.6

What's Wrong?

Expected results wasn't provided by Doris 1.2.6, which I used 'NOT IN' to filter A table‘s data and B table's(auxiliary table) data contains NULL. U guys can use the table structure and data below to reproduce the scenario.

CREATE TABLE temp.temp_sss ( tracking_number varchar(200) NULL ) ENGINE=OLAP UNIQUE KEY(tracking_number) COMMENT 'OLAP' DISTRIBUTED BY HASH(tracking_number) BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.olap: 1, tag.location.default: 3", "in_memory" = "false", "storage_format" = "V2", "function_column.sequence_type"="largeint", "disable_auto_compaction" = "false" );

insert into temp.temp_sss values('344444'); insert into temp.temp_sss values(''); insert into temp.temp_sss values(null);

CREATE TABLE temp_sss11 ( tracking_number varchar(200) NULL ) ENGINE=OLAP UNIQUE KEY(tracking_number) COMMENT 'OLAP' DISTRIBUTED BY HASH(tracking_number) BUCKETS 1 PROPERTIES ( "replication_allocation" = "tag.location.olap: 1, tag.location.default: 3", "in_memory" = "false", "storage_format" = "V2", "function_column.sequence_type"="largeint", "disable_auto_compaction" = "false" );

insert into temp.temp_sss11 values('344444'); insert into temp.temp_sss11 values('aaaabbbs'); insert into temp.temp_sss11 values(null);

SELECT * FROM temp.temp_sss11 WHERE tracking_number NOT IN (SELECT tracking_number FROM temp.temp_sss);

What You Expected?

The result should contains A table's data not exists in B table

How to Reproduce?

No response

Anything Else?

No response

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

Code of Conduct

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

[ixzc]

If you remove the null, the result is what you expect. Your result is empty because the result data has null.