Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.2.0
-
Untriaged
-
1
-
Unknown
-
Analytics CBO Sprint 7, Analytics CBO Sprint 8, Analytics CBO Sprint 9
Description
This query should give us two NL index joins because of the hints.
select count (1) from R join S on
R.u1K /*+ indexnl */ = tobigint(S.u1K)
join T on tobigint(S.u4K) /*+ indexnl */ = T.u4K ;
Here is the logical plan that is given to the CBO optimizer.
distribute result [$$70] -- |UNPARTITIONED|
|
project ([$$70]) -- |UNPARTITIONED|
|
assign [$$70] <- [{"$1": 480, "$2": $$81}] -- |UNPARTITIONED|
|
aggregate [$$81] <- [agg-sql-count($$68)] -- |UNPARTITIONED|
|
assign [$$68] <- [1] -- |PARTITIONED|
|
join (eq($$79, $$80)) -- |PARTITIONED|
|
assign [$$79] <- [to-bigint($$85)] -- |PARTITIONED| <<<<<< THIS ASSIGN!!
|
join (eq($$77, $$78)) -- |PARTITIONED|
|
assign [$$77] <- [$$R.getField("u1K")] -- |PARTITIONED|
|
data-scan []<-[$$71, $$R, $$72] <- Default.R -- |PARTITIONED|
|
empty-tuple-source -- |PARTITIONED|
|
assign [$$78] <- [to-bigint($$84)] -- |PARTITIONED|
|
assign [$$85] <- [$$S.getField("u4K")] -- |PARTITIONED|
|
assign [$$84] <- [$$S.getField("u1K")] -- |PARTITIONED|
|
data-scan []<-[$$73, $$S, $$74] <- Default.S -- |PARTITIONED|
|
empty-tuple-source -- |PARTITIONED|
|
assign [$$80] <- [$$T.getField("u4K")] -- |PARTITIONED|
|
data-scan []<-[$$75, $$T, $$76] <- Default.T -- |PARTITIONED|
|
empty-tuple-source -- |PARTITIONED|
|
The problem happens because of these two lines
join (eq($$79, $$80)) – |PARTITIONED|
assign [$$79] <- [to-bigint($$85)] – |PARTITIONED| <<<<<< THIS ASSIGN!!
The CBO code used the assign in the "internal edge" to modify the join predicate as follows:
join(eq(to-bigint($$79), $$80).
This fails to pass the ifApplicable test to see if a nl index join is possible.
Consequently, we get a hash join between S and T followed by an index NL join with R.
Some of these queries produced an internal edge with an assign statement such as $$25 <-- tobigint ($$20)
The assign statement is then used to modify the next join predicate eq($$25, $$30) to
eq(tobigint($$20), $$30).
It turns out that NL index join code only accepts the original join predicate.
The fix will be to push down the assigns from the internal edge to the corresponding leaf node.
