Zhuo Cao created CALCITE-7207:
---------------------------------
Summary: 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
1. build a Semi join RelNode
```java
builder.join(JoinRelType.SEMI, condition)
.build();
```
```
// 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]])
```
2. There is an alias problem in issue generated MySQL sql. The 'AS `t0`' alias
operation should be removed.
```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);
```
```sql
-- the converted mysql sql
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`)
-- should remove 'AS `t0`' alias operation
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`)
```
2. An alternative solution is to use equivalent INNER JOIN + PROJECT + DISTINCT
operations instead.
```java
builder.join(JoinRelType.INNER, condition)
.project(lFieldNames.stream().map(f->builder.field(f)).toList())
.distinct()
.build();
```
```
// 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]])
```
```sql
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`
```
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
