angelzouxin commented on a change in pull request #2116:
URL: https://github.com/apache/calcite/pull/2116#discussion_r495724588
##########
File path:
core/src/test/java/org/apache/calcite/test/enumerable/EnumerableBatchNestedLoopJoinTest.java
##########
@@ -224,4 +228,104 @@
.with(CalciteConnectionProperty.FORCE_DECORRELATE, forceDecorrelate)
.withSchema("s", new ReflectiveSchema(schema));
}
+
+
+ @Test void jdbcLeftBatchJoinTestSQL1() {
+ final String[] sqls = {null};
+ CalciteAssert.model(JdbcTest.FOODMART_SCOTT_MODEL)
+ .with(Lex.MYSQL)
+ .query(
+ "select e.empno, e.ename, f.store_name, f.store_id from\n"
+ + "(select store_name, store_id from foodmart.store where
store_id < 2) f\n"
+ + " left join SCOTT.emp e on f.store_id < e.empno and e.empno
<= 7369")
+ .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
+ planner.removeRule(EnumerableRules.ENUMERABLE_CORRELATE_RULE);
+
planner.addRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE);
+ })
+ .withHook(Hook.QUERY_PLAN, (Consumer<String>) sql -> {
+ sqls[0] = sql;
+ })
+ .returnsUnordered(
+ "empno=7369; ename=SMITH; store_name=HQ; store_id=0",
+ "empno=7369; ename=SMITH; store_name=Store 1; store_id=1");
+
+ assertThat(sqls[0],
+ isLinux("SELECT *\n"
+ + "FROM (SELECT \"EMPNO\", \"ENAME\"\n"
+ + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
+ + "WHERE \"EMPNO\" <= 7369 AND (? < \"EMPNO\" OR (? < \"EMPNO\" OR
? < \"EMPNO\") OR (?"
Review comment:
EnumerableBatchNestedLoopJoinRule will add a filter condition with
batchSize
https://github.com/apache/calcite/blob/43cf101576e3108f94aa51ffe4257a1d2a37823e/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableBatchNestedLoopJoinRule.java#L137
such as
`call#555: Apply rule [ReduceExpressionsRule(Filter)] to
[rel#96:LogicalFilter.NONE.[](input=RelSubset#47,condition=OR(AND(<($cor0.store_id,
$0), <=($0, ?0)), AND(<($cor1.store_id, $0), <=($0, ?0)),
AND(<($cor2.store_id, $0), <=($0, ?0)), AND(<($cor3.store_id, $0), <=($0, ?0)),
AND(<($cor4.store_id, $0), <=($0, ?0)), AND(<($cor5.store_id, $0), <=($0, ?0)),
AND(<($cor6.store_id, $0), <=($0, ?0)), AND(<($cor7.store_id, $0), <=($0, ?0)),
AND(<($cor8.store_id, $0), <=($0, ?0)), AND(<($cor9.store_id, $0), <=($0, ?0)),
AND(<($cor10.store_id, $0), <=($0, ?0)), AND(<($cor11.store_id, $0), <=($0,
?0)), AND(<($cor12.store_id, $0), <=($0, ?0)), AND(<($cor13.store_id, $0),
<=($0, ?0)), AND(<($cor14.store_id, $0), <=($0, ?0)), AND(<($cor15.store_id,
$0), <=($0, ?0)), AND(<($cor16.store_id, $0), <=($0, ?0)),
AND(<($cor17.store_id, $0), <=($0, ?0)), AND(<($cor18.store_id, $0), <=($0,
?0)), AND(<($cor19.store_id, $0), <=($0, ?0)), AND(<($cor20.store_id, $0),
<=($0, ?0)), AND(<($cor21.store_id, $0
), <=($0, ?0)), AND(<($cor22.store_id, $0), <=($0, ?0)),
AND(<($cor23.store_id, $0), <=($0, ?0)), AND(<($cor24.store_id, $0), <=($0,
?0)), AND(<($cor25.store_id, $0), <=($0, ?0)), AND(<($cor26.store_id, $0),
<=($0, ?0)), AND(<($cor27.store_id, $0), <=($0, ?0)), AND(<($cor28.store_id,
$0), <=($0, ?0)), AND(<($cor29.store_id, $0), <=($0, ?0)),
AND(<($cor30.store_id, $0), <=($0, ?0)), AND(<($cor31.store_id, $0), <=($0,
?0)), AND(<($cor32.store_id, $0), <=($0, ?0)), AND(<($cor33.store_id, $0),
<=($0, ?0)), AND(<($cor34.store_id, $0), <=($0, ?0)), AND(<($cor35.store_id,
$0), <=($0, ?0)), AND(<($cor36.store_id, $0), <=($0, ?0)),
AND(<($cor37.store_id, $0), <=($0, ?0)), AND(<($cor38.store_id, $0), <=($0,
?0)), AND(<($cor39.store_id, $0), <=($0, ?0)), AND(<($cor40.store_id, $0),
<=($0, ?0)), AND(<($cor41.store_id, $0), <=($0, ?0)), AND(<($cor42.store_id,
$0), <=($0, ?0)), AND(<($cor43.store_id, $0), <=($0, ?0)),
AND(<($cor44.store_id, $0), <=($0, ?0)), AND(<($cor45.store_id, $0), <=($0, ?0)
), AND(<($cor46.store_id, $0), <=($0, ?0)), AND(<($cor47.store_id, $0), <=($0,
?0)), AND(<($cor48.store_id, $0), <=($0, ?0)), AND(<($cor49.store_id, $0),
<=($0, ?0)), AND(<($cor50.store_id, $0), <=($0, ?0)), AND(<($cor51.store_id,
$0), <=($0, ?0)), AND(<($cor52.store_id, $0), <=($0, ?0)),
AND(<($cor53.store_id, $0), <=($0, ?0)), AND(<($cor54.store_id, $0), <=($0,
?0)), AND(<($cor55.store_id, $0), <=($0, ?0)), AND(<($cor56.store_id, $0),
<=($0, ?0)), AND(<($cor57.store_id, $0), <=($0, ?0)), AND(<($cor58.store_id,
$0), <=($0, ?0)), AND(<($cor59.store_id, $0), <=($0, ?0)),
AND(<($cor60.store_id, $0), <=($0, ?0)), AND(<($cor61.store_id, $0), <=($0,
?0)), AND(<($cor62.store_id, $0), <=($0, ?0)), AND(<($cor63.store_id, $0),
<=($0, ?0)), AND(<($cor64.store_id, $0), <=($0, ?0)), AND(<($cor65.store_id,
$0), <=($0, ?0)), AND(<($cor66.store_id, $0), <=($0, ?0)),
AND(<($cor67.store_id, $0), <=($0, ?0)), AND(<($cor68.store_id, $0), <=($0,
?0)), AND(<($cor69.store_id, $0), <=($0, ?0)), AND(<($cor
70.store_id, $0), <=($0, ?0)), AND(<($cor71.store_id, $0), <=($0, ?0)),
AND(<($cor72.store_id, $0), <=($0, ?0)), AND(<($cor73.store_id, $0), <=($0,
?0)), AND(<($cor74.store_id, $0), <=($0, ?0)), AND(<($cor75.store_id, $0),
<=($0, ?0)), AND(<($cor76.store_id, $0), <=($0, ?0)), AND(<($cor77.store_id,
$0), <=($0, ?0)), AND(<($cor78.store_id, $0), <=($0, ?0)),
AND(<($cor79.store_id, $0), <=($0, ?0)), AND(<($cor80.store_id, $0), <=($0,
?0)), AND(<($cor81.store_id, $0), <=($0, ?0)), AND(<($cor82.store_id, $0),
<=($0, ?0)), AND(<($cor83.store_id, $0), <=($0, ?0)), AND(<($cor84.store_id,
$0), <=($0, ?0)), AND(<($cor85.store_id, $0), <=($0, ?0)),
AND(<($cor86.store_id, $0), <=($0, ?0)), AND(<($cor87.store_id, $0), <=($0,
?0)), AND(<($cor88.store_id, $0), <=($0, ?0)), AND(<($cor89.store_id, $0),
<=($0, ?0)), AND(<($cor90.store_id, $0), <=($0, ?0)), AND(<($cor91.store_id,
$0), <=($0, ?0)), AND(<($cor92.store_id, $0), <=($0, ?0)),
AND(<($cor93.store_id, $0), <=($0, ?0)), AND(<($cor94.store_id,
$0), <=($0, ?0)), AND(<($cor95.store_id, $0), <=($0, ?0)),
AND(<($cor96.store_id, $0), <=($0, ?0)), AND(<($cor97.store_id, $0), <=($0,
?0)), AND(<($cor98.store_id, $0), <=($0, ?0)), AND(<($cor99.store_id, $0),
<=($0, ?0))))]`
so this query becomes very complicated.
The above SQL is just the right part of left join in test case. The join
logic is executed by EnumerableBatchNestedLoopJoin.
https://github.com/apache/calcite/blob/978bb7ea44969351468d1b5e240e8f57af7e5770/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableBatchNestedLoopJoin.java#L147
in this query, the cheapest plan is
`
Plan after physical tweaks: EnumerableCalc(expr#0..3=[{inputs}],
empno=[$t2], ename=[$t3], store_name=[$t0], store_id=[$t1]): rowcount = 312.5,
cumulative cost = {1257.5 rows, 3510.5 cpu, 0.0 io}, id = 278
EnumerableBatchNestedLoopJoin(condition=[<($1, $2)], joinType=[left],
batchSize=[100]): rowcount = 312.5, cumulative cost = {945.0 rows, 1010.5 cpu,
0.0 io}, id = 274
JdbcToEnumerableConverter: rowcount = 50.0, cumulative cost = {195.0
rows, 286.0 cpu, 0.0 io}, id = 265
JdbcProject(store_name=[$3], store_id=[$0]): rowcount = 50.0,
cumulative cost = {190.0 rows, 281.0 cpu, 0.0 io}, id = 263
JdbcFilter(condition=[<($0, 2)]): rowcount = 50.0, cumulative cost =
{150.0 rows, 201.0 cpu, 0.0 io}, id = 261
JdbcTableScan(table=[[foodmart, store]]): rowcount = 100.0,
cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 1
JdbcToEnumerableConverter: rowcount = 12.5, cumulative cost = {193.75
rows, 362.25 cpu, 0.0 io}, id = 272
JdbcFilter(condition=[AND(<=($0, ?0), OR(<($cor100.store_id, $0),
<($cor101.store_id, $0), <($cor102.store_id, $0), <($cor103.store_id, $0),
<($cor104.store_id, $0), <($cor105.store_id, $0), <($cor106.store_id, $0),
<($cor107.store_id, $0), <($cor108.store_id, $0), <($cor109.store_id, $0),
<($cor110.store_id, $0), <($cor111.store_id, $0), <($cor112.store_id, $0),
<($cor113.store_id, $0), <($cor114.store_id, $0), <($cor115.store_id, $0),
<($cor116.store_id, $0), <($cor117.store_id, $0), <($cor118.store_id, $0),
<($cor119.store_id, $0), <($cor120.store_id, $0), <($cor121.store_id, $0),
<($cor122.store_id, $0), <($cor123.store_id, $0), <($cor124.store_id, $0),
<($cor125.store_id, $0), <($cor126.store_id, $0), <($cor127.store_id, $0),
<($cor128.store_id, $0), <($cor129.store_id, $0), <($cor130.store_id, $0),
<($cor131.store_id, $0), <($cor132.store_id, $0), <($cor133.store_id, $0),
<($cor134.store_id, $0), <($cor135.store_id, $0), <($cor136.store_id, $0),
<($cor137.store_id, $0)
, <($cor138.store_id, $0), <($cor139.store_id, $0), <($cor140.store_id, $0),
<($cor141.store_id, $0), <($cor142.store_id, $0), <($cor143.store_id, $0),
<($cor144.store_id, $0), <($cor145.store_id, $0), <($cor146.store_id, $0),
<($cor147.store_id, $0), <($cor148.store_id, $0), <($cor149.store_id, $0),
<($cor150.store_id, $0), <($cor151.store_id, $0), <($cor152.store_id, $0),
<($cor153.store_id, $0), <($cor154.store_id, $0), <($cor155.store_id, $0),
<($cor156.store_id, $0), <($cor157.store_id, $0), <($cor158.store_id, $0),
<($cor159.store_id, $0), <($cor160.store_id, $0), <($cor161.store_id, $0),
<($cor162.store_id, $0), <($cor163.store_id, $0), <($cor164.store_id, $0),
<($cor165.store_id, $0), <($cor166.store_id, $0), <($cor167.store_id, $0),
<($cor168.store_id, $0), <($cor169.store_id, $0), <($cor170.store_id, $0),
<($cor171.store_id, $0), <($cor172.store_id, $0), <($cor173.store_id, $0),
<($cor174.store_id, $0), <($cor175.store_id, $0), <($cor176.store_id, $0),
<($cor177.store_id,
$0), <($cor178.store_id, $0), <($cor179.store_id, $0), <($cor180.store_id,
$0), <($cor181.store_id, $0), <($cor182.store_id, $0), <($cor183.store_id, $0),
<($cor184.store_id, $0), <($cor185.store_id, $0), <($cor186.store_id, $0),
<($cor187.store_id, $0), <($cor188.store_id, $0), <($cor189.store_id, $0),
<($cor190.store_id, $0), <($cor191.store_id, $0), <($cor192.store_id, $0),
<($cor193.store_id, $0), <($cor194.store_id, $0), <($cor195.store_id, $0),
<($cor196.store_id, $0), <($cor197.store_id, $0), <($cor198.store_id, $0),
<($cor199.store_id, $0)))]): rowcount = 12.5, cumulative cost = {192.5 rows,
361.0 cpu, 0.0 io}, id = 270
JdbcProject(EMPNO=[$0], ENAME=[$1]): rowcount = 100.0, cumulative
cost = {180.0 rows, 261.0 cpu, 0.0 io}, id = 268
JdbcTableScan(table=[[SCOTT, EMP]]): rowcount = 100.0, cumulative
cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 5
`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
