Metabase auto add NULLIF in the sum expression

Get Help
1

Hi All,

I have just found out, in the recent version ( maybe since 50 ), metabase automatic add NULLIF function in the sum expression.

example :
create custom expression
=sum(sales)/sum(grossprofit)

in the sql windows, metabase add nullif
=sum(sales)/nullif(sum(grossprofit),0)

this additional nullif statement produce null value in mariadb columnstore, is there any option to turn off automatic add nullif function?

2

without that nullif, you'd just get an error - is that what you want?

3

Hi Andrew,

no, wiithout "nullif" the sql statement return correct result in the mariadb columnstore

4

What's the 'correct' answer when dividing by zero other than an error?

5

Hi Andrew,

I suggest that Metabase should not automatically generate NULLIF. Instead, it should allow users to add it when necessary. In my case, using MariaDB ColumnStore, the additional NULLIF function returns a null value. However, without the NULLIF function, the query returns the correct result.

I also found a workaround for those using MariaDB ColumnStore. You can add * 1 to the expression. For example:

In the expression:
=Sum([Gross Profit]) / (Sum([Sales]))
it returns a NULL value.

But with the expression:
=Sum([Gross Profit]) / (Sum([Sales]) * 1)
it returns the correct value.