N1QL Query stops working in 5.5.1 when keys has empty string, works in 5.0.1

server

#1

The following N1QL Query works in Community Edition 5.0.1 build 5003 but stops working in 5.5.1 which we are just testing.

select meta(variant).id, variant.parent_id fromdatastoreitemsvariant LEFT JOINdatastoreitemsparent ON KEYS variant.parent_id WHERE variant.datastore_id = 'rVY' AND variant.customer_id = '3d50db41-b800-11e8-a9de-2614d88ffdfc' AND parent IS MISSING

in 5.5.1. we get the following error:
Code=12008
Error performing bulk get operation - cause: MCResponse status=EINVAL, opcode=GET, opaque=0, msg: "

The query console also shows a yellow exclamation mark with the message in the screenshot:

This query contains the following fields not found in the inferred schema for their bucket, the field names might be misspelled: datastoreitems.id

EXPLAIN:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IntersectScan",
        "scans": [
          {
            "#operator": "IndexScan3",
            "as": "variant",
            "index": "idx_flowrunid",
            "index_id": "4f6f0542a4f89052",
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "datastoreitems",
            "namespace": "default",
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "\"rVY\"",
                    "inclusion": 3,
                    "low": "\"rVY\""
                  }
                ]
              }
            ],
            "using": "gsi"
          },
          {
            "#operator": "IndexScan3",
            "as": "variant",
            "index": "idx_customer",
            "index_id": "d93401a00a117a00",
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "datastoreitems",
            "namespace": "default",
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "\"3d50db41-b800-11e8-a9de-2614d88ffdfc\"",
                    "inclusion": 3,
                    "low": "\"3d50db41-b800-11e8-a9de-2614d88ffdfc\""
                  }
                ]
              }
            ],
            "using": "gsi"
          }
        ]
      },
      {
        "#operator": "Fetch",
        "as": "variant",
        "keyspace": "datastoreitems",
        "namespace": "default"
      },
      {
        "#operator": "Join",
        "as": "parent",
        "keyspace": "datastoreitems",
        "namespace": "default",
        "on_keys": "(`variant`.`parent_id`)",
        "outer": true
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "((((`variant`.`datastore_id`) = \"rVY\") and ((`variant`.`customer_id`) = \"3d50db41-b800-11e8-a9de-2614d88ffdfc\")) and (`parent` is missing))"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "(meta(`variant`).`id`)"
                },
                {
                  "expr": "(`variant`.`parent_id`)"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "select meta(`variant`).id, variant.parent_id from `datastoreitems` variant LEFT JOIN `datastoreitems` parent ON KEYS variant.parent_id   WHERE variant.datastore_id = 'rVY' AND variant.customer_id = '3d50db41-b800-11e8-a9de-2614d88ffdfc' AND parent IS MISSING"
}

Related indexes:

CREATE INDEXidx_datastore_parentidONdatastoreitems(parent_id,datastore_id) WITH { "defer_build":true }

CREATE INDEXidx_variantids_lengthONdatastoreitems(object_length(ifmissingornull(variantids, {}))) WHERE (parent_id= "") WITH { "defer_build":true }

CREATE INDEXidx_datastoreONdatastoreitems(datastore_id,folder) WITH { "defer_build":true }

CREATE INDEXidx_customerONdatastoreitems(customer_id) WITH { "defer_build":true }

Any ideas?


#2

The error is coming from memcached not sure why? The query using IntersectScan which is not optimal. The following is right index.

If problem still exist kill cbq-engine process which automatically restarts.

CREATE INDEX idx1 ON datastoreitems(datastore_id, customer_id, master_id,parent_id);


#3

Thanks for your response.
Creating the index didn’t help so far.

We are running couchbase (Enterprise Edition 5.5.1 build 3511) in a Docker container. Just restarted the container and tried again.
Here is the excerpt from docker-compose:

couchbase:
        image: 'couchbase/server'
        volumes:
          - './temp/couchbase:/opt/couchbase/var'
        ports: 
          - "8091-8094:8091-8094"
          - "11210:11210"
        cpu_count: 2
        mem_limit: 2g

I continue digging.


#4

Hum. Is simple SELECT with single USE KEYS working.


#5

SELECT … USE KEYS seems to work and returns results.

I have executed the query without the join to see the documents on the left side of the LEFT JOIN:

select meta(`variant`).id, variant.parent_id, parent.id as mid from `datastoreitems` variant   WHERE variant.datastore_id = 'rVY' AND variant.customer_id = '3d50db41-b800-11e8-a9de-2614d88ffdfc'

There is only a single document with an empty string as parent_id

[
  {
    "id": "D::mymasterparentid::default::rVY",
    "parent_id": ""
  }
]

Could this cause the problem in the LEFT JOIN query?


#6

Is this uses KV Fetch. What is variant.master_id


#7

variant.master_id was a typo (from another query) in my initial post. I correct my initial question.
I retried using the corrected query, but still same error.

select meta(variant).id, variant.parent_id fromdatastoreitemsvariant LEFT JOINdatastoreitemsparent ON KEYS variant.parent_id WHERE variant.datastore_id = 'rVY' AND variant.customer_id = '3d50db41-b800-11e8-a9de-2614d88ffdfc' AND parent IS MISSING

EXPLAIN (including the index suggested by you)

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexScan3",
        "as": "variant",
        "covers": [
          "cover ((`variant`.`datastore_id`))",
          "cover ((`variant`.`customer_id`))",
          "cover ((`variant`.`master_id`))",
          "cover ((`variant`.`parent_id`))",
          "cover ((meta(`variant`).`id`))"
        ],
        "index": "idx1",
        "index_id": "71f2daa91a4e4cb",
        "index_projection": {
          "entry_keys": [
            0,
            1,
            3
          ],
          "primary_key": true
        },
        "keyspace": "datastoreitems",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "high": "\"rVY\"",
                "inclusion": 3,
                "low": "\"rVY\""
              },
              {
                "high": "\"3d50db41-b800-11e8-a9de-2614d88ffdfc\"",
                "inclusion": 3,
                "low": "\"3d50db41-b800-11e8-a9de-2614d88ffdfc\""
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "Join",
        "as": "parent",
        "keyspace": "datastoreitems",
        "namespace": "default",
        "on_keys": "cover ((`variant`.`parent_id`))",
        "outer": true
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "(((cover ((`variant`.`datastore_id`)) = \"rVY\") and (cover ((`variant`.`customer_id`)) = \"3d50db41-b800-11e8-a9de-2614d88ffdfc\")) and (`parent` is missing))"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "cover ((meta(`variant`).`id`))"
                },
                {
                  "expr": "cover ((`variant`.`parent_id`))"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "select meta(`variant`).id, variant.parent_id from `datastoreitems` variant LEFT JOIN `datastoreitems` parent ON KEYS variant.parent_id   WHERE variant.datastore_id = 'rVY' AND variant.customer_id = '3d50db41-b800-11e8-a9de-2614d88ffdfc' AND parent IS MISSING"
}

I noticed something else.
When I execute the query multiple times I get 3x different errors:

1st:

[
  {
    "code": 12008,
    "msg": "Error performing bulk get operation  - cause: MCResponse status=EINVAL, opcode=GET, opaque=0, msg: "
  }
]

2nd

[
  {
    "code": 12008,
    "msg": "Error performing bulk get operation  - cause: write tcp 127.0.0.1:55958->127.0.0.1:11210: write: broken pipe"
  }
]

3rd:

[
  {
    "code": 12008,
    "msg": "Error performing bulk get operation  - cause: EOF"
  }
]

I didn’t notice this effect the first time.

Does not look like a communication / port problem?


#8

The problem is following query.

SELECT * 
FROM default USE KEYS "";

Opened MB-31307. Fix will be available in 5.5.2 . cc @synesty

Can you try direct KV GET using SDKS with document key “” in both 5.5.x and 5.0


#9

Ah ok. Yes this query always shows an error in CB console.

With SDK (using 2.5.2) an a KV lookup with an empty string ("") I do not get this error, but a different one:

JsonDocument jsonDocument = bucket.get("");

Caused by: java.lang.IllegalArgumentException: The Document ID must not be null or empty.
	at com.couchbase.client.core.node.locate.KeyValueLocator.keyIsValid(KeyValueLocator.java:298) ~[na:na]
	at com.couchbase.client.core.node.locate.KeyValueLocator.locateForCouchbaseBucket(KeyValueLocator.java:118) ~[na:na]
	at com.couchbase.client.core.node.locate.KeyValueLocator.locateAndDispatch(KeyValueLocator.java:85) ~[na:na]
	at com.couchbase.client.core.RequestHandler.dispatchRequest(RequestHandler.java:250) ~[na:na]
	at com.couchbase.client.core.RequestHandler.onEvent(RequestHandler.java:201) ~[na:na]
	at com.couchbase.client.core.RequestHandler.onEvent(RequestHandler.java:77) ~[na:na]
	at com.couchbase.client.deps.com.lmax.disruptor.BatchEventProcessor.run(BatchEventProcessor.java:137) ~[na:na]
	at com.couchbase.client.deps.io.netty.util.concurrent.DefaultThreadFactory$DefaultRunnableDecorator.run(DefaultThreadFactory.java:138) ~[na:na]
	... 1 common frames omitted

It seems the SDK handles it already.


#10

Thanks for the info. Hope that 5.5.2 will be available as CE any time soon.


#11

I can confirm this bug is fixed for us in CB 6.0 CE. Thanks for that :slight_smile: