MySQL Bugs: #63807: WB creates incorrect create query when a if using view

Bug #63807	WB creates incorrect create query when a if using view
Submitted:	20 Dec 2011 12:05	Modified:	6 Jul 2012 5:05
Reporter:	Javier Domingo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.36, 5.2.37	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This example sincronized in a wrong way the view with the database, as it tries to insert a table with the same select

CREATE VIEW `txomon_lmb97`.`vi_peo_asi_eve_rea` AS
SELECT
    `people`.`name`,
    `people`.`surname`,
    `events`.`day_hour`,
    `seasons`.`year`,
    IF(`seasons`.`spell`,'Summer','Winter'),
    `event_types`.`event_type`,
    `asistences`.`arrival`,
    `reasons`.`reason`,
    `posts`.`post`
FROM
    `people`,
    `events`,
    `seasons`,
    `event_types`,
    `asistences`,
    `reasons`,
    `posts`,
    `rel_posts_people`
WHERE
    (`asistences`.`reason`=`reasons`.`id`)
    AND (`asistences`.`person`=`people`.`id`)
    AND (`asistences`.`foreseen_event`=`events`.`id`)
    AND (`events`.`event_type`=`event_types`.`id`)
    AND (`events`.`season`=`seasons`.`id`)
    AND (`event_types`.`assistants_post`=`posts`.`id`)
;

So it fails, because it makes a 

CREATE TABLE IF NOT EXISTS `people`.`name` int,
    `people`.`surname`INT,
    `events`.`day_hour` INT,
    `seasons`.`year` ..,
    IF(`seasons`.`spell`,'Summer','Winter'),
    `event_types`.`event_type`,
    `asistences`.`arrival`,
    `reasons`.`reason`,
    `posts`.`post`;

How to repeat:
1.- Model a view that uses functions
2.- Try to sync it with a database

Suggested fix:
Make WB analyze whether if the select statement is using functions, and if it, create a generic field for that.

What exact server version, 5.x.y, are you trying to synchronize with? Please, upload simple .mwb file that demonstrates the problem.

The idea was that if you use in the select, something that cant be used for creating a table, it will fail the sync. Just because WB creates and drops a table with the view's fields, and then creates the view.

Create a model with:

CREATE TABLE trying_crash
(id INT,
active BIT(1));

And then a view that makes some data manipulation between the table and the view

CREATE VIEW vi_try AS
SELECT
    trying_crash.id,
    IF(trying_crash.active,'active','unactive')
FROM trying_crash;

And when you try to sync it, it will tell you it failed because cant be created a table like this:

CREATE TABLE vi_try
(    trying_crash.id INT,
    IF(trying_crash.active,'active','unactive') INT);

WB makes a table before creating a view to avoid errors, but doesn't have into account that the syntax for the CREATE TABLE can not be correct although it is for a view.

OK, this is what can happen:

Executing SQL script in server

ERROR: Error 1059: Identifier name 'if(c1, 'active, very active', 'inactive, very very very inactive')' is too long

-- -----------------------------------------------------
-- Placeholder table for view `mydb2`.`view1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb2`.`view1` (`idtable1` INT, `if(c1, 'active, very active', 'inactive, very very very inactive')` INT)

SQL script execution finished: statements: 6 succeeded, 1 failed

So, the problem in CREATE TABLE generated may be related to column name used (when it is just long enough expression). Workbench, indeed, should detect this before sending to server and suggest to define alias for columns like this.

This is fixed as of 5.2.41, and here's the changelog entry:

If a view contains a row that is too long to be represented as a
table column, it will now be substituted with an alias column in
a placeholder table, similar to how 'mysqldump'
behaves. Before it could generate invalid SQL, and fail to
synchronize the model.