MySQL Bugs: #13006: the creation order of views cannot be controlled

Bug #13006	the creation order of views cannot be controlled
Submitted:	6 Sep 2005 9:50	Modified:	27 Oct 2009 7:27
Reporter:	Gunter Rombauts	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S4 (Feature request)
Version:	1.1.16	OS:	Any (windows)
Assigned to:		CPU Architecture:	Any

Description:
The creation of views cannot be controlled.

There are cases where a view depends on another view. In this case the order of creation of these views is important. In the current version you cannot influence this.

How to repeat:
CREATE OR REPLACE VIEW `cir`.`itemsbyprofiletypehelper` AS
SELECT profiletype.ID, Count(item.CODE) AS COUNTOFCODE
FROM `cir`.`item` INNER JOIN (`cir`.`product` INNER JOIN
(`cir`.`producttype` INNER JOIN `cir`.`profiletype` ON producttype.profiletypeID = profiletype.ID)
ON product.producttypecode = producttype.code) ON item.productCODE = product.CODE
WHERE (((item.customerspecific)=0) AND
      ((item.COLOR)=product.maincolor) AND ((item.dataformat)='FP1')) OR
      (((item.COLOR)=product.maincolor) AND
      ((item.dataformat)<>'FP1') AND ((item.privatelabel)=0))
GROUP BY profiletype.ID

CREATE OR REPLACE VIEW `cir`.`itemsbyprofiletype` AS
SELECT profiletype.ID,
	itemsbyprofiletypehelper.COUNTOFCODE AS itemCOUNT
FROM (`cir`.`itemsbyprofiletypehelper` RIGHT JOIN `cir`.`profiletype` ON itemsbyprofiletypehelper.ID = profiletype.ID)
ORDER BY profiletype.ID;

If you create the view's inthis order it will work, creating itemsbyprofiletype first will result in an error "table noesn't exists"

Verified as described.

I took Oracle here:

CREATE TABLE t(i integer, j integer, n integer, primary key(i));
CREATE TABLE t2(i integer, j integer, n integer, primary key(i));
CREATE TABLE t3(i integer, j integer, n integer, primary key(i));
INSERT INTO t VALUES(1,2,3);
INSERT INTO t VALUES(2,2,3);
INSERT INTO t VALUES(3,3,4);
INSERT INTO t2 VALUES(1,2,3);
INSERT INTO t2 VALUES(2,3,4);
INSERT INTO t2 VALUES(3,3,4);
INSERT INTO t3 VALUES(1,2,3);
INSERT INTO t3 VALUES(2,3,4);
INSERT INTO t3 VALUES(3,3,4);

CREATE VIEW v AS
SELECT t.j, t2.n FROM t, t2 WHERE t.i=t2.i;

CREATE VIEW a AS
SELECT t3.i, v.n FROM t3, v WHERE t3.j=v.j;

After migration views couldn't be migrated because view 'a' is depending of view 'v' and MT wants to create first view 'a'. 

It looks like view will be created in alphabetical order instead of dependencies.

This needs to be checked with Workbench

Thank you for your bug report. This issue has been already fixed in Workbench 5.2.

Feel free to test Workbench 5.2.

You can download it here:

http://dev.mysql.com/downloads/workbench/5.1.html#Workbench_5.2_Alpha_-_Binaries_and_Sourc...

More informations about Workbench you will find here:

http://dev.mysql.com/workbench/