Linuxea opened a new issue #7803:
URL: https://github.com/apache/shardingsphere/issues/7803
## Question
I use mybatis and shardingsphere in my project.
This is my sql
```sql
@Select("<script> select * " +
"from (select user_id as userId, rank_value as rankValue,
(select\n row_number() over (order by rank_value desc)) as seq " +
" from t_stat_rank_day " +
" where rank_type = #{rankTypeCode} " +
" and rank_day = #{date} " +
" order by rank_value desc, update_time " +
" limit #{maxSeq}) as go " +
"where go.userId = #{userId} </script>")
StatRank seq(@Param("rankTypeCode") Integer rankTypeCode, @Param("date")
LocalDate date, @Param("userId") Long userId, @Param("maxSeq") Integer maxSeq);
```
But something error happens:
```java
line 2:14 no viable alternative at input '(selectrow_number()over'
line 2:14 no viable alternative at input '(selectrow_number()over'
line 2:11 extraneous input '(' expecting ')'
line 2:14 mismatched input 'over' expecting {TRUNCATE, POSITION, VIEW, AS,
ANY, OFFSET, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, BOOLEAN, DATE, TIME,
TIMESTAMP, YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND,
MAX, MIN, SUM, COUNT, AVG, CURRENT, ENABLE, DISABLE, INSTANCE, DO, DEFINER,
CASCADED, LOCAL, CLOSE, OPEN, NEXT, NAME, TYPE, TABLES, TABLESPACE, COLUMNS,
FIELDS, INDEXES, STATUS, MODIFY, VALUE, DUPLICATE, FIRST, LAST, AFTER, OJ,
ACCOUNT, USER, ROLE, START, TRANSACTION, WITHOUT, ESCAPE, SUBPARTITION,
STORAGE, SUPER, TEMPORARY, THAN, UNBOUNDED, SIGNED, UPGRADE, VALIDATION,
ROLLUP, SOUNDS, UNKNOWN, OFF, ALWAYS, COMMITTED, LEVEL, NO, PASSWORD,
PRIVILEGES, ACTION, ALGORITHM, AUTOCOMMIT, BTREE, CHAIN, CHARSET, CHECKSUM,
CIPHER, CLIENT, COALESCE, COMMENT, COMPACT, COMPRESSED, COMPRESSION,
CONNECTION, CONSISTENT, DATA, DISCARD, DISK, ENCRYPTION, END, ENGINE, EVENT,
EXCHANGE, EXECUTE, FILE, FIXED, FOLLOWING, GLOBAL, HASH, IMPORT_, LESS, MEMORY,
NONE, PARSER, PARTIAL, PAR
TITIONING, PERSIST, PRECEDING, PROCESS, PROXY, QUICK, REBUILD, REDUNDANT,
RELOAD, REMOVE, REORGANIZE, REPAIR, REVERSE, SESSION, SHUTDOWN, SIMPLE, SLAVE,
VISIBLE, INVISIBLE, ENFORCED, AGAINST, LANGUAGE, MODE, QUERY, EXTENDED,
EXPANSION, VARIANCE, MAX_ROWS, MIN_ROWS, SQL_BIG_RESULT, SQL_BUFFER_RESULT,
SQL_CACHE, SQL_NO_CACHE, STATS_AUTO_RECALC, STATS_PERSISTENT,
STATS_SAMPLE_PAGES, ROW_FORMAT, WEIGHT_STRING, COLUMN_FORMAT, INSERT_METHOD,
KEY_BLOCK_SIZE, PACK_KEYS, PERSIST_ONLY, BIT_AND, BIT_OR, BIT_XOR,
GROUP_CONCAT, JSON_ARRAYAGG, JSON_OBJECTAGG, STD, STDDEV, STDDEV_POP,
STDDEV_SAMP, VAR_POP, VAR_SAMP, AUTO_INCREMENT, AVG_ROW_LENGTH,
DELAY_KEY_WRITE, ROTATE, MASTER, BINLOG, ERROR, SCHEDULE, COMPLETION, EVERY,
HOST, SOCKET, PORT, SERVER, WRAPPER, OPTIONS, OWNER, RETURNS, CONTAINS,
SECURITY, INVOKER, TEMPTABLE, MERGE, UNDEFINED, DATAFILE, FILE_BLOCK_SIZE,
EXTENT_SIZE, INITIAL_SIZE, AUTOEXTEND_SIZE, MAX_SIZE, NODEGROUP, WAIT, LOGFILE,
UNDOFILE, UNDO_BUFFER_SIZE, REDO_BUFFER_SIZE, HANDLE
R, PREV, ORGANIZATION, DEFINITION, DESCRIPTION, REFERENCE, FOLLOWS, PRECEDES,
IMPORT, CONCURRENT, XML, DUMPFILE, SHARE, CODE, CONTEXT, SOURCE, CHANNEL,
CLONE, AGGREGATE, INSTALL, COMPONENT, UNINSTALL, RESOURCE, EXPIRE, NEVER,
HISTORY, OPTIONAL, REUSE, MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR,
MAX_CONNECTIONS_PER_HOUR, MAX_USER_CONNECTIONS, RETAIN, RANDOM, OLD, ISSUER,
SUBJECT, CACHE, GENERAL, SLOW, USER_RESOURCES, EXPORT, RELAY, HOSTS, FLUSH,
RESET, RESTART, IO_THREAD, SQL_THREAD, SQL_BEFORE_GTIDS, SQL_AFTER_GTIDS,
MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE, RELAY_LOG_POS,
SQL_AFTER_MTS_GAPS, UNTIL, DEFAULT_AUTH, PLUGIN_DIR, STOP, IDENTIFIER_, STRING_}
```
I want to get the rank by using <code>row_number</code>, even if I use
another way for example
```sql
select *
from (select a.*, @rank := @rank + 1 As seq
from (select @rank := 0, user_id, rank_value as rankValue
from hoho_trade.t_stat_rank_day
where rank_type = 1
and rank_day = '2020-10-15'
order by rank_value desc, update_time
limit 50) as a
) b
where user_id = 1594371406316000000;
```
That appears the same mismatch error.
What can I do?
Thanks sincerely!
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
