Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.2.0
-
Security Level: Public
-
Untriaged
-
1
-
Yes
Description
INSERT INTO default VALUES("k01", {"id":1});
|
CREATE INDEX ix1 ON default (id);
|
with
|
cte1 as
|
(
|
select *
|
from
|
(select t1.*,t2.*
|
from [{"a":1}] as t1
|
join [{"b":1}] as t2
|
on t1.a = t2.b) lhs
|
join default AS c1 use hash(build) on lhs.a = c1.id
|
)
|
select raw 1
|
from cte1
|
Returns 0 results (expected 1). If remove USE HASH it gives correct results.
Issue 1) WITH clause query marked correlated which is not
2) c1 BUILD side of hash and it indexScan span has lhs.a which is not available i.e from probe side.
explain with cte1 as ( select * from (select t1.*,t2.* from [{"a":1}] as t1 join [{"b":1}] as t2 on t1.a = t2.b) lhs join default AS c1 use hash(build) on lhs.a = c1.id ) select raw 1 from cte1;
|
{
|
"requestID": "112edd39-6604-4de3-a560-cdf5e2357918",
|
"signature": "json",
|
"results": [
|
{
|
"cardinality": 10,
|
"cost": 0.168,
|
"plan": {
|
"#operator": "With",
|
"bindings": [
|
{
|
"expr": "correlated (select self.* from correlated (select `t1`.*, `t2`.* from [{\"a\": 1}] as `t1` join [{\"b\": 1}] as `t2` on ((`t1`.`a`) = (`t2`.`b`))) as lhs join `default`:`default` as `c1` on ((`lhs`.`a`) = (`c1`.`id`)))",
|
"static": true,
|
"var": "cte1"
|
}
|
],
|
"optimizer_estimates": {
|
"cardinality": 10,
|
"cost": 0.168,
|
"fr_cost": 0.024,
|
"size": 64
|
},
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "ExpressionScan",
|
"alias": "cte1",
|
"expr": "`cte1`",
|
"optimizer_estimates": {
|
"cardinality": 10,
|
"cost": 0.08,
|
"fr_cost": 0.008,
|
"size": 64
|
}
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "InitialProject",
|
"optimizer_estimates": {
|
"cardinality": 10,
|
"cost": 0.16,
|
"fr_cost": 0.016,
|
"size": 64
|
},
|
"raw": true,
|
"result_terms": [
|
{
|
"expr": "1"
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
}
|
},
|
"text": "with cte1 as ( select * from (select t1.*,t2.* from [{\"a\":1}] as t1 join [{\"b\":1}] as t2 on t1.a = t2.b) lhs join default AS c1 use hash(build) on lhs.a = c1.id ) select raw 1 from cte1;",
|
"~subqueries": [
|
{
|
"plan": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "ExpressionScan",
|
"alias": "t1",
|
"expr": "[{\"a\": 1}]",
|
"optimizer_estimates": {
|
"cardinality": 1,
|
"cost": 0.008,
|
"fr_cost": 0.008,
|
"size": 64
|
},
|
"uncorrelated": true
|
},
|
{
|
"#operator": "HashJoin",
|
"build_aliases": [
|
"t2"
|
],
|
"build_exprs": [
|
"(`t2`.`b`)"
|
],
|
"on_clause": "((`t1`.`a`) = (`t2`.`b`))",
|
"optimizer_estimates": {
|
"cardinality": 0.1,
|
"cost": 0.036525483399593904,
|
"fr_cost": 0.028525483399593904,
|
"size": 128
|
},
|
"probe_exprs": [
|
"(`t1`.`a`)"
|
],
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "ExpressionScan",
|
"alias": "t2",
|
"expr": "[{\"b\": 1}]",
|
"optimizer_estimates": {
|
"cardinality": 1,
|
"cost": 0.008,
|
"fr_cost": 0.008,
|
"size": 64
|
},
|
"uncorrelated": true
|
}
|
]
|
}
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "InitialProject",
|
"optimizer_estimates": {
|
"cardinality": 0.1,
|
"cost": 0.03878822509939086,
|
"fr_cost": 0.030788225099390856,
|
"size": 128
|
},
|
"result_terms": [
|
{
|
"expr": "`t1`",
|
"star": true
|
},
|
{
|
"expr": "`t2`",
|
"star": true
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
{
|
"#operator": "Alias",
|
"as": "lhs",
|
"optimizer_estimates": {
|
"cardinality": 0.1,
|
"cost": 0.03878822509939086,
|
"fr_cost": 0.030788225099390856,
|
"size": 128
|
}
|
},
|
{
|
"#operator": "HashJoin",
|
"build_aliases": [
|
"c1"
|
],
|
"build_exprs": [
|
"(`c1`.`id`)"
|
],
|
"on_clause": "((`lhs`.`a`) = (`c1`.`id`))",
|
"probe_exprs": [
|
"(`lhs`.`a`)"
|
],
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "IndexScan3",
|
"as": "c1",
|
"index": "idx1",
|
"index_id": "851cc04a862417cc",
|
"index_projection": {
|
"primary_key": true
|
},
|
"keyspace": "default",
|
"namespace": "default",
|
"spans": [
|
{
|
"exact": true,
|
"range": [
|
{
|
"high": "(`lhs`.`a`)",
|
"inclusion": 3,
|
"index_key": "`id`",
|
"low": "(`lhs`.`a`)"
|
}
|
]
|
}
|
],
|
"using": "gsi"
|
},
|
{
|
"#operator": "Fetch",
|
"as": "c1",
|
"keyspace": "default",
|
"namespace": "default"
|
}
|
]
|
}
|
},
|
{
|
"#operator": "Parallel",
|
"~child": {
|
"#operator": "Sequence",
|
"~children": [
|
{
|
"#operator": "InitialProject",
|
"result_terms": [
|
{
|
"expr": "self",
|
"star": true
|
}
|
]
|
}
|
]
|
}
|
}
|
]
|
},
|
"subquery": "select self.* from correlated (select `t1`.*, `t2`.* from [{\"a\": 1}] as `t1` join [{\"b\": 1}] as `t2` on ((`t1`.`a`) = (`t2`.`b`))) as lhs join `default`:`default` as `c1` on ((`lhs`.`a`) = (`c1`.`id`))"
|
}
|
]
|
}
|
],
|
"status": "success",
|
"metrics": {
|
"elapsedTime": "2.283671ms",
|
"executionTime": "2.221269ms",
|
"resultCount": 1,
|
"resultSize": 11068,
|
"serviceLoad": 2
|
}
|
}
|
Attachments
Issue Links
- backports to
-
-
- Closed
-
-
-
- Closed
-
- causes
-
-
- Closed
-
- is caused by
-
MB-30813 correlated subquery quit correlation on where clause requires use keys - remove or relax limitation
-
- Closed
-