Details
-
Bug
-
Resolution: Fixed
-
Major
-
6.6.3
-
Untriaged
-
1
-
Unknown
Description
Not an Issue with 7.x
repro:
CREATE PRIMARY index on default; |
explain select 1 FROM default s inner JOIN default j ON meta(j).id = s.docid and s.type = j.type WHERE s.type = "xyz"; |
{
|
"requestID": "09e6795f-dddc-44d4-b5dc-c59e42cb2ca9", |
"signature": "json", |
"results": [ |
{
|
"plan": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "PrimaryScan3", |
"as": "s", |
"cardinality": 1, |
"cost": 0.183, |
"index": "#primary", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"using": "gsi" |
},
|
{
|
"#operator": "Fetch", |
"as": "s", |
"keyspace": "default", |
"namespace": "default" |
},
|
{
|
"#operator": "Join", |
"as": "j", |
"keyspace": "default", |
"namespace": "default", |
"on_keys": "(`s`.`docid`)" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "Filter", |
"condition": "((`s`.`type`) = \"xyz\")" |
},
|
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "1" |
}
|
]
|
},
|
{
|
"#operator": "FinalProject" |
}
|
]
|
}
|
}
|
]
|
},
|
"text": "select 1 FROM default s inner JOIN default j ON meta(j).id = s.docid and s.type = j.type WHERE s.type = \"xyz\";" |
}
|
],
|
"status": "success", |
"metrics": { |
"elapsedTime": "70.289363ms", |
"executionTime": "70.09217ms", |
"resultCount": 1, |
"resultSize": 2039 |
}
|
}
|
Above query uses right side document key with additional filter
ON meta(j).id = s.docid and s.type = j.type
|
So this turn into Old ON KEYS JOIN, But there is additional filter that missed to apply.
In 7.x we do additional separate filter on JOIN
"on_keys": "(`s`.`docid`)", |
"filter": "((`s`.`type`) = (`j`.`type`))" |
Workaround:
As this INNER JOIN move s.type = j.type to WHERE, but same issue present for Outer JOIN.
For outer JOIN case until fix in 6.6.x make query not to transform ON KEYS and use ANSI JOIN
Example:
CREATE INDEX ix1 ON default(SUBSTR(META().id,0); |
explain select 1 FROM default s LEFT JOIN default j ON SUBSTR(meta(j).id,0) = s.docid and s.type = j.type WHERE s.type = "xyz"; |
cc Bingjie Miao
Attachments
| For Gerrit Dashboard: MB-49752 | ||||||
|---|---|---|---|---|---|---|
| # | Subject | Branch | Project | Status | CR | V |
| 166375,4 | MB-49752 Evaluate additional ON-clause filters when joining on document key | mad-hatter | query | Status: MERGED | +2 | +1 |
| 166537,2 | MB-49752 Modify fix | mad-hatter | query | Status: ABANDONED | 0 | 0 |