Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20278

PERCENTILE_DISC() returns a wrong data type

    Details

      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

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: