Unable to execute query and not even able to generate Estimated execution plan

I am working on SQL Server 2019.

I have a table dbo.AllDates where I have all dates from 1990 to 2050. I have another table dbo.ActualExchangeRates where I have actual exchange rates for certain currencies on the dates when exchange rate is found in given source.

I am trying to write a query to get all currencies for all dates between 2010 and 2020. If rate is found then write the rate otherwise write NULL.

Given this scenario and given code below, can someone please help me understand why the SELECT query is not generating any results or even not able to see estimated execution plan?

CREATE TABLE dbo.AllDates(Date date)
CREATE TABLE dbo.ActualExchangeRates(Date date, Currency char(3), Rate real)

--Query 1: Not generating any results or estimated plan
SELECT      d.Date, m.Currency, c.Rate
FROM        dbo.AllDates d
INNER JOIN  (
    select
      currency,
      '20100101' as mindate,
      '20201231' as maxdate
    from dbo.ActualExchangeRates
    group by currency
) as m on d.date between m.mindate and m.maxdate
LEFT JOIN   dbo.ActualExchangeRates C ON C.Currency = m.Currency and c.Date = d.Date;

I get the following error after the query runs for 9 minutes against empty tables:

Msg 701, Level 17, State 123, Line 5

There is insufficient system memory in resource pool 'default' to run this query.

Seems like it depends upon how much memory is available to SQL server until it reaches the error. To me, looks like a bug in SQL engine given there is no data in the table.

Now, I know that above query can be written in many different ways and other ways can generate results but my question is why SQL Server just hangs up forever on that query even if both tables are empty?

This is certainly a bug.

I can't tell you the nitty gritty details of the exact nature of the bug but some details are below.

Whilst the execution plan is being compiled the Visual Studio performance profiler shows the CPU time for the thread is spent as follows.

So a lot of CPU time was being spent at

... -> sqllang.dll!COptContext::NormalizeQuery -> ... sqllang.dll!COptContext::PexprTransformTopLevel -> sqllang.dll!CSubRuleImpliedPredInnerAndAllLeftJn::BuildSubstitutes -> sqllang.dll!OptimizerUtil::PexprCreateConjOrDisj

and at

... -> sqllang.dll!COptContext::NormalizeQuery -> ... sqllang.dll!COptContext::PexprTransformTopLevel -> sqllang.dll!COptExpr::DeriveGroupProperties

The stack shows the issue is arising during the Query normalization stage of compilation and appears to be associated with a rule RuleImpliedPredInnerAndAllLeftJn.

The issue does indeed go away when that rule is disabled with option(queryruleoff ImpliedPredInnerAndAllLeftJn) but this is not a good solution to the issue (undocumented and can affect performance as it no longer pushes the implied predicate of [C].[Date]>='2010-01-01' AND [C].[Date] ScaOp_Comp -> ScaOp_Comp -> ScaOp_Logical -> ScaOp_Const -> ScaOp_Comp -> ScaOp_Const -> ScaOp_Comp -> ScaOp_Logical -> ScaOp_Logical -> LogOp_Select -> LogOp_LeftOuterJoin -> ... with memory usage steadily increasing until eventually it runs out of memory and the compilation attempt ends.
(Sample output of that showing a couple of circuits)

On my dev machine

select promised
from sys.dm_exec_query_transformation_stats
where name = 'ImpliedPredInnerAndAllLeftJn'

from before and after grows by ~390,000 with each failed run.

StackExchange Database Administrators Q#326879, answer score: 14