MySQL Forums
Forum List  »  InnoDB

Re: "create table as select ..." failed with unknown reason
Posted by: harry sheng
Date: December 20, 2016 09:47AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: "create table as select ..." failed with unknown reason
743
December 20, 2016 09:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.