DATE_TRUNC filtering failing only in some cases

[lara-bellatin]

Describe the bug DATE_TRUNC filtering fails in some cases but not others.

It fails for this query:

SELECT
  "public"."Order"."channel" AS "channel",
  SUM(
    CASE
      WHEN DATE_TRUNC('month', "public"."Order"."orderDate") BETWEEN DATE_TRUNC('month', (NOW() + INTERVAL '-2 month'))

   AND DATE_TRUNC('month', (NOW() + INTERVAL '-1 month')) THEN "public"."Order"."lineSubtotal"
      ELSE 0.0
    END
  ) AS "tst2"
FROM
  "public"."Order"
GROUP BY
  "public"."Order"."channel"
ORDER BY
  "public"."Order"."channel" ASC

But not for this one:

SELECT
  "public"."Order"."channel" AS "channel",
  SUM(
    CASE
      WHEN DATE_TRUNC('month', "public"."Order"."orderDate") = DATE_TRUNC('month', (NOW() + INTERVAL '-1 month')) THEN "public"."Order"."lineSubtotal"
      ELSE 0.0
    END
  ) AS "tst2"
FROM
  "public"."Order"
GROUP BY
  "public"."Order"."channel"
ORDER BY
  "public"."Order"."channel" ASC

Error Message:

ERROR: Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information. QUERY: SELECT "public"."Order"."channel" AS "channel", SUM(CASE WHEN DATE_TRUNC('month', "public"."Order"."orderDate") BETWEEN DATE_TRUNC('month', (NOW() + INTERVAL '-2 month')) AND DATE_TRUNC('month', (NOW() + INTERVAL '-1 month')) THEN "public"."Order"."lineSubtotal" ELSE 0.0 END) AS "tst2" FROM "public"."Order" GROUP BY "public"."Order"."channel" ORDER BY "public"."Order"."channel" ASC

Version: 1.0.5

[igorlukanin]

Hi @lara-bellatin 👋

Just to double-check: do you have CUBESQL_SQL_PUSH_DOWN set to true?

Also, which BI tool generates these queries? (If they are indeed generated.)