A bug_mysql of Mysql about exists

I met a strange question about exists today.

The first statement is as follows:

Select
count (1) from the
APPLY t
WHERE
EXISTS (
select r.apply_id
from record R
WHERE
t.apply_id = r.apply_id
);

The resulting results are: 89584

The second statement is as follows:

Select
count (1)
from
APPLY t
WHERE
EXISTS (
select
max (r.finish_time)
From the record
R
WHERE
t.apply_id = r.apply_id
);

The resulting results are: 432382

It's quite strange, for the exist clause, to judge whether the value of the subquery exists, that is, the column name, and the maximum value for the column name is no different.

including MySQL official documentation also mentioned

Traditionally, a EXISTS subquery starts with SELECT *, but it could begin with select 5 or select Column1 or anything at All. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

The effect is that MySQL automatically ignores the list of select.

Later tested in his own environment, is indeed a MySQL bug

Test environment: MySQL 5.6.31,5.7.14

mysql> CREATE TABLE t3 (ID int,t datetime);
Query OK, 0 rows affected (0.44 sec)
mysql> insert INTO T3 values (1, ' 20160812 ');
Query OK, 1 row affected (0.16 sec)
mysql> Select 1 from dual where exists (select IDs from T3 where id=2);
Empty Set (0.15 sec)
mysql> Select 1 from dual where exists (select MAX (id) from T3 where id=2);
+---+
| 1 |
+---+
| 1 |

Obviously, the column with ID equal to 2 does not exist, but the second statement is treated as true.

Also confirms the execution plan for the next two statements and the rewritten SQL

First statement

Mysql> EXPLAIN EXTENDED Select 1 from dual where exists (select IDs from T3 where id=2); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----- -------------+
| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered |
Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----- -------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | | 2 | subquery | T3 | NULL | All | NULL | NULL | NULL | NULL | 1 | 100.00 |
Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----
-------------+ 2 rows in set, 2 warnings (0.00 sec) mysql> Show warnings; +---------+------+-------------------------------------------------------------------+
| Level | Code |
message | +---------+------+-------------------------------------------------------------------+
| Warning | 1681 | ' EXTENDED ' is deprecated and would be removed in a future release. |
| Note | 1003 |
/* Select#1/SELECT 1 as ' 1 ' from DUAL where 0 |  +---------+------+-------------------------------------------------------------------+

Second statement

Mysql> EXPLAIN EXTENDED Select 1 from dual where exists (select MAX (id) from T3 where id=2); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----- -----------+
| ID | Select_type | Table | partitions | Type | Possible_keys | Key | Key_len | Ref | Rows | Filtered |
Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----- -----------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No Tables Used | | 2 | subquery | T3 | NULL | All | NULL | NULL | NULL | NULL | 1 | 100.00 |
Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----
-----------+ 2 rows in set, 2 warnings (0.00 sec) mysql> Show warnings; +---------+------+-------------------------------------------------------------------+
| Level | Code |
message | +---------+------+-------------------------------------------------------------------+
| Warning | 1681 | ' EXTENDED ' is deprecated and would be removed in a future release. |
| Note | 1003 |
/* Select#1/SELECT 1 as ' 1 ' from DUAL where 1 |  +---------+------+-------------------------------------------------------------------+ 2 rows in Set (0.00 sec)

The execution plan and the rewritten SQL are indeed different, it seems, is indeed a MySQL bug.

So, a bug was brought to the authorities.

http://bugs.mysql.com/bug.php?id=82562

Summarize

When writing a exists statement, it is recommended that you use the * in a subquery without any functional manipulation of the column to avoid official bugs.

In fact, there's no problem with the Abs,floor function.

Mysql> Select 1 from dual where exists (select ABS (IDS) from T3 where id=2);
Empty Set (0.07 sec)
mysql> Select 1 from dual where exists (select floor (IDS) from T3 where id=2);
Empty Set (0.00 sec)

The above is a small set to introduce the MySQL about exists a bug, I hope to help everyone, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!