Re: Appropriate query syntax for table-value function windowing (e.g. CALCITE-3272)

[Rui Wang]

And I think both TABLE and DESCRIPTOR are not supported as (table)function
parameters?

TABLE parameter: TABLE(table_name) or TABLE table_name to specify an
input table.
DESCRIPTOR parameter: DESCRIPTOR(column_name, ...) to specify columns from
the input table.


-Rui


On Thu, Aug 29, 2019 at 3:24 PM Rui Wang  wrote:

> Thanks Julian for your explanation and the pointer.
>
> I will go to the direction of TABLE(Function) syntax then.
>
>
> -Rui
>
> On Thu, Aug 29, 2019 at 3:03 PM Julian Hyde  wrote:
>
>> Standard SQL doesn’t allow functions in the FROM clause. I think it’s
>> because tables and functions are in different namespaces (and therefore
>> there could be a table and a function with the same name). So you need to
>> use the TABLE keyword to indicate that you are using a function as a table.
>>
>> This has been discussed before; see
>> https://issues.apache.org/jira/browse/CALCITE-1472?focusedCommentId=15662182=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-15662182
>> <
>> https://issues.apache.org/jira/browse/CALCITE-1472?focusedCommentId=15662182=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-15662182
>> >.
>>
>> > On Aug 29, 2019, at 2:37 PM, Rui Wang 
>> wrote:
>> >
>> > Hi Community,
>> >
>> > I have been searching and trying Calcite's query syntax to match
>> > CALCITE-3272  (TUBME
>> as a
>> > table function call).
>> >
>> > Currently, the closest syntax in Calcite I found is:
>> >
>> > FROM TABLE(TUMBLE(params...))
>> >
>> > The better syntax should be:
>> >
>> > FROM TUMBLE(params..), which basically is the form of FROM
>> > table_function_name(params...).
>> >
>> >
>> >
>> > Is the second option already supported by Calcite? if not, would it be
>> > better to go to support it?
>> >
>> >
>> > -Rui
>>
>>

Re: Preserving CAST of STRING operands in comparison operator

[Danny Chan]

This is kind of in the scope of implicit type coercion which is supported in 
CALCITE-2302.

For sql dialect that does not support implicit type coercion, strip explicit 
cast is a mistake.

I think this can be seen as a bug and we should log an issue to fix. But just 
like you said, if we support it for every sql dialect, it would be a huge work, 
we should think of a flexible way.

Best,
Danny Chan
在 2019年8月28日 +0800 PM4:56，Soma Mondal ，写道：
> Hi Julian,
>
> After some further analysis, it seems that the mandatory cast is only
> required in SOME cases for BigQuery.
> Please see attached my analysis for Hive, MySQL, Netezza and Oracle.
>
> https://drive.google.com/open?id=1GJ_VuDY7GQS-aPbWf4EKj73dYCqaRaEqPTjmXLkPW_g
>
> I'm thinking of having the dialect intercept this and check the specific
> conditions (specified in the sheet above) and decide whether or not to
> remove the cast.
>
> Regards,
> Soma
>
>
> On Mon, 26 Aug 2019 at 22:29, Julian Hyde  wrote:
>
> > I might be mistaken, but disabling stripCastFromString() for some dialects
> > and not others doesn’t sound like it’s solving the root cause of the
> > problem.
> >
> > Julian
> >
> >
> > > On Aug 26, 2019, at 7:49 AM, Soma Mondal 
> > wrote:
> > >
> > > Hi Julian,
> > >
> > > 2 tests failed when I made the stripCastFromString() no-op.
> > >
> > > 1.
> > >
> > > testDb2DialectSelectQueryWithGroup
> > > 2.
> > >
> > > testSelectQueryWithGroup
> > >
> > > Above tests pretty much do the same thing and basically strip the cast
> > from
> > > String literal something like this:
> > >
> > > Expected:
> > >
> > > SELECT COUNT(*), SUM(employee_id)
> > >
> > > FROM foodmart.reserve_employee
> > >
> > > WHERE hire_date > '2015-01-01' AND (position_title = 'SDE' OR
> > > position_title = 'SDM')
> > >
> > > GROUP BY store_id, position_title
> > >
> > > But with no-op we get this:
> > >
> > > SELECT COUNT(*), SUM(employee_id)
> > >
> > > FROM foodmart.reserve_employee
> > >
> > > WHERE hire_date > CAST('2015-01-01' AS TIMESTAMP(0)) AND (position_title
> > =
> > > 'SDE' OR position_title = 'SDM')
> > >
> > > GROUP BY store_id, position_title
> > >
> > > Can I go ahead and make changes where calls to stripCastFromString() will
> > > be skipped for specific dialects?
> > >
> > > Regards,
> > >
> > > Soma
> > >
> > >
> > > On Fri, 23 Aug 2019 at 16:02, Soma Mondal 
> > > wrote:
> > >
> > > > Hello,
> > > >
> > > > We have a REL which has this information
> > > > select * from employee where employee_id = cast('12' as float);
> > > >
> > > > but Calcite removes the CAST from the STRING literal('12' in our case).
> > > > select * from employee where employee_id = '12';
> > > >
> > > > There are dialects which needs explicit casting in the above case and we
> > > > need to maintain the CAST in our dialect.
> > > > Calcite removes the cast in SqlImplementor's stripCastFromString()
> > > > method. I would like to understand why Calcite removes the CAST and
> > shall
> > > > we go ahead and make the changes in Calcite to maintain the CAST.
> > > >
> > > > Thanks & Regards,
> > > > Soma Mondal
> > > >
> >
> >

Re: Appropriate query syntax for table-value function windowing (e.g. CALCITE-3272)

[Rui Wang]

Thanks Julian for your explanation and the pointer.

I will go to the direction of TABLE(Function) syntax then.


-Rui

On Thu, Aug 29, 2019 at 3:03 PM Julian Hyde  wrote:

> Standard SQL doesn’t allow functions in the FROM clause. I think it’s
> because tables and functions are in different namespaces (and therefore
> there could be a table and a function with the same name). So you need to
> use the TABLE keyword to indicate that you are using a function as a table.
>
> This has been discussed before; see
> https://issues.apache.org/jira/browse/CALCITE-1472?focusedCommentId=15662182=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-15662182
> <
> https://issues.apache.org/jira/browse/CALCITE-1472?focusedCommentId=15662182=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-15662182
> >.
>
> > On Aug 29, 2019, at 2:37 PM, Rui Wang  wrote:
> >
> > Hi Community,
> >
> > I have been searching and trying Calcite's query syntax to match
> > CALCITE-3272  (TUBME
> as a
> > table function call).
> >
> > Currently, the closest syntax in Calcite I found is:
> >
> > FROM TABLE(TUMBLE(params...))
> >
> > The better syntax should be:
> >
> > FROM TUMBLE(params..), which basically is the form of FROM
> > table_function_name(params...).
> >
> >
> >
> > Is the second option already supported by Calcite? if not, would it be
> > better to go to support it?
> >
> >
> > -Rui
>
>

Re: Appropriate query syntax for table-value function windowing (e.g. CALCITE-3272)

[Julian Hyde]

Standard SQL doesn’t allow functions in the FROM clause. I think it’s because 
tables and functions are in different namespaces (and therefore there could be 
a table and a function with the same name). So you need to use the TABLE 
keyword to indicate that you are using a function as a table.

This has been discussed before; see 
https://issues.apache.org/jira/browse/CALCITE-1472?focusedCommentId=15662182=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-15662182
 
.

> On Aug 29, 2019, at 2:37 PM, Rui Wang  wrote:
> 
> Hi Community,
> 
> I have been searching and trying Calcite's query syntax to match
> CALCITE-3272  (TUBME as a
> table function call).
> 
> Currently, the closest syntax in Calcite I found is:
> 
> FROM TABLE(TUMBLE(params...))
> 
> The better syntax should be:
> 
> FROM TUMBLE(params..), which basically is the form of FROM
> table_function_name(params...).
> 
> 
> 
> Is the second option already supported by Calcite? if not, would it be
> better to go to support it?
> 
> 
> -Rui

Appropriate query syntax for table-value function windowing (e.g. CALCITE-3272)

[Rui Wang]

Hi Community,

I have been searching and trying Calcite's query syntax to match
CALCITE-3272  (TUBME as a
table function call).

Currently, the closest syntax in Calcite I found is:

FROM TABLE(TUMBLE(params...))

The better syntax should be:

FROM TUMBLE(params..), which basically is the form of FROM
table_function_name(params...).



Is the second option already supported by Calcite? if not, would it be
better to go to support it?


-Rui

[jira] [Created] (CALCITE-3308) RelBuilder.literal with a string argument should create a character literal with appropriate character set

[Julian Hyde (Jira)]

Julian Hyde created CALCITE-3308:


 Summary: RelBuilder.literal with a string argument should create a 
character literal with appropriate character set
 Key: CALCITE-3308
 URL: https://issues.apache.org/jira/browse/CALCITE-3308
 Project: Calcite
  Issue Type: Bug
Reporter: Julian Hyde


Suppose you call {{RelBuilder.literal}} with a with a string argument that 
contains non-ASCII characters:
{code:java}
RexNode e = RelBuilder.literal("☘️ my lucky charms!");{code}
 

The call should return a character literal with appropriate character set that 
can handle the contents of the string. (I'm not sure what character set should 
be.)



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

[jira] [Created] (CALCITE-3307) PigRelExTest fails on Windows

[Julian Hyde (Jira)]

Julian Hyde created CALCITE-3307:


 Summary: PigRelExTest fails on Windows
 Key: CALCITE-3307
 URL: https://issues.apache.org/jira/browse/CALCITE-3307
 Project: Calcite
  Issue Type: Bug
Reporter: Julian Hyde


Running {{PigRelExTest}} on Windows, after CALCITE-3122 was merged, gives the 
following error:
{noformat}
2019-08-29 15:33:23,229 [main] ERROR - Failed to locate the winutils binary in 
the hadoop binary path
java.io.IOException: Could not locate executable null\bin\winutils.exe in the 
Hadoop binaries.
at org.apache.hadoop.util.Shell.getQualifiedBinPath(Shell.java:382)
at org.apache.hadoop.util.Shell.getWinUtilsPath(Shell.java:397)
at org.apache.hadoop.util.Shell.(Shell.java:390)
at org.apache.hadoop.util.StringUtils.(StringUtils.java:80)
at 
org.apache.hadoop.security.SecurityUtil.getAuthenticationMethod(SecurityUtil.java:611)
at 
org.apache.hadoop.security.UserGroupInformation.initialize(UserGroupInformation.java:273)
at 
org.apache.hadoop.security.UserGroupInformation.ensureInitialized(UserGroupInformation.java:261)
at 
org.apache.hadoop.security.UserGroupInformation.isAuthenticationMethodEnabled(UserGroupInformation.java:338)
at 
org.apache.hadoop.security.UserGroupInformation.isSecurityEnabled(UserGroupInformation.java:332)
at 
org.apache.pig.backend.hadoop.HKerberos.tryKerberosKeytabLogin(HKerberos.java:70)
at 
org.apache.pig.backend.hadoop.executionengine.HExecutionEngine.init(HExecutionEngine.java:220)
at 
org.apache.pig.backend.hadoop.executionengine.HExecutionEngine.init(HExecutionEngine.java:112)
at org.apache.pig.impl.PigContext.connect(PigContext.java:305)
at org.apache.pig.PigServer.(PigServer.java:231)
at org.apache.pig.PigServer.(PigServer.java:219)
at org.apache.pig.PigServer.(PigServer.java:211)
at org.apache.pig.PigServer.(PigServer.java:207)
at org.apache.calcite.piglet.PigConverter.(PigConverter.java:107)
at org.apache.calcite.piglet.PigConverter.create(PigConverter.java:112)
at 
org.apache.calcite.test.PigRelTestBase.testSetup(PigRelTestBase.java:34)
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.RunBefores.evaluate(RunBefores.java:24)
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)
{noformat}

This issue has arisen many times; see HADOOP-10775 and SPARK-2356.



--
This message was sent by Atlassian Jira
(v8.3.2#803003)

EQUITAS

[Michael Mior]

I was browsing the proceedings of this year's VLDB conference and came
across the paper below doing something similar to Cosette
(http://cosette.cs.washington.edu/), validating two SQL queries are
equivalent. Pairs of queries were pulled from Calcite pre and
post-optimization. I've only skimmed the paper, but I don't believe
they mention any incorrect rewrites, which is great news :)

http://www.vldb.org/pvldb/vol12/p1276-zhou.pdf

--
Michael Mior
mm...@apache.org

Re: Using Spark 2.4.0 as Execution Engine with Apache Calcite

[Shubham Kumar]

Hey Julian,

Thanks, I have subscribed to the list now.

On Wed, Aug 28, 2019 at 10:58 PM Julian Hyde  wrote:

> Your message was received. I guess no one had the time to respond.
>
> Can you please subscribe to the list? I had to moderate your message
> through because you are not a subscriber.
>
> Julian
>
>
> > On Aug 28, 2019, at 9:03 AM, Shubham Kumar 
> wrote:
> >
> > Hey,
> >
> > Does anyone have any insights on this? Also wanted to confirm that it is
> > being sent on the mailing list of Calcite.
> >
> > Thanks
> > Shubham
> >
> > On Mon, Aug 26, 2019 at 6:16 PM Shubham Kumar <
> shubhamkumar1...@gmail.com>
> > wrote:
> >
> >> Hey,
> >>
> >> I am trying to optimize queries by creating materialized views in Hive
> and
> >> then exposing them to Calcite for optimized query rewrite.
> >>
> >> I wanted to know that how mature is the spark adapter of Calcite i.e.
> >> Let's say while making the connection I set parameter spark=true
> >> <
> https://calcite.apache.org/apidocs/org/apache/calcite/config/CalciteConnectionProperty.html#SPARK
> >.
> >> Will the workflow be like this?
> >>
> >> i) Calcite using Hive's MV's for query rewrites.
> >> ii) When query comes ; optimized query rewrite is done by Calcite.
> >> iii) This is converted to spark convention and successfully executed
> using
> >> Spark as the engine.
> >>
> >> I had a doubt since in many places it's mentioned that spark adapter is
> >> not production ready or powerful enough and has been developed for
> >> compatibility till spark 1.x only.
> >>
> >> Also, can we extract out the query rewrite of Calcite using some API
> >> before it is actually executed?
> >> --
> >> Thanks & Regards
> >> Shubham Kumar
> >>
> >>
> >
> > --
> > Thanks & Regards
> >
> > Shubham Kumar
>
>

-- 
Thanks & Regards

Shubham Kumar

Calcite-Master - Build # 1321 - Still Failing

[Apache Jenkins Server]

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

Status: Still Failing

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

Re: row value constructors for pagination

[Danny Chan]

If it is a deterministic promotion, you can write a RelOptRule to support this.

Best,
Danny Chan
在 2019年8月29日 +0800 AM9:13，Thomas D'Silva ，写道：
> I am looking into the possibility of using row value constructors for
> paging through data. The query would be of the form
>
> SELECT * FROM t WHERE (pk1, pk2) > (2,'bar');
>
> which should logically be equivalent to
>
> SELECT * FROM t WHERE pk1 > 2 OR (pk1=2 AND pk2>'bar')
>
> I found a previous thread where this was discussed [1]. Does anyone know
> the scope of work it would take to support this feature in Calcite?
>
> [1]
> https://lists.apache.org/thread.html/8d3154f6a636972777493d176b9bbaa69ab957145470135a0a13a1b3@1419906719@%3Cdev.calcite.apache.org%3E

[jira] [Created] (CALCITE-3306) Add REGEXP_SPLIT_TO_ARRAY function

[Wang Yanlin (Jira)]

Wang Yanlin created CALCITE-3306:


 Summary: Add REGEXP_SPLIT_TO_ARRAY function
 Key: CALCITE-3306
 URL: https://issues.apache.org/jira/browse/CALCITE-3306
 Project: Calcite
  Issue Type: New Feature
Reporter: Wang Yanlin


In work, I found there is no coresponding *split* function in calcite like the 
* regexp_split_to_array* in 
[PostgreSQL|https://www.postgresql.org/docs/9.1/functions-matching.html].

Although we can solve this by creating a udf based on calcite, but a built in 
function might be better.



--
This message was sent by Atlassian Jira
(v8.3.2#803003)