[jira] [Commented] (CALCITE-2978) sorting not applied in subqueries
[ https://issues.apache.org/jira/browse/CALCITE-2978?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16808847#comment-16808847 ] Julian Hyde commented on CALCITE-2978: -- Yes, this is intended behavior. Note that if the sub-query comes from expanding a view with an ORDER BY clause we will try to respect that. > sorting not applied in subqueries > - > > Key: CALCITE-2978 > URL: https://issues.apache.org/jira/browse/CALCITE-2978 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.19.0 >Reporter: Pressenna >Assignee: Danny Chan >Priority: Major > > {code:sql} > CREATE TABLE test (id INT, val INT); > INSERT INTO test VALUES (1,1); > INSERT INTO test VALUES (2,2); > INSERT INTO test VALUES (3,3); > INSERT INTO test VALUES (4,4); > SELECT id FROM (SELECT id, val FROM test ORDER BY val DESC); > {code} > Looks like CALCITE-2798 removes the sorting in sub-queries too aggressively. > Update: > I might be wrong here and jumped the gun too early. > Looks like SQL does not dictate that the outer query has to retain any order > of the inner query. > The sort is applied if a {{LIMIT}} is specified in the inner query, to reduce > the inner result correctly. > Happy to close as invalid. -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Commented] (CALCITE-2978) sorting not applied in subqueries
[ https://issues.apache.org/jira/browse/CALCITE-2978?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16884979#comment-16884979 ] Pressenna commented on CALCITE-2978: So I think I finally found a valid use case for this: {code:java} CREATE TABLE lookup (id INT, name TEXT, rank INT); INSERT INTO lookup VALUES (1, 'GOOD', 1); INSERT INTO lookup VALUES (2, 'GOOD', 3); INSERT INTO lookup VALUES (2, 'GOOD', 2); CREATE TABLE facts (id INT, name TEXT); INSERT INTO facts VALUES (1, 'GOOD'); INSERT INTO facts VALUES (2, 'BAD'); SELECT id, (SELECT lookup.id FROM lookup WHERE lookup.name=facts.name) FROM facts; -- In this cases, the order must be preserved. SELECT id, (SELECT lookup.id FROM lookup WHERE lookup.name=facts.name ORDER BY lookup.rank ASC) FROM facts; -- In this cases, the order must be preserved. SELECT id, (SELECT lookup.id FROM lookup WHERE lookup.name=facts.name ORDER BY lookup.rank DESC) FROM facts; {code} > sorting not applied in subqueries > - > > Key: CALCITE-2978 > URL: https://issues.apache.org/jira/browse/CALCITE-2978 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.19.0 >Reporter: Pressenna >Assignee: Danny Chan >Priority: Major > > {code:sql} > CREATE TABLE test (id INT, val INT); > INSERT INTO test VALUES (1,1); > INSERT INTO test VALUES (2,2); > INSERT INTO test VALUES (3,3); > INSERT INTO test VALUES (4,4); > SELECT id FROM (SELECT id, val FROM test ORDER BY val DESC); > {code} > Looks like CALCITE-2798 removes the sorting in sub-queries too aggressively. > Update: > I might be wrong here and jumped the gun too early. > Looks like SQL does not dictate that the outer query has to retain any order > of the inner query. > The sort is applied if a {{LIMIT}} is specified in the inner query, to reduce > the inner result correctly. > Happy to close as invalid. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (CALCITE-2978) sorting not applied in subqueries
[ https://issues.apache.org/jira/browse/CALCITE-2978?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16884987#comment-16884987 ] Haisheng Yuan commented on CALCITE-2978: This is still not a valid case, unless you add {{limit 1}}. > sorting not applied in subqueries > - > > Key: CALCITE-2978 > URL: https://issues.apache.org/jira/browse/CALCITE-2978 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.19.0 >Reporter: Pressenna >Assignee: Danny Chan >Priority: Major > > {code:sql} > CREATE TABLE test (id INT, val INT); > INSERT INTO test VALUES (1,1); > INSERT INTO test VALUES (2,2); > INSERT INTO test VALUES (3,3); > INSERT INTO test VALUES (4,4); > SELECT id FROM (SELECT id, val FROM test ORDER BY val DESC); > {code} > Looks like CALCITE-2798 removes the sorting in sub-queries too aggressively. > Update: > I might be wrong here and jumped the gun too early. > Looks like SQL does not dictate that the outer query has to retain any order > of the inner query. > The sort is applied if a {{LIMIT}} is specified in the inner query, to reduce > the inner result correctly. > Happy to close as invalid. -- This message was sent by Atlassian JIRA (v7.6.14#76016)
[jira] [Commented] (CALCITE-2978) sorting not applied in subqueries
[ https://issues.apache.org/jira/browse/CALCITE-2978?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16884999#comment-16884999 ] Pressenna commented on CALCITE-2978: [~hyuan], yes, you are right, sorry for reopening. > sorting not applied in subqueries > - > > Key: CALCITE-2978 > URL: https://issues.apache.org/jira/browse/CALCITE-2978 > Project: Calcite > Issue Type: Bug >Affects Versions: 1.19.0 >Reporter: Pressenna >Assignee: Danny Chan >Priority: Major > > {code:sql} > CREATE TABLE test (id INT, val INT); > INSERT INTO test VALUES (1,1); > INSERT INTO test VALUES (2,2); > INSERT INTO test VALUES (3,3); > INSERT INTO test VALUES (4,4); > SELECT id FROM (SELECT id, val FROM test ORDER BY val DESC); > {code} > Looks like CALCITE-2798 removes the sorting in sub-queries too aggressively. > Update: > I might be wrong here and jumped the gun too early. > Looks like SQL does not dictate that the outer query has to retain any order > of the inner query. > The sort is applied if a {{LIMIT}} is specified in the inner query, to reduce > the inner result correctly. > Happy to close as invalid. -- This message was sent by Atlassian JIRA (v7.6.14#76016)