[jira] [Commented] (CALCITE-3738) Missing LogicalSort for INSERT statement
[
https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17028624#comment-17028624
]
pengzhiwei commented on CALCITE-3738:
-
Thank [~hyuan] for your help on this issue.
> Missing LogicalSort for INSERT statement
>
>
> Key: CALCITE-3738
> URL: https://issues.apache.org/jira/browse/CALCITE-3738
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.19.0
>Reporter: pengzhiwei
>Assignee: pengzhiwei
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.22.0
>
> Time Spent: 2h 10m
> Remaining Estimate: 0h
>
> The following sql statement generates a wrong logical plan which missing the
> LogicalSort :
> {code:java}
> insert into t select id*10,name from users order by name;{code}
> the plan is :
> {code:java}
> LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false])
> LogicalProject(id=[*($0, 10)], name=[$1])
> LogicalTableScan(table=[[users]])
> {code}
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> which removes the sort by in sub-query.However the query in insert statement
> is not a sub-query.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3738) Missing LogicalSort for INSERT statement
[
https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17025345#comment-17025345
]
Haisheng Yuan commented on CALCITE-3738:
Thanks for reminding, I have updated both JIRA and commit message.
> Missing LogicalSort for INSERT statement
>
>
> Key: CALCITE-3738
> URL: https://issues.apache.org/jira/browse/CALCITE-3738
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.19.0
>Reporter: pengzhiwei
>Assignee: pengzhiwei
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.22.0
>
> Time Spent: 2h 10m
> Remaining Estimate: 0h
>
> The following sql statement generates a wrong logical plan which missing the
> LogicalSort :
> {code:java}
> insert into t select id*10,name from users order by name;{code}
> the plan is :
> {code:java}
> LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false])
> LogicalProject(id=[*($0, 10)], name=[$1])
> LogicalTableScan(table=[[users]])
> {code}
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> which removes the sort by in sub-query.However the query in insert statement
> is not a sub-query.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3738) Missing LogicalSort for Insert statement
[
https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17025342#comment-17025342
]
Julian Hyde commented on CALCITE-3738:
--
To repeat:
bq. By the way, please capitalize SQL keywords in JIRA case summaries. It makes
it much clearer.
I see "Insert" in the JIRA summary, and "insert" in the commit message. I would
like "INSERT".
> Missing LogicalSort for Insert statement
>
>
> Key: CALCITE-3738
> URL: https://issues.apache.org/jira/browse/CALCITE-3738
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.19.0
>Reporter: pengzhiwei
>Assignee: pengzhiwei
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.22.0
>
> Time Spent: 2h 10m
> Remaining Estimate: 0h
>
> The following sql statement generates a wrong logical plan which missing the
> LogicalSort :
> {code:java}
> insert into t select id*10,name from users order by name;{code}
> the plan is :
> {code:java}
> LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false])
> LogicalProject(id=[*($0, 10)], name=[$1])
> LogicalTableScan(table=[[users]])
> {code}
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> which removes the sort by in sub-query.However the query in insert statement
> is not a sub-query.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3738) Missing LogicalSort for Insert statement
[
https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17017399#comment-17017399
]
Haisheng Yuan commented on CALCITE-3738:
Now I think it may not harm to keep ordering. [~julianhyde] What do you think?
Can we keep the order?
> Missing LogicalSort for Insert statement
>
>
> Key: CALCITE-3738
> URL: https://issues.apache.org/jira/browse/CALCITE-3738
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.19.0
>Reporter: pengzhiwei
>Assignee: pengzhiwei
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1.5h
> Remaining Estimate: 0h
>
> The following sql statement generates a wrong logical plan which missing the
> LogicalSort :
> {code:java}
> insert into t select id*10,name from users order by name;{code}
> the plan is :
> {code:java}
> LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false])
> LogicalProject(id=[*($0, 10)], name=[$1])
> LogicalTableScan(table=[[users]])
> {code}
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> which removes the sort by in sub-query.However the query in insert statement
> is not a sub-query.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3738) Missing LogicalSort for Insert statement
[
https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17017362#comment-17017362
]
Rui Wang commented on CALCITE-3738:
---
Move some discussions from PR to this thread:
I understand [~pzw2018]'s idea. Basically in some engine implementations (e.g.
big data related). It could insert into message queue or log file, in such that
an order could be preserved. Later when the generated data is consumed somehow
(e.g. someone writes a script to process log files), the ordering could be a
property to be used for optimization (e.g. don't sort again).
[~hyuan] suggests the consumer should sort it rather than assuming INSERT INTO
ORDER BY will perverse order. I also think it makes sense as it is what
relational algebra says about a table.
So I don't have a strong opinion to keep the LogicalSort now. If we keep it, it
could be useful in practice. But if we don't keep it, nothing is wrong.
> Missing LogicalSort for Insert statement
>
>
> Key: CALCITE-3738
> URL: https://issues.apache.org/jira/browse/CALCITE-3738
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.19.0
>Reporter: pengzhiwei
>Assignee: pengzhiwei
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1.5h
> Remaining Estimate: 0h
>
> The following sql statement generates a wrong logical plan which missing the
> LogicalSort :
> {code:java}
> insert into t select id*10,name from users order by name;{code}
> the plan is :
> {code:java}
> LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false])
> LogicalProject(id=[*($0, 10)], name=[$1])
> LogicalTableScan(table=[[users]])
> {code}
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> which removes the sort by in sub-query.However the query in insert statement
> is not a sub-query.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3738) Missing LogicalSort for insert statement
[
https://issues.apache.org/jira/browse/CALCITE-3738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17016606#comment-17016606
]
pengzhiwei commented on CALCITE-3738:
-
I think the SQL is not fully equivalent to the generated RelNode if we
remove the LogicalSort. Maybe in some usage scenario, the target table cannot
guarantee the order of the input rows. But in some case, the target table
really need the ordering of the input rows.
For example，I want to insert an ordered rows to a message queue which can
guarantee the order of the input rows. With this default optimization,I cannot
do this.
So can we make this optimization as a RelOptRule, the physics engine can
decide use or not use this rule according to the actual situation.
> Missing LogicalSort for insert statement
>
>
> Key: CALCITE-3738
> URL: https://issues.apache.org/jira/browse/CALCITE-3738
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.19.0
>Reporter: pengzhiwei
>Assignee: pengzhiwei
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1.5h
> Remaining Estimate: 0h
>
> The following sql statement generates a wrong logical plan which missing the
> LogicalSort :
> {code:java}
> insert into t select id*10,name from users order by name;{code}
> the plan is :
> {code:java}
> LogicalTableModify(table=[[t]], operation=[INSERT], flattened=[false])
> LogicalProject(id=[*($0, 10)], name=[$1])
> LogicalTableScan(table=[[users]])
> {code}
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> which removes the sort by in sub-query.However the query in insert statement
> is not a sub-query.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
