Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
Description
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) |
Attachments
Issue Links
- relates to
-
-
- Closed
-