MySQL Bugs: #115467: Logically, the return of >=ANY should contain =ANY

Bug #115467	Logically, the return of >=ANY should contain =ANY
Submitted:	30 Jun 2024 16:29	Modified:	4 Jul 2024 10:43
Reporter:	ru tu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.4.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
 SELECT f1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)=ANY (SELECT c2 FROM t)); -- 1

 
 SELECT f1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT c2 FROM t)); -- empty set

Logically, a sentence containing >=ANY should return a greater result than a sentence containing =ANY。

SELECT c1 AS f1 FROM t;
result:

+---------+
| f1      |
+---------+
|  0.0001 |
|       1 |
| 94.1106 |
+---------+
So f1 should include 0.0001,1,94.1106.

SELECT c2 FROM t;
return -0,3,-1.

Now ((-f1)>=ANY (SELECT c2 FROM t)) is equivalent to (-f1)>= (-1). The eligible F1s are  0.0001,1.

So the result of the second sql should be 0.0001,1, not the empty set.

How to repeat:
drop table if exists t;
CREATE TABLE t (c1 FLOAT,c2 VARCHAR(20),key(c1));
INSERT INTO t VALUES (94.1106,'-0'),(1,'3	'),(0.0001,'-1');

 SELECT f1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)=ANY (SELECT c2 FROM t)); 

 
 SELECT f1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT c2 FROM t));

Hi Mr. tu,

Thank you so much for your bug report.

However, this is not a bug.

SQL Standard very strictly prohibits using various data types in any expression, including the comparison expressions. SQL Standard prescribes that a hard error should be returned as the sole result of such very much non-standard behaviour.

MySQL , and vast majority of the other RDBMS, introduces common denominators for the data of the different types. Hence, for FLOAT and VARCHAR , common denominator is FLOAT.

That is why the result that you get is very logical. 

Not a bug.

Given your second point, if the second SQL query deviates from the strict standards of SQL and should result in a hard error, I anticipate that executing this query would return an error message, not an empty set. This discrepancy could indicate a bug.

Concerning the third point you've mentioned, when these two data types are compared using the common denominator, which is FLOAT, should the anticipated return value align with the outcome of the subsequent SQL statement?

SELECT f1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT cast(c2 as double) FROM t));

+--------+
| f1     |
+--------+
| 0.0001 |
|      1 |
+--------+
2 rows in set (0.00 sec)

At the same time, the same statement executed in OceanBase yields the following result. There is a discrepancy between its output and that of MySQL. I believe there might be a bug in this case.

CREATE TABLE t (c1 FLOAT,c2 VARCHAR(20),key(c1));
INSERT INTO t VALUES (94.1106,'-0'),(1,'3	'),(0.0001,'-1');

SELECT f1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)=ANY (SELECT c2 FROM t)); 
+------+
| f1   |
+------+
|    1 |
+------+
1 row in set (0.022 sec)

SELECT f1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT c2 FROM t));
+--------+
| f1     |
+--------+
| 0.0001 |
|      1 |
+--------+
2 rows in set (0.004 sec)

Hi Mr. Tu,

Thank you so much for your comments.

The answer to both of your questions is no.

Regarding third party products, each product has it's own set of common denominators. These are all variants of not returning the hard error. They all have their strong and weak points.

We shall stick to our set of common denominators for ever.

We do not compare our product with competition, nor do we reply to questions related to third-party products.

Not a bug.