MySQL error "Truncated incorrect DOUBLE value" in procedure

Question

I have problem with my procedure. I try to take values from sales table and make a query using them. Procedure looks like this:

DROP PROCEDURE IF EXISTS turnover;

DELIMITER $$

CREATE PROCEDURE turnover()
BEGIN
    DECLARE col INT;
    DECLARE q TEXT;
    DECLARE i INT DEFAULT 0;
    DECLARE m TEXT;
    SET col = (SELECT count(DISTINCT article) FROM sales);
    SET q = "SELECT article, ";
    WHILE i < co DO
        SET m = (SELECT DISTINCT month FROM sales LIMIT 1 OFFSET i);
        SET q = q + "SUM(IF(month=" + m + ",value,NULL)) AS " + m;
        IF i < (col - 1) THEN
            SET q = q + ", ";
        END IF;
        SET i = i + 1;
    END WHILE;
    SET q = q + " FROM sales GROUP BY article";
    EXECUTE q;
END$$

DELIMITER ;

CALL turnover();

I receive error:

Error Code: 1292. Truncated incorrect DOUBLE value: ',value,NULL)) AS '

How i can make it works?

Thanks.

Answer 1

The col issue was fixed or assumed in the below.

CREATE SCHEMA safe_Tuesday_01; -- safe sandbox
USE safe_Tuesday_01;    -- DO the work in this db to test it

-- a fake table, we need something
create table sales 
(   article varchar (100) not null,
    month int not null
);

Step 1, find out what the string looks like:

DROP PROCEDURE IF EXISTS turnover;
DELIMITER $$
CREATE PROCEDURE turnover()
BEGIN
    DECLARE col INT;
    DECLARE q TEXT;
    DECLARE i INT DEFAULT 0;
    DECLARE m TEXT;
    SET col = (SELECT count(DISTINCT article) FROM sales);
    SET q = "SELECT article, ";
    WHILE i < col DO
        SET m = (SELECT DISTINCT month FROM sales LIMIT 1 OFFSET i);
        SET q = CONCAT(q,"SUM(IF(month=" + m + ",value,NULL)) AS ", m);
        IF i < (col - 1) THEN
            SET q = q + ", ";
        END IF;
        SET i = i + 1;
    END WHILE;
    SET q = CONCAT(q," FROM sales GROUP BY article");
    select q;
    -- EXECUTE q; -- No no no this is wrong anyway
END$$
DELIMITER ;

CALL turnover();

SELECT article, FROM sales GROUP BY article

Well that above SELECT does not look so hot. Repeatedly fix your logic in Step 1 to fix that string.

Step 2, when you fix the code above, plop in the below. Note, at the moment, it is not fixed. So do that, again, above.

But in the below, use a proper PREPARED STATEMENT which you are not.

DROP PROCEDURE IF EXISTS turnover;
DELIMITER $$
CREATE PROCEDURE turnover()
BEGIN
    DECLARE col INT;
    DECLARE q TEXT;
    DECLARE i INT DEFAULT 0;
    DECLARE m TEXT;
    SET col = (SELECT count(DISTINCT article) FROM sales);
    SET q = "SELECT article, ";
    WHILE i < col DO
        SET m = (SELECT DISTINCT month FROM sales LIMIT 1 OFFSET i);
        SET q = CONCAT(q,"SUM(IF(month=" + m + ",value,NULL)) AS ", m);
        IF i < (col - 1) THEN
            SET q = q + ", ";
        END IF;
        SET i = i + 1;
    END WHILE;
    SET q = CONCAT(q," FROM sales GROUP BY article");
    -- select q;
    SET @theSQL=q;
    PREPARE stmt1 FROM @theSQL;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;

When done,

DROP SCHEMA safe_Tuesday_01; -- clean up, poof, sandbox is gone

CONCAT is your friend. You missed this step. It is important that the PREPARE works against a User Variable (with an @ sign) and not a Local Var (from a DECLARE) else it will blow up. So I fixed that above with the @theSQL

Again, see the MySQL Manual Page PREPARE Syntax. It is important to get your string right. That is the point of Step 1. Only then do you move on to Step 2 and using it.

Answer 2

It happens when SELECT DISTINCT month FROM sales doesn't return anything. On the next line, the query fragment is generated as SUM(IF(month=,value,NULL)) AS and, of course, there is an error there (maybe MySQL doesn't produce the correct error message, but this is where the error is).

And the cause of the error is the WHILE line that compares i against the unknown variable co. It should probably read:

WHILE i < col DO

But it doesn't fix the problem because col is the number of distinct values of article and you iterate from 1 to col over the distinct values of month. Most probably they are in different amounts and if the number of articles is bigger then the error will happen again.