MySQL Bugs: #17476: Stored procedure not returning data when it is called first time per connection

Bug #17476	Stored procedure not returning data when it is called first time per connection
Submitted:	16 Feb 2006 15:57	Modified:	4 Mar 2006 1:11
Reporter:	Kayra Otaner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18-standard-log/5.0.19BK/5.1.7BK	OS:	Linux (RHEL4/Suse 10)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
We've created a very simple stored procedure to format date column from a very simple table. First time we call this sp it returns blank, but second and consequtive calls returns data. This is true for each fresh connection. If I prepare sql statement inside sp it works fine. 

How to repeat:
Here is the stored procedure and table definitions : 

CREATE TABLE temp ( d date );
INSERT INTO temp VALUES ( '2005-01-01' );
INSERT INTO temp VALUES ( '2005-01-02' );
INSERT INTO temp VALUES ( '2005-01-03' );
INSERT INTO temp VALUES ( '2005-01-04' );
INSERT INTO temp VALUES ( '2005-02-01' );
INSERT INTO temp VALUES ( '2005-02-02' );

DELIMITER //
DROP PROCEDURE IF EXISTS sptest
//

CREATE PROCEDURE sptest
(
      IN pDateFormat               VARCHAR(10)
)
BEGIN
  SELECT DATE_FORMAT(temp.d, pDateFormat), COUNT(*) FROM temp 
  GROUP BY DATE_FORMAT(temp.d, pDateFormat)
  ;
 END
//

Once we call the procedure first time :
CALL sptest ( '%Y-%m' );

+---------------------------------+----------+
| DATE_FORMAT(temp.d, pDateFormat) | COUNT(*) |
+---------------------------------+----------+
|                                 |        6 |
+---------------------------------+----------+

The following times it works as expected:

+---------------------------------+----------+
| DATE_FORMAT(temp.d, pDateFormat) | COUNT(*) |
+---------------------------------+----------+
| 2005-01                         |        4 |
| 2005-02                         |        2 |
+---------------------------------+----------+

Suggested fix:
Preparing SQL inside stored procedure first solves this problem.

Thank you for the bug report I was able to repeat:

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create db23
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db23
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.19-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE temp ( d date );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO temp VALUES ( '2005-01-01' );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO temp VALUES ( '2005-01-02' );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO temp VALUES ( '2005-01-03' );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO temp VALUES ( '2005-01-04' );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO temp VALUES ( '2005-02-01' );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO temp VALUES ( '2005-02-02' );
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS sptest
    -> //
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> 
mysql> CREATE PROCEDURE sptest
    -> (
    ->       IN pDateFormat               VARCHAR(10)
    -> )
    -> BEGIN
    ->   SELECT DATE_FORMAT(temp.d, pDateFormat), COUNT(*) FROM temp 
    ->   GROUP BY DATE_FORMAT(temp.d, pDateFormat)
    ->   ;
    ->  END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sptest ( '%Y-%m' )//
+----------------------------------+----------+
| DATE_FORMAT(temp.d, pDateFormat) | COUNT(*) |
+----------------------------------+----------+
|                                  |        6 |
+----------------------------------+----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL sptest ( '%Y-%m' )//
+----------------------------------+----------+
| DATE_FORMAT(temp.d, pDateFormat) | COUNT(*) |
+----------------------------------+----------+
| 2005-                            |        6 |
+----------------------------------+----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

It seems like your result set is weirder than mine. It returns 2005- and 6 as the single result row although it should return what I've sent. Can it be because you're using 5.0.19-debug?

When I try following sp, it works :

DELIMITER //

DROP PROCEDURE IF EXISTS `test`.`sptest`//
CREATE PROCEDURE sptest
(
      IN pDateFormat               VARCHAR(10)
)
BEGIN
        set @sql = concat('SELECT DATE_FORMAT(temp.d, \'', 
        pDateFormat, 
        '\'), COUNT(*) FROM temp GROUP BY DATE_FORMAT(temp.d, \'', 
        pDateFormat, 
        '\')');
       prepare stm1 from @sql;
        execute stm1;

END
//

DELIMITER ;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3257

Setting back to 'In progress' as in the review (made over email) I asked to use an alternative solution (and it can turn out to be wrong or not easy to implement).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3387

Pushed to 5.0.19 release tree, and 5.0.20 main tree.

Noted in 5.0.19 changelog.

A stored procedure failed to return data the first time it was
called per connection. (Bug #17476)