search

running-elephant / datart

Datart is a next generation Data Visualization Open Platform
https://running-elephant.github.io/datart-docs/
Apache License 2.0
1.98k stars 590 forks source link

“toDate”函数经过解析之后变成了大写“TODATE”，数据库不识别 #998

Closed CharlesTHN closed 2 years ago

CharlesTHN commented 2 years ago

Datart版本号 1.0.0.beta.2

错误描述 “toDate”函数经过解析之后变成了大写“TODATE”，数据库不识别

如何重现 重现错误的步骤，例如：

  1. 配置数据视图，此时执行没有问题
  2. 到仪表板增加日期控件，关联日期变量
  3. 看到错误

截图 ` 2022-03-14 16:57:48.151 ERROR datart.server.config.WebExceptionHandler : ClickHouse exception, code: 1002, host: 116.-.6.-, port: -; Code: 46. DB::Exception: Unknown function TODATE. Maybe you meant: ['toDate','toDate32']: While processing SELECT dt FROM dim.dim_pub_date WHERE (dt >= TODATE(toDateTime('2022-02-12 00:00:00'))) AND (dt <= TODATE('2022-03-15 00:00:00')). (UNKNOWN_FUNCTION) (version 21.9.2.17 (official build))

ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, code: 1002, host: 116.-.6.-, port: -; Code: 46. DB::Exception: Unknown function TODATE. Maybe you meant: ['toDate','toDate32']: While processing SELECT dt FROM dim.dim_pub_date WHERE (dt >= TODATE(toDateTime('2022-02-12 00:00:00'))) AND (dt <= TODATE('2022-03-15 00:00:00')). (UNKNOWN_FUNCTION) (version 21.9.2.17 (official build))

    at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.getException(ClickHouseExceptionSpecifier.java:91)
    at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:55)
    at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:28)
    at ru.yandex.clickhouse.ClickHouseStatementImpl.checkForErrorAndThrow(ClickHouseStatementImpl.java:875)
    at ru.yandex.clickhouse.ClickHouseStatementImpl.getInputStream(ClickHouseStatementImpl.java:616)
    at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:117)
    at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:100)
    at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:95)
    at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:90)
    at com.alibaba.druid.pool.DruidPooledStatement.executeQuery(DruidPooledStatement.java:296)
    at datart.data.provider.jdbc.adapters.JdbcDataProviderAdapter.execute(JdbcDataProviderAdapter.java:188)
    at datart.data.provider.jdbc.adapters.JdbcDataProviderAdapter.executeOnSource(JdbcDataProviderAdapter.java:372)
    at datart.data.provider.jdbc.adapters.JdbcDataProviderAdapter.execute(JdbcDataProviderAdapter.java:229)
    at datart.data.provider.JdbcDataProvider.execute(JdbcDataProvider.java:90)
    at datart.data.provider.ProviderManager.run(ProviderManager.java:231)
    at datart.data.provider.optimize.DataProviderExecuteOptimizer.runOptimize(DataProviderExecuteOptimizer.java:47)
    at datart.data.provider.ProviderManager.execute(ProviderManager.java:119)
    at datart.server.service.impl.DataProviderServiceImpl.execute(DataProviderServiceImpl.java:263)
    at datart.server.controller.DataProviderController.execute(DataProviderController.java:101)
    at sun.reflect.GeneratedMethodAccessor268.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:652)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:327)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:126)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:81)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:105)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:149)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:103)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:89)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
    at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90)
    at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:110)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:80)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:55)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:336)
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:211)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:183)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:887)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1684)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748)

Caused by: java.lang.Throwable: Code: 46. DB::Exception: Unknown function TODATE. Maybe you meant: ['toDate','toDate32']: While processing SELECT dt FROM dim.dim_pub_date WHERE (dt >= TODATE(toDateTime('2022-02-12 00:00:00'))) AND (dt <= TODATE('2022-03-15 00:00:00')). (UNKNOWN_FUNCTION) (version 21.9.2.17 (official build))

    at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:53)
    ... 98 common frames omitted

`

运行环境详情：(可选，取决于错误发生的具体位置)

tianlu-root commented 2 years ago

这个可能是变量处理逻辑的问题，方便提供你在数据视图页面编写的SQL吗？

CharlesTHN commented 2 years ago

select toDate(fromUnixTimestamp64Milli(reportTime)) ts_date, userId from data_bi.light_chain_track where length(userId) > 5 and ts_date between toDate($DATE_MIN$) and toDate($DATE_MAX$)

image

tianlu-root commented 2 years ago

我看你的SQL里面已经用了toDate函数了，那 $DATE_MIN$ 这个变量类型就应该定义为字符串类型。

CharlesTHN commented 2 years ago

定义为字符串类型也没用 image

tianlu-root commented 2 years ago

明白问题所在了，目前SQL处理确实会修改函数的大小写，这个需要优化。 另外你的场景目前可以尝试用两种方式看能不能正常工作。 方式1、把变量里的toDate函数去掉，直接写变量，然后变量定义为日期类型。 方式2，SQL保持现有写法，然后把两个变量都定义为表达式类型。

CharlesTHN commented 2 years ago

image 如果我这个两个变量都写为today()之后，在数据视图是可以运行的，但是在仪表盘关联日期筛选的时候还是会出现这个问题

CharlesTHN commented 2 years ago

目前有一种情况可以运行，就是变量定义为表达式类型，sql 里的toDate也去掉，这样数据视图和仪表盘关联日期筛选的时候都不会报错。

tianlu-root commented 2 years ago

好的，这个问题我们会尽快修复。

CharlesTHN commented 2 years ago

感谢，另外这个toDate引申出另一个问题，我用toDate再去转换的原因是： 对于日期类型的变量，系统解析使用的是datetime，所以对于精确到日的日期，使用 between and 就有最大边界的问题。 即 我选择 2021/05/04-2021/05/06，经过datetime 翻译了之后就会变成 2021/05/04 00:00:00-2021/05/06 00:00:00 ，那么就少了6号一整天的数据