MySQL Bugs: #42488: metadata field 'length' is 0 after preparing statement: "select ?"

Bug #42488	metadata field 'length' is 0 after preparing statement: "select ?"
Submitted:	30 Jan 2009 18:35	Modified:	24 Feb 2009 8:24
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.1.30	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysql_stmt_result_metadata

Description:
For a program that doesn't know the nature of queries it is preparing 
(num cols, col type), it must rely on the results of mysql_stmt_result_metadata() to make provision for handling the resultset correctly.   

However, this query doesn't give a length or a max_length value:

char *query="select ?";

The problem is that now a client app doesn't know how to setup the MYSQL_BIND result buffers in a generic way.

Output of the testcase is this:

[sbester@localhost ~]$ ./testcase 
client version=50131 (5.1.31)
server version=50130
[ps_func_0000] resultset will contain 1 columns

fields[0].name             = ?
fields[0].table            = 
fields[0].db               = 
fields[0].length           = 0  <------- problematic
fields[0].max_length       = 0
fields[0].type             = 253
<cut>

How to repeat:
attached .c code.

Suggested fix:
Not sure. It would be nice to have some default value for character fields, instead of 0.  Workaround is to have the client application set a default value if server returns zero.

testcase

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

Server returns correct length for statements like "select 'foo'" or "select f1 from t1", so it mostly looks like not a bug for me. Regarding to generic application wouldn't it be not safe to return random values?

If you call mysql_stmt_result_metadata() after each mysql_stmt_execute(), the correct metadata is returned.   However, this workaround is too inefficient to use always, because too many calls would have to be made (1 per execute instead of 1 per prepare), which may involve the client malloc/free buffers all over again.

Sveta: I agree with you - the only reliable "length" that can be guessed is
to make it equal to max_allowed_packet, which is too large.  So, I think this is a case that might well be best handled by the client...

If client finds unacceptable metadata when calling mysql_stmt_result_metadata() before mysql_stmt_execute(), then it will call it after each mysql_stmt_execute().  Otherwise, only call mysql_stmt_result_metadata() once after mysql_stmt_prepare().