Oracle分页rowno的问题

[onlymyme]

不知道有没有人提过这个问题，没细看issue
Oracle的分页 路由到各个数据库执行的SQL语句在rownum的值错误，
测试环境：Oracle分库（ds_0、ds_1、ds_2）
我在ParsingSQLRouter#router(String, List, SQLStatement)里面打印了rewriteEngine.generateSQL()生成的SQL语句

1.这种情况下没有问题

输入SQL : SELECT * FROM ( SELECT ROWNUM AS rowno, T.* FROM t_person T WHERE age=23 AND ROWNUM <= 20) table_alias WHERE table_alias.rowno >= 1

输出ds_2 : SELECT * FROM ( SELECT ROWNUM AS rowno, T.* FROM t_person T WHERE age=23 AND ROWNUM <= 20) table_alias WHERE table_alias.rowno >= 0
输出ds_1 : SELECT * FROM ( SELECT ROWNUM AS rowno, T.* FROM t_person T WHERE age=23 AND ROWNUM <= 20) table_alias WHERE table_alias.rowno >= 0
输出ds_0 : SELECT * FROM ( SELECT ROWNUM AS rowno, T.* FROM t_person T WHERE age=23 AND ROWNUM <= 20) table_alias WHERE table_alias.rowno >= 0

2.调转一下where后面跟着的条件顺序，会出现rownum由20变成220

输入SQL : SELECT * FROM ( SELECT ROWNUM AS rowno, T.* FROM t_person T WHERE ROWNUM <= 20 AND age=23) table_alias WHERE table_alias.rowno >= 1

输出ds_2 : SELECT * FROM ( SELECT ROWNUM AS rowno, T.* FROM t_person T WHERE ROWNUM <= 220AND age=23) table_alias WHERE table_alias.rowno >= 0
输出ds_1 : SELECT * FROM ( SELECT ROWNUM AS rowno, T.* FROM t_person T WHERE ROWNUM <= 220AND age=23) table_alias WHERE table_alias.rowno >= 0
输出ds_0 : SELECT * FROM ( SELECT ROWNUM AS rowno, T.* FROM t_person T WHERE ROWNUM <= 220AND age=23) table_alias WHERE table_alias.rowno >= 0

3.在1的情况下增加order by，会出现2一样的情况，rownum由20变成220

输入SQL: SELECT * FROM ( SELECT ROWNUM AS rowno, T.* FROM t_person T WHERE age=23 AND ROWNUM <= 20 ORDER BY account) table_alias WHERE table_alias.rowno >= 1

输出ds_2 : SELECT * FROM ( SELECT ROWNUM AS rowno, T.* , account AS ORDER_BY_DERIVED_0 FROM t_person T WHERE age=23 AND ROWNUM <= 220ORDER BY account) table_alias WHERE table_alias.rowno >= 0
输出ds_1 : SELECT * FROM ( SELECT ROWNUM AS rowno, T.* , account AS ORDER_BY_DERIVED_0 FROM t_person T WHERE age=23 AND ROWNUM <= 220ORDER BY account) table_alias WHERE table_alias.rowno >= 0
输出ds_0 : SELECT * FROM ( SELECT ROWNUM AS rowno, T.* , account AS ORDER_BY_DERIVED_0 FROM t_person T WHERE age=23 AND ROWNUM <= 220ORDER BY account) table_alias WHERE table_alias.rowno >= 0

看上去应该是rownum如果有跟着其它的什么条件就会出现rownum变大的情况

[haocao]

已在测试用例里面增加相关SQL测试，暂时未发现问题，能否在github上建立一个可以重现问题的demo？请使用https://github.com/shardingjdbc/sharding-jdbc-example作为基准，谢谢。

[onlymyme]

sharding-jdbc-example-2.0.2.zip

已经上传测试例子，测试例子为sharding-jdbc-raw-jdbc-java-example里面的io.shardingjdbc.example.jdbc.java.OracleTest

[tuohai666]

The SQL can be simplified to 'SELECT * FROM t_order WHERE ROWNUM <= 20 AND order_id=23;'.
The root cause of this bug is wrong RowCountToken beginPosition.