Hi, I’ve got this query to test joins:
SELECT s.* FROM clones c JOIN samples s ON KEYS (c.run_name || “_” || c.sample_name) limit 1;
Which gives this result:
[
{
“chain”: “BCRh”,
“run_name”: “run19”,
“species”: “human”
}
]
But when I add "where s.chain = “BCRh” to it, I get no results?
SELECT s.* FROM clones c JOIN samples s ON KEYS (c.run_name || “_” || c.sample_name) WHERE s.chain = “BCRh” limit 1;
What am I doing wrong?
vsr1
2
What is couchbase version and Post explain output
Enterprise Edition 5.0.0 build 3519
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “DistinctScan”,
“scan”: {
"#operator": “IndexScan2”,
“index”: “vjcdr3”,
“index_id”: “f8c6cc1c9a2e86c9”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “clones”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “[“chain”, “BCRh”]”,
“inclusion”: 3,
“low”: “[“chain”, “BCRh”]”
}
]
}
],
“using”: “gsi”
}
},
{
"#operator": “Fetch”,
“as”: “c”,
“keyspace”: “clones”,
“namespace”: “default”
},
{
"#operator": “Join”,
“as”: “s”,
“keyspace”: “samples”,
“namespace”: “default”,
“on_keys”: “(((c
.run_name
) || “") || (c
.sample_name
))"
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “((s
.chain
) = “BCRh”)”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “(s
.chain
)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
{
"#operator": “Limit”,
“expr”: “1”
}
]
},
“text”: "SELECT s.chain FROM clones c JOIN samples s ON KEYS (c.run_name || "” || c.sample_name) WHERE s.chain = “BCRh” limit 1;”
}
vsr1
4
It looks like index vjcdr3 is adaptive index and opened MB-28330