| 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: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 5.7.23 | OS: | CentOS |
| Assigned to: | CPU Architecture: | x86 | |
[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".
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)