Bug #111304 The Query Rewrite Plugin mixes-up LIMIT and OFFSET.

Description:
Hi

(Using the Optimizer category as other bugs for the Rewrite Plugin are under this one, maybe there should be a Query Rewrite Plugin Category)

The Query Rewrite Plugin mixes-up LIMIT and OFFSET.  On a query with LIMIT 1 OFFSET 2, the plugin rewrites to LIMIT 2 OFFSET 1.  See How to repeat for details.

Note that if using the "LIMIT 2,1" syntax instead of "LIMIT 1 OFFSET 2" syntax, things work well, but this means the query to rewrite also needs to be using the "LIMIT 2,1" syntax, which makes impossible to rewrite a query using the "LIMIT 1 OFFSET 2" syntax.

Severity set as S2/Serious because this is an "incorrect query result".

Many thanks for looking into this,

Jean-François Gagné

How to repeat:
# Create a 8.0.33 sandbox.
dbdeployer deploy single mysql_8.0.33

# Load data in the sandbox.
./use <<< "create database test_jfg"
./use test_jfg <<< "create table t(id int primary key, v int)"
./use test_jfg <<< "insert into t values (1,1),(2,2),(3,3)"

# Load the rewrite plugin.
cat ~/opt/mysql/mysql_8.0.33/share/install_rewriter.sql | ./use

# The query below returns 1 row as expected (limit 1).
./use <<< "select id, v from test_jfg.t order by id limit 1 offset 2"
id      v
3       3

# Let's make above query return v+1 instead of v.
./use <<< "
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
VALUES('select id, v from test_jfg.t order by id limit ? offset ?', 'select id, v + 1 from test_jfg.t order by id limit ? offset ?');
CALL query_rewrite.flush_rewrite_rules()
"

# Now the query returns 2 rows, which is unexpected.
./use test_jfg <<< "select id, v from test_jfg.t order by id limit 1 offset 2"
id      v + 1
2       3
3       4

# And in mysql client, we can see that LIMIT and OFFSET has been swapped.
mysql [localhost:8033] {msandbox} ((none)) > select id, v from test_jfg.t order by id limit 1 offset 2;
+----+-------+
| id | v + 1 |
+----+-------+
|  2 |     3 |
|  3 |     4 |
+----+-------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost:8033] {msandbox} ((none)) > show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                  |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'select id, v from test_jfg.t order by id limit 1 offset 2' rewritten to 'select id, v + 1 from test_jfg.t order by id limit 2 offset 1' by a query rewrite plugin |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

---

# Same for 5.7.42.
dbdeployer deploy single mysql_5.7.42

./use <<< "create database test_jfg"
./use test_jfg <<< "create table t(id int primary key, v int)"
./use test_jfg <<< "insert into t values (1,1),(2,2),(3,3)"

cat ~/opt/mysql/mysql_5.7.42/share/install_rewriter.sql | ./use

./use <<< "select id, v from test_jfg.t order by id limit 1 offset 2"
id      v
3       3

./use <<< "
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
VALUES('select id, v from test_jfg.t order by id limit ? offset ?', 'select id, v + 1 from test_jfg.t order by id limit ? offset ?');
CALL query_rewrite.flush_rewrite_rules()
"

./use <<< "select id, v from test_jfg.t order by id limit 1 offset 2"
id      v + 1
2       3
3       4

mysql [localhost:5742] {msandbox} ((none)) > select id, v from test_jfg.t order by id limit 1 offset 2;
+----+-------+
| id | v + 1 |
+----+-------+
|  2 |     3 |
|  3 |     4 |
+----+-------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost:5742] {msandbox} ((none)) > show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                  |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1105 | Query 'select id, v from test_jfg.t order by id limit 1 offset 2' rewritten to 'select id, v + 1 from test_jfg.t order by id limit 2 offset 1' by a query rewrite plugin |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

---

# But the other syntax works for both 8.0.33 and 5.7.42.
./use <<< "select id, v from test_jfg.t order by id limit 2,1"
id      v
3       3

./use <<< "
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
VALUES('select id, v from test_jfg.t order by id limit ?,?', 'select id, v + 1 from test_jfg.t order by id limit ?,?');
CALL query_rewrite.flush_rewrite_rules()
"

./use <<< "select id, v from test_jfg.t order by id limit 2,1"
id      v + 1
3       4

Note that a work-around for this is to use below rewrite rule (using "limit ?, ?" instead of "limit ? offset ?" in the replacement), I am not sure it is satisfying.

./use <<< "
truncate query_rewrite.rewrite_rules;
INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
VALUES('select id, v from test_jfg.t order by id limit ? offset ?', 'select id, v + 1 from test_jfg.t order by id limit ?, ?');
CALL query_rewrite.flush_rewrite_rules()
"

./use <<< "select id, v from test_jfg.t order by id limit 1 offset 2"
id      v + 1
3       4

Hello Jean-François,

Thank you for the report and feedback.

regards,
Umesh