Unexpected behaviour involving UNION ALL in CTE with MYSQL-CodePudding

I've tried to re-create a minimal reproducible example but without success so I'll have to paste my actual query, I'm sorry for the name of the columns and tables in a foreign language.

The queries below have the same WITH preamble.

On MySQL 8.0.22 the following query

WITH
    CTE1 AS (SELECT cinema, giorno, inizio, fine, dipendente
            FROM (SELECT cinema, giorno, dipendente, inizio, SEC_TO_TIME(TIME_TO_SEC(inizio)   TIME_TO_SEC(durata)) AS fine
                FROM Turni JOIN Dipendenti ON matricola = dipendente AND ruolo = 'Maschera'
                UNION ALL SELECT id, nome, NULL, apertura, apertura FROM Cinema, Giorni
                UNION ALL SELECT id, nome, NULL, chiusura, chiusura FROM Cinema, Giorni) AS T0),
    CTE2 AS (SELECT T1.cinema, T1.giorno, T1.inizio AS ora_inizio, MIN(T2.inizio) AS ora_fine
            FROM (SELECT cinema, giorno, inizio FROM CTE1 UNION ALL (SELECT cinema, giorno, fine FROM CTE1)) AS T1
                JOIN (SELECT cinema, giorno, inizio FROM CTE1 UNION ALL (SELECT cinema, giorno, fine FROM CTE1)) AS T2
                    ON T1.cinema = T2.cinema AND T1.giorno = T2.giorno
            WHERE T1.inizio < T2.inizio
            GROUP BY T1.cinema, T1.giorno, T1.inizio)
SELECT T3.cinema, T3.giorno, ora_inizio, ora_fine, dipendente
FROM CTE2 AS T3 JOIN CTE1 AS T4 ON T3.cinema = T4.cinema AND T3.giorno = T4.giorno
WHERE inizio <= ora_inizio AND ora_fine <= fine;

returns a result set of 17 rows.

The query

WITH
    CTE1 AS (SELECT cinema, giorno, inizio, fine, dipendente
            FROM (SELECT cinema, giorno, dipendente, inizio, SEC_TO_TIME(TIME_TO_SEC(inizio)   TIME_TO_SEC(durata)) AS fine
                FROM Turni JOIN Dipendenti ON matricola = dipendente AND ruolo = 'Maschera'
                UNION ALL SELECT id, nome, NULL, apertura, apertura FROM Cinema, Giorni
                UNION ALL SELECT id, nome, NULL, chiusura, chiusura FROM Cinema, Giorni) AS T0),
    CTE2 AS (SELECT T1.cinema, T1.giorno, T1.inizio AS ora_inizio, MIN(T2.inizio) AS ora_fine
            FROM (SELECT cinema, giorno, inizio FROM CTE1 UNION ALL (SELECT cinema, giorno, fine FROM CTE1)) AS T1
                JOIN (SELECT cinema, giorno, inizio FROM CTE1 UNION ALL (SELECT cinema, giorno, fine FROM CTE1)) AS T2
                    ON T1.cinema = T2.cinema AND T1.giorno = T2.giorno
            WHERE T1.inizio < T2.inizio
            GROUP BY T1.cinema, T1.giorno, T1.inizio)
SELECT cinema, giorno, ora_inizio, MIN(ora_fine), NULL
FROM CTE2
GROUP BY cinema, giorno, ora_inizio;

returns a result set of 80 rows.

Instead of returning a result set of 80 17 = 97 rows the following query returns a result set of 18 rows

WITH
    CTE1 AS (SELECT cinema, giorno, inizio, fine, dipendente
            FROM (SELECT cinema, giorno, dipendente, inizio, SEC_TO_TIME(TIME_TO_SEC(inizio)   TIME_TO_SEC(durata)) AS fine
                FROM Turni JOIN Dipendenti ON matricola = dipendente AND ruolo = 'Maschera'
                UNION ALL SELECT id, nome, NULL, apertura, apertura FROM Cinema, Giorni
                UNION ALL SELECT id, nome, NULL, chiusura, chiusura FROM Cinema, Giorni) AS T0),
    CTE2 AS (SELECT T1.cinema, T1.giorno, T1.inizio AS ora_inizio, MIN(T2.inizio) AS ora_fine
            FROM (SELECT cinema, giorno, inizio FROM CTE1 UNION ALL (SELECT cinema, giorno, fine FROM CTE1)) AS T1
                JOIN (SELECT cinema, giorno, inizio FROM CTE1 UNION ALL (SELECT cinema, giorno, fine FROM CTE1)) AS T2
                    ON T1.cinema = T2.cinema AND T1.giorno = T2.giorno
            WHERE T1.inizio < T2.inizio
            GROUP BY T1.cinema, T1.giorno, T1.inizio)
SELECT T3.cinema, T3.giorno, ora_inizio, ora_fine, dipendente
FROM CTE2 AS T3 JOIN CTE1 AS T4 ON T3.cinema = T4.cinema AND T3.giorno = T4.giorno
WHERE inizio <= ora_inizio AND ora_fine <= fine
UNION ALL
SELECT cinema, giorno, ora_inizio, MIN(ora_fine), NULL
FROM CTE2
GROUP BY cinema, giorno, ora_inizio;

Switching the selects I get a result set of 97 elements

WITH
    CTE1 AS (SELECT cinema, giorno, inizio, fine, dipendente
            FROM (SELECT cinema, giorno, dipendente, inizio, SEC_TO_TIME(TIME_TO_SEC(inizio)   TIME_TO_SEC(durata)) AS fine
                FROM Turni JOIN Dipendenti ON matricola = dipendente AND ruolo = 'Maschera'
                UNION ALL SELECT id, nome, NULL, apertura, apertura FROM Cinema, Giorni
                UNION ALL SELECT id, nome, NULL, chiusura, chiusura FROM Cinema, Giorni) AS T0),
    CTE2 AS (SELECT T1.cinema, T1.giorno, T1.inizio AS ora_inizio, MIN(T2.inizio) AS ora_fine
            FROM (SELECT cinema, giorno, inizio FROM CTE1 UNION ALL (SELECT cinema, giorno, fine FROM CTE1)) AS T1
                JOIN (SELECT cinema, giorno, inizio FROM CTE1 UNION ALL (SELECT cinema, giorno, fine FROM CTE1)) AS T2
                    ON T1.cinema = T2.cinema AND T1.giorno = T2.giorno
            WHERE T1.inizio < T2.inizio
            GROUP BY T1.cinema, T1.giorno, T1.inizio)
SELECT cinema, giorno, ora_inizio, MIN(ora_fine) AS ora_fine, NULL AS dipendente
FROM CTE2
GROUP BY cinema, giorno, ora_inizio
UNION ALL
SELECT T3.cinema, T3.giorno, ora_inizio, ora_fine, dipendente
FROM CTE2 AS T3 JOIN CTE1 AS T4 ON T3.cinema = T4.cinema AND T3.giorno = T4.giorno
WHERE inizio <= ora_inizio AND ora_fine <= fine;

Is this a bug or is it caused by a syntactic error?

CodePudding user response：

In the file specified by log_error(see: show variables like 'log_error';) is the following info:

Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff6a0662957    mysqld.exe!??$endl@DU?$char_traits@D@std@@@std@@YAAEAV?$basic_ostream@DU?$char_traits@D@std@@@0@AEAV10@@Z()
7ff69f1e48e2    mysqld.exe!?ha_index_init@handler@@QEAAHI_N@Z()
7ff69f6646e7    mysqld.exe!?Init@?$IndexScanIterator@$0A@@@UEAA_NXZ()
7ff69f6d16b4    mysqld.exe!?Init@AggregateIterator@@UEAA_NXZ()
7ff69f6d2ecb    mysqld.exe!?Read@AppendIterator@@UEAAHXZ()
7ff69f5522be    mysqld.exe!?ExecuteIteratorQuery@Query_expression@@QEAA_NPEAVTHD@@@Z()
7ff69f553896    mysqld.exe!?execute@Query_expression@@QEAA_NPEAVTHD@@@Z()
7ff69f45e15e    mysqld.exe!?execute_inner@Sql_cmd_dml@@MEAA_NPEAVTHD@@@Z()
7ff69f45dfdc    mysqld.exe!?execute@Sql_cmd_dml@@UEAA_NPEAVTHD@@@Z()
7ff69f3bc9ec    mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
7ff69f3b7b91    mysqld.exe!?dispatch_sql_command@@YAXPEAVTHD@@PEAVParser_state@@@Z()
7ff69f3b68c5    mysqld.exe!?dispatch_command@@YA_NPEAVTHD@@PEBTCOM_DATA@@W4enum_server_command@@@Z()
7ff69f3b7ed0    mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
7ff69f21bab8    mysqld.exe!?modify_thread_cache_size@Per_thread_connection_handler@@SAXK@Z()
7ff6a05e0e8f    mysqld.exe!??$endl@DU?$char_traits@D@std@@@std@@YAAEAV?$basic_ostream@DU?$char_traits@D@std@@@0@AEAV10@@Z()
7ff6a01c2edc    mysqld.exe!?my_thread_self_setname@@YAXPEBD@Z()
7ffc27026c0c    ucrtbase.dll!_recalloc()
7ffc27ac54e0    KERNEL32.DLL!BaseThreadInitThunk()
7ffc299e485b    ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (24ba3a15230): WITH
    CTE1 AS (SELECT cinema, giorno, inizio, fine, dipendente
            FROM (SELECT cinema, giorno, dipendente, inizio, SEC_TO_TIME(TIME_TO_SEC(inizio)   TIME_TO_SEC(durata)) AS fine
                FROM Turni JOIN Dipendenti ON matricola = dipendente AND ruolo = 'Maschera'
                UNION ALL SELECT id, nome, NULL, apertura, apertura FROM Cinema, Giorni
                UNION ALL SELECT id, nome, NULL, chiusura, chiusura FROM Cinema, Giorni) AS T0),
    CTE2 AS (SELECT T1.cinema, T1.giorno, T1.inizio AS ora_inizio, MIN(T2.inizio) AS ora_fine
            FROM (SELECT cinema, giorno, inizio FROM CTE1 UNION ALL (SELECT cinema, giorno, fine FROM CTE1)) AS T1
                JOIN (SELECT cinema, giorno, inizio FROM CTE1 UNION ALL (SELECT cinema, giorno, fine FROM CTE1)) AS T2
                    ON T1.cinema = T2.cinema AND T1.giorno = T2.giorno
            WHERE T1.inizio < T2.inizio
            GROUP BY T1.cinema, T1.giorno, T1.inizio)
SELECT T3.cinema, T3.giorno, T3.ora_inizio, T3.ora_fine, T4.dipe
Connection ID (thread ID): 8
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

If this does not help (the info on crashing.html), you can always file a bug at: https://bugs.mysql.com/

For now the workaround is to change the query to (swap the piece before and after the UNION ALL):

WITH
    CTE1 AS (SELECT cinema, giorno, inizio, fine, dipendente
            FROM (SELECT cinema, giorno, dipendente, inizio, SEC_TO_TIME(TIME_TO_SEC(inizio)   TIME_TO_SEC(durata)) AS fine
                FROM Turni JOIN Dipendenti ON matricola = dipendente AND ruolo = 'Maschera'
                UNION ALL SELECT id, nome, NULL, apertura, apertura FROM Cinema, Giorni
                UNION ALL SELECT id, nome, NULL, chiusura, chiusura FROM Cinema, Giorni) AS T0),
    CTE2 AS (SELECT T1.cinema, T1.giorno, T1.inizio AS ora_inizio, MIN(T2.inizio) AS ora_fine
            FROM (SELECT cinema, giorno, inizio FROM CTE1 UNION ALL (SELECT cinema, giorno, fine FROM CTE1)) AS T1
                JOIN (SELECT cinema, giorno, inizio FROM CTE1 UNION ALL (SELECT cinema, giorno, fine FROM CTE1)) AS T2
                    ON T1.cinema = T2.cinema AND T1.giorno = T2.giorno
            WHERE T1.inizio < T2.inizio
            GROUP BY T1.cinema, T1.giorno, T1.inizio)
SELECT cinema, giorno, ora_inizio, MIN(ora_fine), 0
FROM CTE2
GROUP BY cinema, giorno, ora_inizio
UNION ALL 
SELECT T3.cinema, T3.giorno, T3.ora_inizio, T3.ora_fine, T4.dipendente
FROM CTE2 AS T3 JOIN CTE1 AS T4 ON T3.cinema = T4.cinema AND T3.giorno = T4.giorno
WHERE inizio <= ora_inizio AND ora_fine <= fine
;

Another workaround could be to create (temporary) tables from the CTE1 and CTE2 result sets. see this: DBFIDDLE