blackfox1983 opened a new issue #3801: URL: https://github.com/apache/incubator-doris/issues/3801
**Describe the bug** There is a table: ``` MySQL [mydb]> desc mytable; +-------------+------------+------+-------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------+------+-------+---------+-------+ | user_id | BIGINT | Yes | true | N/A | | | value | VARCHAR(1) | Yes | true | N/A | | | ArriveTime | BIGINT | Yes | false | N/A | NONE | | UpdateTime | BIGINT | Yes | false | N/A | NONE | +-------------+------------+------+-------+---------+-------+ ``` Data in the table are as below: ``` MySQL [mydb]> select user_id, value, ArriveTime from mytable limit 10; +---------+-----------------+------------+ | user_id | value | UpdateTime | +---------+-----------------+------------+ | 110 | hello_world_123 | 1591097156 | | 120 | hello@world@123 | 1591100756 | | 150 | hello_world@123 | 1591273556 | +---------+-----------------+------------+ ``` Now, this sql ``` select split_part(value, '_', 1) as bl from mytable where from_unixtime(UpdateTime, "%Y%m%d") = '20200602' ``` will show as below: ``` +-------+ | bl | +-------+ | hello | | NULL | +-------+ ``` So, we need filter the `NULL` rows with the sql: ``` select distinct bl from (select split_part(value '_', 1) as bl from mytable where from_unixtime(UpdateTime, "%Y%m%d") = '20200602') t where t.bl is NULL; ``` **It should be show as follows:** ``` +------+ | bl | +------+ | NULL | +------+ ``` **BUT, In fact, the result returned was** ``` MySQL [mmydb]> select distinct bl from (select split_part(value '_', 1) as bl from mytable where from_unixtime(UpdateTime, "%Y%m%d") = '20200602') t where t.bl is NULL; 3 rows in set (0.01 sec) ``` **It's a wrong result! It's a wrong result! It's a wrong result!** ---------------------------------------------------------------- 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: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
