You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/10/16 01:56:50 UTC
[GitHub] [shardingsphere] Linuxea opened a new issue #7803: use mysql ROW_NUMBER function error
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:
users@infra.apache.org
[GitHub] [shardingsphere] zhongjiajie commented on issue #7803: use mysql ROW_NUMBER function error
Posted by GitBox <gi...@apache.org>.
zhongjiajie commented on issue #7803:
URL: https://github.com/apache/shardingsphere/issues/7803#issuecomment-766879133
> @Linuxea
> Window function&CTE&variables are not supported in 4.x.
@kimmking Does is support in 5.X-alpha?
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] Linuxea closed issue #7803: use mysql ROW_NUMBER function error
Posted by GitBox <gi...@apache.org>.
Linuxea closed issue #7803:
URL: https://github.com/apache/shardingsphere/issues/7803
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #7803: use mysql ROW_NUMBER function error
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #7803:
URL: https://github.com/apache/shardingsphere/issues/7803#issuecomment-709691533
@Linuxea Are you using SQLServer?
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] Linuxea commented on issue #7803: use mysql ROW_NUMBER function error
Posted by GitBox <gi...@apache.org>.
Linuxea commented on issue #7803:
URL: https://github.com/apache/shardingsphere/issues/7803#issuecomment-711614535
@kimmking thanks
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] Linuxea commented on issue #7803: use mysql ROW_NUMBER function error
Posted by GitBox <gi...@apache.org>.
Linuxea commented on issue #7803:
URL: https://github.com/apache/shardingsphere/issues/7803#issuecomment-709688123
```
<sharding-jdbc.version>4.1.0</sharding-jdbc.version>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding-jdbc.version}</version>
</dependency>
```
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu removed a comment on issue #7803: use mysql ROW_NUMBER function error
Posted by GitBox <gi...@apache.org>.
jingshanglu removed a comment on issue #7803:
URL: https://github.com/apache/shardingsphere/issues/7803#issuecomment-709691533
@Linuxea Are you using SQLServer?
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #7803: use mysql ROW_NUMBER function error
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #7803:
URL: https://github.com/apache/shardingsphere/issues/7803#issuecomment-709685439
@Linuxea Which version of ShardingSphere did you use?
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] kimmking commented on issue #7803: use mysql ROW_NUMBER function error
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #7803:
URL: https://github.com/apache/shardingsphere/issues/7803#issuecomment-709775058
@Linuxea
Window function&CTE&variables are not supported in 4.x.
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] Linuxea edited a comment on issue #7803: use mysql ROW_NUMBER function error
Posted by GitBox <gi...@apache.org>.
Linuxea edited a comment on issue #7803:
URL: https://github.com/apache/shardingsphere/issues/7803#issuecomment-709688123
@jingshanglu
```
<sharding-jdbc.version>4.1.0</sharding-jdbc.version>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding-jdbc.version}</version>
</dependency>
```
----------------------------------------------------------------
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:
users@infra.apache.org
[GitHub] [shardingsphere] zhongjiajie commented on issue #7803: use mysql ROW_NUMBER function error
Posted by GitBox <gi...@apache.org>.
zhongjiajie commented on issue #7803:
URL: https://github.com/apache/shardingsphere/issues/7803#issuecomment-766879133
> @Linuxea
> Window function&CTE&variables are not supported in 4.x.
@kimmking Does is support in 5.X-alpha?
----------------------------------------------------------------
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:
users@infra.apache.org