MySQL Bugs: #12489: use of CAST in views

Bug #12489	use of CAST in views
Submitted:	10 Aug 2005 11:53	Modified:	1 Sep 2005 0:34
Reporter:	Manjit Patel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.7-beta-standard	OS:	Linux (Redhat Linux AS3 AMD64)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
I am trying to create the following view: 

CREATE VIEW myView 
AS 
SELECT tbl1.ID, 
if(strcmp(CAST(tbl1.done AS CHAR), '1'), 'No', 'Yes') AS Done
FROM table1 tbl1 

I get the message 'Query OK' when I run the above, at the command line, to create the view. 

done is a TINYINT(1) field. I expected to get the 'Done' field returned as follows;

If done is 0 then I want 'No' to be returned otherwise if done is 1 then I want 'Yes' to be returned.

However, when I try to select the top 2 records from the view I get the following error; 

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 'strcmp _latin1'1'),_latin1'No',_latin1'Yes') AS `Done` FROM `table1` at line 1 

If I run the select statement above, without the 'CREATE VIEW myView AS' I get two rows returned with the results as expected.

I get the same problem in MySQL version 5.0.10-beta-standard

How to repeat:
Create a simple table with an ID and TINYINT field, populate with 1 record.  Create a view of that table as above using the strcmp function.  Then run the query:

select * from <table>;

You should get the same error as above.

I've experienced a similar issue when using multiple CAST statements when a creating a view.

also breaks SHOW CREATE VIEW, seems to be a bug in the view query rewriter?

a simpler test case:

  create view v1 as select strcmp(cast(42 as char),'1');

these two work though:

  create view v1 as select cast(42 as char);

  create view v1 as select concat(cast(42 as char),'1');

The following view definition should perform the expected operation without triggering the bug
(and with less overhead):

CREATE VIEW myView  
  AS  SELECT tbl1.ID,  if(tbl1.done = 1, 'Yes', 'No') AS Done FROM table1 tbl1;

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/internals/29059

For Item_func_strcmp print() was not defined and for this class was called
print_op of it's parent class. Because of this strcmp() was printed wrongly
and this results int creation of broken view.

Fixed in 5.0.12, cset 1.1918.1.1

Noted in 5.0.12 changelog.