To reproduce

SQL steps:

create table historical (
    account_id int
);

create table metrics (
    metric int
);

create table current (
    account_id int,
    metric int
);

EXPLAIN
SELECT distinct h.account_id, m.metric
  FROM historical h
  cross join metrics m
  LEFT JOIN current c ON h.account_id = c.account_id and m.metric = c.metric
  WHERE c.account_id IS NULL;

In SqlOptimiser.optimise at the rewriteSelectClause step, it will fail. rewrittenModel is:

select h.account_id, m.metric from ((select-group-by account_id, metric, count() count from (select-choose h.account_id account_id, m.metric metric from (historical h where c.account_id = NULL) h) h) h) h

Validation fails in validateColumnAndGetModelIndex because modelAliasesIndexes does not contain m.

It seems the cross join went missing.

This will work instead:

  SELECT h.account_id, m.metric, count()
  FROM historical h
  cross join metrics m
  LEFT JOIN current c ON h.account_id = c.account_id and m.metric = c.metric
  WHERE c.account_id IS NULL;

Which is more or less what the rewrite is supposed to do

QuestDB version:

9.0.2

OS, in case of Docker specify Docker and the Host OS:

N/A

File System, in case of Docker specify Host File System:

N/A

Full Name:

Nick Woolmer

Affiliation:

QuestDB

Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?

• Yes, I have

Additional context

No response