| Bug #21277 | InnoDB, wrong result set, index_merge strategy, second index not evaluated | ||
|---|---|---|---|
| Submitted: | 25 Jul 2006 15:07 | Modified: | 6 Sep 2006 23:37 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.1.12 | OS: | Linux (Linux/x86) |
| Assigned to: | Sergey Petrunya | CPU Architecture: | Any |
[25 Jul 2006 15:08]
Matthias Leich
testscript
Attachment: ml002.test (application/test, text), 1.45 KiB.
[26 Jul 2006 6:11]
Heikki Tuuri
This is probably a MySQL bug. Anyway, I will run this inside gdb and try to locate what goes wrong.
[28 Jul 2006 18:44]
Heikki Tuuri
Not able to repeat this with 5.1.8:
mysql> select * from t0 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
+------+------+------+
| key1 | key2 | key3 |
+------+------+------+
| 31 | 31 | 31 |
| 32 | 32 | 32 |
| 33 | 33 | 33 |
| 34 | 34 | 34 |
| 35 | 35 | 35 |
| 36 | 36 | 36 |
| 37 | 37 | 37 |
| 38 | 38 | 38 |
| 39 | 39 | 39 |
+------+------+------+
9 rows in set (0.00 sec)
mysql> explain
-> select * from t0 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
+----+-------------+-------+-------------+---------------+-------+---------+------+------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+---------------+-------+---------+------+------+--------------------------------------+
| 1 | SIMPLE | t0 | index_merge | i2,i3 | i3,i2 | 4,4 | | 9 | Using sort_union(i3,i2); Using where |
+----+-------------+-------+-------------+---------------+-------+---------+------+------+--------------------------------------+
1 row in set (0.00 sec)
[28 Jul 2006 19:45]
Heikki Tuuri
I am able to repeat the bug with 5.1.12. Something has been broken in the past 5 months.
[29 Jul 2006 11:08]
Heikki Tuuri
Hi!
The bug seems to be in the MySQL query optimizer/executor. When MySQL asks InnoDB to scan the index i2, InnoDB in function ::build_template() checks which columns MySQL wants to fetch. But MySQL has set the table->read_set bitmap to 0x4, which means that MySQL only wants to fetch column 'key3'! It should have said that it also wants 'key2'.
When InnoDB returns the row, the buffer for column 'key2' happens to contain 0x38 as garbage, which makes MySQL to think that we have scanned the whole range 32 ... 40.
Fix: MySQL should correctly set the table->read_set bitmap.
3152 for (i = 0; i < n_fields; i++) {
(gdb)
3153 templ = prebuilt->mysql_template + n_requested_fields;
(gdb) print n_fields
$2 = 3
(gdb) next
3154 field = table->field[i];
(gdb)
3156 if (UNIV_LIKELY(templ_type == ROW_MYSQL_REC_FIELDS)) {
(gdb)
3159 register const ibool index_contains_field =
(gdb)
3162 if (!index_contains_field && prebuilt->read_just_key) {
(gdb)
3166 goto skip_field;
(gdb)
3152 for (i = 0; i < n_fields; i++) {
(gdb)
3153 templ = prebuilt->mysql_template + n_requested_fields;
(gdb)
3154 field = table->field[i];
(gdb)
3156 if (UNIV_LIKELY(templ_type == ROW_MYSQL_REC_FIELDS)) {
(gdb)
3159 register const ibool index_contains_field =
(gdb)
3162 if (!index_contains_field && prebuilt->read_just_key) {
(gdb)
3169 if (index_contains_field && fetch_all_in_key) {
(gdb)
3175 if (bitmap_is_set(table->read_set, i) ||
(gdb) print i
$3 = 1
(gdb) next
3182 if (fetch_primary_key_cols
(gdb)
3152 for (i = 0; i < n_fields; i++) {
(gdb) print table->read_set
$4 = (MY_BITMAP *) 0x89e1910
(gdb) print *table->read_set
$5 = {bitmap = 0x89cdf20, n_bits = 3, last_word_mask = 4294967288,
last_word_ptr = 0x89cdf20, mutex = 0x0}
(gdb) print table->read_set->bitmap
$6 = (my_bitmap_map *) 0x89cdf20
(gdb) print *table->read_set->bitmap
$7 = 4
#0 ha_innobase::build_template (this=0x89cdc08, prebuilt=0x4032ca68,
thd=0x89d3010, table=0x89e1870, templ_type=1) at ha_innodb.cc:3251
#1 0x0824d173 in ha_innobase::change_active_index (this=0x89cdc08, keynr=0)
at ha_innodb.cc:4147
#2 0x0824cd0f in ha_innobase::index_init (this=0x89cdc08, keynr=0,
sorted=true) at ha_innodb.cc:3853
#3 0x08229df4 in QUICK_RANGE_SELECT::init (this=0x89ce4c8) at handler.h:1045
#4 0x08232902 in QUICK_INDEX_MERGE_SELECT::read_keys_and_merge (
this=0x89ce308) at opt_range.cc:7572
#5 0x081e0b0f in join_init_read_record (tab=0x89f1118) at sql_select.cc:10435
#6 0x081df720 in sub_select (join=0x89efa78, join_tab=0x89f1118,
end_of_records=2) at sql_select.cc:9800
#7 0x081df272 in do_select (join=0x89efa78, fields=0x89d336c, table=0x0,
procedure=0x0) at sql_select.cc:9564
#8 0x081d4740 in JOIN::exec (this=0x89efa78) at sql_select.cc:1796
#9 0x081d50ba in mysql_select (thd=0x89d3010, rref_pointer_array=0x89d3408,
tables=0x89eef20, wild_num=1, fields=@0x3, conds=0x89ef988, og_num=0,
order=0x0, group=0x0, having=0x0, proc_param=0x0,
select_options=2147764736, result=0x89efa68, unit=0x89d30b4,
select_lex=0x89d32e0) at sql_select.cc:1958
#10 0x081d0f91 in handle_select (thd=0x89d3010, lex=0x89d304c,
result=0x89efa68, setup_tables_done_option=0) at sql_select.cc:242
#11 0x0819fb72 in execute_sqlcom_select (thd=0x89d3010, all_tables=0x89eef20)
at sql_parse.cc:5282
#12 0x0819f4a2 in mysql_execute_command (thd=0x89d3010) at sql_parse.cc:2620
#13 0x081a1165 in mysql_parse (thd=0x89d3010,
inBuf=0x89eede8 "select * from t0 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40)", length=144519244) at sql_parse.cc:6089
#14 0x08196b79 in dispatch_command (command=COM_DROP_DB, thd=0x89d3010,
packet=0x89e6db9 "", packet_length=144633320) at sql_parse.cc:1816
#15 0x081966bd in do_command (thd=0x89d3010) at sql_parse.cc:1602
#16 0x08195c02 in handle_one_connection (arg=0x89d3010) at sql_parse.cc:1222
#17 0x4003bb63 in start_thread () from /lib/tls/libpthread.so.0
#18 0x4024518a in clone () from /lib/tls/libc.so.6
Regards,
Heikki
[21 Aug 2006 13:02]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/10673 ChangeSet@1.2274, 2006-08-21 17:03:07+04:00, sergefp@mysql.com +3 -0 BUG#21277: Wrong results in index_merge queries: Remove the code that cleared "read fields set" for merged scans. That code was based on assumption that "We're going to just read rowids", while actually QUICK_RANGE_SELECT code would also need key part values to check that retrieved record(s) fall within the scanned intervals.
[22 Aug 2006 13:37]
Sergey Petrunya
Notes for the changelog: Queries that use index_merge/sort_union method to access an InnoDB table could produce wrong results. The bug was introduced in 5.1.10 when "upgrading to new handler and bitmap interface".
[4 Sep 2006 11:43]
Evgeny Potemkin
Fixed in 5.1.12
[6 Sep 2006 23:37]
Jon Stephens
Documented bugfix in 5.1.12 changelog.
Description: SET SESSION STORAGE_ENGINE = InnoDB; create table t0 ( key1 int not null, key2 int not null default 0, key3 int not null default 0); insert into t0(key1) values (1),(2),(3),(4),(5),(6),(7),(8); set @d=8; insert into t0 (key1) select key1+@d from t0; set @d=@d*2; insert into t0 (key1) select key1+@d from t0; set @d=@d*2; insert into t0 (key1) select key1+@d from t0; set @d=@d*2; insert into t0 (key1) select key1+@d from t0; set @d=@d*2; insert into t0 (key1) select key1+@d from t0; set @d=@d*2; insert into t0 (key1) select key1+@d from t0; set @d=@d*2; insert into t0 (key1) select key1+@d from t0; set @d=@d*2; alter table t0 add index i2(key2); alter table t0 add index i3(key3); update t0 set key2=key1,key3=key1; explain select * from t0 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 index_merge i2,i3 i3,i2 4,4 NULL 9 Using sort_union(i3,i2); Using where select * from t0 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); key1 key2 key3 31 31 31 32 32 32 33 33 33 34 34 34 <-------- This result set is too small. Where are the rows with values between 35 and 39 ? # Statement for comparison # Please note that the optimizer strategy looks very similar. explain select * from t0 where (key3 BETWEEN 30 + 1 and 35 - 1) or (key2 BETWEEN 32 + 1 and 40 - 1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 index_merge i2,i3 i3,i2 4,4 NULL 11 Using sort_union(i3,i2); Using where select * from t0 where (key3 BETWEEN 30 + 1 and 35 - 1) or (key2 BETWEEN 32 + 1 and 40 - 1); key1 key2 key3 31 31 31 32 32 32 33 33 33 34 34 34 35 35 35 36 36 36 37 37 37 38 38 38 39 39 39 <---- This is the correct result set. My environment: - PC(Pentium M, x86-32Bit) with Linux(SuSE 9.3) - MySQL compiled (compile-pentium-debug-max) from source Version 5.1 ChangeSet@1.2264, 2006-07-24 Some observations: 1. MySQL 5.0 ChangeSet@1.2202, 2006-06-27 (sorry a bit outdated) does not show this bug. 2. If I take MyISAM instead of InnoDB the bug disappears. Therefore I am unsure if we have here an InnoDB specific bug or if the upper SQL layer (Optimizer?) treats InnoDB somehow wrong. 3. If I switch the order of the "or" terms within the statement select * from t0 where (key2 >32 and key2 < 40) or (key3 > 30 and key3<35); I get a different incomplete result set. Rows with values between 33 and 39. So it looks like the second (order in statement) index is not evaluated. How to repeat: Please use my attached testscript ml002.test copy it to mysql-test/t echo "Dummy" > r/ml002.result # Produce a dummy file with # expected results ./mysql-test-run ml002