(SQL) Input validation doesn't work, input is set to INT and a condition is put (if input is decimal) but it still accepts non-integers (like 2.52)

I'm learning SLQ (in MySQL) at the moment and I was playing with parameter validation. I am building a procedure that returns customers with a specific id as input parameter (asks for the id and it returns all receipts of said customers).

I want to add 2 validation conditions, first for negative number (which works), and second for decimal numbers (since customer_id is always and intager).

I am confused why the procedure accepts decimal numbers even if I specified the data type as INT and why my added condition doesn't work either. If I give a decimal number as input (like 3.24) it returns the result as if the id was the whole part of the decimal number (returns for id=3).

Can anyody explain why this happens and how to build a working validation? Thank you!

My code

BEGIN
    IF 
        customer_id != TRUNCATE(customer_id,0) THEN SIGNAL SQLSTATE '58002' SET MESSAGE_TEXT = 'Invalid id code1';
    ELSEIF  customer_id<0 THEN SIGNAL SQLSTATE '58002' SET MESSAGE_TEXT = 'Invalid id code2';
    ELSE
            SELECT * FROM receipts r WHERE customer_id = r.customer_id; 
    
    END IF;
END

Solution

The way that parameters are handled depends on whether the server is in strict mode or not. Consider the following simple stored procedure -

CREATE PROCEDURE `test_sp`(IN customer_id INT)
BEGIN
    SELECT customer_id;
END

With the session running in non-strict mode -

SET @@session.sql_mode = sys.list_drop(@@session.sql_mode, 'STRICT_ALL_TABLES');
SET @@session.sql_mode = sys.list_drop(@@session.sql_mode, 'STRICT_TRANS_TABLES');

CALL test_sp(3.24); # outputs 3
CALL test_sp(3.54); # outputs 4
CALL test_sp(-3.24); # outputs -3
CALL test_sp(-3.54); # outputs -4

The decimal values passed into the sp are silently rounded and no errors or warnings are issued. If we do the same in strict mode we get exactly the same result with no errors or warnings raised -

SET @@session.sql_mode = sys.list_add(@@session.sql_mode, 'STRICT_ALL_TABLES');

CALL test_sp(3.24); # outputs 3
CALL test_sp(3.54); # outputs 4
CALL test_sp(-3.24); # outputs -3
CALL test_sp(-3.54); # outputs -4

So, regardless of whether we are in strict mode or not decimal values are always rounded to give a valid integer input.

We get slightly different results if we define our sp like this -

CREATE PROCEDURE `test_sp`(IN customer_id INT UNSIGNED)
BEGIN
    SELECT customer_id;
END

With the same four calls in non-strict mode we get -

SET @@session.sql_mode = sys.list_drop(@@session.sql_mode, 'STRICT_ALL_TABLES');
SET @@session.sql_mode = sys.list_drop(@@session.sql_mode, 'STRICT_TRANS_TABLES');

CALL test_sp(3.24); # outputs 3
CALL test_sp(3.54); # outputs 4
CALL test_sp(-3.24); # outputs 0
CALL test_sp(-3.54); # outputs 0

So the negative values get coerced to 0 and if we check, a warning is raised -

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'customer_id' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)

Now, doing the same again but in strict mode we get a different outcome -

SET @@session.sql_mode = sys.list_add(@@session.sql_mode, 'STRICT_ALL_TABLES');

CALL test_sp(3.24); # outputs 3
CALL test_sp(3.54); # outputs 4
CALL test_sp(-3.24); # Error Code: 1264. Out of range value for column 'customer_id' at row 1
CALL test_sp(-3.54); # Error Code: 1264. Out of range value for column 'customer_id' at row 1

So, your attempt to check for decimal input fails because the value has already been rounded to an integer before it gets to your code.