Need help in calcite materialized view.

[udit varshney]

Hi Team,

I am trying to use calcite for maintaining materialized views. I am facing
some issues while rewriting sql. I am using volcano planner and initiating
calcite through following ways:
1. Initiating volcano planner then adding materialized rules.
2. Adding materialized views in planner.
3. Rewriting sql: After calling convertQuery(), I am calling decorrelate()
and trimUnusedFields() and then findBestExp().

For some queries rewriting is not happening. Here are the following
scenarios:
1.  If I am not calling trimUnusedFields() then the following cases are not
working.
  Table: create table (id int, c1 int, c2 int);
  MV SQL: select id , c1 from t1 where id < 10;
  case 1: select c1, id from t1 where id < 10;
2. If I am calling trimUnusedFields() then some of the tpcds are not
working. Tpcds query is attached in mail.

I do not know much about calcite internals so I am not able to understand
why it is behaving like this. Let me know how I can resolve this.


Thanks,
Udit Kumar
 QUERY: TPCDS-Q11
with year_total as (
 select c_customer_id customer_id
   ,c_first_name customer_first_name
   ,c_last_name customer_last_name
   ,c_preferred_cust_flag customer_preferred_cust_flag
   ,c_birth_country customer_birth_country
   ,c_login customer_login
   ,c_email_address customer_email_address
   ,d_year dyear
   ,sum(ss_ext_list_price-ss_ext_discount_amt) year_total
   ,'s' sale_type
 from customer
 ,store_sales
 ,date_dim
 where c_customer_sk = ss_customer_sk
   and ss_sold_date_sk = d_date_sk
 group by c_customer_id
 ,c_first_name
 ,c_last_name
 ,c_preferred_cust_flag
 ,c_birth_country
 ,c_login
 ,c_email_address
 ,d_year
 union all
 select c_customer_id customer_id
   ,c_first_name customer_first_name
   ,c_last_name customer_last_name
   ,c_preferred_cust_flag customer_preferred_cust_flag
   ,c_birth_country customer_birth_country
   ,c_login customer_login
   ,c_email_address customer_email_address
   ,d_year dyear
   ,sum(ws_ext_list_price-ws_ext_discount_amt) year_total
   ,'w' sale_type
 from customer
 ,web_sales
 ,date_dim
 where c_customer_sk = ws_bill_customer_sk
   and ws_sold_date_sk = d_date_sk
 group by c_customer_id
 ,c_first_name
 ,c_last_name
 ,c_preferred_cust_flag
 ,c_birth_country
 ,c_login
 ,c_email_address
 ,d_year
 )
  select
  t_s_secyear.customer_id
 ,t_s_secyear.customer_first_name
 ,t_s_secyear.customer_last_name
 ,t_s_secyear.customer_preferred_cust_flag
 from year_total t_s_firstyear
 ,year_total t_s_secyear
 ,year_total t_w_firstyear
 ,year_total t_w_secyear
 where t_s_secyear.customer_id = t_s_firstyear.customer_id
 and t_s_firstyear.customer_id = t_w_secyear.customer_id
 and t_s_firstyear.customer_id = t_w_firstyear.customer_id
 and t_s_firstyear.sale_type = 's'
 and t_w_firstyear.sale_type = 'w'
 and t_s_secyear.sale_type = 's'
 and t_w_secyear.sale_type = 'w'
 and t_s_firstyear.dyear = 2001
 and t_s_secyear.dyear = 2001+1
 and t_w_firstyear.dyear = 2001
 and t_w_secyear.dyear = 2001+1
 and t_s_firstyear.year_total > 0
 and t_w_firstyear.year_total > 0
 and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total 
/ t_w_firstyear.year_total else 0.0 end
 > case when t_s_firstyear.year_total > 0 then 
t_s_secyear.year_total / t_s_firstyear.year_total else 0.0 end
 order by t_s_secyear.customer_id
 ,t_s_secyear.customer_first_name
 ,t_s_secyear.customer_last_name
 ,t_s_secyear.customer_preferred_cust_flag
limit 100;



MV SQL:
select c_customer_id customer_id
   ,c_first_name customer_first_name
   ,c_last_name customer_last_name
   ,c_preferred_cust_flag customer_preferred_cust_flag
   ,c_birth_country customer_birth_country
   ,c_login customer_login
   ,c_email_address customer_email_address
   ,d_year dyear
   ,sum(ss_ext_list_price-ss_ext_discount_amt) year_total
   ,'s' sale_type
 from customer
 ,store_sales
 ,date_dim
 where c_customer_sk = ss_customer_sk
   and ss_sold_date_sk = d_date_sk
 group by c_customer_id
 ,c_first_name
 ,c_last_name
 ,c_preferred_cust_flag
 ,c_birth_country
 ,c_login
 ,c_email_address
 ,d_year
 union all
 select c_customer_id customer_id
   ,c_first_name customer_first_name
   ,c_last_name customer_last_name
   ,c_preferred_cust_flag customer_preferred_cust_flag
   ,c_birth_country customer_birth_country
   ,c_login customer_login
   ,c_email_address customer_email_address
   ,d_year dyear
   ,sum(ws_ext_list_price-ws_ext_discount_amt) year_total
   ,'w' sale_type
 from 

Re: PR Review Request

[Julian Hyde]

Martijn,

Since you requested a reply, I am replying. To answer your question, I don’t 
know of a way to move this topic forward. We have more PRs than people to 
review them.

Julian


> On Jun 19, 2022, at 11:58 PM, Martijn Visser  wrote:
> 
> Hi everyone,
> 
> I just wanted to reach out to the Calcite community once more on this topic
> since no reply was received. Would be great if someone could get back to us.
> 
> Best regards,
> 
> Martijn
> 
> Op wo 8 jun. 2022 om 11:24 schreef Martijn Visser > :
> 
>> Hi everyone,
>> 
>> I would like to follow-up on this email that was sent by Jing. So far, no
>> progress has been made, despite reaching out to the mailing list, the
>> original Jira ticket and reaching out to people directly. Is there a way
>> that we can move this PR/topic forward?
>> 
>> For context, in Apache Flink we're currently heavily using Calcite.
>> However, we are now at the stage where Calcite is actually holding us back.
>> It would be great if we can find a way to strengthen our bond and move both
>> Calcite and Flink forward.
>> 
>> Looking forward to your thoughts,
>> 
>> Martijn
>> 
>> On 2022/01/26 07:05:37 Jing Zhang wrote:
>>> Hi community,
>>> My apologies for interrupting.
>>> Anyone could help to review the pr
>>> https://github.com/apache/calcite/pull/2606?
>>> Thanks a lot.
>>> 
>>> CALCITE-4865 is the first sub-task of CALCITE-4864. This Jira aims to
>>> extend existing Table function in order to support Polymorphic Table
>>> Function which is introduced as the part of ANSI SQL 2016.
>>> 
>>> The brief change logs of the PR are:
>>>  - Update `Parser.jj` to support partition by clause and order by clause
>>> for input table with set semantics of PTF
>>>  - Introduce `TableCharacteristics` which contains three characteristics
>>> of input table of table function
>>>  - Update `SqlTableFunction` to add a method `tableCharacteristics`,
>> the
>>> method returns the table characteristics for the ordinal-th argument to
>>> this table function. Default return value is Optional.empty which means
>> the
>>> ordinal-th argument is not table.
>>>  - Introduce `SqlSetSemanticsTable` which represents input table with
>> set
>>> semantics of Table Function, its `SqlKind` is `SET_SEMANTICS_TABLE`
>>>  - Updates `SqlValidatorImpl` to validate only set semantic table of
>> Table
>>> Function could have partition by and order by clause
>>>  - Update `SqlToRelConverter#substituteSubQuery` to parse subQuery which
>>> represents set semantics table.
>>> 
>>> PR: https://github.com/apache/calcite/pull/2606
>>> JIRA: https://issues.apache.org/jira/browse/CALCITE-4865
>>> Parent JARA: https://issues.apache.org/jira/browse/CALCITE-4864
>>> 
>>> Best,
>>> Jing Zhang
>>> 
>> 

Re: PR Review Request

[Austin Bennett]

>From the peanut gallery :-)  -->

Wow; yes, lots of open PRs.  https://github.com/apache/calcite/pulls

How can individuals from the Flink [sub-]community, and/or more general
calcite community help lighten this load?  Is there much weight given to
reviews from non-committers; how to increase the # of people capable of
providing worthwhile reviews [ that are recognized as such ]?



On Mon, Jun 20, 2022 at 11:47 AM Julian Hyde  wrote:

> Martijn,
>
> Since you requested a reply, I am replying. To answer your question, I
> don’t know of a way to move this topic forward. We have more PRs than
> people to review them.
>
> Julian
>
>
> > On Jun 19, 2022, at 11:58 PM, Martijn Visser 
> wrote:
> >
> > Hi everyone,
> >
> > I just wanted to reach out to the Calcite community once more on this
> topic
> > since no reply was received. Would be great if someone could get back to
> us.
> >
> > Best regards,
> >
> > Martijn
> >
> > Op wo 8 jun. 2022 om 11:24 schreef Martijn Visser <
> martijnvis...@apache.org
> >> :
> >
> >> Hi everyone,
> >>
> >> I would like to follow-up on this email that was sent by Jing. So far,
> no
> >> progress has been made, despite reaching out to the mailing list, the
> >> original Jira ticket and reaching out to people directly. Is there a way
> >> that we can move this PR/topic forward?
> >>
> >> For context, in Apache Flink we're currently heavily using Calcite.
> >> However, we are now at the stage where Calcite is actually holding us
> back.
> >> It would be great if we can find a way to strengthen our bond and move
> both
> >> Calcite and Flink forward.
> >>
> >> Looking forward to your thoughts,
> >>
> >> Martijn
> >>
> >> On 2022/01/26 07:05:37 Jing Zhang wrote:
> >>> Hi community,
> >>> My apologies for interrupting.
> >>> Anyone could help to review the pr
> >>> https://github.com/apache/calcite/pull/2606?
> >>> Thanks a lot.
> >>>
> >>> CALCITE-4865 is the first sub-task of CALCITE-4864. This Jira aims to
> >>> extend existing Table function in order to support Polymorphic Table
> >>> Function which is introduced as the part of ANSI SQL 2016.
> >>>
> >>> The brief change logs of the PR are:
> >>>  - Update `Parser.jj` to support partition by clause and order by
> clause
> >>> for input table with set semantics of PTF
> >>>  - Introduce `TableCharacteristics` which contains three
> characteristics
> >>> of input table of table function
> >>>  - Update `SqlTableFunction` to add a method `tableCharacteristics`,
> >> the
> >>> method returns the table characteristics for the ordinal-th argument to
> >>> this table function. Default return value is Optional.empty which means
> >> the
> >>> ordinal-th argument is not table.
> >>>  - Introduce `SqlSetSemanticsTable` which represents input table with
> >> set
> >>> semantics of Table Function, its `SqlKind` is `SET_SEMANTICS_TABLE`
> >>>  - Updates `SqlValidatorImpl` to validate only set semantic table of
> >> Table
> >>> Function could have partition by and order by clause
> >>>  - Update `SqlToRelConverter#substituteSubQuery` to parse subQuery
> which
> >>> represents set semantics table.
> >>>
> >>> PR: https://github.com/apache/calcite/pull/2606
> >>> JIRA: https://issues.apache.org/jira/browse/CALCITE-4865
> >>> Parent JARA: https://issues.apache.org/jira/browse/CALCITE-4864
> >>>
> >>> Best,
> >>> Jing Zhang
> >>>
> >>
>
>

Re: [DISCUSS] Towards Calcite 1.31.0

[Julian Hyde]

I agree that it’s a regression, and caused by my CALCITE-35 change. I’ve 
assigned the bug to myself. I’m not sure I have time to fix it this week, so we 
can back out my change if necessary to get the release out on schedule. If 
someone else can fix it I would be grateful.


> On Jun 19, 2022, at 2:26 AM, Vova Vysotskyi  wrote:
> 
> Hello,
> 
> I have found the following regression while verifying the new release to work 
> with Apache Drill: https://issues.apache.org/jira/browse/CALCITE-5194.
> This issue appeared after changes for 
> https://issues.apache.org/jira/browse/CALCITE-35.
> Since it is a regression, I suggest treating it as a blocker for the upcoming 
> release.
> 
> Kind regards,
> Volodymyr Vysotskyi
> 
> On 2022/06/16 06:38:45 Viliam Durina wrote:
>> I'll try to work on comments in CALCITE-5157
>>  today
>> 
>> On Wed, 15 Jun 2022 at 07:30, Yanjing Wang 
>> wrote:
>> 
>>> Hi Andrei,
>>> The followings need review process to merge.
>>> https://issues.apache.org/jira/browse/CALCITE-4512
>>> https://issues.apache.org/jira/browse/CALCITE-5045
>>> https://issues.apache.org/jira/browse/CALCITE-5043
>>> https://issues.apache.org/jira/browse/CALCITE-4987
>>> It would be great if they were fixed in this release.
>>> 
>>> Dmitry Sysolyatin  于2022年6月14日周二 17:18写道：
>>> 
 Hi!
 It would be good to merge:
 https://issues.apache.org/jira/browse/CALCITE-5134
 https://issues.apache.org/jira/browse/CALCITE-5127
 
 Both of those issues are related to correct execution queries with
 subqueries.
 
 On Tue, Jun 14, 2022 at 6:59 AM Andrei Sereda  wrote:
 
> Thanks to all who reviewed the PRs for 1.31 release.
> 
> There are about 16 issues remaining tagged for 1.31 (list
> <
> 
 
>>> https://issues.apache.org/jira/issues/?jql=project%20%3D%20CALCITE%20AND%20fixVersion%20%3D%201.31.0%20AND%20status%20in%20(Open%2C%20%22In%20Progress%22%2C%20%22In%20Review%22%2C%20%22In%20Implementation%22)%20ORDER%20BY%20priority%20DESC
>> 
> ).
> 
> Please let me know which ones are important for this release (and I'll
> wait) otherwise I'll re-tag those issues for next release 1.32.
> 
> Regards,
> Andrei.
> 
> 
> 
> On Mon, Jun 13, 2022 at 1:05 PM Julian Hyde 
> wrote:
> 
>> I’m on vacation this week so don’t ask me to review PRs.
>> 
>> It looks like we’re on course for an RC this week, and definitely
>>> don’t
>> wait for me. Maybe we can get a few more PRs merged before then, and
 move
>> the rest to 1.32 (or clear the fix version if the person who agreed
>>> to
> fix
>> the bug has not responded).
>> 
>> Julian
>> 
>> 
>>> On Jun 4, 2022, at 2:17 PM, Andrei Sereda 
>>> wrote:
>>> 
>>> Hello,
>>> 
>>> I have created a JIRA filter
>>> <
>> 
> 
 
>>> https://issues.apache.org/jira/issues/?jql=project%20%3D%20CALCITE%20AND%20fixVersion%20%3D%201.31.0%20AND%20status%20in%20(Open%2C%20%22In%20Progress%22%2C%20%22In%20Review%22%2C%20%22In%20Implementation%22)%20ORDER%20BY%20priority%20DESC
>>> 
>>> for 1.31 release. There are currently 25 unresolved issues (planned
 for
>>> 1.31).
>>> 
>>> If you are familiar with some of the JIRA tickets can you please
 review
>> the
>>> PRs and resolve the ticket ?
>>> 
>>> The plan is to have an RC in 1-2 weeks.
>>> 
>>> Regards,
>>> Andrei.
>>> 
>>> 
>>> On Sat, Jun 4, 2022 at 6:13 AM Vova Vysotskyi <
>>> volody...@apache.org>
>> wrote:
>>> 
 Hello,
 
 Could we also include https://github.com/apache/calcite/pull/2305
> into
 this release?
 I have rebased it onto the latest master and fixed all merge
> conflicts.
 
 Kind regards,
 Volodymyr Vysotskyi
 
 On 2022/05/30 17:57:28 Julian Hyde wrote:
> Viliam, I marked the Jira case ‘fix in 1.31’ so that someone will
 at
 least review your PR.
> 
>> On May 30, 2022, at 7:09 AM, Viliam Durina
  wrote:
>> 
>> Our PR is reasonably simple and as far as I'm aware, it doesn't
 need
 more
>> changes, we'd be glad to find a reviewer and having it merged:
>> https://github.com/apache/calcite/pull/2808
>> 
>> Viliam
>> 
>> On Fri, 27 May 2022 at 21:49, Julian Hyde <
>>> jhyde.apa...@gmail.com
> 
 wrote:
>> 
>>> +1 mid-june release, and thank you to Ruben for sending the
> reminder.
>>> 
>>> Three weeks ago we had a discussion about the fixVersion [1]
 field
>> and
>>> seemed to reach consensus. There were new responsibilities for
 the
 release
>>> manager, as described here by Ruben:
>>> 
 Before starting the release process fo