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

HASH JOIN returns wrong results

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 6.6.3
    • 6.6.5
    • query
    • Untriaged
    • 1
    • Unknown

    Description

      FYI: Works in 7.0.0

      insert into default values ("car1",[ { "model": "bmw", "series": "x1" }, { "model": "renault", "series": "captur" }, { "model":"suzuki", "series":"vitara" } ] );
       SELECT item.* FROM ['bmw', 'renault'] model INNER JOIN ARRAY_FLATTEN((SELECT RAW car FROM default car WHERE meta(car).id = 'car1'),1) item  ON model = item.model;
      {
          "requestID": "dbee168e-3570-4572-ab89-06f098c9e8a6",
          "signature": {
              "*": "*"
          },
          "results": [
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "2.646726ms",
              "executionTime": "2.561986ms",
              "resultCount": 0,
              "resultSize": 0
          }
      }
      cbq> SELECT item.* FROM ['bmw', 'renault'] model INNER JOIN ARRAY_FLATTEN((SELECT RAW car FROM default car WHERE meta(car).id = 'car1'),1) item USE NL ON model = item.model;
      {
          "requestID": "cf795ee5-e429-4d94-a4a5-bc395af47d82",
          "signature": {
              "*": "*"
          },
          "results": [
          {
              "model": "bmw",
              "series": "x1"
          },
          {
              "model": "renault",
              "series": "captur"
          }
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "2.42608ms",
              "executionTime": "2.37998ms",
              "resultCount": 2,
              "resultSize": 116
          }
      }
      

      https://forums.couchbase.com/t/processing-document-which-contains-array-of-objects/31211

      Attachments

        For Gerrit Dashboard: MB-47547
        # Subject Branch Project Status CR V

        Activity

          In 6.6.x Hash Join is considered by default only when RHS of JOIN is expression. So in this case.

          Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - In 6.6.x Hash Join is considered by default only when RHS of JOIN is expression. So in this case.

          Build couchbase-server-6.6.4-9901 contains query commit 91a03bd with commit message:
          MB-47547 Properly fix up build/probe side of hash join if CBO switches them

          build-team Couchbase Build Team added a comment - Build couchbase-server-6.6.4-9901 contains query commit 91a03bd with commit message: MB-47547 Properly fix up build/probe side of hash join if CBO switches them

          Verified on 6.6.5-10060

          cbq> SELECT item.* FROM ['bmw', 'renault'] model INNER JOIN ARRAY_FLATTEN((SELECT RAW car FROM default car WHERE meta(car).id = 'car1'),1) item  ON model = item.model;
          {
              "requestID": "e901a749-feec-4cb3-8491-890d6de5fe27",
              "signature": {
                  "*": "*"
              },
              "results": [
              {
                  "model": "bmw",
                  "series": "x1"
              },
              {
                  "model": "renault",
                  "series": "captur"
              }
              ],
              "status": "success",
              "metrics": {
                  "elapsedTime": "58.01778ms",
                  "executionTime": "57.863128ms",
                  "resultCount": 2,
                  "resultSize": 116
              }
          }
           

          pierre.regazzoni Pierre Regazzoni added a comment - Verified on 6.6.5-10060 cbq> SELECT item.* FROM [ 'bmw' , 'renault' ] model INNER JOIN ARRAY_FLATTEN((SELECT RAW car FROM default car WHERE meta(car).id = 'car1' ), 1 ) item  ON model = item.model; {     "requestID" : "e901a749-feec-4cb3-8491-890d6de5fe27" ,     "signature" : {         "*" : "*"     },     "results" : [     {         "model" : "bmw" ,         "series" : "x1"     },     {         "model" : "renault" ,         "series" : "captur"     }     ],     "status" : "success" ,     "metrics" : {         "elapsedTime" : "58.01778ms" ,         "executionTime" : "57.863128ms" ,         "resultCount" : 2 ,         "resultSize" : 116     } }

          People

            pierre.regazzoni Pierre Regazzoni
            Sitaram.Vemulapalli Sitaram Vemulapalli
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty