Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL)
-
None
Description
PERCENTILE_DISC() should preserve exact data type of the column specified in the ORDER BY clause. But it does not.
INT variants are erronesouly converted to BIGINT
CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating INT); |
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 5); |
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 3); |
INSERT INTO t1 VALUES ('Lady of the Flies', 1); |
INSERT INTO t1 VALUES ('Lady of the Flies', 2); |
INSERT INTO t1 VALUES ('Lady of the Flies', 5); |
CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) |
WITHIN GROUP (ORDER BY star_rating) |
OVER (PARTITION BY name) AS pc FROM t1; |
SHOW CREATE TABLE t2; |
+-------+---------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`name` char(30) DEFAULT NULL,
|
`pc` bigint(18) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+---------------------------------------------------------------------------------------------------------------------------+
|
Notice, INT was erroneously converted to BIGINT(18).
UNSIGNED INT variants are erronesouly converted to signed BIGINT, data loss is possible
CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating BIGINT UNSIGNED); |
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 0x8000000000000005); |
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 0x8000000000000003); |
INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000001); |
INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000002); |
INSERT INTO t1 VALUES ('Lady of the Flies', 0x8000000000000003); |
CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) |
WITHIN GROUP (ORDER BY star_rating) |
OVER (PARTITION BY name) AS pc FROM t1; |
SHOW CREATE TABLE t2; |
+-------+---------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`name` char(30) DEFAULT NULL,
|
`pc` bigint(18) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+---------------------------------------------------------------------------------------------------------------------------+
|
Notice, the UNSIGNED flag disappeared.
SELECT * FROM t2; |
+-----------------------+----------------------+
|
| name | pc |
|
+-----------------------+----------------------+
|
| Lady of the Flies | -9223372036854775806 |
|
| Lady of the Flies | -9223372036854775806 |
|
| Lady of the Flies | -9223372036854775806 |
|
| Lord of the Ladybirds | -9223372036854775805 |
|
| Lord of the Ladybirds | -9223372036854775805 |
|
+-----------------------+----------------------+
|
Notice, huge unsigned values were converted to their negative couterparts.
FLOAT is erronesouly converted to DOUBLE
CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating FLOAT); |
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 5); |
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 3); |
INSERT INTO t1 VALUES ('Lady of the Flies', 1); |
INSERT INTO t1 VALUES ('Lady of the Flies', 2); |
INSERT INTO t1 VALUES ('Lady of the Flies', 5); |
CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) |
WITHIN GROUP (ORDER BY star_rating) |
OVER (PARTITION BY name) AS pc FROM t1; |
SHOW CREATE TABLE t2; |
+-------+------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`name` char(30) DEFAULT NULL,
|
`pc` double(18,10) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+------------------------------------------------------------------------------------------------------------------------------+
|
DECIMAL is converted to a different precision and scale, data loss is possible
CREATE OR REPLACE TABLE t1 (name CHAR(30), star_rating DECIMAL(30,2)); |
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 50000000000); |
INSERT INTO t1 VALUES ('Lord of the Ladybirds', 30000000000); |
INSERT INTO t1 VALUES ('Lady of the Flies', 10000000000); |
INSERT INTO t1 VALUES ('Lady of the Flies', 20000000000); |
INSERT INTO t1 VALUES ('Lady of the Flies', 50000000000); |
CREATE OR REPLACE TABLE t2 AS SELECT name, PERCENTILE_DISC(0.5) |
WITHIN GROUP (ORDER BY star_rating) |
OVER (PARTITION BY name) AS pc FROM t1; |
SHOW CREATE TABLE t2; |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`name` char(30) DEFAULT NULL,
|
`pc` decimal(16,10) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------+
|
Notice, DECIMAL(30,2) was demoted to DECIMAL(16,10), and the data in t2, it was truncated:
SELECT * FROM t2; |
+-----------------------+-------------------+
|
| name | pc |
|
+-----------------------+-------------------+
|
| Lady of the Flies | 999999.9999999999 |
|
| Lady of the Flies | 999999.9999999999 |
|
| Lady of the Flies | 999999.9999999999 |
|
| Lord of the Ladybirds | 999999.9999999999 |
|
| Lord of the Ladybirds | 999999.9999999999 |
|
+-----------------------+-------------------+
|
Attachments
Issue Links
- relates to
-
-
- Closed
-