Hello,

   please enable me to run my SQL query in wpDataTables. Stored procedures can not, and this query throws a bug. The SQL query in Heidi and phpMyAdmin works OK.

SQL Query:

with     trn as (SELECT td.datetime,
            MAX(IF(td.id_pen_name=10,td.value, NULL)) AS pen1,
            MAX(IF(td.id_pen_name=11,td.value, NULL)) AS pen2
        FROM trend_data AS td
        WHERE (
            td.id_pen_name=10 OR
            td.id_pen_name=11
            ) AND (td.datetime BETWEEN '2017-02-12 01:00:29' AND '2017-03-03 01:00:29')
        GROUP BY td.datetime),
    time_table as (SELECT (@datecounter:=DATE_ADD(@datecounter, interval 30 second)) as datetime
        FROM trend_data as td,            (SELECT @datecounter := DATE_ADD('2017-02-12 01:00:29', interval -30 second)) as r
       WHERE @datecounter<'2017-03-03 01:00:29'),
    all_table as (SELECT tt.datetime,
    (@prev1 := coalesce(trn.pen1, @prev1)) as pen1,
    (@prev2 := coalesce(trn.pen2, @prev2)) as pen2
    FROM time_table AS tt
    LEFT OUTER JOIN trn ON trn.datetime=tt.datetime
    JOIN (SELECT @prev1:=(SELECT value FROM trend_data AS td where td.id_pen_name=10 and td.datetime<='2017-02-12 01:00:29' ORDER BY td.datetime DESC LIMIT 1)) as x1
    JOIN (SELECT @prev2:=(SELECT value FROM trend_data AS td where td.id_pen_name=11 and td.datetime<='2017-02-12 01:00:29' ORDER BY td.datetime DESC LIMIT 1)) as x2)
-- SELECT * from all_table
SELECT FROM_UNIXTIME((UNIX_TIMESTAMP(tm.datetime) div 3600)*3600+3600) as resample_time,
    (CASE 0 WHEN 0 THEN round(AVG(tm.pen1),3) WHEN 1 THEN round(MIN(tm.pen1),3) WHEN 2 THEN round(MAX(tm.pen1),3) WHEN 3 THEN round((tm.pen1),3) ELSE NULL END) as pen1,
    (CASE 0 WHEN 0 THEN round(AVG(tm.pen2),3) WHEN 1 THEN round(MIN(tm.pen2),3) WHEN 2 THEN round(MAX(tm.pen2),3) WHEN 3 THEN round((tm.pen2),3) ELSE NULL END) as pen2
    FROM all_table AS tm
    GROUP BY resample_time;