Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
Description
This problem was found by tntnatbry while working on MCOL-4612 and MCOL-4613.
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a INT, b INT UNSIGNED) ENGINE=ColumnStore; |
INSERT INTO t1 VALUES (-1,1); |
SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t1) tu; |
+------------+
|
| a |
|
+------------+
|
| 4294967295 |
|
| 1 |
|
+------------+
|
Looks wrong. The expected result would be:
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a INT, b INT UNSIGNED) ENGINE=InnoDB; |
INSERT INTO t1 VALUES (-1,1); |
SELECT * FROM (SELECT a FROM t1 UNION SELECT b FROM t1) tu; |
{code:sql}
|
+------+ |
| a |
|
+------+ |
| -1 |
|
| 1 |
|
+------+ |
The UNION code attempts to figure a common data type and translate the data to that for comparison. In this case, the common data type is UNSIGNED, so the result is unsigned. Any result column can only have one data type. Our code will not support a different type for different rows. If we choose INT as the common type, the result would be incorrect for a different data set.
A potential fix is to choose a larger data type like BIGINT to hold the result. This would be large enough to handle the largest UNSIGNED and still handle negative numbers. It becomes a little more problematic if one of the types is UNSIGNED BIGINT. Perhaps we could do something with int128_t.