Inconsistent results with and without the optimizer #4258
Description
#4252
sql fragment
SELECT month AS month, sum(anon_6.value) AS value
FROM (
SELECT date_trunc('month', day) AS month, coalesce(sum(value), 0.0) AS value
FROM df2
WHERE CAST(day AS DATE) >= '2022-01-01 00:00:00'
AND CAST(day AS DATE) <= '2022-01-31 00:00:00'
GROUP BY date_trunc('month', day)
) AS anon_6
GROUP BY GROUPING SETS((month))
┌────────────┬───────┐
│ month ┆ value │
╞════════════╪═══════╡
│ 2022-01-01 ┆ 100 │
└────────────┴───────┘
Elapsed: 5 ms
PRAGMA disable_optimizer
SELECT month AS month, sum(anon_6.value) AS value
FROM (
SELECT date_trunc('month', day) AS month, coalesce(sum(value), 0.0) AS value
FROM df2
WHERE CAST(day AS DATE) >= '2022-01-01 00:00:00'
AND CAST(day AS DATE) <= '2022-01-31 00:00:00'
GROUP BY date_trunc('month', day)
) AS anon_6
GROUP BY GROUPING SETS((month))
┌┐
└┘
Elapsed: 2 msIn the above example, day will be converted to VARCHAR, because the type id of VARCHAR is bigger than DATE.
When the optimizer is enabled, '2022-01-01 00:00:00' will be converted to DATE In the ComparisonSimplificationRule. The result of the conversion is '2022-01-01'::DATE.
When the optimizer is disabled, the phy plan will have a filter operator. I think this predicate will get false value.
┌─────────────┴─────────────┐
│ FILTER │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ (CAST(day AS VARCHAR) >= │
│ '2022-01-01 00:00:00' AND │
│ CAST(day AS VARCHAR) ... │
│ -01-31 00:00:00') │
└─────────────┬─────────────┘ Should TryCastAs be run in strict mode In the ComparisonSimplificationRule? Are there other considerations for not using strict mode? If strict mode is enabled, the final result will be an empty set like without optimizer. Because in strict mode it will check whether there is no space after day part. '2022-01-01 00:00:00' will can't be converted to DATE. For other types, it's not clear to me what they do in strict mode.