[
https://issues.apache.org/jira/browse/CALCITE-7207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhen Chen resolved CALCITE-7207.
--------------------------------
Fix Version/s: 1.42.0
Resolution: Fixed
Fixed in
[{{f2df771}}|https://github.com/apache/calcite/commit/f2df771d86f52e15fba8f626eaeb8b0b9b944823]
Thanks for review [~mbudiu]
[~snow] Feel free to bring up any other questions later, and I'll follow up.
> Semi Join RelNode cannot be translated into correct MySQL SQL
> -------------------------------------------------------------
>
> Key: CALCITE-7207
> URL: https://issues.apache.org/jira/browse/CALCITE-7207
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.40.0
> Reporter: Zhuo Cao
> Assignee: Zhen Chen
> Priority: Minor
> Labels: pull-request-available
> Fix For: 1.42.0
>
>
>
> 1. build a Semi join RelNode
> {code:java}
> builder.join(JoinRelType.SEMI, condition)
> .build();{code}
> {code:java}
> // relational algebra semi join
> LogicalJoin(condition=[AND(=($0, $2), <($1, $3))], joinType=[semi])
> LogicalProject(car_id=[$0], speed=[$2])
> LogicalJoin(condition=[=($1, $0)], joinType=[inner])
> LogicalProject(car_id=[$0])
> LogicalTableScan(table=[[shenzhen, Car]])
> LogicalTableScan(table=[[shenzhen, Car]])
> LogicalProject(car_id=[$0], direction=[$3])
> LogicalJoin(condition=[=($1, $0)], joinType=[inner])
> LogicalProject(car_id=[$0])
> LogicalTableScan(table=[[shenzhen, Car]])
> LogicalTableScan(table=[[shenzhen, Car]]){code}
> 2. There is an alias problem in issue generated MySQL sql. The 'AS `t0`'
> alias operation should be removed.
> {code:java}
> // use mysql dialect
> SqlDialect mysqlDialect = MysqlSqlDialect.DEFAULT;
> RelToSqlConverter mysqlConverter = new RelToSqlConverter(mysqlDialect);
> SqlNode sqlNode = mysqlConverter.visitRoot(inv.getRelNode()).asStatement();
> String sql = sqlNode.toSqlString(mysqlDialect).getSql();
> System.out.println(sql);{code}
> The generated incorrect MySQL sql .
> {code:java}
> SELECT `t`.`car_id`, `Car0`.`speed`
> FROM ((SELECT `car_id`
> FROM `shenzhen`.`Car`) AS `t` INNER JOIN `shenzhen`.`Car` AS `Car0` ON
> `t`.`car_id` = `Car0`.`car_id`) AS `t0`
> WHERE EXISTS (SELECT 1
> FROM (SELECT `t1`.`car_id`, `Car2`.`direction`
> FROM (SELECT `car_id`
> FROM `shenzhen`.`Car`) AS `t1`
> INNER JOIN `shenzhen`.`Car` AS `Car2` ON `t1`.`car_id` = `Car2`.`car_id`) AS
> `t2`
> WHERE `t`.`car_id` = `t2`.`car_id` AND `Car0`.`speed` <
> `t2`.`direction`){code}
> The "AS `t0`" alias operation should be removed.
> {code:java}
> SELECT `t`.`car_id`, `Car0`.`speed`
> FROM (SELECT `car_id`
> FROM `shenzhen`.`Car`) AS `t` INNER JOIN `shenzhen`.`Car` AS `Car0` ON
> `t`.`car_id` = `Car0`.`car_id`
> WHERE EXISTS (SELECT 1
> FROM (SELECT `t1`.`car_id`, `Car2`.`direction`
> FROM (SELECT `car_id`
> FROM `shenzhen`.`Car`) AS `t1`
> INNER JOIN `shenzhen`.`Car` AS `Car2` ON `t1`.`car_id` = `Car2`.`car_id`) AS
> `t2`
> WHERE `t`.`car_id` = `t2`.`car_id` AND `Car0`.`speed` <
> `t2`.`direction`){code}
> 3. An alternative solution is to use equivalent INNER JOIN + PROJECT +
> DISTINCT operations instead.
> {code:java}
> builder.join(JoinRelType.INNER, condition)
> .project(lFieldNames.stream().map(f->builder.field(f)).toList())
> .distinct()
> .build();{code}
> {code:java}
> // Use equivalent INNER JOIN + PROJECT + DISTINCT operations instead.
> LogicalAggregate(group=[\{0, 1}])
> LogicalProject(car_id=[$0], speed=[$1])
> LogicalJoin(condition=[AND(=($0, $2), <($1, $3))], joinType=[inner])
> LogicalProject(car_id=[$0], speed=[$2])
> LogicalJoin(condition=[=($1, $0)], joinType=[inner])
> LogicalProject(car_id=[$0])
> LogicalTableScan(table=[[shenzhen0, Car]])
> LogicalTableScan(table=[[shenzhen0, Car]])
> LogicalProject(car_id=[$0], direction=[$3])
> LogicalJoin(condition=[=($1, $0)], joinType=[inner])
> LogicalProject(car_id=[$0])
> LogicalTableScan(table=[[shenzhen0, Car]])
> LogicalTableScan(table=[[shenzhen0, Car]]){code}
> {code:java}
> SELECT `t0`.`car_id`, `t0`.`speed`
> FROM (SELECT `t`.`car_id`, `Car0`.`speed`
> FROM (SELECT `car_id`
> FROM `shenzhen0`.`Car`) AS `t`
> INNER JOIN `shenzhen0`.`Car` AS `Car0` ON `t`.`car_id` = `Car0`.`car_id`) AS
> `t0`
> INNER JOIN (SELECT `t1`.`car_id`, `Car2`.`direction`
> FROM (SELECT `car_id`
> FROM `shenzhen0`.`Car`) AS `t1`
> INNER JOIN `shenzhen0`.`Car` AS `Car2` ON `t1`.`car_id` = `Car2`.`car_id`) AS
> `t2` ON `t0`.`car_id` = `t2`.`car_id` AND `t0`.`speed` <
> `t2`.`direction`{code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
