[jira] [Created] (CALCITE-5894) Add SortRemoveRedundantRule to remove redundant sort fields if sort fields contains unique key

[JingDas (Jira)]

JingDas created CALCITE-5894:


 Summary: Add SortRemoveRedundantRule to remove redundant sort 
fields if sort fields contains unique key
 Key: CALCITE-5894
 URL: https://issues.apache.org/jira/browse/CALCITE-5894
 Project: Calcite
  Issue Type: New Feature
Reporter: JingDas
Assignee: JingDas


In some scene, Sort fields can be reduct, if sort fields contain unique key

For Example
{code:java}
SELECT name, Emp.salary FROM Emp
order by empno, ename{code}
where `empno` is a key,  `ename` is redundant since `empno` alone is sufficient 
to determine the order of any two records.

So the SQL can be optimized as following:
{code:java}
SELECT name, Emp.salary FROM Emp
order by empno{code}
Sorting is an expensive operation, however. Therefore, it is imperative that 
sorting
is optimized to avoid unnecessary sort field.

 



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

Re: AggregateProjectPullUpConstantsRule not working with the Volcano Planner

[Nick Riasanovsky]

Thank you for your response. There was an issue with my configuration that
I was able to resolve.

On Thu, Aug 3, 2023 at 4:33 PM Julian Hyde  wrote:

> The constraints on a RelSubset should be the union of the constraints of
> all of the RelNodes in that subset. (I haven’t tested it, or read the code.
> But if it doesn’t do that, you’re probably seeing a bug, or you haven’r
> configured your metadata providers correctly.)
>
> > On Aug 3, 2023, at 9:33 AM, Nick Riasanovsky  wrote:
> >
> > Hello everyone,
> >
> > I am attempting to use AggregateProjectPullUpConstantsRule within an
> > optimizer that is using the VolcanoPlanner. When doing so I encounter
> > issues with what should be valid constants on the line
> > `mq.getPulledUpPredicates()` because aggregate.getInput() is a RelSubset.
> > As a result, the predicates list is always empty. If I replace the uses
> of
> > aggregate.getInput() with input then everything works as expected.
> >
> > I am happy to submit a PR upstream with this fix, but I first want to
> > confirm that my interpretation is correct as I am relatively new to using
> > the volcano planner and could have missed a configuration step.
> >
> > Thanks everyone,
> > Nick Riasanovsky
>
>

DECIMAL(2, 3) meaning

[mbudiu]

Hello,

 

I notice that Calcite happily accepts decimal type specifications where the
scale is greater than the precision.

There are quite a few tests with such types.

 

What is the meaning of such types?

 

The SQL 92 standard has this statement on page 109:

 

15)The  of an  shall not be greater than

the  of the .

 

Thank you,

Mihai

Re: AggregateProjectPullUpConstantsRule not working with the Volcano Planner

[Julian Hyde]

The constraints on a RelSubset should be the union of the constraints of all of 
the RelNodes in that subset. (I haven’t tested it, or read the code. But if it 
doesn’t do that, you’re probably seeing a bug, or you haven’r configured your 
metadata providers correctly.)

> On Aug 3, 2023, at 9:33 AM, Nick Riasanovsky  wrote:
> 
> Hello everyone,
> 
> I am attempting to use AggregateProjectPullUpConstantsRule within an
> optimizer that is using the VolcanoPlanner. When doing so I encounter
> issues with what should be valid constants on the line
> `mq.getPulledUpPredicates()` because aggregate.getInput() is a RelSubset.
> As a result, the predicates list is always empty. If I replace the uses of
> aggregate.getInput() with input then everything works as expected.
> 
> I am happy to submit a PR upstream with this fix, but I first want to
> confirm that my interpretation is correct as I am relatively new to using
> the volcano planner and could have missed a configuration step.
> 
> Thanks everyone,
> Nick Riasanovsky

AggregateProjectPullUpConstantsRule not working with the Volcano Planner

[Nick Riasanovsky]

Hello everyone,

I am attempting to use AggregateProjectPullUpConstantsRule within an
optimizer that is using the VolcanoPlanner. When doing so I encounter
issues with what should be valid constants on the line
`mq.getPulledUpPredicates()` because aggregate.getInput() is a RelSubset.
As a result, the predicates list is always empty. If I replace the uses of
aggregate.getInput() with input then everything works as expected.

I am happy to submit a PR upstream with this fix, but I first want to
confirm that my interpretation is correct as I am relatively new to using
the volcano planner and could have missed a configuration step.

Thanks everyone,
Nick Riasanovsky

Re: Easier and more comprehensive testing

[Stamatis Zampetakis]

Hey Mihai,

Indeed the .iq files are mostly end-to-end tests in Calcite. The
Quidem [1] framework is used for running these tests. The Quidem
approach is pretty neat cause we don't have to carry Java boilerplate
code and formatting around which makes the tests much easier to read,
write, inspect history, and copy-paste across projects. Separating
SQL/DDL/DML files in separate non-java files is used in other projects
as well. The most relevant example that comes to mind is Apache Hive
that has ~5K .q files (see cast4.q [2] for an example) and its own
test framework for running those.

End-to-end tests are great but so are unit tests. One does not replace
the other and both are needed in a project. End-to-end tests are much
slower than unit tests so if we have too many of them we will not be
able to run all of them during the build. Again Hive is nice example
on this front cause due to the big number of end-to-end tests
developers cannot run everything locally (it requires more than 24
hours). As a result, lots of PRs are created by running just a few
tests locally and then waiting for the CI to finish. If test failures
are detected, new commits must be pushed, and the whole process is
repeated; the whole cycle takes several days. Unit tests are there to
catch as many problems as possible early on and save us from this
lengthy back and forth.

The end-to-end tests in Calcite may be few compared to other projects
but we should take into account that Calcite is not a complete DBMS.
DBMS that are built using Calcite tend to have many more end-to-end
tests and they are essentially testing Calcite as well.

To conclude, I am strongly in favor of adding new tests especially if
they help to uncover new bugs and problems in the project. For
end-to-end tests, I would probably invest on Quidem (and .iq files) as
far as it concerns Calcite and I would always push for unit tests when
applicable.

Best,
Stamatis

[1] https://github.com/julianhyde/quidem
[2] 
https://github.com/apache/hive/blob/0447b88d8308be669eb9102637a65736ed2bfccf/ql/src/test/queries/clientpositive/cast4.q

On Sat, Jul 29, 2023 at 5:04 AM LakeShen  wrote:
>
> Hi mbudiu,thank you for bringing SqlLogicTest to calcite.
> Now we're working on an optimizer based on Calcite that supports Postgresql
> semantics as a whole. I am very interested in your bringing the Postgres
> test to Calcite and would like to participate in it. Could you please give
> me a complete collection of reference materials?
> The current document information is scattered, some in sql-logic-test
> github and some in sql-to-dbsp-compiler github, which requires everyone to
> read on github by themselves. If you could give me a complete end-to-end
> demo of adding tests, I would really appreciate it. Best, LakeShen
>
>  于2023年7月29日周六 01:04写道：
>
> > The correct link is
> > https://github.com/feldera/dbsp/tree/main/sql-to-dbsp-compiler, but it's
> > not particularly important, I apologize for the broken one.
> >
> > I appreciate you pointing out the Sql Logic Test project, I wrote that
> > code too. But these two projects are almost entirely disjoint. SLT brings a
> > few million pre-written tests. But none of the SLT tests exercises any of
> > the SQL functions. So you need to write many more tests for each *function*
> > implemented, and most contributions to Calcite lately are functions in
> > different dialects.
> >
> > Moreover, while SLT found quite a few bugs, I only had time to file one of
> > them so far. For each failing test I have to figure out (1) whether it's a
> > duplicate, (2) to create a minimal reproduction, (3) to figure out which
> > part of the compiler is faulty in order to understand where to insert the
> > test, (4) create a reproduction tailored for the faulty  component.
> > Reproductions for planner bugs are different than reproductions for library
> > bugs. But some bugs happen only if you combine some planner rules with some
> > functions.
> >
> > With the proposal below you only need a CLI to a database to generate new
> > tests (e.g., BigQuery): you execute write the queries in the CLI, then
> > copy-paste the output into a test. This makes it easier to file bugs, but
> > harder to diagnose them. But it also makes it much easier to write lots of
> > tests, because it enables people without Calcite expertise to write the
> > tests.
> >
> > Mihai
> >
> > -Original Message-
> > From: stanilovsky evgeny
> > Sent: Friday, July 28, 2023 2:43 AM
> > To: dev@calcite.apache.org
> > Subject: Re: Easier and more comprehensive testing
> >
> > Hello, your github link doesn`t open.
> > plz check discussion here in dev list titled:
> >
> > Running Sql Logic Tests for Calcite
> > This is the JIRA case: https://issues.apache.org/jira/browse/CALCITE-5615
> > And this is the PR: https://github.com/apache/calcite/pull/3145
> >
> >
> > > Hello,
> > >
> > >
> > > I am working to test our calcite-based compiler
> > > (https://github.com/feldera/dbsp/sql-to-dbsp-compiler), 

Re: [Question] CAST possibility cases

[stanilovsky evgeny]

Thanks a lot for response.
Ok i found partial CAST tests like :  
SqlOperatorTest#testCastBooleanToNumeric, SqlOperatorTest#testCastToString  
and so on.

Soon i will try to fix all observed CAST discrepancy according to:

ISO/IEC 9075-2:1999
6.22  standard



Hi,stanilovsky, CALCITE-5662
 CAST(BOOLEAN as
INTEGER) throws a NumberFormatException. Maybe you can get some  
information

from this JIRA. Best, LakeShen

Julian Hyde  于2023年8月2日周三 23:04写道：


I think there’s at least one jira case discussing Boolean cast. Can you
locate it?

> On Aug 2, 2023, at 5:46 AM, stanilovsky evgeny <
estanilovs...@gridgain.com> wrote:
>
> hello all !
> According to sql standard, chapter: 6.22 
> We can found, for example, that casting from BOOLEAN is deprecated  
into

Exact Numeric, but according to SqlTypeCoercionRule
> it`s possible for now. Probably i miss something, can someone  
highlight

me in such a case? If no - did i need to fill the appropriate issue ?
> Also i can`t found sufficient CAST tests (
>
> thanks !