Details
-
Bug
-
Status: Needs Feedback (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.3.2
-
None
Description
-
-
- Problem Description
-
When `DISTINCTROW` is used, the expression `NULLIF((- TRUE), c0)` returns different results in a direct query versus a query rewritten with a CTE/derived table: the direct query returns `-1`, while the rewritten query returns `1`. Without `DISTINCTROW`, both return `-1`.
-
-
- How to Reproduce
Execute the following SQL:
- How to Reproduce
-
```sql
DROP DATABASE IF EXISTS vp_nullif;
CREATE DATABASE vp_nullif;
USE vp_nullif;
CREATE TABLE source (
c0 DATETIME NOT NULL,
UNIQUE KEY(c0)
) ENGINE=InnoDB;
INSERT INTO source VALUES
('2000-01-01 00:00:01'),
('2000-01-01 00:00:02');
CREATE TABLE l (
vp_rowid BIGINT NOT NULL PRIMARY KEY,
c0 DATETIME NOT NULL
) ENGINE=MyISAM;
CREATE TABLE r (
vp_rowid BIGINT NOT NULL PRIMARY KEY
) ENGINE=MyISAM;
INSERT INTO l VALUES
(1,'2000-01-01 00:00:01'),
(2,'2000-01-01 00:00:02');
INSERT INTO r VALUES (1),(2);
– Direct query
SELECT DISTINCTROW c0, c0, NULLIF((- TRUE), c0)
FROM source;
– Rewritten query (using derived table)
SELECT DISTINCTROW c0, c0, NULLIF((- TRUE), c0)
FROM (SELECT c0 FROM source) AS derived;
```
-
-
- Expected Behavior
Whether `DISTINCTROW` is used or not, and whether the query is rewritten with a CTE/derived table or not, `NULLIF((- TRUE), c0)` should return `-1` (since `c0` is a `DATETIME` and can never equal `-1`).
- Expected Behavior
-
-
-
- Actual Behavior
-
- Direct query (with `DISTINCTROW`): third column returns `-1`
- Rewritten query (with `DISTINCTROW` and derived table): third column returns `1`
- Without `DISTINCTROW`, both the direct query and the rewritten query return `-1`