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

[ANSI JOIN] ROJ query hangs

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.0
    • Fix Version/s: 5.5.0
    • Component/s: query
    • Labels:
    • Environment:
      5.5.0-1753
    • Triage:
      Untriaged
    • Is this a Regression?:
      Unknown

      Description

      This query:
      SELECT * FROM default d RIGHT OUTER JOIN standard_bucket0 s ON (d.name == s.name) limit 10

      Never finishes executing

      There are two buckets default and standard_bucket0, both have the exact same 12096 docs in them and the indexes present are:
      primary(default)

      primary(standard)

      default(name)

       

      Explain:

      {
      "plan": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "PrimaryScan3",
      "as": "s",
      "index": "#primary",
      "index_projection": {
      "primary_key": true
      },
      "keyspace": "standard_bucket0",
      "namespace": "default",
      "using": "gsi"
      },
      {
      "#operator": "Fetch",
      "as": "s",
      "keyspace": "standard_bucket0",
      "namespace": "default"
      },
      {
      "#operator": "Parallel",
      "~child": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "AnsiJoin",
      "alias": "d",
      "on_clause": "((`d`.`name`) = (`s`.`name`))",
      "outer": true,
      "~child": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "IndexScan3",
      "ansi_join": true,
      "as": "d",
      "index": "idx1",
      "index_id": "f6fb7495c5f7760b",
      "index_projection": {
      "primary_key": true
      },
      "keyspace": "default",
      "namespace": "default",
      "spans": [
      {
      "exact": true,
      "range": [
      {
      "high": "(`s`.`name`)",
      "inclusion": 3,
      "low": "(`s`.`name`)"
      }
      ]
      }
      ],
      "using": "gsi"
      },
      {
      "#operator": "Fetch",
      "ansi_join": true,
      "as": "d",
      "keyspace": "default",
      "namespace": "default"
      }
      ]
      }
      },
      {
      "#operator": "InitialProject",
      "result_terms": [
      {
      "expr": "self",
      "star": true
      }
      ]
      },
      {
      "#operator": "FinalProject"
      }
      ]
      }
      }
      ]
      },
      {
      "#operator": "Limit",
      "expr": "10"
      }
      ]
      },
      "text": "SELECT * FROM default d RIGHT OUTER JOIN standard_bucket0 s ON (d.name == s.name) LIMIT 10"
      }

        Attachments

          Issue Links

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

            Activity

            Hide
            ajay.bhullar Ajay Bhullar added a comment -

            This backup is the same as previous backups I have attached, what is different about this one? Bingjie Miao

            Show
            ajay.bhullar Ajay Bhullar added a comment - This backup is the same as previous backups I have attached, what is different about this one? Bingjie Miao
            Hide
            bingjie.miao Bingjie Miao added a comment -

            The structure of the directory looks different. Can you try restore from this backup and let me know what command works? Ajay Bhullar

            Show
            bingjie.miao Bingjie Miao added a comment - The structure of the directory looks different. Can you try restore from this backup and let me know what command works? Ajay Bhullar
            Hide
            ajay.bhullar Ajay Bhullar added a comment -

            Bingjie Miao the same restory command should work, for some reason there is an extra directory that backups nothing, remember that you need the two buckets created on your server first.

            ./cbbackupmgr restore --archive /data/backups --repo MB-27763 --cluster http://127.0.0.1:8091 --username Administrator --password password

             

            I just unzipped the tar in the archive directory I created

            Show
            ajay.bhullar Ajay Bhullar added a comment - Bingjie Miao the same restory command should work, for some reason there is an extra directory that backups nothing, remember that you need the two buckets created on your server first. ./cbbackupmgr restore --archive /data/backups --repo MB-27763 --cluster http://127.0.0.1:8091 --username Administrator --password password   I just unzipped the tar in the archive directory I created
            Hide
            bingjie.miao Bingjie Miao added a comment -

            I believe the original hang is due to the backfill bug (MB-27201), once that's resolved, the query completes but is still executing for long and not ending after the first 10 results are retrieved. This secondary issue has been fixed by MB-28159. Now the query returns quickly after 10 results, as expected. Use build 5.5.0-1903 or later for verification.

            Show
            bingjie.miao Bingjie Miao added a comment - I believe the original hang is due to the backfill bug ( MB-27201 ), once that's resolved, the query completes but is still executing for long and not ending after the first 10 results are retrieved. This secondary issue has been fixed by MB-28159 . Now the query returns quickly after 10 results, as expected. Use build 5.5.0-1903 or later for verification.
            Hide
            ajay.bhullar Ajay Bhullar added a comment -

            Same limit issue as MB-27862, verified in 5.5.0-2314

            Show
            ajay.bhullar Ajay Bhullar added a comment - Same limit issue as MB-27862 , verified in 5.5.0-2314

              People

              • Assignee:
                ajay.bhullar Ajay Bhullar
                Reporter:
                ajay.bhullar Ajay Bhullar
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Gerrit Reviews

                  There are no open Gerrit changes

                    PagerDuty

                    Error rendering 'com.pagerduty.jira-server-plugin:PagerDuty'. Please contact your Jira administrators.