Bug #116211 zero value of year does not display as 4 characters in union clause

• Submitted: 24 Sep 8:14
• Modified: 24 Sep 9:00
• Reporter: ximin liang (OCA)
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 9.0.1, 8.4.2, 8.0.39
• OS: Any
• CPU Architecture: Any

[24 Sep 8:14] ximin liang

Description:
In doc: https://dev.mysql.com/doc/refman/9.0/en/year.html
`Year` data type should be display as 4 character, here is
a example with union:

How to repeat:
Can be repeated in 9.0.1:

create table t1(c1 varchar(2));
create table t2(c2 year);
insert into t2 values (0);
(select c1 from t1) union (select c2 from t2);
+------+
| c1   |
+------+
| 0    |
+------+
mysql> select c2 from t2;
+------+
| c2   |
+------+
| 0000 |
+------+
1 row in set (0.00 sec)

Notice that in 8.0.24, result is '0000'.

Suggested fix:
It is about implement of union and Streaming iterator.
Union create string data type for temp table using c1 of t1.
When read rows from t2 and write data to tmp table fields, it write integer 0.

[24 Sep 8:57] ximin liang

the contents in `Suggested fix` is for sql `(select c1 from t1) union all (select c2 from t2)`

[24 Sep 9:00] MySQL Verification Team

Hello ximin liang,

Thank you for the report and test case.

regards,
Umesh

[26 Sep 11:19] Roy Lyseng

Posted by developer:
 
It is a minor problem with a reasonable workaround:

select c1 from t1 union select cast(c2 as char(4)) from t2;