Stored FUNCTION is not returning result in MySql?

Question

I have a simple MySql query which successfully returns single value.

select tl.tour_log_id 
from tour_log tl
WHERE tl.log = "SUBTOUR_START" 
      AND tl.inquiry_id = 7519618
     and tl.truck_id = 450 and tl.tour_id = 6174
 limit 1; -- tour_log_id -> 736318. Even without limit 1, query gives always single value. This is how database is structured.

However, I do have a Mysql Stored Function, which is supposed to do the same thing, but I'm getting null. I generated this function by doing Right Click on Functions -> Create Function.

CREATE DEFINER=`root`@`localhost` FUNCTION `getTourLogIdForSubtourStart`( 
     inquiryId int, truckId int, tourId int) RETURNS int
        DETERMINISTIC
    BEGIN
        DECLARE tourLogIdSubtourStart int; 
        DECLARE tourLogIdSubtourEnd int;  
        
        select tour_log.tour_log_id into tourLogIdSubtourStart
        from fleaty.tour_log tl
        WHERE tl.log = "SUBTOUR_START" 
              AND tl.inquiry_id = inquiryId
             and tl.truck_id = truckId and tl.tour_id = tourId
         limit 1;    -- 
    
        -- set tourLogIdSubtourEnd = callSomeOtherFunction(tourLogIdSubtourStart, inquiryId, truckId);
         
        -- here will be cursor to process some result set, based on tourLogIdSubtourStart and tourLogIdSubtourEnd
    
        
        
    RETURN  (tourLogIdSubtourStart);
    END

This is how I call above function:

set @s = getTourLogIdForSubtourStart(7519618, 450, 6174);

select @s;

This prints null. Why?

Answer 1

Quite simply put never use column names as variable names

CREATE tABLE tour_log (tour_log_id int, log varchar(19),inquiry_id BIGint,truck_id int, tour_id int)

INSERT INTO tour_log VALUEs (1,'SUBTOUR_START',7519618, 450, 6174)

  CREATE  FUNCTION `getTourLogIdForSubtourStart`( 
     _inquiryId int, _truckId int, _tourId int) RETURNS int
        DETERMINISTIC
    BEGIN
        DECLARE tourLogIdSubtourStart int; 
        DECLARE tourLogIdSubtourEnd int;  
        
        select tour_log_id into tourLogIdSubtourStart
        from tour_log tl
        WHERE tl.log = "SUBTOUR_START" 
              AND tl.inquiry_id = _inquiryId
             and tl.truck_id = _truckId and tl.tour_id = _tourId
         limit 1;    -- 
    
        -- set tourLogIdSubtourEnd = callSomeOtherFunction(tourLogIdSubtourStart, inquiryId, truckId);
         
        -- here will be cursor to process some result set, based on tourLogIdSubtourStart and tourLogIdSubtourEnd
    
        
        
    RETURN  (tourLogIdSubtourStart);
    END

set @s = getTourLogIdForSubtourStart(7519618, 450, 6174);

select @s;

✓

| @s |
| -: |
|  1 |

SELECT * FROM tour_log

tour_log_id | log           | inquiry_id | truck_id | tour_id
----------: | :------------ | ---------: | -------: | ------:
          1 | SUBTOUR_START |    7519618 |      450 |    6174

db<>fiddle here