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: | |
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
[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: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.