Jie Yang created CALCITE-6884:
---------------------------------
Summary: RelToSqlConverter generates invalid MySQL syntax for
Correlate operator with multiple tables
Key: CALCITE-6884
URL: https://issues.apache.org/jira/browse/CALCITE-6884
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.38.0
Reporter: Jie Yang
The `RelToSqlConverter` class has a bug when converting a `Correlate` operator
to SQL for MySQL dialect. When the left input of the correlate contains
multiple tables (like a join), it generates invalid MySQL syntax by placing
parentheses around the tables and applying an alias to the entire expression.
## Steps to Reproduce
1. Using the TPC-H schema
2. Starting with this query:
SELECT SUM(l_extendedprice) / 7.0 AS avg_yearly
FROM lineitem, part
WHERE p_partkey = l_partkey
AND p_brand = 'Brand#23'
AND p_container = 'MED BOX'
AND l_quantity < (SELECT 0.2 * AVG(l_quantity)
FROM lineitem
WHERE l_partkey = p_partkey);
3. Apply the "{{{}FILTER_SUB_QUERY_TO_CORRELATE"{}}} rule
4. Convert the resulting RelNode to SQL using the MySQL dialect
## Expected Behavior
The generated SQL should use syntax that MySQL supports for lateral joins, for
example:
SELECT SUM(`$cor0`.`l_extendedprice`) / 7.0 AS `avg_yearly`
FROM `lineitem`, `part`,
LATERAL (SELECT 0.2*AVG(`l_quantity`) AS `EXPR$0`
FROM `lineitem`
WHERE `l_partkey`=`p_partkey`) AS `t2`
WHERE `p_partkey`=`l_partkey`
AND `p_brand`='Brand#23'
AND `p_container`='MED BOX'
AND `l_quantity`<`t2`.`EXPR$0`;
## Actual Behavior
The converter generates this invalid SQL:
SELECT SUM(`$cor0`.`l_extendedprice`) / 7.0 AS `avg_yearly`
FROM (`lineitem`, `part`) AS `$cor0`,
LATERAL (SELECT 0.2*AVG(`l_quantity`) AS `EXPR$0`
FROM `lineitem`
WHERE `l_partkey`=`$cor0`.`p_partkey`) AS `t2`
WHERE `$cor0`.`p_partkey`=`$cor0`.`l_partkey`
AND `$cor0`.`p_brand`='Brand#23'
AND `$cor0`.`p_container`='MED BOX'
AND `$cor0`.`l_quantity`<`t2`.`EXPR$0`;
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
