MySQL: Error Code: 1292. Truncated incorrect DOUBLE value

Question

I'm trying to create a stored function which tests for the existence of multiple things in a database returning boolean statement of false if at least one exists and true if none exist.

Specifically there's multiple tables I'm joining namely: BurgerOrder, Patties, Buns.

The BurgerOrder table contains the PattyID (the meat in the burger) & the BunID (Type of bread). Note a burger can have 2 patties in it. Here's what I've tried.

DELIMITER //

CREATE FUNCTION PattyTest(BunCode INT)
RETURNS VARCHAR(5) DETERMINISTIC

BEGIN 
    DECLARE Result VARCHAR(5);

    IF BunCode NOT IN (SELECT B.ID 
                        FROM BurgerOrder BO JOIN Patties P JOIN Buns B 
                        ON B.ID = BO.BunID AND P.ID = B.PattyID 
                        WHERE B.Type = 'sourdough' OR P.Type = 'Grilled Chicken' or 'Beef') 
                        THEN SET Result = TRUE;

    ELSE SET Result = FALSE;

    END IF;

    RETURN Result;

END //

DELIMITER ;

There are couple things I'm not sure about. It returns VARCHAR(5) because I could not find a boolean data type. It currently returns 1s or 0s as true and false.

Secondly i believe this function seems to work just fine when I actually test it out, with one error.

Error Code: 1292. Truncated incorrect DOUBLE value: 'Beef'

I want the function to return True when either beef, chicken or sourdough bread are in any particular burger order I test. But I think what causes the issue is when a burger I enter has 2 beef patties inside. When I run the above function it works fine. It is only when I call the function (E.G. Select PattyTest(23)) is shows Error Code 1292.

Strangely SQL shows the error message once, then continues to work fine and return the correct results.

Any help is greatly appreciated!

Answer 1

You missed in the where clause a comparing column

Use this instead

DELIMITER //

CREATE FUNCTION PattyTest(BunCode INT)
RETURNS VARCHAR(5) DETERMINISTIC

BEGIN 
    DECLARE Result VARCHAR(5);

IF BunCode NOT IN (SELECT B.ID 
                    FROM BurgerOrder BO JOIN Patties P JOIN Buns B 
                    ON B.ID = BO.BunID AND P.ID = B.PattyID 
                    WHERE B.Type = 'sourdough' OR P.Type = 'Grilled Chicken' or P.Type = 'Beef') 
                    THEN SET Result = TRUE;

ELSE SET Result = FALSE;

END IF;

RETURN Result;

END //

DELIMITER ;