CODEWITHSUNDEEP
mysql
quimmo
quimmo

Reputation: 23

why mysql change my code view?

I create tables:

CREATE TABLE `table1` (
  `idtable1` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idtable1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `table2` (
  `idtable2` int(11) NOT NULL AUTO_INCREMENT,
  `idtable1` int(11) DEFAULT NULL,
  `table2col1` int(11) DEFAULT NULL,
  `table2col2` int(11) DEFAULT NULL,
  PRIMARY KEY (`idtable2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

i create a view:

CREATE VIEW `view1` AS
select t2.* 
from table1 t1, table2 t2
where t1.idtable1 = t2.idtable1

when complie mysql change my code:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `view1` AS
    select 
        `t2`.`idtable2` AS `idtable2`,
        `t2`.`idtable1` AS `idtable1`,
        `t2`.`table2col1` AS `table2col1`,
        `t2`.`table2col2` AS `table2col2`
    from
        (`table1` `t1`
        join `table2` `t2`)
    where
        (`t1`.`idtable1` = `t2`.`idtable1`)

there any way to avoid this behavior?

here is another example, no answer: Why is mysql rewriting my view queries?

mysql adds unnecessary parenthesis and put the code on one line.

Even when using the correct syntax mysql change the view adding unnecessary parentheses.

Original:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `view2` AS
    select 
        `t2`.`idtable2` AS `idtable2`,
        `t2`.`idtable1` AS `idtable1`,
        `t2`.`table2col1` AS `table2col1`,
        `t2`.`table2col2` AS `table2col2`
    from
        `table1` `t1` 
         left join `table2` `t2` on  `t1`.`idtable1` = `t2`.`idtable1`

Compiled:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `view2` AS
    select 
        `t2`.`idtable2` AS `idtable2`,
        `t2`.`idtable1` AS `idtable1`,
        `t2`.`table2col1` AS `table2col1`,
        `t2`.`table2col2` AS `table2col2`
    from
        (`table1` `t1`
        left join `table2` `t2` ON ((`t1`.`idtable1` = `t2`.`idtable1`)))

Upvotes: 2

Views: 1166

Answers (1)

Schwern
Schwern

Reputation: 164939

What you're seeing is issues resulting from the SQL being compiled and then decompiled.

human readable SQL -> compiled form -> human readable SQL

Don't worry, it's all equivalent code. If you want an example, write some JSON by hand, run it through a JSON parser, then turn that data back into JSON. It won't look the same as the original.

This is a common problem in converting data known as "round tripping". Without extra work, non-semantic information like comments, indentation, and parenthesis (or the lack of them) is lost. MySQL may also apply optimizations and semantic transformations, such as turning your FROM/WHERE into a JOIN. It's also making implicit code and defaults (such as ALGORITHM = UNDEFINED) explicit.

Seeing the result of a round trip can help you spot subtle bugs in your code, especially about order of operations. A decompiler can sometimes be asked to add extra parenthesis to make the order obvious.

There's no point in MySQL storing your original CREATEs for tables and views, they become useless if ALTER is used. However, returning your queries as originally written may be possible.

Upvotes: 1

Related Questions