[
https://issues.apache.org/jira/browse/CALCITE-7207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhuo Cao updated CALCITE-7207:
------------------------------
Description:
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}
{code:java}
{code: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`)
{code} {code}
{code:java}
{code:sql}
– 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`)
{code} {code}
2. 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}
was:
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}
{code:java}
{code: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`)
{code}{code}
{code:java}
– 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`)
{code}
2. 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}
> 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
> Priority: Minor
>
>
> 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}
>
> {code:java}
> {code: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`)
> {code} {code}
> {code:java}
>
> {code:sql}
> – 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`)
> {code} {code}
>
>
> 2. 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)
