Bug: TDVT StandardTests Cannot coerce arithmetic expression error for Interval #11
Description
There are 300+ TDVT StandardTests failing with Interval and arithmetic expression coercion error.
Error during planning: Cannot coerce arithmetic expression Null * Interval(MonthDayNano) to valid types | SELECT ((CAST('1900-01-01 00:00:00' AS TIMESTAMP) + NULL * INTERVAL '1 DAY') + 1 * 3 * INTERVAL '1 MONTH') AS "TEMP(Test)(2232502461)(0)"
Error during planning: Cannot coerce arithmetic expression Int64 * Interval(MonthDayNano) to valid types | SELECT ("calcs"."date0" - (EXTRACT(EPOCH FROM (CAST(("calcs"."date1" - 1 * INTERVAL '1 DAY') AS TIMESTAMP) - "calcs"."date2")) / (60.0 * 60 * 24)) * INTERVAL '1 DAY') AS "TEMP(Test)(750868662)(0)"
Error during planning: Cannot coerce arithmetic expression Float64 * Interval(MonthDayNano) to valid types | SELECT ("calcs"."date0" + "calcs"."num4" * INTERVAL '1 DAY') AS "TEMP(Test)(1817739360)(0)"
This is currently a DataFusion limitation:
All tests failures with example input
+-------------+-------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| error_count | error_message | input_example |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 214 | Error during planning: Cannot coerce arithmetic expression Null * Interval(MonthDayNano) to valid types | SELECT ((CAST('1900-01-01 00:00:00' AS TIMESTAMP) + NULL * INTERVAL '1 DAY') + 1 * 3 * INTERVAL '1 MONTH') AS "TEMP(Test)(2232502461)(0)" |
| | | FROM "testv1"."calcs" "calcs" |
| | | HAVING (COUNT(1) > 0) |
| 92 | Failed to execute query: Error during planning: Cannot coerce arithmetic expression Null * Interval(MonthDayNano) to valid types | SELECT (1 + CAST(EXTRACT(DOW FROM (CAST('1900-01-01 00:00:00' AS TIMESTAMP) + NULL * INTERVAL '1 DAY')) AS INTEGER)) AS "TEMP(Test)(3556637072)(0)" |
| | | FROM "testv1"."calcs" "calcs" |
| | | HAVING (COUNT(1) > 0) |
| 66 | Error during planning: Cannot coerce arithmetic expression Int64 * Interval(MonthDayNano) to valid types | SELECT ("calcs"."date0" - (EXTRACT(EPOCH FROM (CAST(("calcs"."date1" - 1 * INTERVAL '1 DAY') AS TIMESTAMP) - "calcs"."date2")) / (60.0 * 60 * 24)) * INTERVAL '1 DAY') AS "TEMP(Test)(750868662)(0)" |
| | | FROM "testv1"."calcs" "calcs" |
| | | GROUP BY ("calcs"."date0" - (EXTRACT(EPOCH FROM (CAST(("calcs"."date1" - 1 * INTERVAL '1 DAY') AS TIMESTAMP) - "calcs"."date2")) / (60.0 * 60 * 24)) * INTERVAL '1 DAY') |
| 14 | Error during planning: Cannot coerce arithmetic expression Float64 * Interval(MonthDayNano) to valid types | SELECT ("calcs"."date0" + "calcs"."num4" * INTERVAL '1 DAY') AS "TEMP(Test)(1817739360)(0)" |
| | | FROM "testv1"."calcs" "calcs" |
| | | GROUP BY ("calcs"."date0" + "calcs"."num4" * INTERVAL '1 DAY') |
| 8 | Failed to execute query: Error during planning: Cannot coerce arithmetic expression Float64 * Interval(MonthDayNano) to valid types | SELECT CAST((DATE_TRUNC( 'day', CAST("calcs"."date2" AS DATE) ) + (-EXTRACT(DOW FROM "calcs"."date2") * INTERVAL '1 DAY')) AS DATE) AS "TEMP(Test)(1630131013)(0)" |
| | | FROM "testv1"."calcs" "calcs" |
| | | GROUP BY CAST((DATE_TRUNC( 'day', CAST("calcs"."date2" AS DATE) ) + (-EXTRACT(DOW FROM "calcs"."date2") * INTERVAL '1 DAY')) AS DATE) |
| 4 | This feature is not implemented: Unsupported SQL binary operator PGExp | SELECT ("calcs"."int2"^2) AS "TEMP(Test)(3898674109)(0)" |
| | | FROM "testv1"."calcs" "calcs" |
| | | GROUP BY ("calcs"."int2"^2) |
| 4 | Error during planning: Invalid function 'sign'. | SELECT CAST(SIGN("calcs"."int2") AS SMALLINT) AS "TEMP(Test)(3509671532)(0)" |
| | Did you mean 'sin'? | FROM "testv1"."calcs" "calcs" |
| | | GROUP BY CAST(SIGN("calcs"."int2") AS SMALLINT) |
| 2 | Failed to execute query: Error during planning: Cannot coerce arithmetic expression Int64 * Interval(MonthDayNano) to valid types | SELECT CAST(TRUNC(EXTRACT(YEAR FROM ("calcs"."date0" + ((-(1 + CAST(EXTRACT(DOW FROM "calcs"."date0") AS INTEGER))) + 1) * INTERVAL '1 DAY'))) AS INTEGER) AS "TEMP(Test)(1308221269)(0)" |
| | | FROM "testv1"."calcs" "calcs" |
| | | GROUP BY CAST(TRUNC(EXTRACT(YEAR FROM ("calcs"."date0" + ((-(1 + CAST(EXTRACT(DOW FROM "calcs"."date0") AS INTEGER))) + 1) * INTERVAL '1 DAY'))) AS INTEGER) |
| 2 | Error during planning: Invalid function 'variance'. | SELECT VARIANCE("calcs"."num4") AS "TEMP(Test)(1358865)(0)" |
| | Did you mean 'range'? | FROM "testv1"."calcs" "calcs" |
| | | HAVING (COUNT(1) > 0) |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+