Bug #28902 Strange: CREATE TEMPORARY TABLE <not existing database>.t1 is successful
Submitted: 5 Jun 2007 18:46 Modified: 2 Apr 2008 9:44
Reporter: Matthias Leich Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[5 Jun 2007 18:46] Matthias Leich
Description:
The behaviour around the creation of a temporary table
is not covered by the manual which says in
http://dev.mysql.com/doc/refman/5.1/en/create-table.html :
   The table name can be specified as db_name.tbl_name to
   create the table in a specific database. This works
   regardless of whether there is a default database,
   assuming that the database exists.

Example:
--disable_warnings
DROP DATABASE IF EXISTS mysqltest1;
--enable_warnings

--error ER_BAD_DB_ERROR
CREATE TABLE mysqltest1.t1 (f1 INTEGER);
--> ERROR 42000: Unknown database 'mysqltest1'
    # Ok, was to be expected.

CREATE TEMPORARY TABLE mysqltest1.t1 (f1 INTEGER);
--> # Why is this successful ?
    # The manual says ....
    # "assuming that the database exists".

SHOW CREATE TABLE mysqltest1.t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `f1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

My environment:
- mysql-5.1-rpl last ChangeSet@1.2543, 2007-06-05
- Linux openSUSE 10.2 (X86-64), Core2Duo (x86 64 Bit)

I guess that we have this behaviour also in older
MySQL releases and on all OS.

How to repeat:
See above

Suggested fix:
Please follow the SQL standard if there is a strict
description how the behaviour should be.

If a vendor specific behaviour is allowed please
change the manual so that it describes the current behaviour.
[6 Sep 2007 16:50] Konstantin Osipov
Fix in 5.2.
[15 Oct 2007 12:40] Konstantin Osipov
Not a critical issue. Requires an incompatible change.
Of the same nature as Bug#21431. Should be fixed together, delaying for now.