MySQL Bugs: #112238: Inconsistent results caused by FIELD()

Bug #112238	Inconsistent results caused by FIELD()
Submitted:	1 Sep 2023 16:08	Modified:	4 Sep 2023 8:37
Reporter:	Zuming Jiang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5, 5.6, 5.7.43, 8.0.34	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Dear MySQL developers,

I used my new fuzzer to fuzz MySQL and found a logic bug that make MySQL server output inconsistent results.

How to repeat:

*** Test Case 1 ***

select FIELD((case when true then ('Q' || 'k') else '?;' end), null, 'c', null) as c0;

I simplified "case when true then ('Q' || 'k') else '?;' end" to "('Q' || 'k')", and got Test Case 2:

*** Test Case 2 ***

select FIELD(('Q' || 'k'), null, 'c', null) as c0;

*** Expected results ***

Test Case 1 and Test Case 2 return the same results.

*** Actual results ***

Test Case 1 and Test Case 2 returned inconsistent results.

Test Case 1 returned:

+----+
| c0 |
+----+
|  0 |
+----+

Test Case 2 returned:

+----+
| c0 |
+----+
|  2 |
+----+

Hello Zuming Jiang,

Thank you for the report and test case.

regards,
Umesh

For test case 1, the result type of `case when true then ('Q' || 'k') else '?;' end` is `STRING_RESULT` and all other parameters' result type is `STRING_RESULT`, so the compare type of function `Item_func_field` is `STRING_RESULT`.

For test case 2, the result type of `('Q' || 'k')` is `INT_RESULT` and all other parameters' result type is `STRING_RESULT`, so the compare type of function `Item_func_field` is `REAL_RESULT`.