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

[18 Oct 2006 6:25] Roberto Spadim
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?
[18 Oct 2006 6:51] Sveta Smirnova
Thank you for the report.

Verified as described on Linux using last BK sources. Version 5.0 is affected too.
[16 Dec 2006 13:52] Bugs System
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.
[10 Jan 2007 17:13] Bugs System
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.
[11 Jan 2007 13:46] Bugs System
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.
[28 Jan 2007 2:01] Igor Babaev
Pushed to 4.1.23, 5.0.36, 5.1.16-beta main trees.
[9 Feb 2007 20:31] Paul DuBois
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.