Asof join with equality throws Not implemented Error.

[michael-hlavacek]

What happens?

When I run the example from the documentation for asof join on tables with actual data, I get an Error: Not implemented Error: Unsupported comparison type for ASOF join.

When I remove either the equality condition or data from one of the tables, everything works. So all of

create table prices("when" timestamp with time zone, symbol int, price int);

create table trades("when" timestamp with time zone, symbol int);
insert into trades values ('2020-01-01 00:00:03+00', 1);

SELECT t.*, p.price
FROM trades t ASOF JOIN prices p 
  ON t.symbol = p.symbol AND t.when >= p.when;

create table prices("when" timestamp with time zone, symbol int, price int);
insert into prices values ('2020-01-01 00:00:00+00', 1, 42);

create table trades("when" timestamp with time zone, symbol int);

SELECT t.*, p.price
FROM trades t ASOF JOIN prices p 
  ON t.symbol = p.symbol AND t.when >= p.when;

and

create table prices("when" timestamp with time zone, symbol int, price int);
insert into prices values ('2020-01-01 00:00:00+00', 1, 42);

create table trades("when" timestamp with time zone, symbol int);
insert into trades values ('2020-01-01 00:00:03+00', 1);

SELECT t.*, p.price
FROM trades t ASOF JOIN prices p 
  ON t.when >= p.when;

work fine.

Also, changing the names of all columns and tables preserves the problem.

To Reproduce

create table prices("when" timestamp with time zone, symbol int, price int);
insert into prices values ('2020-01-01 00:00:00+00', 1, 42);

create table trades("when" timestamp with time zone, symbol int);
insert into trades values ('2020-01-01 00:00:03+00', 1);

SELECT t.*, p.price
FROM trades t ASOF JOIN prices p 
  ON t.symbol = p.symbol AND t.when >= p.when;

OS:

Ubuntu x64 in WSL on Windows 11

DuckDB Version:

v0.9.2-dev51 2646836

DuckDB Client:

cli

Full Name:

Míma Hlaváček

Affiliation:

Blindspot.ai

Have you tried this on the latest main branch?

I have tested with a main build

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• Yes, I have

[hawkfish]

The problem is that the optimiser can tell that your test data causes the inequality to always be true. We should fix this, but it is a somewhat artificial situation.

[michael-hlavacek]

I had this crash on me in a process where we batch-process some data, does not feel that artificial to me. (Not this specific query, of course, but an equivalent one with one equality and one inequality.)

[hawkfish]

Ah sorry, it read like a random finding. But machine generated makes sense. Thanks for isolating it so nicely.

[michael-hlavacek]

No worries. Thanks for looking into this! :)