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

join with double intersect scan returns no rows

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • Cheshire-Cat
    • 6.6.2, 7.0.0
    • query
    • Untriaged
    • 1
    • Yes

    Description

      Load travel-sample, drop all indexes created by default.

      create index def_type on `travel-sample`(type);
      create index def_city on `travel-sample`(city);

      select h.city, h.name, a.airportname from `travel-sample` a join `travel-sample` h on a.city = h.city where h.type = "hotel" and a.type = "airport";

      this query uses intersect scan of the two indexes under nested-loop join. Return no result.

      select h.city, h.name, a.airportname from `travel-sample` a join `travel-sample` h USE HASH(build) on a.city = h.city where h.type = "hotel" and a.type = "airport";

      this query forces hash join and returns 2212 result documents.

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MB-42825 Join queries hang without returning results

          • Major - Major loss of function.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MB-44331 Incorrect result with Subquery as right-hand side of ANSI JOIN, and subquery references CTE

          • Major - Major loss of function.
          • Closed
          For Gerrit Dashboard: MB-44258
          # Subject Branch Project Status CR V
          145674,2 MB-44258 join with intersect scan returns no rows, regression from MB 42825 mad-hatter query Status: MERGED +2 +1
          145819,4 MB-44258 join with intersect scan returns no rows, regression from MB 42825 master query Status: MERGED +2 +1

          Activity

            Ascending order - Click to sort in descending order
            build-team Couchbase Build Team added a comment -

            Build couchbase-server-7.0.0-4446 contains query commit 09578d9 with commit message:
            MB-44258 join with intersect scan returns no rows, regression from MB 42825

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.0-4446 contains query commit 09578d9 with commit message: MB-44258 join with intersect scan returns no rows, regression from MB 42825
            build-team Couchbase Build Team added a comment -

            Build couchbase-server-6.6.2-9503 contains query commit ac48bde with commit message:
            MB-44258 join with intersect scan returns no rows, regression from MB 42825

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.6.2-9503 contains query commit ac48bde with commit message: MB-44258 join with intersect scan returns no rows, regression from MB 42825
            pierre.regazzoni Pierre Regazzoni added a comment -

            Verified on 7.0.0-4545 and 6.6.2-9502:

                "status": "success",
            		 
                "metrics": {
            		 
                    "elapsedTime": "4.717027042s",
            		 
                    "executionTime": "4.716915376s",
            		 
                    "resultCount": 2212,
            		 
                    "resultSize": 256376,
            		 
                    "serviceLoad": 6
            		 
                }

            pierre.regazzoni Pierre Regazzoni added a comment - Verified on 7.0.0-4545 and 6.6.2-9502:     "status" : "success" ,     "metrics" : {         "elapsedTime" : "4.717027042s" ,         "executionTime" : "4.716915376s" ,         "resultCount" : 2212 ,         "resultSize" : 256376 ,         "serviceLoad" : 6     }

            People

              pierre.regazzoni Pierre Regazzoni
              marco.greco Marco Greco
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty