opt: support constrained in between filters with multiple partitioning columns

[rharding6373]

Currently the optimizer may be unable to generate a constrained in between filter for a constrained scan if an index is partitioned by multiple columns that have different order, even if the partitions themselves are constrained.

For example the following query should be able to use a constrained scan, but the optimizer is currently unable to generate constrained spans for the in between filters:

CREATE TABLE tab_part (
    col0
        INT8 NOT NULL,
    col1
        INT8 NOT NULL,
    col2
        INT8 NOT NULL,
    INDEX (col0 DESC, col1, col2)
        PARTITION BY LIST (col0, col1)
            (
                PARTITION one VALUES IN ((1, 10)),
                PARTITION two VALUES IN ((2, 20))
            )
);

SELECT * FROM tab_part WHERE col2 > 4;

See #80820 (comment) for more details.

Jira issue: CRDB-15172

[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!