MySQL Bugs: #16142: Calling a stored procedure that returns a resultset will return a 2nd resultset

Bug #16142	Calling a stored procedure that returns a resultset will return a 2nd resultset
Submitted:	2 Jan 2006 20:01	Modified:	26 Jul 2006 22:44
Reporter:	Hasani Blackwell	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.19-BK, 5.0.18	OS:	Linux (Linux, Windows)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
When CALLing a stored procedure that returns a resultset, and 2nd empty result set will be returned. If a stored procedure does not return a resultset, then only one resultset is returned.

E.x.:
Given the following procedures:

CREATE PROCEDURE `noop`() BEGIN END

CREATE PROCEDURE `echo`(IN txt TEXT)
BEGIN
	SELECT txt AS `echoed_text`;
END

Executing "CALL noop();CALL noop();CALL noop();" will return three result sets
but
Executing: "CALL echo('hi');CALL echo('hi');CALL echo('hi');" will return 6, with every other result set being an empty one starting with the 2nd result set.

E.x.:
CRT
Executing: "CALL noop();CALL noop();CALL noop();"
Empty resultset returned.
Empty resultset returned.
Empty resultset returned.
====================
CRT
Executing: "CALL echo('hi');CALL echo('hi');CALL echo('hi');"
1 field resultset returned.
Empty resultset returned.
1 field resultset returned.
Empty resultset returned.
1 field resultset returned.
Empty resultset returned.
====================
Press any key to continue

How to repeat:
//Add the following procedures to the database
//CREATE PROCEDURE `noop`()
//BEGIN
//END
//
//CREATE PROCEDURE `echo`(IN txt TEXT)
//BEGIN
//	SELECT txt AS `echoed_text`;
//END
#include "stdafx.h"
#include <stdio.h>
#include <winsock.h>
#include <mysql.h>

int executeQuery(const char* multiSQL)
{
	printf("CRT\n");
	MYSQL handle;
	mysql_init(&handle);
	
	if(&handle == NULL)
		return -1;
	
	if(!mysql_real_connect(&handle, "127.0.0.1", "root", NULL, "test", 0, NULL, CLIENT_MULTI_STATEMENTS | CLIENT_MULTI_RESULTS))
	{
		mysql_close(&handle);
		return -1;
	}

	printf("Executing: \"%s\"\n", multiSQL);
	if(mysql_real_query(&handle, multiSQL, (unsigned long) strlen(multiSQL)))
	{
		mysql_close(&handle);
		return -1;
	}
	
	MYSQL_RES* result = NULL;
	result = mysql_store_result(&handle);
	if(mysql_field_count(&handle) > 0)
		printf("%i field resultset returned.\n", mysql_field_count(&handle));
	else
		printf("Empty resultset returned.\n");
	if(result != NULL)
		mysql_free_result(result);
	while(true)
	{
		int res = mysql_next_result(&handle);
		if(res != 0)
		{
			mysql_free_result(result);
			mysql_close(&handle);
			return 0;
		}
		result = mysql_store_result(&handle);
		if(result != NULL)
		mysql_free_result(result);
		
		if(mysql_field_count(&handle) > 0)
			printf("%i field resultset returned.\n", mysql_field_count(&handle));
		else
			printf("Empty resultset returned.\n");
	}
	mysql_close(&handle);
	return 0;
}

int main(int argc, char* argv[])
{
	const char* multiSQL = "CALL noop();CALL noop();CALL noop();";
	
	executeQuery(multiSQL);
	printf("====================\n");

	multiSQL = "CALL echo('hi');CALL echo('hi');CALL echo('hi');";
	executeQuery(multiSQL);
	printf("====================\n");
	return 0;
}

reproduceable c code

Attachment: main.cpp (text/plain), 1.76 KiB.

I think I was wrong to say a "2nd resultset". It should be "an additional emprty resultset" since mysql stored procedures can probably return multiple resultsets.

And I want to clarify the statement "If a stored procedure does not return a resultset, then only one resultset is returned.". the returned resultset is empty/null, which is what one would expect.

Thank you for a problem report. Verified with your test case on Linux with latest 5.0.19-BK build (ChangeSet@1.2020.3.1, 2006-01-10 13:44:08+02:00):

[openxs@Fedora 5.0]$ ./16142
CRT
Executing: "CALL noop();CALL noop();CALL noop();"
Empty resultset returned.
Empty resultset returned.
Empty resultset returned.
====================
CRT
Executing: "CALL echo('hi');CALL echo('hi');CALL echo('hi');"
1 field resultset returned.
Empty resultset returned.
1 field resultset returned.
Empty resultset returned.
1 field resultset returned.
Empty resultset returned.
====================

I only replaced that <windows.h> with <string.h> and built with g++ as usual.

Looks like a bug or something that should be explained in the prominent place.

The extra result set does not contain any data but is used to return status information
about the procedure itself, e.g. about the warnings it produced.

So this is actually expected behavior, but i have to confess that it is not properly documented ...

I agree with Hartmut, this is a documentation issue.
And this design is precisely the reason why stored procedures that return a result set are not supported in prepared statements - multiple result sets are not implemented there.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.