MySQL Bugs: #20661: mysql_next_result and mysql_more_results don't work for stored procedures

Bug #20661	mysql_next_result and mysql_more_results don't work for stored procedures
Submitted:	23 Jun 2006 14:00	Modified:	26 Jul 2006 22:43
Reporter:	Ian Sparrowhawk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.22	OS:	Linux (Linux)
Assigned to:	Paul Dubois	CPU Architecture:	Any
Triage:	D4 (Minor)

Description:
mysql_next_result and mysql_more_results don't work for stored procedures, they just keep returning values suggesting that there are more resultsets to process

How to repeat:
1. Invoke a procedure which optionally returns one or more resultsets. 

2. Loop over the resultsets, for example:

         int i = 0;
         do
         {
            resultset[i] = mysql_use_result(connection);
            if (resultset[i] == NULL)
            {
               std::cout << "Null resultset\n";
            }
            std::cout << "Resultset " << i << " read\n";
            mysql_free_result(queryresult[i]);
            i++;
          } while(!mysql_next_result(connection));

If our stored procedure returns 3 resultsets we would expect the following output:

     Resultset 0 read
     Resultset 1 read
     Resultset 2 read

However, the following occurs:

     Resultset 0 read
     Resultset 1 read
     Resultset 2 read
     Null resultset
     Resultset 3 read
     Segmentation fault

Thank you for a problem report. Please, check with a newer version, 5.0.22. In case of the same behaviour, please, provide a complete test case, including CREATE PROCEDURE and complete C code of your test program.

Script to insert data into the example Coin table

Attachment: CoinsData.sql (application/octet-stream, text), 1.47 KiB.

Script to create the example Coin table

Attachment: CoinsTable.sql (application/octet-stream, text), 269 bytes.

Stored procedure called by the example program

Attachment: getColour.sql (application/octet-stream, text), 707 bytes.

Make file to build the example (g++)

Attachment: Makefile (text/plain), 509 bytes.

Example code to demonstrate the bug

Attachment: example1.cpp (application/octet-stream, text), 2.20 KiB.

Repeated with 5.0.22 as requested, still doesn't work.  Example files attached to recreate the problem...

Thanks,

Ian Sparrowhawk

Segmentation fault was because 4 resultsets are returned, really. When I changed line 5 to:

MYSQL_RES *queryresult[4]; // SP should return 3 resultsets

I've got the following results:

openxs@suse:~/dbs/5.0> export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/openxs/dbs
/5.0/lib/mysql
openxs@suse:~/dbs/5.0> export CFG=/home/openxs/dbs/5.0/bin/mysql_config
openxs@suse:~/dbs/5.0> g++ -o 20661 `$CFG --cflags` 20661.cpp `$CFG --libs`
openxs@suse:~/dbs/5.0> vi 20661.cpp
openxs@suse:~/dbs/5.0> g++ -o 20661 `$CFG --cflags` 20661.cpp `$CFG --libs`
openxs@suse:~/dbs/5.0> ./20661
Stored Procedure called successfully
Result set 0 read
Result set 1 read
Result set 2 read
Null resultset
Retval=0, resultsets= 3

There are 4 result sets for the procedure, really (you are printing out i, but i started as 0). So, mysql_next_result just works as expected.

The stored procedure consists of only 3 select statements, so should produce the same results as the SQL commented as 'Version 2' in the example.c file. If it is indeed producing a phantom 4th resultset then we have a more serious bug than I first thought.

You say the output from the program when you ran it calling the stored procedure was:

	Result set 0 read
	Result set 1 read
	Result set 2 read
	Null resultset

The do..while loop is terminated on the condition that mysql_next_result() returns -1 (Successful and there are no more results) hence the loop should terminate after Resultset 2, not go on and return a Null resultset.

If you run the code with Version 2 uncommented instead of Version 1 you will see the following results:

	Result set 0 read
	Result set 1 read
	Result set 2 read
	Retval=0, resultsets= 3

The value of i, it is incremented at the end of the loop, hence having returned resultsets 0, 1 and 2, the final value of i will be 3.  Slightly misleading/lazy coding on my part, my apologies!

This is not a bug, expected behaviour, known to many users already, but it is described only in user comment at http://dev.mysql.com/doc/refman/5.0/en/call.html:

"Posted by Andy Dustman on March 5 2006 5:02am	[Delete] [Edit]

Since every statement in MySQL generates a result set (often empty), CALL also generates an empty result set in addition to any result sets which may be generated by the stored procedure.
..."

I think, this should be explicitely documented in tha main part of that page, and proper examples should added to:

http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html
http://dev.mysql.com/doc/refman/5.0/en/mysql-next-result.html

as well. So, it is valid documentation request.

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.