0

I try to get the seq in the rank list by SQL, It works in MySQL, but when I copy into mybatis java file, It appears some exception like these.

This is my java file

@Select("<script>select * " +
            "from (select a.*, (@rank:=@rank + 1) As seq " +
            "      from (select user_id, rank_value as rankValue " +
            "            from t_stat_rank_day " +
            "            where rank_type = #{rankTypeCode} " +
            "            and rank_day = #{date} " +
            "            order by rank_value desc, update_time  limit #{maxSeq}) as a, " +
            "           (SELECT @rank:=#{startSeq}) r) b " +
            "where user_id = #{userId} </script>")
    StatRank seq(@Param("rankTypeCode") Integer rankTypeCode, @Param("date") LocalDate date, @Param("userId") Long userId, @Param("maxSeq") Integer maxSeq, @Param("startSeq") Integer startSeq);

and mybatis throw a exception as follow


line 1:33 no viable alternative at input '(@rank:'
line 1:33 mismatched input ':' expecting {')', ',', INTO, UNION, FROM, WHERE, FOR, ORDER, GROUP, HAVING, LIMIT, WINDOW, LOCK}
### Error querying database.  Cause: java.lang.IndexOutOfBoundsException: Index: 0
### The error may exist in com/hoho/service/modules/stat/dao/StatRankDayMapper.java (best guess)
### The error may involve com.hoho.service.modules.stat.dao.StatRankDayMapper.seq-Inline
### The error occurred while setting parameters
### SQL: select * from (select a.*, (@rank:=@rank + 1) As seq       from (select user_id, rank_value as rankValue             from t_stat_rank_day             where rank_type = ?             and rank_day = ?             order by rank_value desc, update_time  limit ?) as a,            (SELECT @rank:=?) r) b where user_id = ?
### Cause: java.lang.IndexOutOfBoundsException: Index: 0
    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
    at jdk.internal.reflect.GeneratedMethodAccessor303.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:567)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
    ... 119 common frames omitted
Caused by: java.lang.IndexOutOfBoundsException: Index: 0
    at java.base/java.util.Collections$EmptyList.get(Collections.java:4483)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitExpr(MySQLVisitor.java:264)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitExpr(MySQLVisitor.java:121)
    at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$ExprContext.accept(MySQLStatementParser.java:11241)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.createProjection(MySQLDMLVisitor.java:441)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitProjection(MySQLDMLVisitor.java:429)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitProjection(MySQLDMLVisitor.java:127)
    at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$ProjectionContext.accept(MySQLStatementParser.java:5370)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitProjections(MySQLDMLVisitor.java:405)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitProjections(MySQLDMLVisitor.java:127)
    at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$ProjectionsContext.accept(MySQLStatementParser.java:4865)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitSelectClause(MySQLDMLVisitor.java:324)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitSelectClause(MySQLDMLVisitor.java:127)
    at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$SelectClauseContext.accept(MySQLStatementParser.java:4551)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitUnionClause(MySQLDMLVisitor.java:317)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitUnionClause(MySQLDMLVisitor.java:127)
    at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$UnionClauseContext.accept(MySQLStatementParser.java:4446)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitSubquery(MySQLDMLVisitor.java:630)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitSubquery(MySQLDMLVisitor.java:127)
    at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$SubqueryContext.accept(MySQLStatementParser.java:7437)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitTableFactor(MySQLDMLVisitor.java:514)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitTableFactor(MySQLDMLVisitor.java:127)
    at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$TableFactorContext.accept(MySQLStatementParser.java:6349)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitTableReference(MySQLDMLVisitor.java:498)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitTableReference(MySQLDMLVisitor.java:127)
    at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$TableReferenceContext.accept(MySQLStatementParser.java:6277)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitEscapedTableReference(MySQLDMLVisitor.java:491)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitEscapedTableReference(MySQLDMLVisitor.java:127)
    at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$EscapedTableReferenceContext.accept(MySQLStatementParser.java:5947)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitTableReferences(MySQLDMLVisitor.java:484)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitTableReferences(MySQLDMLVisitor.java:127)
    at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$TableReferencesContext.accept(MySQLStatementParser.java:5891)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitFromClause(MySQLDMLVisitor.java:476)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitFromClause(MySQLDMLVisitor.java:127)
    at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$FromClauseContext.accept(MySQLStatementParser.java:5846)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitSelectClause(MySQLDMLVisitor.java:329)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitSelectClause(MySQLDMLVisitor.java:127)
    at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$SelectClauseContext.accept(MySQLStatementParser.java:4551)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitUnionClause(MySQLDMLVisitor.java:317)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitUnionClause(MySQLDMLVisitor.java:127)
    at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$UnionClauseContext.accept(MySQLStatementParser.java:4446)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitSelect(MySQLDMLVisitor.java:309)
    at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitSelect(MySQLDMLVisitor.java:127)
    at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$SelectContext.accept(MySQLStatementParser.java:3098)
    at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
    at org.apache.shardingsphere.sql.parser.SQLParserEngine.parse0(SQLParserEngine.java:80)
    at org.apache.shardingsphere.sql.parser.SQLParserEngine.parse(SQLParserEngine.java:61)
    at org.apache.shardingsphere.underlying.route.DataNodeRouter.createRouteContext(DataNodeRouter.java:97)
    at org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:89)
    at org.apache.shardingsphere.underlying.route.DataNodeRouter.route(DataNodeRouter.java:76)
    at org.apache.shardingsphere.underlying.pluggble.prepare.PreparedQueryPrepareEngine.route(PreparedQueryPrepareEngine.java:54)
    at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.executeRoute(BasePrepareEngine.java:96)
    at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.prepare(BasePrepareEngine.java:83)
    at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.prepare(ShardingPreparedStatement.java:183)
    at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:143)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
    at jdk.internal.reflect.GeneratedMethodAccessor261.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:567)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
    at com.sun.proxy.$Proxy305.query(Unknown Source)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
    ... 125 common frames omitted

the sql

SELECT @rank:=#{startSeq}) r

Because it miss the key word like 'from'. This is sql is not complete at the mybatis angel.

What can I write to avoid this situation ?

Linuxea
  • 319
  • 1
  • 4
  • 15
  • This would be a ShardingSphere issue. – ave Oct 15 '20 at 18:11
  • I think you are right. So excuse me What can I do except row_number and the wrong way beflow to get the rank in mybatis ? – Linuxea Oct 16 '20 at 00:03
  • There is no MyBatis specific solution. I know nothing about ShardingSphere, but for MySQL 8.x, [ROW_NUMBER](https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number) may be the best way to achieve it in SQL. See the answers of this [question](https://stackoverflow.com/q/1895110/1261766) for alternatives. Or, you can assign rank numbers in Java code after getting the query result. – ave Oct 16 '20 at 05:08
  • https://github.com/apache/shardingsphere/issues/7803 – Linuxea Oct 19 '20 at 05:54

0 Answers0