Bug #23409 create view with problem
Submitted: 18 Oct 2006 8:25 Modified: 9 Feb 2007 21:31
Reporter: Roberto Spadim (Basic Quality Contributor)
Status: Closed
Category:Server Severity:S1 (Critical)
Version:5.1.11-beta, 5.0 BK, 4.1 OS:Any (all)
Assigned to: Evgeny Potemkin Target Version:
Tags: create view with problem

[18 Oct 2006 8: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 8: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 14: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 18: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 14: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 3:01] Igor Babaev 
Pushed to 4.1.23, 5.0.36, 5.1.16-beta main trees.
[9 Feb 2007 21: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.