vendredi 29 janvier 2021

T-SQL unexpected behavior

I am collecting telemetry messages into a SQL trigger, which used to work fine for several weeks (about 5 millions events ingested correctly), and I am experiencing unexpected behavior since a couple days, even though I did not modify this particular function.

The function decodes a raw payload to extract GPS coordinates, and is as such:

ALTER FUNCTION [getLongitude](@Firmware varchar(30), @RawData varchar(24))
    RETURNS float
    AS
    BEGIN
        DECLARE @EventID varchar(1),
                @Longitude float;

        SET @EventID = SUBSTRING(@RawData, 1, 1);
        IF @Firmware = 'V1'
            BEGIN
                IF @EventID = '1'
                    BEGIN
                        IF CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) < 8388608
                            BEGIN
                                SET @Longitude = CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) * 215.0 / 10000000;
                            END
                        ELSE
                            BEGIN
                                SET @Longitude = -1 * (16777216 - CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int)) * 215.0 / 10000000;
                            END
                    END
                ELSE SET @Longitude = 100; -- other events do not include GPS information
            END
        ELSE
            BEGIN
                IF @Firmware = 'V2'
                    BEGIN
                        IF @EventID = '1' OR @EventID = '2'
                            BEGIN
                                IF CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) < 8388608
                                    BEGIN
                                        SET @Longitude = CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) * 215.0 / 10000000;
                                    END
                                ELSE
                                    BEGIN
                                        SET @Longitude = -1 * (16777216 - CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int)) * 215.0 / 10000000;
                                    END
                            END             
                        ELSE SET @Longitude = 100; -- other events do not include GPS information
                    END
                ELSE SET @Longitude = 100; -- other firmwares are unknown
            END
        RETURN @Longitude;
    END;

For a specific device which has firmware V1, the function returns 100 as a value for the longitude. I have included "SELECT" lines to make sure the conditions were correctly applied, and this is where I do not understand the output:

DECLARE 
                @RawData varchar(24),
                @Firmware varchar(30),
                @EventID varchar(1),
                @Longitude float;
        
        SET @RawData = '1a330000003437cd3bfe95a7';
        SET @Firmware = 'V1';
        SET @EventID = SUBSTRING(@RawData, 1, 1);
        IF @Firmware = 'V1'
            BEGIN
                SELECT 'Cond1'
                IF @EventID = '1'
                    BEGIN
                        SELECT 'Cond 1.1';
                        IF CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) < 8388608
                            BEGIN
                            select 'Cond 1.1.1';
                                SET @Longitude = CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) * 215.0 / 10000000;
                            END
                        ELSE
                            BEGIN
                            SELECT 'Cond 1.1.2';
                                SET @Longitude = -1 * (16777216 - CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int)) * 215.0 / 10000000;
                                SELECT @Longitude
                            END
                    END
                ELSE 
                    SELECT 'Cond 1.2'
                    SET @Longitude = 100;
            END
        ELSE
            BEGIN
            SELECT 'Cond 2'
                IF @Firmware = 'V2'
                    BEGIN
                        IF @EventID = '1' OR @EventID = '2'
                            BEGIN
                                IF CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) < 8388608
                                    BEGIN
                                        SET @Longitude = CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) * 215.0 / 10000000;
                                    END
                                ELSE
                                    BEGIN
                                        SET @Longitude = -1 * (16777216 - CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int)) * 215.0 / 10000000;
                                    END
                            END             
                        ELSE
                            SELECT 'Cond 2.1'
                            SET @Longitude = 100;
                    END
                ELSE
                    SELECT 'Cond 2.2'
                    SET @Longitude = 100;
            SELECT 'Here?'
            END
            SELECT @Longitude

My outputs are:

Cond 1
Cond 1.1
Cond 1.1.2
-1,9943615 -- the correct value that I expect as function output
100

How come the longitude is finally set to 100 while neither Cond 1.2, Cond 2.1 nor Cond 2.2 are displayed?

I forgot to mention that fct.hexstrtovarbin is function that changes an hexadecimal string to a varbinary:

CREATE FUNCTION fct.hexstrtovarbin(@input varchar(8000)) RETURNS varbinary(8000)
AS
BEGIN
DECLARE @Result AS varbinary(8000)

IF LEN(@input) % 2 <> 0 
    BEGIN 
        SET @Result = 0000000000;
    END 
ELSE
    BEGIN
        SET @Result = CONVERT(VARBINARY(8000), @input, 2);
    END
    RETURN @Result
END;

Aucun commentaire:

Enregistrer un commentaire