Getting incorrect behavior while joining on datetime columns in metabase version 40.2

Hi Team,

Currently we are using metabase version 0.40.2 which we upgraded from 0.37.3. We are seeing incorrect behavior while joining tables on datetime columns. This behavior we didn't see in 37.3.

Example of SQL generated by metabase when created a simple question. We can clear see join on datetime columns and datetrunc function generated by metabase. On one side it is taking date_trunc on a week and on other side it is taking date_trunc on a minute. This is reason why we are seeing incorrect behavior when join tables on datetime columns. In 37.3 version it used to join correctly on date_trunc with week on both tables.

SELECT "dbname"."schamename"."table1"."col1" AS "col1", "dbname"."schamename"."table1"."col2" AS "col2", "dbname"."schamename"."table1"."col3" AS "col3", "dbname"."schamename"."table1"."col4" AS "col4", "dbname"."schamename"."table1"."__SYS__EXECUTION_TIME" AS "__SYS__EXECUTION_TIME", "dbname"."schamename"."table1"."__SYS__TIMESTAMP" AS "__SYS__TIMESTAMP", "table2"."LATEST_EXECUTION_DATETIME" AS "table2__LATEST_EXECUTION_DATETIME"
FROM "dbname"."schamename"."table1" INNER JOIN "dbname"."schamename"."table2" "table2" ON date_trunc("week", CAST("dbname"."schamename"."table1"."__SYS__EXECUTION_TIME" AS timestamp)) = date_trunc("minute", CAST("table2"."LATEST_EXECUTION_DATETIME" AS timestamp))
LIMIT 1048575

Please let us know your feedback. Thanks!

Hi @vijayvatsalraj
You are seeing this issue, which has been fixed in the upcoming 0.41:
https://github.com/metabase/metabase/issues/10924

Upgrade to 0.40.5: https://github.com/metabase/metabase/releases/latest
And try clearing the fingerprints for the fields and do a new sync, which will generate new fingerprints. See this issue for more details about fingerprints:
https://github.com/metabase/metabase/issues/18074