Re: [RFC] ASOF Join

On Mon, Nov 22, 2021 at 03:44:37PM +0300, Alexander Kuzmenkov wrote:
> On 21.11.2021 07:53, Ilya Anfimov wrote:
> > DISCLAIMER: I am both seeing this first time and I don't have a
> > good understanding of the PosgreSQL development practices.
>
> > pure evil
> > ridiculous
> No worries, at least you got the etiquette just right.
>
>
> There are two points in your mail that I'd like to discuss.
> First, the ASOF grammar being bad because it's implicit. I do
> agree on the general idea that explicit is better UX than implic-
> it, especially when we're talking about SQL where you spend half
> the time battling the query planner already. However, in the
> grammar I proposed it's unambiguous which conditions are ASOF and
> which are not -- all inequalities are ASOF, all equalities are

I see at least two operators in postgres that implement ordering
while they are not being <= ( ~<=~ -- for text compare byte-by-
byte, and *<= for internal record compare)
and four cases that are literally <= , but don't implement or-
dering -- box, lseg, path and circle are compared by length and
fuzzy floating-point comparision.

Are you sure an implementor and a programmer will easily decide
what is just a boolean test, and what is an order?

What's worse, preference of values doesn't have a lot in common
with filters you want on them. Let's get your example of a time
matching: another reasonable business case is to match the near-
est time point in any direction, within a reasonable time limit.
Like timea BETWEEN timeb - '1s' AND timeb + '1s' ,
and to choose something like min(@(timea-timeb)) among them (*We
strangely don't have an absolute value operator on interval, but
I think you've got the point*).

> not, and there can be no other kinds of conditions for this type
> of join. It can also support any number of ASOF conditions. Which
> grammar exactly do you suggest? Maybe something like this:

>
> asks JOIN bids ON asks.instrument = bids.instrument ASOF asks.timestamp <= bids.timestamp

I suggest JOIN bids ORDER BY asks.timestamp DESC LIMIT 1
ON asks.instrument = bids.instrument AND asks.timestamp <= bids.timestamp

LIMIT 1 could also be implied.