This is decidedly wrong. The CTE needs to be reused, not repeated.
{{
MariaDB [bts]> select calsettrace(1);
----------------
| calsettrace(1) |
----------------
| 0 |
----------------
1 row in set (0.000 sec)
MariaDB [bts]> explain
-> with mn as
-> (select count
cnt, year from flights group by year)
-> select * from
-> (
-> select * from mn
-> union all
-> select sum(cnt),'total' year from mn group by 'total'
-> ) q;
-----------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-----------------------------------------------------------------+
| 1 | PUSHED SELECT | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
-----------------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [bts]> with mn as
-> (select count cnt, year from flights group by year)
-> select * from
-> (
-> select * from mn
-> union all
-> select sum(cnt),'total' year from mn group by 'total'
-> ) q;
---------------+
| cnt | year |
---------------+
| 4624931 | 2020 |
| 7856869 | 2018 |
| 8092727 | 2019 |
| 5819079 | 2015 |
| 5617658 | 2016 |
| 5674621 | 2017 |
| 37685885 | total |
---------------+
7 rows in set, 1 warning (0.623 sec)
MariaDB [bts]> select calgettrace();
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| calgettrace() |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
BPS PM flights 3028 (year) 0 9416 0 0.578 114
TAS UM - - - - - - 0.561 6
TNS UM - - - - - - 0.000 6
TNS UM - - - - - - 0.000 6
BPS PM flights 3028 (year) 0 9416 0 0.582 114
TAS UM - - - - - - 0.562 6
TNS UM - - - - - - 0.000 6
TAS UM - - - - - - 0.000 1
TNS UM - - - - - - 0.000 1
TUS UM - - - - - - 0.003 7
TNS UM - - - - - - 0.000 7
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.000 sec)
}}