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
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