sql+opt: translate upcasts in comparisons to suitable bound enlargements

[knz]

This is something I tripped up against earlier, which I saw @awoods187 trip against, and which Andrew Deally just reported customers trip up on:

select * from sometable where tscol::date = '2018-07-01'

where tscol has type TIMESTAMP and is indexed, will (currently) not use an index. This is a major surprise.

The workaround is pretty inconvenient, the query must be manually translated to

... where tscol >= '2018-07-01'::timestamp and tscol < '2018-07-02'::timestamp

(which is itself error prone in edge cases like February 28th on leap years etc).

This optimization must be performed for any comparison of the pattern Val::T = LITERAL where:

• T is a type of lower precision than Val
• there is a "natural", unambiguous projection from the type of Val to T.
• the = comparison can be translated to an exact interval.

Other examples:

• DECIMAL vs INT (where deccol::INT = 123)
• TIMESTAMP vs TIME
• TIME vs DATE
• (when we have them) INT4 vs INT8 etc see sql: make the CockroachDB integer types more compatible with postgres #26925

but not TIMESTAMP vs INT (no natural conversion) or DECIMAL vs FLOAT (no natural conversion).

Jira issue: CRDB-4951

[knz]

cc @RaduBerinde @andy-kimball

[knz]

Also optimization is possible for inequalities when the bound has an exact representation in both types!

[RaduBerinde]

#20046 is similar.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
5 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

[knz]

still exists in 21.1

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

[knz]

still relevant