Bug #57329 Cannot Forward Engineering a view with JOIN ... USING syntax
Submitted: 8 Oct 2010 0:02 Modified: 5 Jan 2011 11:57
Reporter: Rick Su Email Updates:
Status: Closed Impact on me:
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:WB 5.2.28 CE, Revision 6722, 5.2.29 OS:Windows (Windows 7 32bit Home Premium)
Assigned to: Alexander Musienko CPU Architecture:Any
Tags: forward engineering, Table Join, VIEW

[8 Oct 2010 0:02] Rick Su
Workbench failed to forward engineering when the EER diagram contains a view of joined tables with syntax "JOIN ... USING"

The "Placeholder table for view" in generated SQL script has duplicate column name, therefore it returned Error 1060: Duplicate column name 'common_col' 

How to repeat:
Place 2 new tables in EER diagram

tbl_a (
  `a_id` INT,
  `common_col` INT

tbl_b (
  `b_id` INT,
  `common_col` INT

now, place a new view, using "JOIN ... USING" syntax

SELECT * FROM tbl_a JOIN tbl_b USING(common_col)

Save current EER diagram, and go "Database" -> "Forward Engineering"
follow the wizard, and until the generate script shows up.
Notice that the "Placeholder table for view" has duplicate column name, if you continue the step, the script will return Error 1060: Duplicate column
[29 Oct 2010 13:58] Valeriy Kravchuk
Indeed, one can create such a view form the command line client. So, Workbench either should NOT generate that dummy table, or should do it smarter and include common column only once.
[8 Dec 2010 20:50] Johannes Taxacher
fix confirmed in repository
[5 Jan 2011 11:57] Tony Bedford
An entry has been added to the 5.2.31 changelog:

Forward engineering a model failed with a duplicate column name error when the EER diagram contained a view consisting of tables joined using the JOIN...USING syntax.