info from D lee

I tried it. It did not work.

this syntax is ok

SELECT
measure_id,
location_id,
sex_id,
age_group_id,
cause_id,
metric_id,
MIN(val) AS min,
MAX(val) AS max,
MIN(lower) AS min_ui,
MAX(upper) AS max_ui
FROM output_cod_single_year_v265
WHERE measure_id IN(1)
GROUP BY measure_id, location_id, sex_id, age_group_id, cause_id, metric_id;

but as soon as I add another column in the where clause, it errors out

MariaDB [mytest]> SELECT
-> measure_id,
-> location_id,
-> sex_id,
-> age_group_id,
-> cause_id,
-> metric_id,
-> MIN(val) AS min,
-> MAX(val) AS max,
-> MIN(lower) AS min_ui,
-> MAX(upper) AS max_ui
-> FROM output_cod_single_year_v265
-> WHERE measure_id IN(1) AND metric_id IN(3)
-> GROUP BY measure_id, location_id, sex_id, age_group_id, cause_id, metric_id;
ERROR 1815 (HY000): Internal error: IDB-2021: 'mytest.output_cod_single_year_v265.metric_id' is not in GROUP BY clause. All non-aggregate columns in the SELECT and ORDER BY clause must be included in the GROUP BY clause.
MariaDB [mytest]>

Putting () around the columns in the where clause did not help.

customer reported this worked in infinidb

A test shows that this does indeed appear to work in InfiniDB

This similar query works fine against tpch:
select
l_orderkey,
l_partkey,
l_suppkey,
l_linenumber,
l_discount,
l_shipdate,
min(l_quantity) as min,
max(l_quantity) as max,
min(l_extendedprice) as min_ex,
max(l_extendedprice) as max_ex
from lineitem
where l_orderkey=5986562
and l_partkey=181890
and l_suppkey=6927
group by l_orderkey, l_partkey, l_suppkey, l_linenumber, l_discount, l_shipdate;

By paring the where clause in the reporter's query to just one = statement, it works. Adding another = statement breaks it. The IN clauses seem to not be an issue. However this statement with three where conditions works.

By removing just one of the fields from the original query, it works with two where conditions. Seems there's some sort of heterodyning between the number of group by conditions and the number of where conditions.

The optimizer sees items in the where clause that are basically compares against a constant – IN with one value is treated as a constant. It then decides that having a group by clause for this field is a waste of time and optimizes it out. Columnstore doesn't know it got optimized out and complains about the lack of a group by for that field.

WHERE measure_id IN(1)
and
WHERE measure_id = 1

Are both optimized out and cause the issue.

WHERE measure_id IN(1,2)
would not be optimized out and no problem would occur.

During the changes from mysql 5.1.73 and mariadb 10.1, the optimizer was changed – an added call to remove_const was added. remove_const is a function written for ORDER BY and was repurposed for GROUP BY. It's supposed to remove any duplicate phrases and optimize out any GROUP BY that have a corresponding constant WHERE clause.

The Columnstore Engine edits to be sure there is a GROUP BY for every non aggregated return column. I don't know what happens if this edit is taken out. InfiniDB put a 'if !InfiniDB' statement around the previously existing call to remove_const, so I added such a check to the new call.

Looks good to me, approved. Also good catch with the README fix.

Moving to Daniel for QA

dleeyh Has this been QAed and added to AutoPilot ?

Verified the query from the user.