MySQL Bugs: #27388: prepared statement crashes server or returns incorrect results

Bug #27388	prepared statement crashes server or returns incorrect results
Submitted:	23 Mar 2007 5:55	Modified:	5 Apr 2007 4:52
Reporter:	scott fagg	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	5.0.37, 5.1 bk, 5.2-falcon	OS:	Linux (linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Prepared statement produces different results on success runs. If query is executed as a normal query, results are identical each time.

On older version of mysql (5.0.18 on Fedora Core 5), same process crashes server.

First run of prepared statement produces expected results. Second run of prepared statement returns a ridiculous number of records.

How to repeat:
PREPARE MDB2_STATEMENT_mysql_17ce59e4bc7ec3383c84ded00d2ab13c FROM
'select count(*)
  from vw_TDRS
  left join vw_TDRH on vw_TDRS.review_tag = vw_TDRH.review_tag
  where vw_TDRS.task_tag = ? ';

SET @0 = 9332;
EXECUTE MDB2_STATEMENT_mysql_17ce59e4bc7ec3383c84ded00d2ab13c USING @0;

(returns a count of zero)

SET @0 = 9332 ;
EXECUTE MDB2_STATEMENT_mysql_17ce59e4bc7ec3383c84ded00d2ab13c USING @0 ;

(returns a count of 1250536)

Running the standalone query :

select count(*)
  from vw_TDRS
  left join vw_TDRH on vw_TDRS.review_tag = vw_TDRH.review_tag
  where vw_TDRS.task_tag = 9332

.. produces the same result (zero) each time.

leaving out the where clause :

select count(*)
  from vw_TDRS
  left join vw_TDRH on vw_TDRS.review_tag = vw_TDRH.review_tag

.. returns a count of 4986;

The amount of data i'm querying is quite large, and i haven't manage to produce a trivial case just yet. The views in question vw_TDRS and vw_TDRH are complicated, and refer to the same tables.

Script to generate target database : 

-- 
-- Table structure for table `deliverables_reviews_fields`
-- 

CREATE TABLE `deliverables_reviews_fields` (
  `tag` int(4) NOT NULL auto_increment,
  `help_id` int(11) NOT NULL,
  `deliverable_tag` int(4) NOT NULL default '0',
  `field_name` char(128) NOT NULL default '',
  `field_type` int(4) NOT NULL default '1',
  `field_source` varchar(255) NOT NULL,
  `default_value` char(80) default NULL,
  `note` char(255) default NULL,
  `review_title` int(1) NOT NULL default '0',
  `display_order` int(4) NOT NULL default '0',
  `parent` int(4) NOT NULL default '0',
  PRIMARY KEY  (`tag`),
  KEY `deliverable_tag` (`deliverable_tag`),
  KEY `field_type` (`field_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=147 ;

-- 
-- Dumping data for table `deliverables_reviews_fields`
-- 

INSERT INTO `deliverables_reviews_fields` (`tag`, `help_id`, `deliverable_tag`, `field_name`, `field_type`, `field_source`, `default_value`, `note`, `review_title`, `display_order`, `parent`) VALUES 
(23, 425, 1, 'Nature of Review', 1, '', '', NULL, 1, 23, 0),
(25, 424, 1, 'Applicable drawing procedures', 1, '', '', NULL, 0, 22, 0),
(26, 427, 1, 'Drawings Reviewed', 1, '', '', NULL, 0, 28, 0),
(27, 426, 1, 'Nominated Reviewer', 5, '', '', NULL, 0, 26, 0),
(38, 0, 1, 'Date Scheduled', 7, '', NULL, NULL, 0, 24, 0),
(39, 0, 1, 'Date Held', 8, '', NULL, NULL, 0, 25, 0),
(87, 0, 1, 'Attendees', 1, '', NULL, NULL, 0, 27, 0),
(88, 428, 1, 'Review Outcomes & follow up action', 1, '', NULL, NULL, 0, 29, 0),
(146, 0, 1, 'PD Initials for project level review', 16, '17', NULL, NULL, 0, 0, 0);

-- --------------------------------------------------------

-- 
-- Table structure for table `task_deliverables_reviews`
-- 

CREATE TABLE `task_deliverables_reviews` (
  `tag` int(14) NOT NULL default '0',
  `uid` int(4) NOT NULL default '0',
  `task_tag` int(4) NOT NULL default '0',
  `deliverable_tag` int(4) NOT NULL default '0',
  `active` int(4) NOT NULL default '0',
  `effective_from` int(12) default NULL,
  `effective_from_user` int(11) default '0',
  `effective_to` int(14) default NULL,
  `effective_to_user` int(11) default '0',
  PRIMARY KEY  (`tag`),
  KEY `task_tag` (`task_tag`),
  KEY `deliverable_tag` (`deliverable_tag`),
  KEY `active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 
-- Dumping data for table `task_deliverables_reviews`
-- 

INSERT INTO `task_deliverables_reviews` (`tag`, `uid`, `task_tag`, `deliverable_tag`, `active`, `effective_from`, `effective_from_user`, `effective_to`, `effective_to_user`) VALUES 
(17480, 17480, 13, 1, 1, 1174619873, 34017, NULL, 0);

-- --------------------------------------------------------

-- 
-- Table structure for table `task_deliverables_reviews_data`
-- 

CREATE TABLE `task_deliverables_reviews_data` (
  `tag` int(4) NOT NULL auto_increment,
  `review_tag` int(14) NOT NULL default '0',
  `data` text NOT NULL,
  `field_tag` int(4) NOT NULL default '0',
  PRIMARY KEY  (`tag`),
  KEY `review_tag` (`review_tag`),
  KEY `field_tag` (`field_tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=90865 ;

-- 
-- Dumping data for table `task_deliverables_reviews_data`
-- 

INSERT INTO `task_deliverables_reviews_data` (`tag`, `review_tag`, `data`, `field_tag`) VALUES 
(90856, 17480, 'f', 23),
(90857, 17480, 'f', 25),
(90858, 17480, 'f', 26),
(90859, 17480, '', 27),
(90860, 17480, '0', 38),
(90861, 17480, '0', 39),
(90862, 17480, 'f', 87),
(90863, 17480, 'f', 88),
(90864, 17480, '34017', 146);

-- --------------------------------------------------------

-- 
-- Table structure for table `vw_TDRH`
-- 

CREATE VIEW `vw_TDRH` AS select `TDR`.`task_tag` AS `task_tag`,`TDR`.`tag` AS `review_tag`,`TDR`.`deliverable_tag` AS `deliverable_tag`,`TDRD`.`data` AS `data`,`TDRD`.`field_tag` AS `field_tag`,`DR`.`field_type` AS `field_type` from ((`amsx`.`task_deliverables_reviews` `TDR` join `amsx`.`task_deliverables_reviews_data` `TDRD` on((`TDR`.`tag` = `TDRD`.`review_tag`))) join `amsx`.`deliverables_reviews_fields` `DR` on((`TDRD`.`field_tag` = `DR`.`tag`))) where ((`DR`.`field_type` = 8) and (`TDR`.`active` = 1));

-- --------------------------------------------------------

-- 
-- Table structure for table `vw_TDRS`
-- 

CREATE VIEW `vw_TDRS` AS select `TDR`.`task_tag` AS `task_tag`,`TDR`.`tag` AS `review_tag`,`TDR`.`deliverable_tag` AS `deliverable_tag`,`TDRD`.`data` AS `data`,`TDRD`.`field_tag` AS `field_tag`,`DR`.`field_type` AS `field_type` from ((`amsx`.`task_deliverables_reviews` `TDR` join `amsx`.`task_deliverables_reviews_data` `TDRD` on((`TDR`.`tag` = `TDRD`.`review_tag`))) join `amsx`.`deliverables_reviews_fields` `DR` on((`TDRD`.`field_tag` = `DR`.`tag`))) where ((`DR`.`field_type` = 7) and (`TDR`.`active` = 1));

script to trigger crash/incorrect results : 

PREPARE st2 FROM 'select count(*) 
from vw_TDRS 
left join vw_TDRH 
on vw_TDRS.review_tag = vw_TDRH.review_tag 
where vw_TDRS.task_tag = ? and vw_TDRS.deliverable_tag = ?';

SET @0 = 13;
SET @1 = 1;
EXECUTE st2 USING @0, @1;
SET @0 = 13 ;
SET @1 = 1 ;
EXECUTE st2 USING @0, @1 ;

On 5.0.37 this produces outputs of "1" and "9".
On 5.0.18 the server crashes.

Thank you for the report.

Verified as described.

Workaround: use @a, @b instead of @0, @1

Thanks for work-around. PHP's PEAR MDB2 module generates the @0 , @1 which is where i first struck the problem.

This problem was fixed by the patch for bug #20492. The test case reported there was just another manifestation of the problem.

The reported wrong behavior cannot be observed anymore:

mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 5.0.40-debug |
+--------------+
1 row in set (0.00 sec)

mysql> PREPARE st2 FROM 'select count(*)
    '> from vw_TDRS
    '> left join vw_TDRH
    '> on vw_TDRS.review_tag = vw_TDRH.review_tag
    '> where vw_TDRS.task_tag = ? and vw_TDRS.deliverable_tag = ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE st2 USING @0, @1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> SET @0 = 13 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @1 = 1 ;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE st2 USING @0, @1 ;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql>
mysql> SET @0 = 13 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @1 = 1 ;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE st2 USING @0, @1 ;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Thanks. Is 5.0.40 released ?