You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by DroopyHoo <ol...@gmail.com> on 2015/11/19 09:29:43 UTC
question about implement a scalarFunction whose argument type is date
or timestamp
Hi,
I have met a problem when using calcite 1.4 to implement a
scalarFunction for my udf.
the field type is date and I want to realize the MY_FUNC(date)
*SQL : select MY_FUNC(date) from table;*
if I set my function's argument type to java.sql.Date, calcite will
report Exception:
_/Caused by: org.codehaus.commons.compiler.CompileException: Line 124,
Column 45: No applicable constructor/method found for actual parameters
"int"; /_
if I set the function's argument type to int, calcite will report
Exception:
_/Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No
match found for function signature MY_FUNCTION(<DATE>)/_
In my opinion, Now, I implement both two functions (one the arg type is
int, other's is Date ), and it can work, but I think whether there is
another better choice ? By the way , is date type must be transformed
to integer type when process the query ?
--
-------
Wei Hu
Re: question about implement a scalarFunction whose argument type is date or timestamp
Posted by Julian Hyde <jh...@apache.org>.
Calcite uses int, int, long to represent DATE, TIME, TIMESTAMP (respectively) if they are NOT NULL, and Integer, Integer, Long to represent them if they are nullable.
It seems likely that Calcite is deducing that the return type of the UDF is DATE NOT NULL. This is incorrect: it should be nullable DATE.
I have logged a feature request https://issues.apache.org/jira/browse/CALCITE-1013 to allow you to say that a UDF returns NOT NULL values.
But please also log this bug.
Julian
> On Dec 9, 2015, at 12:35 AM, Droopy Hoo <ol...@gmail.com> wrote:
>
> now the null value of Date or Timestamp type columns could not be queried.
>
> the runtime exception is cannot convert null to long
>
> Using Integer or Long to represent Date and Timestamp may be a better
> choice?
>
> 2015-11-20 4:41 GMT+08:00 Julian Hyde <jh...@apache.org>:
>
>> You are definitely seeing a bug. You should be able to define a UDF with a
>> java.sql.Date argument and have Calcite call it.
>>
>> I think the cause is that Calcite uses int internally to represent dates,
>> and it is not correctly inserting code to translate int to Date. You have
>> discovered a clever workaround — Calcite sees the Date version of your
>> function, and knows that the parameter type is a SQL DATE, then generates
>> code that uses the int version of the function.
>>
>> Can you please log a bug for this? We will try to fix for 1.6.
>>
>> Julian
>>
>>
>>> On Nov 19, 2015, at 12:29 AM, DroopyHoo <ol...@gmail.com> wrote:
>>>
>>> Hi,
>>>
>>> I have met a problem when using calcite 1.4 to implement a
>> scalarFunction for my udf.
>>>
>>> the field type is date and I want to realize the MY_FUNC(date)
>>>
>>> *SQL : select MY_FUNC(date) from table;*
>>>
>>> if I set my function's argument type to java.sql.Date, calcite will
>> report Exception:
>>>
>>> _/Caused by: org.codehaus.commons.compiler.CompileException: Line 124,
>> Column 45: No applicable constructor/method found for actual parameters
>> "int"; /_
>>>
>>>
>>> if I set the function's argument type to int, calcite will report
>> Exception:
>>>
>>> _/Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No
>> match found for function signature MY_FUNCTION(<DATE>)/_
>>>
>>>
>>> In my opinion, Now, I implement both two functions (one the arg type is
>> int, other's is Date ), and it can work, but I think whether there is
>> another better choice ? By the way , is date type must be transformed to
>> integer type when process the query ?
>>>
>>> --
>>> -------
>>> Wei Hu
>>>
>>
>>
Re: question about implement a scalarFunction whose argument type is
date or timestamp
Posted by Droopy Hoo <ol...@gmail.com>.
now the null value of Date or Timestamp type columns could not be queried.
the runtime exception is cannot convert null to long
Using Integer or Long to represent Date and Timestamp may be a better
choice?
2015-11-20 4:41 GMT+08:00 Julian Hyde <jh...@apache.org>:
> You are definitely seeing a bug. You should be able to define a UDF with a
> java.sql.Date argument and have Calcite call it.
>
> I think the cause is that Calcite uses int internally to represent dates,
> and it is not correctly inserting code to translate int to Date. You have
> discovered a clever workaround — Calcite sees the Date version of your
> function, and knows that the parameter type is a SQL DATE, then generates
> code that uses the int version of the function.
>
> Can you please log a bug for this? We will try to fix for 1.6.
>
> Julian
>
>
> > On Nov 19, 2015, at 12:29 AM, DroopyHoo <ol...@gmail.com> wrote:
> >
> > Hi,
> >
> > I have met a problem when using calcite 1.4 to implement a
> scalarFunction for my udf.
> >
> > the field type is date and I want to realize the MY_FUNC(date)
> >
> > *SQL : select MY_FUNC(date) from table;*
> >
> > if I set my function's argument type to java.sql.Date, calcite will
> report Exception:
> >
> > _/Caused by: org.codehaus.commons.compiler.CompileException: Line 124,
> Column 45: No applicable constructor/method found for actual parameters
> "int"; /_
> >
> >
> > if I set the function's argument type to int, calcite will report
> Exception:
> >
> > _/Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No
> match found for function signature MY_FUNCTION(<DATE>)/_
> >
> >
> > In my opinion, Now, I implement both two functions (one the arg type is
> int, other's is Date ), and it can work, but I think whether there is
> another better choice ? By the way , is date type must be transformed to
> integer type when process the query ?
> >
> > --
> > -------
> > Wei Hu
> >
>
>
Re: question about implement a scalarFunction whose argument type is date or timestamp
Posted by Julian Hyde <jh...@apache.org>.
You are definitely seeing a bug. You should be able to define a UDF with a java.sql.Date argument and have Calcite call it.
I think the cause is that Calcite uses int internally to represent dates, and it is not correctly inserting code to translate int to Date. You have discovered a clever workaround — Calcite sees the Date version of your function, and knows that the parameter type is a SQL DATE, then generates code that uses the int version of the function.
Can you please log a bug for this? We will try to fix for 1.6.
Julian
> On Nov 19, 2015, at 12:29 AM, DroopyHoo <ol...@gmail.com> wrote:
>
> Hi,
>
> I have met a problem when using calcite 1.4 to implement a scalarFunction for my udf.
>
> the field type is date and I want to realize the MY_FUNC(date)
>
> *SQL : select MY_FUNC(date) from table;*
>
> if I set my function's argument type to java.sql.Date, calcite will report Exception:
>
> _/Caused by: org.codehaus.commons.compiler.CompileException: Line 124, Column 45: No applicable constructor/method found for actual parameters "int"; /_
>
>
> if I set the function's argument type to int, calcite will report Exception:
>
> _/Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature MY_FUNCTION(<DATE>)/_
>
>
> In my opinion, Now, I implement both two functions (one the arg type is int, other's is Date ), and it can work, but I think whether there is another better choice ? By the way , is date type must be transformed to integer type when process the query ?
>
> --
> -------
> Wei Hu
>