opt: rewrite greatest(a, b) < c to (a < c AND b < c) about cockroach HOT 3 OPEN

Hello, I am Blathers. I am here to help you get the issue triaged.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-queries (found keywords: optimizer,vectorized)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

from cockroach.

Hi @rootjalex, thanks for filing an issue.

The optimizer might consider rewriting these expressions, i.e. greatest(a, b) < c -> a < c AND b < c and least(a, b) < c -> a < c OR b < c. I'm new to cockroachdb, so not sure how much the optimizer already tries to rewrite conditionals, but if I do this greatest rewrite manually, I get the index behavior that I expect (i.e. x < 15000 AND y < 15000 produces a filter(index join(scan))).

Yes, I think to optimize this we'd need to add another comparison normalization rule, similar to these.

from cockroach.

Unfortunately, the transformation suggested is not valid in the presence of NULL arguments to greatest/least. See my comment here: #119979 (review)

I think there are likely some tweaks we can make to make it valid, though there will likely be limitations that may limit its usefulness in addressing the original goal of leading to constrained index scans.

from cockroach.