| Bug #80032 | SELECT in Stored Procedure not work correct | ||
|---|---|---|---|
| Submitted: | 18 Jan 2016 14:53 | Modified: | 19 Jan 2016 11:20 |
| Reporter: | tapin13 none | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S1 (Critical) |
| Version: | 5.5.46 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | Any | |
[19 Jan 2016 7:09]
MySQL Verification Team
Hello! Thank you for the report. Could you please provide exact repeatable test case( DDL of table, and exact routine used). If you can provide more information, feel free to add it to this bug and change the status back to 'Open'. Thank you for your interest in MySQL. Thanks, Umesh
[19 Jan 2016 11:20]
tapin13 none
Stored Procedure:
DELIMITER $$
USE `binaricoptions`$$
DROP PROCEDURE IF EXISTS `spForgotPassword`$$
CREATE DEFINER=`root`@`%` PROCEDURE `spForgotPassword`(
`email` VARCHAR(255)
, `language` VARCHAR(255)
)
BEGIN
DECLARE `customerID` INT;
DECLARE `firstName` VARCHAR(255);
DECLARE `lastName` VARCHAR(255);
DECLARE `templateID` INT;
DECLARE `subject` VARCHAR(255);
DECLARE `body` TEXT;
SET `customerID` = 0;
SET `templateID` = 0;
SELECT
`tcustomers`.fID
, `tcustomers`.`fFirstName`
, `tcustomers`.`fLastName`
INTO
`customerID`
, firstName
, lastName
FROM
`tcustomers`
WHERE
tcustomers.`fDeleted` = 0
AND fEmail = `email`
LIMIT 1;
SELECT
ttemplates.`fID`
, ttemplates.fsubject
, ttemplates.fbody
INTO `templateID`, `subject`, `body`
FROM
ttemplates
INNER JOIN `tlanguages` ON (`tlanguages`.`fID` = ttemplates.flanguageID)
WHERE
`tlanguages`.`fCode` = `language`
AND ttemplates.`ftype` = 'forgotPassword'
LIMIT 1;
IF `templateID` > 0 THEN
SELECT
`customerID` `customerID`
, firstName `firstName`
, lastName `lastName`
, `subject` `subject`
, `body` `body`
;
ELSE
SELECT
`customerID` `customerID`
, firstName `firstName`
, lastName `lastName`
, ttemplates.fsubject `subject`
, ttemplates.fbody `body`
FROM
ttemplates
WHERE
ttemplates.flanguageID = 1
AND ttemplates.`ftype` = 'forgotPassword'
;
END IF;
END$$
DELIMITER ;
-------------------
This select, make it not correct
SELECT
ttemplates.`fID`
, ttemplates.fsubject
, ttemplates.fbody
INTO `templateID`, `subject`, `body`
FROM
ttemplates
INNER JOIN `tlanguages` ON (`tlanguages`.`fID` = ttemplates.flanguageID)
WHERE
`tlanguages`.`fCode` = `language`
AND ttemplates.`ftype` = 'forgotPassword'
LIMIT 1;
Table of templates:
CREATE TABLE `ttemplates` (
`fID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ftype` enum('forgotPassword') NOT NULL,
`flanguageID` int(10) unsigned NOT NULL DEFAULT '0',
`fSubject` varchar(255) NOT NULL DEFAULT '',
`fBody` text NOT NULL,
PRIMARY KEY (`fID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
have to have more then 1 record!
table tlanguages:
CREATE TABLE `tlanguages` (
`fID` int(11) NOT NULL AUTO_INCREMENT,
`fName` varchar(255) NOT NULL,
`fCode` varchar(10) NOT NULL,
PRIMARY KEY (`fID`),
KEY `ix_fName` (`fName`),
KEY `ix_fCode` (`fCode`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8
---
In versions 5.5.44 It was work correct!
Any other info, ask me.
Description: mysql --version mysql Ver 14.14 Distrib 5.5.46, for debian-linux-gnu (x86_64) using readline 6.3 uname -a Linux ip-10-85-36-161 3.13.0-44-generic #73-Ubuntu SMP Tue Dec 16 00:22:43 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux When i make select in SP (storedProcedure) SELECT ttemplates.`fID` , ttemplates.fsubject , ttemplates.fbody INTO `templateID`, `subject`, `body` FROM ttemplates INNER JOIN `tlanguages` ON (`tlanguages`.`fID` = ttemplates.flanguageID) WHERE `tlanguages`.`fCode` = `language` AND ttemplates.`ftype` = 'forgotPassword' ; I got bug result. table ttemplates have 2 records: "fID" "ftype" "flanguageID" "fSubject" "fBody" "1" "forgotPassword" "1" "Reset Password" "test1 " "2" "forgotPassword" "33" "Password Erinnerung" "test2" I got result: Reset Password, test2 It's mix of 1 and 2 rows!!! If i run only this select, i got correct result, one row. P.S. Any questions, you can contact my by mail. How to repeat: Add LIMIT 1. Or use previews version.