MySQL subquery
Environment: Server version: 5.7.10-log MySQL Community Server (GPL)
I've listed the three queries below.
The first query is the subquery of the 2nd and 3rd queries.
The difference of the 2nd and 3rd queries is the column name used in the main query. In the 2nd query, column name tsmark is used in both the main and sub query. In the 3rd query, column name tsmark2 is used in the main query.
I expected the 2nd and 3rd queries return the same result set. But they don't.
mysql> SELECT ((cast(c.time_recorded as date) + interval hour(c.time_recorded) hour) + interval (floor(((minute(c.time_recorded) + 7) / 15)) * 15) minute) AS tsmark,
-> r.monitored_object_site_id AS site, r.lag_port AS lag, r.port_id AS port, r.queue_id AS queue,
-> (c.periodic_time) AS periodic_time,
-> (c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded) AS octets_forwarded
-> FROM (service_combined_network_ingress_octets r JOIN service_combined_network_ingress_octets_periodic c ON ((r.id = c.id)))
-> WHERE r.time_recorded BETWEEN str_to_date('2016-09-01 00:00:00', '%Y-%m-%d %H:%i:%s') AND str_to_date('2016-09-01 01:00:00', '%Y-%m-%d %H:%i:%s')
-> AND r.monitored_object_site_id IN ('10.1.1.105') AND r.port_id='1/1/1' AND r.queue_id = 1 AND (ISNULL(r.suspect) OR (r.suspect = FALSE))
-> ;
+---------------------+------------+------+-------+-------+---------------+------------------+
| tsmark | site | lag | port | queue | periodic_time | octets_forwarded |
+---------------------+------------+------+-------+-------+---------------+------------------+
| 2016-09-01 00:00:00 | 10.1.1.105 | NULL | 1/1/1 | 1 | 900 | 353003400 |
| 2016-09-01 00:15:00 | 10.1.1.105 | NULL | 1/1/1 | 1 | 900 | 103156200 |
| 2016-09-01 00:30:00 | 10.1.1.105 | NULL | 1/1/1 | 1 | 900 | 250512300 |
| 2016-09-01 00:45:00 | 10.1.1.105 | NULL | 1/1/1 | 1 | 900 | 324279900 |
| 2016-09-01 01:00:00 | 10.1.1.105 | NULL | 1/1/1 | 1 | 900 | 282281400 |
+---------------------+------------+------+-------+-------+---------------+------------------+
5 rows in set (2.11 sec)
mysql> SELECT DATE_FORMAT(t.tsmark, '%Y-%m-01') AS tsmark, t.site, t.lag, t.port, t.queue, -> FLOOR(SUM(t.periodic_time)) AS periodic_time,
-> SUM(t.octets_forwarded) AS octets_forwarded
-> 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 tsmark,
-> r.monitored_object_site_id AS site, r.lag_port AS lag, r.port_id AS port, r.queue_id AS queue,
-> (c.periodic_time) AS periodic_time,
-> (c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded) AS octets_forwarded
-> FROM (service_combined_network_ingress_octets r JOIN service_combined_network_ingress_octets_periodic c ON ((r.id = c.id)))
-> WHERE r.time_recorded BETWEEN str_to_date('2016-09-01 00:00:00', '%Y-%m-%d %H:%i:%s') AND str_to_date('2016-09-01 01:00:00', '%Y-%m-%d %H:%i:%s')
-> AND r.monitored_object_site_id IN ('10.1.1.105') AND r.port_id='1/1/1' AND r.queue_id = 1 AND (ISNULL(r.suspect) OR (r.suspect = FALSE))
-> ) AS t
-> GROUP BY tsmark, site, lag, port, queue;
+------------+------------+------+-------+-------+---------------+------------------+
| tsmark | site | lag | port | queue | periodic_time | octets_forwarded |
+------------+------------+------+-------+-------+---------------+------------------+
| 2016-09-01 | 10.1.1.105 | NULL | 1/1/1 | 1 | 900 | 353003400 |
| 2016-09-01 | 10.1.1.105 | NULL | 1/1/1 | 1 | 900 | 103156200 |
| 2016-09-01 | 10.1.1.105 | NULL | 1/1/1 | 1 | 900 | 250512300 |
| 2016-09-01 | 10.1.1.105 | NULL | 1/1/1 | 1 | 900 | 324279900 |
| 2016-09-01 | 10.1.1.105 | NULL | 1/1/1 | 1 | 900 | 282281400 |
+------------+------------+------+-------+-------+---------------+------------------+
5 rows in set, 1 warning (2.12 sec)
mysql> SELECT DATE_FORMAT(t.tsmark, '%Y-%m-01') AS tsmark2, t.site, t.lag, t.port, t.queue,
-> FLOOR(SUM(t.periodic_time)) AS periodic_time,
-> SUM(t.octets_forwarded) AS octets_forwarded
-> 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 tsmark,
-> r.monitored_object_site_id AS site, r.lag_port AS lag, r.port_id AS port, r.queue_id AS queue,
-> (c.periodic_time) AS periodic_time,
-> (c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded) AS octets_forwarded
-> FROM (service_combined_network_ingress_octets r JOIN service_combined_network_ingress_octets_periodic c ON ((r.id = c.id)))
-> WHERE r.time_recorded BETWEEN str_to_date('2016-09-01 00:00:00', '%Y-%m-%d %H:%i:%s') AND str_to_date('2016-09-01 01:00:00', '%Y-%m-%d %H:%i:%s')
-> AND r.monitored_object_site_id IN ('10.1.1.105') AND r.port_id='1/1/1' AND r.queue_id = 1 AND (ISNULL(r.suspect) OR (r.suspect = FALSE))
-> ) AS t
-> GROUP BY tsmark2, site, lag, queue;
+------------+------------+------+-------+-------+---------------+------------------+
| tsmark2 | site | lag | port | queue | periodic_time | octets_forwarded |
+------------+------------+------+-------+-------+---------------+------------------+
| 2016-09-01 | 10.1.1.105 | NULL | 1/1/1 | 1 | 4500 | 1313233200 |
+------------+------------+------+-------+-------+---------------+------------------+
1 row in set (2.10 sec)