MariaDB Syntax is covered here: https://mariadb.com/kb/en/mariadb/select-with-rollup/ and should be considered the reference point for behavior / syntax.

This item is being closed because it was well passed the expiration date with no activity. If you suspect this was done in error please create a new ticket.

I have reopened this ticket as this affects TPC-DS query67.

sergey.zefirov Could you add your suggestions for QA on how to test this feature.

sergey.zefirov Found problems with union all for MCS+MDB tables:

DROP DATABASE IF EXISTS mcs84_db;
CREATE DATABASE mcs84_db;
USE mcs84_db;

CREATE TABLE booksales (country CHAR(35), genre CHAR(15), year INT, sales INT) ENGINE=Columnstore;

INSERT INTO booksales VALUES
('Senegal','fiction',2014,12234), ('Senegal','fiction',2015,15647),
('Senegal','non-fiction',2014,64980), ('Senegal','non-fiction',2015,78901),
('Paraguay','fiction',2014,87970), ('Paraguay','fiction',2015,76940),
('Paraguay','non-fiction',2014,8760), ('Paraguay','non-fiction',2015,9030);

CREATE TABLE booksales3 (country3 CHAR(35), genre3 CHAR(15), year3 INT, sales3 INT);

INSERT INTO booksales3 VALUES
('Senegal','fiction',2014,12234), ('Senegal','fiction',2015,15647),
('Senegal','non-fiction',2014,64980), ('Senegal','non-fiction',2015,78901),
('Paraguay','fiction',2014,87970), ('Paraguay','fiction',2015,76940),
('Paraguay','non-fiction',2014,8760), ('Paraguay','non-fiction',2015,9030);

SELECT country, year, genre, SUM(sales) FROM booksales GROUP BY country, year, genre WITH ROLLUP UNION all SELECT country3, year3, genre3, SUM(sales3) FROM booksales3 GROUP BY country3,year3,genre3 WITH ROLLUP;

returns:

country year genre SUM(sales)
NULL NULL NULL 354462
Paraguay 2014 NULL 96730
Paraguay 2014 fiction 87970
Paraguay 2014 fiction 87970
Paraguay 2014 non-fiction 8760
Paraguay 2014 non-fiction 8760
Paraguay 2015 NULL 85970
Paraguay 2015 fiction 76940
Paraguay 2015 fiction 76940
Paraguay 2015 non-fiction 9030
Paraguay 2015 non-fiction 9030
Paraguay NULL NULL 182700
Senegal 2014 NULL 77214
Senegal 2014 fiction 12234
Senegal 2014 fiction 12234
Senegal 2014 non-fiction 64980
Senegal 2014 non-fiction 64980
Senegal 2015 NULL 94548
Senegal 2015 fiction 15647
Senegal 2015 fiction 15647
Senegal 2015 non-fiction 78901
Senegal 2015 non-fiction 78901
Senegal NULL NULL 171762

'normal' rows doubled by union all
but rollup rows not

if both tables are MDB or both MCS - rollup rows are doubled

sergey.zefirov second problem with union of 2 MCS tables:

DROP DATABASE IF EXISTS mcs84_db;
CREATE DATABASE mcs84_db;
USE mcs84_db;

CREATE TABLE booksales (country CHAR(35), genre CHAR(15), year INT, sales INT) ENGINE=Columnstore;

INSERT INTO booksales VALUES
('Senegal','fiction',2014,12234), ('Senegal','fiction',2015,15647),
('Senegal','non-fiction',2014,64980), ('Senegal','non-fiction',2015,78901),
('Paraguay','fiction',2014,87970), ('Paraguay','fiction',2015,76940),
('Paraguay','non-fiction',2014,8760), ('Paraguay','non-fiction',2015,9030);

CREATE TABLE booksales2 (country2 CHAR(35), genre2 CHAR(15), year2 INT, sales2 INT) ENGINE=Columnstore;

INSERT INTO booksales2 VALUES
('Senegal','fiction',2014,12234), ('Senegal','fiction',2015,15647),
('Senegal','non-fiction',2014,64980), ('Senegal','non-fiction',2015,78901),
('Paraguay','fiction',2014,87970), ('Paraguay','fiction',2015,76940),
('Paraguay','non-fiction',2014,8760), ('Paraguay','non-fiction',2015,9030);

select country,year,genre,sum(sales),avg(sales) from (SELECT country, year, genre, sales FROM booksales UNION all SELECT country2, year2, genre2, sales2 FROM booksales2) t1 GROUP BY country,year,genre WITH ROLLUP;

returns:

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

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

---------------------------------------------
8 rows in set (0.094 sec)

no rollup rows!!! (also no for MCS+MDB tables or MDB+MCS)

(if both tables are MDB - rollup rows exist):

MariaDB [mcs84_db]> select country3,year3,genre3,sum(sales3),avg(sales3) from (SELECT country3, year3, genre3, sales3 FROM booksales3 UNION all SELECT country4, year4, genre4, sales4 FROM booksales4) t1 GROUP BY country3,year3,genre3 WITH ROLLUP;
------------------------------------------------

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

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

At drone CI under docker first query using two engine tables raise error:

select country, year3, sum(sales3) from booksales b1 join booksales3 b3 on b1.sales=b3.sales3 group by country,year3 WITH ROLLUP;

mysqltest: At line 74: query 'select country, year3, sum(sales3) from booksales b1 join booksales3 b3 on b1.sales=b3.sales3 group by country,year3 WITH ROLLUP' failed: ER_INTERNAL_ERROR (1815): Internal error: CrossEngineStep::execute() fatal error running mysql_real_connect() in libmysql_client lib (1045) (Access denied for user 'cejuser'@'localhost' (using password: YES))

kirill.perov@mariadb.com The last issue description looks like there was no CrossEngineJoin user with the specific password in MDB or there was no `select flush privileges;` has been called after the user had been added.

sergey.zefirov, drrtuy Yes, error in drone was resolved by adding CrossEngineJoin user.

The issue with WITH ROLLUP on subselect found by kirill.perov@mariadb.com has been fixed.

query with 2 MCS tables is working well
but I can not reach working query between MCS and MDB tables