Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.11, 11.8, 12.3, 11.4.10
-
Not for Release Notes
Description
Two views with the same definition but different view algorithms produce different results on MariaDB.
The query groups by an indexed column, computes `VAR_SAMP(...)`, and filters with `HAVING ... IS NULL`.
`TEMPTABLE` returns the expected `NULL` group, while `MERGE` incorrectly returns a non-`NULL` aggregate value.
How to repeat:
CREATE TABLE t ( |
a INT, |
b CHAR(1), |
KEY (b) |
);
|
|
|
INSERT INTO t VALUES |
(1, 'a'), |
(2, 'a'), |
(3, 'b'); |
|
|
CREATE ALGORITHM=MERGE VIEW vm AS |
SELECT * |
FROM t; |
|
|
CREATE ALGORITHM=TEMPTABLE VIEW vt AS |
SELECT * |
FROM t; |
|
|
SELECT VAR_SAMP(a) AS v |
FROM vm |
GROUP BY b |
HAVING v IS NULL; |
|
|
SELECT VAR_SAMP(a) AS v |
FROM vt |
GROUP BY b |
HAVING v IS NULL; |
Observed result - Query A (`MERGE`):
| v |
|---|
| 0.5 |
Observed result - Query B (`TEMPTABLE`):
| v |
|---|
| NULL |
Attachments
Issue Links
- duplicates
-
MDEV-39725 CREATE VIEW extraction changes row multiplicity for DISTINCT over grouped aggregate results
-
- Confirmed
-