Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
11.4.10
-
Not for Release Notes
Description
Under semantic-equivalent rewriting, extracting a grouped VAR_SAMP subquery into a view causes a stable COUNT( * ) result mismatch on MariaDB when the grouped result is ordered and then filtered by IS NULL.
The source and mutated queries are logically equivalent, but return different final values.
CREATE TABLE t1_case06 ( |
c1 INT NOT NULL, |
c6 VARCHAR(10) NOT NULL |
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; |
|
|
INSERT INTO t1_case06 VALUES |
(2323, 'sample_s'), |
(3269, 'sample_S'), |
(143, 'sample_3Bs'); |
|
|
CREATE INDEX idx_c6 ON t1_case06 (c6); |
|
|
-- Source Original SQL
|
SELECT COUNT(*) AS col_1 |
FROM ( |
SELECT c6, VAR_SAMP(c1) AS v |
FROM t1_case06 |
GROUP BY c6 |
ORDER BY c6 DESC |
) q
|
WHERE v IS NULL; |
|
|
-- View SQL
|
CREATE VIEW v_case06 AS |
SELECT c6, VAR_SAMP(c1) AS v |
FROM t1_case06 |
GROUP BY c6; |
|
|
-- Mutated SQL
|
SELECT COUNT(*) AS col_1 |
FROM ( |
SELECT c6, v |
FROM v_case06 |
ORDER BY c6 DESC |
) q
|
WHERE v IS NULL; |
Observed result - Query A (original):
| col_1 |
|---|
| 0 |
Observed result - Query B (mutated):
| col_1 |
|---|
| 1 |
Attachments
Issue Links
- duplicates
-
MDEV-39725 CREATE VIEW extraction changes row multiplicity for DISTINCT over grouped aggregate results
-
- Confirmed
-