Bug #118521 cast(double as char) result is incorrect

• Submitted: 25 Jun 2:54
• Modified: 17 Jul 12:12
• Reporter: haizhen xue
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[25 Jun 2:54] haizhen xue

Description:
create table t (`double_col` double);

insert into t values(-2.2250738585072014e-308);

SELECT double_col FROM t where (double_col) NOT IN  (CAST(-2.2250738585072014e-308 AS CHAR));

8.0.39:
mysql> SELECT double_col FROM t where double_col NOT IN (CAST(-2.2250738585072014e-308 AS CHAR));
+--------------------------+
| double_col               |
+--------------------------+
| -2.2250738585072014e-308 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.39    |
+-----------+
1 row in set (0.00 sec)

mysql> select CAST(-2.2250738585072014e-308 AS CHAR);
+----------------------------------------+
| CAST(-2.2250738585072014e-308 AS CHAR) |
+----------------------------------------+
| -2.2250738585072e-308                  |
+----------------------------------------+
1 row in set (0.00 sec)

8.0.22:
mysql> SELECT double_col FROM t where (double_col) NOT IN  (CAST(-2.2250738585072014e-308 AS CHAR));
Empty set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 8.0.22-debug |
+--------------+
1 row in set (0.00 sec)

mysql> select  CAST(-2.2250738585072014e-308 AS CHAR);
+----------------------------------------+
| CAST(-2.2250738585072014e-308 AS CHAR) |
+----------------------------------------+
| -2.2250738585072014e-308               |
+----------------------------------------+
1 row in set (0.00 sec)

How to repeat:
create table t (`double_col` double);

insert into t values(-2.2250738585072014e-308);

SELECT double_col FROM t where (double_col) NOT IN  (CAST(-2.2250738585072014e-308 AS CHAR));

[25 Jun 3:02] haizhen xue

mysql> SELECT double_col FROM t where (double_col) NOT IN (NULL);
Empty set (0.00 sec)

mysql> SELECT double_col FROM t where (double_col,double_col) NOT IN ((NULL,NULL));
Empty set (0.00 sec)

mysql> SELECT double_col FROM t where (double_col)  IN (NULL);
Empty set (0.00 sec)

mysql> SELECT double_col FROM t where (double_col,double_col) IN ((NULL,NULL));
Empty set (0.00 sec)

is result ok?

[17 Jul 12:12] MySQL Verification Team

Hello Haizhen Xue,

Thank you for the bug report.
IMHO this is not a bug.

Regarding NULL:
Please go through the document available at https://dev.mysql.com/doc/refman/8.4/en/working-with-null.html

The NULL value can be surprising until you get used to it. Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.
To test for NULL, use the IS NULL and IS NOT NULL operators, as shown in the doc.

Regarding CAST(DOUBLE AS CHAR):
You are trying to insert different value in double data type i.e. -2.2250738585072014e-308. Hence the results are varying.

For more info, please see here https://dev.mysql.com/doc/refman/8.4/en/floating-point-types.html

You can also try CAST('-2.2250738585072014e-308' AS CHAR)

Regards,
Ashwini Patil