Bug #69367 EXPLAIN shows "Scanned 1 database" while the query scans many databases
Submitted: 31 May 2013 20:32 Modified: 31 May 2013 22:55
Reporter: Sergey Petrunya Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any

[31 May 2013 20:32] Sergey Petrunya
Description:
For some queries over I_S tables, EXPLAIN will show "Scanned 1 database" while the query actually scans many databases.

Look at the example below. The query looks for table t1 in all databases. Query result returns 8 tables in different databases.

However, EXPLAIN shows "Scanned 1 database".

How to repeat:
MySQL [test]> create database test1;
Query OK, 1 row affected (0.00 sec)

MySQL [test]> create database test2;
Query OK, 1 row affected (0.00 sec)

MySQL [test]> create table test1.t1 (a int);
Query OK, 0 rows affected (0.26 sec)

MySQL [test]> create table test2.t1 (a int);
Query OK, 0 rows affected (0.27 sec)

MySQL [test]> explain select table_schema, table_name from information_schema.tables where  table_name='t1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tables
         type: ALL
possible_keys: NULL
          key: TABLE_NAME
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using where; Skip_open_table; Scanned 1 database
1 row in set (4.96 sec)

MySQL [test]> select table_schema, table_name from information_schema.tables where  table_name='t1';
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
...
| test         | t1         |
| test1        | t1         |
| test2        | t1         |
+--------------+------------+
8 rows in set (0.03 sec)
[31 May 2013 20:42] Sergey Petrunya
fixed synopsis
[31 May 2013 22:55] MySQL Verification Team
Thank you for the bug report.
[7 Jun 2013 9:46] Georgi Kodinov
Posted by developer:
 
The problem is in the way EXPLAIN prints the extra column. In explain_extra() function there's the following code : 
      if (table_list->has_db_lookup_value &&
          table_list->has_table_lookup_value)
      {
        if (push_extra(ET_SCANNED_DATABASES, "0"))
          return true;
      }
      else if (table_list->has_db_lookup_value ||
               table_list->has_table_lookup_value)
      {
        if (push_extra(ET_SCANNED_DATABASES, "1"))
          return true;
      }
      else
      {
        if (push_extra(ET_SCANNED_DATABASES, "all"))
          return true;
      }
The first branch is correct : if there's both table and database lookup it won't scan any databases.
But the second branch is bad : if there's a table lookup value it may scan more than 1 database as you're correctly pointing out.