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: Stamatis Zampetakis

[Hongze Zhang]

Congratulations, Stamatis!


Hongze

At 2019-12-20 18:38:12, "Jesus Camacho Rodriguez"  wrote:
>Congrats Stamatis! Well deserved!
>
>-Jesús
>
>On Thu, Dec 19, 2019 at 8:51 PM Julian Hyde  wrote:
>
>> Glad to have you as the new chair, Stamatis! You have been a mature,
>> helpful and moderating voice in the community for quite some time. Well
>> deserved.
>>
>> Francis, thank you for serving as chair. Calcite became better and
>> stronger under your watch.
>>
>> I am delighted that we have had 5 chairs in the four years since Calcite
>> graduated (me, Jesus, Michael, Francis and now Stamatis). Each of the
>> chairs has been excellent, has contributed something different, and all
>> still actively involved in the community.
>>
>> Julian
>>
>>
>> > On Dec 19, 2019, at 1:32 AM, Igor Guzenko 
>> wrote:
>> >
>> > Congratulations, Stamatis!
>> >
>> > On Thu, Dec 19, 2019 at 9:04 AM Alessandro Solimando <
>> > alessandro.solima...@gmail.com> wrote:
>> >
>> >> Congratulations, Stamatis!
>> >>
>> >> Il Gio 19 Dic 2019, 07:16 Enrico Olivelli  ha
>> >> scritto:
>> >>
>> >>> Congratulations Stamatis!
>> >>>
>> >>> Enrico
>> >>>
>> >>> Il gio 19 dic 2019, 04:40 Rui Wang  ha scritto:
>> >>>
>>  Congratulations and Thanks Stamatis!
>> 
>> 
>> 
>>  -Rui
>> 
>>  On Wed, Dec 18, 2019 at 6:52 PM XING JIN 
>> >>> wrote:
>> 
>> > Congratulations Stamatis!
>> >
>> > -Jin
>> >
>> > Chunwei Lei  于2019年12月19日周四 上午10:33写道：
>> >
>> >> Congratulations Stamatis!
>> >>
>> >>
>> >> Best,
>> >> Chunwei
>> >>
>> >>
>> >> On Thu, Dec 19, 2019 at 9:36 AM Danny Chan 
>>  wrote:
>> >>
>> >>> Congratulations Stamatis!
>> >>>
>> >>> Best,
>> >>> Danny Chan
>> >>> 在 2019年12月19日 +0800 AM7:37，dev@calcite.apache.org，写道：
>> 
>>  Congratulations Stamatis!
>> >>>
>> >>
>> >
>> 
>> >>>
>> >>
>>
>>

Re: [VOTE] Calcite logo selection

[Hongze Zhang]

+1 for 5B

Hongze

> On Jul 1, 2019, at 12:01, Danny Chan  wrote:
> 
> +1 for 5B, but using the font of 2C.
> 
> Best,
> Danny Chan
> 在 2019年6月29日 +0800 PM4:48，Ivan Grgurina ，写道：
>> +1 for 5B, but using the font from 2C.
>> 
>> 
>> From: Yuva raj 
>> Sent: Saturday, June 29, 2019 9:17 AM
>> To: dev@calcite.apache.org
>> Subject: Re: [VOTE] Calcite logo selection
>> 
>> +1 for 5B
>> 
>> On Fri, 28 Jun 2019 at 18:58, Michael Mior  wrote:
>> 
>>> Based on the previous thread[0], many have weighed in on their
>>> preference. There were two clear front runners which we will now vote
>>> on. The vote will be open for 72 hours. Keep in mind that we can still
>>> discuss some smaller changes such as font, color, etc. with whatever
>>> logo is selected.
>>> 
>>> The two alternatives are below:
>>> 
>>> https://github.com/zabetak/calcite/blob/calcite-logo/site/img/index.md#candidate-5b
>>> 
>>> https://github.com/zabetak/calcite/blob/calcite-logo/site/img/index.md#candidate-2c
>>> 
>>> I'm not currently placing a vote since I really can't decide between
>>> the two. But I like both much better than the current logo. Thanks to
>>> all who contributed a design!
>>> 
>>> [0]
>>> https://mail-archives.apache.org/mod_mbox/calcite-dev/201906.mbox/%3C8587ADCD-FF19-4486-98C1-4B645614FB47%40apache.org%3E
>>> --
>>> Michael Mior
>>> mm...@apache.org
>>> 
>> 
>> 
>> --
>> *Thanks*
>> 
>> *Yuvaraj L*

Re: [VOTE] Release apache-calcite-1.20.0 (release candidate 0)

[Hongze Zhang]

+1

On macOS 10.14.5, openjdk 11.0.2:
- Built and ran unit tests from tarball OK
- Built and ran unit tests from target commit 3979b460c OK
- Checked signatures and hashes OK

Thanks Michael!

Hongze

> On Jun 21, 2019, at 06:42, Michael Mior  wrote:
> 
> Thanks for noting the issue in the license. I agree that we can
> correct this after the release.
> --
> Michael Mior
> mm...@apache.org
> 
> Le jeu. 20 juin 2019 à 15:53, Vladimir Sitnikov
>  a écrit :
>> 
>> Checked GPG signature - OK
>> Checked hash - OK
>> Release notes - OK
>> 
>> +1 (binding)
>> 
>> In general I support the release, however LICENSE file is slightly out of
>> date.
>> 
>> The license contains the following:
>> - site/_sass/_font-awesome.scss
>>Font-awesome css files v4.1.0 (
>> http://fortawesome.github.io/Font-Awesome/)
>> 
>> In fact, the included library is v4.2.0.
>> That however is a minor issue and I guess it can be corrected in the future.
>> 
>> Vladimir

Re: Parsing DB2 sql statements

[Hongze Zhang]

Hi Andrew,

If you are focusing parsing only, I believe you can just use the babel parser - 
in babel we have added support for DATE function via CALCITE-3022. The thing is 
you need to compile Calcite's code by yourself - 1.20.0 hasn't been released 
yet. 

Hongze

> On Jun 6, 2019, at 19:06, Andrew O  wrote:
> 
> I'm doing a project trying to parse some IBM DB2 sql expressions (to
> analyze table / column usages).
> 
> To note,
> 1) these are existing ad-hoc user queries so I can't change their syntax
> 2)  I don't have a connection / schema for the database,  but my
> understanding is that this shouldn't be required by Calcite for this
> kind of work.
> 
> Currently I'm hitting some parsing issues with queries like
> 
>select Date(x.col1), x.col2 from myTable x
> 
> It fails parse at the point of the Date function call. This is trying to
> use the function to convert the value of col1 to a date (almost similar to
> a cast/convert).
> 
> https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_bif_date.html
> 
> 
> My current thinking would be that this  may need changes to the parser
> grammar (through an extension?). Or perhaps this could be registered as a
> custom User Defined Function of some kind (although Date seems like a
> reserved word)?
> 
> Is this the right thinking,  or is there another approach I could look at?
> 
> Thanks in advance
> 
> Andrew

Re: Linq expressions to RexNode

[Hongze Zhang]

Although requirement of such functionality is not usual but there is actually a 
method 
"org.apache.calcite.prepare.CalcitePrepareImpl.EmptyScalarTranslator.toRex(Expression
 expression)" which intended to do translation from linq4j expressions to rex 
nodes. But since the method is currently supposed to be serving Calcite 
internal calls only I don't think everything is there.

Hongze

> On Jun 5, 2019, at 05:15, Haisheng Yuan  wrote:
> 
> Hi Khai,
> 
> I don't think there is such kind of utility function in Calcite.
> 
> - Haisheng 
> Yuan--
> 发件人：Khai Tran
> 日　期：2019年06月05日 01:28:21
> 收件人：dev@calcite.apache.org (dev@calcite.apache.org)
> 主　题：Linq expressions to RexNode
> 
> Just a bit strange, but just wonder if in calcite code base, is there any 
> util function to convert linq expression back to RexNode?
> 

[jira] [Created] (CALCITE-3095) Add several system properties to control rules and traits

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-3095:
-

 Summary: Add several system properties to control rules and traits
 Key: CALCITE-3095
 URL: https://issues.apache.org/jira/browse/CALCITE-3095
 Project: Calcite
  Issue Type: Improvement
Reporter: Hongze Zhang


This is to make following constant flags configurable from Calcite system 
properties:

* CalcitePrepareImpl.ENABLE_COLLATION_TRAIT
* CalcitePrepareImpl.ENABLE_ENUMERABLE
* CalcitePrepareImpl.ENABLE_STREAM



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Created] (CALCITE-3093) Decouple JDBC connection calls from PlannerImpl

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-3093:
-

 Summary: Decouple JDBC connection calls from PlannerImpl
 Key: CALCITE-3093
 URL: https://issues.apache.org/jira/browse/CALCITE-3093
 Project: Calcite
  Issue Type: Improvement
  Components: core
Reporter: Hongze Zhang
Assignee: Hongze Zhang


Currently the class {{PlannerImpl}} creates JDBC connections[1] internally to 
instantiate {{RelOptPlanner}}. For improving the design I suggest to reverse 
the call chain, for example, we can make JDBC API call PlannerImpl to create 
{{RelOptPlanner}}, {{SqlValidator}}, {{SqlToRelConverter}} instances, etc.

This JIRA topic is the first step of the improvement - we should decouple the 
use of JDBC API from PlannerImpl. Planner API is designed to be a reusable 
toolkit and we should make it work individually without the help of JDBC API.

[1] 
https://github.com/apache/calcite/blob/4e1b68e3c0b2a90832cc790c6166cebb14ae2970/core/src/main/java/org/apache/calcite/prepare/PlannerImpl.java#L143-L151



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Created] (CALCITE-3075) Evaluate Travis CI for Windows build

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-3075:
-

 Summary: Evaluate Travis CI for Windows build
 Key: CALCITE-3075
 URL: https://issues.apache.org/jira/browse/CALCITE-3075
 Project: Calcite
  Issue Type: Improvement
Reporter: Hongze Zhang


Travis.org supports running CI on Windows now: 
https://blog.travis-ci.com/2018-10-11-windows-early-release, it is probably 
worth to investigate if running Windows CI using Travis would benefit us more 
than AppVevor can do.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Created] (CALCITE-3074) Move MySQL's JSON operators to SqlLibraryOperators

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-3074:
-

 Summary: Move MySQL's JSON operators to SqlLibraryOperators
 Key: CALCITE-3074
 URL: https://issues.apache.org/jira/browse/CALCITE-3074
 Project: Calcite
  Issue Type: Sub-task
Reporter: Hongze Zhang
Assignee: Hongze Zhang
 Fix For: 1.20.0


This is a follow-up to CALCITE-2846.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Re: [ANNOUNCE] New committer: Danny Chan

[Hongze Zhang]

Congratulations, Danny!

Best,
Hongze

-- Original Message --
From: "Jark Wu" 
To: dev@calcite.apache.org
Sent: 2019/5/14 10:04:44
Subject: Re: [ANNOUNCE] New committer: Danny Chan


Congratulations Danny!

Best,
Jark

On Tue, 14 May 2019 at 09:57, Yuzhao Chen  wrote:


 Thank you everyone for your kind messages.

 Currently I am working in Alibaba Blink SQL Engine team in Hangzhou,
 Zhejiang, China. We are developing a production version of Apache Flink.
 Our team has done many promotions for flink-table module and recently we
 are merging and contributing our codebase to the Apache Flink community.

 It is my honor to be part of Apache Calcite community, I will contribute
 continuously to this great project.

 Best,
 Danny Chan
 在 2019年5月14日 +0800 AM6:40，Francis Chuang ，写道：
 > Apache Calcite's Project Management Committee (PMC) has invited Danny
 > Chan to become a committer, and we are pleased to announce that he has
 > accepted.
 >
 > Danny has been a prolific contributor to Calcite, with CALCITE-2969
 > being one of his more complex contributions to date. He has also been
 > extremely active on our mailing lists, contributing to many design
 > discussions.
 >
 > Danny, welcome, thank you for your contributions, and we look forward
 > your further interactions with the community! If you wish, please feel
 > free to tell us more about yourself and what you are working on.
 >
 > Francis (on behalf of the Apache Calcite PMC)

Re: [VOTE] Release apache-calcite-avatica-1.15.0 (release candidate 0)

[Hongze Zhang]

+1

(OpenJDK 1.8, Fedora 29)
* Built and run tests from tarball  [OK]
* Built and run tests from Git commit 95e154 [OK]
* Run Calcite tests with the RC [OK]
* Run some smoke tests with SQLLine, DBeaver [OK]
* Checked signatures and hashes [OK]

Thanks Francis!

Hongze

-- Original Message --
From: "Andrew Pilloud" 
To: dev@calcite.apache.org
Sent: 2019/5/10 0:10:05
Subject: Re: [VOTE] Release apache-calcite-avatica-1.15.0 (release 
candidate 0)



+1

Tested with Apache Beam, all tests pass. Also tested error messages with
SqlLine, SQL Workbench/J, and SQuirreL SQL.

Thanks for the quick turnaround!

Andrew

*From: *Francis Chuang 
*Date: *Thu, May 9, 2019 at 2:35 AM
*To: * 

Hi all,


 I have created a build for Apache Calcite Avatica 1.15.0, release
 candidate 0.

 Thanks to everyone who has contributed to this release.

 You can read the release notes here:

 
https://github.com/apache/calcite-avatica/blob/branch-avatica-1.15/site/_docs/history.md

 The commit to be voted upon:

 
https://gitbox.apache.org/repos/asf?p=calcite-avatica.git;a=commit;h=95e15456e4b16f1a51b0e86babb113114c9b62d0

 Its hash is 95e15456e4b16f1a51b0e86babb113114c9b62d0

 The artifacts to be voted on are located here:

 
https://dist.apache.org/repos/dist/dev/calcite/apache-calcite-avatica-1.15.0-rc0/

 The hashes of the artifacts are as follows:
 src.tar.gz.sha512

 
1d75990eb77f1123ecdafb21f004719383b396fab84cb8aa4a773e9fe57fa7adc1cdd0b31285e74e90cdaa84f3c00249da50e74d9510403e51f07f4bbe21d7dc

 A staged Maven repository is available for review at:
 https://repository.apache.org/content/repositories/orgapachecalcite-1060

 Release artifacts are signed with the following key:
 https://people.apache.org/keys/committer/francischuang.asc

 If you do not have a Java environment available, you can run the tests
 using docker. To do so, install docker and docker-compose, then run
 "docker-compose run test" from the root of the directory.

 Please vote on releasing this package as Apache Calcite Avatica 1.15.0.

 The vote is open for the next 72 hours and passes if a majority of at
 least three +1 PMC votes are cast.

 [ ] +1 Release this package as Apache Calcite 1.14.0
 [ ]  0 I don't feel strongly about it, but I'm okay with the release
 [ ] -1 Do not release this package because...


 Here is my vote:

 +1 (binding)

 Francis

Re: JIRA usage reminders

[Hongze Zhang]

Thank you very much for proposing the document changes, Stamatis.

Regarding the issue status, personally I am good to try following a unified 
convention anyway, thank you for logging that to website also.

And I went quickly through the JIRA console page, but didn't either find an 
obvious way to custom the "screen"[1] of issue resolution (where the list of 
unreleased versions seems to be changeable). Or we can first do something 
manually to deal with the existing JIRA issues[2] having "Resolved" marked as 
resolution, and avoid using (if possible) that option in future.

By the way, does the resolution "Resolved" and status "RESOLVED" mean 
differently? If "Resolved" does have its own use then maybe I misunderstood it. 
Generally it confused me more or less when I bumped into it at the first time.

Thanks,
Hongze

[1] 
https://confluence.atlassian.com/adminjiraserver/defining-a-screen-938847288.html
[2] 
https://issues.apache.org/jira/browse/CALCITE-2923?jql=project%20%3D%20CALCITE%20AND%20resolution%20%3D%20Resolved

> On May 6, 2019, at 01:40, Stamatis Zampetakis  wrote:
> 
> I tried to summarize what we discussed here in the following PR:
> 
> https://github.com/apache/calcite/pull/1196
> 
> I will leave it open for a few days in case we want to do some
> modifications.
> 
> @Hongze:
> Thanks for cleaning up those [3] unfinished issues.
> 
> Since nobody else expressed an opinion between closing issues directly or
> marking them as resolved, I kept the second option. If the others have
> another opinion we can easily change it back. As I said in the beginning,
> I'm OK with both options.
> 
> Regarding the JIRA configuration, I don't think we can do much. It seems
> that field values are configured globally per JIRA instance (and not per
> project) so I guess we cannot remove some values without affecting all
> Apache projects. I'm not so familiar with JIRA so if somebody else knows
> how to remove "Resolved" from the values of "Resolution" field (without
> affecting all projects) it would be great.
> 
> 
> On Sat, Apr 27, 2019 at 3:38 AM Hongze Zhang  wrote:
> 
>> Thanks for the summing up, Stamatis. That helps a lot, and I am convinced
>> by the advantages.
>> 
>> Also, I've re-checked the original definition[1] of the status flags
>> CLOSED, RESOLVED, etc. Following is what "RESOLVED" is for:
>> 
>>> RESOLVED: The issue is considered finished, the resolution is correct.
>> Issues which are not closed can be reopened.
>> 
>> It seems that marking an issue as "RESOLVED" implies we have anyway
>> provided a resolution, no matter whether "Fixed" the resolution is, so
>> using a "RESOLVED" at first looks to be reasonable to me now.
>> 
>> Originally I wanted to debate on this because I see we (including myself
>> ;) ) used a lot of "CLOSED"s[2] in similar cases these days, after
>> finishing release of version 1.19. I can see the motivation of closing them
>> directly, say, a JIRA contributor was pretty confident with the conclusion
>> that the bug is invalid/duplicated/etc., so that the issue is no need to be
>> reopened anymore. Should we still need RM to do a double-check in cases
>> like this?
>> 
>> Besides, I also found some issues[3] that are created for quite a long
>> time and marked as RESOLVED but never CLOSED. I prefer to go through and
>> try closing them somehow after we finish this discussion.
>> 
>> Also I found we both have status "RESOLVED" and resolution "Resolved". Are
>> we able to remove the latter? That's pretty confusing.
>> 
>> And +1 to not having the "next" fixing version. I am not sure if we can
>> remove the flag from JIRA console, but I see we already locked some of
>> "status" flags such as "ACCEPTED", "REVIEWABLE", etc..
>> 
>> Hongze
>> 
>> [1] https://issues.apache.org/jira/ShowConstantsHelp.jspa
>> [2]
>> https://issues.apache.org/jira/browse/CALCITE-2821?jql=project%20%3D%20CALCITE%20AND%20status%20%3D%20Closed%20AND%20resolution%20in%20(Unresolved%2C%20%22Won%27t%20Fix%22%2C%20Duplicate%2C%20Invalid%2C%20Incomplete%2C%20%22Cannot%20Reproduce%22%2C%20Later%2C%20%22Not%20A%20Problem%22%2C%20Implemented%2C%20Done%2C%20%22Auto%20Closed%22%2C%20%22Pending%20Closed%22%2C%20REMIND%2C%20Resolved%2C%20%22Not%20A%20Bug%22%2C%20Workaround%2C%20Staged%2C%20Delivered%2C%20%22Information%20Provided%22%2C%20%22Works%20for%20Me%22%2C%20%22Feedback%20Received%22%2C%20%22Won%27t%20Do%22)%20ORDER%20BY%20created%20DESC%2C%20priority%20ASC%2C%20updated%20DESC
>> [3]
>> https://i

Re: How to access map field value in an Array

[Hongze Zhang]

Hi Vishwas,

The way to access elements from array or map is documented[1] on Calcite 
website. Is that what you need?

By the way, the ordinal you use to access an array should start from 1 rather 
than 0. For instance, if you execute SQL:

> select x[2]['key2'] as v from (values(array[map['key1', 'value1'], 
> map['key1', 'value1', 'key2', 'value2']])) as t(x)

The SQL is good, and Calcite will give you result "V=value2".

Best,
Hongze

[1] http://calcite.apache.org/docs/reference.html#value-constructors

> On May 5, 2019, at 20:45, Vishwas Bm  wrote:
> 
> Hi,
> 
> Can anyone help me in telling how to write a sql query directly to access a
> map value in an array.
> 
> I have an array field with this format. Now how to get value using the
> mapKey for a specific array Index.
> 
> *FieldName:* Field_X
> *Type:* ARRAY>
> 
> 
> 
> *Thanks & Regards,*
> 
> *Vishwas *

[jira] [Created] (CALCITE-3046) CompileException when inserting casted value of composited user defined type

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-3046:
-

 Summary: CompileException when inserting casted value of 
composited user defined type
 Key: CALCITE-3046
 URL: https://issues.apache.org/jira/browse/CALCITE-3046
 Project: Calcite
  Issue Type: Bug
Reporter: Hongze Zhang


Reproduce the error by running following test case from {{ServerTest.java}}:

{code:java}
  @Test public void testInsertCastedValueOfCompositeUdt() throws Exception {
try (Connection c = connect();
 Statement s = c.createStatement()) {
  // CALCITE-2464: Allow to set nullability for columns of structured types
  boolean b = s.execute("create type mytype as (i int, j int)");
  assertThat(b, is(false));
  b = s.execute("create table w (i int not null, j mytype)");
  assertThat(b, is(false));
  int x = s.executeUpdate("insert into w "
  + "values (1, cast((select j from w) as mytype))");
  assertThat(x, is(1));
}
  }
{code}

Root cause of the error:

{code}
Caused by: org.codehaus.commons.compiler.CompileException: Line 96, Column 84: 
Cannot determine simple type name "Record2_0"
at 
org.codehaus.janino.UnitCompiler.compileError(UnitCompiler.java:12211)
at 
org.codehaus.janino.UnitCompiler.getReferenceType(UnitCompiler.java:6833)
at 
org.codehaus.janino.UnitCompiler.getReferenceType(UnitCompiler.java:6594)
at org.codehaus.janino.UnitCompiler.getType2(UnitCompiler.java:6573)
at org.codehaus.janino.UnitCompiler.access$13900(UnitCompiler.java:215)
at 
org.codehaus.janino.UnitCompiler$22$1.visitReferenceType(UnitCompiler.java:6481)
at 
org.codehaus.janino.UnitCompiler$22$1.visitReferenceType(UnitCompiler.java:6476)
at org.codehaus.janino.Java$ReferenceType.accept(Java.java:3928)
at org.codehaus.janino.UnitCompiler$22.visitType(UnitCompiler.java:6476)
at org.codehaus.janino.UnitCompiler$22.visitType(UnitCompiler.java:6469)
at org.codehaus.janino.Java$ReferenceType.accept(Java.java:3927)
at org.codehaus.janino.UnitCompiler.getType(UnitCompiler.java:6469)
at org.codehaus.janino.UnitCompiler.access$1300(UnitCompiler.java:215)
at 
org.codehaus.janino.UnitCompiler$36.getParameterTypes2(UnitCompiler.java:10383)
at 
org.codehaus.janino.IClass$IInvocable.getParameterTypes(IClass.java:960)
at org.codehaus.janino.IClass$IMethod.getDescriptor2(IClass.java:1227)
at org.codehaus.janino.IClass$IInvocable.getDescriptor(IClass.java:983)
at org.codehaus.janino.IClass.getIMethods(IClass.java:248)
at org.codehaus.janino.IClass.getIMethods(IClass.java:237)
at org.codehaus.janino.UnitCompiler.compile2(UnitCompiler.java:492)
at org.codehaus.janino.UnitCompiler.compile2(UnitCompiler.java:981)
at org.codehaus.janino.UnitCompiler.compile2(UnitCompiler.java:951)
at org.codehaus.janino.UnitCompiler.access$200(UnitCompiler.java:215)
at 
org.codehaus.janino.UnitCompiler$2.visitAnonymousClassDeclaration(UnitCompiler.java:409)
at 
org.codehaus.janino.UnitCompiler$2.visitAnonymousClassDeclaration(UnitCompiler.java:406)
at 
org.codehaus.janino.Java$AnonymousClassDeclaration.accept(Java.java:1149)
at org.codehaus.janino.UnitCompiler.compile(UnitCompiler.java:406)
at org.codehaus.janino.UnitCompiler.compileGet2(UnitCompiler.java:5509)
at org.codehaus.janino.UnitCompiler.access$9500(UnitCompiler.java:215)
at 
org.codehaus.janino.UnitCompiler$16.visitNewAnonymousClassInstance(UnitCompiler.java:4432)
at 
org.codehaus.janino.UnitCompiler$16.visitNewAnonymousClassInstance(UnitCompiler.java:4396)
at 
org.codehaus.janino.Java$NewAnonymousClassInstance.accept(Java.java:5238)
at org.codehaus.janino.UnitCompiler.compileGet(UnitCompiler.java:4396)
at 
org.codehaus.janino.UnitCompiler.compileGetValue(UnitCompiler.java:5662)
at org.codehaus.janino.UnitCompiler.compileGet2(UnitCompiler.java:5182)
at org.codehaus.janino.UnitCompiler.access$9100(UnitCompiler.java:215)
at 
org.codehaus.janino.UnitCompiler$16.visitMethodInvocation(UnitCompiler.java:4423)
at 
org.codehaus.janino.UnitCompiler$16.visitMethodInvocation(UnitCompiler.java:4396)
at org.codehaus.janino.Java$MethodInvocation.accept(Java.java:5073)
at org.codehaus.janino.UnitCompiler.compileGet(UnitCompiler.java:4396)
at 
org.codehaus.janino.UnitCompiler.compileGetValue(UnitCompiler.java:5662)
at org.codehaus.janino.UnitCompiler.compile2(UnitCompiler.java:2580)
at org.codehaus.janino.UnitCompiler.access$2700(UnitCompiler.java:215)
at 
org.codehaus.janino.UnitCompiler$6.visitLocalVariableDeclarationStatement(UnitCompiler.java:1503)
at 
org.codehaus.janino.UnitCompiler$6.visitLocalV

[jira] [Created] (CALCITE-3045) NullPointerException when casting null literal to user defined type

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-3045:
-

 Summary: NullPointerException when casting null literal to user 
defined type
 Key: CALCITE-3045
 URL: https://issues.apache.org/jira/browse/CALCITE-3045
 Project: Calcite
  Issue Type: Bug
Reporter: Hongze Zhang
 Fix For: 1.20.0


You can reproduce the bug by running following test case from UdtTest.java:

{code:java}
  @Test public void testUdt2() {
final String sql = "select CAST(null AS \"adhoc\".mytype2) as ld "
+ "from (VALUES ROW(1, 'SameName')) AS \"t\" (\"id\", \"desc\")";
withUdt().query(sql).returns("LD=1\n");
  }
{code}

Error thrown:

{code}
java.sql.SQLException: Error while executing SQL "select CAST(null AS 
"adhoc".mytype2) as ld from (VALUES ROW(1, 'SameName')) AS "t" ("id", "desc")": 
null

at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at 
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163)
at 
org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227)
at 
org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:522)
at 
org.apache.calcite.test.CalciteAssert$AssertQuery.lambda$returns$1(CalciteAssert.java:1440)
at 
org.apache.calcite.test.CalciteAssert$AssertQuery.withConnection(CalciteAssert.java:1372)
at 
org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1438)
at 
org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1421)
at 
org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1384)
at org.apache.calcite.test.UdtTest.testUdt2(UdtTest.java:65)
at 
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at 
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at 
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at 
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at 
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at 
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at 
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at 
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at 
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at 
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at 
com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at 
com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at 
com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Suppressed: org.apache.calcite.util.TestUtil$ExtraInformation: With 
materializationsEnabled=false, limit=0
at org.apache.calcite.util.TestUtil.rethrow(TestUtil.java:268)
at 
org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:554)
... 28 more
Caused by: java.lang.NullPointerException
at java.base/java.util.Objects.requireNonNull(Objects.java:221)
at 
org.apache.calcite.sql.validate.SqlValidatorImpl.setValidatedNodeType(SqlValidatorImpl.java:1616)
at 
org.apache.calcite.sql2rel.StandardConvertletTable.convertCast(StandardConvertletTable.java:532)
at 
org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:63)
at 
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4758)
at 
org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4063)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.jav

Re: Clean up JIRA tickets

[Hongze Zhang]

Thanks for the help and clarifying, Francis.

Hongze

-- Original Message --
From: "Francis Chuang" 
To: dev@calcite.apache.org
Sent: 2019/4/30 6:23:01
Subject: Re: Clean up JIRA tickets


This is because the option is only available to people with the Administrator 
role. It seems counter-intuitive, but according to the JIRA documentation, you 
need to be an administrator to do this: 
https://confluence.atlassian.com/jiracoreserver073/editing-multiple-issues-at-the-same-time-861257342.html

I've given you the administrator role on JIRA, so the option will be available 
when you want to make a bulk transition next time.

Volodymyr and I also encountered this problem last year when we were release 
managers for the first time:
- 
https://lists.apache.org/thread.html/21ba575b7adc5ac3e1520675136db501d0958641ce98f1d0a0d291c7@%3Cdev.calcite.apache.org%3E
- 
https://lists.apache.org/thread.html/216c7fc441509f9da8c63937f30d5458a1222f475da89a4ec4417ece@%3Cdev.calcite.apache.org%3E

Francis

On 30/04/2019 3:58 am, Hongze Zhang wrote:

Thanks Josh, I noticed that the mails are sent, apologize to everyone being 
annoyed.

Maybe I missed something but I re-checked the whole process of bulk operation 
and still didn't found such a button. Not sure if it is related to JIRA roles.

Apologize again, please ignore these emails.

Hongze




On Apr 30, 2019, at 01:21, Josh Elser  wrote:

Yes, please check the "don't send emails" button in the future :)

Thanks for cleaning them up, Hongze!

On 4/29/19 12:11 PM, Kevin Risden wrote:

You can do this as a bulk transition to avoid emailing everyone of the
change. If you select "Tools" -> "Bulk change".
Kevin Risden
On Mon, Apr 29, 2019 at 10:46 AM Hongze Zhang  wrote:

Hi all,

As I have suggested on a related thread[1] started by Stamatis, I am
planning to close the JIRA tickets that are marked as status "RESOLVED" but
bound with an earlier fix version. Say, if a ticket is marked "RESOLVED",
and its fix version is provided but not one of our unreleased versions
1.20.0/1.21.0/avatica-1.15.0/avatica-go-4.0.0/avatica-go-5.0.0, it is going
to be closed.

Here is a link to the full list (currently 78 tickets in total):


https://issues.apache.org/jira/issues/?jql=project%20%3D%20CALCITE%20AND%20status%20%3D%20Resolved%20AND%20fixVersion%20not%20in%20(1.20.0%2C1.21.0%2Cavatica-1.15.0%2Cavatica-go-4.0.0%2Cavatica-go-5.0.0)

I'll close them by the ascending order of JIRA ID respectively. If you
used to participate in the discussion of any issue on the list, and you
think it is too early to close, please feel free to reopen and add some
follow-up comments.

Thanks,
Hongze


[1]
https://lists.apache.org/thread.html/03f1e1937892e2ccbb30b639219122c28ca3c389e10aa01ed2214ca1@%3Cdev.calcite.apache.org%3E

Re: Clean up JIRA tickets

[Hongze Zhang]

Thanks a lot for the tips, Kevin! This is indeed what I need. However I didn't 
notice the existing of the tool before. ;)

Hongze

> On Apr 30, 2019, at 00:11, Kevin Risden  wrote:
> 
> You can do this as a bulk transition to avoid emailing everyone of the
> change. If you select "Tools" -> "Bulk change".
> 
> Kevin Risden
> 
> 
> On Mon, Apr 29, 2019 at 10:46 AM Hongze Zhang  wrote:
> 
>> Hi all,
>> 
>> As I have suggested on a related thread[1] started by Stamatis, I am
>> planning to close the JIRA tickets that are marked as status "RESOLVED" but
>> bound with an earlier fix version. Say, if a ticket is marked "RESOLVED",
>> and its fix version is provided but not one of our unreleased versions
>> 1.20.0/1.21.0/avatica-1.15.0/avatica-go-4.0.0/avatica-go-5.0.0, it is going
>> to be closed.
>> 
>> Here is a link to the full list (currently 78 tickets in total):
>> 
>> 
>> https://issues.apache.org/jira/issues/?jql=project%20%3D%20CALCITE%20AND%20status%20%3D%20Resolved%20AND%20fixVersion%20not%20in%20(1.20.0%2C1.21.0%2Cavatica-1.15.0%2Cavatica-go-4.0.0%2Cavatica-go-5.0.0)
>> 
>> I'll close them by the ascending order of JIRA ID respectively. If you
>> used to participate in the discussion of any issue on the list, and you
>> think it is too early to close, please feel free to reopen and add some
>> follow-up comments.
>> 
>> Thanks,
>> Hongze
>> 
>> 
>> [1]
>> https://lists.apache.org/thread.html/03f1e1937892e2ccbb30b639219122c28ca3c389e10aa01ed2214ca1@%3Cdev.calcite.apache.org%3E
>> 
> 

Re: Clean up JIRA tickets

[Hongze Zhang]

Thanks Josh, I noticed that the mails are sent, apologize to everyone being 
annoyed.

Maybe I missed something but I re-checked the whole process of bulk operation 
and still didn't found such a button. Not sure if it is related to JIRA roles.

Apologize again, please ignore these emails.

Hongze



> On Apr 30, 2019, at 01:21, Josh Elser  wrote:
> 
> Yes, please check the "don't send emails" button in the future :)
> 
> Thanks for cleaning them up, Hongze!
> 
> On 4/29/19 12:11 PM, Kevin Risden wrote:
>> You can do this as a bulk transition to avoid emailing everyone of the
>> change. If you select "Tools" -> "Bulk change".
>> Kevin Risden
>> On Mon, Apr 29, 2019 at 10:46 AM Hongze Zhang  wrote:
>>> Hi all,
>>> 
>>> As I have suggested on a related thread[1] started by Stamatis, I am
>>> planning to close the JIRA tickets that are marked as status "RESOLVED" but
>>> bound with an earlier fix version. Say, if a ticket is marked "RESOLVED",
>>> and its fix version is provided but not one of our unreleased versions
>>> 1.20.0/1.21.0/avatica-1.15.0/avatica-go-4.0.0/avatica-go-5.0.0, it is going
>>> to be closed.
>>> 
>>> Here is a link to the full list (currently 78 tickets in total):
>>> 
>>> 
>>> https://issues.apache.org/jira/issues/?jql=project%20%3D%20CALCITE%20AND%20status%20%3D%20Resolved%20AND%20fixVersion%20not%20in%20(1.20.0%2C1.21.0%2Cavatica-1.15.0%2Cavatica-go-4.0.0%2Cavatica-go-5.0.0)
>>> 
>>> I'll close them by the ascending order of JIRA ID respectively. If you
>>> used to participate in the discussion of any issue on the list, and you
>>> think it is too early to close, please feel free to reopen and add some
>>> follow-up comments.
>>> 
>>> Thanks,
>>> Hongze
>>> 
>>> 
>>> [1]
>>> https://lists.apache.org/thread.html/03f1e1937892e2ccbb30b639219122c28ca3c389e10aa01ed2214ca1@%3Cdev.calcite.apache.org%3E
>>> 

Clean up JIRA tickets

[Hongze Zhang]

Hi all,

As I have suggested on a related thread[1] started by Stamatis, I am planning 
to close the JIRA tickets that are marked as status "RESOLVED" but bound with 
an earlier fix version. Say, if a ticket is marked "RESOLVED", and its fix 
version is provided but not one of our unreleased versions 
1.20.0/1.21.0/avatica-1.15.0/avatica-go-4.0.0/avatica-go-5.0.0, it is going to 
be closed.

Here is a link to the full list (currently 78 tickets in total):

https://issues.apache.org/jira/issues/?jql=project%20%3D%20CALCITE%20AND%20status%20%3D%20Resolved%20AND%20fixVersion%20not%20in%20(1.20.0%2C1.21.0%2Cavatica-1.15.0%2Cavatica-go-4.0.0%2Cavatica-go-5.0.0)

I'll close them by the ascending order of JIRA ID respectively. If you used to 
participate in the discussion of any issue on the list, and you think it is too 
early to close, please feel free to reopen and add some follow-up comments.

Thanks,
Hongze


[1] 
https://lists.apache.org/thread.html/03f1e1937892e2ccbb30b639219122c28ca3c389e10aa01ed2214ca1@%3Cdev.calcite.apache.org%3E

[jira] [Created] (CALCITE-3029) Java-oriented field type is wrongly forced to be NOT NULL after being converted to SQL-oriented

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-3029:
-

 Summary: Java-oriented field type is wrongly forced to be NOT NULL 
after being converted to SQL-oriented
 Key: CALCITE-3029
 URL: https://issues.apache.org/jira/browse/CALCITE-3029
 Project: Calcite
  Issue Type: Bug
Affects Versions: 1.19.0
Reporter: Hongze Zhang
 Fix For: 1.20.0


A Java-oriented field type loses its nullable constraint after calling method 
{{org.apache.calcite.jdbc.JavaTypeFactoryImpl#toSql(org.apache.calcite.rel.type.RelDataType)}}.

It seems that this issue is introduced by CALCITE-2464 fix.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Re: [ANNOUNCE] Stamatis Zampetakis joins Calcite PMC

[Hongze Zhang]

Congratulations, Stamatis! And thank you so much for your thoughtful 
suggestions and kindly help on different aspects and discussions.

> On Apr 27, 2019, at 10:44, Francis Chuang  wrote:
> 
> I'm pleased to announce that Stamatis has accepted an invitation to
> join the Calcite PMC. Stamatis has been a consistent and helpful
> figure in the Calcite community for which we are very grateful. We
> look forward to the continued contributions and support.
> 
> Please join me in congratulating Stamatis!
> 
> - Francis (on behalf of the Calcite PMC)

Re: [ANNOUNCE] New committers: Ruben Quesada Lopez

[Hongze Zhang]

Congratulations, Ruben!

> On Apr 27, 2019, at 10:39, Francis Chuang  wrote:
> 
> 
> Apache Calcite's Project Management Committee (PMC) has invited Ruben Quesada 
> Lopez to become a committer, and we are pleased to announce that he has 
> accepted.
> 
> In just a few months, Ruben has contributed more than 15 pull requests to the 
> project, fixing bugs and implementing new features.
> 
> Ruben, welcome, thank you for your contributions, and we look forward your 
> further interactions with the community! If you wish, please feel free to 
> tell us more about yourself and what you are working on.
> 
> Francis (on behalf of the Apache Calcite PMC)

Re: [ANNOUNCE] New committers: Zhiwei Peng

[Hongze Zhang]

Congratulations, Zhiwei!

> On Apr 27, 2019, at 10:37, Francis Chuang  wrote:
> 
> Zhiwei Peng

Re: Function sets (aka flavor and dialect)

[Hongze Zhang]

I slightly prefer "function set" (should be "FunctionSet"?), as long as 
starting with "fun", which names the existing connection property.

Hongze

> On Apr 27, 2019, at 06:45, Yuzhao Chen  wrote:
> 
> Thx for the discussing, Julian
> 
> I’m also confused about the difference between SqlConformance and SqlDialect, 
> and now the Flavor, why not just use one word SqlDialect uniformly ? My 
> interpretation about sql dialect includes the functions, cause they are part 
> of the sql syntax as operators.
> 
> Best,
> Danny Chan
> 在 2019年4月27日 +0800 AM2:47，Michael Mior ，写道：
>> I think "function set" sounds like a reasonable name. My current
>> interpretation of dialect is that it's more related to the SQL syntax
>> accepted by each system. I'm not really sure what the intended
>> difference is between dialect and conformance is, but it seems like
>> perhaps these two concepts could be merged.
>> --
>> Michael Mior
>> mm...@apache.org
>> 
>> Le ven. 26 avr. 2019 à 14:14, Julian Hyde  a écrit :
>>> 
>>> There’s a discussion in https://issues.apache.org/jira/browse/CALCITE-2846 
>>>  about reorganizing the 
>>> Sql operator table. The idea is for people to be able to start a connection 
>>> with, say, the standard set of SQL functions, plus functions to emulate 
>>> MySQL, plus spatial functions. And for us to reorganize the code so that if 
>>> a function is in both MySQL and Oracle but not in the SQL Standard we only 
>>> define that function in one place.
>>> 
>>> We need a word for a "set of functions". (Standard, MySQL, Oracle and 
>>> Spatial are examples of sets of functions in the above paragraph.) It is 
>>> tempting to call this a dialect, but that word has an existing meaning that 
>>> we do not want to change. “Conformance” is another existing concept that we 
>>> need to work with. I suggested “flavor” in the JIRA case, but now I’m 
>>> thinking it is an arbitrary word that gives very little clue as to its 
>>> purpose. The concept is already exposed via the connect-string parameter 
>>> “fun" (e.g. “jdbc:calcite:fun=spatial,oracle”).
>>> 
>>> Any ideas for a better word, or a better way of organizing the dialect / 
>>> conformance / function set concepts.
>>> 
>>> Julian
>>> 

Re: JIRA usage reminders

[Hongze Zhang]

Thanks for the summing up, Stamatis. That helps a lot, and I am convinced by 
the advantages.

Also, I've re-checked the original definition[1] of the status flags CLOSED, 
RESOLVED, etc. Following is what "RESOLVED" is for:

> RESOLVED: The issue is considered finished, the resolution is correct. Issues 
> which are not closed can be reopened.

It seems that marking an issue as "RESOLVED" implies we have anyway provided a 
resolution, no matter whether "Fixed" the resolution is, so using a "RESOLVED" 
at first looks to be reasonable to me now.

Originally I wanted to debate on this because I see we (including myself ;) ) 
used a lot of "CLOSED"s[2] in similar cases these days, after finishing release 
of version 1.19. I can see the motivation of closing them directly, say, a JIRA 
contributor was pretty confident with the conclusion that the bug is 
invalid/duplicated/etc., so that the issue is no need to be reopened anymore. 
Should we still need RM to do a double-check in cases like this?

Besides, I also found some issues[3] that are created for quite a long time and 
marked as RESOLVED but never CLOSED. I prefer to go through and try closing 
them somehow after we finish this discussion.

Also I found we both have status "RESOLVED" and resolution "Resolved". Are we 
able to remove the latter? That's pretty confusing.

And +1 to not having the "next" fixing version. I am not sure if we can remove 
the flag from JIRA console, but I see we already locked some of "status" flags 
such as "ACCEPTED", "REVIEWABLE", etc..

Hongze

[1] https://issues.apache.org/jira/ShowConstantsHelp.jspa
[2] 
https://issues.apache.org/jira/browse/CALCITE-2821?jql=project%20%3D%20CALCITE%20AND%20status%20%3D%20Closed%20AND%20resolution%20in%20(Unresolved%2C%20%22Won%27t%20Fix%22%2C%20Duplicate%2C%20Invalid%2C%20Incomplete%2C%20%22Cannot%20Reproduce%22%2C%20Later%2C%20%22Not%20A%20Problem%22%2C%20Implemented%2C%20Done%2C%20%22Auto%20Closed%22%2C%20%22Pending%20Closed%22%2C%20REMIND%2C%20Resolved%2C%20%22Not%20A%20Bug%22%2C%20Workaround%2C%20Staged%2C%20Delivered%2C%20%22Information%20Provided%22%2C%20%22Works%20for%20Me%22%2C%20%22Feedback%20Received%22%2C%20%22Won%27t%20Do%22)%20ORDER%20BY%20created%20DESC%2C%20priority%20ASC%2C%20updated%20DESC
[3] 
https://issues.apache.org/jira/browse/CALCITE-1209?jql=project%20%3D%20CALCITE%20AND%20status%20%3D%20Resolved%20ORDER%20BY%20created%20ASC%2C%20priority%20ASC%2C%20updated%20DESC

> On Apr 27, 2019, at 02:13, Stamatis Zampetakis  wrote:
> 
> Thanks everybody for the feedback. I will try to gather up everything said
> here and complete the website.
> 
> @Hongze
> Using directly closed for duplicates, won't fix, etc., is a subject to
> debate so I am ok with any decision we take on this.
> 
> Summing up below some pros and cons.
> 
> Advantages:
> * Simplifies the resolution of issues since only the release manager is
> responsible for closing them.
> * Decreases the possibility of errors since the release manager can verify
> that the resolution status assigned to the issue is correct.
> 
> Disadvantages:
> * Adds more burden to the release manager;
> * Looks weird in combination with other fields such as the resolution
> status.
> 
> 
> @Francis
> The current instructions [3] mention the following
> 
> " If you are committed to fixing the issue before the upcoming release set
> the fix version accordingly (e.g., 1.20.0), otherwise leave it as blank."
> 
> which implies that "next" should never be used. We can try to make it more
> explicit and if possible lock its usage.
> 
> 
> [3] https://github.com/apache/calcite/blob/master/site/develop/index.md
> 
> On Thu, Apr 25, 2019 at 11:56 PM Francis Chuang 
> wrote:
> 
>> Thanks for getting this discussion started, Stamatis!
>> 
>> I was actually going to start some discussion regarding the "next" fix
>> version/release on JIRA after making Avatica 1.14.0 rc0 available for
>> voting, so I think this thread would be a good place to do so too.
>> 
>> There are currently 18 issues on JIRA with the fix version set to "next"
>> [1]. In my opinion this creates extra work for the release manager.
>> 
>> For me, I open the list of commits on Github and check that the
>> corresponding issue in JIRA (if any) has the correct fix version set and
>> has been resolved. Unfortunately, some issues were tagged as "next" and
>> I had to hunt those issues down individually and fix them, rather than
>> being able to get a list of them by visiting the avatica-1.14.0 page on
>> JIRA.In addition, the "next" version does not seem meaningful to me, as
>> we do know the version number of the next release. There also seem to be
>> a few pretty old issues set to "next" but have not been worked on in a
>> while. In my opinion, it would be better if these issues have their Fix
>> Version set to blank instead, as it may give the wrong impression (false
>> hope), that a fix is in progress.
>> 
>> What do you guys think? If the "next" fix version should not be used, 

Re: JIRA usage reminders

[Hongze Zhang]

Thank you very much for the summarizing, Stamatis! And +1 to documenting them 
to website.

But still one thing I'm not pretty much sure:

> There are cases where the JIRA issue may be solved in the discussion (or
> some other reason) without necessitating a change. In such cases, the
> contributor or committer involved in the discussion should:

>   - resolve the issue (not close it);
>   - ...

If a JIRA issue is both tagged "Resolution: Not A Bug/Problem" and "Fix 
version: 1.20.0", that is going to look wired to me. Because we rarely need to 
fix a non-existing bug/problem in a specific release. Should we use "closed" 
instead?


Thanks,
Hongze




> On Apr 25, 2019, at 21:51, Vladimir Sitnikov  
> wrote:
> 
> Stamatis> If you see things that are
> Stamatis> incorrect or that need to be done differently feel free to
> reply to this
> Stamatis> email.
> 
> LGTM.
> 
> Stamatis, thanks for the writeup, however I'm inclined to suppose it
> makes sense to put that on the website.
> 
> Such mails will be hard to find, especially for the ones who don't
> know such a mail exists at all.
> On the other hand, "/develop/" page is trivial to navigate even by
> just browsing the website.
> 
> Vladimir

Re: Using Calcite for JDBC SQL optimizer without execution

[Hongze Zhang]

Hi Andrew,

My feeling is that maybe what you actually need is the util class 
RelToSqlConverter[1]. But if you are using JdbcAdapter, it does not always 
translate the whole SQL you executed as you want. For example in this case[2], 
the logical plan is only partially pushed down to the source storage. Which 
means, the SQL Calcite uses to query your database is not always equivalent to 
your original input.

Best,
Hongze


[1] 
https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
[2] 
https://github.com/apache/calcite/blob/b03cdc486cf5c7232bbc6fa9b5f02f564e9601c3/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java#L426-L431

> On Apr 18, 2019, at 14:50, Andrew O  wrote:
> 
> Thanks. To try and illustrate a little more -  I have users creating ad-hoc
> SQL that provides the data they want, but may not be written optimally.
> I'd like to be able to use Calcite in some form to accept a SQL string
> from them and return an improved / optimised SQL string.
> 
> For my use case,  calcite can have a live connection to the DBMS to get
> schema information and and other metadata it needs for optimization
> purposes.   However I would like for it to avoid the normal final step of
> actually executing the user's query to fetch data.
> 
> Hopefully that explains the goal / context better,  but if not please let
> me know.
> 
> Regards
> 
> Andrew
> 
> On Thu, 18 Apr 2019, 05:34 Yuzhao Chen,  wrote:
> 
>> Andrew, I’m not very sure if I got your idea right.
>> 
>> Do you want a promoted `Sql` compared  to the original ? Then you should
>> transform Calcite’s RelNodes tree to sql, but Calcite do not support this
>> yet, The SqlNode unparse to sql is actually supported.
>> 
>> It is not that equivalent for RelNodes tree and sql text, e.g. How to
>> describe a SemiJoin in sql ? Another question is the pure text to data
>> source will actually have another planning phrase, we can not make sure
>> this plan is the best and efficient.
>> 
>> Best,
>> Danny Chan
>> 在 2019年4月18日 +0800 AM5:22，Andrew O ，写道：
>>> I'm interested in using Calcite in a "no execute" mode that would
>>> effectively return the optimized SQL but not actually execute it. The
>>> intention would be to leverage a) the query planning / optimization b)
>> the
>>> support for outputting different SQL dialects.
>>> 
>>> I can see some Tests (e.g. JdbcTest.java) use CalciteAssert and that it
>> has
>>> a check "planHasSql" function. This seems to use a Hook to capture the
>> sql
>>> as part of the execution flow, so is not quite what I'm trying to do.
>>> 
>>> I appreciate Calcite attempts to provide abstractions for different
>>> execution layers to allow cross adaptor function, so in theory a single
>> SQL
>>> statement may not always be available. However in my context I know it
>>> will be executing only against a single JDBC source and not other types.
>>> 
>>> Is there any suggested / recommended approaches to this, or pointers to
>>> bits of code to look at?
>>> 
>>> Thanks in advance
>>> 
>>> Andrew
>> 

Re: [DISCUSS] RelCompositeTrait

[Hongze Zhang]

You are right, removing the collations could only workaround what causes us to 
find the issue on multi-sort. Maybe we'd better not to remove them (at least 
currently) since they provide a way to easily test against composite trait.

Hongze

> On Apr 17, 2019, at 01:15, Haisheng Yuan  wrote:
> 
>> it looks like if we want to get these problems fixed quickly we can just 
>> remove
>> EnumerableValues's collation emitting.
> 
> I am afraid even removing Values collation enumeration won't actually give it 
> a quick fix,
> because Multi-sorted table, if there is, might still encounter the same issue 
> with Values.
> 
> 
> Thanks ~
> Haisheng Yuan
> ----------
> 发件人：Hongze Zhang
> 日　期：2019年04月16日 18:10:17
> 收件人：
> 主　题：Re: [DISCUSS] RelCompositeTrait
> 
> If we minimize the issue scope to Calcite itself, I think the 3 JIRA 
> tickets: CALCITE-2010, CALCITE-2593, CALCITE-2764 that Haisheng has 
> listed (thanks, Haisheng!) are all related to the multi-sorted 
> EnumerableValue more or less. An it looks like if we want to get these 
> problems fixed quickly we can just remove EnumerableValues's collation 
> emitting. I recall that (correct me if I am wrong) the rel is even not 
> able to emit descending collations, so I suppose it is not perfect at 
> first.
> 
> And another discussion is about enumerating traits. IMHO it's hard to 
> tell Calcite didn't really try avoiding enumerating them already. The 
> methods RelCollationImpl#satisfies[1] and RelDistributions#satisfies[2] 
> already did a job of testing the relationship between traits without 
> checking equality. So the whole thing is looking like we already tried 
> to not to enumerate them but failed at last.
> 
> Regarding the composite traits, one embarrassing thing I can see so far 
> is about the method RelTraitSet#simplify[3]. The JavaDoc says the method 
> is to "return a trait set similar to this one but with all composite 
> traits flattened". But when we look into the related implementations 
> RelCollationTraitDef#getDefault[4]/RelDistributionTraitDef#getDefault[5], 
> they seem not to flatten anything, the traits just simply get wiped. 
> This causes me to worry about if it is really correct that 
> RelCollation/RelDistribution extends RelMultipleTrait, because we can't 
> leverage the trait simplification but are actually hurt by it. If a rel 
> loses it's physical property, we can never prevent from adding 
> unnecessary sorts/exchanges.
> 
> Besides, even if we decide to add some extra sorts/exchanges, that is 
> somehow not easy so far. See CALCITE-2592/CALCITE-2970, the planner is 
> not that smooth to automatically add them.
> 
> Overall, regarding these "small" problems, I think none of them is 
> really impossible to be solved (yes coming up right solutions may be not 
> that straightforward). But of course in future if a brand new design can 
> be proposed to improve the entire trait system (such as avoid 
> enumerating traits), I think that would be totally a great thing.
> 
> Best,
> Hongze
> 
> 
> [1]https://github.com/apache/calcite/blob/9538374e8fae5cec7d6f7b270850f5dfb4c1fc06/core/src/main/java/org/apache/calcite/rel/RelCollationImpl.java#L118
> [2]https://github.com/apache/calcite/blob/9538374e8fae5cec7d6f7b270850f5dfb4c1fc06/core/src/main/java/org/apache/calcite/rel/RelDistributions.java#L143
> [3]https://github.com/apache/calcite/blob/9538374e8fae5cec7d6f7b270850f5dfb4c1fc06/core/src/main/java/org/apache/calcite/plan/RelTraitSet.java#L526-L538
> [4]https://github.com/apache/calcite/blob/9538374e8fae5cec7d6f7b270850f5dfb4c1fc06/core/src/main/java/org/apache/calcite/rel/RelCollationTraitDef.java#L58-L60
> [5]https://github.com/apache/calcite/blob/9538374e8fae5cec7d6f7b270850f5dfb4c1fc06/core/src/main/java/org/apache/calcite/rel/RelDistributionTraitDef.java#L47-L49
> 
> -- Original Message --
> From: "Haisheng Yuan" 
> To: "Jacques Nadeau" ; "Apache Calcite dev list" 
> 
> Sent: 2019/4/15 12:27:04
> Subject: Re: Re: [DISCUSS] RelCompositeTrait
> 
>>> There are major challenges with asking for particular traits as well.
>> Imagine a desired aggregate on 7 columns. What does the requestor request
>> with regards to distribution? All seven columns? One column? Some
>> combination in between?
>> 
>> The same challenges exist for enumerating all the traits as well. Imagine
>> there is an order by the 7 grouping keys on top of the aggregate on 7 
>> columns,
>> but with different sort direction:
>> select * from foo group by a,b,c... order by c desc, a asc, b desc...
>> What sort order, direction should the sort-based

Re: [DISCUSS] RelCompositeTrait

[Hongze Zhang]

If we minimize the issue scope to Calcite itself, I think the 3 JIRA 
tickets: CALCITE-2010, CALCITE-2593, CALCITE-2764 that Haisheng has 
listed (thanks, Haisheng!) are all related to the multi-sorted 
EnumerableValue more or less. An it looks like if we want to get these 
problems fixed quickly we can just remove EnumerableValues's collation 
emitting. I recall that (correct me if I am wrong) the rel is even not 
able to emit descending collations, so I suppose it is not perfect at 
first.


And another discussion is about enumerating traits. IMHO it's hard to 
tell Calcite didn't really try avoiding enumerating them already. The 
methods RelCollationImpl#satisfies[1] and RelDistributions#satisfies[2] 
already did a job of testing the relationship between traits without 
checking equality. So the whole thing is looking like we already tried 
to not to enumerate them but failed at last.


Regarding the composite traits, one embarrassing thing I can see so far 
is about the method RelTraitSet#simplify[3]. The JavaDoc says the method 
is to "return a trait set similar to this one but with all composite 
traits flattened". But when we look into the related implementations 
RelCollationTraitDef#getDefault[4]/RelDistributionTraitDef#getDefault[5], 
they seem not to flatten anything, the traits just simply get wiped. 
This causes me to worry about if it is really correct that 
RelCollation/RelDistribution extends RelMultipleTrait, because we can't 
leverage the trait simplification but are actually hurt by it. If a rel 
loses it's physical property, we can never prevent from adding 
unnecessary sorts/exchanges.


Besides, even if we decide to add some extra sorts/exchanges, that is 
somehow not easy so far. See CALCITE-2592/CALCITE-2970, the planner is 
not that smooth to automatically add them.


Overall, regarding these "small" problems, I think none of them is 
really impossible to be solved (yes coming up right solutions may be not 
that straightforward). But of course in future if a brand new design can 
be proposed to improve the entire trait system (such as avoid 
enumerating traits), I think that would be totally a great thing.


Best,
Hongze


[1]https://github.com/apache/calcite/blob/9538374e8fae5cec7d6f7b270850f5dfb4c1fc06/core/src/main/java/org/apache/calcite/rel/RelCollationImpl.java#L118
[2]https://github.com/apache/calcite/blob/9538374e8fae5cec7d6f7b270850f5dfb4c1fc06/core/src/main/java/org/apache/calcite/rel/RelDistributions.java#L143
[3]https://github.com/apache/calcite/blob/9538374e8fae5cec7d6f7b270850f5dfb4c1fc06/core/src/main/java/org/apache/calcite/plan/RelTraitSet.java#L526-L538
[4]https://github.com/apache/calcite/blob/9538374e8fae5cec7d6f7b270850f5dfb4c1fc06/core/src/main/java/org/apache/calcite/rel/RelCollationTraitDef.java#L58-L60
[5]https://github.com/apache/calcite/blob/9538374e8fae5cec7d6f7b270850f5dfb4c1fc06/core/src/main/java/org/apache/calcite/rel/RelDistributionTraitDef.java#L47-L49

-- Original Message --
From: "Haisheng Yuan" 
To: "Jacques Nadeau" ; "Apache Calcite dev list" 


Sent: 2019/4/15 12:27:04
Subject: Re: Re: [DISCUSS] RelCompositeTrait


 There are major challenges with asking for particular traits as well.

Imagine a desired aggregate on 7 columns. What does the requestor request
with regards to distribution? All seven columns? One column? Some
combination in between?

The same challenges exist for enumerating all the traits as well. Imagine
there is an order by the 7 grouping keys on top of the aggregate on 7 columns,
but with different sort direction:
select * from foo group by a,b,c... order by c desc, a asc, b desc...
What sort order, direction should the sort-based stream aggregate provide?
All ascending, all descending, order (a,b,c...), order(..c,b,a), or all the 
combination?
All of those enumerated traits are useless except one; for others, additional
sort operator will be needed.

Another example is aggregate on top of join, where join on 7 keys, and aggregate
on 2 of the join keys. In distributed system, what distribution trait would the 
join
operator provide? The 2 grouping keys? All the join keys? All the combination?

Enumerating some/all the deliverable traits, is not prupose driven. All the 
traits
may be just useless for parent operator. On the other hand, asking the child
operator particular traits, is purpose driven, at least the traits asked by 
parent
operator are worth consideration, not as wasteful as the former.

If I understand RelCompositeTrait's intent correctly, the enumerated traits, no
matter some combination or all combination, should be saved here. But in fact,
it seems not. And as Jacques mentioned, many people rely on RelMetadata
operations to pull up the traitsets through operators.

This makes me curious and wonder if there are any true use cases or systems
who rely on RelCompositeTrait. If someone has the story, we would love to hear.

Put that aside, even RelCompositeTrait is indispensible, why do we bother 

Re: Join, SemiJoin, Correlate

[Hongze Zhang]

I didn't take look on the PR in detail so far, but it seems that a topic about 
backward compatibility would be worth to discuss anyway.

Regarding the Enumerable's API, I don't think there are many use cases of them 
from Calcite users. Although users may create instances in some custom rules, 
or extend the Enumerable rels' classes to implement some specific behaviors, I 
am still not sure if such cases are that usual.

For example, I've run a Google search for term "EnumerableJoin.create" on 
github.com, only 2 results returned[1], and both are from apache/calcite 
project. Similar result on "EnumerableCorrelate.create". I am pretty sure that 
Google could not give a precise result about code usage (I don't find a way to 
search these terms using GitHub code search), but at least it shows some sort 
of trend. As a comparison there are 33 results[2] for "LogicalJoin.create", 
some are from external projects.

So my question would be: how much backward compatibility should we respect when 
we make API changes to Calcite? To me it is not much clear. I know 
compatibility is very, very important for an Apache project (see "The Apache 
Project Maturity Model/QU40"[3]), but I am not sure if we should add 
"@Depracated" to any changed public staffs, the code will be messy and hard to 
understand.

Anyway my example about EnumerableJoin/Correlate just shows my confusion on a 
broader topic. So I will be +1 to the consensus that already be achieved so 
far. But I'll be happy to hear more principles on how to manage the backward 
compatibility for Calcite, such as: what's the definition about Calcite's 
public API, or what changes would be considered backward-incompatible, etc. I 
think that will also benefit our developers a lot.


Best,
Hongze


[1] 
https://www.google.com/search?q=%22EnumerableJoin.create%22+site%3A%3Ahttps%3A%2F%2Fgithub.com
[2] 
https://www.google.com/search?q=%22LogicalJoin.create%22+site%3A%3Ahttps%3A%2F%2Fgithub.com
[3] 
https://community.apache.org/apache-way/apache-project-maturity-model.html#quality

> On Apr 14, 2019, at 14:53, Walaa Eldin Moustafa  wrote:
> 
> Agreed, but not sure what would the best way to do it be without
> making the code very confusing.
> 
> On Sat, Apr 13, 2019 at 2:46 PM Haisheng Yuan  wrote:
>> 
>> I share the same concern with you.
>> 
>> 
>> 
>> 
>> 
>> Thanks~
>> Haisheng 
>> Yuan--
>> 发件人：Stamatis Zampetakis
>> 日　期：2019年04月14日 05:37:29
>> 收件人：
>> 主　题：Re: Join, SemiJoin, Correlate
>> 
>> Hi Danny,
>> 
>> Thanks a lot for taking this on, it is a great start!
>> 
>> I didn't look thoroughly through the PR but I noticed that there are many
>> renaming/refactoring of public APIs. I am not sure if we should introduce
>> so many breaking changes without prior notice. A most conservative approach
>> would be to keep existing classes/methods, mark them as deprecated, and
>> then remove them in one of the coming releases. I am not sure if that is
>> the right way to go so let's see what the others have to say.
>> 
>> Best,
>> Stamatis
>> 
>> On Fri, Apr 12, 2019 at 9:18 AM Yuzhao Chen  wrote:
>> 
>>> Hi, @Haisheng Yuan, @Julian Hyde, @Stamatis Zampetakis,
>>> @Walaa Eldin Moustafa
>>> 
>>> I have did the work for this discussion, and look forward to your
>>> suggestions.
>>> 
>>> 
>>> ### Diff
>>> - Deprecate SemiJoin, EquiJoin, EnumerableSemiJoin, SemiJoinType,
>>> EnumerableSemiJoinRule, EnumerableThetaJoin
>>> - Make EnumerableMergeJoin extends Join instead of EquiJoin
>>> - Add SEMI and ANTI join type to JoinRelType, add method
>>> returnsJustFirstInput() to decide if the join only outputs left side
>>> - Correlate use JoinRelType instead of SemiJoinType
>>> - Rename EnumerableCorrelate to EnumerableNestedLoopJoin and make it
>>> exptends Join instead of Correlate
>>> - Rename EnumerableJoin to EnumerableHashJoin
>>> - EnumerableJoinRule will convert semi-join to EnumerableNestedLoopJoin
>>> (EnumerableSemiJoin's function is merged into this rule)
>>> - Add method isNonCorrelateSemiJoin() in Join.java to make sure if this
>>> join is a semi-join (Comes from SemiJoinRule) or comes from
>>> decorrelation(SubqueryRemoveRule or RelDecorrelator), the returns value
>>> true means the join is a semi-join equivalent to SemiJoin before this patch.
>>> - Cache the JoinInfo in Join and use it to get leftKeys and rightKeys,
>>> merge the SemiJoin#computeSelfCost to Join#computeSelfCost
>>> - RelBuilder removes SemiJoinFactory, method #semiJoin now return a
>>> LogicalJoin with JoinRelType#SEMI
>>> 
>>> ### Rules tweak
>>> - JoinAddRedundantSemiJoinRule now create LogicalJoin with
>>> JoinRelType#SEMI instead of SemiJoin
>>> - JoinToCorrelateRule remove SEMI instance and change the matchs condition
>>> to !join.getJoinType().generatesNullsOnLeft() which also allowed ANTI
>>> compared before this patch.
>>> - SemiJoinRule match SEMI join specificlly
>>> 
>>> ### Metadata tweak
>>> - RelMdAllPredicates, 

[jira] [Created] (CALCITE-2993) ParseException may be thrown for legal SQL queries due to incorrect "LOOKAHEAD(1)" hints

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2993:
-

 Summary: ParseException may be thrown for legal SQL queries due to 
incorrect "LOOKAHEAD(1)" hints
 Key: CALCITE-2993
 URL: https://issues.apache.org/jira/browse/CALCITE-2993
 Project: Calcite
  Issue Type: Bug
Reporter: Hongze Zhang
Assignee: Hongze Zhang
 Fix For: 1.20.0


In some cases, ParseException is thrown incorrectly:

Case 1
{code:sql}
select lead(x) ignore from t
{code}

Case 2 
{code:sql}
select *
  from t match_recognize
  (
 after match skip to last
pattern (strt down+ up+)
define
  down as down.price < PREV(down.price),
  up as up.price > prev(up.price)
  ) mr
{code}

Case 3
{code:sql}
select json_object(key: value) from t
{code}

And the failure on Case 1 is introduced by the fix of CALCITE-2847, Case 2/3 
are not.




--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Re: Snowflake Support

[Hongze Zhang]

Rajesh,

I am not very familiar with Snowflake but I think you need to extend 
Babel parser[1] to support Teradata Database's syntax first (also don't 
forget to specify Calcite JDBC property 
"parserFactory=org.apache.calcite.sql.parser.babel.SqlBabelParserImpl#FACTORY" 
to make sure the Babel parser is used). If you would like to use Calcite 
JDBC adapter (see example[2]) to connect to Snowflake, you should add 
Snowflake dialect support for Calcite. Maybe we don't have enough 
tutorials so far, but as an example you can have a look at a recent Git 
commit[3] about adding a dialect for Spark.


Best,
Hongze


[1] https://github.com/apache/calcite/tree/master/babel/src/main
[2] 
https://github.com/apache/calcite/blob/72e952d1a79ee2d7ba05de88cbc2ac11f65cd879/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcSchema.java#L472-L496
[3] 
https://github.com/apache/calcite/commit/8eb852039db04c132ae7a99943495f87cf39dfd2


-- Original Message --
From: "Rajeshkumar R" 
To: dev@calcite.apache.org
Sent: 2019/4/9 20:12:38
Subject: Snowflake Support


Hello

I would like to add snowflake dialect support to sqlparser in calcite. The
objective is to convert Teradata queries to snowflake dialect. Could you
please guide me to achieve this?

Thanks
Rajesh

Re: Is sort-merge join rule supports a non-equi join?

[Hongze Zhang]

Sorry I made a mistake : (. It seems that the rule adds filters 
actively[1] and does not accepts cartesian joins[2]. Besides the effects 
should be the same.


P.S. Correct a typo from my first email: equal join -> cartesian join

Hongze

[1] 
https://github.com/apache/calcite/blob/d7946a94adfd2e788f5d324910944dd65dab11ee/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeJoinRule.java#L93-L110
[2] 
https://github.com/apache/calcite/blob/d7946a94adfd2e788f5d324910944dd65dab11ee/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeJoinRule.java#L59-L61


-- Original Message --
From: "Hongze Zhang" 
To: dev@calcite.apache.org
Sent: 2019/4/1 12:00:04
Subject: Re: Is sort-merge join rule supports a non-equi join?

As far as you can see, the rule doesn't currently support non-equal 
join.


While you execute a SQL with non-equal join conditions, the rule can 
only handle the equal join generated by JoinExtractFilterRule[1]. So 
AFAIK you are not able to receive performance benefits by this rule so 
far unless you have sorted join input already.


Best,
Hongze


[1] 
https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/rules/JoinExtractFilterRule.java


-- Original Message --
From: "周来" 
To: dev@calcite.apache.org
Sent: 2019/4/1 11:26:48
Subject: Is sort-merge join rule supports a non-equi join?


I want to fire a sort-merge join rule to improve the performance for a
theta join,
but I found the comment of EnumerableMergeJoinRule.java  says
`// EnumerableMergeJoin only supports inner join.
 (It supports non-equi join, using a post-filter; see below.)`.

could someone tell me how to fire the rule for a  non-equi join?

Re: Is sort-merge join rule supports a non-equi join?

[Hongze Zhang]

As far as you can see, the rule doesn't currently support non-equal 
join.


While you execute a SQL with non-equal join conditions, the rule can 
only handle the equal join generated by JoinExtractFilterRule[1]. So 
AFAIK you are not able to receive performance benefits by this rule so 
far unless you have sorted join input already.


Best,
Hongze


[1] 
https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/rules/JoinExtractFilterRule.java


-- Original Message --
From: "周来" 
To: dev@calcite.apache.org
Sent: 2019/4/1 11:26:48
Subject: Is sort-merge join rule supports a non-equi join?


I want to fire a sort-merge join rule to improve the performance for a
theta join,
but I found the comment of EnumerableMergeJoinRule.java  says
`// EnumerableMergeJoin only supports inner join.
 (It supports non-equi join, using a post-filter; see below.)`.

could someone tell me how to fire the rule for a  non-equi join?

Re: Calcite doesn't work with LOOKAHEAD(3)

[Hongze Zhang]

Just out of my curiosity, could you please share your case about "LOOKAHEAD 
doest not work as expected"? Does changing to JavaCC 5.0 actually fixes the 
problem?

Thanks,
Hongze


> On Mar 31, 2019, at 19:17, Muhammad Gelbana  wrote:
> 
> I'm facing trouble with supporting selecting from table function for Babel
> parser and I beleive that LOOKAHEAD isn't working as expected too.
> I thought it might actually be a bug so I checked out the master branch and
> updated the JavaCC maven plugin version to 2.6 (it's currently 2.4), but
> that failed *142* test cases and errored *9*.
> 
> The plugin v2.4 imports the JavaCC library v4
> The plugin v2.6 imports the JavaCC library v5
> 
> Unfortunately the release notes for the JavaCC library are broken and I'm
> not aware of another source for the release notes for that project.
> Should I open a Jira to upgrade that plugin version ?
> 
> Thanks,
> Gelbana
> 
> 
> On Thu, Mar 28, 2019 at 4:18 AM Rui Li  wrote:
> 
>> Thanks Hongze, that's good to know.
>> 
>> On Thu, Mar 28, 2019 at 8:43 AM Hongze Zhang  wrote:
>> 
>>>> Besides, if I enable forceLaCheck, JavaCC suggests to use a lookahead
>> of
>>> 3
>>>> or more. I guess we'd better get rid of these warnings if we want to
>>> stick
>>>> to lookahead(2).
>>> 
>>> That makes sense. Actually we had a discussion[1] on moving to
>>> "LOOKAHEAD=1", and seems we are close to finish it. By doing this we have
>>> extra benefits that we don't need to turn forceLaCheck on and JavaCC
>> should
>>> give suggestions during maven build.
>>> 
>>> Hongze
>>> 
>>> 
>>> [1] https://issues.apache.org/jira/browse/CALCITE-2847
>>> 
>>>> On Mar 27, 2019, at 10:40, Rui Li  wrote:
>>>> 
>>>> Thanks Hongze for looking into the issue! Are you suggesting this is
>> more
>>>> likely to be a JavaCC bug?
>>>> I filed a ticket anyway in case we want to further track it:
>>>> https://issues.apache.org/jira/browse/CALCITE-2957
>>>> Besides, if I enable forceLaCheck, JavaCC suggests to use a lookahead
>> of
>>> 3
>>>> or more. I guess we'd better get rid of these warnings if we want to
>>> stick
>>>> to lookahead(2).
>>>> 
>>>> On Wed, Mar 27, 2019 at 8:54 AM Hongze Zhang 
>> wrote:
>>>> 
>>>>> Thanks, Yuzhao.
>>>>> 
>>>>> Since the more generic problem is that the production "E()"[1] causes
>>> the
>>>>> parent production's looking ahead returns too early, I tried to find a
>>> bad
>>>>> case of the same reason under current default setting LOOKAHEAD=2 but
>> it
>>>>> seems that under this number we didn't have a chance to meet the issue
>>> yet.
>>>>> 
>>>>> So after that I suggest to not to treat this as a Calcite's issue
>>>>> currently.
>>>>> 
>>>>> Best,
>>>>> Hongze
>>>>> 
>>>>> [1]
>>>>> 
>>> 
>> https://github.com/apache/calcite/blob/11c067f9992d9c8bc29e2326dd8b299ad1e9dbdc/core/src/main/codegen/templates/Parser.jj#L335
>>>>> 
>>>>>> On Mar 26, 2019, at 20:42, Yuzhao Chen  wrote:
>>>>>> 
>>>>>> Maybe we should fire a jira if it is a bug.
>>>>>> 
>>>>>> Best,
>>>>>> Danny Chan
>>>>>> 在 2019年3月26日 +0800 PM8:33，Hongze Zhang ，写道：
>>>>>>> Ops, correct a typo:
>>>>>>> 
>>>>>>> "... after uncommenting a line ..." -> "... after commenting a line
>>>>>>> ...".
>>>>>>> 
>>>>>>> Best,
>>>>>>> Hongze
>>>>>>> 
>>>>>>> -- Original Message --
>>>>>>> From: "Hongze Zhang" 
>>>>>>> To: dev@calcite.apache.org
>>>>>>> Sent: 2019/3/26 19:28:08
>>>>>>> Subject: Re: Calcite doesn't work with LOOKAHEAD(3)
>>>>>>> 
>>>>>>>> Firstly, thank you very much for sharing the case, Rui!
>>>>>>>> 
>>>>>>>> I have run a test with the SQL you provided and also run into the
>>> same
>>>>> exception (under a global LOOKAHEAD 3). After debugging the generated
>>>>> parser code,

Re: master build broken

[Hongze Zhang]

I see the CI of the last commit is passed now:
https://travis-ci.org/apache/calcite/builds/512347766?utm_source=github_status_medium=notification

But the build status of GitHub commit (the yellow point after "committed 
xx days ago") is still now being synced.



Hongze


-- Original Message --
From: "Chunwei Lei" 
To: "Yuzhao Chen" ; "dev" 
Sent: 2019/3/28 15:11:32
Subject: 回复：master build broken


Yes. I also find some updates in  https://www.traviscistatus.com/. Maybe we 
should just wait.



Bests,
Chunwei


--
发件人：Yuzhao Chen 
发送时间：2019年3月28日(星期四) 15:05
收件人：dev ; 雷春蔚(勿烦) 
主　题：Re: master build broken


It seems all the Travis buildings are stuck  at state Waiting to be queued.

Best,
Danny Chan
在 2019年3月28日 +0800 PM2:40，雷春蔚(勿烦) ，写道：
Hi,
 I notice that the travis-ci build of a new PR is much slower than before and 
Calcite master looks like still build broken. Is anybody looking into it?



 Bests,
 Chunwei

Re: Calcite doesn't work with LOOKAHEAD(3)

[Hongze Zhang]

> Besides, if I enable forceLaCheck, JavaCC suggests to use a lookahead of 3
> or more. I guess we'd better get rid of these warnings if we want to stick
> to lookahead(2).

That makes sense. Actually we had a discussion[1] on moving to "LOOKAHEAD=1", 
and seems we are close to finish it. By doing this we have extra benefits that 
we don't need to turn forceLaCheck on and JavaCC should give suggestions during 
maven build.

Hongze


[1] https://issues.apache.org/jira/browse/CALCITE-2847

> On Mar 27, 2019, at 10:40, Rui Li  wrote:
> 
> Thanks Hongze for looking into the issue! Are you suggesting this is more
> likely to be a JavaCC bug?
> I filed a ticket anyway in case we want to further track it:
> https://issues.apache.org/jira/browse/CALCITE-2957
> Besides, if I enable forceLaCheck, JavaCC suggests to use a lookahead of 3
> or more. I guess we'd better get rid of these warnings if we want to stick
> to lookahead(2).
> 
> On Wed, Mar 27, 2019 at 8:54 AM Hongze Zhang  wrote:
> 
>> Thanks, Yuzhao.
>> 
>> Since the more generic problem is that the production "E()"[1] causes the
>> parent production's looking ahead returns too early, I tried to find a bad
>> case of the same reason under current default setting LOOKAHEAD=2 but it
>> seems that under this number we didn't have a chance to meet the issue yet.
>> 
>> So after that I suggest to not to treat this as a Calcite's issue
>> currently.
>> 
>> Best,
>> Hongze
>> 
>> [1]
>> https://github.com/apache/calcite/blob/11c067f9992d9c8bc29e2326dd8b299ad1e9dbdc/core/src/main/codegen/templates/Parser.jj#L335
>> 
>>> On Mar 26, 2019, at 20:42, Yuzhao Chen  wrote:
>>> 
>>> Maybe we should fire a jira if it is a bug.
>>> 
>>> Best,
>>> Danny Chan
>>> 在 2019年3月26日 +0800 PM8:33，Hongze Zhang ，写道：
>>>> Ops, correct a typo:
>>>> 
>>>> "... after uncommenting a line ..." -> "... after commenting a line
>>>> ...".
>>>> 
>>>> Best,
>>>> Hongze
>>>> 
>>>> -- Original Message --
>>>> From: "Hongze Zhang" 
>>>> To: dev@calcite.apache.org
>>>> Sent: 2019/3/26 19:28:08
>>>> Subject: Re: Calcite doesn't work with LOOKAHEAD(3)
>>>> 
>>>>> Firstly, thank you very much for sharing the case, Rui!
>>>>> 
>>>>> I have run a test with the SQL you provided and also run into the same
>> exception (under a global LOOKAHEAD 3). After debugging the generated
>> parser code, I think the problem is probably in the generated LOOKAHEAD
>> method SqlParserImpl#jj_3R_42():
>>>>> 
>>>>> 
>>>>>> final private boolean jj_3R_42() {
>>>>>> if (!jj_rescan) trace_call("SqlSelect(LOOKING AHEAD...)");
>>>>>> if (jj_scan_token(SELECT)) { if (!jj_rescan)
>> trace_return("SqlSelect(LOOKAHEAD FAILED)"); return true; }
>>>>>> if (jj_3R_190()) { if (!jj_rescan) trace_return("SqlSelect(LOOKAHEAD
>> FAILED)"); return true; }
>>>>>> { if (!jj_rescan) trace_return("SqlSelect(LOOKAHEAD SUCCEEDED)");
>> return false; }
>>>>>> }
>>>>> 
>>>>> The LOOKAHEAD method checks only a single token . This is
>> definitely not enough since we have already set the number to 3.
>>>>> 
>>>>> Unfortunately I didn't find a root cause so far, but after
>> uncommenting a line[1] in production "SqlSelect()" then everything goes
>> back to normal. I'm inclined to believe JavaCC has some unexpected behavior
>> when dealing with LOOKAHEAD on a production with the shape like
>> "SqlSelectKeywords()"[2].
>>>>> 
>>>>> Please feel free to log a JIRA ticket with which we can track further
>> information of the issue.
>>>>> 
>>>>> Best,
>>>>> Hongze
>>>>> 
>>>>> 
>>>>> [1]
>> https://github.com/apache/calcite/blob/1b430721c0d9e22b2252ffcd893b42959cb7966c/core/src/main/codegen/templates/Parser.jj#L1030
>>>>> [2]
>> https://github.com/apache/calcite/blob/1b430721c0d9e22b2252ffcd893b42959cb7966c/core/src/main/codegen/templates/Parser.jj#L288
>>>>> 
>>>>> -- Original Message --
>>>>> From: "Rui Li" 
>>>>> To: dev@calcite.apache.org
>>>>> Sent: 2019/3/26 16:53:44
>>>>> Subject: Calcite doesn't work with LOOKAHEAD(3)
>>>>> 
>>>>>> Hi,
>>>>>> 
>>>>>> I'm trying to extend Calcite grammar to support some custom
>> statements. And
>>>>>> I need to increase LOOKAHEAD to 3 to resolve some ambiguity. But when
>> I did
>>>>>> that, the parser fails to parse queries like:
>>>>>> * select t.key from (select key from src) t*
>>>>>> 
>>>>>> With exception:
>>>>>> *Caused by: org.apache.calcite.sql.parser.impl.ParseException:*
>>>>>> *Encountered "( select key" at line 1, column 19.*
>>>>>> *Was expecting one of:*
>>>>>> *  ...*
>>>>>> *  ...*
>>>>>> *  ...*
>>>>>> *  ...*
>>>>>> *  ...*
>>>>>> * "LATERAL" ...*
>>>>>> * "(" "WITH" ...*
>>>>>> *...*
>>>>>> 
>>>>>> So I'm wondering whether there's some limitation on the LOOKAHEAD we
>> can
>>>>>> use?
>>>>>> 
>>>>>> --
>>>>>> Best regards!
>>>>>> Rui Li
>> 
>> 
> 
> -- 
> Best regards!
> Rui Li

Re: [DISCUSS] Towards Calcite 1.19.0

[Hongze Zhang]

Firstly, thanks for managing the release 1.19.0, Kevin!

I don't know which way is better between "merge" and "rebase" when 
unifying master and site, but it seems that we used a "rebase"[1] in 
1.18.0 release.


And an interesting thing is, the GitHub's comparison page[2] shows some 
inconsistency between the code diff and commit log: in the commit log, 
site branch is 7 commits ahead the master branch, but the code diff 
contains only the commits 07fcedb and 27706dd. So probably it is just 
caused by some imperfect display of the commit log?


Hongze


[1] 
https://lists.apache.org/thread.html/f21033b869ca97c1d094665a5c24fb8430b30483640840265a652fa3@%3Ccommits.calcite.apache.org%3E

[2] https://github.com/apache/calcite/compare/master...site

-- Original Message --
From: "Julian Hyde" 
To: "dev@calcite.apache.org" 
Sent: 2019/3/27 16:01:22
Subject: Re: [DISCUSS] Towards Calcite 1.19.0


I thought it would be straightforward.

Checkout site, then rebase onto master. The site branch should be a
subset of the commits on master branch, and those commits would turn
into no-ops.

On Wed, Mar 27, 2019 at 12:24 AM Stamatis Zampetakis  wrote:


 I would think that the following are sufficient:

 1. git ckeckout site
 2. git rebase -i b8f4edfcf107aa99f9e8007b3f26f2b94ba7d341
 3. pick the commits that should remain in the site normally just two:
 d 2afea1fc9 Site: Elastic query example on _MAP
 d 15a6d384f Site: Add Zoltan Haindrich as committer
 d d561dba0e Site: Add commit message guidelines for contributors (Stamatis
 Zampetakis)
 d a96db9117 Site: [CALCITE-2734] Update mongo documentation to reflect
 filename changes
 pick 27706dd07 Site: News item for release 1.19.0
 pick 07fcedb42 Site: Add new committers (Haisheng Yuan, Hongze Zhang and
 Stamatis Zampetakis)
 4. git push origin site -f

 The result should be similar to [1], I guess. However it requires
 force-pushing so I didn't do it directly to the apache repo
 before verifying that is the correct way.

 [1] https://github.com/zabetak/calcite/commits/site





 Στις Τετ, 27 Μαρ 2019 στις 4:07 π.μ., ο/η Francis Chuang <
 francischu...@apache.org> έγραψε:

 > Here's a list of the 7 commits that are "present" under the site branch,
 > but "missing" from master: https://github.com/apache/calcite/compare/site
 >
 > I think the main reason is that we followed the instructions under the
 > site folder and cherry-picked those commits from master into site. I
 > believe that we should pull those commits into site using rebase
 > instead. To even out the branches after a release, I think a rebase
 > should be used, so that there won't be a merge commit and both master
 > and site would be even.
 >
 > I also noticed a96db91 exist on the site branch, but the change is
 > exactly the same as e80ec97, so I think it can be removed.
 >
 > I am not 100% on this, but I think Julian should be able to clarify as
 > he was RM for the last Calcite release.
 >
 > If my assumption was correct, I think we can do the following to fix:
 > - Remove the offending commits (including the merge from master commit)
 > from the site branch
 > - Create a temp branch including commits up to 06b1894 from master
 > - Rebase the temp branch into site and delete the temp branch.
 > - Rebase my latest site commit (11c067f) into the site branch.
 >
 > On 27/03/2019 12:52 pm, Kevin Risden wrote:
 > > Maybe I screwed something up by cherry picking
 > > 27706dd078049601ea26cef7c45a95046719c444 into site branch? I didn't want
 > to
 > > remerge master again. Since just needed that one commit on the site
 > branch.
 > > According to the site readme, I thought we only had to cherry-pick
 > commits
 > > to the site branch between releases.
 > >
 > > Kevin Risden
 > >
 > >
 > > On Tue, Mar 26, 2019 at 7:57 PM Stamatis Zampetakis 
 > > wrote:
 > >
 > >> Thanks again Kevin.
 > >>
 > >> Regarding the differences between the master and the site it seems that
 > >> something is off.
 > >>
 > >> I think that it is normal to be 10 commits behind master since from the
 > >> time that master was merged into site there have been 10 commits on the
 > >> master.
 > >>
 > >> On the other hand the 7 commits ahead is bit weird. Normally, it should
 > be
 > >> 3 I think (only those made after the merge of master into site).
 > >>
 > >> By doing
 > >>
 > >> git log master..site
 > >>
 > >> I get the following which shows that some commits exist in the site but
 > not
 > >> in the master.
 > >>
 > >> commit d09ea01b8c79dd162c40ad4bc0dc7f2d9ae18214 (HEAD -> site,
 &

Re: Calcite doesn't work with LOOKAHEAD(3)

[Hongze Zhang]

Thanks, Yuzhao.

Since the more generic problem is that the production "E()"[1] causes the 
parent production's looking ahead returns too early, I tried to find a bad case 
of the same reason under current default setting LOOKAHEAD=2 but it seems that 
under this number we didn't have a chance to meet the issue yet. 

So after that I suggest to not to treat this as a Calcite's issue currently.

Best,
Hongze

[1] 
https://github.com/apache/calcite/blob/11c067f9992d9c8bc29e2326dd8b299ad1e9dbdc/core/src/main/codegen/templates/Parser.jj#L335

> On Mar 26, 2019, at 20:42, Yuzhao Chen  wrote:
> 
> Maybe we should fire a jira if it is a bug.
> 
> Best,
> Danny Chan
> 在 2019年3月26日 +0800 PM8:33，Hongze Zhang ，写道：
>> Ops, correct a typo:
>> 
>> "... after uncommenting a line ..." -> "... after commenting a line
>> ...".
>> 
>> Best,
>> Hongze
>> 
>> -- Original Message --
>> From: "Hongze Zhang" 
>> To: dev@calcite.apache.org
>> Sent: 2019/3/26 19:28:08
>> Subject: Re: Calcite doesn't work with LOOKAHEAD(3)
>> 
>>> Firstly, thank you very much for sharing the case, Rui!
>>> 
>>> I have run a test with the SQL you provided and also run into the same 
>>> exception (under a global LOOKAHEAD 3). After debugging the generated 
>>> parser code, I think the problem is probably in the generated LOOKAHEAD 
>>> method SqlParserImpl#jj_3R_42():
>>> 
>>> 
>>>> final private boolean jj_3R_42() {
>>>> if (!jj_rescan) trace_call("SqlSelect(LOOKING AHEAD...)");
>>>> if (jj_scan_token(SELECT)) { if (!jj_rescan) 
>>>> trace_return("SqlSelect(LOOKAHEAD FAILED)"); return true; }
>>>> if (jj_3R_190()) { if (!jj_rescan) trace_return("SqlSelect(LOOKAHEAD 
>>>> FAILED)"); return true; }
>>>> { if (!jj_rescan) trace_return("SqlSelect(LOOKAHEAD SUCCEEDED)"); return 
>>>> false; }
>>>> }
>>> 
>>> The LOOKAHEAD method checks only a single token . This is 
>>> definitely not enough since we have already set the number to 3.
>>> 
>>> Unfortunately I didn't find a root cause so far, but after uncommenting a 
>>> line[1] in production "SqlSelect()" then everything goes back to normal. 
>>> I'm inclined to believe JavaCC has some unexpected behavior when dealing 
>>> with LOOKAHEAD on a production with the shape like "SqlSelectKeywords()"[2].
>>> 
>>> Please feel free to log a JIRA ticket with which we can track further 
>>> information of the issue.
>>> 
>>> Best,
>>> Hongze
>>> 
>>> 
>>> [1] 
>>> https://github.com/apache/calcite/blob/1b430721c0d9e22b2252ffcd893b42959cb7966c/core/src/main/codegen/templates/Parser.jj#L1030
>>> [2] 
>>> https://github.com/apache/calcite/blob/1b430721c0d9e22b2252ffcd893b42959cb7966c/core/src/main/codegen/templates/Parser.jj#L288
>>> 
>>> -- Original Message --
>>> From: "Rui Li" 
>>> To: dev@calcite.apache.org
>>> Sent: 2019/3/26 16:53:44
>>> Subject: Calcite doesn't work with LOOKAHEAD(3)
>>> 
>>>> Hi,
>>>> 
>>>> I'm trying to extend Calcite grammar to support some custom statements. And
>>>> I need to increase LOOKAHEAD to 3 to resolve some ambiguity. But when I did
>>>> that, the parser fails to parse queries like:
>>>> * select t.key from (select key from src) t*
>>>> 
>>>> With exception:
>>>> *Caused by: org.apache.calcite.sql.parser.impl.ParseException:*
>>>> *Encountered "( select key" at line 1, column 19.*
>>>> *Was expecting one of:*
>>>> *  ...*
>>>> *  ...*
>>>> *  ...*
>>>> *  ...*
>>>> *  ...*
>>>> * "LATERAL" ...*
>>>> * "(" "WITH" ...*
>>>> *...*
>>>> 
>>>> So I'm wondering whether there's some limitation on the LOOKAHEAD we can
>>>> use?
>>>> 
>>>> --
>>>> Best regards!
>>>> Rui Li

Re: Calcite doesn't work with LOOKAHEAD(3)

[Hongze Zhang]

Ops, correct a typo:

"... after uncommenting a line ..." -> "... after commenting a line 
...".


Best,
Hongze

-- Original Message ------
From: "Hongze Zhang" 
To: dev@calcite.apache.org
Sent: 2019/3/26 19:28:08
Subject: Re: Calcite doesn't work with LOOKAHEAD(3)


Firstly, thank you very much for sharing the case, Rui!

I have run a test with the SQL you provided and also run into the same 
exception (under a global LOOKAHEAD 3). After debugging the generated parser 
code, I think the problem is probably in the generated LOOKAHEAD method 
SqlParserImpl#jj_3R_42():


> final private boolean jj_3R_42() {
> if (!jj_rescan) trace_call("SqlSelect(LOOKING AHEAD...)");
> if (jj_scan_token(SELECT)) { if (!jj_rescan) trace_return("SqlSelect(LOOKAHEAD 
FAILED)"); return true; }
> if (jj_3R_190()) { if (!jj_rescan) trace_return("SqlSelect(LOOKAHEAD 
FAILED)"); return true; }
> { if (!jj_rescan) trace_return("SqlSelect(LOOKAHEAD SUCCEEDED)"); return 
false; }
> }

The LOOKAHEAD method checks only a single token . This is definitely 
not enough since we have already set the number to 3.

Unfortunately I didn't find a root cause so far, but after uncommenting a line[1] in production 
"SqlSelect()" then everything goes back to normal. I'm inclined to believe JavaCC has 
some unexpected behavior when dealing with LOOKAHEAD on a production with the shape like 
"SqlSelectKeywords()"[2].

Please feel free to log a JIRA ticket with which we can track further 
information of the issue.

Best,
Hongze


[1] 
https://github.com/apache/calcite/blob/1b430721c0d9e22b2252ffcd893b42959cb7966c/core/src/main/codegen/templates/Parser.jj#L1030
[2] 
https://github.com/apache/calcite/blob/1b430721c0d9e22b2252ffcd893b42959cb7966c/core/src/main/codegen/templates/Parser.jj#L288

-- Original Message --
From: "Rui Li" 
To: dev@calcite.apache.org
Sent: 2019/3/26 16:53:44
Subject: Calcite doesn't work with LOOKAHEAD(3)


Hi,

I'm trying to extend Calcite grammar to support some custom statements. And
I need to increase LOOKAHEAD to 3 to resolve some ambiguity. But when I did
that, the parser fails to parse queries like:
*select t.key from (select key from src) t*

With exception:
*Caused by: org.apache.calcite.sql.parser.impl.ParseException:*
*Encountered "( select key" at line 1, column 19.*
*Was expecting one of:*
* ...*
* ...*
* ...*
* ...*
* ...*
*"LATERAL" ...*
*"(" "WITH" ...*
*...*

So I'm wondering whether there's some limitation on the LOOKAHEAD we can
use?

--
Best regards!
Rui Li

Re: Calcite doesn't work with LOOKAHEAD(3)

[Hongze Zhang]

Firstly, thank you very much for sharing the case, Rui!

I have run a test with the SQL you provided and also run into the same 
exception (under a global LOOKAHEAD 3). After debugging the generated 
parser code, I think the problem is probably in the generated LOOKAHEAD 
method SqlParserImpl#jj_3R_42():



> final private boolean jj_3R_42() {
> if (!jj_rescan) trace_call("SqlSelect(LOOKING AHEAD...)");
> if (jj_scan_token(SELECT)) { if (!jj_rescan) 
trace_return("SqlSelect(LOOKAHEAD FAILED)"); return true; }
> if (jj_3R_190()) { if (!jj_rescan) trace_return("SqlSelect(LOOKAHEAD 
FAILED)"); return true; }
> { if (!jj_rescan) trace_return("SqlSelect(LOOKAHEAD SUCCEEDED)"); 
return false; }

> }

The LOOKAHEAD method checks only a single token . This is 
definitely not enough since we have already set the number to 3.


Unfortunately I didn't find a root cause so far, but after uncommenting 
a line[1] in production "SqlSelect()" then everything goes back to 
normal. I'm inclined to believe JavaCC has some unexpected behavior when 
dealing with LOOKAHEAD on a production with the shape like 
"SqlSelectKeywords()"[2].


Please feel free to log a JIRA ticket with which we can track further 
information of the issue.


Best,
Hongze


[1] 
https://github.com/apache/calcite/blob/1b430721c0d9e22b2252ffcd893b42959cb7966c/core/src/main/codegen/templates/Parser.jj#L1030
[2] 
https://github.com/apache/calcite/blob/1b430721c0d9e22b2252ffcd893b42959cb7966c/core/src/main/codegen/templates/Parser.jj#L288


-- Original Message --
From: "Rui Li" 
To: dev@calcite.apache.org
Sent: 2019/3/26 16:53:44
Subject: Calcite doesn't work with LOOKAHEAD(3)


Hi,

I'm trying to extend Calcite grammar to support some custom statements. And
I need to increase LOOKAHEAD to 3 to resolve some ambiguity. But when I did
that, the parser fails to parse queries like:
*select t.key from (select key from src) t*

With exception:
*Caused by: org.apache.calcite.sql.parser.impl.ParseException:*
*Encountered "( select key" at line 1, column 19.*
*Was expecting one of:*
* ...*
* ...*
* ...*
* ...*
* ...*
*"LATERAL" ...*
*"(" "WITH" ...*
*...*

So I'm wondering whether there's some limitation on the LOOKAHEAD we can
use?

--
Best regards!
Rui Li

Re: Calcite-Master - Build # 1078 - Still Failing

[Hongze Zhang]

Thank you for your quick response, Kevin!

As no one objects so far, I've created a JIRA ticket[1] for the issue 
and the proposal, but unfortunately I can't do the update by myself 
because of not owning access to the Jenkins admin currently. could you 
or anyone else please help with it? Thank you very much.


Besides, I tried to compare the build time between "mvn verify" and "mvn 
clean verify" but it seems that the master branch's build is now blocked 
by CALCITE-2953 on which Haisheng and Jesus has already been working.
And I have tried both the commands on my personal machine (VM, 6 cores, 
8G). "mvn clean verify" costs 8m19s, and "mvn verify" costs 6m7s. The 
former seems to be longer but still tolerable to me. So I suggest to 
make the change and see how it behaves on the Jenkins nodes.



Thanks,
Hongze


[1] https://issues.apache.org/jira/browse/CALCITE-2956

-- Original Message --
From: "Kevin Risden" 
To: dev@calcite.apache.org
Sent: 2019/3/26 1:38:24
Subject: Re: Calcite-Master - Build # 1078 - Still Failing


I don't think there are any downsides to changing to "mvn clean verify ..."
other than the builds might take a bit longer.

Kevin Risden


On Mon, Mar 25, 2019 at 1:36 PM Hongze Zhang  wrote:


 Hi all,

 This is caused by a known problem which is analyzed in mail thread[1], and
 it seems that the problem keeps failing our Jenkins build for a period of
 time. I had tried cleaning Calcite's workspaces on several slave nodes but
 it could not prevent the same error happening on other unexpected nodes.

 As a possible solution, do you think it's feasible to change our Jenkins
 build command from "mvn verify ..." to "mvn clean verify ..."?

 Best,
 Hongze


 [1]
 
https://lists.apache.org/thread.html/%3C455426799.3105.1552292851062.JavaMail.jenkins@jenkins02%3E


 > On Mar 25, 2019, at 23:29, Apache Jenkins Server <
 jenk...@builds.apache.org> wrote:
 >
 > The Apache Jenkins build system has built Calcite-Master (build #1078)
 >
 > Status: Still Failing
 >
 > Check console output at
 https://builds.apache.org/job/Calcite-Master/1078/ to view the results.

[jira] [Created] (CALCITE-2956) Jenkins failure caused by cached classes generated by previous build

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2956:
-

 Summary: Jenkins failure caused by cached classes generated by 
previous build
 Key: CALCITE-2956
 URL: https://issues.apache.org/jira/browse/CALCITE-2956
 Project: Calcite
  Issue Type: Bug
Reporter: Hongze Zhang


Here is the Jenkins output including the error caused by the cached class files:
https://builds.apache.org/job/Calcite-Master/1078/jdk=JDK%201.8%20(latest),label_exp=ubuntu&&!cloud-slave&&!H27/console

This was discussed in several threads such as [1] and [2].

[1] 
https://lists.apache.org/thread.html/%3C455426799.3105.1552292851062.JavaMail.jenkins@jenkins02%3E
[2]
https://lists.apache.org/thread.html/cdf06554f83ea22a39e4133562f14e9ad5a04be6adc15e4032198c92@%3Cdev.calcite.apache.org%3E



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Re: [ANNOUNCE] New committers: Hongze Zhang

[Hongze Zhang]

Thank you very much for your introduction (And no apology necessary :) ), 
Francis ! And thank you all for the kind words.

I am currently working at Tencent. During the last few months, I have been 
focusing on providing near-standard SQL support for an existing streaming 
compute engine. We introduce Calcite to the engine and it now works pretty 
well. 

It is a great honor for me to become a committer of Apache Calcite. I also look 
forward to contribute more in future.

Thanks,
Hongze

> On Mar 26, 2019, at 07:46, Stamatis Zampetakis  wrote:
> 
> Congrats Hongze and welcome on board.
> 
> Calcite support for JSON is getting better every day and there are many
> traits of you behind all that.
> Thanks a lot for your work on Calcite and looking forward working with you.
> 
> Best,
> Stamatis
> 
> Στις Δευ, 25 Μαρ 2019 στις 10:28 μ.μ., ο/η Francis Chuang <
> francischu...@apache.org> έγραψε:
> 
>> (Note: This was supposed to be sent last month, but I've somehow
>> forgotten. Please accept my apologies.)
>> 
>> Apache Calcite's Project Management Committee (PMC) has invited Hongze
>> Zhang to become a committer, and we are pleased to announce that he has
>> accepted.
>> 
>> Hongze has been a consistent contributor to Calcite, being responsible
>> for fixing various bugs and some pretty big patches. In addition, he has
>> been reviewing PRs and participating in discussions on our mailing lists.
>> 
>> Hongze, welcome, thank you for your contributions, and we look forward
>> your further interactions with the community! If you wish, please feel
>> free to tell us more about yourself and what you are working on.
>> 
>> Francis (on behalf of the Apache Calcite PMC)
>> 

Re: Calcite-Master - Build # 1078 - Still Failing

[Hongze Zhang]

Hi all,

This is caused by a known problem which is analyzed in mail thread[1], and it 
seems that the problem keeps failing our Jenkins build for a period of time. I 
had tried cleaning Calcite's workspaces on several slave nodes but it could not 
prevent the same error happening on other unexpected nodes.

As a possible solution, do you think it's feasible to change our Jenkins build 
command from "mvn verify ..." to "mvn clean verify ..."?

Best,
Hongze


[1] 
https://lists.apache.org/thread.html/%3C455426799.3105.1552292851062.JavaMail.jenkins@jenkins02%3E


> On Mar 25, 2019, at 23:29, Apache Jenkins Server  
> wrote:
> 
> The Apache Jenkins build system has built Calcite-Master (build #1078)
> 
> Status: Still Failing
> 
> Check console output at https://builds.apache.org/job/Calcite-Master/1078/ to 
> view the results.

[jira] [Created] (CALCITE-2933) In Druid adapter, expression like "cast(cast(\"timestamp\" as timestamp) as varchar)" returns as epoch millisecond

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2933:
-

 Summary: In Druid adapter, expression like 
"cast(cast(\"timestamp\" as timestamp) as varchar)" returns as epoch millisecond
 Key: CALCITE-2933
 URL: https://issues.apache.org/jira/browse/CALCITE-2933
 Project: Calcite
  Issue Type: Improvement
        Reporter: Hongze Zhang


SQL 1:
{code}
select cast("timestamp" as timestamp) as t from "foodmart" order by t limit 1
{code}
Result:
{code}
T=1997-01-01 00:00:00
{code}
SQL 2:
{code}
select cast(cast("timestamp" as timestamp) as varchar) as t from "foodmart" 
order by t limit 1
{code}
Result:
{code}
T=85207680
{code}

The second query should returns the same value as the first one.




--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Re: Calcite-Master - Build # 1070 - Still Failing

[Hongze Zhang]

Hi Stamatis,

I am not much familiar with Calcite's Jenkins CI currently, but I can 
see the reason of the build error is that some cached classes generated 
by old code reference the class SequencedAdderAggregateLambdaFactory 
that has been renamed already.


I have wiped out the workspace on slave nodes H20, H22, H24, now during 
the latest build the error seems went away[1]. But do you or anyone know 
a way to clean the workspace on all possible build slave nodes? I am not 
sure if what I have done is enough to prevent that the same problem 
occurs again.


Best,
Hongze


[1] https://builds.apache.org/job/Calcite-Master/1072/

-- Original Message --
From: "Hongze Zhang" 
To: dev@calcite.apache.org
Sent: 2019/3/11 17:42:43
Subject: Re: Calcite-Master - Build # 1070 - Still Failing


Stamatis,

Thank you for reminding me, working on finding a reason.

Best,
Hongze

-- Original Message --
From: "Stamatis Zampetakis" 
To: dev@calcite.apache.org
Sent: 2019/3/11 17:05:12
Subject: Re: Calcite-Master - Build # 1070 - Still Failing


Jenkis fails quite often lately and many times with the following error:

java.lang.ClassNotFoundException:
org.apache.calcite.adapter.enumerable.SequencedAdderAggregateLambdaFactory

It seems related with the following commit:

https://github.com/apache/calcite/commit/f992e6c8fbc3712c6bd70096fb6b05da448b2cab

where this class was renamed.

Can you please have a look Hongze Zhang?

Best,
Stamatis




Στις Δευ, 11 Μαρ 2019 στις 9:27 π.μ., ο/η Apache Jenkins Server <
jenk...@builds.apache.org> έγραψε:


 The Apache Jenkins build system has built Calcite-Master (build #1070)

 Status: Still Failing

 Check console output at https://builds.apache.org/job/Calcite-Master/1070/
 to view the results.

Re: Calcite-Master - Build # 1070 - Still Failing

[Hongze Zhang]

Stamatis,

Thank you for reminding me, working on finding a reason.

Best,
Hongze

-- Original Message --
From: "Stamatis Zampetakis" 
To: dev@calcite.apache.org
Sent: 2019/3/11 17:05:12
Subject: Re: Calcite-Master - Build # 1070 - Still Failing


Jenkis fails quite often lately and many times with the following error:

java.lang.ClassNotFoundException:
org.apache.calcite.adapter.enumerable.SequencedAdderAggregateLambdaFactory

It seems related with the following commit:

https://github.com/apache/calcite/commit/f992e6c8fbc3712c6bd70096fb6b05da448b2cab

where this class was renamed.

Can you please have a look Hongze Zhang?

Best,
Stamatis




Στις Δευ, 11 Μαρ 2019 στις 9:27 π.μ., ο/η Apache Jenkins Server <
jenk...@builds.apache.org> έγραψε:


 The Apache Jenkins build system has built Calcite-Master (build #1070)

 Status: Still Failing

 Check console output at https://builds.apache.org/job/Calcite-Master/1070/
 to view the results.

Re: Calcite-RelNode clarifications

[Hongze Zhang]

If the function TIMESTAMPADD is not supported in Oracle, in general the 
OracleSqlDialect should translate it to some other expressions or calls. But it 
seems that currently the dialect does not provide the translation[1]. 

It would be great if you can log a JIRA case to describe your issue, or even 
contribute a PR to solve the problem.


Best,
Hongze


[1] 
https://github.com/apache/calcite/blob/50012d221d6e9a5e42a957ab0723e0f706b25be3/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java#L55

> On Mar 8, 2019, at 7:13 PM, Rakesh Nair  wrote:
> 
> TIMESTAMPADD

Re[2]: Calcite-RelNode clarifications

[Hongze Zhang]

Hi RamKrishna,

As you said:

> So, basically I need to add 2 Hours to CurrentTime.

I think the generated function call "TIMESTAMPADD(HOUR, 2, 
CURRENT_TIMESTAMP)" already did that.
If what you want is to compare it's value with a MySQL's Date (with 
format -xx-xx), based on Christopher's suggestion I think you can 
write:


```
builder.call(SqlStdOperatorTable.FLOOR, ImmutableList.of(timeAdd, 
builder.getRexBuilder().makeFlag(TimeUnitRange.DAY)))

```

Calcite will generate MySQL-dialect SQL like 
"DATE_FORMAT(TIMESTAMPADD(HOUR, 2, CURRENT_TIMESTAMP), '%Y-%m-%d')" 
which produces String result with Date precision. Or you can simply use 
operator CAST:


```
builder.cast(timeAdd, SqlTypeName.DATE))
```
to extract Date part from the variable "timeAdd". Both of the two ways 
should give you result that is comparable with coulmn "HIRE_TIME".


Best,
Hongze

-- Original Message --
From: "Rakesh Nair" 
To: dev@calcite.apache.org
Sent: 2019/3/8 16:34:47
Subject: Re: Calcite-RelNode clarifications


Hello Mr. Baynes,
Thanks for responding.
1. Luckily I was able to solve it on my own, thanks anyway. Moving on..
2. Let me explain my scenario in detail, you see we have an application
that queries multiple dbs like Mysql, Oracle, etc..with some static queries
which we are maintaining in their respective dialects for all these dbs.
Now we're trying to maintain a Generic RelNode class for different queries
and then convert it to respective Db dialect as and when required. For eg:
If I need to run a *select * from `emp`* query in Mysql. I will create a
relNode like so,
*RelNode node = builder.scan("emp").build();*  and then convert it to its
corresponding Mysql query using,

*SqlDialect dialect = SqlDialect.DatabaseProduct.MYSQL.getDialect();*
*RelToSqlConverter converter = new RelToSqlConverter(dialect);*
*SqlNode sqlNode = converter.visitChild(0, node).asStatement();*
*String query = sqlNode.toSqlString(dialect).getSql();*
If I need to convert the same query to its Oracle counterpart i just need
to initialise dialect as Oracle dialect. Now for most of the queries, I've
been able to create its corresponding RelNode. But in some queries I'm
getting stuck which I've mentioned as below:
Now I have another MySql query like so,
*SELECT * FROM `emp` WHERE HIRE_TIME = DATE_ADD( NOW(), INTERVAL 2 HOUR);*
Now I woud like to create the corresponding RelNode for this query. So far
this is what I've been able to do:

*builder.scan("emp");*
*ImmutableList constExps =
ImmutableList.of(builder.getRexBuilder().makeFlag(TimeUnitRange.HOUR),
builder.literal(2), builder.call(SqlStdOperatorTable.CURRENT_TIMESTAMP));*

*RelDataType any = builder.getTypeFactory().createSqlType(SqlTypeName.ANY);*

*RexNode timeAdd = builder.getRexBuilder().makeCall(any,
SqlStdOperatorTable.TIMESTAMP_ADD, constExps);*
*RelNode node = builder.filter(builder.equals(builder.field("HIRE_TIME"),
timeAdd)).build();*

And the corresponding MySql is coming out to be like this:

*SELECT * FROM `db`.`emp` WHERE `HIRE_TIME` = TIMESTAMPADD(HOUR, 2,
CURRENT_TIMESTAMP) *which is clearly not correct.

So, basically I need to add 2 Hours to CurrentTime. Can you explain how to
accomplish this using RelBuilder(with a RelNode example if possible).

Thanks and Regards,
RamKrishna.

On Thu, Mar 7, 2019 at 9:34 PM Chris Baynes  wrote:


 1. Could you give an example of what your expected output/query is here?
 I'm not sure what you're trying to achieve.
 2. There are mappings in MysqlSqlDialect.java from Calcite FLOOR to Mysql
 DATE_FORMAT. So what you want in your RelNode is something like
 FLOOR(HIRE_DATE TO day)

 On Tue, Mar 5, 2019 at 6:41 PM Rakesh Nair  wrote:

 > Hi,
 > Sorry to be a bother, but I've been sitting on this for quite a while now
 > and would really like to clear it. So I've been trying to use the
 > RelBuilder for building relational expressions.
 > 1. I'm trying to build the relational expressions for using Trim()
 > function. This si what I've done so far:
 > RelNode node =
 > builder.scan("emp").project(builder.call(SqlStdOperatorTable.TRIM,
 > builder.getRexBuilder().makeFlag(Flag.BOTH),builder.literal(" "),
 > builder.field("EMPNAME"))).build();
 > Query Explain:
 > LogicalProject($f0=[TRIM(FLAG(BOTH), ' ', $2)])
 >   LogicalTableScan(table=[[hr, emp]])
 > Corresponding Mysql Query:
 > SELECT TRIM(BOTH ' ' FROM `EMPNAME`) AS `$f0`
 > FROM `hr`.`emp`
 > Could somebody tell me what I'm doing wrong here?
 > 2. How can I convert the following Mysql Query:
 > SELECT DATE_FORMAT(HIRE_DATE,'%Y/%m/%d'), EMPNAME FROM `emp` WHERE
 > DATE_FORMAT(HIRE_DATE,'%Y/%m/%d') = DATE_FORMAT(NOW(),'%Y/%m/%d');
 > to its corresponding Relational Algebra using RelBuilder? Simply put how
 > can i convert the date formats in RelBuilder?
 >
 > Thanks and Regards,
 > RamKrishna.
 >


 --

 *Christopher Baynes*
 Lead Developer

 *Contiamo – all your data in one place*

 Stresemannstraße 123 (c/o WeWork) | 10963 Berlin | Germany

 E-mail:  ch...@contiamo.com

 Web: 

Re: JIRAs and Pull Requests Cleanup

[Hongze Zhang]

There is a Github robot "Stale"[1][2] for closing inactive PRs automatically.

Below are some Apache projects already using it:

1. https://github.com/apache/incubator-druid/blob/master/.github/stale.yml
2. https://github.com/apache/airflow/blob/master/.github/stale.yml
3. https://github.com/apache/beam/blob/master/.github/stale.yml

Could it be helpful for our repository too?



Best,
Hongze


[1] https://github.com/apps/stale
[2] https://github.com/probot/stale

>Thanks!
>
>Kevin Risden
>
>On Wed, Feb 27, 2019, 17:55 Stamatis Zampetakis  wrote:
>
>> I also went over the third page [1]. I am leaving the rest (first and
>> second) to somebody else :)
>>
>> [1] https://github.com/apache/calcite/pulls?page=3=is%3Apr+is%3Aopen
>>
>> Στις Πέμ, 28 Φεβ 2019 στις 12:03 π.μ., ο/η Stamatis Zampetakis <
>> zabe...@gmail.com> έγραψε:
>>
>> > Good idea Kevin!
>> >
>> > I will give you a hand. I will go over the fourth page [1] of pull
>> > requests right now.
>> >
>> > [1] https://github.com/apache/calcite/pulls?page=4=is%3Apr+is%3Aopen
>> >
>> > Στις Τετ, 27 Φεβ 2019 στις 11:13 μ.μ., ο/η Julian Hyde > >
>> > έγραψε:
>> >
>> >> +999!
>> >>
>> >> > On Feb 27, 2019, at 1:29 PM, Francis Chuang > >
>> >> wrote:
>> >> >
>> >> > Thanks for jumping on this, Kevin!
>> >> >
>> >> > On 28/02/2019 6:36 am, Kevin Risden wrote:
>> >> >> There are 105 open pull requests against apache/calcite repo [1].
>> >> There are
>> >> >> only 48 Calcite JIRAs labeled with pull-request-available [2].
>> >> >> I'm planning to go through in the next few days and make sure that we
>> >> have
>> >> >> PRs that match open JIRAs and are labeled pull-request-available. If
>> >> there
>> >> >> are PRs that are open for JIRAs that are closed, planning to close
>> >> those
>> >> >> PRs with a comment.
>> >> >> [1] https://github.com/apache/calcite/pulls
>> >> >> [2]
>> >> >>
>> >>
>> https://issues.apache.org/jira/issues/?jql=project%20%3D%20CALCITE%20AND%20resolution%20%3D%20Unresolved%20AND%20labels%20%3D%20pull-request-available%20ORDER%20BY%20priority%20DESC
>> >> >> Kevin Risden
>> >> >
>> >>
>> >>
>>

Re: [calcite] branch master updated (5ada462 -> 82ab280)

[Hongze Zhang]

Just forced this push to change the commit message from
"[CALCITE-2787] Json aggregate calls with different null clause get incorrectly 
merged during converting from SQL to relational algebra”
to
"[CALCITE-2787] JSON aggregate calls with different null clause get incorrectly 
merged during converting from SQL to relational algebra”.

> On Feb 28, 2019, at 00:13, hon...@apache.org wrote:
> 
> This is an automated email from the ASF dual-hosted git repository.
> 
> hongze pushed a change to branch master
> in repository https://gitbox.apache.org/repos/asf/calcite.git.
> 
> 
> discard 5ada462  [CALCITE-2787] Json aggregate calls with different null 
> clause get incorrectly merged during converting from SQL to relational algebra
> new 82ab280  [CALCITE-2787] JSON aggregate calls with different null 
> clause get incorrectly merged during converting from SQL to relational algebra
> 
> This update added new revisions after undoing existing revisions.
> That is to say, some revisions that were in the old version of the
> branch are not in the new version.  This situation occurs
> when a user --force pushes a change and generates a repository
> containing something like this:
> 
> * -- * -- B -- O -- O -- O   (5ada462)
>\
> N -- N -- N   refs/heads/master (82ab280)
> 
> You should already have received notification emails for all of the O
> revisions, and so the following emails describe only the N revisions
> from the common base, B.
> 
> Any revisions marked "omit" are not gone; other references still
> refer to them.  Any revisions marked "discard" are gone forever.
> 
> The 3234 revisions listed above as "new" are entirely new to this
> repository and will be described in separate emails.  The revisions
> listed as "add" were already present in the repository and have only
> been added to this reference.
> 
> 
> Summary of changes:

[jira] [Created] (CALCITE-2876) Add MySQLSqlOperatorTable

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2876:
-

 Summary: Add MySQLSqlOperatorTable
 Key: CALCITE-2876
 URL: https://issues.apache.org/jira/browse/CALCITE-2876
 Project: Calcite
  Issue Type: Improvement
Reporter: Hongze Zhang


Similar to OracleSqlOperatorTable[1], we should have a new 
MySQLSqlOperatorTable to store MySQL-specific operators. (E.g. JSON_TYPE)


[1] 
https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/fun/OracleSqlOperatorTable.java



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Created] (CALCITE-2871) Implement JSON_TABLE function

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2871:
-

 Summary: Implement JSON_TABLE function
 Key: CALCITE-2871
 URL: https://issues.apache.org/jira/browse/CALCITE-2871
 Project: Calcite
  Issue Type: Sub-task
Reporter: Hongze Zhang






--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Created] (CALCITE-2872) Support for specifying encoding in JSON representation clause

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2872:
-

 Summary: Support for specifying encoding in JSON representation 
clause
 Key: CALCITE-2872
 URL: https://issues.apache.org/jira/browse/CALCITE-2872
 Project: Calcite
  Issue Type: Sub-task
Reporter: Hongze Zhang






--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Created] (CALCITE-2870) Support for JSON query quotes behavior

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2870:
-

 Summary: Support for JSON query quotes behavior
 Key: CALCITE-2870
 URL: https://issues.apache.org/jira/browse/CALCITE-2870
 Project: Calcite
  Issue Type: Sub-task
Reporter: Hongze Zhang






--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Re: [jira] [Assigned] (CALCITE-2838) Simplification: Remove redundant IS TRUE checks

[Hongze Zhang]

Sorry for making the wrong operation, Zoltan. I’ve reassigned the issue to you.


Hongze

> On Feb 25, 2019, at 23:09, Hongze Zhang (JIRA)  wrote:
> 
> 
> [ 
> https://issues.apache.org/jira/browse/CALCITE-2838?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
>  ]
> 
> Hongze Zhang reassigned CALCITE-2838:
> -----
> 
>Assignee: Hongze Zhang  (was: Zoltan Haindrich)
> 
>> Simplification: Remove redundant IS TRUE checks
>> ---
>> 
>>Key: CALCITE-2838
>>URL: https://issues.apache.org/jira/browse/CALCITE-2838
>>Project: Calcite
>> Issue Type: Improvement
>>   Reporter: Zoltan Haindrich
>>   Assignee: Hongze Zhang
>>   Priority: Blocker
>> Labels: pull-request-available
>>Fix For: 1.19.0
>> 
>> Time Spent: 2h 50m
>> Remaining Estimate: 0h
>> 
>> In case simplifcation is already processing in unknownAsFalse mode, {{expr 
>> IS TRUE}} is redundant - and may just prevent further optimizations from 
>> happening:
>> {code}
>>  @Test public void testRedundantIsTrue() {
>>// in case of unknownAsFalse
>>// x is TRUE <=> x
>>checkSimplify3(isTrue(vBool(1)),
>>"IS TRUE(?0.bool1)",
>>"?0.bool1",
>>"IS TRUE(?0.bool1)");
>>  }
>> {code}
>> there are some further possibilities
>> {code}
>> (unknownAsTrue) x is NOT FALSE <=> x
>> (unknownAsTrue) x is FALSE <=> not x
>> {code}
> 
> 
> 
> --
> This message was sent by Atlassian JIRA
> (v7.6.3#76005)

[jira] [Created] (CALCITE-2868) Support for JSON key uniqueness constraint

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2868:
-

 Summary: Support for JSON key uniqueness constraint
 Key: CALCITE-2868
 URL: https://issues.apache.org/jira/browse/CALCITE-2868
 Project: Calcite
  Issue Type: Sub-task
Reporter: Hongze Zhang






--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Created] (CALCITE-2867) JSON support

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2867:
-

 Summary: JSON support
 Key: CALCITE-2867
 URL: https://issues.apache.org/jira/browse/CALCITE-2867
 Project: Calcite
  Issue Type: Task
  Components: core
Reporter: Hongze Zhang


Complete JSON support for Calcite.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Created] (CALCITE-2866) Allow passing custom SqlValidator / SqlToRelConverter implementations into FrameworkConfig

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2866:
-

 Summary: Allow passing custom SqlValidator / SqlToRelConverter 
implementations into FrameworkConfig
 Key: CALCITE-2866
 URL: https://issues.apache.org/jira/browse/CALCITE-2866
 Project: Calcite
  Issue Type: Improvement
  Components: core
Reporter: Hongze Zhang






--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Re: Integrating MySQL's JSON functions

[Hongze Zhang]

Thanks for your help on this topic, Qianjin! It would be great if you are 
willing to contribute more. Be free to file JIRA issues / open PRs at the time 
when you think is right.

Best,
Hongze



Hongze
 
From: ForwardXu
Date: 2019-02-25 09:43
To: Hongze Zhang
Subject: Integrating MySQL's JSON functions
Hi Honeze Zhang,
 
 
Thank you very much for the late review and merge the PR for JSON_TYPE. I think 
JSON_TYPE has completed the first step of the whole Mysql JSON function. I 
think we can continue the JSON_DEPTH/JSON_LENGTH functions.
 
 
best
qianjin

Re: How to make a TIMESTAMP_ADD call using RelBuilder?

[Hongze Zhang]

Mail format is converted to something bad :(
The example:

final RexNode shiftedDateField =
   relBuilder.call(
   SqlStdOperatorTable.DATETIME_PLUS,
rexBuilder.makeTimestampLiteral(new TimestampString(2019, 1, 1, 0, 0, 0), 0),
rexBuilder.makeCall(SqlStdOperatorTable.MULTIPLY,
rexBuilder.makeIntervalLiteral(
   TimeUnit.MONTH.multiplier,
   new SqlIntervalQualifier(TimeUnit.MONTH, null, SqlParserPos.ZERO)
   ),
relBuilder.literal(1)
   )
   );

> On Feb 23, 2019, at 00:03, Hongze Zhang  wrote:
> 
>> RexNode shiftedDateField =
>relBuilder.call(
>SqlStdOperatorTable.DATETIME_PLUS,
> rexBuilder.makeTimestampLiteral(new TimestampString(2019, 1, 1, 0, 0, 0), 0),
> rexBuilder.makeCall(SqlStdOperatorTable.MULTIPLY,
> rexBuilder.makeIntervalLiteral(
>TimeUnit.MONTH.multiplier,
>new SqlIntervalQualifier(TimeUnit.MONTH, null, 
> SqlParserPos.ZERO)
>),
> relBuilder.literal(1)
>)
>);

Re: How to make a TIMESTAMP_ADD call using RelBuilder?

[Hongze Zhang]

>EnumerableCalc( expr#6=[TIMESTAMPADD($t3, $t4, $t5)] ...)
>
>But Calcite (both 1.18 and 1.19-SNAPSHOT) can't execute this plan:
>
>Caused by: java.lang.RuntimeException: cannot translate call
>TIMESTAMPADD($t3, $t4, $t5)As @Stamatis has said, this is normal. Calcite 
>doesn't provide an implementation for TIMESTAMPADD by default.Below is an 
>usage example of operator DATETIME_PLUS if you are using RexBuilder:final 
>RexNode shiftedDateField =
relBuilder.call(
SqlStdOperatorTable.DATETIME_PLUS,
rexBuilder.makeTimestampLiteral(new TimestampString(2019, 1, 1, 0, 0, 0), 0),
rexBuilder.makeCall(SqlStdOperatorTable.MULTIPLY,
rexBuilder.makeIntervalLiteral(
TimeUnit.MONTH.multiplier,
new SqlIntervalQualifier(TimeUnit.MONTH, null, 
SqlParserPos.ZERO)
),
relBuilder.literal(1)
)
);













在 2019-02-22 23:49:35，"Stamatis Zampetakis"  写道：
>Hi Anton,
>
>The TIMESTAMPADD operator never ends up in the logical plan so essentially
>there is no code for executing it. It is just used as a syntactic sugar in
>the parser.
>
>Have a look below to see how you can obtain the equivalent in relational
>algebra:
>https://github.com/apache/calcite/blob/8eb852039db04c132ae7a99943495f87cf39dfd2/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java#L1437
>
>Best,
>Stamatis
>
>Στις Παρ, 22 Φεβ 2019 στις 3:51 μ.μ., ο/η Anton Haidai <
>anton.hai...@gmail.com> έγραψε:
>
>> Hongze, thank you for your answer. Your code successfully produces a
>> plan with the following node:
>>
>> EnumerableCalc( expr#6=[TIMESTAMPADD($t3, $t4, $t5)] ...)
>>
>> But Calcite (both 1.18 and 1.19-SNAPSHOT) can't execute this plan:
>>
>> Caused by: java.lang.RuntimeException: cannot translate call
>> TIMESTAMPADD($t3, $t4, $t5)
>> at
>> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCall(RexToLixTranslator.java:719)
>> at
>> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:693)
>> at
>> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:197)
>>
>> While the SQL from my first message produces the following node:
>>
>> EnumerableCalc(expr#0..26=[{inputs}], expr#27=[1], expr#28=[1],
>> expr#29=[*($t27, $t28)], expr#30=[+($t0, $t29)], EXPR$0=[$t30])
>>
>> And Calcite is able to execute it. So now I'm trying to replicate it
>> using a RelBuilder (using INTERVAL instead of TIMESTAMP_ADD), without
>> a success so far.
>>
>> On Fri, Feb 22, 2019 at 4:11 PM Hongze Zhang  wrote:
>> >
>> > Correct the flag:
>> > TimeUnitRange.MONTH -> TimeUnit.MONTH
>> >
>> >
>> > Best,
>> > Hongze
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > At 2019-02-22 22:07:11, "Hongze Zhang"  wrote:
>> >
>> > Hi Anton,
>> >
>> >
>> > Following code works to me:
>> >
>> >
>> >
>> > final RexNode shiftedDateField =
>> >
>> > relBuilder.call(
>> >
>> > SqlStdOperatorTable.TIMESTAMP_ADD,
>> >
>> > relBuilder.getRexBuilder().makeFlag(TimeUnitRange.MONTH),
>> >
>> > relBuilder.literal(1),
>> >
>> > relBuilder.getRexBuilder().makeDateLiteral(new DateString(2019,
>> 1, 1))
>> >
>> > );
>> >
>> >
>> >
>> >
>> >
>> > Best,
>> > Hongze
>> >
>> >
>> >
>> >
>> >
>> > At 2019-02-22 19:16:09, "Anton Haidai"  wrote:
>> > >Hello. In SQL, I can execute a query like "SELECT TIMESTAMPADD(month,
>> > >1, "date" ) FROM ..." and it works. But my attempts to do the same
>> > >thing using RelBuilder are not successful, for example, this code does
>> > >not work:
>> > >
>> > >RexNode shiftedDateField = relBuilder.call(
>> > >SqlStdOperatorTable.TIMESTAMP_ADD,
>> > >relBuilder.literal("month"),
>> > >relBuilder.literal(1),
>> > >dateFieldRef
>> > >);
>> > >
>> > >Could you please tell me, how to make a TIMESTAMP_ADD call using
>> RelBuilder?
>> > >--
>> > >Best regards,
>> > >Anton.
>> >
>> >
>> >
>> >
>> >
>> >
>>
>>
>>
>> --
>> Best regards,
>> Anton.
>>

Re:How to make a TIMESTAMP_ADD call using RelBuilder?

[Hongze Zhang]

Correct the flag:
TimeUnitRange.MONTH -> TimeUnit.MONTH


Best,
Hongze










At 2019-02-22 22:07:11, "Hongze Zhang"  wrote:

Hi Anton,


Following code works to me:



final RexNode shiftedDateField =

relBuilder.call(

SqlStdOperatorTable.TIMESTAMP_ADD,

relBuilder.getRexBuilder().makeFlag(TimeUnitRange.MONTH),

relBuilder.literal(1),

relBuilder.getRexBuilder().makeDateLiteral(new DateString(2019, 1, 1))

);





Best,
Hongze





At 2019-02-22 19:16:09, "Anton Haidai"  wrote:
>Hello. In SQL, I can execute a query like "SELECT TIMESTAMPADD(month,
>1, "date" ) FROM ..." and it works. But my attempts to do the same
>thing using RelBuilder are not successful, for example, this code does
>not work:
>
>RexNode shiftedDateField = relBuilder.call(
>SqlStdOperatorTable.TIMESTAMP_ADD,
>relBuilder.literal("month"),
>relBuilder.literal(1),
>dateFieldRef
>);
>
>Could you please tell me, how to make a TIMESTAMP_ADD call using RelBuilder?
>-- 
>Best regards,
>Anton.





 

Re:How to make a TIMESTAMP_ADD call using RelBuilder?

[Hongze Zhang]

Hi Anton,


Following code works to me:



final RexNode shiftedDateField =

relBuilder.call(

SqlStdOperatorTable.TIMESTAMP_ADD,

relBuilder.getRexBuilder().makeFlag(TimeUnitRange.MONTH),

relBuilder.literal(1),

relBuilder.getRexBuilder().makeDateLiteral(new DateString(2019, 1, 1))

);





Best,
Hongze





At 2019-02-22 19:16:09, "Anton Haidai"  wrote:
>Hello. In SQL, I can execute a query like "SELECT TIMESTAMPADD(month,
>1, "date" ) FROM ..." and it works. But my attempts to do the same
>thing using RelBuilder are not successful, for example, this code does
>not work:
>
>RexNode shiftedDateField = relBuilder.call(
>SqlStdOperatorTable.TIMESTAMP_ADD,
>relBuilder.literal("month"),
>relBuilder.literal(1),
>dateFieldRef
>);
>
>Could you please tell me, how to make a TIMESTAMP_ADD call using RelBuilder?
>-- 
>Best regards,
>Anton.

Re: Global LOOKAHEAD of the SQL parser

[Hongze Zhang]

> Is there a way to have that tester check error messages if the test is 
> defined in BabelParserTest? (Perhaps you could inspect the stack and see 
> whether “BabelParserTest.test” appears in the top few entries.)

Thanks Julian! And this makes sense. I'll give it a try in the later 
modifications of the PR.

Best,
Hongze


Hongze
 
From: Julian Hyde
Date: 2019-02-15 04:32
To: dev
Subject: Re: Global LOOKAHEAD of the SQL parser
Thanks for the clarification. You have convinced me that modifying the parser 
will not be too onerous. I was pleased to see that only one change was required 
to the “server” parser, for instance.
 
I saw that you overrode the tester in BabelParserTest. I makes sense, but it 
creates an exposure: tests that are defined in BabelParserTest (as opposed to 
inherited from SqlParserTest) will never have their error message checked. Is 
there a way to have that tester check error messages if the test is defined in 
BabelParserTest? (Perhaps you could inspect the stack and see whether 
“BabelParserTest.test” appears in the top few entries.)
 
Julian
 
> On Feb 14, 2019, at 12:36 AM, Hongze Zhang  wrote:
> 
> I think it's not something unacceptable to require parser developers to know 
> the usage of LOOKAHEAD hints of JavaCC, for keeping the parser efficient and 
> ambiguous free. 
> Firstly, it's not really that difficult to learn. By setting the global 
> LOOKAHEAD to 1, JavaCC will perform ambiguity checking automatically during 
> the maven build lifecycle. If someone changed the parser all he needs to do 
> is to add enough LOOKAHEAD hints following the warning message, it is not 
> difficult for a developer, I think.
> And there are really not that many LOOKAHEAD hints need to be added. In 
> current version of Calcite, there are already 60 LOOKAHEAD hints in 
> Parser.jj[1], then I have added 37 new ones[2] in the PR to make things work. 
> also, Parser.jj has about 6800 lines of code now. Which means, averagely we 
> need one new LOOKAHEAD per 70 new lines, this is not much.
> Also, the smaller LOOKAHEAD is, more readable the ParseException could be. 
> You can see some "fixme"s in SqlParserTest about ParseException's error 
> message get healed by the change[3].
> 
> Best,
> Hongze
> 
> 
> [1] 
> https://github.com/apache/calcite/blob/master/core/src/main/codegen/templates/Parser.jj
> [2] 
> https://github.com/apache/calcite/blob/656608339f901fd5a6b919ee79a37ba25c16b0d2/core/src/main/codegen/templates/Parser.jj
> [3] 
> https://github.com/apache/calcite/pull/1041/files#diff-ef277e7d229cfc747255eb61ce6fd46dL1386
> 
> 
> 
> Hongze
> 
> From: Julian Hyde
> Date: 2019-02-14 04:12
> To: dev
> Subject: Re: Global LOOKAHEAD of the SQL parser
> The performance improvements are impressive - basically the parser got 10x 
> faster.
> 
> However, the parser is now more difficult to develop. Is this worth the 
> speedup?
> 
> Julian
> 
> 
>> On Feb 13, 2019, at 5:30 AM, Hongze Zhang  wrote:
>> 
>> Thank you very much for the quick response, Julian!
>> 
>> 
>> The parse will be broken if we simply set LOOKAHEAD to 1. There will be a 
>> lot of lookahead warning that are newly produced or hidden before (JavaCC 
>> does not perform LA check when global LOOKAHEAD is larger than 1[1]), and 
>> many test cases will turn to fail. I could run the benchmark because the SQL 
>> generated by the benchmark is not touching the broken part of the parser. 
>> For solving the problem deeply I have done some tentative work and opened a 
>> JIRA issue[2] with a PR.
>> And I didn't change the global LOOKAHEAD for Babel in the PR. This is 
>> because Babel turns a lot of reserved keyword to non-reserved[3], if we set 
>> the LOOKAHEAD to 1 JavaCC will generate more choice conflict than the 
>> default parser for Babel[4]. To deal with this we must add extra lookahead 
>> hints into default parser's code, and the default parser will lose 
>> performance by the hints. I would suggest to take the concept that "Babel is 
>> powerful but slower" and not optimize Babel. But if there is a way we can 
>> easily solve the conflicts without touching default parser I would be a fan 
>> of optimizing Babel too. What do you think?
>> 
>> 
>> Best,
>> Hongze
>> 
>> 
>> 
>> 
>> [1] https://javacc.org/javaccgrm
>> [2] https://issues.apache.org/jira/browse/CALCITE-2847
>> [3] 
>> https://github.com/apache/calcite/blob/2102f1f5442fa271c258b7754da8df07d65847ec/babel/src/main/codegen/config.fmpp#L338
>> [4] https://www.dropbox.com/s/wpap95dko4wnlcc/babel_warnings.log?dl=0
>> At 2019-02-13 04:11:33, "Julian Hyde"  wrote:
>>> Does the 

Re: Global LOOKAHEAD of the SQL parser

[Hongze Zhang]

I think it's not something unacceptable to require parser developers to know 
the usage of LOOKAHEAD hints of JavaCC, for keeping the parser efficient and 
ambiguous free. 
Firstly, it's not really that difficult to learn. By setting the global 
LOOKAHEAD to 1, JavaCC will perform ambiguity checking automatically during the 
maven build lifecycle. If someone changed the parser all he needs to do is to 
add enough LOOKAHEAD hints following the warning message, it is not difficult 
for a developer, I think.
And there are really not that many LOOKAHEAD hints need to be added. In current 
version of Calcite, there are already 60 LOOKAHEAD hints in Parser.jj[1], then 
I have added 37 new ones[2] in the PR to make things work. also, Parser.jj has 
about 6800 lines of code now. Which means, averagely we need one new LOOKAHEAD 
per 70 new lines, this is not much.
Also, the smaller LOOKAHEAD is, more readable the ParseException could be. You 
can see some "fixme"s in SqlParserTest about ParseException's error message get 
healed by the change[3].

Best,
Hongze


[1] 
https://github.com/apache/calcite/blob/master/core/src/main/codegen/templates/Parser.jj
[2] 
https://github.com/apache/calcite/blob/656608339f901fd5a6b919ee79a37ba25c16b0d2/core/src/main/codegen/templates/Parser.jj
[3] 
https://github.com/apache/calcite/pull/1041/files#diff-ef277e7d229cfc747255eb61ce6fd46dL1386



Hongze
 
From: Julian Hyde
Date: 2019-02-14 04:12
To: dev
Subject: Re: Global LOOKAHEAD of the SQL parser
The performance improvements are impressive - basically the parser got 10x 
faster.
 
However, the parser is now more difficult to develop. Is this worth the speedup?
 
Julian
 
 
> On Feb 13, 2019, at 5:30 AM, Hongze Zhang  wrote:
> 
> Thank you very much for the quick response, Julian!
> 
> 
> The parse will be broken if we simply set LOOKAHEAD to 1. There will be a lot 
> of lookahead warning that are newly produced or hidden before (JavaCC does 
> not perform LA check when global LOOKAHEAD is larger than 1[1]), and many 
> test cases will turn to fail. I could run the benchmark because the SQL 
> generated by the benchmark is not touching the broken part of the parser. 
> For solving the problem deeply I have done some tentative work and opened a 
> JIRA issue[2] with a PR.
> And I didn't change the global LOOKAHEAD for Babel in the PR. This is because 
> Babel turns a lot of reserved keyword to non-reserved[3], if we set the 
> LOOKAHEAD to 1 JavaCC will generate more choice conflict than the default 
> parser for Babel[4]. To deal with this we must add extra lookahead hints into 
> default parser's code, and the default parser will lose performance by the 
> hints. I would suggest to take the concept that "Babel is powerful but 
> slower" and not optimize Babel. But if there is a way we can easily solve the 
> conflicts without touching default parser I would be a fan of optimizing 
> Babel too. What do you think?
> 
> 
> Best,
> Hongze
> 
> 
> 
> 
> [1] https://javacc.org/javaccgrm
> [2] https://issues.apache.org/jira/browse/CALCITE-2847
> [3] 
> https://github.com/apache/calcite/blob/2102f1f5442fa271c258b7754da8df07d65847ec/babel/src/main/codegen/config.fmpp#L338
> [4] https://www.dropbox.com/s/wpap95dko4wnlcc/babel_warnings.log?dl=0
> At 2019-02-13 04:11:33, "Julian Hyde"  wrote:
>> Does the parser produce correct results if you set LOOKAHEAD to 1? If so, we 
>> should use that.
>> 
>> Do the extension parsers (e.g. Babel) also work with that setting?
>> 
>> We thought we were using as little lookahead as we could get away with, but 
>> maybe we were wrong.
>> 
>>> On Feb 12, 2019, at 8:24 AM, Hongze Zhang  wrote:
>>> 
>>> Hi all,
>>> 
>>> 
>>> Recently I have spent some time on playing with Calcite's built-in SQL 
>>> parsers. And now I'm interested with the reason why the global LOOKAHEAD is 
>>> set to 2[1] by default.
>>> I run a comparative benchmark using the ParserBenchmark util class, and the 
>>> output log shows visible parsing performance improvement after setting 
>>> global LOOKAHEAD to 1. For example, the metric 
>>> "ParserBenchmark.parseCached" reduced from 1693.821 us/op ± 1921.925 
>>> us/op[2] to 655.452 ± 181.100 us/op[3].
>>> 
>>> 
>>> JavaCC always generates java methods like jj_2_**(...) for LL(k) (k > 1) 
>>> grammar[4], I am almost sure this way is not efficient comparing with the 
>>> generated code for LL(1) grammar. It might be great If we can somehow take 
>>> the advantage of JavaCC's LL(1). 
>>> And of course I could see there was some trade-off consideration about not 
>>> using LL(1) by default. Then I have done some s

Re: [DISCUSS] Towards Calcite 1.19.0

[Hongze Zhang]

Thank you very much for managing the release, Kevin.


If the community have enough reviewing resource I would hope the fix of 
CALCITE-2785[1] and CALCITE-2787[2] could be bring to Calcite 1.19.0. Besides I 
think I can try my best helping review PRs with which I have enough knowledge 
too.


Best,
Hongze





At 2019-02-13 19:40:18, "Zoltan Haindrich"  wrote:
>Hello,
>
>In Hive I'm a little bit behind in upgrading to 1.18 and although the upgrade 
>would not cause any correctness issues; but in a sense it's more conservative 
>in doing some 
>simplifications - which could be interpreted as regressions; if we take that 
>into account that even the plan could get worse.
>
>I've a few patches almost ready - they are very small changes (actually Julian 
>mentioned that they are kinda too small, so next time I will not be opening 
>separate jiras 
>for them)
>
>I will finish them and launch a custom Hive test with the latest master to see 
>if there are any new issues coming from that direction.
>I should get the results for it by tomorrow.
>
>cheers,
>Zoltan
>
>
>On 2/12/19 11:30 PM, Stamatis Zampetakis wrote:
>> I was not suggesting changing the release process. I wanted just to
>> highlight the fact that if the aforementioned tickets are not part of 1.19
>> I will have to create an unofficial bundle which includes them in order to
>> keep the downstream project working. Sorry for the confusion.
>> 
>> Στις Τρί, 12 Φεβ 2019 στις 8:56 μ.μ., ο/η Julian Hyde 
>> έγραψε:
>> 
>>> Stamatis,
>>>
>>> We’ve so far managed to avoid making patch releases. It keeps life simpler
>>> if all releases are from the main line. And simple is important, given that
>>> there are no salaried release or QA engineers working on Calcite.
>>>
>>> But as part of that contract, we commit to making releases from main line
>>> frequently and regularly. Hopefully 1.19 will arrive soon enough for your
>>> purposes.
>>>
>>> Julian
>>>
>>>
 On Feb 12, 2019, at 5:27 AM, Stamatis Zampetakis 
>>> wrote:

 In order to enable Calcite on a downstream project I need to patch
 the official release with the PRs of the following Jiras.

 [CALCITE-2464] Allow to set nullability for columns of structured types
>>> [1]
 [CALCITE-2677] Struct types with one field are not mapped correctly to
>>> Java
 Classes [2]
 [CALCITE-2776] Wrong value when accessing struct types with one attribute
 [3]

 I think the discussion has advanced quite a lot for CALCITE-2464 so I
>>> could
 probably take it on my self,
 but I would really appreciate some input regarding CALCITE-2677 and
 CALCITE-2776.
 Let's continue the discussion under the respective Jiras.

 Best,
 Stamatis

 [1] https://jira.apache.org/jira/browse/CALCITE-2464
 [2] https://jira.apache.org/jira/browse/CALCITE-2677
 [3] https://jira.apache.org/jira/browse/CALCITE-2776





 Στις Δευ, 11 Φεβ 2019 στις 9:49 μ.μ., ο/η Francis Chuang <
 francischu...@apache.org> έγραψε:

> Thanks for getting this started, Kevin!
>
> On 12/02/2019 6:39 am, Julian Hyde wrote:
 Are there any JIRA cases that should make it into 1.19.0 but are not
> yet
 finished?
>>
>> Yes - there are a lot cases that have a PR ready that have not been
> reviewed / committed. We, the committers, will need to make a team
>>> effort
> to get as many of these in as possible.
>>
>> Julian
>>
>>
>>> On Feb 11, 2019, at 11:05 AM, Michael Mior  wrote:
>>>
>>> Thanks for getting the ball rolling Kevin!
>>> --
>>> Michael Mior
>>> mm...@apache.org
>>>
>>> Le lun. 11 févr. 2019 à 09:51, Kevin Risden  a
> écrit :

 Calcite 1.18.0 was released on 2018-12 (coming up on 2 months ago).
>>> It
> is
 time to get the ball rolling for the Calcite 1.19.0 release since
>>> there
 have been releases every 2-3 months.

 Calcite currently has 32 JIRA issues tagged for 1.19.0 with 68
>>> commits.
 Avatica currently has 2 JIRA issues tagged for avatica-1.14.0 with 8
 commits.

 Are there any JIRA cases that should make it into 1.19.0 but are not
> yet
 finished?

 Since there are only two minor commits to Avatica I don't think we
> need a
 new Avatica release before the Calcite release.

 Kevin Risden
>>
>
>
>>>
>>>
>> 

Re: Global LOOKAHEAD of the SQL parser

[Hongze Zhang]

Thank you very much for the quick response, Julian!


The parse will be broken if we simply set LOOKAHEAD to 1. There will be a lot 
of lookahead warning that are newly produced or hidden before (JavaCC does not 
perform LA check when global LOOKAHEAD is larger than 1[1]), and many test 
cases will turn to fail. I could run the benchmark because the SQL generated by 
the benchmark is not touching the broken part of the parser. 
For solving the problem deeply I have done some tentative work and opened a 
JIRA issue[2] with a PR.
And I didn't change the global LOOKAHEAD for Babel in the PR. This is because 
Babel turns a lot of reserved keyword to non-reserved[3], if we set the 
LOOKAHEAD to 1 JavaCC will generate more choice conflict than the default 
parser for Babel[4]. To deal with this we must add extra lookahead hints into 
default parser's code, and the default parser will lose performance by the 
hints. I would suggest to take the concept that "Babel is powerful but slower" 
and not optimize Babel. But if there is a way we can easily solve the conflicts 
without touching default parser I would be a fan of optimizing Babel too. What 
do you think?


Best,
Hongze




[1] https://javacc.org/javaccgrm
[2] https://issues.apache.org/jira/browse/CALCITE-2847
[3] 
https://github.com/apache/calcite/blob/2102f1f5442fa271c258b7754da8df07d65847ec/babel/src/main/codegen/config.fmpp#L338
[4] https://www.dropbox.com/s/wpap95dko4wnlcc/babel_warnings.log?dl=0
At 2019-02-13 04:11:33, "Julian Hyde"  wrote:
>Does the parser produce correct results if you set LOOKAHEAD to 1? If so, we 
>should use that.
>
>Do the extension parsers (e.g. Babel) also work with that setting?
>
>We thought we were using as little lookahead as we could get away with, but 
>maybe we were wrong.
>
>> On Feb 12, 2019, at 8:24 AM, Hongze Zhang  wrote:
>> 
>> Hi all,
>> 
>> 
>> Recently I have spent some time on playing with Calcite's built-in SQL 
>> parsers. And now I'm interested with the reason why the global LOOKAHEAD is 
>> set to 2[1] by default.
>> I run a comparative benchmark using the ParserBenchmark util class, and the 
>> output log shows visible parsing performance improvement after setting 
>> global LOOKAHEAD to 1. For example, the metric "ParserBenchmark.parseCached" 
>> reduced from 1693.821 us/op ± 1921.925 us/op[2] to 655.452 ± 181.100 
>> us/op[3].
>> 
>> 
>> JavaCC always generates java methods like jj_2_**(...) for LL(k) (k > 1) 
>> grammar[4], I am almost sure this way is not efficient comparing with the 
>> generated code for LL(1) grammar. It might be great If we can somehow take 
>> the advantage of JavaCC's LL(1). 
>> And of course I could see there was some trade-off consideration about not 
>> using LL(1) by default. Then I have done some search on dev list and JIRA 
>> cases but found nothing. Does anyone hold information about that?
>> 
>> 
>> Best,
>> Hongze
>> 
>> 
>> [1] 
>> https://github.com/apache/calcite/blob/883666929478aabe07ee5b9e572c43a6f1a703e2/core/pom.xml#L304
>> [2] https://www.dropbox.com/s/il6nodc44dzo0rz/bench_la2.log?dl=0
>> [3] https://www.dropbox.com/s/4rrou71siskdhhm/bench_la1.log?dl=0
>> [4] 
>> http://www.cs.tau.ac.il/~msagiv/courses/lab/Shai2/tools/javacc/examples/JavaGrammars/OPTIMIZING
>> 

[jira] [Created] (CALCITE-2847) Optimize global LOOKAHEAD for SQL parsers

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2847:
-

 Summary: Optimize global LOOKAHEAD for SQL parsers
 Key: CALCITE-2847
 URL: https://issues.apache.org/jira/browse/CALCITE-2847
 Project: Calcite
  Issue Type: Improvement
  Components: core
Reporter: Hongze Zhang
Assignee: Julian Hyde


Currently global LOOKAHEAD is set to 2 for the built-in SQL parsers[1], we'd 
like to optimize LOOKAHEAD to 1 to enable performance enhancement if possible.



[1]https://github.com/apache/calcite/blob/883666929478aabe07ee5b9e572c43a6f1a703e2/core/pom.xml#L304



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Global LOOKAHEAD of the SQL parser

[Hongze Zhang]

Hi all,


Recently I have spent some time on playing with Calcite's built-in SQL parsers. 
And now I'm interested with the reason why the global LOOKAHEAD is set to 2[1] 
by default.
I run a comparative benchmark using the ParserBenchmark util class, and the 
output log shows visible parsing performance improvement after setting global 
LOOKAHEAD to 1. For example, the metric "ParserBenchmark.parseCached" reduced 
from 1693.821 us/op ± 1921.925 us/op[2] to 655.452 ± 181.100 us/op[3].


JavaCC always generates java methods like jj_2_**(...) for LL(k) (k > 1) 
grammar[4], I am almost sure this way is not efficient comparing with the 
generated code for LL(1) grammar. It might be great If we can somehow take the 
advantage of JavaCC's LL(1). 
And of course I could see there was some trade-off consideration about not 
using LL(1) by default. Then I have done some search on dev list and JIRA cases 
but found nothing. Does anyone hold information about that?


Best,
Hongze


[1] 
https://github.com/apache/calcite/blob/883666929478aabe07ee5b9e572c43a6f1a703e2/core/pom.xml#L304
[2] https://www.dropbox.com/s/il6nodc44dzo0rz/bench_la2.log?dl=0
[3] https://www.dropbox.com/s/4rrou71siskdhhm/bench_la1.log?dl=0
[4] 
http://www.cs.tau.ac.il/~msagiv/courses/lab/Shai2/tools/javacc/examples/JavaGrammars/OPTIMIZING

Re:Re: Integrating MySQL's JSON functions

[Hongze Zhang]

Thanks for the suggestions Julian and Stamatis!


I would support the idea about adding a MysqlSqlOperatorTable to Calcite, what 
I was not sure about is whether to put MySQL's JSON functions to it. I was 
wandering if user could easily know (e.g.) JSON_TYPE is a MySQL functions and 
JSON_VALUE is a built-in function.
But I understand your opinions and actually I have no strong inclination as 
long as we can bring the feature to Calcite.


Best,
Hongze









在 2019-02-12 02:43:52，"Julian Hyde"  写道：
>A MysqlSqlOperatorTable isn’t a bad idea. Each operator table could be a place 
>to collect the operators for that DB.
>
>I have a couple of asks:
>* Don’t include standard operators on that table
>* If operators are shared with another DB (e.g. the LEAST function will occur 
>in both OracleSqlOperatorTable and MysqlSqlOperatorTable) make sure that the 
>code only occurs in one place.
>
>Julian
>
>
>> On Feb 11, 2019, at 5:51 AM, Stamatis Zampetakis  wrote:
>> 
>> Hi Hongze, Qianjin,
>> 
>> I apologise fo the late response.
>> 
>> I still don't understand why it is problematic to create a separate
>> MySQLOperatorTable.
>> You can easily combine operator tables (using ChainedSqlOperatorTable for
>> instance) and pass them around to places were otherwise you would put a
>> single operator table (e.g., SqlValidator).
>> It is also easy to use multiple operator tables at a higher level using the
>> connection property FUN [1].
>> 
>> Best,
>> Stamatis
>> 
>> [1]
>> https://calcite.apache.org/apidocs/org/apache/calcite/config/CalciteConnectionProperty.html#FUN
>> 
>> 
>> Στις Παρ, 1 Φεβ 2019 στις 6:39 π.μ., ο/η ForwardXu  έγραψε:
>> 
>>> hi statmatis:
>>> It's also possible that we could do better in both ways, as Hongze Zhang
>>> said. One of the initial reasons I implemented json_type was to use it in
>>> flink as well. However, we know that flink is not open to support a dialect
>>> like mysql.
>>> best
>>> qianjin
>>> 
>>> 
>>> 
>>> 
>>> -- 原始邮件 --
>>> 发件人: "Hongze Zhang";
>>> 发送时间: 2019年2月1日(星期五) 中午1:09
>>> 收件人: "dev@calcite.apache.org";
>>> 
>>> 主题: Re: Re: Integrating MySQL's JSON functions
>>> 
>>> 
>>> 
>>> Hi Stamatis,
>>> 
>>> Thanks for mentioning MySQLOperatorTable!
>>> 
>>> I just read some code about the usage of OracleOperatorTable in Calcite,
>>> but I am now not strongly inclined to add MySQL's JSON functions to
>>> MySQLOperatorTable.
>>> MySQL's JSON functions are rarely conflict with what are from standard,
>>> and they extends the JSON ability of MySQL in many sides. I think it might
>>> be a good try to treat functions from both MySQL's and standard's as a
>>> whole JSON operator suite.
>>> Users may want to directly use any function they know, I think it is
>>> better to not to confuse them with the truth that what's in standard and
>>> what's not.
>>> For the conflict part like JSON_OBJECT(key , value) and JSON_OBJECT(key :
>>> value), I think we can just support both.
>>> 
>>> What do you think?
>>> 
>>> 
>>> 
>>> Hongze
>>> 
>>> From: Stamatis Zampetakis
>>> Date: 2019-02-01 06:32
>>> To: dev
>>> Subject: Re: Integrating MySQL's JSON functions
>>> Nice document Hongze!
>>> 
>>> Since the functions are only present in MySQL why not create a
>>> MySQLOperatorTable and put them there? I went over the discussion in
>>> CALCITE-2791 but I did not understand why it is preferable to put them in
>>> the SqlStdOperatorTable.
>>> 
>>> Στις Πέμ, 31 Ιαν 2019 στις 10:51 μ.μ., ο/η Julian Hyde 
>>> έγραψε:
>>> 
>>>> +1
>>>> 
>>>> In the reference doc, let’s make it clear that these are in MySQL but not
>>>> in the standard. (Unlike some of their other extensions to standard SQL,
>>>> MySQL seem to have done a good job - they are well-designed and
>>>> well-documented.)
>>>> 
>>>>> On Jan 31, 2019, at 10:20 AM, Hongze Zhang  wrote:
>>>>> 
>>>>> Hi all,
>>>>> 
>>>>> 
>>>>> There is a PR CALCITE-2791[1] from xuqianjin about adding a MySQL
>>>> function JSON_TYPE[2], and I want to know what do you think about it.
>>>>> 
>>>>> 
>>>>> JSON_

Re: Calcite Validator Customization

[Hongze Zhang]

Hi,


We used to face the same problem overriding the default behavior including 
operand type checker of a standard operator. If you are using Calcite as an 
individual planner (or other circumstances you are not taking the Calcite's 
default planner tool org.apache.calcite.prepare.PlannerImpl) and using built-in 
validator implementation (org.apache.calcite.sql.validate.SqlValidatorImpl), 
you should set validator.setIdentifierExpansion(true) after creating your own 
validator, this way the overrided operators would be retained by relational 
algebras.


Best,
Hongze


At 2019-02-07 08:24:00, "Michael Mior"  wrote:
>There are two main steps you'd have to take here. Firstly, you'd have
>to change the operand types accepted by CONCAT in SqlStdOperatorTable.
>Second, you'd have to redefine the CONCAT method in RexImpTable to
>something that actually builds the string instead of just using
>String.concat.
>
>Unfortunately, I don't think you can override the behaviour of
>built-in operators without making your build of Calcite.
>--
>Michael Mior
>mm...@apache.org
>
>Le mer. 6 févr. 2019 à 17:07, Paul Trepagnier  a écrit :
>>
>> I am using Calcite to try to be a federated database server for a BI tool.
>> This BI tool is sending queries that I cannot change at the source.  So, I
>> am trying to do some customizations within Calcite to handle these
>> queries.
>>
>> For instance, one of the queries tries to do a sql concatenate between a
>> string and an integer.  Calcite throws an exception for
>> this: org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply
>> '||' to arguments of type ' || '. Supported form(s):
>> ' || '
>>
>> Is there an easy way to override the behavior of the concatenate operator?
>> I am new to Calcite, so I do not know what my options are for customizing
>> the planning/validation behavior.  If someone is able to point me in the
>> right direction, I would greatly appreciate it.
>>
>> Thank you for any help you can provide,
>>
>> Paul

Re: Re: [ANNOUNCE] New committer: Stamatis Zampetakis

[Hongze Zhang]

Congratulations, Stamatis!



Hongze
 
From: Andrei Sereda
Date: 2019-02-01 11:13
To: dev
Subject: Re: [ANNOUNCE] New committer: Stamatis Zampetakis
Welcome, Stamatis, and thanks for your help.
 
On Thu, Jan 31, 2019 at 6:22 PM Stamatis Zampetakis 
wrote:
 
> Thank you all for the warm welcome!
>
>
> I hold a PhD on query optimization for massively parallel systems and I am
> interested in anything around data management.
>
>
> I am currently working on introducing relational algebra and SQL on a
> multi-version database with various types of indexes which till now
> supported only mono-table queries.
>
>
> I had the chance to discover Calcite just about the time that I was going
> to build everything from my previous research prototypes. I am very happy
> to have found a project as generic and complete as Calcite and I owe a big
> thanks to the community for this.
>
>
> It's an honour to be a committer for the project and I will do my best to
> improve and promote Calcite as much as I can.
>
>
> Best,
>
> Stamatis
>
> Στις Τετ, 30 Ιαν 2019 στις 10:39 μ.μ., ο/η Francis Chuang <
> francischu...@apache.org> έγραψε:
>
> > Congrats, Stamatis!
> >
> > Francis
> >
> > On 31/01/2019 7:05 am, Kevin Risden wrote:
> > > Congrats and welcome Stamatis!
> > >
> > > Kevin Risden
> > >
> > > On Wed, Jan 30, 2019 at 3:03 PM Enrico Olivelli 
> > wrote:
> > >>
> > >> Kudos !
> > >>
> > >> Enrico
> > >>
> > >> Il giorno mer 30 gen 2019, 19:27 Julian Hyde  ha
> > scritto:
> > >>
> > >>> Welcome, Stamatis! In addition to your code contributions, I have
> been
> > >>> appreciating your wise & moderating contributions to technical
> > discussions,
> > >>> and answers to people’s questions on the dev list.
> > >>>
> > >>> Julian
> > >>>
> > >>>
> >  On Jan 30, 2019, at 10:01 AM, Jesus Camacho Rodriguez <
> > >>> jcama...@apache.org> wrote:
> > 
> >  Apache Calcite's Project Management Committee (PMC) has invited
> >  Stamatis Zampetakis to become a committer, and we are pleased to
> >  announce that he has accepted.
> > 
> >  Over the past few months, Stamatis has made several contributions to
> >  Calcite and he is a very active participant in discussions in issues
> >  and mailing lists.
> > 
> >  Stamatis, welcome, thank you for your contributions, and we look
> >  forward your further interactions with the community! If you wish,
> >  please feel free to tell us more about yourself and what you are
> >  working on.
> > 
> >  Jesús (on behalf of the Apache Calcite PMC)
> > >>>
> > >>>
> >
> >
>

Re: Re: [ANNOUNCE] New committer: Zoltan Haindrich

[Hongze Zhang]

Congratulations, Zoltan!



Hongze
 
From: Andrei Sereda
Date: 2019-02-01 11:14
To: dev
Subject: Re: [ANNOUNCE] New committer: Zoltan Haindrich
Welcome, Zoltan!
 
On Wed, Jan 30, 2019 at 4:39 PM Francis Chuang 
wrote:
 
> Congrats, Zoltan!
>
> Francis
>
> On 31/01/2019 7:05 am, Kevin Risden wrote:
> > Congrats and welcome Zoltan!
> >
> > Kevin Risden
> >
> > On Wed, Jan 30, 2019 at 2:55 PM Enrico Olivelli 
> wrote:
> >>
> >> Congrats!
> >>
> >> Enrico
> >>
> >> Il giorno mer 30 gen 2019, 19:25 Julian Hyde  ha
> scritto:
> >>
> >>> Welcome, Zoltan! You’ve already contributed plenty of great work, and I
> >>> look forward to further contributions!
> >>>
> >>> Julian
> >>>
> >>>
>  On Jan 30, 2019, at 10:05 AM, Jesus Camacho Rodriguez <
> >>> jcama...@apache.org> wrote:
> 
>  Apache Calcite's Project Management Committee (PMC) has invited Zoltan
>  Haindrich to become a committer, and we are pleased to announce that
>  he has accepted.
> 
>  Over the past few months, Zoltan has contributed many improvements and
>  fixes to core parts of the project related to query optimization.
> 
>  Zoltan, welcome, thank you for your contributions, and we look forward
>  your further interactions with the community! If you wish, please feel
>  free to tell us more about yourself and what you are
>  working on.
> 
>  Jesús (on behalf of the Apache Calcite PMC)
> >>>
> >>>
>
>

Re: Re: Integrating MySQL's JSON functions

[Hongze Zhang]

Hi Stamatis,

Thanks for mentioning MySQLOperatorTable!

I just read some code about the usage of OracleOperatorTable in Calcite, but I 
am now not strongly inclined to add MySQL's JSON functions to 
MySQLOperatorTable.
MySQL's JSON functions are rarely conflict with what are from standard, and 
they extends the JSON ability of MySQL in many sides. I think it might be a 
good try to treat functions from both MySQL's and standard's as a whole JSON 
operator suite. 
Users may want to directly use any function they know, I think it is better to 
not to confuse them with the truth that what's in standard and what's not. 
For the conflict part like JSON_OBJECT(key , value) and JSON_OBJECT(key : 
value), I think we can just support both. 

What do you think?



Hongze
 
From: Stamatis Zampetakis
Date: 2019-02-01 06:32
To: dev
Subject: Re: Integrating MySQL's JSON functions
Nice document Hongze!
 
Since the functions are only present in MySQL why not create a
MySQLOperatorTable and put them there? I went over the discussion in
CALCITE-2791 but I did not understand why it is preferable to put them in
the SqlStdOperatorTable.
 
Στις Πέμ, 31 Ιαν 2019 στις 10:51 μ.μ., ο/η Julian Hyde 
έγραψε:
 
> +1
>
> In the reference doc, let’s make it clear that these are in MySQL but not
> in the standard. (Unlike some of their other extensions to standard SQL,
> MySQL seem to have done a good job - they are well-designed and
> well-documented.)
>
> > On Jan 31, 2019, at 10:20 AM, Hongze Zhang  wrote:
> >
> > Hi all,
> >
> >
> > There is a PR CALCITE-2791[1] from xuqianjin about adding a MySQL
> function JSON_TYPE[2], and I want to know what do you think about it.
> >
> >
> > JSON_TYPE is not a standard JSON function defined by SQL:2016. In MySQL
> the use of this function is to get the type of a JSON doc. The similar
> operator in Calcite 1.18.0 is "IS JSON (VALUE/SCALAR/OBJECT/ARRAY)",
> however sometimes JSON_TYPE can do more than it.
> >
> > As for other non-standard JSON functions, I made a simple summary table
> about JSON support (mainly as functions) details of some of most important
> SQL implementations. you can open it via link
> https://docs.google.com/spreadsheets/d/1A6vrEEu3WK49dJ5mfaYeMLFvAy0JR9uoJsiaKhxUal4/edit#gid=0
> >
> >
> > And I suggest to introduce some JSON functions that is from MySQL and
> might be useful for Calcite but not supported, not Just JSON_TYPE:
> > 1. JSON_INSERT / JSON_SET / JSON_REPLACE /JSON_REMOVE [3]
> > 2. JSON_DEPTH / JSON_LENGTH / JSON_TYPE [4]
> > I think the PR[1] can be a good start.
> >
> >
> > Does anyone have thought about this? Looking forward to your reply.
> >
> >
> > [1] https://issues.apache.org/jira/browse/CALCITE-2791
> > [2]
> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
> > [3]
> https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html
> > [4]
> https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html
>
>

Integrating MySQL's JSON functions

[Hongze Zhang]

Hi all,


There is a PR CALCITE-2791[1] from xuqianjin about adding a MySQL function 
JSON_TYPE[2], and I want to know what do you think about it.


JSON_TYPE is not a standard JSON function defined by SQL:2016. In MySQL the use 
of this function is to get the type of a JSON doc. The similar operator in 
Calcite 1.18.0 is "IS JSON (VALUE/SCALAR/OBJECT/ARRAY)", however sometimes 
JSON_TYPE can do more than it.

As for other non-standard JSON functions, I made a simple summary table about 
JSON support (mainly as functions) details of some of most important SQL 
implementations. you can open it via link 
https://docs.google.com/spreadsheets/d/1A6vrEEu3WK49dJ5mfaYeMLFvAy0JR9uoJsiaKhxUal4/edit#gid=0


And I suggest to introduce some JSON functions that is from MySQL and might be 
useful for Calcite but not supported, not Just JSON_TYPE:
1. JSON_INSERT / JSON_SET / JSON_REPLACE /JSON_REMOVE [3]
2. JSON_DEPTH / JSON_LENGTH / JSON_TYPE [4]
I think the PR[1] can be a good start.


Does anyone have thought about this? Looking forward to your reply.


[1] https://issues.apache.org/jira/browse/CALCITE-2791
[2] 
https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
[3] https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html
[4] https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html 

[jira] [Created] (CALCITE-2787) Json aggregate calls with different null clause get incorrectly merged during converting from sql to rel

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2787:
-

 Summary: Json aggregate calls with different  null clause get 
incorrectly merged during converting from sql to rel
 Key: CALCITE-2787
 URL: https://issues.apache.org/jira/browse/CALCITE-2787
 Project: Calcite
  Issue Type: Bug
  Components: core
Affects Versions: 1.18.0
Reporter: Hongze Zhang
Assignee: Julian Hyde


SQL:
 
{code:sql}
WITH tab(x) AS (VALUES 1, 2, 1, CAST(NULL AS INTEGER))
SELECT JSON_ARRAYAGG(x), JSON_ARRAYAGG(x NULL ON NULl) FROM tab
{code}

Produces:

{code}
EXPR$0  |EXPR$1  |
||
[1,2,1] |[1,2,1] |
{code}

Should be:

{code}
EXPR$0  |EXPR$1  |
||
[1,2,1] |[1,2,1,null] |
{code}




--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Created] (CALCITE-2786) Add order by clause support for JSON_ARRAYAGG

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2786:
-

 Summary: Add order by clause support for JSON_ARRAYAGG
 Key: CALCITE-2786
 URL: https://issues.apache.org/jira/browse/CALCITE-2786
 Project: Calcite
  Issue Type: Bug
  Components: core
Reporter: Hongze Zhang
Assignee: Hongze Zhang






--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Created] (CALCITE-2785) In EnumerableAggregate, wrong result produced If there are sorted aggregates and non-sorted aggregates at the same time.

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2785:
-

 Summary: In EnumerableAggregate, wrong result produced If there 
are sorted aggregates and non-sorted aggregates at the same time.
 Key: CALCITE-2785
 URL: https://issues.apache.org/jira/browse/CALCITE-2785
 Project: Calcite
  Issue Type: Bug
  Components: core
Affects Versions: 1.18.0
Reporter: Hongze Zhang
Assignee: Hongze Zhang


SQL:

{code:sql}
WITH tab(X) AS  (values 1, 2, 1)
SELECT COLLECT(x), COLLECT(x) WITHIN GROUP (ORDER BY 1) FROM tab
{code}

Result:

{code}
EXPR$0 |EXPR$1  |
---||
   |[1,2,1] |
{code}

Correct result:
{code}
EXPR$0 |EXPR$1  |
---||
[1,2,1]|[1,2,1] |
{code}



The problem is introduced by 
[CALCITE-2224|https://issues.apache.org/jira/browse/CALCITE-2224].



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Re:JSON(B) Support like in Postgres

[Hongze Zhang]

Hi Julian, 


If I remember right, Calcite does not support Postgres's json and jsonb 
datatype in current version (1.18).
Calcite has built-in JSON support (see CALCITE-2266[1]) similar to what has 
been implemented in Oracle and MS SQL, It is a earlier version of the whole 
JSON things described in the SQL standard. For now these functions[2] mainly 
accepts character datatypes as JSON input, other data types are not supported 
yet.


I am not so familiar with Postgres's JSON implementation but I think the 
implementation are wildly different with Calcite's, Some functions have 
conflict syntax with Calcite's function(E.g. JSON_VALUE).
If you'd like to process JSON using Postgres's syntax, maybe at first you need 
to change the Parser code of Calcite to support Postgres's json and jsonb 
Operators, and also disable the built-in JSON_VALUE function then add 
Postgres's JSON functions (if you want to use Postgres's JSON_VALUE function on 
Calcite 1.18).


Best,
Hongze


[1] https://issues.apache.org/jira/browse/CALCITE-2266
[2] http://calcite.apache.org/docs/reference.html#json-functions







At 2018-12-29 18:18:59, "Julian Feinauer"  wrote:
>Hi all,
>
>we use Postgres a lot and make heavy use of the JSONB datatype [1].
>Is there support for something similar in Calcite?
>If so, can someone point me to the docs as I’ve not found anything in the list 
>of builtin functions.
>
>There are several reasons why it would be cool for us to have Calcite in Front 
>of postgres to do some query rewriting if necessary but for that we would 
>definitely need support for something which could be transformed to JSONB.
>
>Best
>Julian
>
>[1] https://www.postgresql.org/docs/9.5/functions-json.html

[jira] [Created] (CALCITE-2670) Combine similar JSON aggregate functions in operator table

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2670:
-

 Summary: Combine similar JSON aggregate functions in operator table
 Key: CALCITE-2670
 URL: https://issues.apache.org/jira/browse/CALCITE-2670
 Project: Calcite
  Issue Type: Improvement
  Components: core
Reporter: Hongze Zhang
Assignee: Julian Hyde


Improvement contains following:
 # Combine *JSON_ARRAYAGG_NULL_ON_NULL* and *JSON_ARRAYAGG_ABSENT_ON_NULL*
 # Combine *JSON_OBJECTAGG_NULL_ON_NULL* and *JSON_OBJECTAGG_ABSENT_ON_NULL*
 # Add *SYMBOL* type Support for *JavaTypeFactoryImpl#getJavaClass*
 This is to generate *Enum* java type for *SYMBOL*. Current version of Calcite 
generates *Object[]*, which delivers type casting error, or some method 
compatible problems when we combine the functions above.
 # Add SQL-to-Rel test cases for JSON functions.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Re: JSON support

[Hongze Zhang]

I am very glad to see the patch has been accepted，and many thanks to all of you 
(Michael, Julian, Vladimir, ...) for continuously paying attention to this 
thread, and for the great review work. 

Thank you again!

Hongze








At 2018-11-10 02:30:31, "Julian Hyde"  wrote:
>Hongze has also just contributed WITHIN GROUP [1] - basically the ability for 
>aggregate functions to operate on sorted input, the same way that many window 
>functions do. So, thanks for the great contributions.
>
>But also, I want to thank our committers (Michael, Vladimir). Shepherding 
>complex PRs into the code base while maintaining the high standards of the 
>code is difficult and time-consuming.
>
>Julian
>
>[1] https://issues.apache.org/jira/browse/CALCITE-2224 
>
>> On Nov 9, 2018, at 10:03 AM, Stamatis Zampetakis  wrote:
>> 
>> Great addition! Many thanks to all (Hongze, Michael, Vladimir) of you :)
>> 
>> Στις Παρ, 9 Νοε 2018 στις 5:44 μ.μ., ο/η Michael Mior 
>> έγραψε:
>> 
>>> Just wanted to put out a thanks to Hongze on this thread for the numerous
>>> changes that went into the initial pull request which has now landed! Happy
>>> to see this completed.
>>> 
>>> --
>>> Michael Mior
>>> mm...@apache.org
>>> 
>>> 
>>> Le mer. 29 août 2018 à 16:11, Julian Hyde  a écrit :
>>> 
 Somehow I missed this… we have a pull request for JSON support. It’s a
>>> big
 change (both in terms of importance and the amount of effort).
 
 https://github.com/apache/calcite/pull/785 <
 https://github.com/apache/calcite/pull/785>
 
 https://issues.apache.org/jira/browse/CALCITE-2266 <
 https://issues.apache.org/jira/browse/CALCITE-2266>
 
 I made a quick review, and it looks good. I see Vladimir has reviewed.
>>> Can
 someone else (not necessarily a committer) take a look?
 
 Julian
 
 
 
>>> 

[jira] [Created] (CALCITE-2648) Implementation of EnumerableWindow does not preserve input collation

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2648:
-

 Summary: Implementation of EnumerableWindow does not preserve 
input collation
 Key: CALCITE-2648
 URL: https://issues.apache.org/jira/browse/CALCITE-2648
 Project: Calcite
  Issue Type: Bug
Reporter: Hongze Zhang
Assignee: Julian Hyde


Here is a case:
{code:sql}
select x, COUNT(*) OVER (PARTITION BY x) from (values (20), (35)) as t(x) ORDER 
BY x
{code}
Final plan:
{code:java}
EnumerableWindow(window#0=[window(partition {0} order by [] range between 
UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [COUNT()])])
  EnumerableValues(tuples=[[{ 20 }, { 35 }]])
{code}
Output rows:
{code:java}
X  |EXPR$1 |
---|---|
35 |1  |
20 |1  |
{code}
EnumerableWindow is supposed to preserve input collations, as a result 
EnumerableSort is ignored. However the implementation of EnumerableWindow 
generates non-ordered output (when PARTITION BY clause is used).



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Created] (CALCITE-2593) Failed to plan when SQL is like "sum(X + 1) filter (where Y)"

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2593:
-

 Summary: Failed to plan when SQL is like "sum(X + 1) filter (where 
Y)"
 Key: CALCITE-2593
 URL: https://issues.apache.org/jira/browse/CALCITE-2593
 Project: Calcite
  Issue Type: Improvement
  Components: core
Reporter: Hongze Zhang
Assignee: Julian Hyde


{code:java}
java.lang.RuntimeException: exception while executing [select sum(X + 1) filter 
(where Y) as "SET" from (values (1, TRUE), (2, TRUE)) AS t(X, Y) limit 10] at 
org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1366)
 at 
org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1339)
 at 
org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1302)
 at org.apache.calcite.test.JdbcTest.testWithinGroupClause5(JdbcTest.java:6736) 
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at 
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 
at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
 at java.lang.reflect.Method.invoke(Method.java:498) at 
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
 at 
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
 at 
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
 at 
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
 at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at 
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
 at 
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
 at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at 
org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at 
org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at 
org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at 
org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at 
org.junit.runners.ParentRunner.run(ParentRunner.java:363) at 
org.junit.runner.JUnitCore.run(JUnitCore.java:137) at 
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
 at 
com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
 at 
com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
 at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70) 
Caused by: java.lang.RuntimeException: With materializationsEnabled=false, 
limit=0 at 
org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:573) at 
org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1362)
 ... 25 more Caused by: java.sql.SQLException: Error while executing SQL 
"select sum(X + 1) filter (where Y) as "SET" from (values (1, TRUE), (2, TRUE)) 
AS t(X, Y) limit 10": Node [rel#22:Subset#3.ENUMERABLE.[]] could not be 
implemented; planner state: Root: rel#22:Subset#3.ENUMERABLE.[] Original rel: 
LogicalSort(subset=[rel#22:Subset#3.ENUMERABLE.[]], fetch=[10]): rowcount = 
1.0, cumulative cost = {1.0 rows, 4.0 cpu, 0.0 io}, id = 17 
LogicalAggregate(subset=[rel#16:Subset#2.NONE.[]], group=[{}], SET=[SUM($0) 
FILTER $1]): rowcount = 1.0, cumulative cost = {1.1375000476837158 rows, 0.0 
cpu, 0.0 io}, id = 15 LogicalProject(subset=[rel#14:Subset#1.NONE.[1]], 
$f0=[+($0, 1)], Y=[$1]): rowcount = 2.0, cumulative cost = {2.0 rows, 4.0 cpu, 
0.0 io}, id = 13 LogicalValues(subset=[rel#12:Subset#0.NONE.[]], tuples=[[{ 1, 
true }, { 2, true }]]): rowcount = 2.0, cumulative cost = {2.0 rows, 1.0 cpu, 
0.0 io}, id = 1 Sets: Set#0, type: RecordType(INTEGER X, BOOLEAN Y) 
rel#12:Subset#0.NONE.[], best=null, importance=0.6561 
rel#1:LogicalValues.NONE.[[0, 1], [1]](type=RecordType(INTEGER X, BOOLEAN 
Y),tuples=[{ 1, true }, { 2, true }]), rowcount=2.0, cumulative cost={inf} 
rel#37:Subset#0.ENUMERABLE.[], best=rel#36, importance=0.32805 
rel#36:EnumerableValues.ENUMERABLE.[[0, 1], [1]](type=RecordType(INTEGER X, 
BOOLEAN Y),tuples=[{ 1, true }, { 2, true }]), rowcount=2.0, cumulative 
cost={2.0 rows, 1.0 cpu, 0.0 io} Set#1, type: RecordType(INTEGER $f0, BOOLEAN 
Y) rel#14:Subset#1.NONE.[1], best=null, importance=0.7291 
rel#13:LogicalProject.NONE.[[1]](input=rel#12:Subset#0.NONE.[],$f0=+($0, 
1),Y=$1), rowcount=2.0, cumulative cost={inf} rel#41:Subset#1.ENUMERABLE.[], 
best=rel#40, importance=0.36455 
rel#40:EnumerableProject.ENUMERABLE.[](input=rel#37:Subset#0.ENUMERABLE.[],$f0=+($0,
 1),Y=$1), rowcount=2.0, cumulative cost={4.0 rows, 5.0 cpu, 0.0 io} 
rel#43:Subset#1.ENUMERABLE.[1], best=null, importance=0.7291 Set#2, 
type: RecordType(INTEGER SET) rel#16:Subset#2.NONE.[], best=null, 
impor

[jira] [Created] (CALCITE-2539) Several test case not passed in CalciteSqlOperatorTest.java

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2539:
-

 Summary: Several test case not passed in 
CalciteSqlOperatorTest.java
 Key: CALCITE-2539
 URL: https://issues.apache.org/jira/browse/CALCITE-2539
 Project: Calcite
  Issue Type: Bug
  Components: core
Reporter: Hongze Zhang
Assignee: Julian Hyde


After fixing [CALCITE-2535|https://issues.apache.org/jira/browse/CALCITE-2535], 
several test cases turn to failed and should be checked again.

Listing the related test cases:

* testCastInvalid
* testWeek
* testLiteralBeyondLimit
* testExtractIntervalDayTime
* testCastExactNumericLimits
* testDayOfWeek
* testDayOfYear
* testExtractTimestamp
* testCastToString
* testDivideOperator
* testSimilarToOperator
* testJdbcFn
* testCastStringToDateTime



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Created] (CALCITE-2535) Runtime failure check not working in SqlTesterImpl.java

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2535:
-

 Summary: Runtime failure check not working in SqlTesterImpl.java
 Key: CALCITE-2535
 URL: https://issues.apache.org/jira/browse/CALCITE-2535
 Project: Calcite
  Issue Type: Bug
  Components: core
Reporter: Hongze Zhang
Assignee: Julian Hyde


Problem at SqlTesterImpl#checkFails:

When argument "runtime" is specified as true, error assertion and error message 
matching will not be performed.

This issue could result in wrong test result in SqlOperatorBaseTest.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

[jira] [Created] (CALCITE-2530) Reasonable behavior of TRIM function when the length of trim character is not 1(one)

[Hongze Zhang (JIRA)]

Hongze Zhang created CALCITE-2530:
-

 Summary: Reasonable behavior of TRIM function when the length of 
trim character is not 1(one)
 Key: CALCITE-2530
 URL: https://issues.apache.org/jira/browse/CALCITE-2530
 Project: Calcite
  Issue Type: Improvement
  Components: core
Reporter: Hongze Zhang
Assignee: Julian Hyde


Calcite's TRIM function takes the first character of  string to 
trim the , whereas SQL standard suggests to report a "trim error" 
when the length of  is not 1.

I am not sure if this could be treated as an issue(since the calcite's way 
might has better availability), until I found this:

[https://issues.apache.org/jira/browse/BEAM-4704|https://issues.apache.org/jira/browse/BEAM-4704]

It seems user could be sometimes confused with the behavior of TRIM function.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)