MySQL Bugs: #116843: The logical error of the INET_NTOA function in the NOT IN operation.

Bug #116843	The logical error of the INET_NTOA function in the NOT IN operation.
Submitted:	2 Dec 2024 14:25	Modified:	3 Dec 2024 11:21
Reporter:	wang jack	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.4.1	OS:	Red Hat (Linux 3.10.0-1160.102.1.el7.x86_64 )
Assigned to:		CPU Architecture:	x86 (x86_64)

Description:
When executing the following SQL query, a logical error was found in the behavior of the INET_NTOA function in the NOT IN operation, resulting in query results that do not meet expectations.

This query will return 1 row.
select (NOT ((INET_NTOA(15)) NOT IN (t0.c0))) from t0;

However, when I place (NOT ((INET_NTOA(15)) NOT IN (t0.c0))) in the WHERE condition, no data can be retrieved.

How to repeat:
execute blow sqls

drop table if exists t0;
CREATE TABLE t0(c0 DECIMAL PRIMARY KEY STORAGE DISK);
INSERT INTO t0 VALUES (0);

mysql> select (NOT ((INET_NTOA(15)) NOT IN (t0.c0))) from t0;
+----------------------------------------+
| (NOT ((INET_NTOA(15)) NOT IN (t0.c0))) |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT 1 FROM t0 WHERE (NOT ((INET_NTOA(15)) NOT IN (t0.c0)));
Empty set, 1 warning (0.02 sec)

HI Mr. jack,

Thank you for your bug report.

However, this is not a bug.

You are mixing BOOLEAN data type with a DECIMAL data type.

SQL Standard strictly prohibits mixing of data types in an expression.

MySQL tries to do something, so it has to convert to the third data type. Hence, the results are totally, totally unpredictable. However, this is better then strictly following the standard, which requires returning a hard error.

Not a bug.

Thanks for your reply!

First, I executed the same SQL on both TiDB 8.4 and MariaDB 11.6, which are MySQL-compatible databases, and they both handled the query correctly.

Second, I don't think the question is "mixing BOOLEAN data type with a DECIMAL data type".
According to my perspective, even if there are issues with type conversion,, the expression should exhibit the same behavior in both the SELECT block and the WHERE block.

The expression (NOT ((INET_NTOA(15)) NOT IN (t0.c0))) can be correctly evaluated to 1 in the SELECT clause.
mysql> select (NOT ((INET_NTOA(15)) NOT IN (t0.c0))) from t0;
+----------------------------------------+
| (NOT ((INET_NTOA(15)) NOT IN (t0.c0))) |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
However, the same expression is not correctly recognized in the WHERE clause.
mysql> SELECT 1 FROM t0 WHERE (NOT ((INET_NTOA(15)) NOT IN (t0.c0)));
Empty set, 1 warning (0.02 sec)

Third, I provided another set of queries for comparison.

I think the issue should involve how the return value of the INET_NTOA function is handled, rather than mixing data types.

mysql> select INET_NTOA(15);
+---------------+
| INET_NTOA(15) |
+---------------+
| 0.0.0.15      |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT (NOT (('0.0.0.15') NOT IN (t0.c0))) from t0;
+-------------------------------------+
| (NOT (('0.0.0.15') NOT IN (t0.c0))) |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT 1 FROM t0 WHERE (NOT (('0.0.0.15') NOT IN (t0.c0)));
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.00 sec)

Hi Mr. jack,

No, this is not a bug.

First of all, each RDBMS has it's own set of type reconciliation algorithms......

Second, SELECT list and WHERE condition are not treated the same. That is why you have got warning with WHERE condition.

Not a bug.

Thank you very much for your patient response.

You are truly welcome .....