Q: Why does comparison work incorrectly a stored procedure/function? I have a table that has an ID key column using binary(16) to store a GUID/UUID, the rest of the columns are varchar(45).
I have written a function that runs a select for ID given the other columns, and if ID is not found then creates a new one. My issue is that this function is returning an ID that does not match my data. If I just run the select portion of the function on the workbench it returns nothing as expected.
Here is my function:
CREATE DEFINER=`sa`@`192.100.100.%` FUNCTION `SelectReadingHeaders`(testType VARCHAR(45)
                                     , COL1 VARCHAR(45)
                                     , COL2 VARCHAR(45)
                                     , COL3 VARCHAR(45)
                                     , COL4 VARCHAR(45)
                                     , COL5 VARCHAR(45)
                                     , COL6 VARCHAR(45)
                                     , COL7 VARCHAR(45)
                                     , COL8 VARCHAR(45)
                                     , COL9 VARCHAR(45)
                                     , COL10 VARCHAR(45)
                                     , COL11 VARCHAR(45)
                                     , COL12 VARCHAR(45)
                                     , COL13 VARCHAR(45)
                                     , COL14 VARCHAR(45)
                                     , COL15 VARCHAR(45)
                                     , COL16 VARCHAR(45)) RETURNS binary(16)
BEGIN
    DECLARE columnID BINARY(16);
    SELECT `ID` FROM `ate`.`readings_columns`
        WHERE (`TYPE` <=> testType
            AND`COL1` <=> COL1
            AND `COL2` <=> COL2
            AND `COL3` <=> COL3
            AND `COL4` <=> COL4
            AND `COL5` <=> COL5
            AND `COL6` <=> COL6
            AND `COL7` <=> COL7
            AND `COL8` <=> COL8
            AND `COL9` <=> COL9
            AND `COL10` <=> COL10
            AND `COL11` <=> COL11
            AND `COL12` <=> COL12
            AND `COL13` <=> COL13
            AND `COL14` <=> COL14
            AND `COL15` <=> COL15
            AND `COL16` <=> COL16)
        LIMIT 1
        INTO columnID;
    IF columnID IS null THEN
        SET columnID = GuidToBinary(UUID());
        INSERT INTO `ate`.`readings_columns`
        VALUES (columnID
              , testType
              , COL1
              , COL2
              , COL3
              , COL4
              , COL5
              , COL6
              , COL7
              , COL8
              , COL9
              , COL10
              , COL11
              , COL12
              , COL13
              , COL14
              , COL15
              , COL16);
    END IF;
    RETURN columnID;
END

The next block is my test call that returns an erroneous result. This set of columns/test type does not exist in the table. The value returned is from a row with a matching TYPE column, as well as some of the null columns at the end, but is not a complete match. ToGuid is a function that makes the binary(16) guid human readable.:
SELECT ToGuid(SelectReadingHeaders('TCXOHunt','TEMPERATURE','ADC Ratio','AH0','AH1','AH2','BH0','BH1','BH2','INTERNALTEMPERATURE',null,null,null,null,null,null,null))

And yet if I type the select function into a new query tab it returns 0 results as expected:
SELECT ToGuid(`ID`) FROM `ate`.`readings_columns`
    WHERE `TYPE` <=> 'TCXOHunt'
        AND`COL1` <=> 'TEMPERATURE'
        AND `COL2` <=> 'ADC Ratio'
        AND `COL3` <=> 'AH0'
        AND `COL4` <=> 'AH1'
        AND `COL5` <=> 'AH2'
        AND `COL6` <=> 'BH0'
        AND `COL7` <=> 'BH1'
        AND `COL8` <=> 'BH2'
        AND `COL9` <=> 'INTERNALTEMPERATURE'
        AND `COL10` <=> null
        AND `COL11` <=> null
        AND `COL12` <=> null
        AND `COL13` <=> null
        AND `COL14` <=> null
        AND `COL15` <=> null
        AND `COL16` <=> null
    LIMIT 1


A: MySQL can't determine if you mean a column name or a parameter or variable name. Therefore when these have the same names MySQL tends to compare the column with itself when you write your procedure/function like that.
