Bug #114118 Field function return wrongs result if casting fails.
Submitted: 26 Feb 11:42 Modified: 26 Feb 12:11
Reporter: Rahul Malik Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.36 OS:Any
Assigned to: CPU Architecture:Any

[26 Feb 11:42] Rahul Malik
Description:
mysql>  select field(0,"1",2,3,0);
+--------------------+
| field(0,"1",2,3,0) |
+--------------------+
|                  4 |
+--------------------+
1 row in set (0,00 sec)

mysql>  select field(0,"x",2,3,0);
+--------------------+
| field(0,"x",2,3,0) |
+--------------------+
|                  1 |
+--------------------+
1 row in set, 1 warning (0,00 sec)

mysql>  select field(0,"1x",2,3,0);
+---------------------+
| field(0,"1x",2,3,0) |
+---------------------+
|                   4 |
+---------------------+
1 row in set, 1 warning (0,00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '1x' |
+---------+------+----------------------------------------+
1 row in set (0,00 sec)

mysql>

How to repeat:
select field with a number where casting fails. 

Suggested fix:
IF it fails to cast a string to int. It should exclude that. rather converting it to 0 which results in wrong result.
[26 Feb 12:11] MySQL Verification Team
Hi Mr. Malik,

Thank you for your bug report.

However, this is not a bug ....... 

According to our Reference Manual:

"
If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.
"

Hence, we are dealing here with floating point values. Transforming strings and integers into a floating point types, with values that you provided, will definitely lead to the errors. Hence, either try to use all integers or all strings.

Mixing the types in expressions or functions is prohibited by SQL standard. Hence, MySQL does what it can ......

Not a bug.
[26 Feb 12:16] MySQL Verification Team
Hi,

If you use that function as specified in our Reference Manual, it works just fine:

mysql>  select field(0,1,2,3,0);
+------------------+
| field(0,1,2,3,0) |
+------------------+
|                4 |
+------------------+
1 row in set (0.00 sec)

mysql>  select field("0","x","2","3","0");
+----------------------------+
| field("0","x","2","3","0") |
+----------------------------+
|                          4 |
+----------------------------+
1 row in set (0.00 sec)

mysql>  select field("0","1x","2","3","0");
+-----------------------------+
| field("0","1x","2","3","0") |
+-----------------------------+
|                           4 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

Not a bug.