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

[ANSI JOIN] ROJ query hangs

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 5.5.0
    • 5.5.0
    • query
    • 5.5.0-1753
    • Untriaged
    • 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

            ajay.bhullar Ajay Bhullar added a comment -

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

            ajay.bhullar Ajay Bhullar added a comment - Same limit issue as MB-27862 , verified in 5.5.0-2314
            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.

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

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

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

            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

            People

              ajay.bhullar Ajay Bhullar
              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