ClickHouse / ClickHouse/clickhouse-java

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

Offen
#1,448 1 Kommentar 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen
action:verify area:data-type area:sql-parser bug investigating
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

Beitragsleitfaden öffnen

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

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.