[#MCOL-5205] UNION on signed and unsigned integer types returns incorrect results

Here is an example SQL script with wrong outputs when UNOIN of MCS computes with signed and unsigned integers. The results are all wrong.

use mysql;

SET default_storage_engine=ColumnStore;

CREATE TABLE t1 (a SMALLINT);

INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFFFF8000 AS SIGNED)+2),(-1),(0),(1),(0x7FFF);

CREATE TABLE t2 (a INT UNSIGNED);

INSERT INTO t2 VALUES (0),(1),(0xFFFF-2);

SELECT * FROM ((SELECT a FROM t1 UNION ALL SELECT a FROM t2) as tu) ORDER BY a;

SELECT * FROM ((SELECT a FROM t2 UNION ALL SELECT a FROM t1) as tu) ORDER BY a;

+-------+

| a     |

+-------+

| 0     |

| 0     |

| 1     |

| 1     |

| 32767 |

| 32770 |

| 65533 |

| 65535 |

+-------+

8 rows in set

Time: 0.013s

+-------+

| a     |

+-------+

| 0     |

| 0     |

| 1     |

| 1     |

| 32767 |

| 32770 |

| 65533 |

| 65535 |

+-------+

8 rows in set

Time: 0.015s

The correct output should be the same under the innodb engine, so we change the second line in the script above to `SET default_storage_engine=InnoDB;`:

MariaDB [mysql]> SELECT * FROM ((SELECT a FROM t1 UNION ALL SELECT a FROM t2) as tu) ORDER BY a;

+--------+

| a      |

+--------+

| -32766 |

|     -1 |

|      0 |

|      0 |

|      1 |

|      1 |

|  32767 |

|  65533 |

+--------+

8 rows in set (0.000 sec)

MariaDB [mysql]> SELECT * FROM ((SELECT a FROM t2 UNION ALL SELECT a FROM t1) as tu) ORDER BY a;

+--------+

| a      |

+--------+

| -32766 |

|     -1 |

|      0 |

|      0 |

|      1 |

|      1 |

|  32767 |

|  65533 |

+--------+

8 rows in set (0.001 sec)

[MCOL-5205] UNION on signed and unsigned integer types returns incorrect results Created: 2022-08-23 Updated: 2023-02-28 Resolved: 2023-02-28
Status:	Closed
Project:	MariaDB ColumnStore
Component/s:	MDB Plugin
Affects Version/s:	None
Fix Version/s:	23.02.1

[MCOL-5205] UNION on signed and unsigned integer types returns incorrect results Created: 2022-08-23 Updated: 2023-02-28 Resolved: 2023-02-28