By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- View base column sizes
create table tmp as (select 19000+1000 ,020000, 20000 ,2.222) ;
-- Third column truncated in MySQL 8.0, but not MariaDB 10.2
create table tmp2 as (select 19000+1000 , 020000, 20000 ,2 ) union( select 2.222 , 2.222 , 2.222 , 2.222 )
-- OK in both as expected, but big difference in column sizes
create table tmp3 as (select 2.222 as a , 2.222 as b , 2.222 as c , 2.222 as d )union (select 19000+1000 , 020000, 20000 ,2.2 )
describe tmp;
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| 19000+1000 | int(7) | NO | 0 | ||
| 020000 | int(6) | NO | 0 | ||
| 20000 | int(5) | NO | 0 | ||
| 2.222 | decimal(4,3) | NO | 0.000 |
select * from tmp
| 19000+1000 | 020000 | 20000 | 2.222 |
|---|---|---|---|
| 20000 | 20000 | 20000 | 2.222 |
describe tmp2
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| 19000+1000 | decimal(9,3) | NO | 0.000 | ||
| 020000 | decimal(8,3) | NO | 0.000 | ||
| 20000 | decimal(7,3) | NO | 0.000 | ||
| 2 | decimal(4,3) | NO | 0.000 |
select * from tmp2
| 19000+1000 | 020000 | 20000 | 2 |
|---|---|---|---|
| 20000.000 | 20000.000 | 9999.999 | 2.000 |
| 2.222 | 2.222 | 2.222 | 2.222 |
describe tmp3
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| a | decimal(9,3) | NO | 0.000 | ||
| b | decimal(9,3) | NO | 0.000 | ||
| c | decimal(8,3) | NO | 0.000 | ||
| d | decimal(4,3) | NO | 0.000 |
select * from tmp3
| a | b | c | d |
|---|---|---|---|
| 2.222 | 2.222 | 2.222 | 2.222 |
| 20000.000 | 20000.000 | 20000.000 | 2.200 |