Large documents not returned by queries

SQL++
1

Hi,

I have a CB production database with some large (500kbyte - 600kbyte) documents, but queries (like select * from core where awId = ‘idvalue’) don’t return these docs (where awId is a field containing the same value as meta().id).
A similar query by meta().id does return the same doc, and shows that the field has the expected value.
I can also access the document in Couchbase Console / Data buckets / Documents by id.

My Couchbase server version is 4.5.0-2601 Enterprise Edition (build-2601)

I changed app.min.js to allow large document handling by the UI, and I copied one of these docs to a development environment, where I encountered the same issue.
I tried to change indexer configuration as described in https://issues.couchbase.com/browse/DOC-40
and Changing mapreduce limits , restarted CB service and waited a long while.

I checked Couchbase log files searched for the id of the document. Found it in access logs, but not in any errors.

What else can I do to make couchbase queries return these large documents?

Thanks
Balint

2

Is the following queries works. Post the EXPLAIN output. try with back tics around identifier preserve case sensitiveness.

select META().id from core where `awId` = "idvalue";
select META().id,* from core where `awId` = "idvalue";
3

None of them work, returned empty result:
{
“results”: [],
“metrics”: {
“elapsedTime”: “13.9640515s”,
“executionTime”: “13.9640515s”,
“resultCount”: 0,
“resultSize”: 0
}
}

Explain result:
[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “#primary”,
“keyspace”: “core”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “core”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “((core.awId) = “6074720c-d074-449d-ad99-9fd780c2f5da-MySales”)”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “meta(core)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “SELECT meta()\r\nFROM core\r\nWHERE awId = ‘6074720c-d074-449d-ad99-9fd780c2f5da-MySales’”
}
]

4

It is using PrimaryScan and it should have document can u post the sample document(no need whole document required only field awId where you are looking the value and its document key.

Is this works?

SELECT meta().id, awId FROM core WHERE META().id = "6074720c-d074-449d-ad99-9fd780c2f5da-MySales";

Also you can try this index and your original query.
CREATE INDEX ix_awid ON core(awId);

5

This query correctly returns the whole document:
SELECT * FROM core WHERE META().id = "6074720c-d074-449d-ad99-9fd780c2f5da-MySales"
Result (parts left out marked …):
[
{
“core”: {
“core”: {
“awChannels”: […],
“awChannelsState”: 0,
“awCurrentStatus”: […],
“awDocumentRevision”: 3,
“awGraph”: “MySales”,
“awId”: “6074720c-d074-449d-ad99-9fd780c2f5da-MySales”,
“awLastModifiedDate”: “2018-01-22T15:04:26.8689211Z”,
…
}
}
}
]

The query you suggested
SELECT meta().id, awId FROM core WHERE META().id = "6074720c-d074-449d-ad99-9fd780c2f5da-MySales"
returns an incomplete result (awId is missing):
[
{
“id”: “6074720c-d074-449d-ad99-9fd780c2f5da-MySales”
}
]

6

I also tried creating the suggested index. After that the error still remains (the following query still returns an empty result set):
SELECT * FROM core WHERE awId = ‘6074720c-d074-449d-ad99-9fd780c2f5da-MySales’
Result:
{
“results”: ,
“metrics”: {
“elapsedTime”: “22.0388ms”,
“executionTime”: “22.0388ms”,
“resultCount”: 0,
“resultSize”: 0
}
}

7

In fact, it seems that no matter what other fields I specify on the result list only meta().id is displayed, nothing else.

This query
SELECT meta().id, awId, awChannelsState, awDocumentRevision, awGraph
FROM core WHERE META().id = ‘6074720c-d074-449d-ad99-9fd780c2f5da-MySales’

returns
[
{
“id”: “6074720c-d074-449d-ad99-9fd780c2f5da-MySales”
}
]

8

You have bucket name “core” and document root also “core” that is why it is not getting results. Looking awId in the root but it is one level nested.
Normally document root has fields ( {“f1”:“val1”,“f2”:2})
Alias the bucket and use fully qualified name.

SELECT c.* FROM core AS c WHERE c.core.awId = '6074720c-d074-449d-ad99-9fd780c2f5da-MySales’;

9

Oh! Thanks. Must have made a copy/paste error when copying from production to dev.