Description:
Please see the markdown-formatted bug report attached.
A query `SELECT COUNT(*) FROM t1 CROSS JOIN t3 WHERE t3.name IN (<3-table non-equi GROUP-free
subquery>)` returns **30 over the base table `t` but only 27 over a row-identical view `t`** whose
sole difference is that it is defined with a `LATERAL` derived table
(`SELECT ll.* FROM tb AS ls, LATERAL (SELECT ls.id, ls.name, ls.created_at) AS ll`). The output
cardinality of a join+filter is pure relational algebra — a function of the data alone, independent
of physical row order **and** of the chosen plan — so two evaluations over identical rows must agree.
The divergence is entirely in the **semijoin** that MySQL derives from the `IN (subquery)`:
`SET optimizer_switch='semijoin=off'` makes both sides return 30. The buggy plan applies
duplicate-weedout semijoin over the LATERAL-materialized derived tables and drops 3 rows.
How to repeat:
CREATE TABLE t (id BIGINT, name VARCHAR(255), created_at VARCHAR(255));
INSERT INTO t VALUES (1,'a','x'),(2,'b','x'),(3,'b','x'),(4,NULL,'x'),(4,'c','x'),(5,'d','x');
ALTER TABLE t RENAME TO tb;
CREATE VIEW t AS SELECT ll.id, ll.name, ll.created_at
FROM tb AS ls,
LATERAL (SELECT ls.id AS id, ls.name AS name, ls.created_at AS created_at) AS ll;
-- EQUIVALENT (this view): 27 (WRONG)
SELECT COUNT(*) FROM t AS t1 CROSS JOIN t AS t3
WHERE t3.name IN (SELECT t6.name FROM t AS t4 JOIN t AS t5 ON t4.id != t5.id
JOIN t AS t6 ON t5.id = t6.id);
-- CONTROL: same view, semijoin off -> 30 (correct)
SET SESSION optimizer_switch='semijoin=off';
SELECT COUNT(*) FROM t AS t1 CROSS JOIN t AS t3
WHERE t3.name IN (SELECT t6.name FROM t AS t4 JOIN t AS t5 ON t4.id != t5.id
JOIN t AS t6 ON t5.id = t6.id);
Expected vs actual
| query | expected | actual |
|---|---|---|
| COUNT over base table `t` | 30 | 30 |
| COUNT over LATERAL-view `t` | 30 | **27** |
| COUNT over LATERAL-view `t`, `semijoin=off` | 30 | 30 |
Description: Please see the markdown-formatted bug report attached. A query `SELECT COUNT(*) FROM t1 CROSS JOIN t3 WHERE t3.name IN (<3-table non-equi GROUP-free subquery>)` returns **30 over the base table `t` but only 27 over a row-identical view `t`** whose sole difference is that it is defined with a `LATERAL` derived table (`SELECT ll.* FROM tb AS ls, LATERAL (SELECT ls.id, ls.name, ls.created_at) AS ll`). The output cardinality of a join+filter is pure relational algebra — a function of the data alone, independent of physical row order **and** of the chosen plan — so two evaluations over identical rows must agree. The divergence is entirely in the **semijoin** that MySQL derives from the `IN (subquery)`: `SET optimizer_switch='semijoin=off'` makes both sides return 30. The buggy plan applies duplicate-weedout semijoin over the LATERAL-materialized derived tables and drops 3 rows. How to repeat: CREATE TABLE t (id BIGINT, name VARCHAR(255), created_at VARCHAR(255)); INSERT INTO t VALUES (1,'a','x'),(2,'b','x'),(3,'b','x'),(4,NULL,'x'),(4,'c','x'),(5,'d','x'); ALTER TABLE t RENAME TO tb; CREATE VIEW t AS SELECT ll.id, ll.name, ll.created_at FROM tb AS ls, LATERAL (SELECT ls.id AS id, ls.name AS name, ls.created_at AS created_at) AS ll; -- EQUIVALENT (this view): 27 (WRONG) SELECT COUNT(*) FROM t AS t1 CROSS JOIN t AS t3 WHERE t3.name IN (SELECT t6.name FROM t AS t4 JOIN t AS t5 ON t4.id != t5.id JOIN t AS t6 ON t5.id = t6.id); -- CONTROL: same view, semijoin off -> 30 (correct) SET SESSION optimizer_switch='semijoin=off'; SELECT COUNT(*) FROM t AS t1 CROSS JOIN t AS t3 WHERE t3.name IN (SELECT t6.name FROM t AS t4 JOIN t AS t5 ON t4.id != t5.id JOIN t AS t6 ON t5.id = t6.id); Expected vs actual | query | expected | actual | |---|---|---| | COUNT over base table `t` | 30 | 30 | | COUNT over LATERAL-view `t` | 30 | **27** | | COUNT over LATERAL-view `t`, `semijoin=off` | 30 | 30 |