This appears to be tied up in the 18 digit limitation for columnstore decimals as if you change salary to be decimal(12,6) in the table definition then the query works. MCOL-641 tracks supporting the full range of decimals however in the interim it would be better to raise a runtime error if this case occurs so at least the user knows there is an issue rather than have incorrect results.

It actually looks like there is a larger issue with handling of decimal columns across cross engine join:
We ran some tests to verify what you said about precision limits, and it looks like the issue is not just the precision. Take a look at this test case:

CREATE TABLE employees (
`id` INT NOT NULL,
`name` VARCHAR(255) NOT NULL
) ENGINE=Columnstore;

CREATE TABLE roles (
`employee_id` INT NOT NULL,
`role` VARCHAR(255) NOT NULL,
`salary` DECIMAL(6,4) NOT NULL DEFAULT '9.9'
) ENGINE=InnoDB;

– Result: salary values: (0.0500, 0.0500, 0.0100)
– Expected: salary values: (0.0500, 2.0000, 0.0100)
SELECT *
FROM employees
JOIN roles ON employees.id = roles.employee_id;

– Result: SUM(salary): (0.0500, 0.0100). The value 2.0000 is ignored.
– Expected: SUM(salary): (0.0500, 2.0100).
SELECT employees.id, COUNT, SUM(salary)
FROM employees
JOIN roles ON employees.id = roles.employee_id
GROUP BY employees.id;

If you change the DECIMAL specification, there are some values that work, such as DECIMAL(6,2) or DECIMAL(7,4). Does it make any sense?

A workaround for now is to switch the innodb column type from decimal to double.

We should consider a proper fix for this rather than erroring.

The cause is a bad precision/scale for the result column, the fix is to use the precision/scale from the cross engine results instead.

Pull request for 1.0 and 1.1. The pull requests are slightly different as 1.0 uses libdrizzle whereas 1.1 uses MairaDB Connector/C

Builds verified: Github source 1.0.10, 1.1.0

1.0.10

[root@localhost mariadb-columnstore-server]# git show
commit 478209c9d58e0c34d0a177b39b42ed865ad30ccf
Author: David Hill <david.hill@mariadb.com>
Date: Thu May 18 15:11:05 2017 -0500

[root@localhost mariadb-columnstore-engine]# git show
commit 4d5a59d3d104cda2a7715b4e619318e43e964214
Author: david hill <david.hill@mariadb.com>
Date: Fri Jun 2 11:03:12 2017 -0500

1.1.0
[root@localhost mariadb-columnstore-server]# git show
commit e2d23d2639f5ac181932b523dbf8953451889b3e
Author: david hill <david.hill@mariadb.com>
Date: Thu Jun 1 09:32:54 2017 -0500

[root@localhost mariadb-columnstore-engine]# git show
commit cf1cc5b42f5d879e9fe91e65c18eed28515f173f
Merge: 5fe3689 be59506
Author: david hill <david.hill@mariadb.com>
Date: Fri Jun 2 11:02:09 2017 -0500

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

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

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

MariaDB [mytest]> CREATE TABLE employees (
-> `id` INT NOT NULL,
-> `name` VARCHAR(255) NOT NULL
-> ) ENGINE=Columnstore;
Query OK, 0 rows affected (0.52 sec)

MariaDB [mytest]>
MariaDB [mytest]> CREATE TABLE roles (
-> `employee_id` INT NOT NULL,
-> `role` VARCHAR(255) NOT NULL,
-> `salary` DECIMAL(12,8) NOT NULL DEFAULT '9.9'
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

MariaDB [mytest]> INSERT INTO employees (`id`, `name`) VALUES (1, 'John'), (2, 'Mary');
Query OK, 2 rows affected (0.27 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [mytest]> INSERT INTO roles (`employee_id`, `role`, `salary`) VALUES (1, 'Programmer', 3.33), (2, 'DBA', 4.57);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [mytest]> SELECT * FROM roles;
---------------------------------

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

---------------------------------
2 rows in set (0.00 sec)

MariaDB [mytest]> SELECT name, salary FROM employees JOIN roles on employees.id = roles.employee_id;
----------------+

----------------+

----------------+
2 rows in set (0.28 sec)