MySQL Bugs: #43661: Disable Nested Join Optimization in view creation

Bug #43661	Disable Nested Join Optimization in view creation
Submitted:	15 Mar 2009 23:43	Modified:	17 Mar 2009 2:15
Reporter:	Jared S (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S4 (Feature request)
Version:	5.1.32	OS:	Any (Any)
Assigned to:		CPU Architecture:	Any
Tags:	create, inner, join, left, qc, show, VIEW

Description:
Hi,

Creating a view with LEFT\INNER JOIN changes to only "JOIN"

The data been returned is correct.

How to repeat:
-- CTR-ENTER & Edit

DROP VIEW IF EXISTS `viewaccounts`;  CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `viewaccounts` AS
 (SELECT
 'P' AS `Ex1`,'' AS `items`,`classic`.`type-payments`.`description` AS `description`,`classic`.`sale-payments`.`id` AS `id`,`classic`.`sale-payments`.`id` AS `jobno`,`classic`.`sale-payments`.`idbusiness` AS `idbusiness`,`classic`.`sale-payments`.`entered` AS `entered`,(`classic`.`sale-payments`.`amount` * -(1)) AS `xPrice`,`type-reason`.`description` AS `xRate`,'' AS `xNotes`,'' AS `xFrom`,'' AS `xTo`
FROM
 `classic`.`sale-payments` 
  INNER JOIN `classic`.`type-payments` ON `classic`.`type-payments`.`id` = `classic`.`sale-payments`.`idtype`
  INNER JOIN `classic`.`type-reason` ON `classic`.`type-reason`.`id` = `classic`.`sale-payments`.`idreason`
  INNER JOIN `classic`.`sale-business` ON `classic`.`sale-business`.`id` = `classic`.`sale-payments`.`idbusiness`
WHERE
 ((`classic`.`sale-business`.`suspended` = 'N') AND (`classic`.`sale-business`.`sendacct` = 'Y'))
ORDER BY
 `sale-trackers`.`entered`)
UNION ALL 
 (select
 'T' AS `Ex1`,`classic`.`sale-trackers`.`items` AS `items`,`classic`.`type-cargo`.`description` AS `description`,`classic`.`sale-trackers`.`id` AS `id`,`classic`.`sale-trackers`.`jobno` AS `jobno`,`classic`.`sale-trackers`.`idbusiness0` AS `idbusiness`,`classic`.`sale-trackers`.`entered` AS `entered`,`classic`.`sale-trackers`.`price` AS `xPrice`,`classic`.`sale-trackers`.`rate` AS `xRate`,`classic`.`sale-trackers`.`notes` AS `xNotes`,ifnull(concat(concat('(',`t1`.`suburb`),')'),`t4`.`company`) AS `xFrom`,IFNULL(CONCAT(CONCAT('(',`t2`.`suburb`),')'),`t5`.`company`) AS `xTo`
FROM
 ((((((`classic`.`sale-trackers`
 INNER JOIN `classic`.`type-cargo` ON((`classic`.`type-cargo`.`id` = `classic`.`sale-trackers`.`idcargo`)))
 INNER JOIN `classic`.`sale-business` `t3` ON((`t3`.`id` = `classic`.`sale-trackers`.`idbusiness0`)))
 INNER JOIN `classic`.`sale-business` `t4` ON((`t4`.`id` = `classic`.`sale-trackers`.`idbusiness1`)))
 INNER JOIN `classic`.`sale-business` `t5` ON((`t5`.`id` = `classic`.`sale-trackers`.`idbusiness2`))) 
 LEFT OUTER JOIN `classic`.`flat-postcodes` `t1` ON((`t1`.`id` = `classic`.`sale-trackers`.`idpostcode1`)))
 LEFT OUTER JOIN `classic`.`flat-postcodes` `t2` ON((`t2`.`id` = `classic`.`sale-trackers`.`idpostcode2`)))
WHERE
 ((`t3`.`suspended` = 'N') AND (`t3`.`sendacct` = 'Y'))
ORDER BY
 `sale-trackers`.`entered`);

Thank you for the report.

> Creating a view with LEFT\INNER JOIN changes to only "JOIN"

But what do you mean by this phrase? Does LEFT\INNER JOIN changes to only "JOIN" in the output of SHOW CREATE VIEW or where? Also, please, provide output of SHOW CREATE TABLE for all underlying tables.

-- 1. create table1
CREATE TABLE `t1` (
  `idt1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`idt1`)
)
ENGINE = InnoDB;

-- 2. create table2
CREATE TABLE `t2` (
  `idt2` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`idt2`)
)
ENGINE = InnoDB;

-- 3. create view
CREATE VIEW `myview` AS
select t1.* from t1 inner join t2 on t2.idt2=t1.idt1;

-- 4. [OUTPUT] SHOW CREATE VIEW `myview`
DROP VIEW IF EXISTS `myview`;
CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `myview` AS select `t1`.`idt1` AS `idt1` from (`t1` join `t2` on((`t2`.`idt2` = `t1`.`idt1`)));

Now, everything works as expected.  As a report programmer I would feel more **comfortable** if MySQL used more **modern** or **untempered** sql in the view and also the create.  You may wish to keep mysql the way it is for versions < 5.0.

I am unsure of internal workings of InnoDB, would say that the "engine" runs on the field content of I_S.view.view_definition -- which basically tampers sql embedded in the view at create time.

Thank you for the feedback.

So I assume my guesswork is right: "You complain about INNER JOIN converts to JOIN in the view definition". As they are equivalent (see http://dev.mysql.com/doc/refman/5.1/en/join.html for details) this is not a bug.

But you complain about LEFT JOIN also. LEFT JOIN is not same as JOIN, so this can be a bug. Could you please provide test case when LEFT JOIN converts to JOIN in view definition?

Changed to S4.

Looks like MySQL forces nested joins in sql_view.cc at line 1440.

Would be nice to have comment from developer anyway.

Nested (join(join(join)) apparently selects inner\left outer 'on the fly'.

This would never get approved as S4 since nested join are fail safe over DBA's hand written statements.

Closed bug, nested SQL statements are safer than non-nested.