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

    • 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

        1. t.sql.gz
          1.39 MB
          Valerii Kravchuk

        Activity

          People

            bharath.bokka Bharath Bokka (Inactive)
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.