Bug #103963 subqueries with ALL with NULL value
Submitted: 9 Jun 2021 17:01 Modified: 10 Jun 2021 10:44
Reporter: Trang Huyen Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any
Tags: ALL, null

[9 Jun 2021 17:01] Trang Huyen
Description:
Execute below sql query:

Select 
*
from
  table1 as A
where A.col1 >= ALL
(select B.col1 from table1 as B)

=> this sql query return no row

Data in col1 : [10, NULL]

It returns now row after compare [10, NULL] and ifself [10, NULL] , the comparison operator is >= , so it should return row 10.

How to repeat:
Create table1 that has column col1 , and table1 contains 2 rows with value in the col1 is 10 and NULL.

Execute following query: 
Select 
*
from
  table1 as A
where A.col1 >= ALL
(select B.col1 from table1 as B)

=> check the results
[9 Jun 2021 21:21] MySQL Verification Team
Thank you for the bug report. Please check with current release 8.0.25, if still the issue is observed provide a complete test case (create table, insert data commands) not just their description, print here your results too. Thanks.
[10 Jun 2021 5:03] Trang Huyen
no row returned

Attachment: error.PNG (image/png, text), 95.97 KiB.

[10 Jun 2021 5:03] Trang Huyen
no row returned

Attachment: error.PNG (image/png, text), 95.97 KiB.

[10 Jun 2021 5:06] Trang Huyen
I check with current release 8.0.25 and still see the issue.

My steps to reproduce:

create table test_all (i integer);
insert into test_all values (1);
insert into test_all values (2);
insert into test_all values (NULL);

then execute a sql query with comparison operator with subqueries with ALL:

select * from test_all as table1 where table1.i >= ALL (select test_all.i from test_all );

=> It returns no row

I expected it should return a row which has i value 2.

I upload current result as an image above this comment. error.PNG
[10 Jun 2021 9:56] MySQL Verification Team
Thank you for the feedback. I checked with others RDBMS like Oracle 19c and as  MySQL not returns row as you expected:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE J;
Query OK, 1 row affected (0.03 sec)

mysql> USE J
Database changed
mysql> create table test_all (i integer);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test_all values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_all values (2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_all values (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_all as table1 where table1.i >= ALL (select test_all.i from test_all );
Empty set (0.01 sec)

mysql>
=======================================================================
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create table test_all (i integer);

Table created.

SQL> insert into test_all values (1);

1 row created.

SQL> insert into test_all values (2);

1 row created.

SQL> insert into test_all values (NULL);

1 row created.

SQL> select * from test_all table1 where table1.i >= ALL (select test_all.i from test_all );

no rows selected

SQL>
[10 Jun 2021 10:44] Trang Huyen
I see. 
Thank for your confirm.
I will update this info.