Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
1.2.3, 1.2.5
-
None
-
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.
Build tested-
1.2.6-1
[bbokka@cs-tst-02 centos7]$ cat gitversionInfo.txt
engine commit:
30a15a1
Complex SELECT with window functions and subqueries as per the description works fine.
Ex:
[root@localhost ~]# mcsmysql test < t.sql
Followed same steps as per the description.
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;
-----
--------------------------------------------------------------------------
--------------------------------------------------------------------------
---------------------------------------------------------------------1 row in set (0.094 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;
-----
--------------------------------------------------------------------------
--------------------------------------------------------------------------
---------------------------------------------------------------------1 row in set (0.071 sec)
The SELECT query gave the same output for both InnoDB and Columstore engines. Closing the bug.