Bug #106007 different result between in and join query on mysql system table
Submitted: 30 Dec 2021 7:53 Modified: 30 Jan 2022 9:03
Reporter: Jaricho wu Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.7.23 OS:CentOS
Assigned to: CPU Architecture:x86

[30 Dec 2021 7:53] Jaricho wu
Description:
mysql> SELECT d.db
    -> FROM mysql.db d
    -> WHERE db IN
    ->     (SELECT SCHEMA_NAME
    ->      FROM information_schema.SCHEMATA
    ->      WHERE SCHEMA_NAME NOT IN ('information_schema',
    ->                                'configdb',
    ->                                'mysql',
    ->                                'performance_schema',
    ->                                'sys')
    ->     );
Empty set (0.00 sec)
mysql> SELECT d.db
    -> FROM mysql.db d
    -> INNER JOIN information_schema.SCHEMATA s ON d.db = s.SCHEMA_NAME
    -> WHERE SCHEMA_NAME NOT IN ('information_schema',
    ->                           'configdb',
    ->                           'mysql',
    ->                           'performance_schema',
    ->                           'sys');
+---------+
| db      |
+---------+
| testadb |
| testbdb |
| testcdb |
+---------+
3 rows in set (0.00 sec)

How to repeat:
mysql> SELECT d.db
    -> FROM mysql.db d
    -> WHERE db IN
    ->     (SELECT SCHEMA_NAME
    ->      FROM information_schema.SCHEMATA
    ->      WHERE SCHEMA_NAME NOT IN ('information_schema',
    ->                                'configdb',
    ->                                'mysql',
    ->                                'performance_schema',
    ->                                'sys')
    ->     );
Empty set (0.00 sec)
mysql> SELECT d.db
    -> FROM mysql.db d
    -> INNER JOIN information_schema.SCHEMATA s ON d.db = s.SCHEMA_NAME
    -> WHERE SCHEMA_NAME NOT IN ('information_schema',
    ->                           'configdb',
    ->                           'mysql',
    ->                           'performance_schema',
    ->                           'sys');
+---------+
| db      |
+---------+
| testadb |
| testbdb |
| testcdb |
+---------+
3 rows in set (0.00 sec)
[30 Dec 2021 9:03] MySQL Verification Team
Please provide the complete SQL script test case not just the queries. Thanks in advance.
[31 Jan 2022 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".