Bug #21231 A wrong result for a simple query with a subquery on information schema
Submitted: 22 Jul 2006 17:26 Modified: 2 Aug 2006 18:52
Reporter: Igor Babaev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.0 OS:Any (all)
Assigned to: Igor Babaev CPU Architecture:Any

[22 Jul 2006 17:26] Igor Babaev
Description:
The following simple query on information schema returns an empty set though
apparently it must return at least one row as the table tables from information_schema is not empty.

select table_name from information_schema.tables
  where table_name=(select max(table_name) 
                      from information_schema.tables)

How to repeat:
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.23-debug |
+--------------+
1 row in set (0.00 sec)

mysql> select table_name from information_schema.tables
    ->   where table_name=(select max(table_name)
    ->                       from information_schema.tables);
Empty set (0.00 sec)

Suggested fix:
Fix is ready and will be applied after a formal verification.
[23 Jul 2006 1:16] MySQL Verification Team
Thank you for the bug report.
[23 Jul 2006 1:19] MySQL Verification Team
Sorry I forgot to mention 5.1 is already fixed:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select table_name from information_schema.tables
    -> where table_name=(select max(table_name) 
    -> from information_schema.tables);
+------------+
| table_name |
+------------+
| VIEWS      | 
+------------+
1 row in set (0.07 sec)

mysql>
[25 Jul 2006 2:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9524
[25 Jul 2006 19:10] Sergey Petrunya
Approving the fix as a backport of part of 5.1's changes
[2 Aug 2006 18:47] Evgeny Potemkin
The function get_all_tables filling all information schema
tables reset lex->sql_command to SQLCOM_SHOW_FIELDS. After
this the function could evaluate partial conditions related to
some columns. If these conditions contained a subquery over
information schema it led to a wrong evaluation and a wrong 
result set.

Fixed in 5.0.25, 5.1.12
[2 Aug 2006 18:52] Paul DuBois
Noted in 5.0.25, 5.1.12 changelogs.