ClickHouse / ClickHouse/clickhouse-java
ParsingException during insert for negative (before 1970-01-01) values into DateTime
- Langage dominant
- Java
- Étoiles
- 1.6k
- Forks
- 636
- Merge moyen
- 2 j 23 h
- PR mergées (30 j)
- 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\
```
Guide de contribution
Ouvrir le guide de contribution
Piste de recherche
Commencez par reproduire le problème signalé avec la table externe Greenplum en utilisant le pilote JDBC ClickHouse, puis suivez les entrées de la pile dans ClickHousePreparedStatementImpl.executeBatch et ClickHouseStatementImpl.sendStream. Vérifiez comment les valeurs DateTime64 négatives sont sérialisées et analysées. Le travail est terminé lorsque l’insertion de valeurs antérieures au 1970-01-01 réussit et que la ligne résultante peut être relue correctement.
Rédigé par le modèle d'indexation à partir du texte de l'issue.
Évaluation
- Stack technique
- java, sql
- Domaine
- databases
- Type d'issue
- Bug
- Difficulté
- 4/5
- Temps estimé
- 3-5 jours
- Activité
- À l'abandon
- Clarté
- Plutôt claire
- Accessibilité débutants
- 42/100