Re: [DISCUSS] not equal operator vs less than combining greater than

[Yanjing Wang]

Thanks Benchao,

I changed the canonizing HEP_PROGRAM to only FILTER_REDUCE_EXPRESSIONS rule
so that the materialized view can't be applied, the original HEP_PROGRAM is
ok. I don't know if the original HEP_PROGRAM is a must and I expect it
should be applied without the original HEP_PROGRAM in this case.

Benchao Li  于2022年6月1日周三 11:53写道：

> Hi Yanjing,
>
> I know your concerns, and I know the difference between the inferred types.
> However, my point is that this does not affect the materialization
> substitution.
>
> My branch is:
> https://github.com/libenchao/calcite/tree/5169-simplification-improvement
> You can try your test case on this branch, or cherry-pick the commit to
> your branch.
>
> Yanjing Wang  于2022年6月1日周三 11:34写道：
>
> > Benchao,
> >
> > I noticed your Sargs in RelOptRulesTest.xml, you can see
> >   LogicalFilter(condition=[SEARCH($1, Sarg[(-∞..'':VARCHAR(20)),
> > ('':VARCHAR(20)..'3':VARCHAR(20)), ('3':VARCHAR(20)..+∞)]:VARCHAR(20))])
> > vs
> >   LogicalFilter(condition=[SEARCH($1, Sarg[(-∞..'':CHAR(1)),
> > ('':CHAR(1)..'3'), ('3'..+∞)]:CHAR(1))])
> >
> > the difference between VARCHAR(20) and CHAR(1) is my problem.
> >
> > --Your test case passed on my branch.
> > It's not on my branch merged with master and your pr, did you test
> > in MaterializedViewSubstitutionVisitorTest? What's your branch version?
> >
> >
> > Benchao Li  于2022年5月31日周二 19:55写道：
> >
> > > Hi Yanjing,
> > >
> > > Your test case passed on my branch. Can you try it with my improvement
> > > in https://github.com/apache/calcite/pull/2821?
> > >
> > > Yanjing Wang  于2022年5月31日周二 16:06写道：
> > >
> > > > Hi, Benchao,
> > > >
> > > > The pr is good, and I noticed that the two sarg types is not same.
> > > >
> > > > Add the following test in MaterializedViewSubstitutionVisitorTest,
> you
> > > will
> > > > see the substitution fails and sarg types is not same.
> > > >
> > > > @Test void testFilter2() {
> > > > sql("select * from \"EMP\" where (\"ENAME\" > '' or \"ENAME\" <
> > '') "
> > > > + "and (\"ENAME\" > '3' or \"ENAME\" < '3')",
> > > > "select * from \"EMP\" where \"ENAME\" <> '' and \"ENAME\" <>
> > > '3'")
> > > > .withDefaultSchemaSpec(CalciteAssert.SchemaSpec.SCOTT)
> > > > .ok();
> > > >   }
> > > >
> > > > Benchao Li  于2022年5月30日周一 14:47写道：
> > > >
> > > > > Hi Yanjing,
> > > > >
> > > > > The type derivation is different in this case indeed.
> > > > > However, after my fix[1], they both can be optimized to Sarg,
> > > > > and they have the same plan structure.
> > > > > (I've added tests to show this, welcome review)
> > > > >
> > > > > In your case, if you have some different handling logic in later
> > steps,
> > > > > maybe you can share with us.
> > > > >
> > > > > [1] https://github.com/apache/calcite/pull/2821
> > > > >
> > > > > Yanjing Wang  于2022年5月30日周一 12:14写道：
> > > > >
> > > > > > Thanks Julian, Benchao,
> > > > > >
> > > > > > This is not only a problem about expression simplification, also
> > type
> > > > > > consistency in conversion[1].
> > > > > > As Xiong said in CALCITE-4993
> > > > > >  , "EQUALS
> and
> > > > > > NOT-EQUALS comparison. Because they use the same
> LEAST_RESTRICTIVE
> > > > > > strategy to validate the parameter. Other comparators use the
> > COMPARE
> > > > > > strategy."
> > > > > >
> > > > > > This will cause NOT-EQUALS comparison will use different strategy
> > > from
> > > > > > other compare operator like LESS THAN to generate literal
> operand's
> > > > type.
> > > > > >
> > > > > > In my case, if "name" column has a type varchar(256),  "name" <>
> ''
> > > and
> > > > > > "name" <> '3' will cause '' and '3' have type varchar(256), but
> > > > ("name"
> > > > > >
> > > > > > '' or "name" < '') and ("name" > '3' or "name" < '3') will cause
> ''
> > > and
> > > > > '3'
> > > > > > has type char(0) and char(1) respectively.
> > > > > >
> > > > > > [1]
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> https://github.com/apache/calcite/blob/f278efb0411ba29c7bd167f5d02e566bf542acb3/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java#L993
> > > > > >
> > > > > > Benchao Li  于2022年5月29日周日 15:35写道：
> > > > > >
> > > > > > > I've filed an issue[1] to track this.
> > > > > > >
> > > > > > > [1] https://issues.apache.org/jira/browse/CALCITE-5169
> > > > > > >
> > > > > > > Benchao Li  于2022年5月29日周日 11:19写道：
> > > > > > >
> > > > > > > > Hi all,
> > > > > > > >
> > > > > > > > I've confirmed it.
> > > > > > > > The reason why different plans for queries:
> > > > > > > > query 1:
> > > > > > > > select * from "emps" where "name" <> '' and "name" <> '3'
> > > > > > > > query 2:
> > > > > > > > select * from "emps" where ("name" > '' or "name" < '') and
> > > > ("name" >
> > > > > > '3'
> > > > > > > > or "name" < '3')
> > > > > > > > is not from the operator consistency.
> > > > > > > > It's just because the expression: ("name" > '' or "name" <
> '')
> > > and

Re: [DISCUSS] not equal operator vs less than combining greater than

[Benchao Li]

Hi Yanjing,

I know your concerns, and I know the difference between the inferred types.
However, my point is that this does not affect the materialization
substitution.

My branch is:
https://github.com/libenchao/calcite/tree/5169-simplification-improvement
You can try your test case on this branch, or cherry-pick the commit to
your branch.

Yanjing Wang  于2022年6月1日周三 11:34写道：

> Benchao,
>
> I noticed your Sargs in RelOptRulesTest.xml, you can see
>   LogicalFilter(condition=[SEARCH($1, Sarg[(-∞..'':VARCHAR(20)),
> ('':VARCHAR(20)..'3':VARCHAR(20)), ('3':VARCHAR(20)..+∞)]:VARCHAR(20))])
> vs
>   LogicalFilter(condition=[SEARCH($1, Sarg[(-∞..'':CHAR(1)),
> ('':CHAR(1)..'3'), ('3'..+∞)]:CHAR(1))])
>
> the difference between VARCHAR(20) and CHAR(1) is my problem.
>
> --Your test case passed on my branch.
> It's not on my branch merged with master and your pr, did you test
> in MaterializedViewSubstitutionVisitorTest? What's your branch version?
>
>
> Benchao Li  于2022年5月31日周二 19:55写道：
>
> > Hi Yanjing,
> >
> > Your test case passed on my branch. Can you try it with my improvement
> > in https://github.com/apache/calcite/pull/2821?
> >
> > Yanjing Wang  于2022年5月31日周二 16:06写道：
> >
> > > Hi, Benchao,
> > >
> > > The pr is good, and I noticed that the two sarg types is not same.
> > >
> > > Add the following test in MaterializedViewSubstitutionVisitorTest, you
> > will
> > > see the substitution fails and sarg types is not same.
> > >
> > > @Test void testFilter2() {
> > > sql("select * from \"EMP\" where (\"ENAME\" > '' or \"ENAME\" <
> '') "
> > > + "and (\"ENAME\" > '3' or \"ENAME\" < '3')",
> > > "select * from \"EMP\" where \"ENAME\" <> '' and \"ENAME\" <>
> > '3'")
> > > .withDefaultSchemaSpec(CalciteAssert.SchemaSpec.SCOTT)
> > > .ok();
> > >   }
> > >
> > > Benchao Li  于2022年5月30日周一 14:47写道：
> > >
> > > > Hi Yanjing,
> > > >
> > > > The type derivation is different in this case indeed.
> > > > However, after my fix[1], they both can be optimized to Sarg,
> > > > and they have the same plan structure.
> > > > (I've added tests to show this, welcome review)
> > > >
> > > > In your case, if you have some different handling logic in later
> steps,
> > > > maybe you can share with us.
> > > >
> > > > [1] https://github.com/apache/calcite/pull/2821
> > > >
> > > > Yanjing Wang  于2022年5月30日周一 12:14写道：
> > > >
> > > > > Thanks Julian, Benchao,
> > > > >
> > > > > This is not only a problem about expression simplification, also
> type
> > > > > consistency in conversion[1].
> > > > > As Xiong said in CALCITE-4993
> > > > >  , "EQUALS and
> > > > > NOT-EQUALS comparison. Because they use the same  LEAST_RESTRICTIVE
> > > > > strategy to validate the parameter. Other comparators use the
> COMPARE
> > > > > strategy."
> > > > >
> > > > > This will cause NOT-EQUALS comparison will use different strategy
> > from
> > > > > other compare operator like LESS THAN to generate literal operand's
> > > type.
> > > > >
> > > > > In my case, if "name" column has a type varchar(256),  "name" <> ''
> > and
> > > > > "name" <> '3' will cause '' and '3' have type varchar(256), but
> > > ("name"
> > > > >
> > > > > '' or "name" < '') and ("name" > '3' or "name" < '3') will cause ''
> > and
> > > > '3'
> > > > > has type char(0) and char(1) respectively.
> > > > >
> > > > > [1]
> > > > >
> > > > >
> > > >
> > >
> >
> https://github.com/apache/calcite/blob/f278efb0411ba29c7bd167f5d02e566bf542acb3/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java#L993
> > > > >
> > > > > Benchao Li  于2022年5月29日周日 15:35写道：
> > > > >
> > > > > > I've filed an issue[1] to track this.
> > > > > >
> > > > > > [1] https://issues.apache.org/jira/browse/CALCITE-5169
> > > > > >
> > > > > > Benchao Li  于2022年5月29日周日 11:19写道：
> > > > > >
> > > > > > > Hi all,
> > > > > > >
> > > > > > > I've confirmed it.
> > > > > > > The reason why different plans for queries:
> > > > > > > query 1:
> > > > > > > select * from "emps" where "name" <> '' and "name" <> '3'
> > > > > > > query 2:
> > > > > > > select * from "emps" where ("name" > '' or "name" < '') and
> > > ("name" >
> > > > > '3'
> > > > > > > or "name" < '3')
> > > > > > > is not from the operator consistency.
> > > > > > > It's just because the expression: ("name" > '' or "name" < '')
> > and
> > > > > > ("name"
> > > > > > > > '3'
> > > > > > > or "name" < '3') cannot be translated into Sarg for now.
> > > > > > >
> > > > > > > I'll file a Jira issue to track and improve this.
> > > > > > >
> > > > > > > Benchao Li  于2022年5月28日周六 09:30写道：
> > > > > > >
> > > > > > >> FYI, the issue might be this one:
> > > > > > >> https://issues.apache.org/jira/browse/CALCITE-4993
> > > > > > >>
> > > > > > >> I also looked into this in this direction yesterday, however,
> I
> > > > didn't
> > > > > > >> confirm it yet.
> > > > > > >> That's why I didn't reply to this email before. I will do
> > further
> > 

Re: [DISCUSS] not equal operator vs less than combining greater than

[Yanjing Wang]

Benchao,

I noticed your Sargs in RelOptRulesTest.xml, you can see
  LogicalFilter(condition=[SEARCH($1, Sarg[(-∞..'':VARCHAR(20)),
('':VARCHAR(20)..'3':VARCHAR(20)), ('3':VARCHAR(20)..+∞)]:VARCHAR(20))])
vs
  LogicalFilter(condition=[SEARCH($1, Sarg[(-∞..'':CHAR(1)),
('':CHAR(1)..'3'), ('3'..+∞)]:CHAR(1))])

the difference between VARCHAR(20) and CHAR(1) is my problem.

--Your test case passed on my branch.
It's not on my branch merged with master and your pr, did you test
in MaterializedViewSubstitutionVisitorTest? What's your branch version?


Benchao Li  于2022年5月31日周二 19:55写道：

> Hi Yanjing,
>
> Your test case passed on my branch. Can you try it with my improvement
> in https://github.com/apache/calcite/pull/2821?
>
> Yanjing Wang  于2022年5月31日周二 16:06写道：
>
> > Hi, Benchao,
> >
> > The pr is good, and I noticed that the two sarg types is not same.
> >
> > Add the following test in MaterializedViewSubstitutionVisitorTest, you
> will
> > see the substitution fails and sarg types is not same.
> >
> > @Test void testFilter2() {
> > sql("select * from \"EMP\" where (\"ENAME\" > '' or \"ENAME\" < '') "
> > + "and (\"ENAME\" > '3' or \"ENAME\" < '3')",
> > "select * from \"EMP\" where \"ENAME\" <> '' and \"ENAME\" <>
> '3'")
> > .withDefaultSchemaSpec(CalciteAssert.SchemaSpec.SCOTT)
> > .ok();
> >   }
> >
> > Benchao Li  于2022年5月30日周一 14:47写道：
> >
> > > Hi Yanjing,
> > >
> > > The type derivation is different in this case indeed.
> > > However, after my fix[1], they both can be optimized to Sarg,
> > > and they have the same plan structure.
> > > (I've added tests to show this, welcome review)
> > >
> > > In your case, if you have some different handling logic in later steps,
> > > maybe you can share with us.
> > >
> > > [1] https://github.com/apache/calcite/pull/2821
> > >
> > > Yanjing Wang  于2022年5月30日周一 12:14写道：
> > >
> > > > Thanks Julian, Benchao,
> > > >
> > > > This is not only a problem about expression simplification, also type
> > > > consistency in conversion[1].
> > > > As Xiong said in CALCITE-4993
> > > >  , "EQUALS and
> > > > NOT-EQUALS comparison. Because they use the same  LEAST_RESTRICTIVE
> > > > strategy to validate the parameter. Other comparators use the COMPARE
> > > > strategy."
> > > >
> > > > This will cause NOT-EQUALS comparison will use different strategy
> from
> > > > other compare operator like LESS THAN to generate literal operand's
> > type.
> > > >
> > > > In my case, if "name" column has a type varchar(256),  "name" <> ''
> and
> > > > "name" <> '3' will cause '' and '3' have type varchar(256), but
> > ("name"
> > > >
> > > > '' or "name" < '') and ("name" > '3' or "name" < '3') will cause ''
> and
> > > '3'
> > > > has type char(0) and char(1) respectively.
> > > >
> > > > [1]
> > > >
> > > >
> > >
> >
> https://github.com/apache/calcite/blob/f278efb0411ba29c7bd167f5d02e566bf542acb3/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java#L993
> > > >
> > > > Benchao Li  于2022年5月29日周日 15:35写道：
> > > >
> > > > > I've filed an issue[1] to track this.
> > > > >
> > > > > [1] https://issues.apache.org/jira/browse/CALCITE-5169
> > > > >
> > > > > Benchao Li  于2022年5月29日周日 11:19写道：
> > > > >
> > > > > > Hi all,
> > > > > >
> > > > > > I've confirmed it.
> > > > > > The reason why different plans for queries:
> > > > > > query 1:
> > > > > > select * from "emps" where "name" <> '' and "name" <> '3'
> > > > > > query 2:
> > > > > > select * from "emps" where ("name" > '' or "name" < '') and
> > ("name" >
> > > > '3'
> > > > > > or "name" < '3')
> > > > > > is not from the operator consistency.
> > > > > > It's just because the expression: ("name" > '' or "name" < '')
> and
> > > > > ("name"
> > > > > > > '3'
> > > > > > or "name" < '3') cannot be translated into Sarg for now.
> > > > > >
> > > > > > I'll file a Jira issue to track and improve this.
> > > > > >
> > > > > > Benchao Li  于2022年5月28日周六 09:30写道：
> > > > > >
> > > > > >> FYI, the issue might be this one:
> > > > > >> https://issues.apache.org/jira/browse/CALCITE-4993
> > > > > >>
> > > > > >> I also looked into this in this direction yesterday, however, I
> > > didn't
> > > > > >> confirm it yet.
> > > > > >> That's why I didn't reply to this email before. I will do
> further
> > > > > >> verifications and
> > > > > >> post the result here later.
> > > > > >>
> > > > > >>
> > > > > >>
> > > > > >> Julian Hyde  于2022年5月27日周五 23:52写道：
> > > > > >>
> > > > > >>> I think there’s a JIRA case for this. The implicit casts
> prevent
> > > SARG
> > > > > >>> simplification from kicking in. In SARG representation the
> > > > expressions
> > > > > >>> would be the same. Which is why we love SARGs.
> > > > > >>>
> > > > > >>> Julian
> > > > > >>>
> > > > > >>> > On May 26, 2022, at 17:49, Yanjing Wang <
> > > zhuangzixiao...@gmail.com
> > > > >
> > > > > >>> wrote:
> > > > > >>> >
> > > > > >>> > Hi community,
> > > > > 

Re: Implementing JSON_TABLE, even hackily as a UDF?

[Gavin Ray]

I think you're probably right, the functionality for reading JSON must
exist in the File adapter already
Then I'd just need to figure out how to read from "HTTP_GET" UDF as the
JSON contents

Ty, will go look at the source for the adapter

On Tue, May 31, 2022 at 6:51 PM Julian Hyde  wrote:

> Is there any overlap with the file adapter? The file adapter can read
> using various transports (file, http, optional compression) and various
> formats (csv, json, html tables).
>
> > On May 31, 2022, at 12:47 PM, Gavin Ray  wrote:
> >
> > I don't think it's SQL Standard, but essentially it takes a text value
> > containing JSON data (one record or an array) and converts it into a
> > relational table:
> >
> > https://docs.oracle.com/database/121/SQLRF/functions092.htm
> >
> > I've managed to get most of this working, now I seem to be failing to put
> > the syntax together correctly for the query
> > I currently have:
> > - A UDF ("HTTP_GET") which can make HTTP calls and return the data as a
> > String
> > - A table type for Collection> ("MapCollectionTable")
> > - A TableFunctionImpl ("JsonTableFunction"), which takes a JSON string,
> > reads it with Jackson, and converts it into a "MapCollectionTable"
> >
> > The following work:
> >
> > SELECT HTTP_GET('https://jsonplaceholder.typicode.com/posts')
> >
> > SELECT * FROM TABLE(
> >  JSON_TABLE('[{ "id": "1", "name": "foo" }, { "id": "2", "name": "bar"
> > }]'))
> >
> > But trying to combine them fails =/
> >
> > SELECT * FROM TABLE(
> >  JSON_TABLE(HTTP_GET('https://jsonplaceholder.typicode.com/posts')))
> >
> > The TableFunctionImpl is receiving "null" for the JSON value
> > I guess it's maybe expecting static data?
> >
> > Calcite JSON_TABLE wip 2 (github.com)
> > <
> https://gist.github.com/GavinRay97/fbd16dd2b893cb59c720e6d514c5e39a#file-main-java
> >
> >
> > On Tue, May 31, 2022 at 2:44 PM Julian Hyde  wrote:
> >
> >> What is JSON_TABLE? Is it in the SQL standard? Can you give a simple
> >> example?
> >>
> >> On Tue, May 31, 2022 at 10:03 AM Gavin Ray 
> wrote:
> >>>
> >>> I dug up some examples from Github of using the "TableFunctionImpl"
> >> class:
> >>>
> >>> mat-calcite-plugin/TableFunctions.java at
> >>> a57a5ba80768066714c22bd9e8a9b529d4cb9a6b · vlsi/mat-calcite-plugin
> >>> (github.com)
> >>> <
> >>
> https://github.com/vlsi/mat-calcite-plugin/blob/a57a5ba80768066714c22bd9e8a9b529d4cb9a6b/MatCalcitePlugin/src/com/github/vlsi/mat/calcite/functions/TableFunctions.java
> >>>
> >>> Calcite_sql_driver/MazeTable.java at
> >>> 6819088123e67631367ca927b112b58e4eb90829 · dream001/Calcite_sql_driver
> >>> (github.com)
> >>> <
> >>
> https://github.com/dream001/Calcite_sql_driver/blob/6819088123e67631367ca927b112b58e4eb90829/src/test/java/com/yonyou/calcite/MazeTable.java
> >>>
> >>>
> >>> Currently trying to get a TableFunction that just returns a Map >>> Object> as a relation to work
> >>> Have the below -- calling this shows that the function IS invoked, but
> >> the
> >>> data it gives back is garbage:
> >>>
> >>> Calcite JSON_TABLE WIP (github.com)
> >>> 
> >>>
> >>> This is the output of main()
> >>>
> >>> json = {"id":1, "name":"John"}
> >>> json = {"id":1, "name":"John"}
> >>> json = {"id":1, "name":"John"}
> >>> EXPR$0: id
> >>> EXPR$0: id
> >>>
> >>> I'm doing something wrong here for sure, lol.
> >>>
> >>>
> >>> On Tue, May 31, 2022 at 11:35 AM Gavin Ray 
> >> wrote:
> >>>
>  I'm interested in implementing JSON_TABLE functionality for Calcite
> 
>  This opens up some neat usecases, like adding HTTP request UDF's
>  then using JSON_TABLE to convert the result into a table:
> 
>  SELECT JSON_TABLE(
>  HTTP_GET('http://localhost:8080/api/v1/users/1'))
> 
>  Adding support for all of the functionality seems difficult,
>  but I'm wondering whether this could be done as a "SqlTableFunction"
> >> UDF?
> 
>  I'm thinking it might be possible if the JSON_TABLE udf
>  expects Map and does inference based on that?
> 
>  Something like:
> 
>  WITH users AS (
> SELECT HTTP_GET('http://localhost:8080/api/v1/users')
>  ) SELECT
> id,
> name
>  FROM
> JSON_TABLE(JSON_PATH(users, "$.0"))
> 
>  Does anyone have ideas or see issues with this approach?
>  Thank you =)
> 
> 
> 
> 
> >>
>
>

Re: Implementing JSON_TABLE, even hackily as a UDF?

[Julian Hyde]

Is there any overlap with the file adapter? The file adapter can read using 
various transports (file, http, optional compression) and various formats (csv, 
json, html tables).

> On May 31, 2022, at 12:47 PM, Gavin Ray  wrote:
> 
> I don't think it's SQL Standard, but essentially it takes a text value
> containing JSON data (one record or an array) and converts it into a
> relational table:
> 
> https://docs.oracle.com/database/121/SQLRF/functions092.htm
> 
> I've managed to get most of this working, now I seem to be failing to put
> the syntax together correctly for the query
> I currently have:
> - A UDF ("HTTP_GET") which can make HTTP calls and return the data as a
> String
> - A table type for Collection> ("MapCollectionTable")
> - A TableFunctionImpl ("JsonTableFunction"), which takes a JSON string,
> reads it with Jackson, and converts it into a "MapCollectionTable"
> 
> The following work:
> 
> SELECT HTTP_GET('https://jsonplaceholder.typicode.com/posts')
> 
> SELECT * FROM TABLE(
>  JSON_TABLE('[{ "id": "1", "name": "foo" }, { "id": "2", "name": "bar"
> }]'))
> 
> But trying to combine them fails =/
> 
> SELECT * FROM TABLE(
>  JSON_TABLE(HTTP_GET('https://jsonplaceholder.typicode.com/posts')))
> 
> The TableFunctionImpl is receiving "null" for the JSON value
> I guess it's maybe expecting static data?
> 
> Calcite JSON_TABLE wip 2 (github.com)
> 
> 
> On Tue, May 31, 2022 at 2:44 PM Julian Hyde  wrote:
> 
>> What is JSON_TABLE? Is it in the SQL standard? Can you give a simple
>> example?
>> 
>> On Tue, May 31, 2022 at 10:03 AM Gavin Ray  wrote:
>>> 
>>> I dug up some examples from Github of using the "TableFunctionImpl"
>> class:
>>> 
>>> mat-calcite-plugin/TableFunctions.java at
>>> a57a5ba80768066714c22bd9e8a9b529d4cb9a6b · vlsi/mat-calcite-plugin
>>> (github.com)
>>> <
>> https://github.com/vlsi/mat-calcite-plugin/blob/a57a5ba80768066714c22bd9e8a9b529d4cb9a6b/MatCalcitePlugin/src/com/github/vlsi/mat/calcite/functions/TableFunctions.java
>>> 
>>> Calcite_sql_driver/MazeTable.java at
>>> 6819088123e67631367ca927b112b58e4eb90829 · dream001/Calcite_sql_driver
>>> (github.com)
>>> <
>> https://github.com/dream001/Calcite_sql_driver/blob/6819088123e67631367ca927b112b58e4eb90829/src/test/java/com/yonyou/calcite/MazeTable.java
>>> 
>>> 
>>> Currently trying to get a TableFunction that just returns a Map>> Object> as a relation to work
>>> Have the below -- calling this shows that the function IS invoked, but
>> the
>>> data it gives back is garbage:
>>> 
>>> Calcite JSON_TABLE WIP (github.com)
>>> 
>>> 
>>> This is the output of main()
>>> 
>>> json = {"id":1, "name":"John"}
>>> json = {"id":1, "name":"John"}
>>> json = {"id":1, "name":"John"}
>>> EXPR$0: id
>>> EXPR$0: id
>>> 
>>> I'm doing something wrong here for sure, lol.
>>> 
>>> 
>>> On Tue, May 31, 2022 at 11:35 AM Gavin Ray 
>> wrote:
>>> 
 I'm interested in implementing JSON_TABLE functionality for Calcite
 
 This opens up some neat usecases, like adding HTTP request UDF's
 then using JSON_TABLE to convert the result into a table:
 
 SELECT JSON_TABLE(
 HTTP_GET('http://localhost:8080/api/v1/users/1'))
 
 Adding support for all of the functionality seems difficult,
 but I'm wondering whether this could be done as a "SqlTableFunction"
>> UDF?
 
 I'm thinking it might be possible if the JSON_TABLE udf
 expects Map and does inference based on that?
 
 Something like:
 
 WITH users AS (
SELECT HTTP_GET('http://localhost:8080/api/v1/users')
 ) SELECT
id,
name
 FROM
JSON_TABLE(JSON_PATH(users, "$.0"))
 
 Does anyone have ideas or see issues with this approach?
 Thank you =)
 
 
 
 
>> 

Re: Apply contributor permission

[Michael Mior]

I have added you as a contributor. Welcome to Calcite!

Le ven. 27 mai 2022 à 21:27, 4wei  a écrit :

> Hi all,
>
> I want to contribute to calcite, could anyone please give me the
> contributor permission?
>
> My apache Jira account info:
> Account name: 4wei
> Fullname: Mou Wu
> Email: wumou.4...@outlook.com

Re: Implementing JSON_TABLE, even hackily as a UDF?

[Gavin Ray]

I don't think it's SQL Standard, but essentially it takes a text value
containing JSON data (one record or an array) and converts it into a
relational table:

https://docs.oracle.com/database/121/SQLRF/functions092.htm

I've managed to get most of this working, now I seem to be failing to put
the syntax together correctly for the query
I currently have:
- A UDF ("HTTP_GET") which can make HTTP calls and return the data as a
String
- A table type for Collection> ("MapCollectionTable")
- A TableFunctionImpl ("JsonTableFunction"), which takes a JSON string,
reads it with Jackson, and converts it into a "MapCollectionTable"

The following work:

SELECT HTTP_GET('https://jsonplaceholder.typicode.com/posts')

SELECT * FROM TABLE(
  JSON_TABLE('[{ "id": "1", "name": "foo" }, { "id": "2", "name": "bar"
}]'))

But trying to combine them fails =/

SELECT * FROM TABLE(
  JSON_TABLE(HTTP_GET('https://jsonplaceholder.typicode.com/posts')))

The TableFunctionImpl is receiving "null" for the JSON value
I guess it's maybe expecting static data?

Calcite JSON_TABLE wip 2 (github.com)


On Tue, May 31, 2022 at 2:44 PM Julian Hyde  wrote:

> What is JSON_TABLE? Is it in the SQL standard? Can you give a simple
> example?
>
> On Tue, May 31, 2022 at 10:03 AM Gavin Ray  wrote:
> >
> > I dug up some examples from Github of using the "TableFunctionImpl"
> class:
> >
> > mat-calcite-plugin/TableFunctions.java at
> > a57a5ba80768066714c22bd9e8a9b529d4cb9a6b · vlsi/mat-calcite-plugin
> > (github.com)
> > <
> https://github.com/vlsi/mat-calcite-plugin/blob/a57a5ba80768066714c22bd9e8a9b529d4cb9a6b/MatCalcitePlugin/src/com/github/vlsi/mat/calcite/functions/TableFunctions.java
> >
> > Calcite_sql_driver/MazeTable.java at
> > 6819088123e67631367ca927b112b58e4eb90829 · dream001/Calcite_sql_driver
> > (github.com)
> > <
> https://github.com/dream001/Calcite_sql_driver/blob/6819088123e67631367ca927b112b58e4eb90829/src/test/java/com/yonyou/calcite/MazeTable.java
> >
> >
> > Currently trying to get a TableFunction that just returns a Map > Object> as a relation to work
> > Have the below -- calling this shows that the function IS invoked, but
> the
> > data it gives back is garbage:
> >
> > Calcite JSON_TABLE WIP (github.com)
> > 
> >
> > This is the output of main()
> >
> > json = {"id":1, "name":"John"}
> > json = {"id":1, "name":"John"}
> > json = {"id":1, "name":"John"}
> > EXPR$0: id
> > EXPR$0: id
> >
> > I'm doing something wrong here for sure, lol.
> >
> >
> > On Tue, May 31, 2022 at 11:35 AM Gavin Ray 
> wrote:
> >
> > > I'm interested in implementing JSON_TABLE functionality for Calcite
> > >
> > > This opens up some neat usecases, like adding HTTP request UDF's
> > > then using JSON_TABLE to convert the result into a table:
> > >
> > > SELECT JSON_TABLE(
> > > HTTP_GET('http://localhost:8080/api/v1/users/1'))
> > >
> > > Adding support for all of the functionality seems difficult,
> > > but I'm wondering whether this could be done as a "SqlTableFunction"
> UDF?
> > >
> > > I'm thinking it might be possible if the JSON_TABLE udf
> > > expects Map and does inference based on that?
> > >
> > > Something like:
> > >
> > > WITH users AS (
> > > SELECT HTTP_GET('http://localhost:8080/api/v1/users')
> > > ) SELECT
> > > id,
> > > name
> > > FROM
> > > JSON_TABLE(JSON_PATH(users, "$.0"))
> > >
> > > Does anyone have ideas or see issues with this approach?
> > > Thank you =)
> > >
> > >
> > >
> > >
>

Re: Implementing JSON_TABLE, even hackily as a UDF?

[Julian Hyde]

What is JSON_TABLE? Is it in the SQL standard? Can you give a simple example?

On Tue, May 31, 2022 at 10:03 AM Gavin Ray  wrote:
>
> I dug up some examples from Github of using the "TableFunctionImpl" class:
>
> mat-calcite-plugin/TableFunctions.java at
> a57a5ba80768066714c22bd9e8a9b529d4cb9a6b · vlsi/mat-calcite-plugin
> (github.com)
> 
> Calcite_sql_driver/MazeTable.java at
> 6819088123e67631367ca927b112b58e4eb90829 · dream001/Calcite_sql_driver
> (github.com)
> 
>
> Currently trying to get a TableFunction that just returns a Map Object> as a relation to work
> Have the below -- calling this shows that the function IS invoked, but the
> data it gives back is garbage:
>
> Calcite JSON_TABLE WIP (github.com)
> 
>
> This is the output of main()
>
> json = {"id":1, "name":"John"}
> json = {"id":1, "name":"John"}
> json = {"id":1, "name":"John"}
> EXPR$0: id
> EXPR$0: id
>
> I'm doing something wrong here for sure, lol.
>
>
> On Tue, May 31, 2022 at 11:35 AM Gavin Ray  wrote:
>
> > I'm interested in implementing JSON_TABLE functionality for Calcite
> >
> > This opens up some neat usecases, like adding HTTP request UDF's
> > then using JSON_TABLE to convert the result into a table:
> >
> > SELECT JSON_TABLE(
> > HTTP_GET('http://localhost:8080/api/v1/users/1'))
> >
> > Adding support for all of the functionality seems difficult,
> > but I'm wondering whether this could be done as a "SqlTableFunction" UDF?
> >
> > I'm thinking it might be possible if the JSON_TABLE udf
> > expects Map and does inference based on that?
> >
> > Something like:
> >
> > WITH users AS (
> > SELECT HTTP_GET('http://localhost:8080/api/v1/users')
> > ) SELECT
> > id,
> > name
> > FROM
> > JSON_TABLE(JSON_PATH(users, "$.0"))
> >
> > Does anyone have ideas or see issues with this approach?
> > Thank you =)
> >
> >
> >
> >

Re: Implementing JSON_TABLE, even hackily as a UDF?

[Gavin Ray]

I dug up some examples from Github of using the "TableFunctionImpl" class:

mat-calcite-plugin/TableFunctions.java at
a57a5ba80768066714c22bd9e8a9b529d4cb9a6b · vlsi/mat-calcite-plugin
(github.com)

Calcite_sql_driver/MazeTable.java at
6819088123e67631367ca927b112b58e4eb90829 · dream001/Calcite_sql_driver
(github.com)


Currently trying to get a TableFunction that just returns a Map as a relation to work
Have the below -- calling this shows that the function IS invoked, but the
data it gives back is garbage:

Calcite JSON_TABLE WIP (github.com)


This is the output of main()

json = {"id":1, "name":"John"}
json = {"id":1, "name":"John"}
json = {"id":1, "name":"John"}
EXPR$0: id
EXPR$0: id

I'm doing something wrong here for sure, lol.


On Tue, May 31, 2022 at 11:35 AM Gavin Ray  wrote:

> I'm interested in implementing JSON_TABLE functionality for Calcite
>
> This opens up some neat usecases, like adding HTTP request UDF's
> then using JSON_TABLE to convert the result into a table:
>
> SELECT JSON_TABLE(
> HTTP_GET('http://localhost:8080/api/v1/users/1'))
>
> Adding support for all of the functionality seems difficult,
> but I'm wondering whether this could be done as a "SqlTableFunction" UDF?
>
> I'm thinking it might be possible if the JSON_TABLE udf
> expects Map and does inference based on that?
>
> Something like:
>
> WITH users AS (
> SELECT HTTP_GET('http://localhost:8080/api/v1/users')
> ) SELECT
> id,
> name
> FROM
> JSON_TABLE(JSON_PATH(users, "$.0"))
>
> Does anyone have ideas or see issues with this approach?
> Thank you =)
>
>
>
>

Implementing JSON_TABLE, even hackily as a UDF?

[Gavin Ray]

I'm interested in implementing JSON_TABLE functionality for Calcite

This opens up some neat usecases, like adding HTTP request UDF's
then using JSON_TABLE to convert the result into a table:

SELECT JSON_TABLE(
HTTP_GET('http://localhost:8080/api/v1/users/1'))

Adding support for all of the functionality seems difficult,
but I'm wondering whether this could be done as a "SqlTableFunction" UDF?

I'm thinking it might be possible if the JSON_TABLE udf
expects Map and does inference based on that?

Something like:

WITH users AS (
SELECT HTTP_GET('http://localhost:8080/api/v1/users')
) SELECT
id,
name
FROM
JSON_TABLE(JSON_PATH(users, "$.0"))

Does anyone have ideas or see issues with this approach?
Thank you =)

Re: [DISCUSS] not equal operator vs less than combining greater than

[Benchao Li]

Hi Yanjing,

Your test case passed on my branch. Can you try it with my improvement
in https://github.com/apache/calcite/pull/2821?

Yanjing Wang  于2022年5月31日周二 16:06写道：

> Hi, Benchao,
>
> The pr is good, and I noticed that the two sarg types is not same.
>
> Add the following test in MaterializedViewSubstitutionVisitorTest, you will
> see the substitution fails and sarg types is not same.
>
> @Test void testFilter2() {
> sql("select * from \"EMP\" where (\"ENAME\" > '' or \"ENAME\" < '') "
> + "and (\"ENAME\" > '3' or \"ENAME\" < '3')",
> "select * from \"EMP\" where \"ENAME\" <> '' and \"ENAME\" <> '3'")
> .withDefaultSchemaSpec(CalciteAssert.SchemaSpec.SCOTT)
> .ok();
>   }
>
> Benchao Li  于2022年5月30日周一 14:47写道：
>
> > Hi Yanjing,
> >
> > The type derivation is different in this case indeed.
> > However, after my fix[1], they both can be optimized to Sarg,
> > and they have the same plan structure.
> > (I've added tests to show this, welcome review)
> >
> > In your case, if you have some different handling logic in later steps,
> > maybe you can share with us.
> >
> > [1] https://github.com/apache/calcite/pull/2821
> >
> > Yanjing Wang  于2022年5月30日周一 12:14写道：
> >
> > > Thanks Julian, Benchao,
> > >
> > > This is not only a problem about expression simplification, also type
> > > consistency in conversion[1].
> > > As Xiong said in CALCITE-4993
> > >  , "EQUALS and
> > > NOT-EQUALS comparison. Because they use the same  LEAST_RESTRICTIVE
> > > strategy to validate the parameter. Other comparators use the COMPARE
> > > strategy."
> > >
> > > This will cause NOT-EQUALS comparison will use different strategy from
> > > other compare operator like LESS THAN to generate literal operand's
> type.
> > >
> > > In my case, if "name" column has a type varchar(256),  "name" <> '' and
> > > "name" <> '3' will cause '' and '3' have type varchar(256), but
> ("name"
> > >
> > > '' or "name" < '') and ("name" > '3' or "name" < '3') will cause '' and
> > '3'
> > > has type char(0) and char(1) respectively.
> > >
> > > [1]
> > >
> > >
> >
> https://github.com/apache/calcite/blob/f278efb0411ba29c7bd167f5d02e566bf542acb3/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java#L993
> > >
> > > Benchao Li  于2022年5月29日周日 15:35写道：
> > >
> > > > I've filed an issue[1] to track this.
> > > >
> > > > [1] https://issues.apache.org/jira/browse/CALCITE-5169
> > > >
> > > > Benchao Li  于2022年5月29日周日 11:19写道：
> > > >
> > > > > Hi all,
> > > > >
> > > > > I've confirmed it.
> > > > > The reason why different plans for queries:
> > > > > query 1:
> > > > > select * from "emps" where "name" <> '' and "name" <> '3'
> > > > > query 2:
> > > > > select * from "emps" where ("name" > '' or "name" < '') and
> ("name" >
> > > '3'
> > > > > or "name" < '3')
> > > > > is not from the operator consistency.
> > > > > It's just because the expression: ("name" > '' or "name" < '') and
> > > > ("name"
> > > > > > '3'
> > > > > or "name" < '3') cannot be translated into Sarg for now.
> > > > >
> > > > > I'll file a Jira issue to track and improve this.
> > > > >
> > > > > Benchao Li  于2022年5月28日周六 09:30写道：
> > > > >
> > > > >> FYI, the issue might be this one:
> > > > >> https://issues.apache.org/jira/browse/CALCITE-4993
> > > > >>
> > > > >> I also looked into this in this direction yesterday, however, I
> > didn't
> > > > >> confirm it yet.
> > > > >> That's why I didn't reply to this email before. I will do further
> > > > >> verifications and
> > > > >> post the result here later.
> > > > >>
> > > > >>
> > > > >>
> > > > >> Julian Hyde  于2022年5月27日周五 23:52写道：
> > > > >>
> > > > >>> I think there’s a JIRA case for this. The implicit casts prevent
> > SARG
> > > > >>> simplification from kicking in. In SARG representation the
> > > expressions
> > > > >>> would be the same. Which is why we love SARGs.
> > > > >>>
> > > > >>> Julian
> > > > >>>
> > > > >>> > On May 26, 2022, at 17:49, Yanjing Wang <
> > zhuangzixiao...@gmail.com
> > > >
> > > > >>> wrote:
> > > > >>> >
> > > > >>> > Hi community,
> > > > >>> >
> > > > >>> > I have this sql: select * from "emps" where "name" <> '' and
> > "name"
> > > > <>
> > > > >>> '3'
> > > > >>> >
> > > > >>> > I thought it would generate the same plan with
> > > > >>> > select * from "emps" where ("name" > '' or "name" < '') and
> > > ("name" >
> > > > >>> '3'
> > > > >>> > or "name" < '3')
> > > > >>> >
> > > > >>> > but not, the not equal operator consistency is different with
> > less
> > > > >>> than and
> > > > >>> > greater than operator,
> > > > >>> >
> > > > >>> > which will cause the literal '' and '3' have different data
> type
> > in
> > > > >>> plans
> > > > >>> > of the above sqls.
> > > > >>> >
> > > > >>> > That behavior maybe cause some queries will not hit the
> > > > >>> materialization.
> > > > >>> >
> > > > >>> > should we canonize  ("name" > '' or "name" < '') and ("name" >
> > '3'

Re: [DISCUSS] not equal operator vs less than combining greater than

[Yanjing Wang]

Hi, Benchao,

The pr is good, and I noticed that the two sarg types is not same.

Add the following test in MaterializedViewSubstitutionVisitorTest, you will
see the substitution fails and sarg types is not same.

@Test void testFilter2() {
sql("select * from \"EMP\" where (\"ENAME\" > '' or \"ENAME\" < '') "
+ "and (\"ENAME\" > '3' or \"ENAME\" < '3')",
"select * from \"EMP\" where \"ENAME\" <> '' and \"ENAME\" <> '3'")
.withDefaultSchemaSpec(CalciteAssert.SchemaSpec.SCOTT)
.ok();
  }

Benchao Li  于2022年5月30日周一 14:47写道：

> Hi Yanjing,
>
> The type derivation is different in this case indeed.
> However, after my fix[1], they both can be optimized to Sarg,
> and they have the same plan structure.
> (I've added tests to show this, welcome review)
>
> In your case, if you have some different handling logic in later steps,
> maybe you can share with us.
>
> [1] https://github.com/apache/calcite/pull/2821
>
> Yanjing Wang  于2022年5月30日周一 12:14写道：
>
> > Thanks Julian, Benchao,
> >
> > This is not only a problem about expression simplification, also type
> > consistency in conversion[1].
> > As Xiong said in CALCITE-4993
> >  , "EQUALS and
> > NOT-EQUALS comparison. Because they use the same  LEAST_RESTRICTIVE
> > strategy to validate the parameter. Other comparators use the COMPARE
> > strategy."
> >
> > This will cause NOT-EQUALS comparison will use different strategy from
> > other compare operator like LESS THAN to generate literal operand's type.
> >
> > In my case, if "name" column has a type varchar(256),  "name" <> '' and
> > "name" <> '3' will cause '' and '3' have type varchar(256), but  ("name"
> >
> > '' or "name" < '') and ("name" > '3' or "name" < '3') will cause '' and
> '3'
> > has type char(0) and char(1) respectively.
> >
> > [1]
> >
> >
> https://github.com/apache/calcite/blob/f278efb0411ba29c7bd167f5d02e566bf542acb3/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java#L993
> >
> > Benchao Li  于2022年5月29日周日 15:35写道：
> >
> > > I've filed an issue[1] to track this.
> > >
> > > [1] https://issues.apache.org/jira/browse/CALCITE-5169
> > >
> > > Benchao Li  于2022年5月29日周日 11:19写道：
> > >
> > > > Hi all,
> > > >
> > > > I've confirmed it.
> > > > The reason why different plans for queries:
> > > > query 1:
> > > > select * from "emps" where "name" <> '' and "name" <> '3'
> > > > query 2:
> > > > select * from "emps" where ("name" > '' or "name" < '') and ("name" >
> > '3'
> > > > or "name" < '3')
> > > > is not from the operator consistency.
> > > > It's just because the expression: ("name" > '' or "name" < '') and
> > > ("name"
> > > > > '3'
> > > > or "name" < '3') cannot be translated into Sarg for now.
> > > >
> > > > I'll file a Jira issue to track and improve this.
> > > >
> > > > Benchao Li  于2022年5月28日周六 09:30写道：
> > > >
> > > >> FYI, the issue might be this one:
> > > >> https://issues.apache.org/jira/browse/CALCITE-4993
> > > >>
> > > >> I also looked into this in this direction yesterday, however, I
> didn't
> > > >> confirm it yet.
> > > >> That's why I didn't reply to this email before. I will do further
> > > >> verifications and
> > > >> post the result here later.
> > > >>
> > > >>
> > > >>
> > > >> Julian Hyde  于2022年5月27日周五 23:52写道：
> > > >>
> > > >>> I think there’s a JIRA case for this. The implicit casts prevent
> SARG
> > > >>> simplification from kicking in. In SARG representation the
> > expressions
> > > >>> would be the same. Which is why we love SARGs.
> > > >>>
> > > >>> Julian
> > > >>>
> > > >>> > On May 26, 2022, at 17:49, Yanjing Wang <
> zhuangzixiao...@gmail.com
> > >
> > > >>> wrote:
> > > >>> >
> > > >>> > Hi community,
> > > >>> >
> > > >>> > I have this sql: select * from "emps" where "name" <> '' and
> "name"
> > > <>
> > > >>> '3'
> > > >>> >
> > > >>> > I thought it would generate the same plan with
> > > >>> > select * from "emps" where ("name" > '' or "name" < '') and
> > ("name" >
> > > >>> '3'
> > > >>> > or "name" < '3')
> > > >>> >
> > > >>> > but not, the not equal operator consistency is different with
> less
> > > >>> than and
> > > >>> > greater than operator,
> > > >>> >
> > > >>> > which will cause the literal '' and '3' have different data type
> in
> > > >>> plans
> > > >>> > of the above sqls.
> > > >>> >
> > > >>> > That behavior maybe cause some queries will not hit the
> > > >>> materialization.
> > > >>> >
> > > >>> > should we canonize  ("name" > '' or "name" < '') and ("name" >
> '3'
> > or
> > > >>> > "name" < '3') to not equal or vice versa as RelToSql
> > > >>> > <
> > > >>>
> > >
> >
> https://github.com/apache/calcite/blob/9bdfd9a178f493b235d8785afd94fd0c998e8cce/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L870
> > > >>> >
> > > >>> > behaves?
> > > >>>
> > > >>
> > > >>
> > > >> --
> > > >>
> > > >> Best,
> > > >> Benchao Li
> > > >>
> > > >
> > > >
> > > > --
> > > >
> > > > Best,
> > > > Benchao