MySQL: Truncated incorrect DOUBLE value: '4,2'

Question

I'm having issues with the below, I've looked through some of the similar questions but haven't gotten to a full understanding of what is going on and how to resolve it.

'4,2' is a comma seperated string, it could as easily be '4,2,7,19'.

SELECT FN_ADMIN_EDIT_ARTICLE (
    123
    , '4,2'
);

It throws:

Error Code: 1292. Truncated incorrect DOUBLE value: '4,2'

I've tried to cast the input as an NCHAR but it does't seem to help.

SELECT FN_ADMIN_EDIT_ARTICLE (
    123
    , CAST('4,2' AS NCHAR)
);

Below is what FN_ADMIN_EDIT_ARTICLE is doing.

CREATE FUNCTION `FN_ADMIN_EDIT_ARTICLE`(
    `id` INT(11)
    , `tags` VARCHAR(250)
) RETURNS int
BEGIN
    DECLARE `tag` TEXT DEFAULT '';
    DECLARE i INT DEFAULT 1;
    
    /* Remove Old Tags */
    DELETE FROM `td_article_tag`
    WHERE `ata_art_id` = `id`
    AND `ata_tag_id` NOT IN (`tags`);
    
    IF `tags` <> "" THEN
        /* Add New Tags */
        `tag_loop`: LOOP
            SET `tag` = FN_SPLIT_STR(`tags`, ",", i);
            SET i = i + 1;
            
            /* Exit loop if no more tags */
            IF `tag` = '' THEN
                LEAVE `tag_loop`;
            END IF;

            INSERT INTO `td_article_tag` (
                `ata_art_id`
                , `ata_tag_id`
            ) VALUES (
                `id`
                , `tag`
            )
            ON DUPLICATE KEY UPDATE `ata_tag_id` = `tag`;
        END LOOP;
    END IF;
    
    RETURN `id`;
END

FN_SPLIT_STR

CREATE FUNCTION FN_SPLIT_STR (`value` TEXT, delim VARCHAR(12), pos INT)
RETURNS TEXT
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(`value`, delim, pos), LENGTH(SUBSTRING_INDEX(`value`, delim, pos -1)) + 1), delim, '');

If you want that string to be convertible to a double, it should be `'4.2'`, not `'4,2'`, or better yet just use literal `4.2` instead of a string. In any case, you need to provide a [mre] that includes the definition of `FN_ADMIN_EDIT_ARTICLE`, including the definition of the parameters, and the tables getting modified. — Mark Rotteveel, Apr 27 '22 at 10:51
I'm surprised your cast didn't throw a syntax error since there is no nchar datatype in mysql — P.Salmon, Apr 27 '22 at 10:54
@MarkRotteveel I don't want it as a double, it's a comma separated string being passed into the function. — llanato, Apr 27 '22 at 10:55
Then I guess that `ata_tag_id` is an integer or other numeric type, and that causes the `NOT IN` to coerce `tags` to a numeric type. You cannot use `NOT IN` to check inside a string, it checks the individual values in the `IN` list, and you have a singular value, `tags`. — Mark Rotteveel, Apr 27 '22 at 10:58
Consider looking at [MySQL query finding values in a comma separated string](https://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string) — Mark Rotteveel, Apr 27 '22 at 10:59
@P.Salmon MySQL supports NCHAR. https://dbfiddle.uk/?rdbms=mysql_8.0&rdbms2=mysql_5.5&fiddle=734083e76e5104d8f4fcbd1e76700bc3 https://dev.mysql.com/doc/refman/8.0/en/charset-national.html — Akina, Apr 27 '22 at 11:10
Could you please show the td_article_tag table column types? — miciry89, Apr 27 '22 at 11:14
I cannot reproduce your issue. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=db25a71da109f72efdd0c3fb2ad4fbc4 — P.Salmon, Apr 27 '22 at 11:19

MySQL: Truncated incorrect DOUBLE value: '4,2'

0 Answers0