Details
Description
I found a wrong-result issue involving CREATE VIEW, SELECT DISTINCT, ORDER BY, and an expression using NULLIF.
The same SELECT DISTINCT query produces different row counts depending on whether it is used in a view or evaluated through an equivalent CTE / temporary table.
The view returns only 1 row, while the equivalent CTE and temporary table return 2 rows.
This looks incorrect because NULLIF(1, 0) should evaluate to 1, not NULL. Therefore, c0 / NULLIF(1, 0) should be equivalent to c0 / 1. Since the two source rows have different c0 values, the projected rows are distinct and both should be preserved by DISTINCT.
Environment:
MariaDB version:
12.2.2-MariaDB-ubu2404
How to repeat:
DROP VIEW IF EXISTS v0; |
DROP TEMPORARY TABLE IF EXISTS temp_t; |
DROP TABLE IF EXISTS t0; |
|
|
CREATE TABLE t0 ( |
c0 INT, |
c1 INT |
);
|
|
|
INSERT INTO t0 VALUES (2, NULL); |
INSERT INTO t0 VALUES (4, NULL); |
|
|
CREATE VIEW v0 AS |
SELECT DISTINCT |
c1 AS vc_0, |
c0 / NULLIF(1, 0) AS vc_1 |
FROM t0 |
ORDER BY vc_0 ASC, vc_1 ASC; |
|
|
SELECT * FROM v0; |
|
|
WITH CTE AS ( |
SELECT DISTINCT |
c1 AS vc_0, |
c0 / NULLIF(1, 0) AS vc_1 |
FROM t0 |
ORDER BY vc_0 ASC, vc_1 ASC |
)
|
SELECT * FROM CTE; |
|
|
CREATE TEMPORARY TABLE temp_t AS |
SELECT DISTINCT |
c1 AS vc_0, |
c0 / NULLIF(1, 0) AS vc_1 |
FROM t0 |
ORDER BY vc_0 ASC, vc_1 ASC; |
|
|
SELECT * FROM temp_t; |
Actual result:
The VIEW query returns:
+------+--------+
|
| vc_0 | vc_1 |
|
+------+--------+
|
| NULL | 2.0000 |
|
+------+--------+
|
The CTE query returns:
+------+--------+
|
| vc_0 | vc_1 |
|
+------+--------+
|
| NULL | 2.0000 |
|
| NULL | 4.0000 |
|
+------+--------+
|
The TEMPORARY TABLE query returns:
+------+--------+
|
| vc_0 | vc_1 |
|
+------+--------+
|
| NULL | 2.0000 |
|
| NULL | 4.0000 |
|
+------+--------+
|
Attachments
Issue Links
- relates to
-
MDEV-39725 CREATE VIEW extraction changes row multiplicity for DISTINCT over grouped aggregate results
-
- Confirmed
-