clickhouse-java icon indicating copy to clipboard operation
clickhouse-java copied to clipboard
verified publisher icon ClickHouse

ParsingException during insert for negative (before 1970-01-01) values into DateTime

Open TimonSP opened this issue 2 years ago • 0 comments

I got parse exception trying to insert negative (before 1970-01-01) values into DateTime. I'm inserting using Greenplum external table with JDBC_DRIVER=com.clickhouse.jdbc.ClickHouseDriver

Steps to reproduce

  1. Create table with DateTime64(3,'UTC') in ClickHouse
  2. Create external table that linked on this table
  3. Try to insert negative values

Expected behaviour

A row is inserted with values before 1970-01-01

Code example

ClickHouse:

CREATE TABLE TestDateTime64(
    KeyValue String,
    TestDate DateTime64(3,'UTC'),
    FinalColumn Int64
)
ENGINE = MergeTree
ORDER BY KeyValue;

Greenplum:

create writable external table public.TestDateTime64 (
    "KeyValue" text
    ,"TestDate" bigint
    ,"FinalColumn" bigint
)
LOCATION ('pxf://TestDateTime64?PROFILE=JDBC&JDBC_DRIVER=com.clickhouse.jdbc.ClickHouseDriver&DB_URL=jdbc:clickhouse://xxxxx&USER=x&PASS=x')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export')
ENCODING 'UTF8'; 

`INSERT INTO public.TestDateTime64 VALUES ('EXT INSERT -2', -2, -2)

Exception

ClickHouse exception, code: 1002, host: x.x.x.x, port: x; Code: 27. DB::ParsingException: Cannot parse input: expected '\\t' before: '-1\\t-1\
':\
Row 1:\
Column 0,   name: KeyValue,    type: String,               parsed text: \"EXT INSERT -1\"\
Column 1,   name: TestDate,    type: DateTime64(3, 'UTC'), ERROR: text \"-1<TAB>-1<LINE FEED>\" is not like DateTime64(3, 'UTC')\
\
: While executing ParallelParsingBlockInputFormat: (at row 1)\
. (CANNOT_PARSE_INPUT_ASSERTION_FAILED) (version 23.3.9.55 (official build))\
\
\
ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, code: 1002, host: x.x.x.x, port: x; Code: 27. DB::ParsingException: Cannot parse input: expected '\\t' before: '-1\\t-1\
':\
Row 1:\
Column 0,   name: KeyValue,    type: String,               parsed text: \"EXT INSERT -1\"\
Column 1,   name: TestDate,    type: DateTime64(3, 'UTC'), ERROR: text \"-1<TAB>-1<LINE FEED>\" is not like DateTime64(3, 'UTC')\
\
: While executing ParallelParsingBlockInputFormat: (at row 1)\
. (CANNOT_PARSE_INPUT_ASSERTION_FAILED) (version 23.3.9.55 (official build))\
\
\\tat ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.getException(ClickHouseExceptionSpecifier.java:92) ~[clickhouse-jdbc-0.3.1-patch.jar:0.3.1-patch]\
\\tat ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:56) ~[clickhouse-jdbc-0.3.1-patch.jar:0.3.1-patch]\
\\tat ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:29) ~[clickhouse-jdbc-0.3.1-patch.jar:0.3.1-patch]\
\\tat ru.yandex.clickhouse.ClickHouseStatementImpl.checkForErrorAndThrow(ClickHouseStatementImpl.java:1099) ~[clickhouse-jdbc-0.3.1-patch.jar:0.3.1-patch]\
\\tat ru.yandex.clickhouse.ClickHouseStatementImpl.sendStream(ClickHouseStatementImpl.java:1066) ~[clickhouse-jdbc-0.3.1-patch.jar:0.3.1-patch]\
\\tat ru.yandex.clickhouse.ClickHouseStatementImpl.sendStream(ClickHouseStatementImpl.java:1031) ~[clickhouse-jdbc-0.3.1-patch.jar:0.3.1-patch]\
\\tat ru.yandex.clickhouse.ClickHouseStatementImpl.sendStream(ClickHouseStatementImpl.java:1024) ~[clickhouse-jdbc-0.3.1-patch.jar:0.3.1-patch]\
\\tat ru.yandex.clickhouse.ClickHousePreparedStatementImpl.executeBatch(ClickHousePreparedStatementImpl.java:381) ~[clickhouse-jdbc-0.3.1-patch.jar:0.3.1-patch]\
\\tat ru.yandex.clickhouse.ClickHousePreparedStatementImpl.executeBatch(ClickHousePreparedStatementImpl.java:364) ~[clickhouse-jdbc-0.3.1-patch.jar:0.3.1-patch]\
\\tat com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128) ~[HikariCP-3.4.5.jar!/:?]\
\\tat com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.5.jar!/:?]\
\\tat org.greenplum.pxf.plugins.jdbc.writercallable.BatchWriterCallable.call(BatchWriterCallable.java:73) ~[pxf-jdbc-6.6.0.jar!/:?]\
\\tat org.greenplum.pxf.plugins.jdbc.JdbcAccessor.closeForWrite(JdbcAccessor.java:329) ~[pxf-jdbc-6.6.0.jar!/:?]\
\\tat org.greenplum.pxf.service.bridge.WriteBridge.endIteration(WriteBridge.java:90) ~[classes!/:6.6.0]\
\\tat org.greenplum.pxf.service.controller.WriteServiceImpl.readStream(WriteServiceImpl.java:74) ~[classes!/:6.6.0]\
\\tat org.greenplum.pxf.service.controller.WriteServiceImpl.lambda$writeData$0(WriteServiceImpl.java:40) ~[classes!/:6.6.0]\
\\tat java.security.AccessController.doPrivileged(Native Method) ~[?:?]\
\\tat javax.security.auth.Subject.doAs(Subject.java:361) ~[?:?]\
\\tat org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1918) ~[hadoop-common-2.10.2.jar!/:?]\
\\tat org.greenplum.pxf.service.security.BaseSecurityService.doAs(BaseSecurityService.java:122) ~[classes!/:6.6.0]\
\\tat org.greenplum.pxf.service.controller.BaseServiceImpl.processData(BaseServiceImpl.java:74) ~[classes!/:6.6.0]\
\\tat org.greenplum.pxf.service.controller.WriteServiceImpl.writeData(WriteServiceImpl.java:40) ~[classes!/:6.6.0]\
\\tat org.greenplum.pxf.service.rest.PxfWriteResource.produceResponse(PxfWriteResource.java:52) ~[classes!/:6.6.0]\
\\tat org.greenplum.pxf.service.rest.PxfWriteResource.produceResponse(PxfWriteResource.java:19) ~[classes!/:6.6.0]\
\\tat org.greenplum.pxf.service.rest.PxfBaseResource.lambda$processRequest$0(PxfBaseResource.java:51) ~[classes!/:6.6.0]\
\\tat org.greenplum.pxf.service.controller.PxfErrorReporter.invokeWithErrorHandling(PxfErrorReporter.java:26) [classes!/:6.6.0]\
\\tat org.greenplum.pxf.service.rest.PxfBaseResource.processRequest(PxfBaseResource.java:48) [classes!/:6.6.0]\
\\tat org.greenplum.pxf.service.rest.PxfWriteResource.stream(PxfWriteResource.java:47) [classes!/:6.6.0]\
\\tat jdk.internal.reflect.GeneratedMethodAccessor114.invoke(Unknown Source) ~[?:?]\
\\tat jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]\
\\tat java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]\
\\tat org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) [spring-web-5.3.18.jar!/:5.3.18]\
\\tat org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) [spring-web-5.3.18.jar!/:5.3.18]\
\\tat org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) [spring-webmvc-5.3.18.jar!/:5.3.18]\
\\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) [spring-webmvc-5.3.18.jar!/:5.3.18]\
\\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) [spring-webmvc-5.3.18.jar!/:5.3.18]\
\\tat org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) [spring-webmvc-5.3.18.jar!/:5.3.18]\
\\tat org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067) [spring-webmvc-5.3.18.jar!/:5.3.18]\
\\tat org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963) [spring-webmvc-5.3.18.jar!/:5.3.18]\
\\tat org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) [spring-webmvc-5.3.18.jar!/:5.3.18]\
\\tat org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909) [spring-webmvc-5.3.18.jar!/:5.3.18]\
\\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:681) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) [spring-webmvc-5.3.18.jar!/:5.3.18]\
\\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:764) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) [tomcat-embed-websocket-9.0.60.jar!/:?]\
\\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.greenplum.pxf.service.spring.PxfContextMdcLogEnhancerFilter.doFilterInternal(PxfContextMdcLogEnhancerFilter.java:41) [classes!/:6.6.0]\
\\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) [spring-web-5.3.18.jar!/:5.3.18]\
\\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) [spring-web-5.3.18.jar!/:5.3.18]\
\\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) [spring-web-5.3.18.jar!/:5.3.18]\
\\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) [spring-web-5.3.18.jar!/:5.3.18]\
\\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) [spring-web-5.3.18.jar!/:5.3.18]\
\\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96) [spring-boot-actuator-2.5.12.jar!/:2.5.12]\
\\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) [spring-web-5.3.18.jar!/:5.3.18]\
\\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) [spring-web-5.3.18.jar!/:5.3.18]\
\\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) [spring-web-5.3.18.jar!/:5.3.18]\
\\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:889) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.60.jar!/:?]\
\\tat java.lang.Thread.run(Thread.java:829) [?:?]\
Caused by: java.lang.Throwable: Code: 27. DB::ParsingException: Cannot parse input: expected '\\t' before: '-1\\t-1\
':\
Row 1:\
Column 0,   name: KeyValue,    type: String,               parsed text: \"EXT INSERT -1\"\
Column 1,   name: TestDate,    type: DateTime64(3, 'UTC'), ERROR: text \"-1<TAB>-1<LINE FEED>\" is not like DateTime64(3, 'UTC')\
\
: While executing ParallelParsingBlockInputFormat: (at row 1)\
. (CANNOT_PARSE_INPUT_ASSERTION_FAILED) (version 23.3.9.55 (official build))\
\
\\tat ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:54) ~[clickhouse-jdbc-0.3.1-patch.jar:0.3.1-patch]\
\\t... 83 more\

TimonSP avatar Sep 14 '23 02:09 TimonSP