Bug #117852 Incorrect Handling of NULL Values with FIELD Function in SQL Query

• Submitted: 2 Apr 7:14
• Modified: 2 Apr 9:30
• Reporter: zhiqiang cheng
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.1.0, 8.0.41, 8.4.4, 9.2.0
• OS: Ubuntu (20.04)
• CPU Architecture: Any

[2 Apr 7:14] zhiqiang cheng

Description:
The FIELD function behaves unexpectedly when handling NULL values in the query.

How to repeat:
Create Test Table and Insert Sample Data:
CREATE TABLE `t1` (
  `c1` int
);

INSERT INTO `t1` VALUES (1);

CREATE TABLE `t2` (
  `c2` int
);

INSERT INTO `t2` VALUES (2);

Execute the Following Queries:
Query 1: Without WHERE Clause Filtering
select  
 ref_2.c2 as c_1,
 FIELD('I#', OCT(ref_2.c2)) = 0 as w_1
from 
  (t1 as ref_1
    left outer join t2 as ref_2
    on (ref_1.c1 = ref_2.c2 ))

Result:
+------+-----+
| c_1  | w_1 |
+------+-----+
| NULL |   1 |
+------+-----+
1 row in set (0.01 sec)

Query 2: With WHERE Clause Filtering
select  
 ref_2.c2 as c_1,
 FIELD('I#', OCT(ref_2.c2)) = 0 as w_1
from 
  (t1 as ref_1
    left outer join t2 as ref_2
    on (ref_1.c1 = ref_2.c2 ))
where FIELD('I#', OCT(ref_2.c2)) = 0;

Expected Result:
+------+-----+
| c_1  | w_1 |
+------+-----+
| NULL |   1 |
+------+-----+
1 row in set (0.01 sec)

Actual Result:
Empty set (0.01 sec)

Since the first query returned a row with NULL for c_1 and w_1 = 1, the second query should return the same result. However, it incorrectly returns an empty set.

mysql version:
github commit: 61a3a1d8ef15512396b4c2af46e922a19bf2b174
version: 9.1.0 

os version:
Linux ubuntu 5.15.0-134-generic #145~20.04.1-Ubuntu SMP Mon Feb 17 13:27:16 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux

[2 Apr 9:30] MySQL Verification Team

Hello zhiqiang cheng,

Thank you for the report and test case.

regards,
Umesh

[8 Apr 15:00] huahua xu

Because of the predicate on the inner table that rejects null, the outer join is replaced by an inner join and pushdown the predicate  condition to the hash join table.

mysql> explain format=tree select
    ->  ref_2.c2 as c_1,
    ->  FIELD('I#', OCT(ref_2.c2)) = 0 as w_1
    -> from
    ->   (t1 as ref_1
    ->     left outer join t2 as ref_2
    ->     on (ref_1.c1 = ref_2.c2 ))
    -> where FIELD('I#', OCT(ref_2.c2)) = 0;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                     |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (ref_2.c2 = ref_1.c1)  (cost=0.8 rows=1)
    -> Table scan on ref_2  (cost=0.35 rows=2)
    -> Hash
        -> Filter: (field('I#',conv(ref_1.c1,10,8)) = 0)  (cost=0.35 rows=1)
            -> Table scan on ref_1  (cost=0.35 rows=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

In the function `Item_func_field`, null_on_null should be set to false to prevent  OUTER JOIN from being converted to a INNER JOIN.