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!