mysql error 'NULL' at line 1

Question

I got this error when tried to execute this:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

Can't seems to find what is the problem. Appreciate if anyone can help

SET @sql = NULL;

SELECT
  GROUP_CONCAT(
    DISTINCT CONCAT (
      "SUM(IF(DATE(FROM_UNIXTIME(machine_stop)) = '",
      DATE(FROM_UNIXTIME(machine_stop)),"' ,
      (machine_start-machine_stop)/3600, 0)) AS ",
      DATE(FROM_UNIXTIME(machine_stop))
    )
  ) INTO @sql
FROM
  downtime_data
WHERE
  DATE(FROM_UNIXTIME(machine_stop)) >= DATE(NOW()) - INTERVAL 7 DAY;

SET @sql = CONCAT("SELECT
                     failure_code, ", @sql, " 
                   FROM
                     downtime_data 
                   WHERE
                     p.machine='HH1' AND
                     DATE(FROM_UNIXTIME(machine_stop)) >= DATE(NOW()) - INTERVAL 7 DAY 
                   GROUP BY
                     failure_code,
                     DATE(FROM_UNIXTIME(machine_stop))"
                 );

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Answer 1

If by any chance the @sql variable still holds NULL value after the first select statement then you are going to encounter an exception later on while executing the prepare statement.

Look at the following select statement using CONCAT

SET @sql := NULL; SELECT CONCAT('abc',@sql,'def');

The result is NULL. Although you might expect the result to be abcdef.

In order to get abcdef you need to do this

SET @sql := NULL; SELECT CONCAT('abc',IFNULL(@sql,''),'def');

You may try any of the following if it resolves the issue:

Either

1) SET @sql := '';

OR

2) If you want to keep this line SET @sql = NULL; then change the portion of the final query like this SET @sql = CONCAT("SELECT failure_code ", IF(@sql IS NULL, '',CONCAT(',',@sql)),

Here's the final query:

SET @sql = CONCAT("SELECT
                     failure_code ", IF(@sql IS NULL, '',CONCAT(',',@sql)), " 
                   FROM
                     downtime_data 
                   WHERE
                     p.machine='HH1' AND
                     DATE(FROM_UNIXTIME(machine_stop)) >= DATE(NOW()) - INTERVAL 7 DAY 
                   GROUP BY
                     failure_code,
                     DATE(FROM_UNIXTIME(machine_stop))"
                 );

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;