MYSQL Truncated incorrect INTEGER value error

Question

I am running a query and getting the mysql error 1292: "Truncated incorrect INTEGER value" It is a warning and my select works fine, but I would still like to clear up the warnings nonetheless.

I can confirm that the warning occurs everytime the function finds tags linked to the object. When no tags are found, the warning does not occur. So if 50 out of 1000 objects have tags, I will get 50 warnings, like this:

Truncated incorrect INTEGER value: '1|Blondes'

The database function used is:

DELIMITER $$

DROP FUNCTION IF EXISTS `fnObjectTagGetObjectTags` $$
CREATE DEFINER=`root`@`%` FUNCTION `fnObjectTagGetObjectTags`(_objectType int, _objectId bigint) RETURNS varchar(2048) CHARSET utf8
BEGIN

  DECLARE _outObjectTags VARCHAR(2048);

  SET _outObjectTags =
    (
      SELECT (CAST(GROUP_CONCAT(CONCAT(tagId, '|', tagName) separator '~') AS CHAR(10000) CHARACTER SET utf8)) AS objectTagList
      FROM
      (

      SELECT tagId, tagName
        FROM objectTag
        INNER JOIN tag
          ON tagId = objectTagTagId
        WHERE objectTagObjectType = _objectType
          AND objectTagObjectId = _objectId
          AND objectTagIsDisabled = 0
          AND objectTagIsActive = 1
          AND tagIsDisabled = 0
          AND tagIsActive = 1
      ) as subQuery
    );

  RETURN _outObjectTags;

END $$

DELIMITER ;

And the calling query is simply:

SELECT fnObjectTagGetObjectTags(3, album.albumId)
FROM album
WHERE fnObjectTagGetObjectTags(3, album.albumId) IS NOT NULL
AND albumIsDisabled = 0
AND albumIsActive = 1

I just can't figure out why it is doing this. Anyone see anything odd? I am running 5.5.13 Thanks

If `TagId` (`tag.TagId`, I assume) is INTEGER, is `objectTagTagId` INTEGER too? Or vice versa. And same about other pairs of values you are comparing in the WHERE clause (in the function). — Andriy M, Aug 25 '11 at 19:28
@Andriy M: Yeah - all columns used in the WHERE clause are the same type. — Paolo Broccardo, Aug 26 '11 at 06:12

score 2 · Accepted Answer · answered Aug 26 '11 at 17:45

2

Try an explicit cast of just the tagId as a character before the concatenation, since you may be mixing binary and non-binary strings. Like

SELECT (CAST(GROUP_CONCAT(CONCAT(CAST(tagId AS CHAR), '|', tagName) separator '~') AS CHAR(10000) CHARACTER SET utf8))

answered Aug 26 '11 at 17:45

Poodlehat

360
1
2
9

Ok, so adding the cast didn't work. But then after some fiddling it turns out that the query calling this function needed a cast, i.e. `CAST(fnObjectTagGetObjectTags(2, profile.profileId) AS CHAR) AS objectTags` Thanks for sending me in the right direction. – Paolo Broccardo Aug 27 '11 at 06:03

MYSQL Truncated incorrect INTEGER value error

1 Answers1

Linked