Im getting 2 document results of same id when querying couchbase see picture^^. It should return unique id, but in this case same id.
Is this a couchbase issue and if it is then anyone who can help how to resolved the issue?
Hi @couchbaselorence, welcome to the forums. I hope things are going well at Photo Butler.
What you’re seeing is very odd indeed. Keys are unique, and multiple results with the same key should not be returned, especially with a simple filter like that.
Would you mind trying that same query in Query Workbench and report back what you find? (Include any error messages, the query plan, anything you think might be relevant)
SELECT meta().id
FROM `sessions`
WHERE secret = "9825000383248009"
ORDER BY META().id
LIMIT 10
OFFSET 0
I’d be interested to know if Query Workbench returns the same thing.
Does this happen consistently? Does this happen with variations on the filter? What version of Couchbase Server are you using?
Hi @matthew.groves, thank you very much for your kind and immediate response.
Yes the query return 2 result of same document. when try to run in the Query Workbench.
This doesn’t happen all the time, but yesterday our QA recreated it, where when I query I get 2 results of same id.
fyi the result is same identical document, and when you delete one will also deleted the others.
And because you get 2 results the content of the document also doubled that results to wrong computation in my code.
We are using Couchbase version is 6.6.0
I’m somewhat at a loss. I’m hoping @vsr1 might be able to chime in. Can you show the indexes you have created?
I don’t think ever possible. Unless indexer gave same document twice.
Take the query dispalyed above ,run in Query Workbench and share Plan Text from UI. Which will have #ItemsIn, #ItemsOut each operator.
Post the EXPLAIN plan and index definition.
SELECT DISTINCT … See what you get.
Hi @vsr1, our DevOps restarted the couchbase and im not able recreate the query again. But once i encountered it again, I will post here the plan and will do as you suggest.
1 Like
@vsr1 @matthew.groves Hello guys, So im able to recreate now the query where i getting 2 results of same document.
and here is the explain in json
{
"#operator": "Sequence",
"~children": [{
"#operator": "Sequence",
"~children": [{
"#operator": "IndexScan3",
"as": "data",
"covers": [
"cover ((`data`.`secret`))",
"cover ((`data`.`status`))",
"cover ((meta(`data`).`id`))"
],
"index": "idx_sessionSecretStatus",
"index_id": "b9b8629944bc5b7f",
"index_projection": {
"entry_keys": [
0
],
"primary_key": true
},
"keyspace": "sessions",
"namespace": "default",
"spans": [{
"exact": true,
"range": [{
"high": "\"1234567890123456\"",
"inclusion": 3,
"low": "\"1234567890123456\""
}]
}],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [{
"#operator": "Filter",
"condition": "(cover ((`data`.`secret`)) = \"1234567890123456\")"
},
{
"#operator": "InitialProject",
"result_terms": [{
"expr": "cover ((meta(`data`).`id`))"
}]
}
]
}
}
]
},
{
"#operator": "Order",
"limit": "100",
"sort_terms": [{
"expr": "cover ((meta(`data`).`id`))"
}]
},
{
"#operator": "Limit",
"expr": "100"
},
{
"#operator": "FinalProject"
}
]
}
Execute query go to Plan Text and post that. #itemsOut from IndexScan
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "12.988µs"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"execTime": "24.678µs",
"servTime": "1.401253ms"
},
"privileges": {
"List": [
{
"Target": "default:sessions",
"Priv": 7
}
]
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "13.484µs"
},
"~children": [
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "11.518µs"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 2,
"#phaseSwitches": 11,
"execTime": "81.355µs",
"kernTime": "17.102µs",
"servTime": "1.851841ms"
},
"as": "data",
"covers": [
"cover ((`data`.`secret`))",
"cover ((`data`.`status`))",
"cover ((meta(`data`).`id`))"
],
"index": "sessions_secret",
"index_id": "49ba4f721b9add0e",
"index_projection": {
"entry_keys": [
0
],
"primary_key": true
},
"keyspace": "sessions",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"1234567890123456\"",
"inclusion": 3,
"low": "\"1234567890123456\""
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.001",
"#time_absolute": 0.001933196
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "11.595µs"
},
"~children": [
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 2,
"#itemsOut": 2,
"#phaseSwitches": 11,
"execTime": "300.517µs",
"kernTime": "2.04284ms"
},
"condition": "(cover ((`data`.`secret`)) = \"1234567890123456\")",
"#time_normal": "00:00.000",
"#time_absolute": 0.000300517
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 2,
"#itemsOut": 2,
"#phaseSwitches": 11,
"execTime": "69.35µs",
"kernTime": "2.397499ms"
},
"result_terms": [
{
"expr": "cover ((meta(`data`).`id`))"
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.00006934999999999999
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000011595
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000011518
},
{
"#operator": "Order",
"#stats": {
"#itemsIn": 2,
"#itemsOut": 2,
"#phaseSwitches": 11,
"execTime": "109.287µs",
"kernTime": "2.574137ms"
},
"limit": "100",
"sort_terms": [
{
"expr": "cover ((meta(`data`).`id`))"
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.00010928700000000001
},
{
"#operator": "Limit",
"#stats": {
"#itemsIn": 2,
"#itemsOut": 2,
"#phaseSwitches": 5,
"execTime": "51.536µs"
},
"expr": "100",
"#time_normal": "00:00.000",
"#time_absolute": 0.000051536
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 2,
"#itemsOut": 2,
"#phaseSwitches": 7,
"execTime": "50.917µs",
"kernTime": "65.473µs"
},
"#time_normal": "00:00.000",
"#time_absolute": 0.000050917
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000013484
},
"#time_normal": "00:00.001",
"#time_absolute": 0.001425931
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 2,
"#itemsOut": 2,
"#phaseSwitches": 7,
"execTime": "74.76µs",
"kernTime": "4.41368ms"
},
"#time_normal": "00:00.000",
"#time_absolute": 0.00007476000000000001
}
],
"~versions": [
"6.5.0-N1QL",
"6.6.0-7909-enterprise"
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000012988
}Can you verify indexer directly with following command
Also provide index definition.
cbindex -type scan -bucket sessions -low='["123456789012345"]' -high='["123456789012345"]' -incl 3 -index sessions_secret -auth Administrator:password
Sorry I can’t follow, what command and where should i run it?
cc @jeelan.poola Indexer looks like giving same document key twice for non array index.
I already gave cbindex. Run this in shell on the indexer node with cbdindex exist as part of couchbase installations.
replace login/password and values if needed
Example:
This command scan index for value “123456789012345” on first index key of ix20. It says key k01
cbindex -type scan -bucket default -low='["123456789012345"]' -high='["123456789012345"]' -incl 3 -index ix20 -auth Administrator:password
Scan index:
["123456789012345"] ... k01
Total number of entries: 1
Hi @couchbaselorence ,
What is index definition for sessions_secret? If it partition index did you change partition key value. partition keys must be immutable.