You have been subscribed for email updates to this bug report.
Bug #102144 Can't assign value to local variables in store procedure.
Submitted: 5 Jan 2021 4:25 Modified: 6 Jan 2021 12:55
Reporter: Mihai Popescu Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:8.0.22 OS:Ubuntu (Ubuntu 20.04.1 LTS)
Assigned to: CPU Architecture:Any (Intel® Core™ i5-8300H CPU @ 2.30GHz × 8 )
Tags: local variable, stored procedure

[5 Jan 2021 4:25] Mihai Popescu
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
[6 Jan 2021 1:23] MySQL Verification Team
Your original test case having a column called ID and variable called id:

mysql 8.0.22 > CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "040D7FB22C5E81");
+-----------+
| projectID |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

That assuming both are not case sensitive. 

+----------+
| workerId |
+----------+
|     NULL |
+----------+
1 row in set (0.01 sec)

+------+
| id   |
+------+
| NULL |
+------+
1 row in set (0.01 sec)

ERROR 1048 (23000): Column 'WORKERID' cannot be null
mysql 8.0.22 > CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "043FA8B22C5E80");
+-----------+
| projectID |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

+----------+
| workerId |
+----------+
|     NULL |
+----------+
1 row in set (0.01 sec)

+------+
| id   |
+------+
| NULL |
+------+
1 row in set (0.01 sec)

ERROR 1048 (23000): Column 'WORKERID' cannot be null
mysql 8.0.22 >
-------------------------------------------------------------------------
Changing the variable name from id to Xid:

mysql 8.0.22 > CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "040D7FB22C5E81");
+-----------+
| projectID |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

+----------+
| workerId |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

+------+
| Xid  |
+------+
| NULL |
+------+
1 row in set (0.01 sec)

Query OK, 1 row affected (0.01 sec)

mysql 8.0.22 > CALL INSERT_INTO_WORKDAY("Pepper_C1-1A6318", "043FA8B22C5E80");
+-----------+
| projectID |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

+----------+
| workerId |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

+------+
| Xid  |
+------+
|  128 |
+------+
1 row in set (0.02 sec)

Query OK, 1 row affected (0.02 sec)

mysql 8.0.22 >
[6 Jan 2021 12:55] Mihai Popescu
Thank you! I apologise for not seeing that.