[jira] [Comment Edited] (CALCITE-3874) SqlImplementor builder uses wrong context for sub-selects

["Justin Swett (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3874?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17073286#comment-17073286 ] 

Justin Swett edited comment on CALCITE-3874 at 4/2/20, 1:17 AM:
----------------------------------------------------------------

I missed your request... I'll try to look at 3811 this week and report back.


was (Author: jswett):
I missed your request... I'll try to look at this week and report back.

> SqlImplementor builder uses wrong context for sub-selects
> ---------------------------------------------------------
>
>                 Key: CALCITE-3874
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3874
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.22.0
>         Environment: Running against MySQL.
> This situation arrises specifically from running *AggregateProjectMergeRule* on the following rel:
> {code:java}
> LogicalSort(sort0=[$0], dir0=[ASC-nulls-first], fetch=[10])
>   LogicalProject(users.id=[$0], orders.count=[$1])
>     LogicalFilter(condition=[AND(templatedOpTo_BOOLEAN_2a703bc54fc6($2), lookerFirst(asIs(_UTF-8'(COUNT(*)) = 11 OR (COUNT(*)) = 1'), $0, $1, $2))])
>       LogicalAggregate(group=[{0}], orders.count=[COUNT()], orders.count=[COUNT()])
>            "users.id" -> {BasicSqlType@} "DECIMAL(19, 0)"
>            "orders.count" -> {BasicSqlType@} "BIGINT"
>            "orders.count_0" -> {BasicSqlType@} "BIGINT"
>         LogicalProject(users.id=[$5])
>           LogicalJoin(condition=[=($2, $5)], joinType=[left])
>             ExplicitlyAliasedTableScan(table=[[looker, orders]])
>             ExplicitlyAliasedTableScan(table=[[looker, users]])
> {code}
> producing the rel
> {code:java}
> LogicalSort(sort0=[$0], dir0=[ASC-nulls-first], fetch=[10])
>   LogicalProject(users.id=[$0], orders.count=[$1])
>     LogicalFilter(condition=[AND(templatedOpTo_BOOLEAN_2a703bc54fc6($2), lookerFirst(asIs(_UTF-8'(COUNT(*)) = 11 OR (COUNT(*)) = 1'), $0, $1, $2))])
>       LogicalAggregate(group=[{5}], orders.count=[COUNT()], orders.count=[COUNT()])
>            "id0" -> {BasicSqlType@ } "DECIMAL(19, 0)"
>            "orders.count" -> {BasicSqlType@ } "BIGINT"
>            "orders.count_0" -> {BasicSqlType@ } "BIGINT"
>         LogicalJoin(condition=[=($2, $5)], joinType=[left])
>           ExplicitlyAliasedTableScan(table=[[looker, orders]])
>           ExplicitlyAliasedTableScan(table=[[looker, users]])
> {code}
>            Reporter: Justin Swett
>            Assignee: Julian Hyde
>            Priority: Minor
>
> When a sub-query is detected, via RelToSqlConverter visit on Project and in turn SqlImplementor's builder method, the new aliases used for the new context are built from the project's input RowType. This can lead to incorrect generated SQL. Consider the following rel:   
> {code:java}
> -- Including the rowType below each rel  
>   LogicalProject(users.id=[$0], orders.count=[$1])
>       "users.id" -> {BasicSqlType@} "DECIMAL(19, 0)"
>       "orders.count" -> {BasicSqlType@} "BIGINT"
>     LogicalFilter(condition=[AND(templatedOpTo_BOOLEAN_2a703bc54fc6($2), lookerFirst(asIs(_UTF-8'(COUNT(*)) = 11 OR (COUNT(*)) = 1'), $0, $1, $2))])
>         "users.id" -> {BasicSqlType@} "DECIMAL(19, 0)"
>         "orders.count" -> {BasicSqlType@} "BIGINT"
>         "orders.count_0" -> {BasicSqlType@} "BIGINT"
>       LogicalAggregate(group=[{5}], orders.count=[COUNT()], orders.count=[COUNT()])         
>             "id0" -> {BasicSqlType@} "DECIMAL(19, 0)" 
>             "orders.count" -> {BasicSqlType@} "BIGINT"
>             "orders.count_0" -> {BasicSqlType@} "BIGINT"
>         LogicalJoin(condition=[=($2, $5)], joinType=[left])
>                 "id" -> {BasicSqlType@} "DECIMAL(19, 0)"
>                 "status" -> {BasicSqlType@} "VARCHAR"
>                 "user_id" -> {BasicSqlType@} "DECIMAL(19, 0)"
>                 "order_amount" -> {BasicSqlType@} "DOUBLE"
>                 "created_at" -> {BasicSqlType@21558} "TIMESTAMP(0)"
>                 "id0" -> {BasicSqlType@} "DECIMAL(19, 0)"
>                 "name" -> {BasicSqlType@} "VARCHAR"
>                 "age" -> {BasicSqlType@} "DECIMAL(19, 0)"
>                 "created_at0" -> {BasicSqlType@21558} "TIMESTAMP(0)"
>           ExplicitlyAliasedTableScan(table=[[db, orders]])
>           ExplicitlyAliasedTableScan(table=[[db, users]])
> {code}
>  
> The generated SQL from this rel is:
> {code:java}
> SELECT
>     `t0`.`users.id`,
>     `t0`.`orders.count`
> FROM (SELECT
>             `users`.`id` AS `id0`,  -- this is coming from Agg 
>             COUNT(*) AS `orders.count`,
>             COUNT(*) AS `orders.count_0`
>         FROM `orders` AS `orders` LEFT JOIN `users` AS `users` ON `orders`.`user_id` = `users`.`id`
>         GROUP BY
>             `users`.`id`
>         HAVING ((COUNT(*)  = 11)) AND ((COUNT(*)) = 11 OR (COUNT(*)) = 1)) AS `t0`
> ORDER BY
>     `t0`.`users.id`
> {code}
> Expected SQL:
> {code:java}
> SELECT
>     `t1`.`users.id`,
>     `t1`.`orders.count`
> FROM (SELECT
>             `users`.`id` AS `users.id`,  <-- aliased correctly
>             COUNT(*) AS `orders.count`,
>             COUNT(*) AS `orders.count_0`
>         FROM `orders` AS `orders` LEFT JOIN `users` AS `users` ON `orders`.`user_id` = `users`.`id`
>         GROUP BY
>             1
>         HAVING ((COUNT(*)  = 11)) AND ((COUNT(*)) = 11 OR (COUNT(*)) = 1)) AS `t1`
> ORDER BY
>     `t1`.`users.id`
> {code}
> I've traced this to [SqlImplementor |https://github.com/apache/calcite/blob/3c9e156aea4a246318e1fa9ea299adfc9479e20e/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1380]



--
This message was sent by Atlassian Jira
(v8.3.4#803005)