[jira] [Updated] (CALCITE-6190) Incorrect precision derivation for negative numeric types
[
https://issues.apache.org/jira/browse/CALCITE-6190?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Evgeny Stanilovsky updated CALCITE-6190:
Description:
Test highlights the problem :
{code:java}
@Test void testTypeOfAs() {
sql("select DECIMAL '*100.01*' as c1 from (values (true))")
.columnType("DECIMAL(*5, 2*) NOT NULL");
sql("select DECIMAL '*-100.01*' as c1 from (values (true))")
.columnType("DECIMAL(*5, 2*) NOT NULL");
}
{code}
Throws check exception for second expression:
{noformat}
Expected: is "DECIMAL(5, 2) NOT NULL"
but: was "DECIMAL(6, 2) NOT NULL"
{noformat}
Seems root cause in SqlLiteral#createExactNumeric precision derivation not
consider negative numbers.
was:
Test highlights the problem :
@Test void testTypeOfAs() {
sql("select DECIMAL '*100.01*' as c1 from (values (true))")
.columnType("DECIMAL(*5, 2*) NOT NULL");
sql("select DECIMAL '*-100.01*' as c1 from (values (true))")
.columnType("DECIMAL(*5, 2*) NOT NULL");
}
Throws check exception for second expression:
{noformat}
Expected: is "DECIMAL(5, 2) NOT NULL"
but: was "DECIMAL(6, 2) NOT NULL"
{noformat}
Seems root cause in SqlLiteral#createExactNumeric precision derivation not
consider negative numbers.
> Incorrect precision derivation for negative numeric types
> -
>
> Key: CALCITE-6190
> URL: https://issues.apache.org/jira/browse/CALCITE-6190
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Evgeny Stanilovsky
>Priority: Major
>
> Test highlights the problem :
> {code:java}
> @Test void testTypeOfAs() {
> sql("select DECIMAL '*100.01*' as c1 from (values (true))")
> .columnType("DECIMAL(*5, 2*) NOT NULL");
> sql("select DECIMAL '*-100.01*' as c1 from (values (true))")
> .columnType("DECIMAL(*5, 2*) NOT NULL");
> }
> {code}
> Throws check exception for second expression:
> {noformat}
> Expected: is "DECIMAL(5, 2) NOT NULL"
> but: was "DECIMAL(6, 2) NOT NULL"
> {noformat}
> Seems root cause in SqlLiteral#createExactNumeric precision derivation not
> consider negative numbers.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6190) Incorrect precision derivation for negative numeric types
Evgeny Stanilovsky created CALCITE-6190:
---
Summary: Incorrect precision derivation for negative numeric types
Key: CALCITE-6190
URL: https://issues.apache.org/jira/browse/CALCITE-6190
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.36.0
Reporter: Evgeny Stanilovsky
Test highlights the problem :
@Test void testTypeOfAs() {
sql("select DECIMAL '*100.01*' as c1 from (values (true))")
.columnType("DECIMAL(*5, 2*) NOT NULL");
sql("select DECIMAL '*-100.01*' as c1 from (values (true))")
.columnType("DECIMAL(*5, 2*) NOT NULL");
}
Throws check exception for second expression:
{noformat}
Expected: is "DECIMAL(5, 2) NOT NULL"
but: was "DECIMAL(6, 2) NOT NULL"
{noformat}
Seems root cause in SqlLiteral#createExactNumeric precision derivation not
consider negative numbers.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6185) Support more date format
[ https://issues.apache.org/jira/browse/CALCITE-6185?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803420#comment-17803420 ] Caican Cai commented on CALCITE-6185: - [~julianhyde] Thank you for your reminder and reply, I will modify the jira later. Yes, supporting TIMESTAMP will cause some serious problems. At present, I can see that the test I can pass locally is another result in the cli. It seems that there is no unified standard. I am considering whether to support it. its, > Support more date format > > > Key: CALCITE-6185 > URL: https://issues.apache.org/jira/browse/CALCITE-6185 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > I am trying to support more date formats based on BigQuery and Postgres > documents, and improve some Todo items in FormatElementEnum. > [https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements] > [https://www.postgresql.org/docs/current/functions-formatting.html] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6185) Support more date format
[ https://issues.apache.org/jira/browse/CALCITE-6185?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Caican Cai updated CALCITE-6185: Description: I am trying to support more date formats based on BigQuery and Postgres documents, and improve some Todo items in FormatElementEnum. [https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements] [https://www.postgresql.org/docs/current/functions-formatting.html] was: I am trying to support more date formats based on BigQuery and Postgres documents, and improve some Todo items in FormatElementEnum. [https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements] https://www.postgresql.org/docs/current/functions-formatting.html > Support more date format > > > Key: CALCITE-6185 > URL: https://issues.apache.org/jira/browse/CALCITE-6185 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > I am trying to support more date formats based on BigQuery and Postgres > documents, and improve some Todo items in FormatElementEnum. > [https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements] > [https://www.postgresql.org/docs/current/functions-formatting.html] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6189) Improve FormatElementEnumTest
[ https://issues.apache.org/jira/browse/CALCITE-6189?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-6189: Labels: pull-request-available (was: ) > Improve FormatElementEnumTest > - > > Key: CALCITE-6189 > URL: https://issues.apache.org/jira/browse/CALCITE-6189 > Project: Calcite > Issue Type: Improvement > Components: tests >Affects Versions: 1.36.0 >Reporter: Forward Xu >Assignee: Forward Xu >Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6189) Improve FormatElementEnumTest
Forward Xu created CALCITE-6189: --- Summary: Improve FormatElementEnumTest Key: CALCITE-6189 URL: https://issues.apache.org/jira/browse/CALCITE-6189 Project: Calcite Issue Type: Improvement Components: tests Affects Versions: 1.36.0 Reporter: Forward Xu Assignee: Forward Xu Fix For: 1.37.0 -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6188) Multi-query optimization
[
https://issues.apache.org/jira/browse/CALCITE-6188?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803376#comment-17803376
]
Jay Narale edited comment on CALCITE-6188 at 1/5/24 1:18 AM:
-
Not sure if it is the same but currently we treat the Sequence node strictly as
a logical entity, which has the information of the DAG. We then manage the flow
of the DAG through our scheduler. This approach eliminates the necessity for a
physical operator to handle or maintain consistency and logical transformations
can be applied independently.
So our sequence node makes sure that all CTEs are materialized in the
topological order of dependencies and then it executes the primary tree
was (Author: jaynarale):
Not sure if it is the same but currently we treat the Sequence node strictly as
a logical entity, which has the information of the DAG. We then manage the flow
of the DAG through our scheduler. This approach eliminates the necessity for a
physical operator to handle or maintain consistency and logical transformations
can be applied independently
> Multi-query optimization
>
>
> Key: CALCITE-6188
> URL: https://issues.apache.org/jira/browse/CALCITE-6188
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Priority: Major
>
> Devise extensions to SQL so that queries with multiple input tables, multiple
> intermediate tables, and multiple output data sets (result sets and DML
> operations) can be defined in one SQL statement, optimized, and executed
> atomically.
> There are many flavors of multi-query optimization, depending on whether each
> occurrence of "multiple" in the previous paragraph is replaced with 0, 1 or
> "several". Our goal, here, is to allow them all to be expressed. We can then
> devise planning strategies that work for particular flavors.
> Examples of multi-queries:
> * {*}Multiple DML outputs{*}. An INSERT statement that writes into a table
> but also updates an index,
> * {*}Multiple DML outputs, complex intermediate tables{*}. A DAG that
> represents an ETL/ELT job;
> * {*}Multiple query outputs{*}. A query that produces several data sets (say
> a list of invoices for
> orders and a list of products that need to be restocked);
> * {*}DAG query{*}. A query that uses intermediate results more than once.
> See discussion in the [Multi-query optimization email
> thread|https://lists.apache.org/thread/mcdqwrtpx0os54t2nn9vtk17spkp5o5k].
> Here are some SQL examples.
> We add a new keyword {{MULTI}} that represents a statement whose output
> contains multiple data sets and DML operations, each with a unique name. For
> intermediate results, we use the existing {{WITH}} clause.
> h3. 1. Multi DML
> Read from one or more tables, write to one or more tables.
> An example is inserting into a table and also an index on that table
> (represented as a sorted table).
> {code:sql}
> WITH
> empDelta AS (
> SELECT * FROM emp2
> EXCEPT
> SELECT * FROM emp)
> MULTI
> insertEmp AS (
> INSERT INTO emp
> TABLE empDelta),
> insertEmpDeptno AS (
> MERGE empDeptno AS e
> USING TABLE empDelta AS d
> ON e.deptno = d.deptno
> WHEN NOT MATCHED THEN INSERT VALUES (deptno));
> {code}
> h3. 2. Query that creates temporary table and uses it more than once
> {code:sql}
> WITH
> temp AS (
> SELECT *
> FROM emp AS e
> JOIN dept USING (deptno)
> WHERE e.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> SELECT deptno,
> (SELECT AVG(sal)
> FROM temp AS t
> WHERE t.deptno = e.deptno) AS deptAvgSal,
> (SELECT AVG(sal)
> FROM temp AS t
> WHERE t.job = e.job) AS jobAvgSal
> FROM e
> WHERE e.deptno IN (10, 20);
> {code}
> h3. 3. Query whose optimal plan might use a temporary table
> This query produces the same result as the previous query. There is a common
> relational expression, so the optimizer should consider a DAG plan with a
> reified intermediate result.
> {code:sql}
> SELECT deptno,
> (SELECT AVG(e2.sal)
> FROM emp AS e2
> JOIN dept AS d USING (deptno)
> WHERE (e2.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> AND e2.deptno = e.deptno) AS deptAvgSal,
> (SELECT AVG(e3.sal)
> FROM emp AS e3
> JOIN dept AS d USING (deptno)
> WHERE (e3.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> AND e3.job = e.job) AS jobAvgSal
> FROM e
> WHERE e.deptno IN (10, 20);
> {code}
> h3. 4. Query that produces several data sets
> {code:sql}
> WITH
> newOrders AS (
> SELECT *
> FROM orders
> WHERE orderDate > DATE '2023-01-25')
> MULTI
> invoices AS (
> SELECT customerName, SUM(amount)
> FROM newOrders
> GROUP BY customerName),
> restock AS (
> SELECT productId
> FROM inventory
> WHERE productId IN (
> SELECT
[jira] [Comment Edited] (CALCITE-6188) Multi-query optimization
[
https://issues.apache.org/jira/browse/CALCITE-6188?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803376#comment-17803376
]
Jay Narale edited comment on CALCITE-6188 at 1/5/24 1:15 AM:
-
Not sure if it is the same but currently we treat the Sequence node strictly as
a logical entity, which has the information of the DAG. We then manage the flow
of the DAG through our scheduler. This approach eliminates the necessity for a
physical operator to handle or maintain consistency and logical transformations
can be applied independently
was (Author: jaynarale):
Not sure if it is the same but currently we treat the Sequence node strictly as
a logical entity, which has the information of the DAG. We then manage the flow
of the Directed Acyclic Graph (DAG) through our scheduler. This approach
eliminates the necessity for a physical operator to handle or maintain
consistency and logical transformations can be applied independently
> Multi-query optimization
>
>
> Key: CALCITE-6188
> URL: https://issues.apache.org/jira/browse/CALCITE-6188
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Priority: Major
>
> Devise extensions to SQL so that queries with multiple input tables, multiple
> intermediate tables, and multiple output data sets (result sets and DML
> operations) can be defined in one SQL statement, optimized, and executed
> atomically.
> There are many flavors of multi-query optimization, depending on whether each
> occurrence of "multiple" in the previous paragraph is replaced with 0, 1 or
> "several". Our goal, here, is to allow them all to be expressed. We can then
> devise planning strategies that work for particular flavors.
> Examples of multi-queries:
> * {*}Multiple DML outputs{*}. An INSERT statement that writes into a table
> but also updates an index,
> * {*}Multiple DML outputs, complex intermediate tables{*}. A DAG that
> represents an ETL/ELT job;
> * {*}Multiple query outputs{*}. A query that produces several data sets (say
> a list of invoices for
> orders and a list of products that need to be restocked);
> * {*}DAG query{*}. A query that uses intermediate results more than once.
> See discussion in the [Multi-query optimization email
> thread|https://lists.apache.org/thread/mcdqwrtpx0os54t2nn9vtk17spkp5o5k].
> Here are some SQL examples.
> We add a new keyword {{MULTI}} that represents a statement whose output
> contains multiple data sets and DML operations, each with a unique name. For
> intermediate results, we use the existing {{WITH}} clause.
> h3. 1. Multi DML
> Read from one or more tables, write to one or more tables.
> An example is inserting into a table and also an index on that table
> (represented as a sorted table).
> {code:sql}
> WITH
> empDelta AS (
> SELECT * FROM emp2
> EXCEPT
> SELECT * FROM emp)
> MULTI
> insertEmp AS (
> INSERT INTO emp
> TABLE empDelta),
> insertEmpDeptno AS (
> MERGE empDeptno AS e
> USING TABLE empDelta AS d
> ON e.deptno = d.deptno
> WHEN NOT MATCHED THEN INSERT VALUES (deptno));
> {code}
> h3. 2. Query that creates temporary table and uses it more than once
> {code:sql}
> WITH
> temp AS (
> SELECT *
> FROM emp AS e
> JOIN dept USING (deptno)
> WHERE e.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> SELECT deptno,
> (SELECT AVG(sal)
> FROM temp AS t
> WHERE t.deptno = e.deptno) AS deptAvgSal,
> (SELECT AVG(sal)
> FROM temp AS t
> WHERE t.job = e.job) AS jobAvgSal
> FROM e
> WHERE e.deptno IN (10, 20);
> {code}
> h3. 3. Query whose optimal plan might use a temporary table
> This query produces the same result as the previous query. There is a common
> relational expression, so the optimizer should consider a DAG plan with a
> reified intermediate result.
> {code:sql}
> SELECT deptno,
> (SELECT AVG(e2.sal)
> FROM emp AS e2
> JOIN dept AS d USING (deptno)
> WHERE (e2.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> AND e2.deptno = e.deptno) AS deptAvgSal,
> (SELECT AVG(e3.sal)
> FROM emp AS e3
> JOIN dept AS d USING (deptno)
> WHERE (e3.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> AND e3.job = e.job) AS jobAvgSal
> FROM e
> WHERE e.deptno IN (10, 20);
> {code}
> h3. 4. Query that produces several data sets
> {code:sql}
> WITH
> newOrders AS (
> SELECT *
> FROM orders
> WHERE orderDate > DATE '2023-01-25')
> MULTI
> invoices AS (
> SELECT customerName, SUM(amount)
> FROM newOrders
> GROUP BY customerName),
> restock AS (
> SELECT productId
> FROM inventory
> WHERE productId IN (
> SELECT productId FROM newOrders)
> AND itemsOnHand < 10);
> {code}
> h3. 5. Query with a complex DAG, multiple output data sets
[jira] [Comment Edited] (CALCITE-6188) Multi-query optimization
[
https://issues.apache.org/jira/browse/CALCITE-6188?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803376#comment-17803376
]
Jay Narale edited comment on CALCITE-6188 at 1/5/24 1:15 AM:
-
Not sure if it is the same but currently we treat the Sequence node strictly as
a logical entity, which has the information of the DAG. We then manage the flow
of the Directed Acyclic Graph (DAG) through our scheduler. This approach
eliminates the necessity for a physical operator to handle or maintain
consistency and logical transformations can be applied independently
was (Author: jaynarale):
Not sure if it is the same but currently we treat the Sequence node strictly as
a logical entity, which has the information of the DAG. We then manage the flow
of the Directed Acyclic Graph (DAG) through our scheduler. This approach
eliminates the necessity for a physical operator to handle or maintain
consistency.
> Multi-query optimization
>
>
> Key: CALCITE-6188
> URL: https://issues.apache.org/jira/browse/CALCITE-6188
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Priority: Major
>
> Devise extensions to SQL so that queries with multiple input tables, multiple
> intermediate tables, and multiple output data sets (result sets and DML
> operations) can be defined in one SQL statement, optimized, and executed
> atomically.
> There are many flavors of multi-query optimization, depending on whether each
> occurrence of "multiple" in the previous paragraph is replaced with 0, 1 or
> "several". Our goal, here, is to allow them all to be expressed. We can then
> devise planning strategies that work for particular flavors.
> Examples of multi-queries:
> * {*}Multiple DML outputs{*}. An INSERT statement that writes into a table
> but also updates an index,
> * {*}Multiple DML outputs, complex intermediate tables{*}. A DAG that
> represents an ETL/ELT job;
> * {*}Multiple query outputs{*}. A query that produces several data sets (say
> a list of invoices for
> orders and a list of products that need to be restocked);
> * {*}DAG query{*}. A query that uses intermediate results more than once.
> See discussion in the [Multi-query optimization email
> thread|https://lists.apache.org/thread/mcdqwrtpx0os54t2nn9vtk17spkp5o5k].
> Here are some SQL examples.
> We add a new keyword {{MULTI}} that represents a statement whose output
> contains multiple data sets and DML operations, each with a unique name. For
> intermediate results, we use the existing {{WITH}} clause.
> h3. 1. Multi DML
> Read from one or more tables, write to one or more tables.
> An example is inserting into a table and also an index on that table
> (represented as a sorted table).
> {code:sql}
> WITH
> empDelta AS (
> SELECT * FROM emp2
> EXCEPT
> SELECT * FROM emp)
> MULTI
> insertEmp AS (
> INSERT INTO emp
> TABLE empDelta),
> insertEmpDeptno AS (
> MERGE empDeptno AS e
> USING TABLE empDelta AS d
> ON e.deptno = d.deptno
> WHEN NOT MATCHED THEN INSERT VALUES (deptno));
> {code}
> h3. 2. Query that creates temporary table and uses it more than once
> {code:sql}
> WITH
> temp AS (
> SELECT *
> FROM emp AS e
> JOIN dept USING (deptno)
> WHERE e.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> SELECT deptno,
> (SELECT AVG(sal)
> FROM temp AS t
> WHERE t.deptno = e.deptno) AS deptAvgSal,
> (SELECT AVG(sal)
> FROM temp AS t
> WHERE t.job = e.job) AS jobAvgSal
> FROM e
> WHERE e.deptno IN (10, 20);
> {code}
> h3. 3. Query whose optimal plan might use a temporary table
> This query produces the same result as the previous query. There is a common
> relational expression, so the optimizer should consider a DAG plan with a
> reified intermediate result.
> {code:sql}
> SELECT deptno,
> (SELECT AVG(e2.sal)
> FROM emp AS e2
> JOIN dept AS d USING (deptno)
> WHERE (e2.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> AND e2.deptno = e.deptno) AS deptAvgSal,
> (SELECT AVG(e3.sal)
> FROM emp AS e3
> JOIN dept AS d USING (deptno)
> WHERE (e3.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> AND e3.job = e.job) AS jobAvgSal
> FROM e
> WHERE e.deptno IN (10, 20);
> {code}
> h3. 4. Query that produces several data sets
> {code:sql}
> WITH
> newOrders AS (
> SELECT *
> FROM orders
> WHERE orderDate > DATE '2023-01-25')
> MULTI
> invoices AS (
> SELECT customerName, SUM(amount)
> FROM newOrders
> GROUP BY customerName),
> restock AS (
> SELECT productId
> FROM inventory
> WHERE productId IN (
> SELECT productId FROM newOrders)
> AND itemsOnHand < 10);
> {code}
> h3. 5. Query with a complex DAG, multiple output data sets and one DML
> {code:sql}
> WITH
[jira] [Commented] (CALCITE-6188) Multi-query optimization
[
https://issues.apache.org/jira/browse/CALCITE-6188?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803376#comment-17803376
]
Jay Narale commented on CALCITE-6188:
-
Not sure if it is the same but currently we treat the Sequence node strictly as
a logical entity, which has the information of the DAG. We then manage the flow
of the Directed Acyclic Graph (DAG) through our scheduler. This approach
eliminates the necessity for a physical operator to handle or maintain
consistency.
> Multi-query optimization
>
>
> Key: CALCITE-6188
> URL: https://issues.apache.org/jira/browse/CALCITE-6188
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Priority: Major
>
> Devise extensions to SQL so that queries with multiple input tables, multiple
> intermediate tables, and multiple output data sets (result sets and DML
> operations) can be defined in one SQL statement, optimized, and executed
> atomically.
> There are many flavors of multi-query optimization, depending on whether each
> occurrence of "multiple" in the previous paragraph is replaced with 0, 1 or
> "several". Our goal, here, is to allow them all to be expressed. We can then
> devise planning strategies that work for particular flavors.
> Examples of multi-queries:
> * {*}Multiple DML outputs{*}. An INSERT statement that writes into a table
> but also updates an index,
> * {*}Multiple DML outputs, complex intermediate tables{*}. A DAG that
> represents an ETL/ELT job;
> * {*}Multiple query outputs{*}. A query that produces several data sets (say
> a list of invoices for
> orders and a list of products that need to be restocked);
> * {*}DAG query{*}. A query that uses intermediate results more than once.
> See discussion in the [Multi-query optimization email
> thread|https://lists.apache.org/thread/mcdqwrtpx0os54t2nn9vtk17spkp5o5k].
> Here are some SQL examples.
> We add a new keyword {{MULTI}} that represents a statement whose output
> contains multiple data sets and DML operations, each with a unique name. For
> intermediate results, we use the existing {{WITH}} clause.
> h3. 1. Multi DML
> Read from one or more tables, write to one or more tables.
> An example is inserting into a table and also an index on that table
> (represented as a sorted table).
> {code:sql}
> WITH
> empDelta AS (
> SELECT * FROM emp2
> EXCEPT
> SELECT * FROM emp)
> MULTI
> insertEmp AS (
> INSERT INTO emp
> TABLE empDelta),
> insertEmpDeptno AS (
> MERGE empDeptno AS e
> USING TABLE empDelta AS d
> ON e.deptno = d.deptno
> WHEN NOT MATCHED THEN INSERT VALUES (deptno));
> {code}
> h3. 2. Query that creates temporary table and uses it more than once
> {code:sql}
> WITH
> temp AS (
> SELECT *
> FROM emp AS e
> JOIN dept USING (deptno)
> WHERE e.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> SELECT deptno,
> (SELECT AVG(sal)
> FROM temp AS t
> WHERE t.deptno = e.deptno) AS deptAvgSal,
> (SELECT AVG(sal)
> FROM temp AS t
> WHERE t.job = e.job) AS jobAvgSal
> FROM e
> WHERE e.deptno IN (10, 20);
> {code}
> h3. 3. Query whose optimal plan might use a temporary table
> This query produces the same result as the previous query. There is a common
> relational expression, so the optimizer should consider a DAG plan with a
> reified intermediate result.
> {code:sql}
> SELECT deptno,
> (SELECT AVG(e2.sal)
> FROM emp AS e2
> JOIN dept AS d USING (deptno)
> WHERE (e2.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> AND e2.deptno = e.deptno) AS deptAvgSal,
> (SELECT AVG(e3.sal)
> FROM emp AS e3
> JOIN dept AS d USING (deptno)
> WHERE (e3.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> AND e3.job = e.job) AS jobAvgSal
> FROM e
> WHERE e.deptno IN (10, 20);
> {code}
> h3. 4. Query that produces several data sets
> {code:sql}
> WITH
> newOrders AS (
> SELECT *
> FROM orders
> WHERE orderDate > DATE '2023-01-25')
> MULTI
> invoices AS (
> SELECT customerName, SUM(amount)
> FROM newOrders
> GROUP BY customerName),
> restock AS (
> SELECT productId
> FROM inventory
> WHERE productId IN (
> SELECT productId FROM newOrders)
> AND itemsOnHand < 10);
> {code}
> h3. 5. Query with a complex DAG, multiple output data sets and one DML
> {code:sql}
> WITH
> t0 AS (
> SELECT * FROM t WHERE x > 5),
> t00 AS (
> SELECT * FROM t0 WHERE y < 10),
> t000 AS (
> SELECT x, MIN(y) FROM t00 GROUP BY x),
> t1 AS (
> SELECT * FROM t WHERE y > 3),
> t10 AS (
> SELECT * FROM t1 WHERE x < 8),
> t2 AS (
> SELECT * FROM t000
> INTERSECT
> SELECT * FROM t10),
> t3 AS (
> SELECT * FROM u
> INTERSECT
> SELECT * FROM t00)
> MULTI
> q0 AS (SELECT * FROM t3),
> q1 AS
[jira] [Comment Edited] (CALCITE-6186) Enable DATEDIFF for Snowflake, map to BigQuery equivalents
[ https://issues.apache.org/jira/browse/CALCITE-6186?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803374#comment-17803374 ] Tanner Clary edited comment on CALCITE-6186 at 1/5/24 1:07 AM: --- Hey [~julianhyde] I added a Proof of Concept here: https://github.com/apache/calcite/pull/3613 Leave any comments, styling and whatnot will get cleaned up but wanted your thoughts on the initial implementation. was (Author: JIRAUSER298151): Hey [~julianhyde] I added a Proof of Concept here: https://github.com/tanclary/calcite/commit/72815dbbe6894d4bb4cb938b5dab090b28f1047b Leave any comments, styling and whatnot will get cleaned up but wanted your thoughts on the initial implementation. > Enable DATEDIFF for Snowflake, map to BigQuery equivalents > -- > > Key: CALCITE-6186 > URL: https://issues.apache.org/jira/browse/CALCITE-6186 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > > (Any suggestions for a better title would be helpful). > Snowflake supports the DATEDIFF function: > https://docs.snowflake.com/en/sql-reference/functions/datediff > This is equivalent to the BigQuery TIMESTAMP/TIME/DATE/DATETIME_DIFF > functions according to: > https://cloud.google.com/bigquery/docs/migration/snowflake-sql#date_and_time_functions > The operands are reorder but otherwise the functions operate similarly -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6186) Enable DATEDIFF for Snowflake, map to BigQuery equivalents
[ https://issues.apache.org/jira/browse/CALCITE-6186?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803374#comment-17803374 ] Tanner Clary commented on CALCITE-6186: --- Hey [~julianhyde] I added a Proof of Concept here: https://github.com/tanclary/calcite/commit/72815dbbe6894d4bb4cb938b5dab090b28f1047b Leave any comments, styling and whatnot will get cleaned up but wanted your thoughts on the initial implementation. > Enable DATEDIFF for Snowflake, map to BigQuery equivalents > -- > > Key: CALCITE-6186 > URL: https://issues.apache.org/jira/browse/CALCITE-6186 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > > (Any suggestions for a better title would be helpful). > Snowflake supports the DATEDIFF function: > https://docs.snowflake.com/en/sql-reference/functions/datediff > This is equivalent to the BigQuery TIMESTAMP/TIME/DATE/DATETIME_DIFF > functions according to: > https://cloud.google.com/bigquery/docs/migration/snowflake-sql#date_and_time_functions > The operands are reorder but otherwise the functions operate similarly -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6188) Multi-query optimization
[
https://issues.apache.org/jira/browse/CALCITE-6188?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803373#comment-17803373
]
Julian Hyde commented on CALCITE-6188:
--
Thank you, [~jaynarale]! The sequence node is not something I had considered.
I have only come across the case where one DAG node sends a boolean value
(empty result set) to its successor, saying 'I've finished'. Whereas - if I
understand correctly - your sequence says 'I've finished and I have released
all resources'.
> Multi-query optimization
>
>
> Key: CALCITE-6188
> URL: https://issues.apache.org/jira/browse/CALCITE-6188
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Priority: Major
>
> Devise extensions to SQL so that queries with multiple input tables, multiple
> intermediate tables, and multiple output data sets (result sets and DML
> operations) can be defined in one SQL statement, optimized, and executed
> atomically.
> There are many flavors of multi-query optimization, depending on whether each
> occurrence of "multiple" in the previous paragraph is replaced with 0, 1 or
> "several". Our goal, here, is to allow them all to be expressed. We can then
> devise planning strategies that work for particular flavors.
> Examples of multi-queries:
> * {*}Multiple DML outputs{*}. An INSERT statement that writes into a table
> but also updates an index,
> * {*}Multiple DML outputs, complex intermediate tables{*}. A DAG that
> represents an ETL/ELT job;
> * {*}Multiple query outputs{*}. A query that produces several data sets (say
> a list of invoices for
> orders and a list of products that need to be restocked);
> * {*}DAG query{*}. A query that uses intermediate results more than once.
> See discussion in the [Multi-query optimization email
> thread|https://lists.apache.org/thread/mcdqwrtpx0os54t2nn9vtk17spkp5o5k].
> Here are some SQL examples.
> We add a new keyword {{MULTI}} that represents a statement whose output
> contains multiple data sets and DML operations, each with a unique name. For
> intermediate results, we use the existing {{WITH}} clause.
> h3. 1. Multi DML
> Read from one or more tables, write to one or more tables.
> An example is inserting into a table and also an index on that table
> (represented as a sorted table).
> {code:sql}
> WITH
> empDelta AS (
> SELECT * FROM emp2
> EXCEPT
> SELECT * FROM emp)
> MULTI
> insertEmp AS (
> INSERT INTO emp
> TABLE empDelta),
> insertEmpDeptno AS (
> MERGE empDeptno AS e
> USING TABLE empDelta AS d
> ON e.deptno = d.deptno
> WHEN NOT MATCHED THEN INSERT VALUES (deptno));
> {code}
> h3. 2. Query that creates temporary table and uses it more than once
> {code:sql}
> WITH
> temp AS (
> SELECT *
> FROM emp AS e
> JOIN dept USING (deptno)
> WHERE e.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> SELECT deptno,
> (SELECT AVG(sal)
> FROM temp AS t
> WHERE t.deptno = e.deptno) AS deptAvgSal,
> (SELECT AVG(sal)
> FROM temp AS t
> WHERE t.job = e.job) AS jobAvgSal
> FROM e
> WHERE e.deptno IN (10, 20);
> {code}
> h3. 3. Query whose optimal plan might use a temporary table
> This query produces the same result as the previous query. There is a common
> relational expression, so the optimizer should consider a DAG plan with a
> reified intermediate result.
> {code:sql}
> SELECT deptno,
> (SELECT AVG(e2.sal)
> FROM emp AS e2
> JOIN dept AS d USING (deptno)
> WHERE (e2.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> AND e2.deptno = e.deptno) AS deptAvgSal,
> (SELECT AVG(e3.sal)
> FROM emp AS e3
> JOIN dept AS d USING (deptno)
> WHERE (e3.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> AND e3.job = e.job) AS jobAvgSal
> FROM e
> WHERE e.deptno IN (10, 20);
> {code}
> h3. 4. Query that produces several data sets
> {code:sql}
> WITH
> newOrders AS (
> SELECT *
> FROM orders
> WHERE orderDate > DATE '2023-01-25')
> MULTI
> invoices AS (
> SELECT customerName, SUM(amount)
> FROM newOrders
> GROUP BY customerName),
> restock AS (
> SELECT productId
> FROM inventory
> WHERE productId IN (
> SELECT productId FROM newOrders)
> AND itemsOnHand < 10);
> {code}
> h3. 5. Query with a complex DAG, multiple output data sets and one DML
> {code:sql}
> WITH
> t0 AS (
> SELECT * FROM t WHERE x > 5),
> t00 AS (
> SELECT * FROM t0 WHERE y < 10),
> t000 AS (
> SELECT x, MIN(y) FROM t00 GROUP BY x),
> t1 AS (
> SELECT * FROM t WHERE y > 3),
> t10 AS (
> SELECT * FROM t1 WHERE x < 8),
> t2 AS (
> SELECT * FROM t000
> INTERSECT
> SELECT * FROM t10),
> t3 AS (
> SELECT * FROM u
> INTERSECT
> SELECT * FROM t00)
> MULTI
> q0 AS (SELECT * FROM t3),
> q1 AS
[jira] [Updated] (CALCITE-6188) Multi-query optimization
[
https://issues.apache.org/jira/browse/CALCITE-6188?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6188:
-
Description:
Devise extensions to SQL so that queries with multiple input tables, multiple
intermediate tables, and multiple output data sets (result sets and DML
operations) can be defined in one SQL statement, optimized, and executed
atomically.
There are many flavors of multi-query optimization, depending on whether each
occurrence of "multiple" in the previous paragraph is replaced with 0, 1 or
"several". Our goal, here, is to allow them all to be expressed. We can then
devise planning strategies that work for particular flavors.
Examples of multi-queries:
* {*}Multiple DML outputs{*}. An INSERT statement that writes into a table but
also updates an index,
* {*}Multiple DML outputs, complex intermediate tables{*}. A DAG that
represents an ETL/ELT job;
* {*}Multiple query outputs{*}. A query that produces several data sets (say a
list of invoices for
orders and a list of products that need to be restocked);
* {*}DAG query{*}. A query that uses intermediate results more than once.
See discussion in the [Multi-query optimization email
thread|https://lists.apache.org/thread/mcdqwrtpx0os54t2nn9vtk17spkp5o5k].
Here are some SQL examples.
We add a new keyword {{MULTI}} that represents a statement whose output
contains multiple data sets and DML operations, each with a unique name. For
intermediate results, we use the existing {{WITH}} clause.
h3. 1. Multi DML
Read from one or more tables, write to one or more tables.
An example is inserting into a table and also an index on that table
(represented as a sorted table).
{code:sql}
WITH
empDelta AS (
SELECT * FROM emp2
EXCEPT
SELECT * FROM emp)
MULTI
insertEmp AS (
INSERT INTO emp
TABLE empDelta),
insertEmpDeptno AS (
MERGE empDeptno AS e
USING TABLE empDelta AS d
ON e.deptno = d.deptno
WHEN NOT MATCHED THEN INSERT VALUES (deptno));
{code}
h3. 2. Query that creates temporary table and uses it more than once
{code:sql}
WITH
temp AS (
SELECT *
FROM emp AS e
JOIN dept USING (deptno)
WHERE e.job = 'MANAGER'
OR d.location = 'CHICAGO')
SELECT deptno,
(SELECT AVG(sal)
FROM temp AS t
WHERE t.deptno = e.deptno) AS deptAvgSal,
(SELECT AVG(sal)
FROM temp AS t
WHERE t.job = e.job) AS jobAvgSal
FROM e
WHERE e.deptno IN (10, 20);
{code}
h3. 3. Query whose optimal plan might use a temporary table
This query produces the same result as the previous query. There is a common
relational expression, so the optimizer should consider a DAG plan with a
reified intermediate result.
{code:sql}
SELECT deptno,
(SELECT AVG(e2.sal)
FROM emp AS e2
JOIN dept AS d USING (deptno)
WHERE (e2.job = 'MANAGER'
OR d.location = 'CHICAGO')
AND e2.deptno = e.deptno) AS deptAvgSal,
(SELECT AVG(e3.sal)
FROM emp AS e3
JOIN dept AS d USING (deptno)
WHERE (e3.job = 'MANAGER'
OR d.location = 'CHICAGO')
AND e3.job = e.job) AS jobAvgSal
FROM e
WHERE e.deptno IN (10, 20);
{code}
h3. 4. Query that produces several data sets
{code:sql}
WITH
newOrders AS (
SELECT *
FROM orders
WHERE orderDate > DATE '2023-01-25')
MULTI
invoices AS (
SELECT customerName, SUM(amount)
FROM newOrders
GROUP BY customerName),
restock AS (
SELECT productId
FROM inventory
WHERE productId IN (
SELECT productId FROM newOrders)
AND itemsOnHand < 10);
{code}
h3. 5. Query with a complex DAG, multiple output data sets and one DML
{code:sql}
WITH
t0 AS (
SELECT * FROM t WHERE x > 5),
t00 AS (
SELECT * FROM t0 WHERE y < 10),
t000 AS (
SELECT x, MIN(y) FROM t00 GROUP BY x),
t1 AS (
SELECT * FROM t WHERE y > 3),
t10 AS (
SELECT * FROM t1 WHERE x < 8),
t2 AS (
SELECT * FROM t000
INTERSECT
SELECT * FROM t10),
t3 AS (
SELECT * FROM u
INTERSECT
SELECT * FROM t00)
MULTI
q0 AS (SELECT * FROM t3),
q1 AS (SELECT * FROM t2),
d0 AS (DELETE FROM v WHERE v.x IN (SELECT x FROM t1));
{code}
The data flow is the following DAG:
{noformat}
u +
+--> t3 (q0)
+--> t0 --> t00 --+
| +--> t000 --+
t --+ +--> t2 (q1)
| +--> t10 ---+
+--> t1 --+
+--> d0
{noformat}
was:
Devise extensions to SQL so that queries with multiple input tables, multiple
intermediate tables, and multiple output data sets (result sets and DML
operations) can be defined in one SQL statement, optimized, and executed
atomically.
There are many flavors of multi-query optimization, depending on whether each
occurrence of "multiple" in the previous paragraph is replaced with 0, 1 or
"several". Our goal, here, is to allow them all to be expressed. We
[jira] [Comment Edited] (CALCITE-6188) Multi-query optimization
[
https://issues.apache.org/jira/browse/CALCITE-6188?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803368#comment-17803368
]
Jay Narale edited comment on CALCITE-6188 at 1/5/24 12:55 AM:
--
We in Presto have recently supported CTE materialization
[https://github.com/prestodb/presto/pull/20887.
|https://github.com/prestodb/presto/pull/20887]The implementation heavily uses
a new type of logical planNode called a sequence node which makes sure that
deadlocks don't exist. Though our current implementation is not 100% optimized,
we have a similar goal of optimizing all DAGs together.
was (Author: jaynarale):
We in Presto have recently supported CTE materialization
[https://github.com/prestodb/presto/pull/20887.
|https://github.com/prestodb/presto/pull/20887]The implementation heavily uses
a new type of logical planNode called a sequence node. Though our current
implementation is not 100% optimized, we have a similar goal of optimizing all
DAGs together.
> Multi-query optimization
>
>
> Key: CALCITE-6188
> URL: https://issues.apache.org/jira/browse/CALCITE-6188
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Priority: Major
>
> Devise extensions to SQL so that queries with multiple input tables, multiple
> intermediate tables, and multiple output data sets (result sets and DML
> operations) can be defined in one SQL statement, optimized, and executed
> atomically.
> There are many flavors of multi-query optimization, depending on whether each
> occurrence of "multiple" in the previous paragraph is replaced with 0, 1 or
> "several". Our goal, here, is to allow them all to be expressed. We can then
> devise planning strategies that work for particular flavors.
> Examples of multi-queries:
> * {*}Multiple DML outputs{*}. An INSERT statement that writes into a table
> but also updates an index,
> * {*}Multiple DML outputs, complex intermediate tables{*}. A DAG that
> represents an ETL/ELT job;
> * {*}Multiple query outputs{*}. A query that produces several data sets (say
> a list of invoices for
> orders and a list of products that need to be restocked);
> * {*}DAG query{*}. A query that uses intermediate results more than once.
> See discussion in the [Multi-query optimization email
> thread|https://lists.apache.org/thread/mcdqwrtpx0os54t2nn9vtk17spkp5o5k].
> Here are some SQL examples.
> We add a new keyword {{MULTI}} that represents a statement whose output
> contains multiple data sets and DML operations, each with a unique name. For
> intermediate results, we use the existing {{WITH}} clause.
> h3. 1. Multi DML
> Read from one or more tables, write to one or more tables.
> An example is inserting into a table and also an index on that table
> (represented as a sorted table).
> {code:sql}
> WITH
> empDelta AS (
> SELECT * FROM emp2
> EXCEPT
> SELECT * FROM emp)
> MULTI
> insertEmp AS (
> INSERT INTO emp
> TABLE empDelta),
> insertEmpDeptno AS (
> MERGE empDeptno AS e
> USING TABLE empDelta AS d
> ON e.deptno = d.deptno
> WHEN NOT MATCHED THEN INSERT VALUES (deptno));
> {code}
> h3. 2. Query that creates temporary table and uses it more than once
> {code:sql}
> WITH
> temp AS (
> SELECT *
> FROM emp AS e
> JOIN dept USING (deptno)
> WHERE e.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> SELECT deptno,
> (SELECT AVG(sal)
> FROM temp AS t
> WHERE t.deptno = e.deptno) AS deptAvgSal,
> (SELECT AVG(sal)
> FROM temp AS t
> WHERE t.job = e.job) AS jobAvgSal
> FROM e
> WHERE e.deptno IN (10, 20);
> {code}
> h3. 3. Query whose optimal plan might use a temporary table
> This query produces the same result as the previous query. There is a common
> relational expression, so the optimizer should consider a DAG plan with a
> reified intermediate result.
> {code:sql}
> SELECT deptno,
> (SELECT AVG(e2.sal)
> FROM emp AS e2
> JOIN dept AS d USING (deptno)
> WHERE (e2.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> AND e2.deptno = e.deptno) AS deptAvgSal,
> (SELECT AVG(e3.sal)
> FROM emp AS e3
> JOIN dept AS d USING (deptno)
> WHERE (e3.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> AND e3.job = e.job) AS jobAvgSal
> FROM e
> WHERE e.deptno IN (10, 20);
> {code}
> h3. 4. Query that produces several data sets
> {code:sql}
> WITH
> newOrders AS (
> SELECT *
> FROM orders
> WHERE orderDate > DATE '2023-01-25')
> MULTI
> invoices AS (
> SELECT customerName, SUM(amount)
> FROM newOrders
> GROUP BY customerName),
> restock AS (
> SELECT productId
> FROM inventory
> WHERE productId IN (
> SELECT productId FROM newOrders)
> AND itemsOnHand < 10);
> {code}
> h3. 5. Query with a complex DAG, multiple
[jira] [Commented] (CALCITE-6188) Multi-query optimization
[
https://issues.apache.org/jira/browse/CALCITE-6188?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803368#comment-17803368
]
Jay Narale commented on CALCITE-6188:
-
We in Presto have recently supported CTE materialization
[https://github.com/prestodb/presto/pull/20887.
|https://github.com/prestodb/presto/pull/20887]The implementation heavily uses
a new type of logical planNode called a sequence node. Though our current
implementation is not 100% optimized, we have a similar goal of optimizing all
DAGs together.
> Multi-query optimization
>
>
> Key: CALCITE-6188
> URL: https://issues.apache.org/jira/browse/CALCITE-6188
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Priority: Major
>
> Devise extensions to SQL so that queries with multiple input tables, multiple
> intermediate tables, and multiple output data sets (result sets and DML
> operations) can be defined in one SQL statement, optimized, and executed
> atomically.
> There are many flavors of multi-query optimization, depending on whether each
> occurrence of "multiple" in the previous paragraph is replaced with 0, 1 or
> "several". Our goal, here, is to allow them all to be expressed. We can then
> devise planning strategies that work for particular flavors.
> Examples of multi-queries:
> * {*}Multiple DML outputs{*}. An INSERT statement that writes into a table
> but also updates an index,
> * {*}Multiple DML outputs, complex intermediate tables{*}. A DAG that
> represents an ETL/ELT job;
> * {*}Multiple query outputs{*}. A query that produces several data sets (say
> a list of invoices for
> orders and a list of products that need to be restocked);
> * {*}DAG query{*}. A query that uses intermediate results more than once.
> See discussion in the [Multi-query optimization email
> thread|https://lists.apache.org/thread/mcdqwrtpx0os54t2nn9vtk17spkp5o5k].
> Here are some SQL examples.
> We add a new keyword {{MULTI}} that represents a statement whose output
> contains multiple data sets and DML operations, each with a unique name. For
> intermediate results, we use the existing {{WITH}} clause.
> h3. 1. Multi DML
> Read from one or more tables, write to one or more tables.
> An example is inserting into a table and also an index on that table
> (represented as a sorted table).
> {code:sql}
> WITH
> empDelta AS (
> SELECT * FROM emp2
> EXCEPT
> SELECT * FROM emp)
> MULTI
> insertEmp AS (
> INSERT INTO emp
> TABLE empDelta),
> insertEmpDeptno AS (
> MERGE empDeptno AS e
> USING TABLE empDelta AS d
> ON e.deptno = d.deptno
> WHEN NOT MATCHED THEN INSERT VALUES (deptno));
> {code}
> h3. 2. Query that creates temporary table and uses it more than once
> {code:sql}
> WITH
> temp AS (
> SELECT *
> FROM emp AS e
> JOIN dept USING (deptno)
> WHERE e.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> SELECT deptno,
> (SELECT AVG(sal)
> FROM temp AS t
> WHERE t.deptno = e.deptno) AS deptAvgSal,
> (SELECT AVG(sal)
> FROM temp AS t
> WHERE t.job = e.job) AS jobAvgSal
> FROM e
> WHERE e.deptno IN (10, 20);
> {code}
> h3. 3. Query that should create temporary table
> This query produces the same result as the previous query. There is a common
> relational expression, so the optimizer should consider a DAG plan.
> {code:sql}
> SELECT deptno,
> (SELECT AVG(e2.sal)
> FROM emp AS e2
> JOIN dept AS d USING (deptno)
> WHERE (e2.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> AND e2.deptno = e.deptno) AS deptAvgSal,
> (SELECT AVG(e3.sal)
> FROM emp AS e3
> JOIN dept AS d USING (deptno)
> WHERE (e3.job = 'MANAGER'
> OR d.location = 'CHICAGO')
> AND e3.job = e.job) AS jobAvgSal
> FROM e
> WHERE e.deptno IN (10, 20);
> {code}
> h3. 4. Query that produces several data sets
> {code:sql}
> WITH
> newOrders AS (
> SELECT *
> FROM orders
> WHERE orderDate > DATE '2023-01-25')
> MULTI
> invoices AS (
> SELECT customerName, SUM(amount)
> FROM newOrders
> GROUP BY customerName),
> restock AS (
> SELECT productId
> FROM inventory
> WHERE productId IN (
> SELECT productId FROM newOrders)
> AND itemsOnHand < 10);
> {code}
> h3. 5. Query with a complex DAG, multiple output data sets and one DML
> {code:sql}
> WITH
> t0 AS (
> SELECT * FROM t WHERE x > 5),
> t00 AS (
> SELECT * FROM t0 WHERE y < 10),
> t000 AS (
> SELECT x, MIN(y) FROM t00 GROUP BY x),
> t1 AS (
> SELECT * FROM t WHERE y > 3),
> t10 AS (
> SELECT * FROM t1 WHERE x < 8),
> t2 AS (
> SELECT * FROM t000
> INTERSECT
> SELECT * FROM t10),
> t3 AS (
> SELECT * FROM u
> INTERSECT
> SELECT * FROM t00)
> MULTI (
> q0 AS (SELECT * FROM t2),
> q1 AS (SELECT * FROM t3),
>
[jira] [Created] (CALCITE-6188) Multi-query optimization
Julian Hyde created CALCITE-6188:
Summary: Multi-query optimization
Key: CALCITE-6188
URL: https://issues.apache.org/jira/browse/CALCITE-6188
Project: Calcite
Issue Type: Improvement
Reporter: Julian Hyde
Devise extensions to SQL so that queries with multiple input tables, multiple
intermediate tables, and multiple output data sets (result sets and DML
operations) can be defined in one SQL statement, optimized, and executed
atomically.
There are many flavors of multi-query optimization, depending on whether each
occurrence of "multiple" in the previous paragraph is replaced with 0, 1 or
"several". Our goal, here, is to allow them all to be expressed. We can then
devise planning strategies that work for particular flavors.
Examples of multi-queries:
* {*}Multiple DML outputs{*}. An INSERT statement that writes into a table but
also updates an index,
* {*}Multiple DML outputs, complex intermediate tables{*}. A DAG that
represents an ETL/ELT job;
* {*}Multiple query outputs{*}. A query that produces several data sets (say a
list of invoices for
orders and a list of products that need to be restocked);
* {*}DAG query{*}. A query that uses intermediate results more than once.
See discussion in the [Multi-query optimization email
thread|https://lists.apache.org/thread/mcdqwrtpx0os54t2nn9vtk17spkp5o5k].
Here are some SQL examples.
We add a new keyword {{MULTI}} that represents a statement whose output
contains multiple data sets and DML operations, each with a unique name. For
intermediate results, we use the existing {{WITH}} clause.
h3. 1. Multi DML
Read from one or more tables, write to one or more tables.
An example is inserting into a table and also an index on that table
(represented as a sorted table).
{code:sql}
WITH
empDelta AS (
SELECT * FROM emp2
EXCEPT
SELECT * FROM emp)
MULTI
insertEmp AS (
INSERT INTO emp
TABLE empDelta),
insertEmpDeptno AS (
MERGE empDeptno AS e
USING TABLE empDelta AS d
ON e.deptno = d.deptno
WHEN NOT MATCHED THEN INSERT VALUES (deptno));
{code}
h3. 2. Query that creates temporary table and uses it more than once
{code:sql}
WITH
temp AS (
SELECT *
FROM emp AS e
JOIN dept USING (deptno)
WHERE e.job = 'MANAGER'
OR d.location = 'CHICAGO')
SELECT deptno,
(SELECT AVG(sal)
FROM temp AS t
WHERE t.deptno = e.deptno) AS deptAvgSal,
(SELECT AVG(sal)
FROM temp AS t
WHERE t.job = e.job) AS jobAvgSal
FROM e
WHERE e.deptno IN (10, 20);
{code}
h3. 3. Query that should create temporary table
This query produces the same result as the previous query. There is a common
relational expression, so the optimizer should consider a DAG plan.
{code:sql}
SELECT deptno,
(SELECT AVG(e2.sal)
FROM emp AS e2
JOIN dept AS d USING (deptno)
WHERE (e2.job = 'MANAGER'
OR d.location = 'CHICAGO')
AND e2.deptno = e.deptno) AS deptAvgSal,
(SELECT AVG(e3.sal)
FROM emp AS e3
JOIN dept AS d USING (deptno)
WHERE (e3.job = 'MANAGER'
OR d.location = 'CHICAGO')
AND e3.job = e.job) AS jobAvgSal
FROM e
WHERE e.deptno IN (10, 20);
{code}
h3. 4. Query that produces several data sets
{code:sql}
WITH
newOrders AS (
SELECT *
FROM orders
WHERE orderDate > DATE '2023-01-25')
MULTI
invoices AS (
SELECT customerName, SUM(amount)
FROM newOrders
GROUP BY customerName),
restock AS (
SELECT productId
FROM inventory
WHERE productId IN (
SELECT productId FROM newOrders)
AND itemsOnHand < 10);
{code}
h3. 5. Query with a complex DAG, multiple output data sets and one DML
{code:sql}
WITH
t0 AS (
SELECT * FROM t WHERE x > 5),
t00 AS (
SELECT * FROM t0 WHERE y < 10),
t000 AS (
SELECT x, MIN(y) FROM t00 GROUP BY x),
t1 AS (
SELECT * FROM t WHERE y > 3),
t10 AS (
SELECT * FROM t1 WHERE x < 8),
t2 AS (
SELECT * FROM t000
INTERSECT
SELECT * FROM t10),
t3 AS (
SELECT * FROM u
INTERSECT
SELECT * FROM t00)
MULTI (
q0 AS (SELECT * FROM t2),
q1 AS (SELECT * FROM t3),
d0 AS (DELETE FROM v WHERE v.x IN (SELECT x FROM t1))
{code}
The data flow is the following DAG:
{noformat}
u +
+--> t3 (q0)
+--> t0 --> t00 --+
| +--> t000 --+
t --+ +--> t2 (q1)
| +--> t10 ---+
+--> t1 --+
+--> d0
{noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6186) Enable DATEDIFF for Snowflake, map to BigQuery equivalents
[ https://issues.apache.org/jira/browse/CALCITE-6186?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803348#comment-17803348 ] Julian Hyde commented on CALCITE-6186: -- Yes, it's worth experimenting with stuff like that. Assuming we have good test coverage for RelToSql you could be agile - try creating a map, maybe it works for 80% of functions, and if later you find something that works for 90% of functions you can change the map. > Enable DATEDIFF for Snowflake, map to BigQuery equivalents > -- > > Key: CALCITE-6186 > URL: https://issues.apache.org/jira/browse/CALCITE-6186 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > > (Any suggestions for a better title would be helpful). > Snowflake supports the DATEDIFF function: > https://docs.snowflake.com/en/sql-reference/functions/datediff > This is equivalent to the BigQuery TIMESTAMP/TIME/DATE/DATETIME_DIFF > functions according to: > https://cloud.google.com/bigquery/docs/migration/snowflake-sql#date_and_time_functions > The operands are reorder but otherwise the functions operate similarly -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6186) Enable DATEDIFF for Snowflake, map to BigQuery equivalents
[ https://issues.apache.org/jira/browse/CALCITE-6186?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803331#comment-17803331 ] Tanner Clary commented on CALCITE-6186: --- [~julianhyde] Yes I agree a more systematic approach would be better. Especially because I plan on making more contributions that connect BQ and Snowflake operators. I think if we had a table that could store the mapping between operators as well as operand-types this could be really useful. For example, if the map had Enable DATEDIFF for Snowflake, map to BigQuery equivalents > -- > > Key: CALCITE-6186 > URL: https://issues.apache.org/jira/browse/CALCITE-6186 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > > (Any suggestions for a better title would be helpful). > Snowflake supports the DATEDIFF function: > https://docs.snowflake.com/en/sql-reference/functions/datediff > This is equivalent to the BigQuery TIMESTAMP/TIME/DATE/DATETIME_DIFF > functions according to: > https://cloud.google.com/bigquery/docs/migration/snowflake-sql#date_and_time_functions > The operands are reorder but otherwise the functions operate similarly -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6182) Add LENGTH/LEN function (enabled in Snowflake library)
[ https://issues.apache.org/jira/browse/CALCITE-6182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803321#comment-17803321 ] Julian Hyde edited comment on CALCITE-6182 at 1/4/24 8:53 PM: -- (I edited the above message because I force-pushed to fix the message of the previous commit to comply with CALCITE-6187.) was (Author: julianhyde): (I edited the above message because I force-pushed to fix the commit message to comply with CALCITE-6187.) > Add LENGTH/LEN function (enabled in Snowflake library) > -- > > Key: CALCITE-6182 > URL: https://issues.apache.org/jira/browse/CALCITE-6182 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > > Calcite already supports CHAR_LENGTH and LENGTH functions for the standard > library and BigQuery, respectively. > Snowflake also supports LENGTH and LEN as an alias as well. > The functions are identical aside from their names. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6182) Add LENGTH/LEN function (enabled in Snowflake library)
[ https://issues.apache.org/jira/browse/CALCITE-6182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803321#comment-17803321 ] Julian Hyde commented on CALCITE-6182: -- (I edited the above message because I force-pushed to fix the commit message to comply with CALCITE-6187.) > Add LENGTH/LEN function (enabled in Snowflake library) > -- > > Key: CALCITE-6182 > URL: https://issues.apache.org/jira/browse/CALCITE-6182 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > > Calcite already supports CHAR_LENGTH and LENGTH functions for the standard > library and BigQuery, respectively. > Snowflake also supports LENGTH and LEN as an alias as well. > The functions are identical aside from their names. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6182) Add LENGTH/LEN function (enabled in Snowflake library)
[ https://issues.apache.org/jira/browse/CALCITE-6182?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17802323#comment-17802323 ] Julian Hyde edited comment on CALCITE-6182 at 1/4/24 8:51 PM: -- Merged via [429609c0|https://github.com/apache/calcite/commit/429609c03ee5e781d4a9af0d45fdfad5509fdfba], thanks for the review, [~julianhyde]! was (Author: JIRAUSER298151): Merged via [88a4150|https://github.com/apache/calcite/commit/88a415032c0c54bba18d192a4333f56b1da240c1], thanks for the review, [~julianhyde]! > Add LENGTH/LEN function (enabled in Snowflake library) > -- > > Key: CALCITE-6182 > URL: https://issues.apache.org/jira/browse/CALCITE-6182 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > > Calcite already supports CHAR_LENGTH and LENGTH functions for the standard > library and BigQuery, respectively. > Snowflake also supports LENGTH and LEN as an alias as well. > The functions are identical aside from their names. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-6187) Linter should disallow tags such as '[MINOR]' in commit messages
[ https://issues.apache.org/jira/browse/CALCITE-6187?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde resolved CALCITE-6187. -- Fix Version/s: 1.37.0 Resolution: Fixed Fixed in [3c19347c|https://github.com/apache/calcite/commit/3c19347cc45349a21a8c97d6f6e8d3e9f596070f]. > Linter should disallow tags such as '[MINOR]' in commit messages > > > Key: CALCITE-6187 > URL: https://issues.apache.org/jira/browse/CALCITE-6187 > Project: Calcite > Issue Type: Improvement >Reporter: Julian Hyde >Priority: Major > Fix For: 1.37.0 > > > Linter should disallow tags such as '[MINOR]' in commit messages. Such tags > are still allowed if not at the start of the message, and of course Jira > cases such as '[CALCITE-5765] Add LintTest' are allowed. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6186) Enable DATEDIFF for Snowflake, map to BigQuery equivalents
[ https://issues.apache.org/jira/browse/CALCITE-6186?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803314#comment-17803314 ] Julian Hyde commented on CALCITE-6186: -- Does this change cover incoming SQL (i.e. SqlLibraryOperators) or outgoing SQL (RelToSql), or both? It's probably better that commits do in or out, not both. On the 'out' side, I wish we had a more systematic approach (e.g. a mapping table). Or maybe we need a bidirectional mapping that is not tied to 'in' or 'out'. > Enable DATEDIFF for Snowflake, map to BigQuery equivalents > -- > > Key: CALCITE-6186 > URL: https://issues.apache.org/jira/browse/CALCITE-6186 > Project: Calcite > Issue Type: Improvement >Reporter: Tanner Clary >Assignee: Tanner Clary >Priority: Major > > (Any suggestions for a better title would be helpful). > Snowflake supports the DATEDIFF function: > https://docs.snowflake.com/en/sql-reference/functions/datediff > This is equivalent to the BigQuery TIMESTAMP/TIME/DATE/DATETIME_DIFF > functions according to: > https://cloud.google.com/bigquery/docs/migration/snowflake-sql#date_and_time_functions > The operands are reorder but otherwise the functions operate similarly -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6187) Linter should disallow tags such as '[MINOR]' in commit messages
Julian Hyde created CALCITE-6187: Summary: Linter should disallow tags such as '[MINOR]' in commit messages Key: CALCITE-6187 URL: https://issues.apache.org/jira/browse/CALCITE-6187 Project: Calcite Issue Type: Improvement Reporter: Julian Hyde Linter should disallow tags such as '[MINOR]' in commit messages. Such tags are still allowed if not at the start of the message, and of course Jira cases such as '[CALCITE-5765] Add LintTest' are allowed. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6186) Enable DATEDIFF for Snowflake, map to BigQuery equivalents
Tanner Clary created CALCITE-6186: - Summary: Enable DATEDIFF for Snowflake, map to BigQuery equivalents Key: CALCITE-6186 URL: https://issues.apache.org/jira/browse/CALCITE-6186 Project: Calcite Issue Type: Improvement Reporter: Tanner Clary Assignee: Tanner Clary (Any suggestions for a better title would be helpful). Snowflake supports the DATEDIFF function: https://docs.snowflake.com/en/sql-reference/functions/datediff This is equivalent to the BigQuery TIMESTAMP/TIME/DATE/DATETIME_DIFF functions according to: https://cloud.google.com/bigquery/docs/migration/snowflake-sql#date_and_time_functions The operands are reorder but otherwise the functions operate similarly -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6185) Support more date format
[ https://issues.apache.org/jira/browse/CALCITE-6185?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803261#comment-17803261 ] Julian Hyde edited comment on CALCITE-6185 at 1/4/24 7:01 PM: -- The jira subject must describe the feature, i.e. what the user can do that they could not previously. When I review a PR, I ask "Does this PR deliver (and test) what is promised in the Jira subject?" and the current subject "Support more data format" is not specific enough to answer that question. If you're adding support for timezone, there is a serious question of what even is the timezone of, say, a TIMESTAMP value. was (Author: julianhyde): The jira subject must describe the feature, i.e. what the user can do that they could not previously. If you're adding support for timezone, there is a serious question of what even is the timezone of, say, a TIMESTAMP value. > Support more date format > > > Key: CALCITE-6185 > URL: https://issues.apache.org/jira/browse/CALCITE-6185 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > I am trying to support more date formats based on BigQuery and Postgres > documents, and improve some Todo items in FormatElementEnum. > [https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements] > https://www.postgresql.org/docs/current/functions-formatting.html -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6185) Support more date format
[ https://issues.apache.org/jira/browse/CALCITE-6185?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803261#comment-17803261 ] Julian Hyde edited comment on CALCITE-6185 at 1/4/24 6:58 PM: -- The jira subject must describe the feature, i.e. what the user can do that they could not previously. If you're adding support for timezone, there is a serious question of what even is the timezone of, say, a TIMESTAMP value. was (Author: julianhyde): The jira subject must describe the feature, i.e. what the user can do that they could not previously. > Support more date format > > > Key: CALCITE-6185 > URL: https://issues.apache.org/jira/browse/CALCITE-6185 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > I am trying to support more date formats based on BigQuery and Postgres > documents, and improve some Todo items in FormatElementEnum. > [https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements] > https://www.postgresql.org/docs/current/functions-formatting.html -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6185) Support more date format
[ https://issues.apache.org/jira/browse/CALCITE-6185?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17803261#comment-17803261 ] Julian Hyde commented on CALCITE-6185: -- The jira subject must describe the feature, i.e. what the user can do that they could not previously. > Support more date format > > > Key: CALCITE-6185 > URL: https://issues.apache.org/jira/browse/CALCITE-6185 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > I am trying to support more date formats based on BigQuery and Postgres > documents, and improve some Todo items in FormatElementEnum. > [https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements] > https://www.postgresql.org/docs/current/functions-formatting.html -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6185) Support more date format
[ https://issues.apache.org/jira/browse/CALCITE-6185?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-6185: Labels: pull-request-available (was: ) > Support more date format > > > Key: CALCITE-6185 > URL: https://issues.apache.org/jira/browse/CALCITE-6185 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > I am trying to support more date formats based on BigQuery and Postgres > documents, and improve some Todo items in FormatElementEnum. > [https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements] > https://www.postgresql.org/docs/current/functions-formatting.html -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6185) Support more date format
Caican Cai created CALCITE-6185: --- Summary: Support more date format Key: CALCITE-6185 URL: https://issues.apache.org/jira/browse/CALCITE-6185 Project: Calcite Issue Type: Improvement Components: core Affects Versions: 1.36.0 Reporter: Caican Cai Fix For: 1.37.0 I am trying to support more date formats based on BigQuery and Postgres documents, and improve some Todo items in FormatElementEnum. [https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements] https://www.postgresql.org/docs/current/functions-formatting.html -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6184) Add checkNullTest on SqlOperatorTest
[ https://issues.apache.org/jira/browse/CALCITE-6184?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17802846#comment-17802846 ] Caican Cai edited comment on CALCITE-6184 at 1/4/24 10:46 AM: -- I will summarize and sort out those checknulls that should be added with checknull test later. # It is necessary to add a checknull test to datefunction because bugs may be hidden here e.g. [[MINOR] Add checkNull Test on SqlOperatorTest by caicancai · Pull Request #3596 · apache/calcite (github.com)|https://github.com/apache/calcite/pull/3596] # I tried adding a checknull test to the Map, but it seems unnecessary was (Author: JIRAUSER302115): I will summarize and sort out those checknulls that should be added with checknull test later. # It is necessary to add a checknull test to datefunction because bugs may be hidden here e.g[[MINOR] Add checkNull Test on SqlOperatorTest · apache/calcite@6e7b05d (github.com)|https://github.com/apache/calcite/commit/6e7b05d7767381c9928fea7caed6dcb08b54a63f] # I tried adding a checknull test to the Map, but it seems unnecessary > Add checkNullTest on SqlOperatorTest > > > Key: CALCITE-6184 > URL: https://issues.apache.org/jira/browse/CALCITE-6184 > Project: Calcite > Issue Type: Test > Components: tests >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Some tests in calcite's SqlOperatorTest lack checknull. I think the checknull > test should be added. > 1. Conducive to the learning of new developers > 2. Based on the checknull test, we may be able to find some bugs in calcite’s > sqoperator. > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6184) Add checkNullTest on SqlOperatorTest
[ https://issues.apache.org/jira/browse/CALCITE-6184?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17802846#comment-17802846 ] Caican Cai edited comment on CALCITE-6184 at 1/4/24 10:35 AM: -- I will summarize and sort out those checknulls that should be added with checknull test later. # It is necessary to add a checknull test to datefunction because bugs may be hidden here e.g[[MINOR] Add checkNull Test on SqlOperatorTest · apache/calcite@6e7b05d (github.com)|https://github.com/apache/calcite/commit/6e7b05d7767381c9928fea7caed6dcb08b54a63f] # I tried adding a checknull test to the Map, but it seems unnecessary was (Author: JIRAUSER302115): I will summarize and sort out those checknulls that should be added with checknull test later. # It is necessary to add a checknull test to datefunction because bugs may be hidden here e.g[[MINOR] Add checkNull Test on SqlOperatorTest · apache/calcite@6e7b05d (github.com)|https://github.com/apache/calcite/commit/6e7b05d7767381c9928fea7caed6dcb08b54a63f] # > Add checkNullTest on SqlOperatorTest > > > Key: CALCITE-6184 > URL: https://issues.apache.org/jira/browse/CALCITE-6184 > Project: Calcite > Issue Type: Test > Components: tests >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Some tests in calcite's SqlOperatorTest lack checknull. I think the checknull > test should be added. > 1. Conducive to the learning of new developers > 2. Based on the checknull test, we may be able to find some bugs in calcite’s > sqoperator. > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6184) Add checkNullTest on SqlOperatorTest
[ https://issues.apache.org/jira/browse/CALCITE-6184?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] ASF GitHub Bot updated CALCITE-6184: Labels: pull-request-available (was: ) > Add checkNullTest on SqlOperatorTest > > > Key: CALCITE-6184 > URL: https://issues.apache.org/jira/browse/CALCITE-6184 > Project: Calcite > Issue Type: Test > Components: tests >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Some tests in calcite's SqlOperatorTest lack checknull. I think the checknull > test should be added. > 1. Conducive to the learning of new developers > 2. Based on the checknull test, we may be able to find some bugs in calcite’s > sqoperator. > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6184) Add checkNullTest on SqlOperatorTest
[ https://issues.apache.org/jira/browse/CALCITE-6184?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17802846#comment-17802846 ] Caican Cai edited comment on CALCITE-6184 at 1/4/24 10:13 AM: -- I will summarize and sort out those checknulls that should be added with checknull test later. # It is necessary to add a checknull test to datefunction because bugs may be hidden here e.g[[MINOR] Add checkNull Test on SqlOperatorTest · apache/calcite@6e7b05d (github.com)|https://github.com/apache/calcite/commit/6e7b05d7767381c9928fea7caed6dcb08b54a63f] # was (Author: JIRAUSER302115): I will summarize and sort out those checknulls that should be added with checknull test later. > Add checkNullTest on SqlOperatorTest > > > Key: CALCITE-6184 > URL: https://issues.apache.org/jira/browse/CALCITE-6184 > Project: Calcite > Issue Type: Test > Components: tests >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > Fix For: 1.37.0 > > > Some tests in calcite's SqlOperatorTest lack checknull. I think the checknull > test should be added. > 1. Conducive to the learning of new developers > 2. Based on the checknull test, we may be able to find some bugs in calcite’s > sqoperator. > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6184) Add checkNullTest on SqlOperatorTest
[ https://issues.apache.org/jira/browse/CALCITE-6184?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17802846#comment-17802846 ] Caican Cai commented on CALCITE-6184: - I will summarize and sort out those checknulls that should be added with checknull test later. > Add checkNullTest on SqlOperatorTest > > > Key: CALCITE-6184 > URL: https://issues.apache.org/jira/browse/CALCITE-6184 > Project: Calcite > Issue Type: Test > Components: tests >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > Fix For: 1.37.0 > > > Some tests in calcite's SqlOperatorTest lack checknull. I think the checknull > test should be added. > 1. Conducive to the learning of new developers > 2. Based on the checknull test, we may be able to find some bugs in calcite’s > sqoperator. > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-6184) Add checkNullTest on SqlOperatorTest
Caican Cai created CALCITE-6184: --- Summary: Add checkNullTest on SqlOperatorTest Key: CALCITE-6184 URL: https://issues.apache.org/jira/browse/CALCITE-6184 Project: Calcite Issue Type: Test Components: tests Affects Versions: 1.36.0 Reporter: Caican Cai Fix For: 1.37.0 Some tests in calcite's SqlOperatorTest lack checknull. I think the checknull test should be added. 1. Conducive to the learning of new developers 2. Based on the checknull test, we may be able to find some bugs in calcite’s sqoperator. -- This message was sent by Atlassian Jira (v8.20.10#820010)
