I currently have a query in metabase that worked fine in a previous version of metabase (v0.39.4) and now fails to execute properly after upgrading to v0.42.3 - the results are now returned but multiplied by large factors. The query itself joins the results of 2 subqueries by date and includes variables, in the general form of:
select * from (
select date_trunc({{period}}, datefield) as "date", sum(value) as revenue1
from table1
group by "date") temp
left join (
select date_trunc({{period}}, datefield) as "date", sum(value) as revenue2
from table2
group by "date") temp2 on temp."date" = temp2."date"
In the variable field before putting "month", "week", "day" etc. would group the results accordingly. Now the grouping still happens but revenue1 is multiplied by 1,000,000 and revenue2 is multiplied by 100.
If i remove the variable and replace it with static 'month' the results are returned as expected.
There has been several changes in 0.42 to filters and variables, so it would be interesting to know when the problem started. Are you also seeing errors on 0.41.6 or 0.42.0?
The Redshift driver was also upgraded in 0.42.0
hmm weird, well thanks for trying,
I had a look at the logs and couldn't see anything weird
I cant say if we're seeing issues on 0.41.6 as we jumped straight from 0.39.4 to 0.42.3
I'll try and recreate the issue with a simpler query or have a look through the metabase change log to see if anything jumps out
would you say any updates in particular made large changes to filter logic?
@Alexhs The entire filter logic was almost completely rewritten in 42, along with a lot of adjustments of variables too.
Are you referring to doing something like value::float or sum(value)::float fixes the problem?
What column type is value?
It sounds like a driver issue, since Metabase will generate a parametrized query, when there are variables to prevent SQL injection etc, but I'm not seeing anything in the issues or changelog: https://github.com/aws/amazon-redshift-jdbc-driver
yes casting it as value::numeric did fix the problem. value is stored as a numeric type in the db specifically decimal(36,6).
It's very weird as when I perform operations on one of the columns say sum(value1)/10 the issue returns with that column being multiplied by large powers of 10, on top of that sum(value2) gets dropped to 0.
Is there anyway to tell for sure if its an issue with the driver? and does this mean its an unreported driver bug?
@Alexhs The easiest way to test driver versions are with DBeaver.io - it's still a little tricky to get it to use the version you specify, but it's a lot easier than building Metabase.
I will try to create a table with that column type and see if I can reproduce on Metabase.