Description:
I executed a range query with debug mode and it returned correct result. I exexuted a equal condition query after it, and returned just one row result, it should return 20 rows.
mysql> set session debug="d,compare_end_range";
Query OK, 0 rows affected (0.07 sec)
mysql> select * from t_orderby4 where id1>1 and id1<5;
+-----+------+----------+------+------+---------------------------------------+
| id1 | id2 | id3 | id4 | id5 | id6 |
+-----+------+----------+------+------+---------------------------------------+
| 2 | 301 | abc15002 | 1002 | 1102 | xyzbhdfwejfwefgdhdskjghfsdddwfgh15302 |
| 2 | 301 | abc2 | 2 | 102 | xyzbhdfwejfwefgdhdskjghfsdddwfgh302 |
| 2 | 301 | abc30002 | 2002 | 2102 | xyzbhdfwejfwefgdhdskjghfsdddwfgh30302 |
| 2 | 301 | abc45002 | 3002 | 3102 | xyzbhdfwejfwefgdhdskjghfsdddwfgh45302 |
1 ......................................................................... |
| 4 | 2303 | abc80004 | 3004 | 5437 | xyzbhdfwejfwefgdhdskjghfsdddwfgh80304 |
| 4 | 2303 | abc95004 | 4004 | 6437 | xyzbhdfwejfwefgdhdskjghfsdddwfgh95304 |
+-----+------+----------+------+------+---------------------------------------+
60 rows in set (2.65 sec)
Then, I execute a equal condition query as followers.
mysql> select * from t_orderby4 where id1 = 1 for update;
+-----+-----+------+------+------+-------------------------------------+
| id1 | id2 | id3 | id4 | id5 | id6 |
+-----+-----+------+------+------+-------------------------------------+
| 1 | 300 | abc1 | 1 | 101 | xyzbhdfwejfwefgdhdskjghfsdddwfgh301 |
+-----+-----+------+------+------+-------------------------------------+
1 row in set (0.00 sec)
In fact, t_orderby4 has 20 rows for t_orderby4.id1 = 1, but it only return one row as above.
mysql> select count(*) from t_orderby4 where id1 = 1 ;
+----------+
| count(*) |
+----------+
| 20 |
+----------+
1 row in set (0.00 sec)
How to repeat:
1、create table
CREATE TABLE `t_orderby4` (
`id1` int(11) NOT NULL,
`id2` int(11) NOT NULL,
`id3` varchar(64) COLLATE utf8_bin NOT NULL,
`id4` int(11) DEFAULT NULL,
`id5` int(11) DEFAULT NULL,
`id6` varchar(64) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id1`,`id2`,`id3`),
KEY `id4` (`id4`,`id5`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
2、produce the data and load it to table t_orderby4.
(1)make a shell script with codes as followers.
script name : t_orderby4.sh
full codes:
[ -f t_orderby4.csv ] && rm -rf t_orderby4.csv
i=0
j=300
var0="xyzbhdfwejfwefgdhdskjghfsdddwfgh"
func()
{
i=0
while((i++<100000))
do
echo $((i%5000)),$((j++%3000)),"abc$i",$((i%7000)),$(($i/15+j%200)),"$var0$j" >> t_orderby4.csv
done
}
func
(2)execute the script and produce data
sh t_orderby4.sh
(3)load data to table t_orderby4.
load data infile 't_orderby4.csv' into table t_orderby4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
3、execute statements as followeres on debug mode mysqld process.
mysql> set session debug="d,compare_end_range";
mysql> select * from t_orderby4 where id1>1 and id1<5;
mysql> select * from t_orderby4 where id1 = 1 for update;
+-----+-----+------+------+------+-------------------------------------+
| id1 | id2 | id3 | id4 | id5 | id6 |
+-----+-----+------+------+------+-------------------------------------+
| 1 | 300 | abc1 | 1 | 101 | xyzbhdfwejfwefgdhdskjghfsdddwfgh301 |
+-----+-----+------+------+------+-------------------------------------+
1 row in set (0.00 sec)
mysql> select count(*) from t_orderby4 where id1 = 1 ;
+----------+
| count(*) |
+----------+
| 20 |
+----------+
1 row in set (0.00 sec)
Suggested fix:
This bug is caused because the range query set prebuild->m_end_range = true in some cases, but it doesn't reset prebuild->m_end_range = false after the query ends. It should reset prebuild->m_end_range to false in ha_innodb::end_stmt function.
int
ha_innobase::end_stmt()
{
// ...................
m_prebuilt->m_end_range = false;
// ...................
}
Description: I executed a range query with debug mode and it returned correct result. I exexuted a equal condition query after it, and returned just one row result, it should return 20 rows. mysql> set session debug="d,compare_end_range"; Query OK, 0 rows affected (0.07 sec) mysql> select * from t_orderby4 where id1>1 and id1<5; +-----+------+----------+------+------+---------------------------------------+ | id1 | id2 | id3 | id4 | id5 | id6 | +-----+------+----------+------+------+---------------------------------------+ | 2 | 301 | abc15002 | 1002 | 1102 | xyzbhdfwejfwefgdhdskjghfsdddwfgh15302 | | 2 | 301 | abc2 | 2 | 102 | xyzbhdfwejfwefgdhdskjghfsdddwfgh302 | | 2 | 301 | abc30002 | 2002 | 2102 | xyzbhdfwejfwefgdhdskjghfsdddwfgh30302 | | 2 | 301 | abc45002 | 3002 | 3102 | xyzbhdfwejfwefgdhdskjghfsdddwfgh45302 | 1 ......................................................................... | | 4 | 2303 | abc80004 | 3004 | 5437 | xyzbhdfwejfwefgdhdskjghfsdddwfgh80304 | | 4 | 2303 | abc95004 | 4004 | 6437 | xyzbhdfwejfwefgdhdskjghfsdddwfgh95304 | +-----+------+----------+------+------+---------------------------------------+ 60 rows in set (2.65 sec) Then, I execute a equal condition query as followers. mysql> select * from t_orderby4 where id1 = 1 for update; +-----+-----+------+------+------+-------------------------------------+ | id1 | id2 | id3 | id4 | id5 | id6 | +-----+-----+------+------+------+-------------------------------------+ | 1 | 300 | abc1 | 1 | 101 | xyzbhdfwejfwefgdhdskjghfsdddwfgh301 | +-----+-----+------+------+------+-------------------------------------+ 1 row in set (0.00 sec) In fact, t_orderby4 has 20 rows for t_orderby4.id1 = 1, but it only return one row as above. mysql> select count(*) from t_orderby4 where id1 = 1 ; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) How to repeat: 1、create table CREATE TABLE `t_orderby4` ( `id1` int(11) NOT NULL, `id2` int(11) NOT NULL, `id3` varchar(64) COLLATE utf8_bin NOT NULL, `id4` int(11) DEFAULT NULL, `id5` int(11) DEFAULT NULL, `id6` varchar(64) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id1`,`id2`,`id3`), KEY `id4` (`id4`,`id5`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 2、produce the data and load it to table t_orderby4. (1)make a shell script with codes as followers. script name : t_orderby4.sh full codes: [ -f t_orderby4.csv ] && rm -rf t_orderby4.csv i=0 j=300 var0="xyzbhdfwejfwefgdhdskjghfsdddwfgh" func() { i=0 while((i++<100000)) do echo $((i%5000)),$((j++%3000)),"abc$i",$((i%7000)),$(($i/15+j%200)),"$var0$j" >> t_orderby4.csv done } func (2)execute the script and produce data sh t_orderby4.sh (3)load data to table t_orderby4. load data infile 't_orderby4.csv' into table t_orderby4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; 3、execute statements as followeres on debug mode mysqld process. mysql> set session debug="d,compare_end_range"; mysql> select * from t_orderby4 where id1>1 and id1<5; mysql> select * from t_orderby4 where id1 = 1 for update; +-----+-----+------+------+------+-------------------------------------+ | id1 | id2 | id3 | id4 | id5 | id6 | +-----+-----+------+------+------+-------------------------------------+ | 1 | 300 | abc1 | 1 | 101 | xyzbhdfwejfwefgdhdskjghfsdddwfgh301 | +-----+-----+------+------+------+-------------------------------------+ 1 row in set (0.00 sec) mysql> select count(*) from t_orderby4 where id1 = 1 ; +----------+ | count(*) | +----------+ | 20 | +----------+ 1 row in set (0.00 sec) Suggested fix: This bug is caused because the range query set prebuild->m_end_range = true in some cases, but it doesn't reset prebuild->m_end_range = false after the query ends. It should reset prebuild->m_end_range to false in ha_innodb::end_stmt function. int ha_innobase::end_stmt() { // ................... m_prebuilt->m_end_range = false; // ................... }