0.31.0 DATE/DATETIME filtering produces error

[dylanjnz]

• Metabase v0.31.0 on Docker, with PostgreSQL DB.
• Databases are in BigQuery.
• Chrome 70.0.3538.77

I've just upgraded to v0.31.0 and have found an issue when trying to run (new and existing) reports with date filters, specifically when using Before/After, Between, and On:

Before/After:

No matching signature for operator > for argument types: DATETIME, TIMESTAMP. Supported signatures: ANY > ANY at [2:1123]

Between:

No matching signature for operator BETWEEN for argument types: DATETIME, TIMESTAMP, TIMESTAMP. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [2:1169]

The columns being filtered are either DATE and DATETIME types.

[bcbob]

Have updated to 0.31.0 and question builder questions using DATETIME filters are broken. Filters using DATE field work.

[bcbob]

DATE field filters work in Question Builder.
But are also broken (like DATETIMEs) when accessed via dashboard filters.

Filters for custom sql questions seem to work as before.

[mazameli]

@bcbob Are you also querying BigQuery? Haven't been able to reproduce this so far with the Created At field in the Sample Dataset, which is a DATETIME field in an H2 DB.

[dylanjnz]

Confirming DATE does fail (with BigQuery):

No matching signature for operator > for argument types: DATE, TIMESTAMP. Supported signatures: ANY > ANY at [2:1123]

Happy to help debug where possible.

[bcbob]

The problem I’m having is not identical to this.

I’m querying MySQL. Question builder queries using datetime filters are no longer producing results on 0.31.0.
Those using Date are.

However, dashboard date filters linked to question builder queries fail both with date and datetime types.

I’m referring to long existing questions and dashboards that worked smoothly on earlier versions and now simply do not produce results. The problem I’m referring to does not produce an error. Just no results where there were results before.

[lukecarazzo]

I am having the same issue when upgrading to Metabase V0.31.0 on Windows server running custom install. Pointing to mysql database.

[mazameli]

I feel like this issue might be related to #8927

[mazameli]

I'm having trouble reproducing this still. I've tried both with MySQL and BigQuery DBs, with both GUI and SQL questions, both in the query builder and in dashboards, but the only issue I've reproduced is the one reported in #8927. All of my tests have been with new questions though, so I can try creating some questions in an app db with an old version and then upgrading it. Otherwise I'm stumped. Let me know if you have any ideas, @camsaul or @tlrobinson.

[daneren2005]

We are having the same issue. The solution for us was to just take the filter out of the Dashboard altogether. Is there some way for us to grab exactly what you need to reproduce? Is there log information or something else that would help? I did try removing the question from the dashboard and re-adding the same question, and it appeared to work for a few minutes. It started failing again soon after.

[ans-4175]

I know the problem
0.31.0

WHERE `taskforce.ar_payment_recap`.`Tanggal` BETWEEN timestamp('2018-11-01T00:00:00.000Z') AND timestamp('2018-11-10T00:00:00.000Z') ORDER BY `Tanggal` DESC LIMIT 2000

0.30.4

WHERE timestamp_trunc(CAST(`taskforce.ar_payment_recap`.`Tanggal` AS timestamp), day) BETWEEN timestamp_trunc(CAST(timestamp('2018-11-01T00:00:00.000Z') AS timestamp), day) AND timestamp_trunc(CAST(timestamp('2018-11-17T00:00:00.000Z') AS timestamp), day) LIMIT 2000

In 0.31.0 they didn't use timestamp_trunc again for between and on date filter and our table value format is DATE. That's why.

I wonder which files or lines being refactored from 0.30.4 in 0.31.0?

[camsaul]

Same underlying issue as #8927. Closing as a duplicate.

This will be fixed by #8973

[lukecarazzo]

Hi Guys, Has anyone found a solution to this as yet? will the fix only be available in the new version?

[mazameli]

@lukecarazzo We just released an updated version with a bunch of bug fixes, 0.31.1, available here, which contains #8973 which we believe should fix this issue.

[lukecarazzo]

@maz

Thanks so much.

[bcbob]

This particular date problem may be solved.

But on 0.31.1 my dashboard date filters are still broken for GUI question builder questions. There is a "! There was a problem displaying this chart" error on each card on the dashboard. Clicking through to the underlying question passes through the date filter correctly and produces the correct result.

[lukecarazzo]

@mazameli

I use the custom install metabase.jar on windows server. Do you perhaps have a link to the new version in the jar format.

[lukecarazzo]

@mazameli

My date filters are also broken even with the latest version applied. I have the same issue as @bcbbob. If I revert back to older version. My date filters work.

[bcbob]

My dashboard date filters for GUI questions are broken for single date type filters.
Changing the filter to a date range, and specifying a single date as a date range (eg expressing today as "date between 25-11-2018 and 25-11-2018") causes the dashboard filter to work.

(but this of course breaks custom sql questions on the dashboard that expect a single date type variable to be passed through)

[bcbob]

ps: this problem can be re-created on the sample dataset.

For example, create a simple GUI raw data question on the ORDERS table.
Then add it to a dashboard with a date filter.

Try filtering for orders on 11 Feb 2019.
No results will appear unless you filter for orders between 11 Feb 2019 and 11 Feb 2019.

[lukecarazzo]

Please see below, this is what I am getting on the Latest version. metabase v0.31.1 (Latest) - None of the dates filters work at all. If I revert back to version v0.30.4 then all works fine.

[bcbob]

Great news that this issue is fixed and closed.
When is the next release expected to drop?
There must be a large number of broken dashboard filters out there longing to get rocking again...

[lukecarazzo]

@bcbob

Yay. So excited. Can't wait to test all the new functionality. I had to stay on the older version due to this issue.

[bcbob]

Dashboard date filters still broken for query builder questions on 0.31.2 😌
Have started new issue to avoid confusion.