Description:
A query with a CTE that is used twice is not returning any records in MySQL 8.0.18 and later, while it did produce records in 8.0.17.
How to repeat:
CREATE TABLE `partner` (
`id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`instructions` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `partner`
VALUES ('414a0656-02e3-4409-b935-1385927e6e87','x');
CREATE TABLE `timeslot` (
`id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`fromTime` time DEFAULT NULL,
`toTime` time DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `timeslot`
VALUES
('0376d1cf-9f48-4bdf-94d2-6e30b82e525d','15:00:00','23:59:59'),
('33460608-3e53-48d7-8614-dcd3a556ce73','09:00:00','14:59:59'),
('8c882eb2-4
87f-499b-bc95-9a260bf288d5','00:00:00','08:59:59');
WITH dates AS (
SELECT '2021-03-28' as theDate, DAYOFWEEK( '2021-03-28' ) as dow
)
, partnerDays as (
SELECT *, 1 as dow FROM partner
UNION
SELECT *, 2 as dow FROM partner
)
, allSlots AS (
SELECT DISTINCT s.*
FROM (
SELECT *, 1 as dow FROM timeslot
UNION ALL
SELECT *, 2 as dow FROM timeslot
) s
INNER JOIN partnerDays pd ON pd.dow = s.dow
)
, expandedSlots as (
SELECT
timestamp(d.theDate, s.fromTime) AS slotstart
, timestamp(d.theDate, s.toTime) AS slotend
FROM allSlots s
INNER JOIN dates d ON s.dow = d.dow
)
, largestInterval as (
SELECT
min(slotstart) as firstSlotStart, max(slotend) as lastSlotEnd
FROM expandedSlots
)
-- select * from expandedSlots
-- select * from largestInterval
select * from expandedSlots, largestInterval;
Observations:
- worked in 8.0.17 (official Docker image)
- doesn't work in 8.0.18-23 (official Docker images)
- both expandedSlots and largestInterval individually produce rows (commented out), but together in a cartesian join they don't
- dropping the instructions text column in the partner table makes the query work.
Suggested fix:
Possibly a duplicate of https://bugs.mysql.com/bug.php?id=102251
Description: A query with a CTE that is used twice is not returning any records in MySQL 8.0.18 and later, while it did produce records in 8.0.17. How to repeat: CREATE TABLE `partner` ( `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `instructions` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `partner` VALUES ('414a0656-02e3-4409-b935-1385927e6e87','x'); CREATE TABLE `timeslot` ( `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `fromTime` time DEFAULT NULL, `toTime` time DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `timeslot` VALUES ('0376d1cf-9f48-4bdf-94d2-6e30b82e525d','15:00:00','23:59:59'), ('33460608-3e53-48d7-8614-dcd3a556ce73','09:00:00','14:59:59'), ('8c882eb2-4 87f-499b-bc95-9a260bf288d5','00:00:00','08:59:59'); WITH dates AS ( SELECT '2021-03-28' as theDate, DAYOFWEEK( '2021-03-28' ) as dow ) , partnerDays as ( SELECT *, 1 as dow FROM partner UNION SELECT *, 2 as dow FROM partner ) , allSlots AS ( SELECT DISTINCT s.* FROM ( SELECT *, 1 as dow FROM timeslot UNION ALL SELECT *, 2 as dow FROM timeslot ) s INNER JOIN partnerDays pd ON pd.dow = s.dow ) , expandedSlots as ( SELECT timestamp(d.theDate, s.fromTime) AS slotstart , timestamp(d.theDate, s.toTime) AS slotend FROM allSlots s INNER JOIN dates d ON s.dow = d.dow ) , largestInterval as ( SELECT min(slotstart) as firstSlotStart, max(slotend) as lastSlotEnd FROM expandedSlots ) -- select * from expandedSlots -- select * from largestInterval select * from expandedSlots, largestInterval; Observations: - worked in 8.0.17 (official Docker image) - doesn't work in 8.0.18-23 (official Docker images) - both expandedSlots and largestInterval individually produce rows (commented out), but together in a cartesian join they don't - dropping the instructions text column in the partner table makes the query work. Suggested fix: Possibly a duplicate of https://bugs.mysql.com/bug.php?id=102251