MySQL Forums
Forum List  »  Optimizer & Parser

MySQL subquery
Posted by: harry sheng
Date: October 27, 2016 02:51PM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL subquery
1185
October 27, 2016 02:51PM
747
October 28, 2016 01:03AM
737
November 06, 2016 08:50PM
743
November 07, 2016 01:39AM


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.