MySQL Bugs: #26133: `hyphened-named` views wrongly pop in information

Bug #26133	`hyphened-named` views wrongly pop in information_schema.tables
Submitted:	7 Feb 2007 5:39	Modified:	28 Feb 2007 11:26
Reporter:	Jared S (Silver Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	5.1 BK, 5.2-falcon	OS:	Linux (Linux, WinXP)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	hyphen, VIEW

Description:
Hi,

Query browser handles `hyphened-named` views as tables, it displays the wrong icon and they can't be edited ounce created.

How to repeat:
1. Select a DB
2. Create a new view called 'my-view'

#RESULT the new view will be listed as a table

Suggested fix:
Correct the handling of hyphened named views with in Query Browser

Navicat also get the view reported as a table so must be a problem with in the engine.

Changed from Query browser bug to InnoDB bug.

Updated Version to corresponding verison of InnoDB

Thank you for a problem report. Sorry, but was not able to repeat the behaviour described with a simple view, neither on 5.0.36-BK nor on 5.1.15-BK:

openxs@suse:~/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.15-beta Source distribution

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

mysql> create view `my-view` as select 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select table_name from information_schema.views where table_schema='test
' and table_name like 'my%';
+------------+
| table_name |
+------------+
| my-view    |
+------------+
1 row in set (0.01 sec)

mysql> select table_name, table_type from information_schema.tables where table
_schema='test' and table_name like 'my%';
+------------+------------+
| table_name | table_type |
+------------+------------+
| my-view    | VIEW       |
+------------+------------+
1 row in set (0.00 sec)

So, this view is correctly presented in INFORMATION_SCHEMA tables. I aslo see it as view in MySQL Query Browser. 

If you have another view that demonstrates the behaviour described, just send CREATE VIEW statement for it. It is also not InnoDB bug, in any case.

OS=WINXP      !!!!!!!!!!!
SERVER=5.1

Take a look at pic I post so you know this is a genuine problem.

cool little pic

Attachment: view.JPG (image/pjpeg, text), 5.52 KiB.

Please re-open bug, and also confirm version of GUI-TOOLS used to repo.

I just was not able to repeat according to your description, with QB from GUI Tools 5.0r9 and MySQL 5.0.27. 

Please, send CREATE VIEW statement for that view-two from your screenshot. Send also the exact version of MySQL server used, 5.1.x.

Sorry for not being more definate about versions..

[5.1.00] Windows XP Pro SP2
[5.1.15] MySQL
[5.0.09] GUI-TOOLS

Looks like you switch to correct OS, but have changed server from 5.1 to 5.0 and that is now why you can't repeat.

CREATE VIEW `bugs`.`some-view` AS
  SELECT 1;

Thank you for the report.

Verified as described.

The bug reporter is right. This is not a GUI tools bug, but a server bug. See this output:

mysql> use test;
Database changed
mysql> show full tables;
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| ├ñ              | ERROR      |
| a_b            | BASE TABLE |
| alpha          | BASE TABLE |
| k              | BASE TABLE |
| t1             | BASE TABLE |
| t2             | BASE TABLE |
| t3             | BASE TABLE |
| t4             | BASE TABLE |
| t5             | BASE TABLE |
| t6             | BASE TABLE |
| tab            | BASE TABLE |
| test-1         | ERROR      |
+----------------+------------+
12 rows in set (0.01 sec)

mysql>

ERROR is not an expected return value for the second column. One could argue that the GUI tools should be able to skip such tables, but that does not change the original problem.

Tested with 5.1.11-beta-log

Category has changed.

Version 5.0 is unaffected.

Duplicated with Bug#25081