Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-3423

Wrong results for complex query with subquery and window functions over decimal(12,4) column

    XMLWordPrintable

    Details

    • Sprint:
      2019-06

      Description

      Consider the fol,lowing tables (dump of InnoDB table t will be attached):

      MariaDB [test]> show create table t\G
      *************************** 1. row ***************************
             Table: t
      Create Table: CREATE TABLE `t` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `cid` int(11) DEFAULT NULL,
        `d` datetime DEFAULT NULL,
        `c` decimal(12,4) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=196592 DEFAULT CHARSET=latin1
      1 row in set (0,000 sec)
       
      MariaDB [test]> show create table tc\G
      *************************** 1. row ***************************
             Table: tc
      Create Table: CREATE TABLE `tc` (
        `id` int(11) NOT NULL,
        `cid` int(11) DEFAULT NULL,
        `d` datetime DEFAULT NULL,
        `c` decimal(12,4) DEFAULT NULL
      ) ENGINE=Columnstore DEFAULT CHARSET=latin1
      1 row in set (0,000 sec)
       
      MariaDB [test]> select count(*) from t;
      +----------+
      | count(*) |
      +----------+
      |   131072 |
      +----------+
      1 row in set (0,100 sec)
       
      MariaDB [test]> delete from tc;
      Query OK, 131072 rows affected (3,930 sec)
       
      MariaDB [test]> insert into tc select * from t;
      Query OK, 131072 rows affected (8,289 sec)
      Records: 131072  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> pager md5sum
      PAGER set to 'md5sum'
      MariaDB [test]> select * from t order by id;
      75092d9f7a63a7489c1f2ea5aea916aa  -
      131072 rows in set (0,189 sec)
       
      MariaDB [test]> select * from tc order by id;
      75092d9f7a63a7489c1f2ea5aea916aa  -
      131072 rows in set (0,963 sec)
      

      They have the same data based on SELECTs above. Now let me apply a bit complex SELECT with window functions and subqueries:

      MariaDB [test]> select cust, week_d, mpp_sum , stddev(mpp_sum) over (partition by cust) as mpp_stdev , avg(mpp_sum) over (partition by cust) as mpp_mean , sum(mpp_sum) over (partition by cust) as glob_mpp_sum,  count(mpp_sum) over (partition by cust) as glob_mpp_count from  (select distinct cid as cust,  week(d) as week_d , sum(c) over (partition by cid, week(d)) as mpp_sum  from t where year(d) = '2018') as inside where cust = 0 and week_d = 0;
      +------+--------+-------------+------------+-----------------+--------------+----------------+
      | cust | week_d | mpp_sum     | mpp_stdev  | mpp_mean        | glob_mpp_sum | glob_mpp_count |
      +------+--------+-------------+------------+-----------------+--------------+----------------+
      |    0 |      0 | 103715.8800 | 0.00000000 | 103715.88000000 |  103715.8800 |              1 |
      +------+--------+-------------+------------+-----------------+--------------+----------------+
      1 row in set (0,096 sec)
       
      MariaDB [test]> select cust, week_d, mpp_sum , stddev(mpp_sum) over (partition by cust) as mpp_stdev , avg(mpp_sum) over (partition by cust) as mpp_mean , sum(mpp_sum) over (partition by cust) as glob_mpp_sum,  count(mpp_sum) over (partition by cust) as glob_mpp_count from  (select distinct cid as cust,  week(d) as week_d , sum(c) over (partition by cid, week(d)) as mpp_sum  from tc where year(d) = '2018') as inside where cust = 0 and week_d = 0;
      +------+--------+-------------+------------+-------------+--------------+----------------+
      | cust | week_d | mpp_sum     | mpp_stdev  | mpp_mean    | glob_mpp_sum | glob_mpp_count |
      +------+--------+-------------+------------+-------------+--------------+----------------+
      |    0 |      0 | 103715.8800 | 0.00000000 | 10.37158800 |      10.3716 |              1 |
      +------+--------+-------------+------------+-------------+--------------+----------------+
      1 row in set (0,052 sec)
      

      You can see that the values for Columnstore table tc are different (and wrong) in some columns.

        Attachments

          Activity

            People

            Assignee:
            bharath.bokka Bharath Bokka
            Reporter:
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: