Description:
Two algorithm-specific views with the same definition are expected to preserve query semantics. On MySQL `8.4.8`, switching one side from `ALGORITHM=MERGE` to `ALGORITHM=TEMPTABLE` changes the row count of a query built from `CROSS JOIN ... ON EXISTS(...)` with two inline subqueries over the same base table.
How to repeat:
DROP DATABASE IF EXISTS t1;
CREATE DATABASE t1;
USE t1;
CREATE TABLE t3 (
c2 INT,
c8 INT,
c9 INT
);
INSERT INTO t3 VALUES
(0, 0, 0),
(0, 0, 0),
(0, 0, 0),
(0, 0, 0),
(0, 0, 0),
(0, 0, 0),
(0, 0, 0),
(0, 0, 0),
(0, 0, 0),
(0, 0, 0),
(0, 0, 0),
(0, 1, 19);
CREATE OR REPLACE ALGORITHM=MERGE VIEW v_merge AS
SELECT c2
FROM t3;
CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v_temptable AS
SELECT c2
FROM t3;
SELECT COUNT(*) AS col_1
FROM v_merge
CROSS JOIN (SELECT 1 AS pad FROM t3 WHERE c9 = 19) AS h
ON EXISTS(
SELECT 1
FROM (SELECT ROUND(c8, 2) AS col_2 FROM t3 WHERE c9 = 19) AS sub
WHERE v_merge.c2 = sub.col_2
);
SELECT COUNT(*) AS col_1
FROM v_temptable
CROSS JOIN (SELECT 1 AS pad FROM t3 WHERE c9 = 19) AS h
ON EXISTS(
SELECT 1
FROM (SELECT ROUND(c8, 2) AS col_2 FROM t3 WHERE c9 = 19) AS sub
WHERE v_temptable.c2 = sub.col_2
);
Observed results:
MERGE query:
| col_1 |
| ----- |
| 12 |
TEMPTABLE query:
| col_1 |
| ----- |
| 0 |
