Re: New rule for Converting UNION ALL with same inputs but different filters to single input with OR FILTER

[Yanjing Wang]

Thanks Albert.

 you could probably refer to osdi paper "resin general sub query fusion"
> which has chapters talking about general union fusion that deals with the
> case Julian pointed out.
>

The optimization seems very helpful, but it introduces new operators and
the rule for new operators may be more complex.
Hi, Community, What do you think?

Btw, per my understanding cases of union fusion is not that many in
> practice.
>

In particular situations such as VDT and BI tools which generates sql
according to a fixed pattern we will meet many such union all queries.

Albert  于2022年1月23日周日 11:22写道：

> On a more general fusion case, you could probably refer to osdi paper
> "resin general sub query fusion" which has chapters talking about general
> union fusion that deals with the case Julian pointed out.
>
> Btw, per my understanding cases of union fusion is not that many in
> practice. Curious to know the real cases
>
> 在 2022年1月21日星期五，Yanjing Wang  写道：
>
> > Hi Julian, I have logged a jira CALCITE-4990
> >  for this new rule,
> > later I will submit a pr for this.
> >
> > Julian Hyde  于2022年1月21日周五 01:37写道：
> >
> > > Process logic sounds great. Please log a Jira case.
> > >
> > > There’s a lot here, so it’s probably wise to split into a simple PR
> that
> > > just does the simple case (UNION ALL, no Project) and extend later.
> > >
> > > > On Jan 20, 2022, at 7:07 AM, Yanjing Wang  >
> > > wrote:
> > > >
> > > > Thanks Julian and Justin.
> > > >
> > > > What do you think the rule should be called?
> UnionFilterTransposeRule,
> > > >> perhaps?
> > > >>
> > > > SetOpFilterMergeRule?
> > > >
> > > > Maybe that problem does not occur when applied to UNION than when
> > applied
> > > >> to UNION ALL.
> > > >
> > > > Yes, This is very important.
> > > >
> > > > There would seem to be analogous rules for INTERSECT (combine the
> > > >> conditions using AND) and EXCEPT (combine the conditions using AND
> > NOT).
> > > >>
> > > > Excellent extensions, all the three operators process logic are:
> > > >
> > > > For UNION:
> > > > New Filter = left Filter *OR* right Filter.
> > > >
> > > > For INTERSECT:
> > > > New Filter = left Filter *AND* right Filter.
> > > >
> > > > For EXCEPT:
> > > > If left Filter, right Filter have no overlap, transform to left child
> > > tree,
> > > > Otherwise
> > > > New Filter = left Filter *AND NOT *right Filter.
> > > >
> > > > For UNION ALL:
> > > > Add prerequisites:
> > > > left Filter, right Filter must have no overlap.
> > > >
> > > > For INTERSECT ALL:
> > > > Add prerequisites:
> > > > If left Filter, right Filter have no overlap, transform to empty
> > values.
> > > > Otherwise
> > > > the rule can't be applied.
> > > >
> > > > For EXCEPT ALL:
> > > > same for  EXCEPT.
> > > >
> > > >
> > > > work for N-way Union, not just 2-way Union.
> > > >>
> > > > Yes, I will add tests for this.
> > > >
> > > > And I think you should make it work whether or not a Project is
> > present.
> > > >
> > > > Ok, It seems I need construct several different operand match trees
> for
> > > > plan.
> > > >
> > > > Hi Julian, what do you think of the above process logic?
> > > >
> > > > Julian Hyde mailto:jhyde.apa...@gmail.com>>
> > > 于2022年1月20日周四 10:18写道：
> > > >
> > > >> Justin,
> > > >>
> > > >> For planning table or index scans, I would recommend using a single
> > > >> TableScan with a Filter that uses a Sarg, rather than using multiple
> > > >> TableScans connected by a Union. So I think this rule will be
> useful.
> > > >>
> > > >> But I do agree that this proposed rule is not a “no brainer”. It may
> > not
> > > >> do what people want/expect in all cases, and therefore it probably
> > > should
> > > >> not be enabled it by default.
> > > >>
> > > >> Julian
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>> On Jan 19, 2022, at 3:38 PM, Justin Swanhart 
> > > >> wrote:
> > > >>>
> > > >>> Hi,
> > > >>>
> > > >>> Note that this will negate the optimization that one usually is
> > looking
> > > >> for
> > > >>> when writing such queries:
> > > >>>
> > > >>> Select * from TAB where a = 1
> > > >>> UNION ALL
> > > >>> Select * from TAB where b = 1
> > > >>>
> > > >>> In a database with indexes (most databases) this will allow indexes
> > to
> > > be
> > > >>> used on both the a column and the b column.
> > > >>> Databases with bitmap indexes or without indexes would benefit from
> > the
> > > >>> rule.
> > > >>>
> > > >>> On Wed, Jan 19, 2022 at 4:32 PM Julian Hyde <
> jhyde.apa...@gmail.com
> > > >> >>
> > wrote:
> > > >>>
> > >  Can you log a Jira case for this?
> > > 
> > >  I think you should make your rule work for N-way Union, not just
> > 2-way
> > >  Union. And I think you should make it work whether or not a
> Project
> > is
> > >  present.
> > > 
> > > > On Jan 19, 2022, at 1:25 PM, Julian Hyde  > > >
> > > 

Re: [DISCUSS] Refactoring tests

[Julian Hyde]

Gavin, Thanks for the kind words.

All, I have now squashed and rebased onto latest master. The squashed commit is 
https://github.com/julianhyde/calcite/commit/cb59231a72e23be260b21670012af33c47c2610e.
 I plan to merge to master on Monday.

Jacques, You may wish to carefully review my changes to RelMetadataTest. In 
your fix for https://issues.apache.org/jira/browse/CALCITE-4952 
 you made RelMetadataTest a 
parameterized test, so that each case could be called with two metadata 
providers. I undid that, and made it into a test with a protected fixture() 
method, so that a sub-class can run the same tests with a different provider. I 
believe that the test coverage is the same, albeit via different means.

Julian


> On Jan 22, 2022, at 9:53 AM, Gavin Ray  wrote:
> 
> Thank you for doing this, after reading the overview these changes seem
> like they will make a number of things easier related to testing.
> Super useful too when you're trying to start building something with
> Calcite but don't know it well enough to figure out how to put all the
> pieces together yourself.
> 
> On Fri, Jan 21, 2022 at 3:57 PM Julian Hyde  wrote:
> 
>> If it helps you review, I have created a ’summary’ document with a
>> description of the changes. It will become the commit message when I
>> squash, rebase, and merge to master.
>> 
>> 
>> https://github.com/julianhyde/calcite/blob/4885-test-fixtures/summary-of-calcite-4885.md
>> <
>> https://github.com/julianhyde/calcite/blob/4885-test-fixtures/summary-of-calcite-4885.md>
>> 
>> 
>> Julian
>> 
>> 
>>> On Jan 19, 2022, at 9:08 PM, Jacques Nadeau  wrote:
>>> 
>>> Unfortunately, the last minute attendance of the meetup today threw my
>>> schedule off and I won't be able to look at this for at least a few days.
>>> Don't feel obligated to hold up for me.
>>> 
>>> On Wed, Jan 19, 2022, 9:04 AM Jacques Nadeau  wrote:
>>> 
 FYI, I'm trying to do a thorough review today (as much as possible with
 patch this size).
 
 On Wed, Jan 19, 2022, 4:36 AM Michael Mior  wrote:
 
> Thanks for doing this Julian! I haven't been able to do a detailed
>> review,
> but from my skim of the PR, this looks like a nice improvement. I think
> it's always been a bit difficult for new Calcite developers to write
>> good
> tests, especially for new modules. So anything that helps that
>> experience
> is very welcome.
> 
> --
> Michael Mior
> mm...@apache.org
> 
> 
> Le mer. 17 nov. 2021 à 01:15, Vladimir Sitnikov <
> sitnikov.vladi...@gmail.com>
> a écrit :
> 
>> Jacques>This sounds like it will mean we will need to make
>> calcite-core
>> test artifacts available
>> 
>> Test artifacts publication is yet another anti-pattern just like "base
> test
>> class".
>> This change has been discussed:
>> https://lists.apache.org/thread/fz96p94h016p11g777otqntjxg2oxgh1
>> 
>> If you want to depend on a class from tests, consider moving it to
> /testkit
>> module:
>> 
>> 
> 
>> https://github.com/apache/calcite/tree/0899e6c157632ba1c5369a942cfe2be15fb4ed9f/testkit
>> 
>> Jacques>We should think about the rules around Kotlin
>> 
>> What happens in calcite-core/tests stays in calcite-core/tests :)
>> 
>> It is reasonable to assume that testkit module would have
>> dependencies,
>> and testkit would provide API that is usable from Java and other JVM
>> languages.
>> 
>> In that regard, Kotlin dependency in testkit is not much different
>> from
>> Quidem or commons-lang3.
>> Consumers might use Quidem if it fits just like they could use Kotlin
> if it
>> fits.
>> 
>> Vladimir
>> 
> 
 
>> 
>> 

Re: New rule for Converting UNION ALL with same inputs but different filters to single input with OR FILTER

[Albert]

On a more general fusion case, you could probably refer to osdi paper
"resin general sub query fusion" which has chapters talking about general
union fusion that deals with the case Julian pointed out.

Btw, per my understanding cases of union fusion is not that many in
practice. Curious to know the real cases

在 2022年1月21日星期五，Yanjing Wang  写道：

> Hi Julian, I have logged a jira CALCITE-4990
>  for this new rule,
> later I will submit a pr for this.
>
> Julian Hyde  于2022年1月21日周五 01:37写道：
>
> > Process logic sounds great. Please log a Jira case.
> >
> > There’s a lot here, so it’s probably wise to split into a simple PR that
> > just does the simple case (UNION ALL, no Project) and extend later.
> >
> > > On Jan 20, 2022, at 7:07 AM, Yanjing Wang 
> > wrote:
> > >
> > > Thanks Julian and Justin.
> > >
> > > What do you think the rule should be called? UnionFilterTransposeRule,
> > >> perhaps?
> > >>
> > > SetOpFilterMergeRule?
> > >
> > > Maybe that problem does not occur when applied to UNION than when
> applied
> > >> to UNION ALL.
> > >
> > > Yes, This is very important.
> > >
> > > There would seem to be analogous rules for INTERSECT (combine the
> > >> conditions using AND) and EXCEPT (combine the conditions using AND
> NOT).
> > >>
> > > Excellent extensions, all the three operators process logic are:
> > >
> > > For UNION:
> > > New Filter = left Filter *OR* right Filter.
> > >
> > > For INTERSECT:
> > > New Filter = left Filter *AND* right Filter.
> > >
> > > For EXCEPT:
> > > If left Filter, right Filter have no overlap, transform to left child
> > tree,
> > > Otherwise
> > > New Filter = left Filter *AND NOT *right Filter.
> > >
> > > For UNION ALL:
> > > Add prerequisites:
> > > left Filter, right Filter must have no overlap.
> > >
> > > For INTERSECT ALL:
> > > Add prerequisites:
> > > If left Filter, right Filter have no overlap, transform to empty
> values.
> > > Otherwise
> > > the rule can't be applied.
> > >
> > > For EXCEPT ALL:
> > > same for  EXCEPT.
> > >
> > >
> > > work for N-way Union, not just 2-way Union.
> > >>
> > > Yes, I will add tests for this.
> > >
> > > And I think you should make it work whether or not a Project is
> present.
> > >
> > > Ok, It seems I need construct several different operand match trees for
> > > plan.
> > >
> > > Hi Julian, what do you think of the above process logic?
> > >
> > > Julian Hyde mailto:jhyde.apa...@gmail.com>>
> > 于2022年1月20日周四 10:18写道：
> > >
> > >> Justin,
> > >>
> > >> For planning table or index scans, I would recommend using a single
> > >> TableScan with a Filter that uses a Sarg, rather than using multiple
> > >> TableScans connected by a Union. So I think this rule will be useful.
> > >>
> > >> But I do agree that this proposed rule is not a “no brainer”. It may
> not
> > >> do what people want/expect in all cases, and therefore it probably
> > should
> > >> not be enabled it by default.
> > >>
> > >> Julian
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>> On Jan 19, 2022, at 3:38 PM, Justin Swanhart 
> > >> wrote:
> > >>>
> > >>> Hi,
> > >>>
> > >>> Note that this will negate the optimization that one usually is
> looking
> > >> for
> > >>> when writing such queries:
> > >>>
> > >>> Select * from TAB where a = 1
> > >>> UNION ALL
> > >>> Select * from TAB where b = 1
> > >>>
> > >>> In a database with indexes (most databases) this will allow indexes
> to
> > be
> > >>> used on both the a column and the b column.
> > >>> Databases with bitmap indexes or without indexes would benefit from
> the
> > >>> rule.
> > >>>
> > >>> On Wed, Jan 19, 2022 at 4:32 PM Julian Hyde  > >> >>
> wrote:
> > >>>
> >  Can you log a Jira case for this?
> > 
> >  I think you should make your rule work for N-way Union, not just
> 2-way
> >  Union. And I think you should make it work whether or not a Project
> is
> >  present.
> > 
> > > On Jan 19, 2022, at 1:25 PM, Julian Hyde  > >
> > >> wrote:
> > >
> > > It sounds useful.
> > >
> > > What do you think the rule should be called?
> > UnionFilterTransposeRule,
> >  perhaps?
> > >
> > > A challenge when writing the rule will be to ensure that all of the
> >  inputs to the Union are the same. The Volcano framework is not very
> > >> good at
> >  that.
> > >
> > > You should be careful of the case that the conditions overlap. For
> >  example, the rewrite
> > >
> > > SELECT * FROM Emp WHERE deptno < 30
> > > UNION ALL
> > > SELECT * FROM Emp WHERE deptno IN (25, 35, 45)
> > >
> > > to
> > >
> > > SELECT * FROM Emp WHERE deptno < 30 OR deptno IN (25, 35, 45)
> > >
> > > Is not valid, because rows with deptno = 25 will appear twice in
> the
> >  first query, once in the second. Maybe that problem does not occur
> > when
> >  applied to UNION than when 

Re: Apache Calcite - How to create proper nested json object using JSON_OBJECT (it produces json with escapes)

[Hongze Zhang]

I think '=' is being shown because by "format json" the statement produced a 
JSON value expression rather than a regular
JSON string. Ideally "format json" should not be at the rhs of a select item 
and the validator should complain about
that. So there could be a missing check rule in validator.

To avoid producing escaped JSON value user can add "format json" to the inner 
items in a nested structure, for example:
  json_object('foo': json_object('foo': 'bar'))
produces
  {"foo":"{\"foo\":\"bar\"}"}
while
  json_object('foo': json_object('foo': 'bar') format json)
produces
  {"foo":"{"foo":"bar"}"}
.

Not 100% sure but I recall that by SQL standard "format json" should be 
implicitly specified in nested structure but
current Calcite doesn't support that. So one should write it manually.

Best,
Hongze



On Tue, 2022-01-18 at 21:16 +, M Singh wrote:
>  Hi Stamatis:
> Here is the JIRA ticket - https://issues.apache.org/jira/browse/CALCITE-4989
> Thanks again for your advice.
>     On Tuesday, January 18, 2022, 12:25:28 PM EST, M Singh 
>  wrote:  
>  
>   Hi Stamatis:
> Thanks for your quick response.
> I do have a question regarding the tests you have posted - especially the 
> last one (Add tests for JSON_OBJECT in
> functions.iq · zabetak/calcite@988c13c) - it shows the response as 
> {dept={address={number=128, street=Avenue New York, po=92000}, name=CSD, 
> id=1}}
> I am not sure why the key value separator is a '='.
> The complete segment is included below.
> 
> Thanks once again. 
> 
> > select json_object(KEY 'dept' VALUE( |
> >  |  |  json_object(KEY 'id' VALUE(1), KEY 'name' VALUE('CSD'), KEY 
> > 'address' VALUE( |
> >  |  |  json_object(KEY 'number' VALUE(128), KEY 'street' VALUE('Avenue New 
> > York'), KEY 'po' VALUE(92000))format
> > json) |
> >  |  |  ) format json) |
> >  |  |  ) format json; |
> >  |  | 
>  |
> >  |  | 
> > +-+
> >  |
> >  |  | | EXPR$0 | |
> >  |  | 
> > +-+
> >  |
> >  |  | | {dept={address={number=128, street=Avenue New York, po=92000}, 
> > name=CSD, id=1}} | |
> 
> 
> 
> 
> 
> 
> 
>     On Tuesday, January 18, 2022, 04:42:17 AM EST, Stamatis Zampetakis 
>  wrote:  
>  
>  Hello,
> 
> JSON_OBJECT is indeed the appropriate function for this use-case. I did a
> few tests [1] and it seems that escaped quotes are introduced when nesting
> the calls so most likely it is a bug. Please log a JIRA for this.
> 
> Best,
> Stamatis
> 
> [1]
> https://github.com/zabetak/calcite/commit/988c13ce1ff551d6e4046a3c027ff298f79971f8
> 
> On Mon, Jan 17, 2022 at 3:42 PM M Singh 
> wrote:
> 
> > Hi Folks:
> > I am trying to create a nested json object using JSON_OBJECT and am
> > getting a json with escaped quotes.
> > I have the following query in sql line :
> > select JSON_OBJECT(    KEY 'level1'    VALUE(        JSON_OBJECT(
> >     KEY 'level2'            VALUE(                JSON_OBJECT(
> >           KEY 'level3'                      VALUE 'val3')
> >   )            )        )      ) from (values ('{"a":{"b":2}}')) t(v);
> > And it produces the result:
> > 
> > +-+
> > 
> > >                           EXPR$0                            |
> > 
> > +-+
> > 
> > > {"level1":"{\"level2\":\"{\\\"level3\\\":\\\"val3\\\"}\"}"} |
> > +-+
> > Is it possible to create a result which is a proper json object:
> > {"level1":{"level2":{"level3":"val3"}}}
> > 
> > If there any other function/udf that I can use, please let me know.
> > Thanks
>     

Re: [ANNOUNCE] New Calcite PMC chair: Ruben Q L

[Enrico Olivelli]

Congrats!

Enrico

Il Sab 22 Gen 2022, 03:03 xiong duan  ha scritto:

> Congratulations to Ruben and thanks to Haisheng!
>
> Danny Chan  于2022年1月21日周五 12:19写道：
>
> > Congratulations, Ruben, and good luck!
> >
> > Haisheng, Thank you for serving as Chair.
> >
> > Yanjing Wang 于2022年1月21日 周五下午12:16写道：
> >
> > > Congrats Ruben!
> > >
> > > Stamatis Zampetakis  于2022年1月21日周五 06:34写道：
> > >
> > > > Congrats Ruben! You're kind, fair, and knowledgeable, very well
> > deserved.
> > > >
> > > > Many thanks for serving as a chair Haisheng.
> > > >
> > > > Best,
> > > > Stamatis
> > > >
> > > > On Thu, Jan 20, 2022, 12:41 PM Forward Xu 
> > > wrote:
> > > >
> > > > > Congratulations to Ruben!  Thanks for serving as Chair during the
> > last
> > > > > year, Haisheng!
> > > > >
> > > > >
> > > > > forward
> > > > >
> > > > > Ruben Q L  于2022年1月20日周四 19:38写道：
> > > > >
> > > > > > Thanks everyone!
> > > > > > And thank you Haisheng for being our PMC Chair during last year!
> > > > > >
> > > > > >
> > > > > >
> > > > > > On Thu, Jan 20, 2022 at 8:52 AM Alessandro Solimando <
> > > > > > alessandro.solima...@gmail.com> wrote:
> > > > > >
> > > > > > > Congratulations to Ruben and thanks a lot to Haisheng!
> > > > > > >
> > > > > > > On Thu, 20 Jan 2022 at 08:13, 953396112
> > <13282155...@qq.com.invalid
> > > >
> > > > > > > wrote:
> > > > > > >
> > > > > > > > Congratulations to Ruben!
> > > > > > > > Thanks for serving as Chair, Haisheng!
> > > > > > > >
> > > > > > > >
> > > > > > > > Best regards,
> > > > > > > > Zhaohui Xu
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > --原始邮件--
> > > > > > > > 发件人:
> > > > > > > >   "dev"
> > > > > > > >
> >  <
> > > > > > > > hy...@apache.org;
> > > > > > > > 发送时间:2022年1月20日(星期四) 上午10:29
> > > > > > > > 收件人:"dev" > > > > > > >
> > > > > > > > 主题:[ANNOUNCE] New Calcite PMC chair: Ruben Q L
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Calcite community members,
> > > > > > > >
> > > > > > > > I am pleased to announce that we have a new PMC chair and VP
> as
> > > per
> > > > > our
> > > > > > > > tradition of rotating the chair once a year. I have resigned,
> > and
> > > > > Ruben
> > > > > > > Q L
> > > > > > > > was duly elected by the PMC and approved unanimously by the
> > > Board.
> > > > > > > >
> > > > > > > > Please join me in congratulating Ruben!
> > > > > > > >
> > > > > > > > Best,
> > > > > > > > Haisheng Yuan
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>

Re: [DISCUSS] Refactoring tests

[Gavin Ray]

Thank you for doing this, after reading the overview these changes seem
like they will make a number of things easier related to testing.
Super useful too when you're trying to start building something with
Calcite but don't know it well enough to figure out how to put all the
pieces together yourself.

On Fri, Jan 21, 2022 at 3:57 PM Julian Hyde  wrote:

> If it helps you review, I have created a ’summary’ document with a
> description of the changes. It will become the commit message when I
> squash, rebase, and merge to master.
>
>
> https://github.com/julianhyde/calcite/blob/4885-test-fixtures/summary-of-calcite-4885.md
> <
> https://github.com/julianhyde/calcite/blob/4885-test-fixtures/summary-of-calcite-4885.md>
>
>
> Julian
>
>
> > On Jan 19, 2022, at 9:08 PM, Jacques Nadeau  wrote:
> >
> > Unfortunately, the last minute attendance of the meetup today threw my
> > schedule off and I won't be able to look at this for at least a few days.
> > Don't feel obligated to hold up for me.
> >
> > On Wed, Jan 19, 2022, 9:04 AM Jacques Nadeau  wrote:
> >
> >> FYI, I'm trying to do a thorough review today (as much as possible with
> >> patch this size).
> >>
> >> On Wed, Jan 19, 2022, 4:36 AM Michael Mior  wrote:
> >>
> >>> Thanks for doing this Julian! I haven't been able to do a detailed
> review,
> >>> but from my skim of the PR, this looks like a nice improvement. I think
> >>> it's always been a bit difficult for new Calcite developers to write
> good
> >>> tests, especially for new modules. So anything that helps that
> experience
> >>> is very welcome.
> >>>
> >>> --
> >>> Michael Mior
> >>> mm...@apache.org
> >>>
> >>>
> >>> Le mer. 17 nov. 2021 à 01:15, Vladimir Sitnikov <
> >>> sitnikov.vladi...@gmail.com>
> >>> a écrit :
> >>>
>  Jacques>This sounds like it will mean we will need to make
> calcite-core
>  test artifacts available
> 
>  Test artifacts publication is yet another anti-pattern just like "base
> >>> test
>  class".
>  This change has been discussed:
>  https://lists.apache.org/thread/fz96p94h016p11g777otqntjxg2oxgh1
> 
>  If you want to depend on a class from tests, consider moving it to
> >>> /testkit
>  module:
> 
> 
> >>>
> https://github.com/apache/calcite/tree/0899e6c157632ba1c5369a942cfe2be15fb4ed9f/testkit
> 
>  Jacques>We should think about the rules around Kotlin
> 
>  What happens in calcite-core/tests stays in calcite-core/tests :)
> 
>  It is reasonable to assume that testkit module would have
> dependencies,
>  and testkit would provide API that is usable from Java and other JVM
>  languages.
> 
>  In that regard, Kotlin dependency in testkit is not much different
> from
>  Quidem or commons-lang3.
>  Consumers might use Quidem if it fits just like they could use Kotlin
> >>> if it
>  fits.
> 
>  Vladimir
> 
> >>>
> >>
>
>

Re: Apache Calcite - How to create proper nested json object using JSON_OBJECT (it produces json with escapes)

[Stamatis Zampetakis]

Hi,

Agree with you the equals seems wrong as well but don't have time to
investigate. Contributions are welcomed.

Best,
Stamatis

On Tue, Jan 18, 2022 at 10:17 PM M Singh 
wrote:

>  Hi Stamatis:
> Here is the JIRA ticket -
> https://issues.apache.org/jira/browse/CALCITE-4989
> Thanks again for your advice.
> On Tuesday, January 18, 2022, 12:25:28 PM EST, M Singh <
> mans2si...@yahoo.com> wrote:
>
>   Hi Stamatis:
> Thanks for your quick response.
> I do have a question regarding the tests you have posted - especially the
> last one (Add tests for JSON_OBJECT in functions.iq ·
> zabetak/calcite@988c13c) - it shows the response as
> {dept={address={number=128, street=Avenue New York, po=92000}, name=CSD,
> id=1}}
> I am not sure why the key value separator is a '='.
> The complete segment is included below.
>
> Thanks once again.
>
> | select json_object(KEY 'dept' VALUE( |
> |  |  |  json_object(KEY 'id' VALUE(1), KEY 'name' VALUE('CSD'), KEY
> 'address' VALUE( |
> |  |  |  json_object(KEY 'number' VALUE(128), KEY 'street' VALUE('Avenue
> New York'), KEY 'po' VALUE(92000))format json) |
> |  |  |  ) format json) |
> |  |  |  ) format json; |
> |  |  |
>  |
> |  |  |
> +-+
> |
> |  |  | | EXPR$0 | |
> |  |  |
> +-+
> |
> |  |  | | {dept={address={number=128, street=Avenue New York, po=92000},
> name=CSD, id=1}} | |
>
>
>
>
>
>
>
> On Tuesday, January 18, 2022, 04:42:17 AM EST, Stamatis Zampetakis <
> zabe...@gmail.com> wrote:
>
>  Hello,
>
> JSON_OBJECT is indeed the appropriate function for this use-case. I did a
> few tests [1] and it seems that escaped quotes are introduced when nesting
> the calls so most likely it is a bug. Please log a JIRA for this.
>
> Best,
> Stamatis
>
> [1]
>
> https://github.com/zabetak/calcite/commit/988c13ce1ff551d6e4046a3c027ff298f79971f8
>
> On Mon, Jan 17, 2022 at 3:42 PM M Singh 
> wrote:
>
> > Hi Folks:
> > I am trying to create a nested json object using JSON_OBJECT and am
> > getting a json with escaped quotes.
> > I have the following query in sql line :
> > select JSON_OBJECT(KEY 'level1'VALUE(JSON_OBJECT(
> >KEY 'level2'VALUE(JSON_OBJECT(
> >  KEY 'level3'  VALUE 'val3')
> >  )))  ) from (values ('{"a":{"b":2}}')) t(v);
> > And it produces the result:
> >
> > +-+
> >
> > |  EXPR$0|
> >
> > +-+
> >
> > | {"level1":"{\"level2\":\"{\\\"level3\\\":\\\"val3\\\"}\"}"} |
> > +-+
> > Is it possible to create a result which is a proper json object:
> > {"level1":{"level2":{"level3":"val3"}}}
> >
> > If there any other function/udf that I can use, please let me know.
> > Thanks
>

Re: Questions With Materialized Views

[zhiming she]

I try to answer the first question. The materialized view rewriting of 
`SubstitutionVisitor` is based on the equivalent replacement of relational 
algebra expressions. Disadvantages of it are explained on the website [1].
1. it might need to enumerate exhaustively all possible equivalent rewritings 
for a given expression to find a materialized view substitution.
2. is not scalable in the presence of complex views


The second way , `MaterializedViewRule`, is implemented and extended from the 
algorithm described in [GL01]. Its implementation method extracts the structure 
of the query (predicate information, column information, etc.), and then  
perform a series of verifications （detail was in [GL01] ）and determine whether 
to rewrite, generate Compensation Predicate and completes the rewrite. This is 
a A more advanced way. The scope of applicability of this rewrite algorithm is 
wider than that of the first one.

In addition, as you can see from the code, 
`MaterializedViewSubstitutionVisitor` is ready to be discarded.

If there is any misunderstanding, please correct me~


[1] 
https://calcite.apache.org/docs/materialized_views.html#substitution-via-rules-transformation




> 2022年1月21日 上午10:12，徐保荣  写道：
> 
> hello,
> This problems keeps making me puzzled.
> 
>  question1: what is the differences between the two implemenrations of 
> view-based query rewriting?Advantages and disadvantages
> based on view substitution( SubstitutionVisitor、rules:unifyrule）  
> Rewriting using plan structural information(rules:MaterializedViewRule)
> 
>   question2: Is there some materialized rewriting example where the first one 
> not work but the second work? if can list , very thanks
> 
> I look forward to hearing from you very much
> thanks
> 
> 
> 
> 
> 
> rules transformation
> Permalink
> 
>