Description:
A mergeable `VIEW` whose column is a real expression (`CASE WHEN TRUE THEN created_at ELSE CAST(NULL AS CHAR(255)) END`, `CAST(created_at AS CHAR(255))`, or a `JSON_EXTRACT` unpack) silently drops `GROUP BY` groups when queried as
SELECT created_at FROM t t3
GROUP BY t3.id, t3.created_at
HAVING MIN(t3.created_at) >= IFNULL(t3.created_at, t3.created_at);
``
How to repeat:
CREATE TABLE b (id BIGINT, created_at VARCHAR(255));
INSERT INTO b VALUES (NULL, NULL);
INSERT INTO b VALUES (-1, 'trailing ');
INSERT INTO b VALUES (2, 'o''brien');
INSERT INTO b VALUES (-7, '');
INSERT INTO b VALUES (-7, NULL);
INSERT INTO b VALUES (1, 'trailing ');
INSERT INTO b VALUES (-7, 'Zed');
INSERT INTO b VALUES (-1, 'trailing ');
CREATE VIEW t AS
SELECT id, CASE WHEN TRUE THEN created_at ELSE CAST(NULL AS CHAR(255)) END AS created_at
FROM b;
SELECT created_at FROM t t3
GROUP BY t3.id, t3.created_at
HAVING MIN(t3.created_at) >= IFNULL(t3.created_at, t3.created_at);
-- Expected 5 rows: ('trailing '), ("o'brien"), (''), ('trailing '), ('Zed')
-- Actual 2 rows: ('trailing '), ('trailing ')
--
-- Why 5 is correct: every non-NULL group is a singleton created_at, so MIN(created_at)
-- equals created_at and IFNULL(created_at, created_at) is that same non-NULL value.
-- MIN >= IFNULL is TRUE. The two all-NULL groups are UNKNOWN and are dropped on both
-- sides. The CASE view wrongly also drops "o'brien", '', and 'Zed'.
On MySQL 8.4.10, the same setup and query returns three rows:
+------------+
| created_at |
+------------+
| trailing |
| trailing |
| Zed |
+------------+
Description: A mergeable `VIEW` whose column is a real expression (`CASE WHEN TRUE THEN created_at ELSE CAST(NULL AS CHAR(255)) END`, `CAST(created_at AS CHAR(255))`, or a `JSON_EXTRACT` unpack) silently drops `GROUP BY` groups when queried as SELECT created_at FROM t t3 GROUP BY t3.id, t3.created_at HAVING MIN(t3.created_at) >= IFNULL(t3.created_at, t3.created_at); `` How to repeat: CREATE TABLE b (id BIGINT, created_at VARCHAR(255)); INSERT INTO b VALUES (NULL, NULL); INSERT INTO b VALUES (-1, 'trailing '); INSERT INTO b VALUES (2, 'o''brien'); INSERT INTO b VALUES (-7, ''); INSERT INTO b VALUES (-7, NULL); INSERT INTO b VALUES (1, 'trailing '); INSERT INTO b VALUES (-7, 'Zed'); INSERT INTO b VALUES (-1, 'trailing '); CREATE VIEW t AS SELECT id, CASE WHEN TRUE THEN created_at ELSE CAST(NULL AS CHAR(255)) END AS created_at FROM b; SELECT created_at FROM t t3 GROUP BY t3.id, t3.created_at HAVING MIN(t3.created_at) >= IFNULL(t3.created_at, t3.created_at); -- Expected 5 rows: ('trailing '), ("o'brien"), (''), ('trailing '), ('Zed') -- Actual 2 rows: ('trailing '), ('trailing ') -- -- Why 5 is correct: every non-NULL group is a singleton created_at, so MIN(created_at) -- equals created_at and IFNULL(created_at, created_at) is that same non-NULL value. -- MIN >= IFNULL is TRUE. The two all-NULL groups are UNKNOWN and are dropped on both -- sides. The CASE view wrongly also drops "o'brien", '', and 'Zed'. On MySQL 8.4.10, the same setup and query returns three rows: +------------+ | created_at | +------------+ | trailing | | trailing | | Zed | +------------+