[jira] [Commented] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810736#comment-17810736
]
Ulrich Kramer commented on CALCITE-6221:
Yes, it's minimal. Everything works if
- second column is not calculated
- one of the joins is missing (even the latest which is not used)
- the filter condition is missing
> JDBC adapter generates invalid query when the same table is joined multiple
> times
> -
>
> Key: CALCITE-6221
> URL: https://issues.apache.org/jira/browse/CALCITE-6221
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
> Environment: Local development
>Reporter: Ulrich Kramer
>Priority: Major
>
> Adding the following unit test to {{JdbcAdapterTest}}
> {code:java}
> @Test void testUnknownColumn() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("SELECT\n" +
> "\"content-format-owner\",\n" +
> "\"content-owner\"\n" +
> "FROM\n" +
> "(\n" +
> "SELECT\n" +
> "d1.dname AS \"content-format-owner\",\n" +
> "d2.dname || ' ' AS \"content-owner\"\n" +
> "FROM\n" +
> "scott.emp e1\n" +
> "left outer join scott.dept d1 on e1.deptno =
> d1.deptno\n" +
> "left outer join scott.dept d2 on e1.deptno =
> d2.deptno\n" +
> "left outer join scott.emp e2 on e1.deptno =
> e2.deptno\n" +
> "GROUP BY\n" +
> "d1.dname,\n" +
> "d2.dname\n" +
> ")\n" +
> "WHERE\n" +
> "\"content-owner\" IN (?)")
> .runs();
> }
> {code}
> Fails because the following SQL is sent to the underlying database
> {code:SQL}
> SELECT
> "t2"."DNAME" AS "content-format-owner",
> "t2"."DNAME0" || ' ' AS "content-owner"
> FROM
> (
> SELECT
> *
> FROM
> (
> SELECT
> "DEPTNO"
> FROM
> "SCOTT"."EMP"
> ) AS "t"
> LEFT JOIN (
> SELECT
> "DEPTNO",
> "DNAME"
> FROM
> "SCOTT"."DEPT"
> ) AS "t0" ON "t"."DEPTNO" = "t0"."DEPTNO"
> LEFT JOIN (
> SELECT
> "DEPTNO",
> "DNAME"
> FROM
> "SCOTT"."DEPT"
> ) AS "t1" ON "t"."DEPTNO" = "t1"."DEPTNO"
> WHERE
> "t1"."DNAME" || ' ' = ?
> ) AS "t2"
> LEFT JOIN (
> SELECT
> "DEPTNO"
> FROM
> "SCOTT"."EMP"
> ) AS "t3" ON "t2"."DEPTNO" = "t3"."DEPTNO"
> GROUP BY
> "t2"."DNAME",
> "t2"."DNAME0"
> {code}
> The column {{"t2"."DNAME0"}} does not exist.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6063) If ARRAY subquery has ORDER BY (without LIMIT), rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ran Tao updated CALCITE-6063:
-
Summary: If ARRAY subquery has ORDER BY (without LIMIT), rows are not
sorted (was: If ARRAY subquery has ORDER BY(without LIMIT), rows are not
sorted)
> If ARRAY subquery has ORDER BY (without LIMIT), rows are not sorted
> ---
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6063) If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810684#comment-17810684
]
Ran Tao edited comment on CALCITE-6063 at 1/25/24 7:25 AM:
---
thanks for review. About MULTISET/MAP constructor by query:
1. MULTISET constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. MAP constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-6061
So now the remaining problem is that shall we throw parser exception directly
when using orderby in MAP subquery? so we can align it with MULTISET subquery.
Or skip OrderByRemovedInSubQuery to let MAP subquery return ordered rows. This
can align with ARRAY. (not guarantee the order of the final map)
Or not change anything temporarily.
BTW, I have updated the PR and added tests for MULTISET subqery and MAP
subqery, the code is more clear.
was (Author: lemonjing):
thanks for review. About MULTISET/MAP constructor by query:
1. MULTISET constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. MAP constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in MAP subquery? so we can align it with MULTISET subquery.
Or skip OrderByRemovedInSubQuery to let MAP subquery return ordered rows. This
can align with ARRAY. (not guarantee the order of the final map)
Or not change anything temporarily.
BTW, I have updated the PR and added tests for MULTISET subqery and MAP
subqery, the code is more clear.
> If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6063) If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810684#comment-17810684
]
Ran Tao edited comment on CALCITE-6063 at 1/25/24 7:21 AM:
---
thanks for review. About MULTISET/MAP constructor by query:
1. MULTISET constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. MAP constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in MAP subquery? so we can align it with MULTISET subquery.
Or skip OrderByRemovedInSubQuery to let MAP subquery return ordered rows. This
can align with ARRAY. (not guarantee the order of the final map)
Or not change anything temporarily.
BTW, I have updated the PR and added tests for MULTISET subqery and MAP
subqery, the code is more clear.
was (Author: lemonjing):
thanks for review. About MULTISET/MAP constructor by query:
1. MULTISET constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. MAP constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in MAP subquery? so we can align it with MULTISET subquery.
Or skip OrderByRemovedInSubQuery to let MAP subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temporarily.
BTW, I have updated the PR and added tests for MULTISET subqery and MAP
subqery, the code is more clear.
> If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6063) If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810684#comment-17810684
]
Ran Tao edited comment on CALCITE-6063 at 1/25/24 7:15 AM:
---
thanks for review. About MULTISET/MAP constructor by query:
1. MULTISET constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. MAP constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in MAP subquery? so we can align it with MULTISET subquery.
Or skip OrderByRemovedInSubQuery to let MAP subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temporarily.
BTW, I have updated the PR and added tests for MULTISET subqery and MAP
subqery, the code is more clear.
was (Author: lemonjing):
thanks for review. About MULTISET/MAP constructor by query:
1. MULTISET constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. MAP constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or skip OrderByRemovedInSubQuery to let map subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temporarily.
BTW, I have updated the PR and added tests for multiset subqery and map
subqery, the code is more clear.
> If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6063) If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810684#comment-17810684
]
Ran Tao edited comment on CALCITE-6063 at 1/25/24 7:14 AM:
---
thanks for review. About MULTISET/MAP constructor by query:
1. MULTISET constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. MAP constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or skip OrderByRemovedInSubQuery to let map subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temporarily.
BTW, I have updated the PR and added tests for multiset subqery and map
subqery, the code is more clear.
was (Author: lemonjing):
thanks for review. About MULTISET/MAP constructor by query:
1. MULTISET constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. MAP constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or skip OrderByRemovedInSubQuery to let map subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temporarily.
> If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6063) If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810684#comment-17810684
]
Ran Tao edited comment on CALCITE-6063 at 1/25/24 7:13 AM:
---
thanks for review. About MULTISET/MAP constructor by query:
1. MULTISET constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. MAP constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or skip OrderByRemovedInSubQuery to let map subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temporarily.
was (Author: lemonjing):
thanks for review. About MULTISET/MAP constructor by query:
1. MULTISET constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. MAP constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can also change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or skip OrderByRemovedInSubQuery to let map subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temporarily.
> If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6063) If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810684#comment-17810684
]
Ran Tao edited comment on CALCITE-6063 at 1/25/24 7:12 AM:
---
thanks for review. About MULTISET/MAP constructor by query:
1. MULTISET constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. MAP constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can also change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or skip OrderByRemovedInSubQuery to let map subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temporarily.
was (Author: lemonjing):
thanks for review. About MULTISET/MAP constructor by query:
1. multiset constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. map constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can also change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or skip OrderByRemovedInSubQuery to let map subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temporarily.
> If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6063) If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810684#comment-17810684
]
Ran Tao edited comment on CALCITE-6063 at 1/25/24 7:11 AM:
---
thanks for review. About MULTISET/MAP constructor by query:
1. multiset constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. map constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can also change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or skip OrderByRemovedInSubQuery to let map subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temporarily.
was (Author: lemonjing):
thanks for review.
1. multiset constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. map constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can also change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or skip OrderByRemovedInSubQuery to let map subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temporarily.
> If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6063) If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ran Tao updated CALCITE-6063:
-
Summary: If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
(was: If ARRAY subquery has ORDER BY, rows are not sorted)
> If ARRAY subquery has ORDER BY(without LIMIT), rows are not sorted
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6063) If ARRAY subquery has ORDER BY, rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810684#comment-17810684
]
Ran Tao edited comment on CALCITE-6063 at 1/25/24 6:25 AM:
---
thanks for review.
1. multiset constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. map constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can also change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or skip OrderByRemovedInSubQuery to let map subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temporarily.
was (Author: lemonjing):
thanks for review.
1. multiset constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. map constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can also change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or skip OrderByRemovedInSubQuery to let map subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temprorily.
> If ARRAY subquery has ORDER BY, rows are not sorted
> ---
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6063) If ARRAY subquery has ORDER BY, rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810684#comment-17810684
]
Ran Tao edited comment on CALCITE-6063 at 1/25/24 6:23 AM:
---
thanks for review.
1. multiset constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. map constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
we can also change this just like this PR to let subquery return ordered rows.
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or skip OrderByRemovedInSubQuery to let map subquery return ordered rows. (not
guarantee the order of the final map)
Or not change anything temprorily.
was (Author: lemonjing):
thanks for review.
1. multiset constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. map constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or not change it temprorily.
> If ARRAY subquery has ORDER BY, rows are not sorted
> ---
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6063) If ARRAY subquery has ORDER BY, rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810684#comment-17810684
]
Ran Tao edited comment on CALCITE-6063 at 1/25/24 6:18 AM:
---
thanks for review.
1. multiset constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will also throw exception in parser phase.
2. map constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or not change it temprorily.
was (Author: lemonjing):
thanks for review.
1. multiset constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will throw exception in parser phase.
2. map constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or not change it temprorily.
> If ARRAY subquery has ORDER BY, rows are not sorted
> ---
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6063) If ARRAY subquery has ORDER BY, rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810684#comment-17810684
]
Ran Tao edited comment on CALCITE-6063 at 1/25/24 6:10 AM:
---
thanks for review.
1. multiset constructor by query doesn't support order by and limit.
{noformat}
::=
ARRAY [ ]
::=
MULTISET
{noformat}
calcite will throw exception in parser phase.
2. map constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or not change it temprorily.
was (Author: lemonjing):
thanks for review.
1. multiset constructor by query doesn't support order by and limit.
::=
ARRAY [ ]
::=
MULTISET
calcite will throw exception in parser phase.
2. map constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or not change it temprorily.
> If ARRAY subquery has ORDER BY, rows are not sorted
> ---
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6063) If ARRAY subquery has ORDER BY, rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810684#comment-17810684
]
Ran Tao commented on CALCITE-6063:
--
thanks for review.
1. multiset constructor by query doesn't support order by and limit.
::=
ARRAY [ ]
::=
MULTISET
calcite will throw exception in parser phase.
2. map constructor by query is not standard, calcite allows map subquery with
order by now(will pass parser phase). it has 2 cases:
case-1: use ORDER BY without LIMIT
it has no sorting effect in runtime (hit OrderByRemovedInSubQuery rule, sort
will be removed)
case-2: use ORDER BY and LIMIT
the order by will take effect (sort will be reserved),
but we do not guarantee the order of the final map result.
Futhermore, from the semantics of map, sorting is not required. We had a lot of
discussion before about whether to return an ordered map.
I have updated the PR and added tests for multiset subqery and map subqery, the
code is more clear.
So now the remaining problem is that shall we throw parser exception directly
when using orderby in map subquery? so we can align it with multiset subquery.
Or not change it temprorily.
> If ARRAY subquery has ORDER BY, rows are not sorted
> ---
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810582#comment-17810582
]
Julian Hyde commented on CALCITE-6221:
--
Nice bug! Is it minimal? (E.g. does it reproduce with only one use of EMP, or
one use of DEPT, or without {{{}LEFT JOIN{}}}, or with simpler column aliases?)
> JDBC adapter generates invalid query when the same table is joined multiple
> times
> -
>
> Key: CALCITE-6221
> URL: https://issues.apache.org/jira/browse/CALCITE-6221
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
> Environment: Local development
>Reporter: Ulrich Kramer
>Priority: Major
>
> Adding the following unit test to {{JdbcAdapterTest}}
> {code:java}
> @Test void testUnknownColumn() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("SELECT\n" +
> "\"content-format-owner\",\n" +
> "\"content-owner\"\n" +
> "FROM\n" +
> "(\n" +
> "SELECT\n" +
> "d1.dname AS \"content-format-owner\",\n" +
> "d2.dname || ' ' AS \"content-owner\"\n" +
> "FROM\n" +
> "scott.emp e1\n" +
> "left outer join scott.dept d1 on e1.deptno =
> d1.deptno\n" +
> "left outer join scott.dept d2 on e1.deptno =
> d2.deptno\n" +
> "left outer join scott.emp e2 on e1.deptno =
> e2.deptno\n" +
> "GROUP BY\n" +
> "d1.dname,\n" +
> "d2.dname\n" +
> ")\n" +
> "WHERE\n" +
> "\"content-owner\" IN (?)")
> .runs();
> }
> {code}
> Fails because the following SQL is sent to the underlying database
> {code:SQL}
> SELECT
> "t2"."DNAME" AS "content-format-owner",
> "t2"."DNAME0" || ' ' AS "content-owner"
> FROM
> (
> SELECT
> *
> FROM
> (
> SELECT
> "DEPTNO"
> FROM
> "SCOTT"."EMP"
> ) AS "t"
> LEFT JOIN (
> SELECT
> "DEPTNO",
> "DNAME"
> FROM
> "SCOTT"."DEPT"
> ) AS "t0" ON "t"."DEPTNO" = "t0"."DEPTNO"
> LEFT JOIN (
> SELECT
> "DEPTNO",
> "DNAME"
> FROM
> "SCOTT"."DEPT"
> ) AS "t1" ON "t"."DEPTNO" = "t1"."DEPTNO"
> WHERE
> "t1"."DNAME" || ' ' = ?
> ) AS "t2"
> LEFT JOIN (
> SELECT
> "DEPTNO"
> FROM
> "SCOTT"."EMP"
> ) AS "t3" ON "t2"."DEPTNO" = "t3"."DEPTNO"
> GROUP BY
> "t2"."DNAME",
> "t2"."DNAME0"
> {code}
> The column {{"t2"."DNAME0"}} does not exist.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6221) JDBC adapter generates invalid query when the same table is joined multiple times
[
https://issues.apache.org/jira/browse/CALCITE-6221?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6221:
-
Summary: JDBC adapter generates invalid query when the same table is joined
multiple times (was: Invalid query generated when the same table is joined
multiple times)
> JDBC adapter generates invalid query when the same table is joined multiple
> times
> -
>
> Key: CALCITE-6221
> URL: https://issues.apache.org/jira/browse/CALCITE-6221
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
> Environment: Local development
>Reporter: Ulrich Kramer
>Priority: Major
>
> Adding the following unit test to {{JdbcAdapterTest}}
> {code:java}
> @Test void testUnknownColumn() {
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query("SELECT\n" +
> "\"content-format-owner\",\n" +
> "\"content-owner\"\n" +
> "FROM\n" +
> "(\n" +
> "SELECT\n" +
> "d1.dname AS \"content-format-owner\",\n" +
> "d2.dname || ' ' AS \"content-owner\"\n" +
> "FROM\n" +
> "scott.emp e1\n" +
> "left outer join scott.dept d1 on e1.deptno =
> d1.deptno\n" +
> "left outer join scott.dept d2 on e1.deptno =
> d2.deptno\n" +
> "left outer join scott.emp e2 on e1.deptno =
> e2.deptno\n" +
> "GROUP BY\n" +
> "d1.dname,\n" +
> "d2.dname\n" +
> ")\n" +
> "WHERE\n" +
> "\"content-owner\" IN (?)")
> .runs();
> }
> {code}
> Fails because the following SQL is sent to the underlying database
> {code:SQL}
> SELECT
> "t2"."DNAME" AS "content-format-owner",
> "t2"."DNAME0" || ' ' AS "content-owner"
> FROM
> (
> SELECT
> *
> FROM
> (
> SELECT
> "DEPTNO"
> FROM
> "SCOTT"."EMP"
> ) AS "t"
> LEFT JOIN (
> SELECT
> "DEPTNO",
> "DNAME"
> FROM
> "SCOTT"."DEPT"
> ) AS "t0" ON "t"."DEPTNO" = "t0"."DEPTNO"
> LEFT JOIN (
> SELECT
> "DEPTNO",
> "DNAME"
> FROM
> "SCOTT"."DEPT"
> ) AS "t1" ON "t"."DEPTNO" = "t1"."DEPTNO"
> WHERE
> "t1"."DNAME" || ' ' = ?
> ) AS "t2"
> LEFT JOIN (
> SELECT
> "DEPTNO"
> FROM
> "SCOTT"."EMP"
> ) AS "t3" ON "t2"."DEPTNO" = "t3"."DEPTNO"
> GROUP BY
> "t2"."DNAME",
> "t2"."DNAME0"
> {code}
> The column {{"t2"."DNAME0"}} does not exist.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6063) If ARRAY subquery has ORDER BY, rows are not sorted
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6063:
-
Summary: If ARRAY subquery has ORDER BY, rows are not sorted (was: ARRAY
subquery with OrderBy loses Sort)
> If ARRAY subquery has ORDER BY, rows are not sorted
> ---
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6063) ARRAY subquery with OrderBy loses Sort
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810581#comment-17810581
]
Julian Hyde commented on CALCITE-6063:
--
Thanks for the PR. I have reviewed the PR, requested minor changes. I have set
fixVersion to remind us to get this into 1.37.
In {{MULTISET ()}} and {{MAP ()}} does {{ORDER BY}}
remain illegal? If so could you add a validator test.
> ARRAY subquery with OrderBy loses Sort
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6063) ARRAY subquery with OrderBy loses Sort
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6063:
-
Fix Version/s: 1.37.0
> ARRAY subquery with OrderBy loses Sort
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6220) Rewrite MIN/MAX(BOOLEAN) as BOOL_AND/BOOL_OR for Postgres, Redshift
[ https://issues.apache.org/jira/browse/CALCITE-6220?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Tanner Clary updated CALCITE-6220: -- Summary: Rewrite MIN/MAX(BOOLEAN) as BOOL_AND/BOOL_OR for Postgres, Redshift (was: Rewrite MIN/MAX(bool) as BOOL_AND/BOOL_OR for Postgres, Redshift) > Rewrite MIN/MAX(BOOLEAN) as BOOL_AND/BOOL_OR for Postgres, Redshift > --- > > Key: CALCITE-6220 > URL: https://issues.apache.org/jira/browse/CALCITE-6220 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > Labels: pull-request-available > > Postgres and Redshift do not allow for MIN/MAX on columns of type BOOLEAN, > instead they use the BOOL_AND/BOOL_OR functions, which Calcite already > supports. > While CALCITE-6206 is in progress, I plan on just adding this as an override > during unparsing for now unless there are objections. After CALCITE-6206 is > resolved this could be added to the mapping I described there. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6222) Mysql does not have to_char function
[
https://issues.apache.org/jira/browse/CALCITE-6222?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6222:
-
Component/s: (was: site)
> Mysql does not have to_char function
>
>
> Key: CALCITE-6222
> URL: https://issues.apache.org/jira/browse/CALCITE-6222
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> {code:java}
> mysql> select to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS')
> -> ;
> ERROR 1305 (42000): FUNCTION test.to_char does not exist
> {code}
> mysql does not seem to have a to_char function
>
> link：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6222) Mysql does not have to_char function
[
https://issues.apache.org/jira/browse/CALCITE-6222?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810571#comment-17810571
]
Julian Hyde commented on CALCITE-6222:
--
This is not minor. This is a functionality change.
I've linked to CALCITE-5619, which introduced the issue. (And apparently did
not test the functionality introduced.)
> Mysql does not have to_char function
>
>
> Key: CALCITE-6222
> URL: https://issues.apache.org/jira/browse/CALCITE-6222
> Project: Calcite
> Issue Type: Bug
> Components: site
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> {code:java}
> mysql> select to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS')
> -> ;
> ERROR 1305 (42000): FUNCTION test.to_char does not exist
> {code}
> mysql does not seem to have a to_char function
>
> link：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6218) RelToSqlConverter fails to convert correlated lateral joins
[
https://issues.apache.org/jira/browse/CALCITE-6218?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810565#comment-17810565
]
Guillaume Massé edited comment on CALCITE-6218 at 1/24/24 9:04 PM:
---
I'm currently comparing coral execution vs calcite execution of
RelToSqlConverter. There is a small difference in the RelNode I get that does
not show when you use `RelOptUtil.dumpPlan`: when validating the node, the
(Calcite) frontend did not set the rowType property for Correlate, Uncollect
and it's child input LogicalProject.:
{code:java}
LogicalProject(a=[$0], x=[$6])
LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{4}]) // < rowType missing
LogicalProject(a=[$0], b=[$1.n11.b], c=[$1.n12.c], d=[$2.d], xs=[$3],
e=[$4])
LogicalTableScan(table=[[myDb, myTable]])
Uncollect // < rowType missing
LogicalProject(xs=[$cor0.xs]) // < rowType missing
LogicalValues(tuples=[[{ 0 }]])
{code}
I will dig deeper to see if there is an impact on the output SQL.
was (Author: masseguillaume):
I'm currently comparing coral execution vs calcite execution of
RelToSqlConverter. There is a small difference in the RelNode I get that does
not show when you use `RelOptUtil.dumpPlan`: when validating the node, the
frontend did not set the rowType property for Correlate, Uncollect and it's
child input LogicalProject.:
{code:java}
LogicalProject(a=[$0], x=[$6])
LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{4}]) // < rowType missing
LogicalProject(a=[$0], b=[$1.n11.b], c=[$1.n12.c], d=[$2.d], xs=[$3],
e=[$4])
LogicalTableScan(table=[[myDb, myTable]])
Uncollect // < rowType missing
LogicalProject(xs=[$cor0.xs]) // < rowType missing
LogicalValues(tuples=[[{ 0 }]])
{code}
I will dig deeper to see if there is an impact on the output SQL.
> RelToSqlConverter fails to convert correlated lateral joins
> ---
>
> Key: CALCITE-6218
> URL: https://issues.apache.org/jira/browse/CALCITE-6218
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Guillaume Massé
>Priority: Major
>
>
> input query:
> {code:sql}
> SELECT
> "a", "x"
> FROM
> "myDb"."myTable",
> unnest("xs") as "x"; {code}
> schema:
> [https://github.com/MasseGuillaume/calcite/blob/0126e6cfa47061886b2012ad2d2c32408455ae88/testkit/src/main/java/org/apache/calcite/test/CalciteAssert.java#L2180-L2211]
> {code:java}
> myTable(
> a: BIGINT,
> // ...
> xs: ARRAY,
> // ...
> )
> {code}
> logical plan:
> {code:java}
> LogicalProject(a=[$0], x=[$6])
> LogicalCorrelate(correlation=[$cor0], joinType=[inner],
> requiredColumns=[{4}])
> LogicalProject(a=[$0], b=[$1.n11.b], c=[$1.n12.c], d=[$2.d], xs=[$3],
> e=[$4])
> LogicalTableScan(table=[[myDb, myTable]])
> Uncollect
> LogicalProject(xs=[$cor0.xs])
> LogicalValues(tuples=[[{ 0 }]])
> {code}
> obtained sql:
>
> {code:sql}
> SELECT
> "$cor0"."a",
> "$cor0"."xs0" AS "x" -- <-- xs0 ?
> FROM
> (
> SELECT "a", "n1"."n11"."b", "n1"."n12"."c", "n2"."d", "xs", "e" FROM
> "myDb"."myTable"
> ) AS "$cor0",
> LATERAL UNNEST (
> SELECT "$cor0"."xs" FROM (VALUES (0)) AS "t" ("ZERO")
> ) AS "t1" ("xs") AS "t10"
> {code}
> I would expect the query to be converted to something close to the original
> query. Here "xs0" does not exists.
>
> [https://github.com/MasseGuillaume/calcite/commit/0126e6cfa47061886b2012ad2d2c32408455ae88]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6218) RelToSqlConverter fails to convert correlated lateral joins
[
https://issues.apache.org/jira/browse/CALCITE-6218?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810565#comment-17810565
]
Guillaume Massé commented on CALCITE-6218:
--
I'm currently comparing coral execution vs calcite execution of
RelToSqlConverter. There is a small difference in the RelNode I get that does
not show when you use `RelOptUtil.dumpPlan`: when validating the node, the
frontend did not set the rowType property for Correlate, Uncollect and it's
child input LogicalProject.:
{code:java}
LogicalProject(a=[$0], x=[$6])
LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{4}]) // < rowType missing
LogicalProject(a=[$0], b=[$1.n11.b], c=[$1.n12.c], d=[$2.d], xs=[$3],
e=[$4])
LogicalTableScan(table=[[myDb, myTable]])
Uncollect // < rowType missing
LogicalProject(xs=[$cor0.xs]) // < rowType missing
LogicalValues(tuples=[[{ 0 }]])
{code}
I will dig deeper to see if there is an impact on the output SQL.
> RelToSqlConverter fails to convert correlated lateral joins
> ---
>
> Key: CALCITE-6218
> URL: https://issues.apache.org/jira/browse/CALCITE-6218
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Guillaume Massé
>Priority: Major
>
>
> input query:
> {code:sql}
> SELECT
> "a", "x"
> FROM
> "myDb"."myTable",
> unnest("xs") as "x"; {code}
> schema:
> [https://github.com/MasseGuillaume/calcite/blob/0126e6cfa47061886b2012ad2d2c32408455ae88/testkit/src/main/java/org/apache/calcite/test/CalciteAssert.java#L2180-L2211]
> {code:java}
> myTable(
> a: BIGINT,
> // ...
> xs: ARRAY,
> // ...
> )
> {code}
> logical plan:
> {code:java}
> LogicalProject(a=[$0], x=[$6])
> LogicalCorrelate(correlation=[$cor0], joinType=[inner],
> requiredColumns=[{4}])
> LogicalProject(a=[$0], b=[$1.n11.b], c=[$1.n12.c], d=[$2.d], xs=[$3],
> e=[$4])
> LogicalTableScan(table=[[myDb, myTable]])
> Uncollect
> LogicalProject(xs=[$cor0.xs])
> LogicalValues(tuples=[[{ 0 }]])
> {code}
> obtained sql:
>
> {code:sql}
> SELECT
> "$cor0"."a",
> "$cor0"."xs0" AS "x" -- <-- xs0 ?
> FROM
> (
> SELECT "a", "n1"."n11"."b", "n1"."n12"."c", "n2"."d", "xs", "e" FROM
> "myDb"."myTable"
> ) AS "$cor0",
> LATERAL UNNEST (
> SELECT "$cor0"."xs" FROM (VALUES (0)) AS "t" ("ZERO")
> ) AS "t1" ("xs") AS "t10"
> {code}
> I would expect the query to be converted to something close to the original
> query. Here "xs0" does not exists.
>
> [https://github.com/MasseGuillaume/calcite/commit/0126e6cfa47061886b2012ad2d2c32408455ae88]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6218) RelToSqlConverter fails to convert correlated lateral joins
[
https://issues.apache.org/jira/browse/CALCITE-6218?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Guillaume Massé updated CALCITE-6218:
-
Description:
input query:
{code:sql}
SELECT
"a", "x"
FROM
"myDb"."myTable",
unnest("xs") as "x"; {code}
schema:
[https://github.com/MasseGuillaume/calcite/blob/0126e6cfa47061886b2012ad2d2c32408455ae88/testkit/src/main/java/org/apache/calcite/test/CalciteAssert.java#L2180-L2211]
{code:java}
myTable(
a: BIGINT,
// ...
xs: ARRAY,
// ...
)
{code}
logical plan:
{code:java}
LogicalProject(a=[$0], x=[$6])
LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{4}])
LogicalProject(a=[$0], b=[$1.n11.b], c=[$1.n12.c], d=[$2.d], xs=[$3],
e=[$4])
LogicalTableScan(table=[[myDb, myTable]])
Uncollect
LogicalProject(xs=[$cor0.xs])
LogicalValues(tuples=[[{ 0 }]])
{code}
obtained sql:
{code:sql}
SELECT
"$cor0"."a",
"$cor0"."xs0" AS "x" -- <-- xs0 ?
FROM
(
SELECT "a", "n1"."n11"."b", "n1"."n12"."c", "n2"."d", "xs", "e" FROM
"myDb"."myTable"
) AS "$cor0",
LATERAL UNNEST (
SELECT "$cor0"."xs" FROM (VALUES (0)) AS "t" ("ZERO")
) AS "t1" ("xs") AS "t10"
{code}
I would expect the query to be converted to something close to the original
query. Here "xs0" does not exists.
[https://github.com/MasseGuillaume/calcite/commit/0126e6cfa47061886b2012ad2d2c32408455ae88]
was:
input query:
{code:sql}
SELECT
"a", "x"
FROM
"myDb"."myTable",
unnest("xs") as "x"; {code}
schema:
[https://github.com/MasseGuillaume/calcite/blob/0126e6cfa47061886b2012ad2d2c32408455ae88/testkit/src/main/java/org/apache/calcite/test/CalciteAssert.java#L2180-L2211]
{code:java}
myTable(
a: BIGINT,
// ...
xs: ARRAY,
// ...
) {code}
logical plan:
{code:java}
LogicalProject(a=[$0], x=[$6])
LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{4}])
LogicalProject(a=[$0], b=[$1.n11.b], c=[$1.n12.c], d=[$2.d], xs=[$3],
e=[$4])
LogicalTableScan(table=[[myDb, myTable]])
Uncollect
LogicalProject(xs=[$cor0.xs])
LogicalValues(tuples=[[{ 0 }]])
{code}
obtained sql:
{code:sql}
SELECT
"$cor0"."a",
"$cor0"."xs0" AS "x" -- <-- xs0 ?
FROM
(
SELECT "a", "n1"."n11"."b", "n1"."n12"."c", "n2"."d", "xs", "e" FROM
"myDb"."myTable"
) AS "$cor0",
LATERAL UNNEST (
SELECT "$cor0"."xs" FROM (VALUES (0)) AS "t" ("ZERO")
) AS "t1" ("xs") AS "t10"
{code}
I would expect the query to be converted to something close to the original
query. Here "xs0" does not exists.
[https://github.com/MasseGuillaume/calcite/commit/0126e6cfa47061886b2012ad2d2c32408455ae88]
> RelToSqlConverter fails to convert correlated lateral joins
> ---
>
> Key: CALCITE-6218
> URL: https://issues.apache.org/jira/browse/CALCITE-6218
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Guillaume Massé
>Priority: Major
>
>
> input query:
> {code:sql}
> SELECT
> "a", "x"
> FROM
> "myDb"."myTable",
> unnest("xs") as "x"; {code}
> schema:
> [https://github.com/MasseGuillaume/calcite/blob/0126e6cfa47061886b2012ad2d2c32408455ae88/testkit/src/main/java/org/apache/calcite/test/CalciteAssert.java#L2180-L2211]
> {code:java}
> myTable(
> a: BIGINT,
> // ...
> xs: ARRAY,
> // ...
> )
> {code}
> logical plan:
> {code:java}
> LogicalProject(a=[$0], x=[$6])
> LogicalCorrelate(correlation=[$cor0], joinType=[inner],
> requiredColumns=[{4}])
> LogicalProject(a=[$0], b=[$1.n11.b], c=[$1.n12.c], d=[$2.d], xs=[$3],
> e=[$4])
> LogicalTableScan(table=[[myDb, myTable]])
> Uncollect
> LogicalProject(xs=[$cor0.xs])
> LogicalValues(tuples=[[{ 0 }]])
> {code}
> obtained sql:
>
> {code:sql}
> SELECT
> "$cor0"."a",
> "$cor0"."xs0" AS "x" -- <-- xs0 ?
> FROM
> (
> SELECT "a", "n1"."n11"."b", "n1"."n12"."c", "n2"."d", "xs", "e" FROM
> "myDb"."myTable"
> ) AS "$cor0",
> LATERAL UNNEST (
> SELECT "$cor0"."xs" FROM (VALUES (0)) AS "t" ("ZERO")
> ) AS "t1" ("xs") AS "t10"
> {code}
> I would expect the query to be converted to something close to the original
> query. Here "xs0" does not exists.
>
> [https://github.com/MasseGuillaume/calcite/commit/0126e6cfa47061886b2012ad2d2c32408455ae88]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6220) Rewrite MIN/MAX(bool) as BOOL_AND/BOOL_OR for Postgres, Redshift
[ https://issues.apache.org/jira/browse/CALCITE-6220?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-6220: Labels: pull-request-available (was: ) > Rewrite MIN/MAX(bool) as BOOL_AND/BOOL_OR for Postgres, Redshift > > > Key: CALCITE-6220 > URL: https://issues.apache.org/jira/browse/CALCITE-6220 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > Labels: pull-request-available > > Postgres and Redshift do not allow for MIN/MAX on columns of type BOOLEAN, > instead they use the BOOL_AND/BOOL_OR functions, which Calcite already > supports. > While CALCITE-6206 is in progress, I plan on just adding this as an override > during unparsing for now unless there are objections. After CALCITE-6206 is > resolved this could be added to the mapping I described there. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6220) Rewrite MIN/MAX(bool) as BOOL_AND/BOOL_OR for Postgres, Redshift
[ https://issues.apache.org/jira/browse/CALCITE-6220?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Tanner Clary updated CALCITE-6220: -- Summary: Rewrite MIN/MAX(bool) as BOOL_AND/BOOL_OR for Postgres, Redshift (was: Map MAX/MIN to BOOL_OR/BOOL_AND for Postgres and Redshift) > Rewrite MIN/MAX(bool) as BOOL_AND/BOOL_OR for Postgres, Redshift > > > Key: CALCITE-6220 > URL: https://issues.apache.org/jira/browse/CALCITE-6220 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > > Postgres and Redshift do not allow for MIN/MAX on columns of type BOOLEAN, > instead they use the BOOL_AND/BOOL_OR functions, which Calcite already > supports. > While CALCITE-6206 is in progress, I plan on just adding this as an override > during unparsing for now unless there are objections. After CALCITE-6206 is > resolved this could be added to the mapping I described there. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-6211) SUBSTRING with Integer.MIN_VALUE as a second parameter raise unexpected exception
[
https://issues.apache.org/jira/browse/CALCITE-6211?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihai Budiu resolved CALCITE-6211.
--
Fix Version/s: 1.37.0
Resolution: Fixed
Fixed in
https://github.com/apache/calcite/commit/91a8ab8c061f80115ce64fb5be62e02937a0703a
Thank you, [~zstan]
> SUBSTRING with Integer.MIN_VALUE as a second parameter raise unexpected
> exception
> -
>
> Key: CALCITE-6211
> URL: https://issues.apache.org/jira/browse/CALCITE-6211
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Evgeny Stanilovsky
>Assignee: Evgeny Stanilovsky
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> SUBSTRING function with minimal possible integer offset parameter raise
> exception.
> Simple way to reproduce :
> append into *SqlOperatorTest* smth like:
> {noformat}
> f.checkScalar(
> String.format("{fn SUBSTRING('abcdef', %d)}", Integer.MIN_VALUE),
> "abcdef",
> "VARCHAR(6) NOT NULL");
> {noformat}
> And exception will raised:
> {noformat}
> String index out of range: -2147483641
> {noformat}
> it`s all due to integer overflow, check implementation near:
> {noformat}
> public static String substring(String c, int s) {
> final int s0 = s - 1; // -2147483648 - 1 = 2147483647
> if (s0 <= 0) { // this check is broken
> return c;
> }
> if (s > c.length()) {
> return "";
> }
> return c.substring(s0);
> }
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6222) Mysql does not have to_char function
[
https://issues.apache.org/jira/browse/CALCITE-6222?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihai Budiu resolved CALCITE-6222.
--
Resolution: Fixed
Resolved in
https://github.com/apache/calcite/commit/481d6acb4dd3479d864bc82120be73d7c96a1673
Thank you, [~caicancai]
For such small problems it's not necessary to file a JIRA issue.
> Mysql does not have to_char function
>
>
> Key: CALCITE-6222
> URL: https://issues.apache.org/jira/browse/CALCITE-6222
> Project: Calcite
> Issue Type: Bug
> Components: site
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> {code:java}
> mysql> select to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS')
> -> ;
> ERROR 1305 (42000): FUNCTION test.to_char does not exist
> {code}
> mysql does not seem to have a to_char function
>
> link：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6222) Mysql does not have to_char function
[
https://issues.apache.org/jira/browse/CALCITE-6222?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Caican Cai updated CALCITE-6222:
Description:
{code:java}
mysql> select to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS')
-> ;
ERROR 1305 (42000): FUNCTION test.to_char does not exist
{code}
mysql does not seem to have a to_char function
link：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
was:
{code:java}
mysql> select to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS')
-> ;
ERROR 1305 (42000): FUNCTION test.to_char does not exist
{code}
mysql does not seem to have a to_char function
> Mysql does not have to_char function
>
>
> Key: CALCITE-6222
> URL: https://issues.apache.org/jira/browse/CALCITE-6222
> Project: Calcite
> Issue Type: Bug
> Components: site
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> {code:java}
> mysql> select to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS')
> -> ;
> ERROR 1305 (42000): FUNCTION test.to_char does not exist
> {code}
> mysql does not seem to have a to_char function
>
> link：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6222) Mysql does not have to_char function
[
https://issues.apache.org/jira/browse/CALCITE-6222?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-6222:
Labels: pull-request-available (was: )
> Mysql does not have to_char function
>
>
> Key: CALCITE-6222
> URL: https://issues.apache.org/jira/browse/CALCITE-6222
> Project: Calcite
> Issue Type: Bug
> Components: site
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> {code:java}
> mysql> select to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS')
> -> ;
> ERROR 1305 (42000): FUNCTION test.to_char does not exist
> {code}
> mysql does not seem to have a to_char function
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6222) Mysql does not have to_char function
Caican Cai created CALCITE-6222:
---
Summary: Mysql does not have to_char function
Key: CALCITE-6222
URL: https://issues.apache.org/jira/browse/CALCITE-6222
Project: Calcite
Issue Type: Bug
Components: site
Affects Versions: 1.36.0
Reporter: Caican Cai
Fix For: 1.37.0
{code:java}
mysql> select to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS')
-> ;
ERROR 1305 (42000): FUNCTION test.to_char does not exist
{code}
mysql does not seem to have a to_char function
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6063) ARRAY subquery with OrderBy loses Sort
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ran Tao updated CALCITE-6063:
-
Description:
calcite support array query constructor.
but If we run sub-query with orderby:
{code:java}
select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
select array(select x from unnest(array[1,2,3]) as t(x) order by x asc); {code}
they both return
{code:java}
+---+
| EXPR$0 |
+---+
| [1, 2, 3] |
+---+
{code}
however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
It seems that the *order by* not works properly in array sub-query.
This issue is introduced by the issue
https://issues.apache.org/jira/browse/CALCITE-2978
However the ARRAY is not applicable in this scenario.
was:
calcite support array query constructor.
but If we run sub-query with orderby:
{code:java}
select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
select array(select x from unnest(array[1,2,3]) as t(x) order by x asc); {code}
they both return
{code:java}
+---+
| EXPR$0 |
+---+
| [1, 2, 3] |
+---+
{code}
however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
It seems that the *order by* not works properly in array sub-query.
This issue is introduced by the issue
https://issues.apache.org/jira/browse/CALCITE-2978
However the ARRAY is not applicable in this scenario.
we also have another issue broke it.
https://issues.apache.org/jira/browse/CALCITE-3738
> ARRAY subquery with OrderBy loses Sort
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6063) ARRAY subquery with OrderBy loses Sort
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810328#comment-17810328
]
Ran Tao edited comment on CALCITE-6063 at 1/24/24 10:57 AM:
The PR is ready, welcome everyone to review it.
was (Author: lemonjing):
The PR is ready, welcome everyone to help to review it.
> ARRAY subquery with OrderBy loses Sort
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
> we also have another issue broke it.
> https://issues.apache.org/jira/browse/CALCITE-3738
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6063) ARRAY subquery with OrderBy loses Sort
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810328#comment-17810328
]
Ran Tao edited comment on CALCITE-6063 at 1/24/24 10:54 AM:
The PR is ready, welcome everyone to help to review it.
was (Author: lemonjing):
The PR is ready, looking for the review.
> ARRAY subquery with OrderBy loses Sort
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
> we also have another issue broke it.
> https://issues.apache.org/jira/browse/CALCITE-3738
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6063) ARRAY subquery with OrderBy loses Sort
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810328#comment-17810328
]
Ran Tao commented on CALCITE-6063:
--
The PR is ready, looking for the review.
> ARRAY subquery with OrderBy loses Sort
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
> we also have another issue broke it.
> https://issues.apache.org/jira/browse/CALCITE-3738
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6063) ARRAY subquery with OrderBy loses Sort
[
https://issues.apache.org/jira/browse/CALCITE-6063?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-6063:
Labels: pull-request-available (was: )
> ARRAY subquery with OrderBy loses Sort
> --
>
> Key: CALCITE-6063
> URL: https://issues.apache.org/jira/browse/CALCITE-6063
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
>
> calcite support array query constructor.
> but If we run sub-query with orderby:
> {code:java}
> select array(select x from unnest(array[1,2,3]) as t(x) order by x desc);
> select array(select x from unnest(array[1,2,3]) as t(x) order by x asc);
> {code}
> they both return
> {code:java}
> +---+
> | EXPR$0 |
> +---+
> | [1, 2, 3] |
> +---+
> {code}
> however, we expect return *[3, 2, 1]* when use {*}order by x desc{*}.
> It seems that the *order by* not works properly in array sub-query.
> This issue is introduced by the issue
> https://issues.apache.org/jira/browse/CALCITE-2978
> However the ARRAY is not applicable in this scenario.
> we also have another issue broke it.
> https://issues.apache.org/jira/browse/CALCITE-3738
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6221) Invalid query generated when the same table is joined multiple times
Ulrich Kramer created CALCITE-6221:
--
Summary: Invalid query generated when the same table is joined
multiple times
Key: CALCITE-6221
URL: https://issues.apache.org/jira/browse/CALCITE-6221
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.36.0
Environment: Local development
Reporter: Ulrich Kramer
Adding the following unit test to {{JdbcAdapterTest}}
{code:java}
@Test void testUnknownColumn() {
CalciteAssert.model(JdbcTest.SCOTT_MODEL)
.query("SELECT\n" +
"\"content-format-owner\",\n" +
"\"content-owner\"\n" +
"FROM\n" +
"(\n" +
"SELECT\n" +
"d1.dname AS \"content-format-owner\",\n" +
"d2.dname || ' ' AS \"content-owner\"\n" +
"FROM\n" +
"scott.emp e1\n" +
"left outer join scott.dept d1 on e1.deptno =
d1.deptno\n" +
"left outer join scott.dept d2 on e1.deptno =
d2.deptno\n" +
"left outer join scott.emp e2 on e1.deptno =
e2.deptno\n" +
"GROUP BY\n" +
"d1.dname,\n" +
"d2.dname\n" +
")\n" +
"WHERE\n" +
"\"content-owner\" IN (?)")
.runs();
}
{code}
Fails because the following SQL is sent to the underlying database
{code:SQL}
SELECT
"t2"."DNAME" AS "content-format-owner",
"t2"."DNAME0" || ' ' AS "content-owner"
FROM
(
SELECT
*
FROM
(
SELECT
"DEPTNO"
FROM
"SCOTT"."EMP"
) AS "t"
LEFT JOIN (
SELECT
"DEPTNO",
"DNAME"
FROM
"SCOTT"."DEPT"
) AS "t0" ON "t"."DEPTNO" = "t0"."DEPTNO"
LEFT JOIN (
SELECT
"DEPTNO",
"DNAME"
FROM
"SCOTT"."DEPT"
) AS "t1" ON "t"."DEPTNO" = "t1"."DEPTNO"
WHERE
"t1"."DNAME" || ' ' = ?
) AS "t2"
LEFT JOIN (
SELECT
"DEPTNO"
FROM
"SCOTT"."EMP"
) AS "t3" ON "t2"."DEPTNO" = "t3"."DEPTNO"
GROUP BY
"t2"."DNAME",
"t2"."DNAME0"
{code}
The column {{"t2"."DNAME0"}} does not exist.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6213) The default behavior of NullCollation in Presto is LAST
[
https://issues.apache.org/jira/browse/CALCITE-6213?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
hongyu guo resolved CALCITE-6213.
-
Fix Version/s: 1.37.0
Assignee: EveyWu
Resolution: Fixed
> The default behavior of NullCollation in Presto is LAST
> ---
>
> Key: CALCITE-6213
> URL: https://issues.apache.org/jira/browse/CALCITE-6213
> Project: Calcite
> Issue Type: Bug
>Reporter: EveyWu
>Assignee: EveyWu
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
> Attachments: image-2024-01-19-23-54-26-383.png,
> image-2024-01-19-23-57-00-252.png
>
>
> [https://prestodb.io/docs/0.285/sql/select.html]
> Presto default null ordering is NULLS LAST
> !image-2024-01-19-23-54-26-383.png|width=511,height=168!
>
> Demo：select by presto with nulls last
> {code:java}
> select * from product order by brand_name; {code}
> !image-2024-01-19-23-57-00-252.png|width=517,height=83!
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6213) The default behavior of NullCollation in Presto is LAST
[
https://issues.apache.org/jira/browse/CALCITE-6213?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810301#comment-17810301
]
hongyu guo commented on CALCITE-6213:
-
Fixed in
[aca7f02d|https://github.com/apache/calcite/commit/aca7f02dfd510297bd56b07ab94d41033a5146b4].
[~eveywu] Very nice work, thanks for your contribution!
> The default behavior of NullCollation in Presto is LAST
> ---
>
> Key: CALCITE-6213
> URL: https://issues.apache.org/jira/browse/CALCITE-6213
> Project: Calcite
> Issue Type: Bug
>Reporter: EveyWu
>Priority: Major
> Labels: pull-request-available
> Attachments: image-2024-01-19-23-54-26-383.png,
> image-2024-01-19-23-57-00-252.png
>
>
> [https://prestodb.io/docs/0.285/sql/select.html]
> Presto default null ordering is NULLS LAST
> !image-2024-01-19-23-54-26-383.png|width=511,height=168!
>
> Demo：select by presto with nulls last
> {code:java}
> select * from product order by brand_name; {code}
> !image-2024-01-19-23-57-00-252.png|width=517,height=83!
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
