Bug #13189 inconsistent behaviour SHOW CREATE TABLE on base TABLE and VIEW
Submitted: 14 Sep 2005 17:25 Modified: 19 Sep 2005 0:01
Reporter: Matthias Leich Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0 OS:
Assigned to: Evgeny Potemkin CPU Architecture:Any

[14 Sep 2005 17:25] Matthias Leich
Description:
SHOW CREATE TABLE <base table>  and 
           SHOW CREATE TABLE|VIEW <view>
give a different output in the field of SCHEMA names. 
This asymmetry looks a bit ugly and seems to have 
no real benefit, but it is maybe intended and no real bug.
Example:
  CREATE SCHEMA dbtest1;
  # Just to be sure, that dbtest1 is not our current default. 
  USE test;
  CREATE TABLE dbtest1.t1 (f1 BIGINT);
  CREATE VIEW  dbtest1.v1 AS SELECT * FROM dbtest1.t1;
  SHOW CREATE TABLE dbtest1.t1;
  Table   Create Table
  t1      CREATE TABLE `t1` (
                                  #### There is no SCHEMA name.   
    `f1` bigint(20) default NULL
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  SHOW CREATE TABLE dbtest1.v1;
  View    Create View
  v1      CREATE ALGORITHM=UNDEFINED VIEW 
          `dbtest1`.`v1` AS select `dbtest1`.`t1`.`f1` AS `f1` 
            ##### There is the SCHEMA name !
            from `dbtest1`.`t1`
  SHOW CREATE VIEW  dbtest1.v1;
  View    Create View
  v1      CREATE ALGORITHM=UNDEFINED VIEW 
           `dbtest1`.`v1` AS select `dbtest1`.`t1`.`f1` AS `f1`
            ##### There is the SCHEMA name !
            from `dbtest1`.`t1`

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1956, 2005-09-14
I assume we have this inconsistent behaviour since the views
are available.

How to repeat:
Please execute the statements above or use my attached
testscript ml013.test
  copy it to mysql-test/t
  echo "Dummy" > r/ml013.result   # Produce a dummy file with 
                                                   # expected results
  ./mysql-test-run ml013
  inspect r/ml013.reject    # The protocol of the execution.
[14 Sep 2005 17:27] Matthias Leich
testcase

Attachment: ml013.test (application/test, text), 384 bytes.

[19 Sep 2005 0:01] Evgeny Potemkin
Not present/fixed in 5.0.14.
[19 Sep 2005 18:49] Matthias Leich
There is no change of the behaviour in the newest MySQL 5.0 release.
The output of SHOW CREATE TABLE <base table> 
  "CREATE TABLE `t1` (`f1` bigint(20) default NULL) 
                        ###
   ENGINE=MyISAM DEFAULT CHARSET=latin1" does not contain 
the SCHEMA and SHOW CREATE TABLE <view table> 
  "CREATE ALGORITHM=UNDEFINED DEFINER ..... 
    VIEW `dbtest1`.`v1` AS select `dbtest1`.`t1`.`f1` AS `f1` from `dbtest1`.`t1`"
              ########
contains the SCHEMA.
It is maybe only a cosmetic or "academic" issue and somebody might call it 
no bug or the system works as intended.
But we should be aware that any asymmetry looks suspicious and
often points to bugs, not well designed things etc.
One option would be to make the SHOW TABLE <base table> behaviour
similar to the behaviour of SHOW TABLE <view table> (see Bug#10713).
Example for the modified behaviour:
USE test;
SHOW CREATE TABLE test2.t1;
... CREATE TABLE test2.t1 ....
                         #### 
USE test2;
SHOW CREATE TABLE test2.t1;
... CREATE TABLE t1 ....
                         ##
I know that this means a different behaviour compared to history.