Description:
When executing the non-prepared version of the below statement, an empty result is returned. When using the prepared statement above, the result is arbitrary gibberish or one row with empty data (gibberish seems to be a lot more common when using JDBC (version 3.1.8a)).
If the statement is executed at different times, different data will be returned. Sometimes the data is just total gibberish, while sometimes it is readable data from tables not included in the query (not seldom in other databases) or internal MySQL table structure information. The latter is why I have classified this bug as critical.
The bug may be related to bug 9777.
How to repeat:
# Create data
CREATE TABLE production (
production_id int(11) unsigned NOT NULL auto_increment,
partner_id varchar(35) NOT NULL,
production_status_id int(10) unsigned,
PRIMARY KEY (production_id)
) ENGINE=InnoDB;
INSERT INTO production VALUES("1", "Partner1", "10");
INSERT INTO production VALUES("2", "Partner2", "10");
INSERT INTO production VALUES("3", "Partner3", "10");
INSERT INTO production VALUES("4", "Partner4", "10");
CREATE TABLE production_line_article (
production_id int(11) unsigned NOT NULL default '0',
production_line_id int(11) unsigned NOT NULL default '0',
article_id varchar(20),
sequence int(11) NOT NULL default '0',
PRIMARY KEY (production_id,production_line_id)
) ENGINE=InnoDB;
INSERT INTO production_line_article VALUES("1", "1", "SUP", "0");
INSERT INTO production_line_article VALUES("2", "1", "SUP", "1");
INSERT INTO production_line_article VALUES("2", "2", "SUP", "2");
INSERT INTO production_line_article VALUES("2", "3", "SUP", "3");
INSERT INTO production_line_article VALUES("2", "4", "IMP", "4");
INSERT INTO production_line_article VALUES("3", "1", "SUP", "0");
INSERT INTO production_line_article VALUES("4", "1", "SUP", "0");
CREATE TABLE production_precedence (
production_id int(11) NOT NULL default '0',
preceeding_production_id int(11) NOT NULL default '0',
PRIMARY KEY (production_id,preceeding_production_id)
) ENGINE=InnoDB;
CREATE TABLE user_operation (
user_id varchar(50) NOT NULL,
article_id varchar(20) NOT NULL,
PRIMARY KEY (user_id,article_id)
) ENGINE=InnoDB;
INSERT INTO user_operation VALUES("nicke", "IMP");
# Run query
PREPARE stmt1 FROM 'SELECT DISTINCT production.partner_id
FROM production
LEFT JOIN production_precedence ON production.production_id = production_precedence.production_id
LEFT JOIN production pp ON pp.production_id = production_precedence.preceeding_production_id
WHERE EXISTS (
SELECT *
FROM production_line_article AS PL_INNER
WHERE PL_INNER.production_id = production.production_id
AND PL_INNER.sequence <= (
SELECT MIN(sequence) FROM production_line_article PL_SEQNR
WHERE PL_SEQNR.production_id = production.production_id
)
AND EXISTS (
SELECT * FROM user_operation
WHERE user_operation.article_id = PL_INNER.article_id
AND user_operation.user_id = ?
)
)
AND production.production_id = ?
GROUP BY production.production_id
HAVING COUNT(pp.production_id) = 0';
SET @user_id = 'nicke';
SET @production_id = '2';
EXECUTE stmt1 USING @user_id, @production_id;
Suggested fix:
Make the prepared statement behave just like the non-prepared version.
