Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Duplicate
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
Description
create database db; use db; CREATE TABLE `test` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` bigint(20) DEFAULT NULL, PRIMARY KEY (`a`), KEY `idx_b` (`b`) ) ENGINE=InnoDB AUTO_INCREMENT=129033 DEFAULT CHARSET=latin1
Fill it with data:
for i in `seq 1 254` ; do for z in `seq 1 254` ; do echo "insert into test (b) values (INET_ATON('192.168.${i}.${z}'));" ; done ; done | mysql db
Check query execution plan:
explain select MIN(b) from test where b >= inet_aton('192.168.119.32')\G
Results from MySQL:
Database changed
mysql> explain select MIN(b) from test where b >= inet_aton('192.168.119.32')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away
1 row in set (0.00 sec)
Results from MariaDB:
MariaDB [db]> explain select MIN(b) from test where b >= inet_aton('192.168.119.32')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: range
possible_keys: idx_b
key: idx_b
key_len: 9
ref: NULL
rows: 32545
Extra: Using where; Using index
1 row in set (0.00 sec)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The check may make sense for CHAR(N) column and constant longer than N. It may be relevant for integers of different sizes. However, here both values are BIGINT.
Fix suggestion: do not do the max_length comparison if the type is to be compared as integer.
Fixed by fix for MDEV-5257
This was caused by this: https://bugs.launchpad.net/maria/+bug/884175
the problem is in simple_pred(), added lines:
if (args[0]
>max_length < args[1]>max_length)return 0;
we have:
(gdb) p args[0]
$14 = (Item_field *) 0x7fff2c006c60
(gdb) p args[1]
$15 = (Item_func_inet_aton *) 0x7fff2c006ea0
(gdb) p args[0]->max_length
$16 = 20
(gdb) p args[1]->max_length
$17 = 21