[MCOL-653] sum function provides different results between innodb and columnstore Created: 2017-03-31  Updated: 2017-04-05  Resolved: 2017-04-05

Status: Closed
Project: MariaDB ColumnStore
Component/s: MDB Plugin
Affects Version/s: 1.0.8, 1.1.0
Fix Version/s: 1.0.9, 1.1.0

Type: Bug Priority: Critical
Reporter: David Hill (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

from user, reproduced a single-server google cloud

Sprint: 2017-6, 2017-7

 Description   

CREATE SCHEMA roche_dw;

CREATE TABLE roche_dw.authorsr (id INT, name VARCHAR(20), email VARCHAR(20));
INSERT INTO roche_dw.authorsr (id,name,email) VALUES(1,"Vivek","xuz@abc.com");
INSERT INTO roche_dw.authorsr (id,name,email) VALUES(2,"kaka","auz@abc.com");
INSERT INTO roche_dw.authorsr (id,name,email) VALUES(3,"ddd","eee@abc.com");
INSERT INTO roche_dw.authorsr (id,name,email) VALUES(4,"ssss","aaa@abc.com");

CREATE TABLE roche_dw.authorsc (id INT, name VARCHAR(20), email VARCHAR(20)) engine=columnstore;
INSERT INTO roche_dw.authorsc (id,name,email) VALUES(1,"Vivek","xuz@abc.com");
INSERT INTO roche_dw.authorsc (id,name,email) VALUES(2,"kaka","auz@abc.com");
INSERT INTO roche_dw.authorsc (id,name,email) VALUES(3,"ddd","eee@abc.com");
INSERT INTO roche_dw.authorsc (id,name,email) VALUES(4,"ssss","aaa@abc.com");

select sum(1) from roche_dw.authorsr;
select sum(1) from roche_dw.authorsc;

---------------------------------------------------------------------------------------------------------------------

MariaDB [david]> CREATE SCHEMA roche_dw;
Query OK, 1 row affected (0.00 sec)

MariaDB [david]>
MariaDB [david]> CREATE TABLE roche_dw.authorsr (id INT, name VARCHAR(20), email VARCHAR(20));
Query OK, 0 rows affected (0.05 sec)

MariaDB [david]> INSERT INTO roche_dw.authorsr (id,name,email) VALUES(1,"Vivek","xuz@abc.com");
Query OK, 1 row affected (0.01 sec)

MariaDB [david]> INSERT INTO roche_dw.authorsr (id,name,email) VALUES(2,"kaka","auz@abc.com");
Query OK, 1 row affected (0.02 sec)

MariaDB [david]> INSERT INTO roche_dw.authorsr (id,name,email) VALUES(3,"ddd","eee@abc.com");
Query OK, 1 row affected (0.00 sec)

MariaDB [david]> INSERT INTO roche_dw.authorsr (id,name,email) VALUES(4,"ssss","aaa@abc.com");
Query OK, 1 row affected (0.00 sec)

MariaDB [david]>
MariaDB [david]>
MariaDB [david]>
MariaDB [david]> CREATE TABLE roche_dw.authorsc (id INT, name VARCHAR(20), email VARCHAR(20)) engine=columnstore;
Query OK, 0 rows affected (2.55 sec)

MariaDB [david]> INSERT INTO roche_dw.authorsc (id,name,email) VALUES(1,"Vivek","xuz@abc.com");
Query OK, 1 row affected (0.22 sec)

MariaDB [david]> INSERT INTO roche_dw.authorsc (id,name,email) VALUES(2,"kaka","auz@abc.com");
Query OK, 1 row affected (0.09 sec)

MariaDB [david]> INSERT INTO roche_dw.authorsc (id,name,email) VALUES(3,"ddd","eee@abc.com");
Query OK, 1 row affected (0.09 sec)

MariaDB [david]> INSERT INTO roche_dw.authorsc (id,name,email) VALUES(4,"ssss","aaa@abc.com");
Query OK, 1 row affected (0.09 sec)

MariaDB [david]>
MariaDB [david]>
MariaDB [david]> select sum(1) from roche_dw.authorsr;
--------

sum(1)

--------

4

--------
1 row in set (0.02 sec)

MariaDB [david]> select sum(1) from roche_dw.authorsc;
--------

sum(1)

--------

1

--------
1 row in set (0.09 sec)

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2017-03-31 ]

Regression caused by bad behaviour assumption in MCOL-301

Comment by Andrew Hutchings (Inactive) [ 2017-03-31 ]

Reverted my MCOL-301 change, develop and develop-1.0 pull request open. The regression suite results will need updating too.

Comment by Andrew Hutchings (Inactive) [ 2017-03-31 ]

Workaround for now would be using 

count(*)

instead of sum(1)

Comment by Daniel Lee (Inactive) [ 2017-04-04 ]

Build tested: 1.0.9-1 (branch develop-1.0)
[root@localhost mariadb-columnstore-server]# git show
commit 8ef673b333f79586645321a60e1ee3c1bcfc491c
Merge: e688674 7e877fa
Author: dhall-InfiniDB <david.hall@mariadb.com>
Date: Mon Apr 3 09:51:52 2017 -0500

Merge pull request #39 from mariadb-corporation/MCOL-652-1.0

MCOL-652 Fix certain derived table cases

[root@localhost mariadb-columnstore-engine]# git show
commit 1c2e9bd8281c5ad717820b7ec0bc234c47321b22
Merge: 310f8e5 b60c45a
Author: dhall-InfiniDB <david.hall@mariadb.com>
Date: Mon Apr 3 09:47:57 2017 -0500

Merge pull request #144 from mariadb-corporation/MCOL-653-1.0

MCOL-653 Revert SUM(1) behaviour

MariaDB [roche_dw]> select sum(1) from roche_dw.authorsr;
--------

sum(1)

--------

4

--------
1 row in set (0.00 sec)

MariaDB [roche_dw]> select sum(1) from roche_dw.authorsc;
--------

sum(1)

--------

4

--------
1 row in set (0.12 sec)

Comment by Daniel Lee (Inactive) [ 2017-04-05 ]

Build verified: 1.1.0-1 (GitHub develop branch)

Author: dhall-InfiniDB <david.hall@mariadb.com>
Date: Mon Apr 3 09:52:07 2017 -0500

Merge pull request #38 from mariadb-corporation/MCOL-652

MCOL-652 Fix certain derived table cases

[root@localhost mariadb-columnstore-server]# cd mariadb-columnstore-engine/
[root@localhost mariadb-columnstore-engine]# git show
commit 3e85b6ef07ffcfb4edc84e170efcccdb9cd15cc2
Merge: 04bdb60 8baf059
Author: dhall-InfiniDB <david.hall@mariadb.com>
Date: Mon Apr 3 09:48:55 2017 -0500

Merge pull request #143 from mariadb-corporation/MCOL-653

MCOL-653 Revert SUM(1) behaviour

[root@localhost ~]# mcsmysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.1.21-MariaDB Columnstore 1.1.0-1

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE SCHEMA roche_dw;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> CREATE TABLE roche_dw.authorsr (id INT, name VARCHAR(20), email VARCHAR(20));
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> INSERT INTO roche_dw.authorsr (id,name,email) VALUES(1,"Vivek","xuz@abc.com");
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> INSERT INTO roche_dw.authorsr (id,name,email) VALUES(2,"kaka","auz@abc.com");
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> INSERT INTO roche_dw.authorsr (id,name,email) VALUES(3,"ddd","eee@abc.com");
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> INSERT INTO roche_dw.authorsr (id,name,email) VALUES(4,"ssss","aaa@abc.com");
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> CREATE TABLE roche_dw.authorsc (id INT, name VARCHAR(20), email VARCHAR(20)) engine=columnstore;
Query OK, 0 rows affected (0.27 sec)

MariaDB [(none)]> INSERT INTO roche_dw.authorsc (id,name,email) VALUES(1,"Vivek","xuz@abc.com");
Query OK, 1 row affected (0.54 sec)

MariaDB [(none)]> INSERT INTO roche_dw.authorsc (id,name,email) VALUES(2,"kaka","auz@abc.com");
Query OK, 1 row affected (0.15 sec)

MariaDB [(none)]> INSERT INTO roche_dw.authorsc (id,name,email) VALUES(3,"ddd","eee@abc.com");
Query OK, 1 row affected (0.13 sec)

MariaDB [(none)]> INSERT INTO roche_dw.authorsc (id,name,email) VALUES(4,"ssss","aaa@abc.com");
Query OK, 1 row affected (0.22 sec)

MariaDB [(none)]> select sum(1) from roche_dw.authorsr;
--------

sum(1)

--------

4

--------
1 row in set (0.00 sec)

MariaDB [(none)]> select sum(1) from roche_dw.authorsc;
--------

sum(1)

--------

4

--------
1 row in set (0.08 sec)

Generated at Thu Feb 08 02:22:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.