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;

• Ankit Agrawal: change null to is null
• Wahsei: Same problem#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 'is NULL' at line 1
• 1000111: Print the @sql variable after the first select statement. There's a possibility if the @sql variable is still NULL after the first select statement then you will get an exception while executing stmt.
• 1000111: Or try to change in the main query like this: SET @sql = CONCAT("SELECT failure_code, ", IFNULL(@sql,''), ...."
• wchiquito: Print SELECT @`sql`;.

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;

• Wahsei: I think the error came from this PREPARE stmt FROM @sql; MySQL said: Documentation #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
• 1000111: Execute this lines and you will get the same. SET @sql := NULL; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;. That's what I've pointed out in my answer. By the way, have you tried any of the change mentioned in my answer?
• wchiquito: SET @`sql` := CONCAT('SELECT failure_code', IF(@`sql` IS NOT NULL, CONCAT(', ', @`sql`), ''))
• 1000111: That's why I told you to use IFNULL. Probably I failed to make you understand.
• wchiquito: SELECT failure_code, FROM downtime_data != SELECT failure_code FROM downtime_data

Answer 2

I got it, there are no row from the query under this condition

DATE(FROM_UNIXTIME(machine_stop)) >= DATE(NOW()) - INTERVAL 7 DAY

So it returned NULL

Answer 3

I was facing this issue but let me share with you unique solution which I found out, I was working on 2 stored procedures simultaneously, on a new session if I run 1st sp I face this error but when I run 1st sp after running 2nd sp then the error is gone and I get my result.

It is something to do with mysql session variables which was getting set in 2nd sp and working for 1st one as well.