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:	23 Aug 12:40
Reporter:	cl hl	Email Updates:
Status:	Open	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;
```