Description:
Hi team:
Two tables have the same data, and the table structure is as follows
CREATE TABLE test1 (
a bigint(20) unsigned NOT NULL AUTO_INCREMENT,
b decimal(38,0) NOT NULL,
PRIMARY KEY (a)
);
CREATE TABLE test2 (
a bigint(20) unsigned NOT NULL AUTO_INCREMENT,
b decimal(38,0) NOT NULL,
PRIMARY KEY (a) ,
key(b)
);
The following statements have different data outputs
SQL 1: use where filter
mysql> DESC select count(*) from test1 where b BETWEEN '568989202507171830001000' AND '568989202507171830001999';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test1 | NULL | ALL | NULL | NULL | NULL | NULL | 3000 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (1.89 sec)
mysql> select count(*) from test1 where b BETWEEN '568989202507171830001000' AND '568989202507171830001999';
+----------+
| count(*) |
+----------+
| 2000 |
+----------+
1 row in set (2.13 sec)
SQL 2: use index range search and position then return data.
mysql> select count(*) from test2 where b BETWEEN '568989202507171830001000' AND '568989202507171830001999';
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (9.76 sec)
SQL 1: innodb return row then convert to double ,BETWEEN cond also convert to double ,so the accuracy is lost,this process is in
->FilterIterator::Read
->Item_func_between::val_int
function double_from_string_with_check used to handle conversions
SQL 2:use Range scan method,BETWEEN cond '568989202507171830001000' convert to decimel and postition to innodb Btree.function str2my_decimal
used to handle conversion
I think the same statement should return the same amount of data for the same data, and should not be affected by the index.
other , follow sql
mysql> select count(*) from test1 where b = '568989202507171830001000' ;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (8 min 22.38 sec)
mysql> desc select count(*) from test1 where b = '568989202507171830001000' ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test1 | NULL | ALL | NULL | NULL | NULL | NULL | 3000 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (2.17 sec)
this sql also use FilterIterator::Read -> Item_func_eq::val_int to filter data,but '568989202507171830001000' convert to decimel , different from Item_func_between
How to repeat:
Refer to attachment
Suggested fix:
I think the same statement should return the same amount of data for the same data, and should not be affected by the index.
Description: Hi team: Two tables have the same data, and the table structure is as follows CREATE TABLE test1 ( a bigint(20) unsigned NOT NULL AUTO_INCREMENT, b decimal(38,0) NOT NULL, PRIMARY KEY (a) ); CREATE TABLE test2 ( a bigint(20) unsigned NOT NULL AUTO_INCREMENT, b decimal(38,0) NOT NULL, PRIMARY KEY (a) , key(b) ); The following statements have different data outputs SQL 1: use where filter mysql> DESC select count(*) from test1 where b BETWEEN '568989202507171830001000' AND '568989202507171830001999'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test1 | NULL | ALL | NULL | NULL | NULL | NULL | 3000 | 11.11 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (1.89 sec) mysql> select count(*) from test1 where b BETWEEN '568989202507171830001000' AND '568989202507171830001999'; +----------+ | count(*) | +----------+ | 2000 | +----------+ 1 row in set (2.13 sec) SQL 2: use index range search and position then return data. mysql> select count(*) from test2 where b BETWEEN '568989202507171830001000' AND '568989202507171830001999'; +----------+ | count(*) | +----------+ | 1000 | +----------+ 1 row in set (9.76 sec) SQL 1: innodb return row then convert to double ,BETWEEN cond also convert to double ,so the accuracy is lost,this process is in ->FilterIterator::Read ->Item_func_between::val_int function double_from_string_with_check used to handle conversions SQL 2:use Range scan method,BETWEEN cond '568989202507171830001000' convert to decimel and postition to innodb Btree.function str2my_decimal used to handle conversion I think the same statement should return the same amount of data for the same data, and should not be affected by the index. other , follow sql mysql> select count(*) from test1 where b = '568989202507171830001000' ; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (8 min 22.38 sec) mysql> desc select count(*) from test1 where b = '568989202507171830001000' ; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test1 | NULL | ALL | NULL | NULL | NULL | NULL | 3000 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (2.17 sec) this sql also use FilterIterator::Read -> Item_func_eq::val_int to filter data,but '568989202507171830001000' convert to decimel , different from Item_func_between How to repeat: Refer to attachment Suggested fix: I think the same statement should return the same amount of data for the same data, and should not be affected by the index.