search

ClickHouse / clickhouse-java

ClickHouse Java Clients & JDBC Driver
https://clickhouse.com
Apache License 2.0
1.45k stars 534 forks source link

clickhouse-jdbc 执行sql 不支持 中文注释吗 #971

Open kiwimg opened 2 years ago

kiwimg commented 2 years ago

clickhouse-jdbc v0.3.1 不支持代中文注释sql 吗？

-- 注释描述 select * from users

; nested exception is ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, code: 1002, host: XXX.XX.XX.XXX, port: 8123; Code: 62. DB::Exception: Syntax error: failed at position 2190

zhicwu commented 2 years ago

clickhouse-jdbc v0.3.1 不支持代注释sql 吗？

Both legacy and new driver work with SQL with unicode comments. The error you provided is actually complaining table does not exist.

kiwimg commented 2 years ago

中文需要使用 unicode comments？

zhicwu commented 2 years ago

By default the SQL query including comments is utf-8 encoded. Can you provide the exact query and comments leading to a parsing error in JDBC driver?

kiwimg commented 2 years ago

org.springframework.jdbc.UncategorizedSQLException:

Error querying database. Cause: ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, code: 1002, host: xxx.xx.xx.x, port: 8123; Code: 62. DB::Exception: Syntax error: failed at position 2190 ('('): ( -- 筛选条件 将与每一条维度数据取交集 with base as ( select dlob.ids ids from dc_live_or. Unmatched parentheses: (. (SYNTAX_ERROR) (version 21.11.4.14 (official build))

The error may exist in URL [jar:file:/app.jar!/BOOT-INF/lib/export-3.0.0.jar!/mapper/system/ClickHouseMapper.xml]

The error may involve defaultParameterMap

The error occurred while setting parameters

SQL: with res as ( with cur as ( select concat(flow_year,'年',flow_month,'月') as dim, toStartOfMonth(flow_date) mon, count(DISTINCT buyer_code) cusNum, sum(sku_sale_count) amt, sum(sku_sale_count) saleCnt from fffff_all dsa where 1=1 and flow_date >= toDateTime('2021-12-01') and flow_date < addDays(toDateTime('2022-05-31'), 1) and clean_sign = 1 and is_invalid = 0 group by flow_year,flow_month ,mon ), pst as ( select concat(flow_year,'年',flow_month,'月') as dim, addYears(toStartOfMonth(flow_date), 1) mon, count(DISTINCT buyer_code) cusNum, sum(sku_sale_count) amt, sum(sku_sale_count) saleCnt from fffff_all dsa where 1=1 and flow_date >= subtractYears(toDateTime('2021-12-01'), 1) and flow_date < subtractYears(addDays(toDateTime('2022-05-31'), 1), 1) and clean_sign = 1 and is_invalid = 0 group by flow_year,flow_month ,mon ) ,cntCusByTimeRegion as ( select count(distinct buyer_code) cusNum from fffff_all dsa where 1=1 and flow_date >= toDateTime('2021-12-01') and flow_date < addDays(toDateTime('2022-05-31'), 1) ) select case when cur.dim is null or cur.dim = '' then '其他' else cur.dim end as dim, cur.mon flow_dt, cntCusByTimeRegion.cusNum cusNumSumByRegion, cur.cusNum curCusNum, cur.amt as curAmt, cur.saleCnt as curSaleCnt, pst.amt as pstAmt, pst.saleCnt as pstSaleCnt from cur , cntCusByTimeRegion global left join pst on cur.mon = pst.mon group by dim, curCusNum, curAmt, curSaleCnt, pstAmt, pstSaleCnt ,

flow_dt, cusNumSumByRegion ), cntCus as ( SELECT COUNT(1) totalCusNum from oddd do where 1=1 ), cntCusWmq as ( -- 筛选条件 将与每一条维度数据取交集 with base as ( select dlob.ids ids from bitmap dlob where type = 1 ), res as ( select base.ids ids from base ) select groupBitmapAnd(res.ids) totalCusNum from res ) select res.dim dim, res.flow_dt flow_dt, res.cusNumSumByRegion cusNumSumByRegion, any(cntCus.totalCusNum) totalCusNum, any(cntCusWmq.totalCusNum) totalCusNumWmq, round(sum (res.curCusNum), 2) curCusNum, round(sum (res.curAmt), 2) as curAmt, round(sum (res.curSaleCnt), 2) as curSaleCnt, round(sum (res.pstAmt), 2) as pstAmt, round(sum (res.pstSaleCnt), 2) as pstSaleCnt, case when pstAmt = 0 then 0 else round((curAmt - pstAmt) 100 / pstAmt, 2) end as amtIncRt, case when pstSaleCnt = 0 then 0 else round((curSaleCnt - pstSaleCnt) 100 / pstSaleCnt, 2) end as saleCntIncRt, case when totalCusNum = 0 then 0 else round((curCusNum 100 / totalCusNum), 2) end as covRt, case when totalCusNumWmq = 0 then 0 else round((curCusNum 100 / totalCusNumWmq), 2) end as covRtWmq from res ,cntCus , cntCusWmq group by dim ,flow_dt, cusNumSumByRegion limit 1

Cause: ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, code: 1002, host: xxx.xx.xx.x, port: 8123; Code: 62. DB::Exception: Syntax error: failed at position 2190 ('('): ( -- 筛选条件 将与每一条维度数据取交集 with base as ( select dlob.ids ids from dc_live_or. Unmatched parentheses: (. (SYNTAX_ERROR) (version 21.11.4.14 (official build))

; uncategorized SQLException; SQL state [null]; error code [1002]; ClickHouse exception, code: 1002, host: xxx.xx.xx.x, port: 8123; Code: 62. DB::Exception: Syntax error: failed at position 2190 ('('): ( -- 筛选条件 将与每一条维度数据取交集 with base as ( select dlob.ids ids from dc_live_or. Unmatched parentheses: (. (SYNTAX_ERROR) (version 21.11.4.14 (official build)) ; nested exception is ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, code: 1002, host: xxx.xx.xx.x, port: 8123; Code: 62. DB::Exception: Syntax error: failed at position 2190 ('('): ( -- 筛选条件 将与每一条维度数据取交集 with base as ( select dlob.ids ids from dc_live_or. Unmatched parentheses: (. (SYNTAX_ERROR) (version 21.11.4.14 (official build)) 

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) 
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) 
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) 
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88) 
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) 
at com.sun.proxy.$Proxy130.selectOne(Unknown Source) 
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159) 
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:90) 
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148) 
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) 
at com.sun.proxy.$Proxy157.invokeSql(Unknown Source) 
at com.ddd.sccc.dd.export.service.impl.ClickHouseServiceImpl.executeSqlLimit1(ClickHouseServiceImpl.java:100) 
at com.ddd.sccc.dd.export.service.impl.ClickHouseServiceImpl$$FastClassBySpringCGLIB$$565b384e.invoke(<generated>) 
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) 
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749) 
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) 
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88) 
at com.ddd.sccc.dd.framework.aspectj.DataSourceAspect.around(DataSourceAspect.java:50) 
at sun.reflect.GeneratedMethodAccessor316.invoke(Unknown Source) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
at java.lang.reflect.Method.invoke(Method.java:498) 
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644) 
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633) 
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70) 
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175) 
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93) 
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) 
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) 
at com.ddd.sccc.dd.export.service.impl.ClickHouseServiceImpl$$EnhancerBySpringCGLIB$$c1c7d530.executeSqlLimit1(<generated>) 
at com.ddd.sccc.dd.export.service.impl.InvokeExportServiceImpl.cursorItemCkReader(InvokeExportServiceImpl.java:342) 
at com.ddd.sccc.dd.export.service.impl.InvokeExportServiceImpl.buildReader(InvokeExportServiceImpl.java:308) 
at com.ddd.sccc.dd.export.service.impl.InvokeExportServiceImpl.writeToExcel(InvokeExportServiceImpl.java:164) 
at com.ddd.sccc.dd.export.service.impl.InvokeExportServiceImpl.invokeExport(InvokeExportServiceImpl.java:119) 
at com.ddd.sccc.dd.export..task.ExportExcelTask.exoprt(ExportExcelTask.java:62) 
at sun.reflect.GeneratedMethodAccessor146.invoke(Unknown Source) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
at java.lang.reflect.Method.invoke(Method.java:498) 
at com.ddd.sccc.dd.quartz.util.JobInvokeUtil.invokeMethod(JobInvokeUtil.java:54) 
at com.ddd.sccc.dd.quartz.util.JobInvokeUtil.invokeMethod(JobInvokeUtil.java:32) 
at com.ddd.sccc.dd.quartz.util.QuartzDisallowConcurrentExecution.doExecute(QuartzDisallowConcurrentExecution.java:19) 
at com.ddd.sccc.dd.quartz.util.AbstractQuartzJob.execute(AbstractQuartzJob.java:44) 
at org.quartz.core.JobRunShell.run(JobRunShell.java:202) 
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)

Caused by: ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, code: 1002, host: xxx.xx.xx.x, port: 8123; Code: 62. DB::Exception: Syntax error: failed at position 2190 ('('): ( -- 筛选条件 将与每一条维度数据取交集 with base as ( select dlob.ids ids from dc_live_or. Unmatched parentheses: (. (SYNTAX_ERROR) (version 21.11.4.14 (official build)) 

at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.getException(ClickHouseExceptionSpecifier.java:92) 
at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:56) 
at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:29) 
at ru.yandex.clickhouse.ClickHouseStatementImpl.checkForErrorAndThrow(ClickHouseStatementImpl.java:1094) 
at ru.yandex.clickhouse.ClickHouseStatementImpl.getInputStream(ClickHouseStatementImpl.java:773) 
at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQueryStatement(ClickHouseStatementImpl.java:271) 
at ru.yandex.clickhouse.ClickHousePreparedStatementImpl.execute(ClickHousePreparedStatementImpl.java:138) 
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3461) 
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) 
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459) 
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167) 
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497) 
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) 
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) 
at com.baomidou.mybatisplus.core.executor.MybatisReuseExecutor.doQuery(MybatisReuseExecutor.java:65) 
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) 
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) 
at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:165) 
at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:111) 
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) 
at com.sun.proxy.$Proxy219.query(Unknown Source) 
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) 
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) 
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76) 
at sun.reflect.GeneratedMethodAccessor180.invoke(Unknown Source) 
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
at java.lang.reflect.Method.invoke(Method.java:498) 
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) 
... 38 more

Caused by: java.lang.Throwable: Code: 62. DB::Exception: Syntax error: failed at position 2190 ('('): ( -- 筛选条件 将与每一条维度数据取交集 with base as ( select dlob.ids ids from dc_live_or. Unmatched parentheses: (. (SYNTAX_ERROR) (version 21.11.4.14 (official build)) 

at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:54) 
... 64 more

2022-06-27 16:20:30.040 --- [eduler_Worker-7] : 创建cursorItemReader 失败