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

join with double intersect scan returns no rows

    XMLWordPrintable

Details

    • 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

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                PagerDuty