ClickHouse / ClickHouse/clickhouse-java

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

Open
#1,448 1 comment 0 reactions 0 assignees View on GitHub
action:verify area:data-type area:sql-parser bug investigating
Dominant language
Java
Stars
1.6k
Forks
636
Avg merge
2d 23h
Merged PRs (30d)
29

Description

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-1\" 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-1\" 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-1\" 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\
```

Contributor guide

Open the contributing guide

Research direction

Start with the reported Greenplum external-table reproduction using the ClickHouse JDBC driver and trace the stack entries in ClickHousePreparedStatementImpl.executeBatch and ClickHouseStatementImpl.sendStream. Confirm how negative DateTime64 values are serialized and parsed. Done means inserting values before 1970-01-01 succeeds and the resulting row can be read back correctly.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.