Description:
The following query returns an incorrect result when `DISTINCTROW` is used.
The scalar subquery `(SELECT 'str4' FROM t0)` returns the constant string `'str4'` because `t0` contains exactly one row. Therefore, the expression
```sql
REPEAT((SELECT 'str4' FROM t0), 2)
```
should evaluate to:
```text
str4str4
```
However, when `DISTINCTROW` is added to the outer query, MySQL returns:
```text
str4str4str4str4
```
In contrast, removing `DISTINCTROW` produces the expected result:
```text
str4str4
```
`DISTINCTROW` is a synonym for `DISTINCT`, so it should only remove duplicate result rows; it should not change the value produced by the `REPEAT()` expression.
This indicates that the presence of `DISTINCTROW` may incorrectly affect the evaluation of the scalar subquery or the expression containing it, resulting in an incorrectly repeated value.
How to repeat:
```sql
DROP TABLE IF EXISTS t0;
CREATE TABLE t0
(
c0 DECIMAL NULL,
c1 DECIMAL NULL
);
INSERT INTO t0 (c0, c1)
VALUES (NULL, NULL);
```
**Query 1 — with DISTINCTROW**
```sql
SELECT DISTINCTROW
REPEAT((SELECT 'str4' FROM t0), 2) AS ref0
FROM t0;
```
**Actual result:**
```text
ref0
----------------
str4str4str4str4
```
**Expected result:**
```text
ref0
----------------
str4str4
```
**Query 2 — without DISTINCTROW**
```sql
SELECT
REPEAT((SELECT 'str4' FROM t0), 2) AS ref0
FROM t0;
```
**Actual result:**
```text
ref0
----------------
str4str4
```
This demonstrates that adding `DISTINCTROW` changes the value of the expression itself, rather than merely removing duplicate rows.
The result is therefore inconsistent between semantically equivalent queries that differ only by the `DISTINCTROW` modifier.
Description: The following query returns an incorrect result when `DISTINCTROW` is used. The scalar subquery `(SELECT 'str4' FROM t0)` returns the constant string `'str4'` because `t0` contains exactly one row. Therefore, the expression ```sql REPEAT((SELECT 'str4' FROM t0), 2) ``` should evaluate to: ```text str4str4 ``` However, when `DISTINCTROW` is added to the outer query, MySQL returns: ```text str4str4str4str4 ``` In contrast, removing `DISTINCTROW` produces the expected result: ```text str4str4 ``` `DISTINCTROW` is a synonym for `DISTINCT`, so it should only remove duplicate result rows; it should not change the value produced by the `REPEAT()` expression. This indicates that the presence of `DISTINCTROW` may incorrectly affect the evaluation of the scalar subquery or the expression containing it, resulting in an incorrectly repeated value. How to repeat: ```sql DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 DECIMAL NULL, c1 DECIMAL NULL ); INSERT INTO t0 (c0, c1) VALUES (NULL, NULL); ``` **Query 1 — with DISTINCTROW** ```sql SELECT DISTINCTROW REPEAT((SELECT 'str4' FROM t0), 2) AS ref0 FROM t0; ``` **Actual result:** ```text ref0 ---------------- str4str4str4str4 ``` **Expected result:** ```text ref0 ---------------- str4str4 ``` **Query 2 — without DISTINCTROW** ```sql SELECT REPEAT((SELECT 'str4' FROM t0), 2) AS ref0 FROM t0; ``` **Actual result:** ```text ref0 ---------------- str4str4 ``` This demonstrates that adding `DISTINCTROW` changes the value of the expression itself, rather than merely removing duplicate rows. The result is therefore inconsistent between semantically equivalent queries that differ only by the `DISTINCTROW` modifier.