clickhouse-java icon indicating copy to clipboard operation
clickhouse-java copied to clipboard

Published 20 hours ago • verified publisher icon ClickHouse

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

Open kiwimg opened this issue 2 years ago • 4 comments

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

kiwimg avatar Jun 27 '22 08:06 kiwimg

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.

zhicwu avatar Jun 27 '22 13:06 zhicwu

中文需要使用 unicode comments？

kiwimg avatar Jun 28 '22 01:06 kiwimg

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?

zhicwu avatar Jun 28 '22 04:06 zhicwu

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 失败

kiwimg avatar Jun 28 '22 05:06 kiwimg