MySQL Bugs: #121160: VIEW materialization changes AVG(INT) decimal scale across UNION ALL

Bug #121160	VIEW materialization changes AVG(INT) decimal scale across UNION ALL
Submitted:	23 Aug 7:40	Modified:	31 Aug 20:43
Reporter:	cl hl	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	9.7.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL can return different numeric precision for the same `AVG(INT)` expression
when one `UNION ALL` branch is materialized as a view. The direct query returns
six fractional digits, while selecting the equivalent expression through the
view returns four fractional digits.

The row count and grouping keys are unchanged; only the `DECIMAL` scale of the
aggregate result changes across the view boundary.

## Expected result

Both queries should return the same values:

```text
sample_5  41.357143
sample_p  44.545455
```

## Actual result

The direct `UNION ALL` returns:

```text
sample_5  41.357143
sample_p  44.545455
```

After materializing the second branch as a view, the result is:

```text
sample_5  41.3571
sample_p  44.5455
```

How to repeat:
The affected column is an `INT` column:

```sql
CREATE TABLE t1 (
    c1 INT PRIMARY KEY,
    c4 INT,
    c6 VARCHAR(10)
);
```

Direct query:

```sql
SELECT t1.c10 AS q1_col_1, PI() AS q1_col_2
FROM t3 AS t1
WHERE t1.c3 <> 12 AND t1.c12 NOT LIKE 'sample_83%'
UNION ALL
SELECT t2.c6 AS q2_col_1, AVG(t2.c4) AS q2_col_2
FROM t1 AS t2
WHERE EXISTS (
    SELECT t3.c2
    FROM t1 AS t3
    WHERE t3.c1 BETWEEN 37 AND 84
)
GROUP BY t2.c6;
```

Materialized branch:

```sql
CREATE VIEW vect_cut_005 AS
SELECT t2.c6 AS q2_col_1, AVG(t2.c4) AS q2_col_2
FROM t1 AS t2
WHERE EXISTS (
    SELECT t3.c2
    FROM t1 AS t3
    WHERE t3.c1 BETWEEN 37 AND 84
)
GROUP BY t2.c6;

SELECT t1.c10 AS q1_col_1, PI() AS q1_col_2
FROM t3 AS t1
WHERE t1.c3 <> 12 AND t1.c12 NOT LIKE 'sample_83%'
UNION ALL
SELECT input.q2_col_1, input.q2_col_2
FROM vect_cut_005 AS input;
```

Please check your test case, I think it refers to unknown columns.

Use these SQL to reproduce this issue

```sql
DROP DATABASE IF EXISTS round5;
CREATE DATABASE round5;
USE round5;

CREATE TABLE t1 (
    c1 INT PRIMARY KEY,
    c2 VARCHAR(255),
    c3 VARCHAR(255),
    c4 INT,
    c5 DATE,
    c6 VARCHAR(10)
);

CREATE TABLE t3 (
    c1 INT PRIMARY KEY,
    c3 INT,
    c10 LONGTEXT,
    c12 TINYTEXT
);

INSERT INTO t1 (c1, c4, c6) VALUES
    (20, 5, 'sample_5'),
    (52, 14, 'sample_5'),
    (199, 21, 'sample_5'),
    (215, 89, 'sample_5'),
    (284, 0, 'sample_5'),
    (325, 69, 'sample_5'),
    (359, 93, 'sample_5'),
    (491, 2, 'sample_5'),
    (580, 84, 'sample_5'),
    (725, 22, 'sample_5'),
    (745, 23, 'sample_5'),
    (749, 59, 'sample_5'),
    (850, 72, 'sample_5'),
    (889, 26, 'sample_5'),
    (235, 18, 'sample_p'),
    (249, 6, 'sample_p'),
    (332, 91, 'sample_p'),
    (394, 29, 'sample_p'),
    (435, 98, 'sample_p'),
    (559, 63, 'sample_p'),
    (563, 36, 'sample_p'),
    (586, 12, 'sample_p'),
    (605, 71, 'sample_p'),
    (669, 63, 'sample_p'),
    (698, 3, 'sample_p');
```

`t3` is intentionally empty. Its branch returns no rows, but the `PI()`
expression still participates in `UNION ALL` result type negotiation.

Direct query:

```sql
SELECT t1.c10 AS q1_col_1, PI() AS q1_col_2
FROM t3 AS t1
WHERE t1.c3 <> 12 AND t1.c12 NOT LIKE 'sample_83%'
UNION ALL
SELECT t2.c6 AS q2_col_1, AVG(t2.c4) AS q2_col_2
FROM t1 AS t2
WHERE EXISTS (
    SELECT t3.c1
    FROM t1 AS t3
    WHERE t3.c1 BETWEEN 37 AND 84
)
GROUP BY t2.c6;
```

Materialized branch:

```sql
CREATE VIEW vect_cut_005 AS
SELECT t2.c6 AS q2_col_1, AVG(t2.c4) AS q2_col_2
FROM t1 AS t2
WHERE EXISTS (
    SELECT t3.c1
    FROM t1 AS t3
    WHERE t3.c1 BETWEEN 37 AND 84
)
GROUP BY t2.c6;

SELECT t1.c10 AS q1_col_1, PI() AS q1_col_2
FROM t3 AS t1
WHERE t1.c3 <> 12 AND t1.c12 NOT LIKE 'sample_83%'
UNION ALL
SELECT input.q2_col_1, input.q2_col_2
FROM vect_cut_005 AS input;
```

Thank you for the bug report.
However, this is not a bug.
The result of AVG is a decimal number with 4 significant fractional digits.
The two queries have slightly different implementations. In one case, materialization is used, and the AVG result is cut according to the specified fraction.
But in the second case, no materialization is used, and since the decimal operation is actually carried out using 9 significant fraction digits, the full precision result is preserved into the UNION.

The best way to avoid this issue is rewriting AVG(t2.c4) as AVG(CAST(t2.c4 AS DOUBLE)), which will ensure a double precision result with full precision.