search

kaijianding / clickhouse-calcite-optimizer

use calcite to optimize clickhouse sql
5 stars 1 forks source link

执行报错，能解释下原因吗或者怎么解决 #2

Open Darby0551 opened 7 months ago

Darby0551 commented 7 months ago

SELECT countIf(_level >= 1) AS _1 , if(_1 = 0, -1, round(_2 / _1 100, 3)) AS _1_TO_2_RATE , medianExactIf(_x[2].1 - _x[1].1, _level >= 2) AS median1, countIf(_level >= 2) AS _2 , if(_2 = 0, -1, round(_3 / _2 100, 3)) AS _2_TO_3_RATE , medianExactIf(_x[3].1 - _x[2].1, _level >= 3) AS median2, countIf(_level >= 3) AS _3, '1' isTotal, count(distinct_id) as studyTotal, if(studyTotal = 0, -1, round(_3 / studyTotal 100, 3)) AS studyTotalRate FROM ( SELECT distinct_id, ifNull(toString(toDate(_x[1].2)), '') AS studyDate, length(_x) AS _level, _x FROM ( SELECT distinct_id, arrayJoin(xFunnel(53239 , 2 , '')((toUnixTimestamp(time) , date), event = 'loan_list_page_view' , event = '' , event = '' )) AS _x FROM zhurong.events_mock where 1 = 1 AND (time >= '2023-11-01 08:00:00' AND time <= '2023-11-30 08:00:00') AND (event IN ('loan_list_page_view' , '' , '' )) GROUP BY distinct_id ) )SELECT countIf(_level >= 1) AS _1 , if(_1 = 0, -1, round(_2 / _1 100, 3)) AS _1_TO_2_RATE , medianExactIf(_x[2].1 - _x[1].1, _level >= 2) AS median1, countIf(_level >= 2) AS _2 , if(_2 = 0, -1, round(_3 / _2 100, 3)) AS _2_TO_3_RATE , medianExactIf(_x[3].1 - _x[2].1, _level >= 3) AS median2, countIf(_level >= 3) AS _3, '1' isTotal, count(distinct_id) as studyTotal, if(studyTotal = 0, -1, round(_3 / studyTotal 100, 3)) AS studyTotalRate FROM ( SELECT distinct_id, ifNull(toString(toDate(_x[1].2)), '') AS studyDate, length(_x) AS _level, _x FROM ( SELECT distinct_id, arrayJoin(xFunnel(53239 , 2 , '')((toUnixTimestamp(time) , date), event = 'loan_list_page_view' , event = '' , event = '' )) AS _x FROM zhurong.events_mock where 1 = 1 AND (time >= '2023-11-01 08:00:00' AND time <= '2023-11-30 08:00:00') AND (event IN ('loan_list_page_view' , '' , '' )) GROUP BY distinct_id ) )

org.apache.calcite.sql.parser.SqlParseException: Encountered ".1" at line 7, column 21. Was expecting one of: "EXCEPT" ... "FETCH" ... "INTERSECT" ... "LIMIT" ... "OFFSET" ... "ORDER" ... "MINUS" ... "UNION" ... ")" ... "," ... "." ... "NOT" ... "IN" ... "<" ... "<=" ... ">" ... ">=" ... "=" ... "<>" ... "!=" ... "BETWEEN" ... "LIKE" ... "SIMILAR" ... "+" ... "-" ... "*" ... "/" ... "%" ... "||" ... "AND" ... "OR" ... "IS" ... "MEMBER" ... "SUBMULTISET" ... "CONTAINS" ... "OVERLAPS" ... "EQUALS" ... "PRECEDES" ... "SUCCEEDS" ... "IMMEDIATELY" ... "MULTISET" ... "[" ... "FORMAT" ...

at org.apache.calcite.sql.parser.impl.SqlParserImpl.convertException(SqlParserImpl.java:388)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.normalizeException(SqlParserImpl.java:151)
at org.apache.calcite.sql.parser.SqlParser.handleException(SqlParser.java:140)
at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:155)
at org.apache.calcite.sql.parser.SqlParser.parseStmt(SqlParser.java:180)
at org.apache.calcite.prepare.PlannerImpl.parse(PlannerImpl.java:206)
at org.apache.calcite.tools.Planner.parse(Planner.java:50)
at org.apache.calcite.clickhouse.ClickhouseSqlOptimizer.optimize(ClickhouseSqlOptimizer.java:79)
at TestOptimize.test(TestOptimize.java:26)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

Caused by: org.apache.calcite.sql.parser.impl.ParseException: Encountered ".1" at line 7, column 21. Was expecting one of: "EXCEPT" ... "FETCH" ... "INTERSECT" ... "LIMIT" ... "OFFSET" ... "ORDER" ... "MINUS" ... "UNION" ... ")" ... "," ... "." ... "NOT" ... "IN" ... "<" ... "<=" ... ">" ... ">=" ... "=" ... "<>" ... "!=" ... "BETWEEN" ... "LIKE" ... "SIMILAR" ... "+" ... "-" ... "*" ... "/" ... "%" ... "||" ... "AND" ... "OR" ... "IS" ... "MEMBER" ... "SUBMULTISET" ... "CONTAINS" ... "OVERLAPS" ... "EQUALS" ... "PRECEDES" ... "SUCCEEDS" ... "IMMEDIATELY" ... "MULTISET" ... "[" ... "FORMAT" ...

at org.apache.calcite.sql.parser.impl.SqlParserImpl.generateParseException(SqlParserImpl.java:35588)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.jj_consume_token(SqlParserImpl.java:35402)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.FunctionParameterList(SqlParserImpl.java:1752)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.NamedFunctionCall(SqlParserImpl.java:25053)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.AtomicRowExpression(SqlParserImpl.java:16296)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression3(SqlParserImpl.java:16031)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression2b(SqlParserImpl.java:15701)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression2(SqlParserImpl.java:15742)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.Expression(SqlParserImpl.java:15673)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.SelectExpression(SqlParserImpl.java:8786)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.SelectItem(SqlParserImpl.java:7662)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.SelectList(SqlParserImpl.java:7649)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlSelect(SqlParserImpl.java:4325)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQuery(SqlParserImpl.java:630)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.LeafQueryOrExpr(SqlParserImpl.java:15656)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.QueryOrExpr(SqlParserImpl.java:15118)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.OrderedQueryOrExpr(SqlParserImpl.java:504)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmt(SqlParserImpl.java:3700)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmtEof(SqlParserImpl.java:3738)
at org.apache.calcite.sql.parser.impl.SqlParserImpl.parseSqlStmtEof(SqlParserImpl.java:199)
at org.apache.calcite.sql.parser.SqlParser.parseQuery(SqlParser.java:153)
... 28 more
kaijianding commented 6 months ago

猜测是medianExactIf这个方法需要被加入到ClickhouseAggregators这个类里，你改下ClickhouseAggregators试一下。 如果还不行，那麻烦你把建表语句贴一下，我来调试下。