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

            bingjie.miao Bingjie Miao added a comment -

            Ajay Bhullar You first said there is no index other than primary on default, then in the indexes section you included this:

            default(name)

            Do you have this index or not? This index is required for the execution.

            Please also include the explain for the query, and a backup of the two buckets as well.

            Thanks.

            bingjie.miao Bingjie Miao added a comment - Ajay Bhullar You first said there is no index other than primary on default, then in the indexes section you included this: default(name) Do you have this index or not? This index is required for the execution. Please also include the explain for the query, and a backup of the two buckets as well. Thanks.
            ajay.bhullar Ajay Bhullar added a comment - - edited

            for some reason I could not do a cbcollect-info on my last run, will upload the requested information when the error is reproduced. Scratch that last part I do have the idx on default(name), will upload the info if I can i don't know if its the bug or my VM but I cannot use cb-collect info on the vagrant when it fails, will try a different vagrant

            ajay.bhullar Ajay Bhullar added a comment - - edited for some reason I could not do a cbcollect-info on my last run, will upload the requested information when the error is reproduced. Scratch that last part I do have the idx on default(name), will upload the info if I can i don't know if its the bug or my VM but I cannot use cb-collect info on the vagrant when it fails, will try a different vagrant
            ajay.bhullar Ajay Bhullar added a comment - - edited

            Bingjie Miao backup and explain attached, for some reason I am having trouble generating logs still, but after completely destroying and recreating vagrants that is still an issue. The indexes listed in the description are correct 

            ajay.bhullar Ajay Bhullar added a comment - - edited Bingjie Miao backup and explain attached, for some reason I am having trouble generating logs still, but after completely destroying and recreating vagrants that is still an issue. The indexes listed in the description are correct 
            bingjie.miao Bingjie Miao added a comment -

            Ajay Bhullar The content of the backup file is different that what I expected. How do I restore from this backup?

            bingjie.miao Bingjie Miao added a comment - Ajay Bhullar The content of the backup file is different that what I expected. How do I restore from this backup?
            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
            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 -

            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 -

            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 -

            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

            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