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?
without that nullif, you'd just get an error - is that what you want?
Hi Andrew,
no, wiithout "nullif" the sql statement return correct result in the mariadb columnstore
What's the 'correct' answer when dividing by zero other than an error?
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.