Bug #61504 Error generating view code

• Submitted: 13 Jun 2011 17:38
• Modified: 14 Jun 2011 21:22
• Reporter: Josep Lluís Figueras
• Status: Closed
• Category: MySQL Server: Views
• Severity: S2 (Serious)
• Version: 5.1.41
• OS: Linux (Ubuntu 10.04)
• CPU Architecture: Any
• Tags: Incorrect view code generation

[13 Jun 2011 17:38] Josep Lluís Figueras

Description:
I have a schema with table sessio, view resum_vista_2, function carrega_dia() and view resum_vista. All works well but mysqldump generates the code of view resum_vista not very well.

The definition of view resum_vista I wrote is in the first line, but Mysql generates the second line. They have some important differences! 

CODE I WROTE:
select if((dayofweek(sess.dia) >= 2), from_days((to_days(sess.dia) - (dayofweek(sess.dia) - 2))), from_days((to_days(sess.dia) - 6))) AS setmana,
round(sum(sess.distancia),2) AS distancia,round(avg(sess.distancia),2) AS distancia_mitjana, 
round((sum((time_to_sec(sess.temps) * sess.batecs_mig)) / sum(time_to_sec(sess.temps))),0) AS batecs_mig,
round((sum((time_to_sec(sess.temps) * sess.batecs_max)) / sum(time_to_sec(sess.temps))),0) AS batecs_max,
sec_to_time((sum(time_to_sec(sess.temps)) / sum(sess.distancia))) AS ritme,
sec_to_time(sum(time_to_sec(sess.temps))) AS temps_total, 
round(avg(sess.pujada),0) AS pujada_mitjana, 
round(avg(sess.baixada),0) AS baixada_mitjana, 
round(avg(sess.pes),2) AS pes_mig, 
round(avg(sess.greix),2) AS greix_mig, 
round(sum(sess.carrega),2) AS carrega_total, 
round((sum(sess.carrega) / 7),2) AS carrega_diaria, 
if((dayofweek(sess.dia) >= 2), (select carrega_dia(from_days(((to_days(sess.dia) - (dayofweek(sess.dia) - 2)) - 1)))),(select carrega_dia((from_days((to_days(sess.dia) - 6)) - 1)))) AS adaptacio_anterior from resum_vista_2 sess 
group by 1 
order by 1 desc;

INCORRECT CODE GENERATION BY MYSQLDUMP
select if((dayofweek(sess.dia) >= 2), from_days((to_days(sess.dia) - (dayofweek(sess.dia) - 2))), from_days((to_days(sess.dia) - 6))) AS setmana,
round(sum(sess.distancia),2) AS distancia,round(avg(sess.distancia),2) AS distancia_mitjana, 
round((sum((time_to_sec(sess.temps) * sess.batecs_mig)) / sum(time_to_sec(sess.temps))),0) AS batecs_mig,
round((sum((time_to_sec(sess.temps) * sess.batecs_max)) / sum(time_to_sec(sess.temps))),0) AS batecs_max,
sec_to_time((sum(time_to_sec(sess.temps)) / sum(sess.distancia))) AS ritme,
sec_to_time(sum(time_to_sec(sess.temps))) AS temps_total,
round(avg(sess.pujada),0) AS pujada_mitjana,
round(avg(sess.baixada),0) AS baixada_mitjana,
round(avg(sess.pes),2) AS pes_mig,
round(avg(sess.greix),2) AS greix_mig,
round(sum(sess.carrega),2) AS carrega_total,
round((sum(sess.carrega) / 7),2) AS carrega_diaria, 
if((dayofweek(sess.dia) >= 2), (select carrega_dia(from_days(((to_days(sess.dia) - (dayofweek(sess.dia) - 2)) - 1))) AS carrega_dia(from_days(((to_days(sess.dia) - (dayofweek(sess.dia) - 2)) - 1)))), (select carrega_dia((from_days((to_days(sess.dia) - 6)) - 1)) AS carrega_dia((from_days((to_days(sess.dia) - 6)) - 1)))) AS adaptacio_anterior 
from resum_vista_2 sess 
group by 1 
order by 1 desc;

How to repeat:
Create, in this order, table sessio, view resum_vista_2, function carrega_dia() and view resum_vista.

[13 Jun 2011 18:02] Valeriy Kravchuk

Please, check if the same problem happens with a newer version, 5.1.57.

[14 Jun 2011 20:37] Josep Lluís Figueras

Thank you,Valeriy.

I have tested version 5.1.54 together Ubuntu 11.04 and it works!

[14 Jun 2011 21:22] MySQL Verification Team

Closed according reporter last comment.