MySQL Bugs: #22870: Mallformed statement gets executed (calling SP from PS)

Bug #22870	Mallformed statement gets executed (calling SP from PS)
Submitted:	1 Oct 2006 10:28	Modified:	1 Aug 2007 10:49
Reporter:	Tonci Grgin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.0 BK, 5.1 BK	OS:	Linux (SUSE Linux 10.0)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
Calling SP from PS does not retrieve all rows.
This is the continuation of BUG#22297

How to repeat:
All necessary info, SQL statements, expected/gotten results inside attached test
case.

DROP PROCEDURE IF EXISTS testBug22297;
drop table if exists tblTestBug2297_1;
drop table if exists tblTestBug2297_2;
create table tblTestBug2297_1(
id varchar(20) NOT NULL default '',
Income double(19,2) default NULL);	
create table tblTestBug2297_2(
id varchar(20) NOT NULL default '', 
CreatedOn datetime default NULL);
DELIMITER $$
CREATE PROCEDURE testBug22297(pcaseid INT)
BEGIN
  SET @sql = "DROP TEMPORARY TABLE IF EXISTS tmpOrders";
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @sql = "CREATE TEMPORARY TABLE tmpOrders SELECT id, 100 AS Income FROM tblTestBug2297_1 GROUP BY id";
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SELECT id, Income FROM (SELECT e.id AS id ,COALESCE(prof.Income,0) AS Income FROM tblTestBug2297_2 e LEFT JOIN tmpOrders prof ON e.id = prof.id WHERE e.CreatedOn > '2006-08-01') AS Final; 
END $$
DELIMITER ;
INSERT INTO tblTestBug2297_1 (`id`,`Income`) VALUES ('a',4094.00),('b',500.00),('c',3462.17),('d',500.00),('e',600.00);
INSERT INTO tblTestBug2297_2 (`id`,`CreatedOn`) VALUES ('d','2006-08-31 00:00:00'),('e','2006-08-31 00:00:00'),('b','2006-08-31 00:00:00'),('c','2006-08-31 00:00:00'),('a','2006-08-31 00:00:00');

RESULT from cl client
mysql> call testBug22297(100);
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `e`
Org_table:  `tblTestBug2297_2`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     20
Max_length: 1
Decimals:   0
Flags:      NOT_NULL

Field   2:  `Income`
Catalog:    `def`
Database:   ``
Table:      `Final`
Org_table:  ``
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 3
Decimals:   0
Flags:      NUM

+----+--------+
| id | Income |
+----+--------+
| d  |    100 |
| e  |    100 |
| b  |    100 |
| c  |    100 |
| a  |    100 |
| d  |    100 |
| e  |    100 |
| b  |    100 |
| c  |    100 |
| a  |    100 |
| d  |    100 |
| e  |    100 |
| b  |    100 |
| c  |    100 |
| a  |    100 |
+----+--------+
15 rows in set (0.00 sec)

From the test case
Connected!

Running {call sp_test(?)}

 total parameters in SELECT: 1

Exec prep stmt
 total columns in statement: 2

Init result buffers before fetch

Bind result to buffer before fetch

Buffer result
Fetching results ...
  row 1
   column1 (varchar)  :  NULL(0)
   column2 (long)   :  8.88742e-314(0)

  row 2
   column1 (varchar)  :  NULL(0)
   column2 (long)   :  8.88742e-314(0)

 total rows fetched: 2
 MySQL failed to return all rows

Suggested fix:
-

Test case

Attachment: bug22297.c (text/x-csrc), 8.19 KiB.

Thank you for a problem report. Sorry, but this is a documented limitation of prepared statements in MySQL, it seems. According to the manual, http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statements.html:

"The following statements can be used as prepared statements: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW statements. Other statements are not supported in MySQL 5.0."

Changing synopsis according to Valeriy's findings.
According to manual http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statements.html:

"The following statements can be used as prepared statements: CREATE TABLE,
DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW statements.
Other statements are not supported in MySQL 5.0."

This should not work at all. It is my opinion, as statement text should be checked in prepare, that letting this construct pass and retreiving 10-20% of resultset is misleading, so this is a bug (with new synopsis).
We should throw error and stop execution of mallformed statement.

Can't repea it any more. Could be a side effect of one of the bugs in pre-locking/temporary tables handling in stored procedures fixed recently.
Since it is not clear what was causing the failure, closing without adding a test case.