Search code examples
mysqlviewdesigner

All MySQL designers generating wrong code when altering view

I have a MySQL View that is perfectly working:

SELECT `t`.`TraderId` AS `TraderId`,`t`.`ShopName` AS `ShopName`,`t`.`UserId` AS `UserId`,`t`.`CreatedOn` AS `CreatedOn`,`t`.`MOL` AS `MOL`,`u`.`Phone` AS `Phone`,`u`.`Email` AS `Email`,`rt`.`Name` AS `RentType`,(
SELECT SUM(`o`.`Ammount`) AS `AmmountSum`
FROM `Orders` `o`
WHERE (`o`.`TraderId` = `t`.`TraderId`)) AS `Revenue`,(
SELECT SUM(`o`.`Ammount`) AS `SUM(o.Ammount)`
FROM `Orders` `o`
WHERE ((MONTH(`o`.`DeliveryDate`) = MONTH(CURDATE())) AND (YEAR(`o`.`DeliveryDate`) = YEAR(CURDATE())) AND (`o`.`TraderId` = `t`.`TraderId`))) AS `MonthRevenue`,(`t`.`RatingSum` / `t`.`RatingVotes`) AS `Rating`,(
SELECT CASE `rt`.`IsMonthlyBased` WHEN 1 THEN (
SELECT ( (12 * ((YEAR(CURDATE()) 
              - YEAR(`t`.`LastPaidDate`))) 
       + (MONTH(CURDATE()) 
           - MONTH(`t`.`LastPaidDate`))) * `rt`.`Tax`)) END) AS  `Debt`
FROM ((`Traders` `t`
LEFT JOIN `Users` `u` ON((`u`.`UserId` = `t`.`UserId`)))
LEFT JOIN `RentTypes` `rt` ON((`rt`.`RentTypeId` = `t`.`RentTypeId`)))

I don't know how to format the code so if you can copy and paste it inside in editor and see it. Thank you.

So the view is working. But when go to any designer(heideSql, workBench, navicat) and hit the alter view the view script gets generated wrong:

SELECT `t`.`TraderId` AS `TraderId`,`t`.`ShopName` AS `ShopName`,`t`.`UserId` AS `UserId`,`t`.`CreatedOn` AS `CreatedOn`,`t`.`MOL` AS `MOL`,`u`.`Phone` AS `Phone`,`u`.`Email` AS `Email`,`rt`.`Name` AS `RentType`,(
SELECT SUM(`o`.`Ammount`) AS `AmmountSum`
FROM `Orders` `o`
WHERE (`o`.`TraderId` = `t`.`TraderId`)) AS `Revenue`,(
SELECT SUM(`o`.`Ammount`) AS `AmmountSumMonth`
FROM `Orders` `o`
WHERE ((MONTH(`o`.`DeliveryDate`) = MONTH(CURDATE())) AND (YEAR(`o`.`DeliveryDate`) = YEAR(CURDATE())) AND (`o`.`TraderId` = `t`.`TraderId`))) AS `MonthRevenue`,(`t`.`RatingSum` / `t`.`RatingVotes`) AS `Rating`,(
SELECT (CASE `rt`.`IsMonthlyBased` WHEN 1 THEN (
SELECT (((12 * (YEAR(CURDATE()) - YEAR(`t`.`LastPaidDate`))) + (MONTH(CURDATE()) - MONTH(`t`.`LastPaidDate`))) * `rt`.`Tax`) AS `( (12 * ((YEAR(CURDATE()) 
              - YEAR(``t``.``LastPaidDate``))) 
       + (MONTH(CURDATE()) 
           - MONTH(``t``.``LastPaidDate``))) * ``rt``.``Tax``)`) END) AS `CASE ``rt``.``IsMonthlyBased`` WHEN 1 THEN (
SELECT ( (12 * ((YEAR(CURDATE()) 
              - YEAR(``t``.``LastPaidDate``))) 
       + (MONTH(CURDATE()) 
           - MONTH(``t``.``LastPaidDate``))) * ``rt``.``Tax``)) END`) AS `Debt`
FROM ((`Traders` `t`
LEFT JOIN `Users` `u` ON((`u`.`UserId` = `t`.`UserId`)))
LEFT JOIN `RentTypes` `rt` ON((`rt`.`RentTypeId` = `t`.`RentTypeId`)))

My last select column gets generated twice, there are double quotes (``) and so on. So where is my mistake?

Solution

  • MySQL views are stored in a format that is quickly parsed and executed by MySQL, but unlike with stored procedures, the text you entered to create the view is NOT stored. The code the designer pulls out should execute the same as your original query.

    The key is to save your CREATE VIEW statements to .sql files, and when you want to change the view open that file, modify, then copy and paste into MySQL.