Re: "create table as select ..." failed with unknown reason
OK, the cause is the IF() function in STMT_2.
I have IFNULL(t.queue, '') and IFNULL(t.port, ''); and IF(t.queue, t.queue, '') and IF(t.port, t.port, '') in the 1st and 2nd lines of STMT_2.
I changed the IF() in the 2nd line to IFNULL(), all works fine.
Here is the full text of STMT_4 which creates and populates the temporary table as expected:
create temporary table trunktrending as SELECT * FROM ( SELECT @ROWNUM := CASE WHEN @ROWKEY = CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IFNULL(t.queue, ''), '@', IFNULL(t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) THEN @ROWNUM + 1 ELSE 1 END AS rownum, @ROWKEY := CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IFNULL(t.queue, ''), '@', IFNULL(t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) as rowkey, t.base_ts, t.site, t.lag, t.port, t.queue, t.periodic_time, t.octets_forwarded, t.rate, t.octets_dropped FROM ( SELECT ((cast(c.time_recorded as date) + interval hour(c.time_recorded) hour) + interval (floor(((minute(c.time_recorded) + 7) / 15)) * 15) minute) AS base_ts, r.monitored_object_site_id AS site, r.lag_port AS lag, null AS port, null AS queue, AVG(c.periodic_time) AS periodic_time, SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) AS octets_forwarded, SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) / AVG(c.periodic_time) AS rate, SUM(COALESCE(c.in_profile_octets_dropped + c.out_of_profile_octets_dropped, c.octets_dropped)) AS octets_dropped FROM (service_combined_network_egress_octets r JOIN service_combined_network_egress_octets_periodic c ON ((r.id = c.id))) WHERE r.time_recorded >= STR_TO_DATE('2016-08-01', '%Y-%m-%d') AND DAYOFWEEK(r.time_recorded) IN (2,3,4,5,6) AND DATE_FORMAT(r.time_recorded, '%H:%i') BETWEEN '08:00' AND '20:00' AND (ISNULL(r.suspect) OR (r.suspect = FALSE)) GROUP BY base_ts, site, lag, port, queue ORDER BY site, lag, port, queue, DATE_FORMAT(base_ts, '%Y-%m-%d'), rate desc) AS t JOIN (SELECT @ROWNUM:=0, @ROWKEY:='') AS p) AS i WHERE rownum <= 10