Lost connection to MySQL server during query error when I try to use function

I am trying to make a function which checks if any date between say Date-range A and B lies between the Date-range C and D. Whenever I try running the function MySQL Workbench always throws an Error Code:2013 Lost connection to MySQL Server during query. I am assuming this error is arising due to the loop running forever (please correct me If I’m thinking about it completely wrong).

Table Code :

CREATE TABLE TESTING(
LeaveStart DATE,
LeaveEnd DATE,
ProjectStart DATE,
ProjectEnd DATE,
Det BOOL,
ID INT);

Value Insertion Code :

INSERT INTO TESTING VALUES('2018-01-01','2018-01-31','2018-01-10','2018-01-30',FALSE,1);
INSERT INTO TESTING VALUES('2018-01-01','2018-01-31','2018-01-10','2018-01-30',FALSE,2);

Function :

DELIMITER //
CREATE FUNCTION SALARY_DEDUCTION (LeaveStart Date, LeaveEnd Date, ProjectStart DATE, ProjectEnd Date)
RETURNS BOOL NO SQL
DETERMINISTIC 
BEGIN
    DECLARE DET BOOL DEFAULT FALSE;
    DECLARE DT DATE DEFAULT LeaveStart;
    WHILE LeaveStart < LeaveEnd DO
    IF DT BETWEEN ProjectStart AND ProjectEnd THEN
    SET DET = TRUE;
    ELSE 
    SET DET = FALSE;
    SET DT = DATE_ADD(DT, INTERVAL 1 DAY);
    END IF;
    END WHILE;
    RETURN DET;
END ; //

DELIMITER ;

Calling/Driver Code:

UPDATE TESTING AS T
SET 
T.Det = SALARY_DEDUCTION(T.LeaveStart,T.LeaveEnd,T.ProjectStart,T.ProjectEnd)
WHERE 
T.ID=1;

Note: The driver code might throw an error code 1175: safe update, it can be disabled using SET SQL_SAFE_UPDATES=0 before the driver code. If someone could also explain why it throws the error that’ll be great.

DB-Fiddle: https://www.db-fiddle.com/f/4Bof1gdHRnkSYJwVZekRJ9/1

EDIT:

DELIMITER //
CREATE FUNCTION SALARY_DEDUCTION_3(LeaveStart Date, LeaveEnd Date, ProjectStart DATE, ProjectEnd Date)
RETURNS BOOL NO SQL
DETERMINISTIC 
BEGIN
    DECLARE DET BOOL DEFAULT FALSE;
    DECLARE CD DATE DEFAULT LeaveStart;
    myloop: WHILE CD < LeaveEnd DO
    IF CD BETWEEN ProjectStart AND ProjectEnd THEN
    SET DET = TRUE;
    LEAVE myloop;
    END IF;
    SET CD = DATE_ADD(CD, INTERVAL 1 DAY);
    END WHILE myloop;
    RETURN DET;
END ; //

DELIMITER ;

Answer

Your code creates an endless loop (LeaveStart < LeaveEnd is always true).

Also, take a look at the code if you need the loop at all. As it currently stands, you do not need it as the value of the DET will be determined from the last loop round. All other rounds mean nothing.

You could simply do:

UPDATE TESTING
SET Det = (LeaveStart<ProjectEnd AND LeaveEnd>=ProjectStart AND LeaveStart<LeaveEnd)
WHERE ID=1;

Advertisement

Answer