MySQL Bugs: #28605: SHOW CREATE VIEW with views using stored

Bug #28605	SHOW CREATE VIEW with views using stored_procedures no longer showing SP names.
Submitted:	22 May 2007 23:57	Modified:	10 Jun 2007 20:55
Reporter:	Rolf Martin-Hoster
Status:	Closed
Category:	Server: Views	Severity:	S2 (Serious)
Version:	5.0.41, 5.1	OS:	Linux
Assigned to:	Georgi Kodinov	Target Version:
Tags:	regression

Description:
We migrated our server to 5.0.41 this AM and everything has been running smooth. However
this afternoon I noticed that the mysql dumps we use to propagate our testing enviroment
were broken. It turns out that views using stored procedures are not showing the SP
function name in SHOW CREATE VIEW. INFORMATION_SCHEMA.VIEWS does, however, show the
correct information. Replacing the views fixes the problem.

How to repeat:
I've included dumps of show create view and i_s.view in private comments.

Thank you for the report.

I can not repeat described behaviour with sample data.

Please provide output of SHOW CREATE TABLE and SHOW CREATE FUNCTION  for all underlying
tables and stored functions.

I have recreated all of the functions under one of my other views and recreated the view
it self and now I receive an error. On show create view or select :
mysql> show create view account_transfer_view;
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> select * from account_transfer_view;
ERROR 1241 (21000): Operand should contain 1 column(s)

This is the same error I would get when executing a dumped sql file.

Things have gotten signifigantly worse. I attempted to recreated the functions and views
on the live server and am getting operand errors. I have rolled back to 5.0.33 and able to
drop/replace views and they are functioning again.

This is not limited to one view, any view with SP.

test case

Attachment: bug28605.test (application/octet-stream, text), 4.08 KiB.

Thank you for the feedback.

The error was verified. All versions are affected.

Error occured since 5.0.41

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/27562

ChangeSet@1.2504, 2007-05-29 14:45:30+03:00, gkodinov@magare.gmz +3 -0
  Bug #28605: SHOW CREATE VIEW with views using stored_procedures no 
   longer showing SP names.
  SHOW CREATE VIEW uses Item::print() methods to reconstruct the 
  statement text from the parse tree.
  The print() method for stored procedure calls needs allocate 
  space to print the function's quoted name.
  It was incorrectly calculating the length of the buffer needed 
  (was too short).
  Fixed to reflect the actual space needed.

Pushed into 5.1.20-beta

Pushed into 5.0.44

Thank you for your bug report. This issue has been committed to our source repository of
that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version,
including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.44 and 5.1.20 changelogs.

Bug #30057 was marked as duplicate of this one