Issue with 2 joined subqueries and variables multiplying results by 1 mil

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.

any idea whats causing this?

Hi @Alexhs
Please post "Diagnostic Info" from Admin > Troubleshooting, and which database type you're querying.
The {{period}} is a Text filter type?

There has been several filter changes, but what you are describing is definitely strange.
Nothing should cause anything like multiplication.

db im querying is redshift and yes period is a text filter type

"browser-info": {
"language": "en-GB",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.4844.84 Safari/537.36",
"vendor": "Google Inc."
"system-info": {
"file.encoding": "UTF-8",
"": "OpenJDK Runtime Environment",
"java.runtime.version": "",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "",
"java.version": "",
"": "OpenJDK 64-Bit Server VM",
"java.vm.version": "",
"": "Linux",
"os.version": "5.4.95-42.163.amzn2.x86_64",
"user.language": "en",
"user.timezone": "UTC"
"metabase-info": {
"databases": [
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "10.18"
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.23"
"run-mode": "prod",
"version": {
"date": "2022-03-23",
"tag": "v0.42.3",
"branch": "release-x.42.x",
"hash": "33fb268"
"settings": {
"report-timezone": null

@Alexhs No matter what I do, I cannot reproduce.

Try looking in the Redshift query log.

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?

@flamber it turned out explicitly casting both summed columns as numeric fixed the issue, I have no idea why this fixed it

@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:

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 - 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.

Ok thanks @flamber Ill check out or maybe rollback metabase to an earlier version to see specifically which version caused the issues,

let me know if you need me to clarify anything about the table when you recreate it