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.