[jira] [Comment Edited] (CALCITE-6242) The "exists" library function throws a "param not found" error when a column is used in lambda evaluation logic.
[
https://issues.apache.org/jira/browse/CALCITE-6242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17814021#comment-17814021
]
hongyu guo edited comment on CALCITE-6242 at 2/4/24 3:26 AM:
-
Thanks for reporting this bug! In CALCITE-3679, referencing columns is not
supported in lambda functions, and we need to add this feature.
was (Author: JIRAUSER300840):
Thanks for report this! In CALCITE-3679, referencing columns is not supported
in lambda functions, and we need to add this feature.
> The "exists" library function throws a "param not found" error when a column
> is used in lambda evaluation logic.
>
>
> Key: CALCITE-6242
> URL: https://issues.apache.org/jira/browse/CALCITE-6242
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Hanumath Rao Maduri
>Priority: Major
>
> The following query is throwing a "param not found" in calcite whereas the
> same query works fine in apache spark.
> {code:java}
> select *
> from (select array(1, 2, 3) as arr) as t1 inner join
> (select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v); {code}
> The following error reported for the above query
> {code:java}
> > java.sql.SQLException: Error while executing SQL "select *
> > from (select array(1, 2, 3) as arr) as t1 inner join
> > (select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v)": From line 3,
> > column 54 to line 3, column 57: Param 'T2.V' not found in lambda expression
> > '`X` -> `X` = `T2`.`V`' {code}
> Steps to repro:
> 1. Place the query in lambda.iq
> 2. Run CoreQuidemTest.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6242) The "exists" library function throws a "param not found" error when a column is used in lambda evaluation logic.
[
https://issues.apache.org/jira/browse/CALCITE-6242?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Hanumath Rao Maduri updated CALCITE-6242:
-
Description:
The following query is throwing a "param not found" in calcite whereas the same
query works fine in apache spark.
{code:java}
select *
from (select array(1, 2, 3) as arr) as t1 inner join
(select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v); {code}
The following error reported for the above query
{code:java}
> java.sql.SQLException: Error while executing SQL "select *
> from (select array(1, 2, 3) as arr) as t1 inner join
> (select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v)": From line 3,
> column 54 to line 3, column 57: Param 'T2.V' not found in lambda expression
> '`X` -> `X` = `T2`.`V`' {code}
Steps to repro:
1. Place the query in lambda.iq
2. Run CoreQuidemTest.
was:
The following query is throwing a "param not found" in calcite whereas the same
query works fine in apache spark.
{code:java}
select *
from (select array(1, 2, 3) as arr) as t1 inner join
(select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v); {code}
The following error reported for the above query is
{code:java}
> java.sql.SQLException: Error while executing SQL "select *
> from (select array(1, 2, 3) as arr) as t1 inner join
> (select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v)": From line 3,
> column 54 to line 3, column 57: Param 'T2.V' not found in lambda expression
> '`X` -> `X` = `T2`.`V`' {code}
Steps to repro:
1. Place the query in lambda.iq
2. Run CoreQuidemTest.
> The "exists" library function throws a "param not found" error when a column
> is used in lambda evaluation logic.
>
>
> Key: CALCITE-6242
> URL: https://issues.apache.org/jira/browse/CALCITE-6242
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Hanumath Rao Maduri
>Priority: Major
>
> The following query is throwing a "param not found" in calcite whereas the
> same query works fine in apache spark.
> {code:java}
> select *
> from (select array(1, 2, 3) as arr) as t1 inner join
> (select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v); {code}
> The following error reported for the above query
> {code:java}
> > java.sql.SQLException: Error while executing SQL "select *
> > from (select array(1, 2, 3) as arr) as t1 inner join
> > (select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v)": From line 3,
> > column 54 to line 3, column 57: Param 'T2.V' not found in lambda expression
> > '`X` -> `X` = `T2`.`V`' {code}
> Steps to repro:
> 1. Place the query in lambda.iq
> 2. Run CoreQuidemTest.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6242) The "exists" library function throws a "param not found" error when a column is used in lambda evaluation logic.
[
https://issues.apache.org/jira/browse/CALCITE-6242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17814021#comment-17814021
]
hongyu guo commented on CALCITE-6242:
-
Thanks for report this! In CALCITE-3679, referencing columns is not supported
in lambda functions, and we need to add this feature.
> The "exists" library function throws a "param not found" error when a column
> is used in lambda evaluation logic.
>
>
> Key: CALCITE-6242
> URL: https://issues.apache.org/jira/browse/CALCITE-6242
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Hanumath Rao Maduri
>Priority: Major
>
> The following query is throwing a "param not found" in calcite whereas the
> same query works fine in apache spark.
> {code:java}
> select *
> from (select array(1, 2, 3) as arr) as t1 inner join
> (select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v); {code}
> The following error reported for the above query is
> {code:java}
> > java.sql.SQLException: Error while executing SQL "select *
> > from (select array(1, 2, 3) as arr) as t1 inner join
> > (select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v)": From line 3,
> > column 54 to line 3, column 57: Param 'T2.V' not found in lambda expression
> > '`X` -> `X` = `T2`.`V`' {code}
> Steps to repro:
> 1. Place the query in lambda.iq
> 2. Run CoreQuidemTest.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6242) The "exists" library function throws a "param not found" error when a column is used in lambda evaluation logic.
[
https://issues.apache.org/jira/browse/CALCITE-6242?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
hongyu guo updated CALCITE-6242:
Component/s: core
(was: spark)
> The "exists" library function throws a "param not found" error when a column
> is used in lambda evaluation logic.
>
>
> Key: CALCITE-6242
> URL: https://issues.apache.org/jira/browse/CALCITE-6242
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Hanumath Rao Maduri
>Priority: Major
>
> The following query is throwing a "param not found" in calcite whereas the
> same query works fine in apache spark.
> {code:java}
> select *
> from (select array(1, 2, 3) as arr) as t1 inner join
> (select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v); {code}
> The following error reported for the above query is
> {code:java}
> > java.sql.SQLException: Error while executing SQL "select *
> > from (select array(1, 2, 3) as arr) as t1 inner join
> > (select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v)": From line 3,
> > column 54 to line 3, column 57: Param 'T2.V' not found in lambda expression
> > '`X` -> `X` = `T2`.`V`' {code}
> Steps to repro:
> 1. Place the query in lambda.iq
> 2. Run CoreQuidemTest.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-1317) Rewrite "MAX" as "ORDER BY ... LIMIT ..."
[
https://issues.apache.org/jira/browse/CALCITE-1317?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17814003#comment-17814003
]
Julian Hyde commented on CALCITE-1317:
--
[~jiajunbernoulli], I agree that those optimizations make sense. Note that
converting "{{WHERE row_number()}} < constant" to "{{LIMIT}} constant" is the
topic of CALCITE-3181 (and what you have described here is the special case
where the {{GROUP BY}} key is empty).
> Rewrite "MAX" as "ORDER BY ... LIMIT ..."
> -
>
> Key: CALCITE-1317
> URL: https://issues.apache.org/jira/browse/CALCITE-1317
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Priority: Major
>
> We could optimize
> {code}
> SELECT *
> FROM emp
> WHERE empno = (SELECT max(empno) FROM emp)
> {code}
> to
> {code}
> SELECT *
> FROM emp
> ORDER BY empno DESC LIMIT 1
> {code}
> (using the fact that {{empno}} is unique and non-NULL). Similarly, we can
> rewrite
> {code}
> SELECT max(sal)
> FROM emp
> {code}
> to
> {code}
> SELECT sal
> FROM emp
> ORDER BY sal DESC LIMIT 1
> {code}
> (not making any assumptions about whether {{sal}} is unique or allows NULL
> values) and we can rewrite a query to find the highest paid employee(s) in
> each department
> {code}
> SELECT *
> FROM emp AS e
> WHERE sal = (
> SELECT max(sal)
> FROM emp AS e2
> WHERE e2.deptno = e.deptno)
> {code}
> as
> {code}
> SELECT deptno, empno, sal
> FROM (
> SELECT deptno, empno, sal, FIRST_VALUE(sal) OVER w AS topSal
> FROM emp
> WINDOW w AS (PARTITION BY deptno ORDER BY sal DESC))
> WHERE sal = topSal
> {code}
> We might benefit from a generalized {{Sort(limit)}} operator that can find
> the top N within any prefix of the sort key, not just the top N overall.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6242) The "exists" library function throws a "param not found" error when a column is used in lambda evaluation logic.
Hanumath Rao Maduri created CALCITE-6242:
Summary: The "exists" library function throws a "param not found"
error when a column is used in lambda evaluation logic.
Key: CALCITE-6242
URL: https://issues.apache.org/jira/browse/CALCITE-6242
Project: Calcite
Issue Type: Bug
Components: spark
Reporter: Hanumath Rao Maduri
The following query is throwing a "param not found" in calcite whereas the same
query works fine in apache spark.
{code:java}
select *
from (select array(1, 2, 3) as arr) as t1 inner join
(select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v); {code}
The following error reported for the above query is
{code:java}
> java.sql.SQLException: Error while executing SQL "select *
> from (select array(1, 2, 3) as arr) as t1 inner join
> (select 1 as v) as t2 on "EXISTS"(arr, x -> x = t2.v)": From line 3,
> column 54 to line 3, column 57: Param 'T2.V' not found in lambda expression
> '`X` -> `X` = `T2`.`V`' {code}
Steps to repro:
1. Place the query in lambda.iq
2. Run CoreQuidemTest.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Add a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Description: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] Add function List： * DECODE * NVL * IFNULL * LEN * LENGTH * LPAD * RPAD * LTRIM * RTRIM * ENDSWITH * STARTSWITH * GREATEST * LEAST * TRANSLATE * BOOL_AND * BOOL_OR * UNIX_DATE * LEFT * REPEAT * RIGHT * SPACE * UNIX_SECONDS * UNIX_MILLIS * UNIX_MICROS * MD5 * SHA1 * POW was: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] Add function List： * DECODE > Add a few existing functions to Spark library > - > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Add function List： > * DECODE > * NVL > * IFNULL > * LEN > * LENGTH > * LPAD > * RPAD > * LTRIM > * RTRIM > * ENDSWITH > * STARTSWITH > * GREATEST > * LEAST > * TRANSLATE > * BOOL_AND > * BOOL_OR > * UNIX_DATE > * LEFT > * REPEAT > * RIGHT > * SPACE > * UNIX_SECONDS > * UNIX_MILLIS > * UNIX_MICROS > * MD5 > * SHA1 > * POW > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Add a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-6241: Labels: pull-request-available (was: ) > Add a few existing functions to Spark library > - > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Add function List： > * DECODE > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Add a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Description: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] Add function List： * DECODE was: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Add a few existing functions to Spark library > - > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > Add function List： > * DECODE > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Add a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Priority: Minor (was: Major) > Add a few existing functions to Spark library > - > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6241) Add a few existing functions to Spark library
EveyWu created CALCITE-6241: Summary: Add a few existing functions to Spark library Key: CALCITE-6241 URL: https://issues.apache.org/jira/browse/CALCITE-6241 Project: Calcite Issue Type: Improvement Reporter: EveyWu Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：https://spark.apache.org/docs/latest/api/sql/index.html#rtrim -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6241) Add a few existing functions to Spark library
[ https://issues.apache.org/jira/browse/CALCITE-6241?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] EveyWu updated CALCITE-6241: Description: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] was: Add Spark as a supported library for functions that have already been implemented for other libraries. Spark Functions Link：https://spark.apache.org/docs/latest/api/sql/index.html#rtrim > Add a few existing functions to Spark library > - > > Key: CALCITE-6241 > URL: https://issues.apache.org/jira/browse/CALCITE-6241 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Major > > Add Spark as a supported library for functions that have already been > implemented for other libraries. > Spark Functions > Link：[https://spark.apache.org/docs/latest/api/sql/index.html|https://spark.apache.org/docs/latest/api/sql/index.html#rtrim] > > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-1317) Rewrite "MAX" as "ORDER BY ... LIMIT ..."
[
https://issues.apache.org/jira/browse/CALCITE-1317?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17813895#comment-17813895
]
Jiajun Xie commented on CALCITE-1317:
-
Oh, I am thinking about similar optimizations recently:
- Empty OVER
{code:java}
SELECT orderkey, orderstatus FROM (
SELECT row_number() OVER () rn, orderkey, orderstatus
FROM orders
) WHERE rn <= 5{code}
to
{code:java}
SELECT orderkey, orderstatus
FROM ORDERS
LIMIT 5 {code}
- OVER with ORDER BY
{code:java}
SELECT orderkey, orderstatus FROM (
SELECT row_number() OVER (ORDER BY orderstatus) rn, orderkey, orderstatus
FROM orders )
WHERE rn <= 5 {code}
to
{code:java}
SELECT orderkey, orderstatus
FROM orders
ORDER BY orderkey
LIMIT 5{code}
> Rewrite "MAX" as "ORDER BY ... LIMIT ..."
> -
>
> Key: CALCITE-1317
> URL: https://issues.apache.org/jira/browse/CALCITE-1317
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Priority: Major
>
> We could optimize
> {code}
> SELECT *
> FROM emp
> WHERE empno = (SELECT max(empno) FROM emp)
> {code}
> to
> {code}
> SELECT *
> FROM emp
> ORDER BY empno DESC LIMIT 1
> {code}
> (using the fact that {{empno}} is unique and non-NULL). Similarly, we can
> rewrite
> {code}
> SELECT max(sal)
> FROM emp
> {code}
> to
> {code}
> SELECT sal
> FROM emp
> ORDER BY sal DESC LIMIT 1
> {code}
> (not making any assumptions about whether {{sal}} is unique or allows NULL
> values) and we can rewrite a query to find the highest paid employee(s) in
> each department
> {code}
> SELECT *
> FROM emp AS e
> WHERE sal = (
> SELECT max(sal)
> FROM emp AS e2
> WHERE e2.deptno = e.deptno)
> {code}
> as
> {code}
> SELECT deptno, empno, sal
> FROM (
> SELECT deptno, empno, sal, FIRST_VALUE(sal) OVER w AS topSal
> FROM emp
> WINDOW w AS (PARTITION BY deptno ORDER BY sal DESC))
> WHERE sal = topSal
> {code}
> We might benefit from a generalized {{Sort(limit)}} operator that can find
> the top N within any prefix of the sort key, not just the top N overall.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6240) Remove ROW_NUMBER if OVER PARTITION BY unique key
[
https://issues.apache.org/jira/browse/CALCITE-6240?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-6240:
Priority: Minor (was: Major)
> Remove ROW_NUMBER if OVER PARTITION BY unique key
> -
>
> Key: CALCITE-6240
> URL: https://issues.apache.org/jira/browse/CALCITE-6240
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Minor
>
> The SQL Only have ROW_NUMBER function.
> {code:java}
> SELECT empno, ename FROM (
> SELECT ROW_NUMBER() OVER (PARTITION BY empno) rn, empno, ename
> FROM emp
> ) WHERE rn <= 5{code}
> to
> {code:java}
> SELECT empno, ename FROM emp{code}
>
> If we know every partition count is less than filter count, we can optimize
> {code:java}
> SELECT deptno, ename FROM (
> SELECT ROW_NUMBER() OVER (PARTITION BY deptno) rn, deptno, ename
> FROM emp
> ) WHERE rn <= N
> -- Statistic let we know every department count is less than N{code}
> to
> {code:java}
> SELECT empno, ename FROM emp {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6240) Remove ROW_NUMBER if OVER PARTITION BY unique key
[
https://issues.apache.org/jira/browse/CALCITE-6240?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jiajun Xie updated CALCITE-6240:
Summary: Remove ROW_NUMBER if OVER PARTITION BY unique key (was: Removed
ROW_NUMBER if OVER PARTITION BY unique key)
> Remove ROW_NUMBER if OVER PARTITION BY unique key
> -
>
> Key: CALCITE-6240
> URL: https://issues.apache.org/jira/browse/CALCITE-6240
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Major
>
> The SQL Only have ROW_NUMBER function.
> {code:java}
> SELECT empno, ename FROM (
> SELECT ROW_NUMBER() OVER (PARTITION BY empno) rn, empno, ename
> FROM emp
> ) WHERE rn <= 5{code}
> to
> {code:java}
> SELECT empno, ename FROM emp{code}
>
> If we know every partition count is less than filter count, we can optimize
> {code:java}
> SELECT deptno, ename FROM (
> SELECT ROW_NUMBER() OVER (PARTITION BY deptno) rn, deptno, ename
> FROM emp
> ) WHERE rn <= N
> -- Statistic let we know every department count is less than N{code}
> to
> {code:java}
> SELECT empno, ename FROM emp {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6240) Removed ROW_NUMBER if OVER PARTITION BY unique key
Jiajun Xie created CALCITE-6240:
---
Summary: Removed ROW_NUMBER if OVER PARTITION BY unique key
Key: CALCITE-6240
URL: https://issues.apache.org/jira/browse/CALCITE-6240
Project: Calcite
Issue Type: Improvement
Components: core
Reporter: Jiajun Xie
Assignee: Jiajun Xie
The SQL Only have ROW_NUMBER function.
{code:java}
SELECT empno, ename FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY empno) rn, empno, ename
FROM emp
) WHERE rn <= 5{code}
to
{code:java}
SELECT empno, ename FROM emp{code}
If we know every partition count is less than filter count, we can optimize
{code:java}
SELECT deptno, ename FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY deptno) rn, deptno, ename
FROM emp
) WHERE rn <= N
-- Statistic let we know every department count is less than N{code}
to
{code:java}
SELECT empno, ename FROM emp {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6237) Using fractions in LOG function does not return correct results
[
https://issues.apache.org/jira/browse/CALCITE-6237?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17813889#comment-17813889
]
Alessandro Solimando commented on CALCITE-6237:
---
Calcite is a powerful but complex SW, there will always be weak spots in your
knowledge: see how many times committers and PMC members ask for a second
opinion or admit they are not very familiar with a certain area.
Knowledge will increase over time, but there is much more to it, like
describing your problem clearly and at the right abstraction level, providing
the needed context for others to understand your proposal/bug, backing up your
hypotheses with facts, being able to look up in the history if anything is
related and take it into consideration, splitting complex issues into simpler
ones (and creating minimal reproducers), how to interact with people, and I am
surely forgetting something along the way.
At first, one thinks that only contributing code matters, but it's way easier
to build those skills by a mix of contributions, reviews of work from others
and carefully following discussions on Jira and the ML.
No need to be anxious, all feedback is precious and meant to help you becoming
a better contributor. In a volunteer-based project nobody invests time
providing feedback if they don't see enough effort or the chance to improve, so
don't lose your motivation and keep and keep pushing.
Hth,
Alessandro
> Using fractions in LOG function does not return correct results
> ---
>
> Key: CALCITE-6237
> URL: https://issues.apache.org/jira/browse/CALCITE-6237
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Major
> Fix For: 1.37.0
>
>
> {code:java}
> mysql> SELECT LOG10(2/3) AS result;
> +-+
> | result |
> +-+
> | -0.1760912594899757 |
> +-+
> 1 row in set (0.00 sec)
> mysql> SELECT LOG2(0) AS result;
> ++
> | result |
> ++
> | NULL |
> ++
> 1 row in set, 1 warning (0.01 sec)
> mysql> SELECT LOG2(2/3) AS result;
> +-+
> | result |
> +-+
> | -0.5849625021638512 |
> +-+
> 1 row in set (0.00 sec)
> {code}
> When using Calcite's Log2 and Log10 functions, if the parameter is a
> fraction, the integer digits will be retained by default. I think this is
> wrong. Several decimal places (such as tens) should be retained by default.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6214) Remove DISTINCT in aggregate function if field is unique
[
https://issues.apache.org/jira/browse/CALCITE-6214?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17813884#comment-17813884
]
Jiajun Xie commented on CALCITE-6214:
-
Sorry, I misunderstood.
The distinct can be removed if we implement `RelOptTable#isKey` for combined
columns.
> Remove DISTINCT in aggregate function if field is unique
>
>
> Key: CALCITE-6214
> URL: https://issues.apache.org/jira/browse/CALCITE-6214
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Jiajun Xie
>Assignee: Jiajun Xie
>Priority: Minor
> Labels: pull-request-available
>
> For the sql
> {code:java}
> select count(distinct x) cnt
> from(
>select distinct sal x from emp
> ) t {code}
> The distinct that in count can be removed.
> {code:java}
> LogicalAggregate(group=[{}], CNT=[COUNT($0)])
> LogicalAggregate(group=[{0}])
> LogicalProject(X=[$5])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
> But `CoreRules#AGGREGATE_REMOVE` not support it, so there are two DISTINCT.
> {code:java}
> LogicalAggregate(group=[{}], CNT=[COUNT(DISTINCT $0)])
> LogicalAggregate(group=[{0}])
> LogicalProject(X=[$5])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
