Bug #96137 NOT(NOT( FIELD > ALL (SUBQUERY) )) 'S RESULT NOT EQUAL FIELD > ALL (SUBQUERY)

• Submitted: 9 Jul 2019 9:20
• Modified: 9 Jul 2019 11:06
• Reporter: Carol WU
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.7.17 MySQL Community Server
• OS: Linux
• CPU Architecture: Any

[9 Jul 2019 9:20] Carol WU

Description:
mysql> create table t (c1 int, c2 int);
Query OK, 0 rows affected (0.21 sec)

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

mysql> select * from t where c1 < ALL (select c2 from t);
+------+------+
| c1   | c2   |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.01 sec)

mysql> select * from t where NOT(NOT( c1 < ALL (select c2 from t) ));
Empty set (0.00 sec)

How to repeat:
create table t (c1 int, c2 int);
insert into t values(1,2);
select * from t where c1 < ALL (select c2 from t);
select * from t where NOT(NOT( c1 < ALL (select c2 from t) ));

[9 Jul 2019 11:06] MySQL Verification Team

Hi,

thanks for reporting, verified on 8.0.16 too

mysql [localhost:8016] {msandbox} ((none)) > use test
Database changed
mysql [localhost:8016] {msandbox} (test) > show tables;
Empty set (0.00 sec)

mysql [localhost:8016] {msandbox} (test) > create table t (c1 int, c2 int);
Query OK, 0 rows affected (0.02 sec)

mysql [localhost:8016] {msandbox} (test) > insert into t values(1,2);
Query OK, 1 row affected (0.01 sec)

mysql [localhost:8016] {msandbox} (test) > select * from t where c1 < ALL (select c2 from t);
+------+------+
| c1   | c2   |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

mysql [localhost:8016] {msandbox} (test) > select * from t where NOT(NOT( c1 < ALL (select c2 from t) ));
Empty set (0.00 sec)

mysql [localhost:8016] {msandbox} (test) > mysql [localhost:8016] {msandbox} (test) > select * from t where NOT(NOT( c1 > ALL (select c2 
+------+------+
| c1   | c2   |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

mysql [localhost:8016] {msandbox} (test) > select * from t where not(NOT(NOT( c1 < ALL (select c2 from t) )));
Empty set (0.00 sec)