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
-
MB-55059 multi level JOIN on both sides gives wrong results/hangs
- Closed
-
MB-59552 [BP to 7.1.6-MP1 MB-54045] - CTE with sub select and hash join returns wrong result
- Closed
- causes
-
MB-60011 Not able to use primary index as secondary index scan inside correlated subquery
- Closed
- is caused by
-
MB-30813 correlated subquery quit correlation on where clause requires use keys - remove or relax limitation
- Closed