MySQL Bugs: #12905: show fields from view behaving erratically with current database

Bug #12905	show fields from view behaving erratically with current database
Submitted:	31 Aug 2005 11:15	Modified:	13 Sep 2005 23:03
Reporter:	Ritesh Nadhani	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.11-beta	OS:	Any (ALL)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
Hello,

The query "show full fields from dbtest.viewname" does not work if no database is selected for the current database. Though it should be as we are specifying db name in the query.

However, if we select any another db (need not be dbtest), the same query works.

How to repeat:
Create a database with a view. Connect to the mysql server without specifying a database.

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> show fields from test3.v12;
ERROR 1046 (3D000): No database selected
mysql> use manu;
Database changed
mysql> show fields from test3.v12;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| numbe | int(11)     | NO   |     | 0       |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Suggested fix:
I am not a programmer :)

Thank you for a bug report. Everything "works" just as you described.

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

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

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.11-beta-nt |
+----------------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> show fields from test.t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a     | varchar(9) | YES  |     | NULL    |       |
| b     | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> show fields from test.v1;
ERROR 1046 (3D000): No database selected
mysql> use test;
Database changed
mysql> show fields from test.v1;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| a         | varchar(9) | YES  |     | NULL    |       |
| length(a) | bigint(10) | YES  |     | NULL    |       |
| count(*)  | bigint(21) | NO   |     | 0       |       |
+-----------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

Even if you do:

use test2

show fields from test.v1 works. Basically, you will require to have a default database. Not necessarily the same database.

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/internals/29154

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/internals/29356

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/internals/29419

Fixed in 5.0.13

Documented in 5.0.13 changelog:

      <listitem>
        <para>
          <literal>SHOW FIELDS FROM <replaceable>schemaname</replaceable>.<replaceable>viewname</replaceable></literal>
          caused error 1046 when no default schema was set. (Bug #12905) 
        </para>
      </listitem>