Tidb UNION ALL with signed and unsigned values resuls are not matching with mysql

[ramanich1]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1;
CREATE TABLE t1(a TINYINT SIGNED,b TINYINT UNSIGNED,c BIGINT UNSIGNED);
INSERT INTO t1 VALUES (127, 255,  18446744073709551615);
SELECT a ,b,c FROM t1
UNION ALL
SELECT b,c,a FROM t1;

2. What did you expect to see? (Required)

--Mysql 5.7
mysql> SELECT a ,b,c FROM t1 UNION ALL SELECT b,c,a FROM t1;
+------+----------------------+----------------------+
| a    | b                    | c                    |
+------+----------------------+----------------------+
|  127 |                  255 | 18446744073709551615 |
|   -1 | 18446744073709551615 |                  127 |
+------+----------------------+----------------------+
2 rows in set (0.00 sec)
--mysql 8.0.27
+------+----------------------+----------------------+
| a    | b                    | c                    |
+------+----------------------+----------------------+
|  127 |                  255 | 18446744073709551615 |
|  255 | 18446744073709551615 |                  127 |
+------+----------------------+----------------------+
2 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql> SELECT a ,b,c FROM t1 UNION ALL SELECT b,c,a FROM t1;
+------+----------------------+------+
| a    | b                    | c    |
+------+----------------------+------+
|  127 |                  255 |   -1 |
|  255 | 18446744073709551615 |  127 |
+------+----------------------+------+
2 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

| Release Version: v5.5.0-alpha-168-g23f7e51ae
Edition: Community
Git Commit Hash: 23f7e51ae01287fa7f811e1462d8987e4d7727a4
Git Branch: master
UTC Build Time: 2022-01-27 17:39:21
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[morgo]

It looks like the problem occurs when TiDB needs column c to have a 64-bit signed value and a 64-bit unsigned value. It selects 64-bit signed, and incorrectly represents 18446744073709551615 as -1.

What MySQL 8.0 does is changes the type to NewDecimal instead, and is able to represent both values correctly.

[zanmato1984]

MySQL:

mysql> SELECT a ,b,c FROM t1 UNION ALL SELECT b,c,a FROM t1;
Field   1:  `a`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       SHORT
Collation:  binary (63)
Length:     4
Max_length: 3
Decimals:   0
Flags:      NUM

Field   2:  `b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 20
Decimals:   0
Flags:      UNSIGNED NUM

Field   3:  `c`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     21
Max_length: 20
Decimals:   0
Flags:      NUM


+------+----------------------+----------------------+
| a    | b                    | c                    |
+------+----------------------+----------------------+
|  127 |                  255 | 18446744073709551615 |
|  255 | 18446744073709551615 |                  127 |
+------+----------------------+----------------------+
2 rows in set (0.00 sec)

TiDB:

mysql> SELECT a ,b,c FROM t1 UNION ALL SELECT b,c,a FROM t1;
Field   1:  `a`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       TINY
Collation:  binary (63)
Length:     4
Max_length: 3
Decimals:   0
Flags:      BINARY NUM

Field   2:  `b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 20
Decimals:   0
Flags:      UNSIGNED BINARY NUM

Field   3:  `c`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     20
Max_length: 3
Decimals:   0
Flags:      BINARY NUM


+------+----------------------+------+
| a    | b                    | c    |
+------+----------------------+------+
|  127 |                  255 |   -1 |
|  255 | 18446744073709551615 |  127 |
+------+----------------------+------+
2 rows in set (0.01 sec)

The third column type is inferred by TiDB as LONGLONG rather than MySQL's NEWDECIMAL. I think this is a type-inference issue so I'm changing the labels.