MySQL Bugs: #42490: mysql_stmt_result_metadata incorrectly returns null for some statements

Bug #42490	mysql_stmt_result_metadata incorrectly returns null for some statements
Submitted:	30 Jan 2009 18:55	Modified:	2 Feb 2009 7:08
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	5.1.30, 4.1, 5.0, 5.1, 6.0 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysql_stmt_result_metadata

Description:
mysql_stmt_result_metadata return null for some statements that do indeed return a resultset.

e.g:
optimize table t1;
show processlist;

there might be more statements.

This can confuse client programs, and lead to the client thinking there are no results to fetch, which then may cause 'commands out of sync' problems later.

How to repeat:
attached..

Suggested fix:
return the resultset for metadata for all commands that have a resultset!

testcase

Attachment: bug42490.c (text/plain), 4.53 KiB.

Most, if not all SHOW commands are affected too.  The resultset metadata is shown when those same commands are run in "mysql --column-type-info" mode...

Thank you for the report.

Verified as described.

the more I think about this, the more I wonder if it's not a bug at all, like my explanation at bug #42488 . I'll leave it to somebody else to decide, since I'm evidently confused :)

mysqlnd has this comment, which is based on a comment from libmysql:

/*
  Read metadata only if there is actual result set.
  Beware that SHOW statements bypass the PS framework and thus they send
  no metadata at prepare.
*/

If it is a bug at all it is not a CAPI bug. The metadata is not generated
inside client library but it is sent from server in the reply to
COM_STMT_PREPARE. Libmysql merely parses this metadata if present (in
cli_read_prepare_result()) and gives it to the user via
mysql_stmt_result_metadata() function.

Thus, if it is decided that after preparing statements like "optimize table
t1" or "show processlist" user should get metadata info from
mysql_stmt_reslut_metadata(), it is enough to send this metadata from server
after COM_STMT_PREPARE commmand. As soon as server sends the metadata,
libmysql will give it to the user.

Posted by developer:
 
This problem also affects preparing of Stored Procedure calls that return result-set.
For SP like this:

create procedure sample()
begin
  select 'abc';
end 

This C API call returns success:
mysql_stmt_prepare(stmt, "call sample();", ...);

However, mysql_stmt_field_count(stmt) returns 0 fields and mysql_stmt_result_metadata(stmt) returns NULL.

This was verified using libmysqlclient (5.7.44, 8.1.0, 8.2.0) and MySQL Server (5.7.44, 8.1.0, 8.2.0)

Posted by developer:
 
We have an ODBC bug#35362206 that is affected by this issue. In this bug the problem is that the client library gives no meta-data for a prepared statement that executes a stored procedure (which returns a result set). Perhaps this is the basic problem here -- the other statements for which no meta-data is given could be implemented as calls to stored procedures (but I am just guessing here).

Question is: can server give meta-data information for a statement that calls a stored procedure?

Posted by developer:
 
In general it is not possible to know what result will be produced (if any) by a "CALL ..." statement. From that perspective it is correct that server gives no result meta-data when preparing such statement. One could consider adding heuristic guesses that would work in simple cases but it is disputable how useful that would be. I think this bug can be rejected as "won't fix".

Posted by developer:
 
I need to correct my last comment. While it is understandable that no meta-data is given when preparing a "CALL ..." statement the original bug reports other statements for which no meta-data is given, such as "SHOW ..." statements and others. These still need to be investigated.