Blocker Description
Default bucket with primary index and index on join_yr. Create some data with join_yr field, half of which have join_yr=2010 and half that have join_yr=2011. The following queries should use hash joins but use nested loop join instead.
with with_table as (select * from default d0 where join_yr == 2010 order by meta(d0).id limit 1)
select *
from default AS table1
join with_table as table2
on (table1.join_yr == table2.d0.join_yr)
and
with with_table as (select * from default d0 where join_yr == 2010 order by meta(d0).id limit 1)
select *
from default AS table1
join with_table as table2
use hash(probe)
on (table1.join_yr == table2.d0.join_yr)
and
with with_table as (select * from default d0 where join_yr == 2010 order by meta(d0).id limit 1)
select *
from default AS table1
join with_table as table2
use hash(build)
on (table1.join_yr == table2.d0.join_yr)
example plan:
{
|
"plan": { |
"#operator": "With", |
"bindings": [ |
{
|
"expr": "(select self.* from `default` as `d0` where ((`d0`.`join_yr`) = 2010) order by (meta(`d0`).`id`) limit 1)", |
"var": "with_table" |
}
|
],
|
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "IndexScan3", |
"as": "table1", |
"index": "idx1", |
"index_id": "b9879af3e87e423", |
"index_projection": { |
"primary_key": true |
},
|
"keyspace": "default", |
"namespace": "default", |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"inclusion": 0, |
"low": "null" |
}
|
]
|
}
|
],
|
"using": "gsi" |
},
|
{
|
"#operator": "Fetch", |
"as": "table1", |
"keyspace": "default", |
"namespace": "default" |
},
|
{
|
"#operator": "Parallel", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "NestedLoopJoin", |
"alias": "table2", |
"on_clause": "((`table1`.`join_yr`) = ((`table2`.`d0`).`join_yr`))", |
"~child": { |
"#operator": "Sequence", |
"~children": [ |
{
|
"#operator": "ExpressionScan", |
"alias": "table2", |
"expr": "`with_table`" |
}
|
]
|
}
|
},
|
{
|
"#operator": "InitialProject", |
"result_terms": [ |
{
|
"expr": "self", |
"star": true |
}
|
]
|
},
|
{
|
"#operator": "FinalProject" |
}
|
]
|
}
|
}
|
]
|
}
|
},
|
"text": "with with_table as (select * from default d0 where join_yr == 2010 order by meta(d0).id limit 1) select * from default AS table1 join with_table as table2 use hash(probe) on (table1.join_yr == table2.d0.join_yr)" |
}
|
Attachments
| For Gerrit Dashboard: MB-31629 | ||||||
|---|---|---|---|---|---|---|
| # | Subject | Branch | Project | Status | CR | V |
| 100664,1 | MB-31629 temp | master | query | Status: ABANDONED | 0 | 0 |
| 100758,3 | MB-31629 CTE join uses NL and not hash | master | query | Status: MERGED | +2 | +1 |