MySQL Bugs: #108939: Change ANY to ALL, the result set increases

Bug #108939	Change ANY to ALL, the result set increases
Submitted:	31 Oct 2022 14:32	Modified:	1 Nov 2022 13:26
Reporter:	ZongYin Hao	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0.30	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ALL, ANY

Description:
In theory, the result of sql2(ALL) ⊆ the result of sql1(ANY):

SELECT c1 FROM t WHERE c1 >= ANY (SELECT c2 FROM t); -- sql1
SELECT c1 FROM t WHERE c1 >= ALL (SELECT c2 FROM t); -- sql2

However, the result set increases after changing ANY to ALL, seems like a logical bug:

mysql> SELECT c1 FROM t WHERE c1 >= ANY (SELECT c2 FROM t); -- sql1
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT c1 FROM t WHERE c1 >= ALL (SELECT c2 FROM t); -- sql2;
+--------+
| c1     |
+--------+
|      1 |
|      0 |
| 0.0001 |
+--------+
3 rows in set (0.00 sec)

How to repeat:
drop table if exists t;
create table t (c1 double, c2 char(20));
insert into t values (1, '3 '),(0, '1'),(0.0001, 'w');

SELECT c1 FROM t WHERE c1 >= ANY (SELECT c2 FROM t);
SELECT c1 FROM t WHERE c1 >= ALL (SELECT c2 FROM t);

Suggested fix:
This is the result of oceanbase-3.1.4(https://github.com/oceanbase/oceanbase):

mysql> SELECT c1 FROM t WHERE c1 >= ANY (SELECT c2 FROM t); -- sql1
+--------+
| c1     |
+--------+
|      1 |
|      0 |
| 0.0001 |
+--------+
3 rows in set, 2 warnings (0.01 sec)

mysql> SELECT c1 FROM t WHERE c1 >= ALL (SELECT c2 FROM t); -- sql2
Empty set (0.01 sec)

I hope mysql can return this result.

Hi Mr. Hao,

Thank you for your bug report.

However, this is not a bug.

You can not compare values from two different column types.

Many other products return, correctly, the error on such mistakes. MySQL is not strict typing product, yet ....

I changed a domain of second column and one value and got expected results:

c1
1
0
10.0001
------------------------------------------------
------------------------------------------------
c1
10.0001

Not a bug.

Got it, thanks for your reply!

You are truly welcome .....