MySQL Bugs: #44101: Exporting Views generates wrong code.

Bug #44101	Exporting Views generates wrong code.
Submitted:	6 Apr 2009 2:35	Modified:	3 Aug 2009 12:06
Reporter:	Yen-Wei Liu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.1.9 OSS beta 5142	OS:	Windows (SP3 Traditional Chinese version)
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	CHECKED

Description:
This bug is similar to this one but different:

http://bugs.mysql.com/bug.php?id=43061

When generating SQL code , there would be extra "CREATE TABLE" and "DROP TABLE" statements for each of my views, thus leading to SQL error.

How to repeat:
Create Views. Export them. You would see the redundant statements. For example, this is one of my Views:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- -----------------------------------------------------
-- Placeholder table for view `2dadsmsgdev`.`AddressBookNotifcationsView`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `2dadsmsgdev`.`AddressBookNotifcationsView` (`id` INT);

.........................................(omitted).........................................

-- View `2dadsmsgdev`.`AddressBookNotifcationsView`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `2dadsmsgdev`.`AddressBookNotifcationsView`;
CREATE  OR REPLACE VIEW `MessageNotifcationsView` AS
    SELECT 
					t1.NotificationID,
......................................... (omitted).........................................

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

First View was created as a Table, then "DROP TABLE " was applied (should be DROP VIEW).

Sorry for my previous comment , I didn't look at it carefully. In fact there are two issues:

1. 'MessageNotifcationsView' was a copy of  'AddressBookNotifcationsView' . But the export of  'AddressBookNotifcationsView' became 'MessageNotifcationsView'!! And there were two identical 'MessageNotifcationsView' definitions!! ( I barely remember this bug has been reported, hasn't it?)

2. All views were genereated this way (4 views in my case) :

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- -----------------------------------------------------
-- Placeholder table for view `2dadsmsgdev`.`AddressBookNotifcationsView`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `2dadsmsgdev`.`AddressBookNotifcationsView` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `2dadsmsgdev`.`AddressBookRequestsView`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `2dadsmsgdev`.`AddressBookRequestsView` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `2dadsmsgdev`.`MessagesView`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `2dadsmsgdev`.`MessagesView` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `2dadsmsgdev`.`MessageNotifcationsView`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `2dadsmsgdev`.`MessageNotifcationsView` (`id` INT);

-- -----------------------------------------------------
-- View `2dadsmsgdev`.`AddressBookNotifcationsView`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `2dadsmsgdev`.`AddressBookNotifcationsView`;
CREATE  OR REPLACE VIEW `MessageNotifcationsView` AS
    SELECT 
					t1.NotificationID,
..................................(omitted)........................................

-- -----------------------------------------------------
-- View `2dadsmsgdev`.`AddressBookRequestsView`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `2dadsmsgdev`.`AddressBookRequestsView`;
CREATE  OR REPLACE VIEW `AddressBookRequestsView` AS
SELECT 
					t1.NotificationID,
..................................(omitted)........................................

-- -----------------------------------------------------
-- View `2dadsmsgdev`.`MessagesView`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `2dadsmsgdev`.`MessagesView`;
CREATE  OR REPLACE VIEW `MessagesView` AS
    SELECT
			t1.MessageID,
..................................(omitted)........................................

-- -----------------------------------------------------
-- View `2dadsmsgdev`.`MessageNotifcationsView`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `2dadsmsgdev`.`MessageNotifcationsView`;
CREATE  OR REPLACE VIEW `MessageNotifcationsView` AS
    SELECT 
					t1.NotificationID,
..................................(omitted)........................................
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Should you need this SQL file for more information, let me know.

Thank you for the problem report. Please, send .mwb file that demonstrates the problem.

The minimal file to reproduce the bug

Attachment: test.mwb (application/octet-stream, text), 14.65 KiB.

the generated sql on my side

Attachment: test.sql (text/x-sql), 13.52 KiB.

As for the gendrated SQL file, from line 158 to 176 , it's the redundant "CREATE TABLE" statements for the Views.

From line 178 to 210, it's the 'AddressBookNotifcationsView' definition but replaced with 'MessageNotifcationsView', which starts on line 303.

Also, there are also improper  'DROP TABLE IF EXISTS' statements  before each View.

Thank you for the bug report.

This is not a bug but expected behavior. Redundat tables inserted in order to allow references between views even if view that being accessed not yet created.