You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Paul Jackson <pa...@verizon.net.INVALID> on 2023/10/25 19:35:07 UTC

Question on PulledUpPredicates

I'm currently working on CALCITE-6044. I have a problem that I thought would be solved by mq.getAllPredicates(rel) but the output is from a perspective that doesn't seem useful for my problem, which is, given this query:

select deptno, ename, sum(sal)from empwhere deptno=1010group by deptno, ename
and a call to areColumnsUnique(Aggregate rel, mq, bitSetOf(1), false), I'd like it to return true. It currently returns true for columns {0, 1} on account that those are the columns in the group by clause. However, it doesn't recognize that since deptno is set to a constant, the remaining column (ename/1) is by itself an effective key.
To solve this, I plan to collect a bitset of the constant columns (would be {0} in this example), and remove those columns from the group-by columns. I thought the way to collect those columns was to call mq.getAllPredicates(rel) and take the columns from constantMap.keySet(), however, the RexInputRefs are relative to the table scans (returning column 7 instead of 0), ignoring the effects on column ordering of the projection and aggregation.
Is there an existing utility that can either transform these results or return them from the perspective of the current RelNode?
Paul Jackson

Re: Question on PulledUpPredicates

Posted by Julian Hyde <jh...@gmail.com>.
I agree with your observation that if (x, y) is a unique key for R, and x is constant, then y is also a unique key.

When someone calls areColumnsUnique(R, {y}, false), the implementation should (and probably already does) look to see whether {y} is a known unique key (or a superset of one).

But the implementation of getUniqueKeys(R) probably doesn’t yet take into account constant columns. If the known unique keys are {x, y}, {w, x, z}, {y, z}, and {x} is constant, then a better set of unique keys is {y}, {w, z}. (Note that we have converted {x, y} to {y}, and we have discarded {y, z} because it is a superset of the new key.)

That would be a good enhancement to make to getUniqueKeys. Note that it doesn’t depend on the type of the RelNode R; it just relies on the constants known for R, via RelOptPredicateList.constantMap.

Some work seems to have been done on this in https://issues.apache.org/jira/browse/CALCITE-3428.

Julian


> On Oct 25, 2023, at 12:35 PM, Paul Jackson <pa...@verizon.net.INVALID> wrote:
> 
> I'm currently working on CALCITE-6044. I have a problem that I thought would be solved by mq.getAllPredicates(rel) but the output is from a perspective that doesn't seem useful for my problem, which is, given this query:
> 
> select deptno, ename, sum(sal)from empwhere deptno=1010group by deptno, ename
> and a call to areColumnsUnique(Aggregate rel, mq, bitSetOf(1), false), I'd like it to return true. It currently returns true for columns {0, 1} on account that those are the columns in the group by clause. However, it doesn't recognize that since deptno is set to a constant, the remaining column (ename/1) is by itself an effective key.
> To solve this, I plan to collect a bitset of the constant columns (would be {0} in this example), and remove those columns from the group-by columns. I thought the way to collect those columns was to call mq.getAllPredicates(rel) and take the columns from constantMap.keySet(), however, the RexInputRefs are relative to the table scans (returning column 7 instead of 0), ignoring the effects on column ordering of the projection and aggregation.
> Is there an existing utility that can either transform these results or return them from the perspective of the current RelNode?
> Paul Jackson


Re: Question on PulledUpPredicates

Posted by Paul Jackson <pa...@verizon.net.INVALID>.
 Please ignore this question.
The method I needed was mq.getPulledUpPredicates.
-Paul

    On Wednesday, October 25, 2023 at 03:35:07 PM EDT, Paul Jackson <pa...@verizon.net> wrote:  
 
 I'm currently working on CALCITE-6044. I have a problem that I thought would be solved by mq.getAllPredicates(rel) but the output is from a perspective that doesn't seem useful for my problem, which is, given this query:

select deptno, ename, sum(sal)from empwhere deptno=1010group by deptno, ename
and a call to areColumnsUnique(Aggregate rel, mq, bitSetOf(1), false), I'd like it to return true. It currently returns true for columns {0, 1} on account that those are the columns in the group by clause. However, it doesn't recognize that since deptno is set to a constant, the remaining column (ename/1) is by itself an effective key.
To solve this, I plan to collect a bitset of the constant columns (would be {0} in this example), and remove those columns from the group-by columns. I thought the way to collect those columns was to call mq.getAllPredicates(rel) and take the columns from constantMap.keySet(), however, the RexInputRefs are relative to the table scans (returning column 7 instead of 0), ignoring the effects on column ordering of the projection and aggregation.
Is there an existing utility that can either transform these results or return them from the perspective of the current RelNode?
Paul Jackson