You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by hzhuangzhenxi <hz...@corp.netease.com> on 2017/05/04 04:49:39 UTC
ISSUES about DATE and TIMESTAMP
Hi,
Firstly, define a UDF like this:
public class UDFDayOfMonth implements UDF { private final SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); private final Calendar calendar = Calendar.getInstance(); /** * Get the day of month from a date string. * * @param dateStr the dateStr in the format of "yyyy-MM-dd HH:mm:ss" or * "yyyy-MM-dd". * @return an int from 1 to 31. null if the dateStr is not a valid date * string. */ public Integer evaluate(String dateStr) throws ParseException { if (dateStr == null) { return null; } try { java.util.Date date = formatter.parse(dateStr); calendar.setTime(date); return calendar.get(Calendar.DAY_OF_MONTH); } catch (ParseException e) { throw e; } } public Integer evaluate(Date date) { if (date == null) { return null; } calendar.setTime(date); // Time doesn't matter. return calendar.get(Calendar.DAY_OF_MONTH); } public Integer evaluate(Timestamp timestamp) { if (timestamp == null) { return null; } calendar.setTime(timestamp); return calendar.get(Calendar.DAY_OF_MONTH); }}
Secondly, Define filds like this:
CREATE TABLE kafka_source1 ( name CHARACTER(250), price TIMESTAMP) Thirdly, CREATE TEMPORARY function 'UDFDAYOFMONTH' AS 'com.netease.sloth.server.udf.UDFDayOfMonth';INSERT INTO db_sink SELECT name, UDFDAYOFMONTH(price) FROM kafka_source1; After debugging, find that But, typeNames only has 'TIMESTAMP' field，no 'Date' field
assert containsType(types) occurs error.
The error stack:
java.lang.AssertionError
at org.apache.calcite.sql.type.SqlTypeExplicitPrecedenceList.compareTypePrecedence(SqlTypeExplicitPrecedenceList.java:153)
at org.apache.calcite.sql.SqlUtil.bestMatch(SqlUtil.java:622)
at org.apache.calcite.sql.SqlUtil.filterRoutinesByTypePrecedence(SqlUtil.java:589)
at org.apache.calcite.sql.SqlUtil.lookupSubjectRoutines(SqlUtil.java:443)
at org.apache.calcite.sql.SqlUtil.lookupRoutine(SqlUtil.java:366)
at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:244)
at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:223)
at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4445)
at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4432)
at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:137)
at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1532)
at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1515)
at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:428)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:3581)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3088)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateInsert(SqlValidatorImpl.java:3754)
at org.apache.calcite.sql.SqlInsert.validate(SqlInsert.java:148)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:865)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:576)
at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:185)
at com.netease.sloth.server.plan.SlothPlanner.parseSqlDml(SlothPlanner.java:377)
at com.netease.sloth.server.actor.AbstractJobManager.handle(AbstractJobManager.java:149)
at com.netease.sloth.server.actor.BuildInUDFTest.testRunJobLocal(BuildInUDFTest.java:57)
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:51)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
2017-05-04
黄珍锡
网易杭州研究院数据科学中心
杭州市滨江区网商路599号
Tel：18358190458
Email：hzhuangzhenxi@corp.netease.com
Re: ISSUES about DATE and TIMESTAMP
Posted by Julian Hyde <jh...@apache.org>.
Some of your images didn’t come through. Can you log a JIRA case for this, please?
Can you try to create a minimal test case? You might start with UdfTest.testDate:
@Test public void testDate() {
final CalciteAssert.AssertThat with = withUdf();
with.query("values \"adhoc\".\"dateFun\"(DATE '1970-01-01')")
.returnsValue("0”);
}
which is based on Smalls.AllTypesFunction.dateFun() and works successfully.
Julian
> On May 3, 2017, at 9:49 PM, hzhuangzhenxi <hz...@corp.netease.com> wrote:
>
> Hi,
> Firstly, define a UDF like this:
> public class UDFDayOfMonth implements UDF {
>
> private final SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
> private final Calendar calendar = Calendar.getInstance();
>
> /**
> * Get the day of month from a date string.
> *
> * @param dateStr the dateStr in the format of "yyyy-MM-dd HH:mm:ss" or
> * "yyyy-MM-dd".
> * @return an int from 1 to 31. null if the dateStr is not a valid date
> * string.
> */
> public Integer evaluate(String dateStr) throws ParseException {
> if (dateStr == null) {
> return null;
> }
>
> try {
> java.util.Date date = formatter.parse(dateStr);
> calendar.setTime(date);
> return calendar.get(Calendar.DAY_OF_MONTH);
> } catch (ParseException e) {
> throw e;
> }
> }
>
> public Integer evaluate(Date date) {
> if (date == null) {
> return null;
> }
>
> calendar.setTime(date); // Time doesn't matter.
> return calendar.get(Calendar.DAY_OF_MONTH);
> }
>
> public Integer evaluate(Timestamp timestamp) {
> if (timestamp == null) {
> return null;
> }
>
> calendar.setTime(timestamp);
> return calendar.get(Calendar.DAY_OF_MONTH);
> }
> }
> Secondly, Define filds like this:
> CREATE TABLE kafka_source1 (
> name CHARACTER(250),
> price TIMESTAMP
> )
>
> Thirdly,
> CREATE TEMPORARY function 'UDFDAYOFMONTH' AS 'com.netease.sloth.server.udf.UDFDayOfMonth';
> INSERT INTO db_sink SELECT name, UDFDAYOFMONTH(price) FROM kafka_source1;
>
> After debugging, find that
>
>
>
>
>
>
> But, typeNames only has 'TIMESTAMP' field，no 'Date' field
>
>
> assert containsType(types) occurs error.
>
>
>
> The error stack:
> java.lang.AssertionError
> at org.apache.calcite.sql.type.SqlTypeExplicitPrecedenceList.compareTypePrecedence(SqlTypeExplicitPrecedenceList.java:153)
> at org.apache.calcite.sql.SqlUtil.bestMatch(SqlUtil.java:622)
> at org.apache.calcite.sql.SqlUtil.filterRoutinesByTypePrecedence(SqlUtil.java:589)
> at org.apache.calcite.sql.SqlUtil.lookupSubjectRoutines(SqlUtil.java:443)
> at org.apache.calcite.sql.SqlUtil.lookupRoutine(SqlUtil.java:366)
> at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:244)
> at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:223)
> at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4445)
> at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4432)
> at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:137)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1532)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1515)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:428)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:3581)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3088)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateInsert(SqlValidatorImpl.java:3754)
> at org.apache.calcite.sql.SqlInsert.validate(SqlInsert.java:148)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:865)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:576)
> at org.apache.calcite.prepare.PlannerImpl.validate(PlannerImpl.java:185)
> at com.netease.sloth.server.plan.SlothPlanner.parseSqlDml(SlothPlanner.java:377)
> at com.netease.sloth.server.actor.AbstractJobManager.handle(AbstractJobManager.java:149)
> at com.netease.sloth.server.actor.BuildInUDFTest.testRunJobLocal(BuildInUDFTest.java:57)
> 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:51)
> at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237)
> at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
> 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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
>
> 2017-05-04
> 黄珍锡
> 网易杭州研究院数据科学中心
> 杭州市滨江区网商路599号
> Tel：18358190458
> Email：hzhuangzhenxi@corp.netease.com <ma...@corp.netease.com>