Error with Mysql / Mariadb ColumnStore query

1

I get this error.

Internal error: IDB-2021: 'Date' 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.

dB

This error is with Mariadb Column Store using the mysql database type.

I have the same database in both Mysql innodb and Mariadb Column Store. This works OK in mysql.

Query

SELECT sum(`vw_scraplist`.`Cost`) AS `sum`, year(`vw_scraplist`.`Date`) AS `Date`
FROM `vw_scraplist`
GROUP BY year(`vw_scraplist`.`Date`)
ORDER BY year(`vw_scraplist`.`Date`) ASC

Question:

  1. I may be approaching this all wrong. Should I use the mysql database type?
  2. Not even sure where to start. Sql Standards? Use extract instead? Any suggestions are welcome.
  3. I am open to suggestions of where to look for info on this as I have google searched and looked in formums etc, but I don’t think I am searching correctly.
2

This is an SQL Strict mode issue. Both MySQL and MariaDB have an SQL_MODE setting. By default it is set to strict whereas you have to give it correct SQL. The above SQL grouping is not valid in that mode.
I’d say that your MySQL server has this deactivated while your MariaDB has it activated.
The correct solution is to construct the SQL correctly but you can try setting the SQL_MODE to empty so the command will be accepted.
Have a search on the internet there is a lot of literature about the issue.

I suppose that if you could reproduce the issue with the Sample Database we could open an issue on github

HTH

Joe
TSolucio

3

This SQL was constructed by Metabase, not me.

I used the non sql question interface to ask for a graph of cost by year.

Can you comment?

4

Have you tried deactivating the SQL Strict mode?

If you can reproduce the issue with the sample data I can create a ticket on github for you and have the metabase team fix it.