ClickHouse / ClickHouse/clickhouse-java
ParsingException during insert for negative (before 1970-01-01) values into DateTime
- Vorherrschende Sprache
- Java
- Sterne
- 1.6k
- Forks
- 636
- Ø Merge
- 2 T. 23 Std.
- Gemergte PRs (30 T.)
- 29
Beschreibung
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\
```
Beitragsleitfaden
Rechercherichtung
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.
Vom Indexierungsmodell aus dem Issue-Text verfasst.
Bewertung
- Tech-Stack
- java, sql
- Bereich
- databases
- Issue-Typ
- Bug
- Schwierigkeit
- 4/5
- Geschätzter Aufwand
- 3-5 Tage
- Aktivitätsstatus
- Veraltet
- Klarheit
- Größtenteils klar
- Anfängerfreundlichkeit
- 42/100