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)

[23 Jun 2006 14:00] Ian Sparrowhawk
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
[23 Jun 2006 15:29] Valeriy Kravchuk
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.
[27 Jun 2006 8:44] Ian Sparrowhawk
Script to insert data into the example Coin table

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

[27 Jun 2006 8:44] Ian Sparrowhawk
Script to create the example Coin table

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

[27 Jun 2006 8:45] Ian Sparrowhawk
Stored procedure called by the example program

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

[27 Jun 2006 8:45] Ian Sparrowhawk
Make file to build the example (g++)

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

[27 Jun 2006 8:46] Ian Sparrowhawk
Example code to demonstrate the bug

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

[27 Jun 2006 8:47] Ian Sparrowhawk
Repeated with 5.0.22 as requested, still doesn't work.  Example files attached to recreate the problem...

Thanks,

Ian Sparrowhawk
[27 Jun 2006 14:25] Valeriy Kravchuk
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.
[27 Jun 2006 15:00] Ian Sparrowhawk
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!
[27 Jun 2006 15:10] Valeriy Kravchuk
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.
[26 Jul 2006 22:43] Paul Dubois
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.