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;

Stability

change null to is null – Rumen

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 – Stability

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. – Zhang

Or try to change in the main query like this: SET @sql = CONCAT("SELECT failure_code, ", IFNULL(@sql,''), ...." – Zhang

Remove the SET @SQL = NULL entirely. There's no path through the rest of the code where @SQL will not be assigned a new value. If you must initialize it to clear old content, simply set it to an empty string. – Lander

how to print @sql variable ? – Stability

Print SELECT @`sql`;. – Leonidaleonidas

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;

Zhang

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 – Stability

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? – Zhang

SET @`sql` := CONCAT('SELECT failure_code', IF(@`sql` IS NOT NULL, CONCAT(', ', @`sql`), '')) – Leonidaleonidas

That's why I told you to use IFNULL. Probably I failed to make you understand. – Zhang

SELECT failure_code, FROM downtime_data != SELECT failure_code FROM downtime_data – Leonidaleonidas

Thanks @wchiquito. Got the important part out of sight. Honestly I didn't notice it's you commented there. I thought the PO. Sorry mate! – Zhang

@Stability Please check the query now. I've made a correction there. – Zhang

Remember to remove the comma (,) after the column name failure code. – Leonidaleonidas

The comma (,) is removed now @Wahsei. Thanks again @Leonidaleonidas ! – Zhang

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

Stability

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.

Kaylil