在phoenix中upsert date类型数据如下sql:
upsert into "test_date_type" (ID,"birthday") values (102, '2013-09-01 09:00:02');
执行时会有如下错误信息:
com.salesforce.phoenix.schema.TypeMismatchException: ERROR 203 (22005): Type mismatch. DATE and VARCHAR for 2013-09-01 09:00:02
at com.salesforce.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:126)
at com.salesforce.phoenix.expression.LiteralExpression.newConstant(LiteralExpression.java:108)
at com.salesforce.phoenix.compile.UpsertCompiler$UpsertValuesCompiler.visit(UpsertCompiler.java:633)
at com.salesforce.phoenix.compile.UpsertCompiler$UpsertValuesCompiler.visit(UpsertCompiler.java:609)
at com.salesforce.phoenix.parse.LiteralParseNode.accept(LiteralParseNode.java:70)
at com.salesforce.phoenix.compile.UpsertCompiler.compile(UpsertCompiler.java:554)
at com.salesforce.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:290)
at com.salesforce.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.optimizePlan(PhoenixStatement.java:295)
at com.salesforce.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.executeUpdate(PhoenixStatement.java:279)
at com.salesforce.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.execute(PhoenixStatement.java:272)
at com.salesforce.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1014)>
通常在关系型数据库中我们可以用字符串来表示一个date类型,但是在phoenix中是不行的,会抛出TypeMismatchException: ERROR 203 (22005): Type mismatch. DATE and VARCHAR异常。
正确的做法是使用TO_DATE函数:
upsert into "test_date_type" (ID,"birthday") values (102, TO_DATE('2013-09-01 09:00:02'));