MySQL Bugs: #23409: create view with problem

Bug #23409	create view with problem
Submitted:	18 Oct 2006 6:25	Modified:	9 Feb 2007 20:31
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.11-beta, 5.0 BK, 4.1	OS:	Any (all)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	create view with problem

Description:
view query with bug, can't get right query after create view

with 
show fields from vtemp_valf;

error occurs:
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 ') AS `c_temp` from `raja_rondonopolis_temps`.`temp_valf`' at lin

How to repeat:
CREATE TABLE `temp_valf` ( `c_datahora` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `c_temperatura` char(255) NOT NULL DEFAULT '', PRIMARY KEY (`c_datahora`) ) ;

create view vtemp_valf as 
select temp_valf.c_datahora,decode(temp_valf.c_temperatura,'SIF 3941') as c_temp from temp_valf;

show fields from vtemp_valf;

Suggested fix:
?!?! what's going on?

Thank you for the report.

Verified as described on Linux using last BK sources. Version 5.0 is affected too.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17093

ChangeSet@1.2349, 2006-12-16 00:49:08+03:00, evgen@moonbone.local +4 -0
  Bug#23409: Arguments of the ENCODE() and the DECODE() functions wasn't printed
  correctly.
  
  The Item_func::print method was used to print the Item_func_encode and the
  Item_func_decode objects. The last argument to ENCODE and DECODE functions
  is a plain C string and thus Item_func::print wasn't able to print it. This
  leads to those objects being wrongly printed and all view using these
  functions are being erroneous.
  
  The print() method is added to the Item_func_encode class. It correctly
  prints the Item_func_encode and the Item_func_decode objects.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17867

ChangeSet@1.2594, 2007-01-10 20:12:18+03:00, evgen@moonbone.local +4 -0
  Bug#23409: Arguments of the ENCODE() and the DECODE() functions wasn't printed
  correctly.
  
  The Item_func::print method was used to print the Item_func_encode and the
  Item_func_decode objects. The last argument to ENCODE and DECODE functions
  is a plain C string and thus Item_func::print wasn't able to print it.
  
  The print() method is added to the Item_func_encode class. It correctly
  prints the Item_func_encode and the Item_func_decode objects.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17933

ChangeSet@1.2596, 2007-01-11 16:45:38+03:00, evgen@moonbone.local +4 -0
  Bug#23409: Arguments of the ENCODE() and the DECODE() functions were not printed
  correctly.
  
  The Item_func::print method was used to print the Item_func_encode and the
  Item_func_decode objects. The last argument to ENCODE and DECODE functions
  is a plain C string and thus Item_func::print wasn't able to print it.
  
  The print() method is added to the Item_func_encode class. It correctly
  prints the Item_func_encode and the Item_func_decode objects.

Pushed to 4.1.23, 5.0.36, 5.1.16-beta main trees.

Noted in 4.1.23, 5.0.36, 5.1.16 changelogs.

The arguments of the ENCODE() and the DECODE() functions were not
printed correctly, causing problems in the output of EXPLAIN EXTENDED
and in view definitions.