[jira] [Created] (CALCITE-5162) RelMdUniqueKeys can return more precise unique keys for Aggregate

[Chunwei Lei (Jira)]

Chunwei Lei created CALCITE-5162:


 Summary: RelMdUniqueKeys can return more precise unique keys for 
Aggregate
 Key: CALCITE-5162
 URL: https://issues.apache.org/jira/browse/CALCITE-5162
 Project: Calcite
  Issue Type: Improvement
Reporter: Chunwei Lei
Assignee: Chunwei Lei


Currently, RelMdUniqueKeys always returns group by keys as the unique key. 
However, it can return more precise unique keys by looking through group by 
keys. For instance:
{code:java}
select deptno, deptname, count(*) from dept group by deptno, deptname;{code}
RelMdUniqueKeys can return {{deptno}} as the unique key instead of (deptno, 
deptname) if {{deptno}} is unique.



--
This message was sent by Atlassian Jira
(v8.20.7#820007)

Re: Vararg/Variadic UDFs or workarounds?

[Ian Bertolacci]

Hello,
So I’ve been trying to make this work for a while now without a lot of success.
I’ve been able to define our operator in an operator table and have success 
validating queries with it.

However we extend Avatica’s JDBCMeta to do nearly all the heavy lifting.
The problem being that I have absolutely no idea how to get our operator table 
down into the validator that CalcitePrepareImpl creates.

What would seem to be the “standard” way of adding a separate library of 
operators would be to add a new SqlLibrary operator table and set our 
CalciteConfigurationProperties with it, but that requires modifications to the 
Calcite code base, and we’re doing just about all we can to not maintain our 
own fork of Calcite.

Any suggestions?
-Ian J. Bertolacci

From: Ian Bertolacci 
Date: Friday, April 8, 2022 at 10:04 AM
To: dev@calcite.apache.org 
Subject: [External Sender] Re: Vararg/Variadic UDFs or workarounds?
This is excellent thanks you so much!.
-Ian

From: Stamatis Zampetakis 
Date: Wednesday, April 6, 2022 at 2:16 PM
To: dev@calcite.apache.org 
Subject: [External Sender] Re: Vararg/Variadic UDFs or workarounds?
Hi Ian,

>From what I recall the work under CALCITE-2772 is an attempt to allow
introducing vararg UDFs in a more user friendly way.

Supporting vararg UDFs via Schema and Function interfaces is one way to go
although without CALCITE-2772 probably this is not possible.

Another way (and more powerful) would be to use the SqlOperator interface
(either directly or extend some existing implementation) and create your
own customised operator. Then you can plug-in your own operator table with
the custom UDFs in the validator [1].
Note that currently Calcite has some vararg functions/operators and the
first that comes to mind is CONCAT [2]. You may check the changes
introduced by CALCITE-4394 [3] to learn more about this and get inspiration
if you end-up going down the path of implementing your own SqlOperator.

Lastly, you could possibly avoid varargs UDF using a small trick that was
sufficient for me in some use-cases. Make the UDF accept a parameter of
type ARRAY and call it by wrapping the function arguments in an ARRAY
constructor. For instance, the queries would look like the following:

SELECT MY_CUSTOM_UDF(ARRAY['A','B','C'])
SELECT MY_CUSTOM_UDF(ARRAY['A','C'])

Best,
Stamatis

[1]
https://github.com/zabetak/calcite-tutorial/blob/31cce59c747e0b763a934109db6a6e7055f175ae/solution/src/main/java/com/github/zabetak/calcite/tutorial/LuceneQueryProcessor.java#L166
[2]
https://github.com/apache/calcite/blob/a81cfb2ad001589929e190939cf4db928ebac386/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java#L491
[3] https://issues.apache.org/jira/browse/CALCITE-4394

On Tue, Apr 5, 2022 at 12:15 AM Ian Bertolacci
 wrote:

> Howdy!
> We’re trying to add a vararg/variadic UDF, but cannot seem to make it work
> out.
>
> In our system, we define our UDFs to the Schema’s function multimap, and
> so have classes with methods which are provided to
> ScalarFunctionImpl.create(theClass, “methodName” ) to create the Function
> object, and from which the parser/validator draws the SQL function
> signature.
> However, there doesn’t seem to be a way do define such a method where
> ScalarFunctionImpl infers the variadic function signature, but rather as
> accepting a single List parameter.
>
> I see that CALCITE-2772 was raised to solve this but the work seems to
> have stalled in review.
>
> Is there any plan to restart this work?
> And in the mean time, is there a good way of defining variadic UDFs?
>
> One solution we’ve come up with is to define a function with all (except
> the first) optional parameters so that it *looks* like a variadic function,
> but this is limited to 254 parameters by the JVM.
> While 254 parameters is quite a lot, we predict that our users may exceed
> this limit.
> Thanks!
> -Ian J. Bertolacci
>
>

Re: Implicit cast from string to array

[Julian Hyde]

As I commented in https://issues.apache.org/jira/browse/CALCITE-5159 
, this feature is more like 
an array literal. Consider that

  DATE ‘2022-05-19’

is a date literal, and if I write

  INSERT INTO t (dateColumn) VALUES (‘2022-05-19’)

or

  SELECT *
  FROM t
  WHERE dateColumn = ‘2022-05-19’

the character literals become date literals. This array literal seems to work 
similarly. The parsing can happen at prepare time, and if the literal is 
invalid prepare should fail.

Julian



> On May 19, 2022, at 1:48 AM, Dmitry Sysolyatin  
> wrote:
> 
> Thanks!
> But I think I shouldn't create my own implementation of TypeCoercion. I can
> just extend the existing TypeCoercionImpl if I will add
> isStringToArrayCastEnabled() (by analogy with isTypeCoercionEnabled) to
> SqlCallBinding in order to only postgres dialect has this feature.
> 
> On Mon, May 16, 2022 at 12:35 PM Stamatis Zampetakis 
> wrote:
> 
>> Hi Dmitry,
>> 
>> There is the TypeCoercion [1] interface that allows you to customize how
>> implicit casts work.
>> I guess you need to create your own implementation, customize the behavior
>> of the binaryComparisonCoercion method (possibly others as well), and pass
>> it to the validator.
>> 
>> Best,
>> Stamatis
>> 
>> [1]
>> 
>> https://github.com/apache/calcite/blob/1bce280a2957326dc5c249cfd079edfd2c54adf4/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercion.java
>> 
>> On Thu, May 12, 2022 at 4:42 PM Dmitry Sysolyatin >> 
>> wrote:
>> 
>>> Hi!
>>> 
>>> I would like to implement implicit cast from string to array as PG does.
>>> For example: "SELECT ARRAY[1,2,3] = '{1,2,3};'" should return true
>> instead
>>> of exception for Postgres dialect
>>> 
>>> Is there an easy way to implement implicit cast?
>>> 
>> 

[jira] [Created] (CALCITE-5161) NPE when inserting a null value into a decimal column

[Gregory Hart (Jira)]

Gregory Hart created CALCITE-5161:
-

 Summary: NPE when inserting a null value into a decimal column
 Key: CALCITE-5161
 URL: https://issues.apache.org/jira/browse/CALCITE-5161
 Project: Calcite
  Issue Type: Bug
  Components: core
Affects Versions: 1.30.0
Reporter: Gregory Hart


A NullPointerException is thrown when inserting a NULL value into a DECIMAL 
column. My guess is that there's a missing check somewhere to return 
RexBuilder#constantNull instead of creating a new RexLiteral with a null value.

*Steps to reproduce:*

insert into "foodmart"."promotion" values (666, 666, 'Test', NULL, NULL, NULL, 
NULL)

*Expected results:*

The row is inserted.

*Actual results:*
{noformat}
Error while executing SQL "insert into "foodmart"."promotion" values (666, 666, 
'Test', NULL, NULL, NULL, NULL)": Unable to implement 
JdbcToEnumerableConverter: rowcount = 1.0, cumulative cost = 
{1.2002 rows, 1.1 cpu, 0.0 io}, id = 13996
  JdbcTableModify(table=[[foodmart, promotion]], operation=[INSERT], 
flattened=[false]): rowcount = 1.0, cumulative cost = {1.1 rows, 1.0 cpu, 0.0 
io}, id = 13994
    JdbcValues(tuples=[[{ 666, 666, 'Test', null, null, null, null }]]): 
rowcount = 1.0, cumulative cost = {1.0 rows, 1.0 cpu, 0.0 io}, id = 
13982java.sql.SQLException: Error while executing SQL "insert into 
"foodmart"."promotion" values (666, 666, 'Test', NULL, NULL, NULL, NULL)": 
Unable to implement JdbcToEnumerableConverter: rowcount = 1.0, cumulative cost 
= {1.2002 rows, 1.1 cpu, 0.0 io}, id = 13996
  JdbcTableModify(table=[[foodmart, promotion]], operation=[INSERT], 
flattened=[false]): rowcount = 1.0, cumulative cost = {1.1 rows, 1.0 cpu, 0.0 
io}, id = 13994
    JdbcValues(tuples=[[{ 666, 666, 'Test', null, null, null, null }]]): 
rowcount = 1.0, cumulative cost = {1.0 rows, 1.0 cpu, 0.0 io}, id = 13982    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.executeLargeUpdate(AvaticaStatement.java:246)
    at 
org.apache.calcite.avatica.AvaticaStatement.executeUpdate(AvaticaStatement.java:240)
    at org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:541)
    at 
org.apache.calcite.test.CalciteAssert$AssertQuery.lambda$updates$0(CalciteAssert.java:1438)
    at 
org.apache.calcite.test.CalciteAssert$AssertQuery.withConnection(CalciteAssert.java:1384)
    at 
org.apache.calcite.test.CalciteAssert$AssertQuery.updates(CalciteAssert.java:1437)
    at 
org.apache.calcite.test.JdbcAdapterTest.testInsertNull(PtyJdbcAdapterTest.java:26)
    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.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:725)
    at 
org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
    at 
org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
    at 
org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
    at 
org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
    at 
org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
    at 
org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
    at 
org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
    at 
org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
    at 
org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
    at 
org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
    at 
org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
    at 
org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
    at 
org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
    at 
org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:214)
    at 
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at 

[jira] [Created] (CALCITE-5160) ANY, SOME functions should support subquery and scalar arrays

[Dmitry Sysolyatin (Jira)]

Dmitry Sysolyatin created CALCITE-5160:
--

 Summary: ANY, SOME functions should support subquery and scalar 
arrays
 Key: CALCITE-5160
 URL: https://issues.apache.org/jira/browse/CALCITE-5160
 Project: Calcite
  Issue Type: Bug
  Components: core
Reporter: Dmitry Sysolyatin


At the moment ANY, SOME functions support subquery:
{code}
SELECT 1 = SOME (SELECT * FROM UNNEST(ARRAY[1,2,3]))
{code}

But if input argument is array, then query fails
{code}
SELECT 1 = SOME (ARRAY[1,2,3])
SELECT 1 = SOME(. FROM )
{code}
[https://www.postgresql.org/docs/current/functions-comparisons.html#id-1.5.8.30.16]

It is useful for checking if a value exists in an array.



--
This message was sent by Atlassian Jira
(v8.20.7#820007)

Final reminder: ApacheCon North America call for presentations closing soon

[Rich Bowen]

[Note: You're receiving this because you are subscribed to one or more
Apache Software Foundation project mailing lists.]

This is your final reminder that the Call for Presetations for
ApacheCon North America 2022 will close at 00:01 GMT on Monday, May
23rd, 2022. Please don't wait! Get your talk proposals in now!

Details here: https://apachecon.com/acna2022/cfp.html

--Rich, for the ApacheCon Planners

[jira] [Created] (CALCITE-5159) PG dialect should support implicit cast from string to array

[Dmitry Sysolyatin (Jira)]

Dmitry Sysolyatin created CALCITE-5159:
--

 Summary: PG dialect should support implicit cast from string to 
array
 Key: CALCITE-5159
 URL: https://issues.apache.org/jira/browse/CALCITE-5159
 Project: Calcite
  Issue Type: Improvement
  Components: core
Reporter: Dmitry Sysolyatin
Assignee: Dmitry Sysolyatin


According to the documentation 
[https://www.postgresql.org/docs/current/arrays.html]

Postgres dialect should support implicit cast from string to array:
{code:java}
SELECT ARRAY[1,2,3] = '{1,2,3}'
{code}



--
This message was sent by Atlassian Jira
(v8.20.7#820007)

Re: Implicit cast from string to array

[Dmitry Sysolyatin]

Thanks!
But I think I shouldn't create my own implementation of TypeCoercion. I can
just extend the existing TypeCoercionImpl if I will add
isStringToArrayCastEnabled() (by analogy with isTypeCoercionEnabled) to
SqlCallBinding in order to only postgres dialect has this feature.

On Mon, May 16, 2022 at 12:35 PM Stamatis Zampetakis 
wrote:

> Hi Dmitry,
>
> There is the TypeCoercion [1] interface that allows you to customize how
> implicit casts work.
> I guess you need to create your own implementation, customize the behavior
> of the binaryComparisonCoercion method (possibly others as well), and pass
> it to the validator.
>
> Best,
> Stamatis
>
> [1]
>
> https://github.com/apache/calcite/blob/1bce280a2957326dc5c249cfd079edfd2c54adf4/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercion.java
>
> On Thu, May 12, 2022 at 4:42 PM Dmitry Sysolyatin  >
> wrote:
>
> > Hi!
> >
> > I would like to implement implicit cast from string to array as PG does.
> > For example: "SELECT ARRAY[1,2,3] = '{1,2,3};'" should return true
> instead
> > of exception for Postgres dialect
> >
> > Is there an easy way to implement implicit cast?
> >
>