MySQL view with union not working after trying bug fix

Question

I have a mysql 5.6 view that needs a union. I read here on stack overflow there is a server bug using union in a view. The solution is to remove parenthesis in the from. I did that but it is not working and was hoping someone can help me get by the bug somehow.

I have 2 tables ordersuccess and orderfailure. the view calculates a percentage of success for that time period. The time period is every 15m, 4 per hour, 15,30,45,00. Here is schema, both are the same. A count of 0 is not added to the table. A null can be 0 or 100 depending on the ifnull statement.

+---------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | dtime | datetime | NO | PRI | NULL | | | counter | bigint(20) | NO | | NULL | | | rate | bigint(20) | YES | | NULL | | +---------+------------+------+-----+---------+-------+

I have a calendar table to fill in the blanks when nothing is reported during a period. It only has date/times for each period. Here is calendar table;

+-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | dtime | datetime | NO | PRI | NULL | | +-------+----------+------+-----+---------+-------+

Here is the query returning the correct data.

 select 
'1000591' AS `id`, 
`c`.`dtime` AS `dtime`, 
'0' AS `rate`, 
ifnull(`p`.`percent`,100) AS `counter`  
from 
(`calendar` `c` left join 
        (   
select 
    ifnull(f.dtime,s.dtime) as dtime,
    ifnull(ifnull(s.counter,0)/(ifnull(s.counter,0) + ifnull(f.counter,0)) * 100,100) as percent 
    from 
        (select * from OrderSummarySuccess_14521 where id = 1000591) s 
            right join 
        (select * from OrderSummaryFailure_14521 where id = 1000591) f 
        on s.dtime=f.dtime 
union 
select 
    ifnull(f.dtime,s.dtime) as dtime,
    ifnull(ifnull(s.counter,0)/(ifnull(s.counter,0) + ifnull(f.counter,0)) * 100,100) as percent 
    from 
        (select * from OrderSummarySuccess_14521 where id = 1000591) s 
            left join 
        (select * from OrderSummaryFailure_14521 where id = 1000591) f 
        on s.dtime=f.dtime 
    order by dtime  
        ) `p` 
        on((`c`.`dtime` = `p`.`dtime`))
) 
where c.dtime < now()`

I have to make multiple views due to having selects in the from clause. Here are my create view for this query. I removed all of the parenthesis I could. Did I miss something or is there a better way to write the query?

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW d61_14521 AS select '1000591' AS id, c.dtime AS dtime, 0 AS rate, ifnull(p.percent,100) AS counter from calendar c left join d61p2_14521 p on c.dtime = p.dtime;

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW d61P2_14521 AS select '1000591' AS id, c.dtime AS dtime, '0' AS rate, ifnull(p.percent,100) AS counter
from d61P3_14521 union d61P6_14521 where c.dtime < now();

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW d61P3_14521 AS select ifnull(f.dtime,s.dtime) as dtime, ifnull(ifnull(s.counter,0)/(ifnull(s.counter,0) + ifnull(f.counter,0)) * 100,100) as percent from d61P4_14521 s right join d61P5_14521 f on s.dtime=f.dtime ;

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW d61P4_14521 AS select * from OrderSummarySuccess_14521 where id = 1000591 ;

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW d61P5_14521 AS select * from OrderSummaryFailure_14521 where id = 1000591;

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW d61P6_14521 AS select ifnull(f.dtime,s.dtime) as dtime, ifnull(ifnull(s.counter,0)/(ifnull(s.counter,0) + ifnull(f.counter,0)) * 100,100) as percent from d61P4_14521 s left join d61P5_14521 f on s.dtime=f.dtime ;

I create the views in this order. They succeed until d61P2_14521.

1. d61P4_14521
2. d61P5_14521
3. d61P6_14521
4. d61P3_14521
5. d61P2_14521

The view that fails is d61P2_14521. Here is the error.

mysql> CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER -> VIEW d61P2_14521 AS select -> '1000591' AS id, -> c.dtime AS dtime, -> '0' AS rate, -> ifnull(p.percent,100) AS counter -> from -> d61P3_14521 -> union -> d61P6_14521 -> where c.dtime < now(); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'vod61P6_14521 where c.dtime < now()' at line 10

Answer 1

Until MySQL 5.7.7, you cannot use subqueries in from-clauses (or join-clauses) of a view. So you have to move these subqueries in seperate views. Your query can (after a little clean up) be split into 2 views:

create viewSubqueryFullOuterJoin as
  select 
    ifnull(f.dtime,s.dtime) as dtime,
    ifnull(ifnull(s.counter,0)/(ifnull(s.counter,0) 
        + ifnull(f.counter,0)) * 100,100) as percent 
  from OrderSummarySuccess_14521 s
  right join OrderSummaryFailure_14521 f 
  on s.id = 1000591 and f.id = 1000591 and s.dtime=f.dtime 
union 
  select 
    ifnull(f.dtime,s.dtime) as dtime,
    ifnull(ifnull(s.counter,0)/(ifnull(s.counter,0) 
         + ifnull(f.counter,0)) * 100,100) as percent 
  from OrderSummarySuccess_14521 s 
  left join OrderSummaryFailure_14521 f 
  on s.id = 1000591 and f.id = 1000591 and s.dtime=f.dtime
order by dtime;  -- 'order by' might belong in viewQueryWithSubquery

create viewQueryWithSubquery as
select 
  '1000591' AS `id`, 
  `c`.`dtime` AS `dtime`, 
  '0' AS `rate`, 
  ifnull(`p`.`percent`,100) AS `counter`  
from `calendar` `c` 
left join viewSubqueryFullOuterJoin `p`
on `c`.`dtime` = `p`.`dtime`
where `c`.dtime < now();