Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-54412

[CX] Cannot get two Nested Loop Index joins in the same query.

    XMLWordPrintable

Details

    • 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.

      Attachments

        Issue Links

          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

            There are no comments yet on this issue.

            People

              murali.krishna Murali Krishna
              murali.krishna Murali Krishna
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - 32h
                  32h
                  Remaining:
                  Remaining Estimate - 32h
                  32h
                  Logged:
                  Time Spent - Not Specified
                  Not Specified

                  Gerrit Reviews

                    There are no open Gerrit changes

                    PagerDuty