How does MySQL UNION ALL run agregate functions?

Question

I have some SQL code which is working exactly how I want it to:

select 10 as number, "Checklist 10 Foo" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
from checklist_10
union all
select 11 as number, "Checklist 11 Bar" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
from checklist_11
union all
select 12 as number, "Checklist 12 Baz" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
from checklist_12

group by callsign, number
order by max_ts

results:

number,name,max(id),max_ts,callsign,max(time_hint_utc),count(*)
10,Checklist 10 Foo,2,1486554484635,VRTEST,2017-02-08 12:21:32,2
11,Checklist 11 Bar,2,1486554490674,VRTEST,2017-02-08 12:21:39,2
12,Checklist 12 Baz,2,1486554496378,VRTEST,2017-02-08 12:21:44,2
12,Checklist 12 Baz,3,1486554496379,VRTEST2,2017-02-08 12:21:45,1

In particular, I am seeing the correct results for max() and count() i.e. the max and count of each callsign/number combination, not of the individual selects.

How can this work? The aggregate functions look like they are in the table-level selects, yet they function as if they are functions over the union all'ed results.

P.S. Sorry to ask a question, when the only problem is my lack understanding.

Updating with table descriptions:

mysql> describe checklist_10;
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
| Field         | Type                                                         | Null | Key | Default | Extra          |
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned                                          | NO   | PRI | NULL    | auto_increment |
| ts            | bigint(20) unsigned                                          | NO   |     | NULL    |                |
| callsign      | varchar(20)                                                  | NO   |     | NULL    |                |
| smg_id        | tinyint(3) unsigned                                          | NO   |     | NULL    |                |
| time          | int(11)                                                      | NO   |     | NULL    |                |
| time_hint_utc | datetime                                                     | NO   |     | NULL    |                |
| reason        | enum('UNKNOWN','PERIODIC','SHIFT','MENU','EVENT','DECLINED') | NO   |     | NULL    |                |
| foo0          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo1          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo2          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo3          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo4          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo5          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo6          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo7          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo8          | tinyint(1)                                                   | NO   |     | NULL    |                |
| foo9          | tinyint(1)                                                   | NO   |     | NULL    |                |
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
17 rows in set (0.00 sec)

mysql> describe checklist_11;
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
| Field         | Type                                                         | Null | Key | Default | Extra          |
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned                                          | NO   | PRI | NULL    | auto_increment |
| ts            | bigint(20) unsigned                                          | NO   |     | NULL    |                |
| callsign      | varchar(20)                                                  | NO   |     | NULL    |                |
| smg_id        | tinyint(3) unsigned                                          | NO   |     | NULL    |                |
| time          | int(11)                                                      | NO   |     | NULL    |                |
| time_hint_utc | datetime                                                     | NO   |     | NULL    |                |
| reason        | enum('UNKNOWN','PERIODIC','SHIFT','MENU','EVENT','DECLINED') | NO   |     | NULL    |                |
| bar0          | tinyint(1)                                                   | NO   |     | NULL    |                |
| bar1          | tinyint(1)                                                   | NO   |     | NULL    |                |
| bar2          | tinyint(1)                                                   | NO   |     | NULL    |                |
| bar3          | tinyint(1)                                                   | NO   |     | NULL    |                |
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

mysql> describe checklist_12;
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
| Field         | Type                                                         | Null | Key | Default | Extra          |
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned                                          | NO   | PRI | NULL    | auto_increment |
| ts            | bigint(20) unsigned                                          | NO   |     | NULL    |                |
| callsign      | varchar(20)                                                  | NO   |     | NULL    |                |
| smg_id        | tinyint(3) unsigned                                          | NO   |     | NULL    |                |
| time          | int(11)                                                      | NO   |     | NULL    |                |
| time_hint_utc | datetime                                                     | NO   |     | NULL    |                |
| reason        | enum('UNKNOWN','PERIODIC','SHIFT','MENU','EVENT','DECLINED') | NO   |     | NULL    |                |
| baz0          | tinyint(1)                                                   | NO   |     | NULL    |                |
| baz1          | tinyint(1)                                                   | NO   |     | NULL    |                |
| baz2          | tinyint(1)                                                   | NO   |     | NULL    |                |
| baz3          | tinyint(1)                                                   | NO   |     | NULL    |                |
| baz4          | tinyint(1)                                                   | NO   |     | NULL    |                |
+---------------+--------------------------------------------------------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

Data:

mysql> select * from checklist_10;
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+------+------+------+------+------+------+
| id | ts            | callsign | smg_id | time       | time_hint_utc       | reason   | foo0 | foo1 | foo2 | foo3 | foo4 | foo5 | foo6 | foo7 | foo8 | foo9 |
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+------+------+------+------+------+------+
|  1 | 1486554385343 | VRTEST   |      7 | 1486556393 | 2017-02-08 12:19:53 | PERIODIC |    1 |    1 |    0 |    0 |    1 |    1 |    0 |    1 |    0 |    0 |
|  2 | 1486554484635 | VRTEST   |      7 | 1486556492 | 2017-02-08 12:21:32 | SHIFT    |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    1 |    0 |    0 |
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+------+------+------+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from checklist_11;
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+
| id | ts            | callsign | smg_id | time       | time_hint_utc       | reason   | bar0 | bar1 | bar2 | bar3 |
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+
|  1 | 1486554457077 | VRTEST   |      7 | 1486556465 | 2017-02-08 12:21:05 | PERIODIC |    0 |    0 |    0 |    0 |
|  2 | 1486554490674 | VRTEST   |      7 | 1486556499 | 2017-02-08 12:21:39 | SHIFT    |    1 |    1 |    1 |    1 |
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from checklist_12;
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+------+
| id | ts            | callsign | smg_id | time       | time_hint_utc       | reason   | baz0 | baz1 | baz2 | baz3 | baz4 |
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+------+
|  1 | 1486554476903 | VRTEST   |      7 | 1486556485 | 2017-02-08 12:21:25 | PERIODIC |    1 |    1 |    1 |    1 |    1 |
|  2 | 1486554496378 | VRTEST   |      7 | 1486556504 | 2017-02-08 12:21:44 | SHIFT    |    1 |    1 |    1 |    1 |    1 |
|  3 | 1486554496379 | VRTEST2  |      7 | 1486556505 | 2017-02-08 12:21:45 | SHIFT    |    1 |    1 |    1 |    1 |    1 |
+----+---------------+----------+--------+------------+---------------------+----------+------+------+------+------+------+
3 rows in set (0.00 sec)

No 1054 error:

mysql> select 10 as number, "Checklist 10 Foo" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
    -> from checklist_10
    -> union all
    -> select 11 as number, "Checklist 11 Bar" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
    -> from checklist_11
    -> union all
    -> select 12 as number, "Checklist 12 Baz" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
    -> from checklist_12
    -> 
    -> group by callsign, number
    -> order by max_ts;
+--------+------------------+---------+---------------+----------+---------------------+----------+
| number | name             | max(id) | max_ts        | callsign | max(time_hint_utc)  | count(*) |
+--------+------------------+---------+---------------+----------+---------------------+----------+
|     10 | Checklist 10 Foo |       2 | 1486554484635 | VRTEST   | 2017-02-08 12:21:32 |        2 |
|     11 | Checklist 11 Bar |       2 | 1486554490674 | VRTEST   | 2017-02-08 12:21:39 |        2 |
|     12 | Checklist 12 Baz |       2 | 1486554496378 | VRTEST   | 2017-02-08 12:21:44 |        2 |
|     12 | Checklist 12 Baz |       3 | 1486554496379 | VRTEST2  | 2017-02-08 12:21:45 |        1 |
+--------+------------------+---------+---------------+----------+---------------------+----------+
4 rows in set (0.00 sec)

Pls provide some sample data and the output you received using your sample data and your above query — Shadow, Feb 08 '17 at 15:31
@RyanVincent Thanks for your interest, I've added the table descriptions and data. — fadedbee, Feb 08 '17 at 15:38
I'm curious don't you get a 1054 unknown column number in group by error? — P.Salmon, Feb 08 '17 at 15:45
@P.Salmon MySQL allows the use of aliases in the group by clause — Shadow, Feb 08 '17 at 15:46
@Shadow I asked because I did get and error when I tried to reproduce the query. — P.Salmon, Feb 08 '17 at 15:48
@P.Salmon No 1054 error - I will paste the output on the end of the question... — fadedbee, Feb 08 '17 at 15:50
@P.Salmon is your MySQL version earlier than v5.7.5 and is the full group by sql mode enabled in your MySQL? — Shadow, Feb 08 '17 at 15:54

Solarflare · Accepted Answer · 2017-02-08T16:04:04.333

2

It does not work as you think it does. According to the documentation,

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

So what happens here is that you have two implicit group by (that will always just return one row, no matter if you e.g. have different callsigns in your data) and one explicit group by that actually only groups your last select.

So for MySQL, your query looks and acts like

(select 10 as number, "Checklist 10 Foo" as name, max(id), max(ts) as max_ts, 
    callsign, max(time_hint_utc), count(*)
from checklist_10)

union all

(select 11 as number, "Checklist 11 Bar" as name, max(id), max(ts) as max_ts, 
   callsign, max(time_hint_utc), count(*)
from checklist_11)

union all

(select 12 as number, "Checklist 12 Baz" as name, max(id), max(ts) as max_ts, 
   callsign, max(time_hint_utc), count(*)
from checklist_12
group by callsign, number)

order by max_ts

You could test this e.g. by changing the callsign for one of your rows in checklist_10 or checklist_11. It will not group as expected then. Or you can e.g. rename the alias number to number1 in either your first or last query. It will (only) work in the first case, in the latter case, the group by cannot find the column number now.

Also, in your case this will only work if you disable the only_full_group_by sql mode, because your select list includes columns other than aggregate functions.

edited Feb 08 '17 at 16:04

answered Feb 08 '17 at 15:39

Solarflare

10,721
2
18
35

2

I agree. The OP set up the test data in such way that his query produces the expected outcome. If any `vtest2` data were added to the other tables, then the results would not be in line with his expectations. – Shadow Feb 08 '17 at 15:50
I don't fully understand what you have written, because it I can't see why the last select clause is different from the other two. – fadedbee Feb 08 '17 at 15:56
2

@chrisdew your last select statement has a group by clause, the other ones do not. That empty line before the group by does not make the group by to apply to the results of the union. – Shadow Feb 08 '17 at 15:59
Ah, I've tricked myself - the last select is special because the `group by` and `order by` clauses are bound to the last select only, not the result of the `union all`'s. – fadedbee Feb 08 '17 at 16:01
1

@Solarflare: the order by applies to the last query only, you need to have explicit brackets around the selects for the order by to apply to the entire unioned resultset. – Shadow Feb 08 '17 at 16:13
1

@Shadow No, the `order by` in combination with a `union` will always act on the whole resultset, unless it is explicitly inside a bracket. Both versions (the original one completely without brackets and the one with brackets, but the order by outside of it) should act the same. – Solarflare Feb 08 '17 at 16:40
1

@Solarflare I beg to diasagree. Quote from MySQL manual: `"To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one."` https://dev.mysql.com/doc/refman/5.7/en/union.html – Shadow Feb 08 '17 at 18:05
1

@Shadow Yes, that quote is correct. But I meant what is said some lines after that quote: `"A statement without parentheses is equivalent to one parenthesized as just shown."` (So without brackets like the OP did it is the same as with brackets as in my answer, with `order by` outside of the brackets). And some lines before that: `"To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT"` – Solarflare Feb 08 '17 at 19:16

score 0 · Answer 2 · answered Feb 08 '17 at 16:14

It seems that the group by binds to the individual selects, but that order by operates over the union all of the selects.

The solution was to put a group by clause in each select:

mysql> select 10 as number, "Checklist 10 Foo" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
    -> from checklist_10
    -> group by callsign
    -> 
    -> union all
    -> 
    -> select 11 as number, "Checklist 11 Bar" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
    -> from checklist_11
    -> group by callsign
    -> 
    -> union all
    -> 
    -> select 12 as number, "Checklist 12 Baz" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*)
    -> from checklist_12
    -> group by callsign
    -> 
    -> order by max_ts
    -> ;
+--------+------------------+---------+---------------+----------+---------------------+----------+
| number | name             | max(id) | max_ts        | callsign | max(time_hint_utc)  | count(*) |
+--------+------------------+---------+---------------+----------+---------------------+----------+
|     10 | Checklist 10 Foo |       2 | 1486554484635 | VRTEST   | 2017-02-08 12:21:32 |        2 |
|     11 | Checklist 11 Bar |       2 | 1486554490674 | VRTEST   | 2017-02-08 12:21:39 |        2 |
|     12 | Checklist 12 Baz |       2 | 1486554496378 | VRTEST   | 2017-02-08 12:21:44 |        2 |
|     12 | Checklist 12 Baz |       3 | 1486554496379 | VRTEST2  | 2017-02-08 12:21:45 |        1 |
|     10 | Checklist 10 Foo |       3 | 1486554496380 | VRTEST2  | 2017-02-08 12:21:46 |        1 |
+--------+------------------+---------+---------------+----------+---------------------+----------+
5 rows in set (0.00 sec)

How does MySQL UNION ALL run agregate functions?

2 Answers2