You have been subscribed for email updates to this bug report.
Description:
In MySQL 8.0 reference manual there is this example on page 2694:
CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
DECLARE xname VARCHAR(5) DEFAULT 'bob';
DECLARE newname VARCHAR(5);
DECLARE xid INT;
SELECT xname, id INTO newname, xid
FROM table1 WHERE xname = xname;
SELECT newname;
END;
For my database I created the following stored procedure:
DELIMITER //
CREATE PROCEDURE INSERT_INTO_WORKDAY(IN deviceName VARCHAR(16), IN cardUID VARCHAR(14))
BEGIN
DECLARE projectId INT;
DECLARE workerId INT;
DECLARE id INT;
SELECT MIN(ID) FROM PROJECT WHERE DEVICEID = (SELECT MIN(ID) FROM DEVICE WHERE NAME = deviceName) ORDER BY ID DESC LIMIT 1 INTO projectId;
SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID INTO workerId;
SELECT MIN(ID) FROM WORKDAY WHERE WORKERID = workerId AND PROJECTID = projectId AND STOPTIME IS NULL AND STARTTIME IS NOT NULL INTO id;
SELECT projectId;
SELECT workerId;
SELECT id;
IF (id >= 0)
THEN
UPDATE WORKDAY SET STOPTIME = now() WHERE ID = id;
ELSE
INSERT INTO WORKDAY (WORKERID, PROJECTID, STARTTIME) VALUES (workerId, projectId, now());
END IF;
END; //
DELIMITER ;
When I call the method, the local variables projectId, workerId and id are NULL even for non null returning SELECT statements.
I also tried:
SET projectId = (SELECT MIN(ID) FROM PROJECT WHERE DEVICEID = (SELECT MIN(ID) FROM DEVICE WHERE NAME = deviceName) ORDER BY ID DESC LIMIT 1);
SET workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);
SET id = (SELECT MIN(ID) FROM WORKDAY WHERE WORKERID = workerId AND PROJECTID = projectId AND STOPTIME IS NULL AND STARTTIME IS NOT NULL);
with same result.
It worked fine when I wrote:
SELECT MIN(ID) FROM PROJECT WHERE DEVICEID = (SELECT MIN(ID) FROM DEVICE WHERE NAME = deviceName) ORDER BY ID DESC LIMIT 1 INTO @projectId;
SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID INTO @workerId;
SELECT MIN(ID) FROM WORKDAY WHERE WORKERID = @workerId AND PROJECTID = @projectId AND STOPTIME IS NULL AND STARTTIME IS NOT NULL INTO @id;
How to repeat:
MySQL version:
mysql -V
mysql Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
My stored procedure:
DELIMITER //
CREATE PROCEDURE INSERT_INTO_WORKDAY(IN deviceName VARCHAR(16), IN cardUID VARCHAR(14))
BEGIN
DECLARE projectId INT;
DECLARE workerId INT;
DECLARE id INT;
SELECT MIN(ID) FROM PROJECT WHERE DEVICEID = (SELECT MIN(ID) FROM DEVICE WHERE NAME = deviceName) ORDER BY ID DESC LIMIT 1 INTO projectId;
SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID INTO workerId;
SELECT MIN(ID) FROM WORKDAY WHERE WORKERID = workerId AND PROJECTID = projectId AND STOPTIME IS NULL AND STARTTIME IS NOT NULL INTO id;
SELECT projectId;
SELECT workerId;
SELECT id;
IF (id >= 0)
THEN
UPDATE WORKDAY SET STOPTIME = now() WHERE ID = id;
ELSE
INSERT INTO WORKDAY (WORKERID, PROJECTID, STARTTIME) VALUES (workerId, projectId, now());
END IF;
END; //
DELIMITER ;
How to call my procedure:
CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "045D91B22C5E80");
CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "040D7FB22C5E81");
CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "043FA8B22C5E80");
CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "04D894B22C5E80");
CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "0405C6B22C5E81");
My database dump file:
CREATE DATABASE IF NOT EXISTS `testEccelC1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `testEccelC1`;
-- MySQL dump 10.13 Distrib 8.0.22, for Linux (x86_64)
--
-- Host: localhost Database: testEccelC1
-- ------------------------------------------------------
-- Server version 8.0.22-0ubuntu0.20.04.3
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `DEVICE`
--
DROP TABLE IF EXISTS `DEVICE`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `DEVICE` (
`ID` int NOT NULL AUTO_INCREMENT,
`NAME` varchar(30) NOT NULL,
`IP` varchar(15) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `NAME` (`NAME`,`IP`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `DEVICE`
--
LOCK TABLES `DEVICE` WRITE;
/*!40000 ALTER TABLE `DEVICE` DISABLE KEYS */;
INSERT INTO `DEVICE` VALUES (4,'Pepper_C1-1A5F30','192.168.0.40'),(1,'Pepper_C1-1A6318','192.168.0.10'),(2,'Pepper_C1-1A631D','192.168.0.20'),(3,'Pepper_C1-1A633D','192.168.0.30');
/*!40000 ALTER TABLE `DEVICE` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `POSITION`
--
DROP TABLE IF EXISTS `POSITION`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `POSITION` (
`ID` int NOT NULL AUTO_INCREMENT,
`POSITION` varchar(15) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `POSITION`
--
LOCK TABLES `POSITION` WRITE;
/*!40000 ALTER TABLE `POSITION` DISABLE KEYS */;
INSERT INTO `POSITION` VALUES (1,'electric'),(2,'mecanic'),(3,'software'),(4,'proiectare'),(5,'operational');
/*!40000 ALTER TABLE `POSITION` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `PROJECT`
--
DROP TABLE IF EXISTS `PROJECT`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `PROJECT` (
`ID` int NOT NULL AUTO_INCREMENT,
`GENUMBER` varchar(15) NOT NULL,
`RONUMBER` varchar(15) NOT NULL,
`DESCRIPTION` varchar(100) DEFAULT NULL,
`DEVICEID` int NOT NULL,
`ACTIVE` tinyint(1) DEFAULT '1',
PRIMARY KEY (`ID`),
KEY `DEVICEID` (`DEVICEID`),
CONSTRAINT `PROJECT_ibfk_1` FOREIGN KEY (`DEVICEID`) REFERENCES `DEVICE` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `PROJECT`
--
LOCK TABLES `PROJECT` WRITE;
/*!40000 ALTER TABLE `PROJECT` DISABLE KEYS */;
INSERT INTO `PROJECT` VALUES (1,'0','0','OPERATIONAL',1,1);
/*!40000 ALTER TABLE `PROJECT` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `WORKDAY`
--
DROP TABLE IF EXISTS `WORKDAY`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `WORKDAY` (
`ID` int NOT NULL AUTO_INCREMENT,
`WORKERID` int NOT NULL,
`PROJECTID` int NOT NULL,
`STARTTIME` timestamp NOT NULL,
`STOPTIME` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `WORKER` (`WORKERID`),
KEY `PROJECT` (`PROJECTID`),
CONSTRAINT `WORKDAY_ibfk_1` FOREIGN KEY (`WORKERID`) REFERENCES `WORKER` (`ID`),
CONSTRAINT `WORKDAY_ibfk_2` FOREIGN KEY (`PROJECTID`) REFERENCES `PROJECT` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `WORKDAY`
--
LOCK TABLES `WORKDAY` WRITE;
/*!40000 ALTER TABLE `WORKDAY` DISABLE KEYS */;
INSERT INTO `WORKDAY` VALUES (3,1,1,'2021-01-03 11:52:34','2021-01-03 14:03:51');
/*!40000 ALTER TABLE `WORKDAY` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `WORKER`
--
DROP TABLE IF EXISTS `WORKER`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `WORKER` (
`ID` int NOT NULL AUTO_INCREMENT,
`FIRSTNAME` varchar(15) NOT NULL,
`LASTNAME` varchar(20) NOT NULL,
`CARDNUMBER` varchar(14) NOT NULL,
`POSITIONID` int NOT NULL,
PRIMARY KEY (`ID`),
KEY `POSITIONID` (`POSITIONID`),
CONSTRAINT `WORKER_ibfk_1` FOREIGN KEY (`POSITIONID`) REFERENCES `POSITION` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `WORKER`
--
LOCK TABLES `WORKER` WRITE;
/*!40000 ALTER TABLE `WORKER` DISABLE KEYS */;
INSERT INTO `WORKER` VALUES (1,'Ionut Mihai','Popescu','045D91B22C5E80',1),(2,'Ilie','Zbagan','040D7FB22C5E81',2),(3,'Ilie','Ungureanu','043FA8B22C5E80',1),(4,'Adrian','Tehanciuc','04D894B22C5E80',2),(5,'Ioan','Bitoanca','0405C6B22C5E81',2);
/*!40000 ALTER TABLE `WORKER` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Dumping events for database 'testEccelC1'
--
--
-- Dumping routines for database 'testEccelC1'
--
/*!50003 DROP PROCEDURE IF EXISTS `INSERT_INTO_WORKDAY` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `INSERT_INTO_WORKDAY`(IN deviceName VARCHAR(16), IN cardUID VARCHAR(14))
BEGIN
DECLARE projectId INT;
DECLARE workerId INT;
DECLARE id INT;
SET projectId = (SELECT MIN(ID) FROM PROJECT WHERE DEVICEID = (SELECT MIN(ID) FROM DEVICE WHERE NAME = deviceName) ORDER BY ID DESC LIMIT 1);
SET workerId = (SELECT MIN(ID) FROM WORKER WHERE CARDNUMBER = cardUID);
SET id = (SELECT MIN(ID) FROM WORKDAY WHERE WORKERID = workerId AND PROJECTID = projectId AND STOPTIME IS NULL AND STARTTIME IS NOT NULL);
SELECT projectID;
SELECT workerId;
SELECT id;
IF (id >= 0)
THEN
UPDATE WORKDAY SET STOPTIME = now() WHERE ID = id;
ELSE
INSERT INTO WORKDAY (WORKERID, PROJECTID, STARTTIME) VALUES (workerId, projectId, now());
END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2021-01-05 6:22:01