[MDEV-4822] Wrong result (missing row) with index scan over Aria table - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.3, 5.5.32
Fix Version/s: 5.5.35
Component/s: None
Labels:
- optimizer

Description

Note: It looks like other bugs we had with index_merge, but I don't remember (and can't find) a combination with Aria, so I'll file it in case it's different.

SET optimizer_switch = 'index_merge=on,index_merge_sort_union=on';

CREATE TABLE t1 (id INT, state VARCHAR(10), INDEX(state), PRIMARY KEY (id)) ENGINE=Aria;

ALTER TABLE t1 DISABLE KEYS;
INSERT INTO t1 VALUES (1,'California'),(2,'Ohio');
INSERT INTO t1 VALUES (3,'Alabama'),(4,'Jersey');
ALTER TABLE t1 ENABLE KEYS;

SELECT * FROM t1 FORCE KEY (PRIMARY,state) WHERE state > 'H' OR id < 255 ;

Actual result:

id	state
1	California
2	Ohio
4	Jersey

Expected result:

id	state
1	California
2	Ohio
3	Alabama
4	Jersey

bzr version-info

revision-id: sanja@montyprogram.com-20130718081618-6ax63urznfnqmzgt
revno: 3839
branch-nick: 5.5

Also reproducible on 10.0 and 10.0-base, but not on 5.3.
Not reproducible with MyISAM.

Minimal optimizer_switch: index_merge=on,index_merge_sort_union=on
Reproducible with the default optimizer_switch too.

EXPLAIN:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	index_merge	PRIMARY,state	state,PRIMARY	13,4	NULL	4	100.00	Using sort_union(state,PRIMARY); Using where
Warnings:
Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`state` AS `state` from `test`.`t1` FORCE INDEX (`state`) FORCE INDEX (PRIMARY) where ((`test`.`t1`.`state` > 'H') or (`test`.`t1`.`id` < 255))

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Sergei Petrunia added a comment - 2013-08-23 16:55

It is possible to construct a testcase that is not using index_merge:

MariaDB [j4]> CREATE TABLE t1 (id INT, state VARCHAR(10), INDEX(state), PRIMARY KEY (id)) ENGINE=Aria;
Query OK, 0 rows affected (0.13 sec)

MariaDB [j4]> ALTER TABLE t1 DISABLE KEYS;
Query OK, 0 rows affected (0.01 sec)

MariaDB [j4]> INSERT INTO t1 VALUES (1,'California'),(2,'Ohio');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [j4]> INSERT INTO t1 VALUES (3,'Alabama'),(4,'Jersey');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [j4]> ALTER TABLE t1 ENABLE KEYS;
Query OK, 0 rows affected (0.10 sec)

MariaDB [j4]> SELECT * FROM t1 FORCE KEY (PRIMARY) WHERE id < 255;
--------------+

state

--------------+

1	California
2	Ohio

--------------+
2 rows in set (0.01 sec)

MariaDB [j4]> SELECT * FROM t1 IGNORE KEY (PRIMARY) WHERE id < 255;
--------------+

state

--------------+

1	California
2	Ohio
3	Alabama
4	Jersey

--------------+
4 rows in set (0.01 sec)

Show

Sergei Petrunia added a comment - 2013-08-23 16:55 It is possible to construct a testcase that is not using index_merge: MariaDB [j4] > CREATE TABLE t1 (id INT, state VARCHAR(10), INDEX(state), PRIMARY KEY (id)) ENGINE=Aria; Query OK, 0 rows affected (0.13 sec) MariaDB [j4] > ALTER TABLE t1 DISABLE KEYS; Query OK, 0 rows affected (0.01 sec) MariaDB [j4] > INSERT INTO t1 VALUES (1,'California'),(2,'Ohio'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [j4] > INSERT INTO t1 VALUES (3,'Alabama'),(4,'Jersey'); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [j4] > ALTER TABLE t1 ENABLE KEYS; Query OK, 0 rows affected (0.10 sec) MariaDB [j4] > SELECT * FROM t1 FORCE KEY (PRIMARY) WHERE id < 255; --- -----------+ id state --- -----------+ 1 California 2 Ohio --- -----------+ 2 rows in set (0.01 sec) MariaDB [j4] > SELECT * FROM t1 IGNORE KEY (PRIMARY) WHERE id < 255; --- -----------+ id state --- -----------+ 1 California 2 Ohio 3 Alabama 4 Jersey --- -----------+ 4 rows in set (0.01 sec)

Hide

Permalink

Sergei Petrunia added a comment - 2013-08-23 17:02

EXPLAIN for the problematic query:

MariaDB [j4]> explain SELECT * FROM t1 FORCE KEY (PRIMARY) WHERE id < 255;
-----------------------------------------------------------------------------------+

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

-----------------------------------------------------------------------------------+

SIMPLE

range

PRIMARY

NULL

Using index condition

-----------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Show

Sergei Petrunia added a comment - 2013-08-23 17:02 EXPLAIN for the problematic query: MariaDB [j4] > explain SELECT * FROM t1 FORCE KEY (PRIMARY) WHERE id < 255; ----- ----------- ----- ----- ------------- ------- ------- ---- ---- ----------------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ----------- ----- ----- ------------- ------- ------- ---- ---- ----------------------+ 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using index condition ----- ----------- ----- ----- ------------- ------- ------- ---- ---- ----------------------+ 1 row in set (0.01 sec)

Hide

Permalink

Sergei Petrunia added a comment - 2013-08-23 17:04

EXPLAIN shows that Index Condition Pushdown is used. If I disable it and run this testcase:

set optimizer_switch='index_condition_pushdown=off';
CREATE TABLE t1 (id INT, state VARCHAR(10), INDEX(state), PRIMARY KEY (id)) ENGINE=Aria;
ALTER TABLE t1 DISABLE KEYS;
INSERT INTO t1 VALUES (1,'California'),(2,'Ohio');
INSERT INTO t1 VALUES (3,'Alabama'),(4,'Jersey');
ALTER TABLE t1 ENABLE KEYS;
SELECT * FROM t1 FORCE KEY (PRIMARY) WHERE id < 255;
SELECT * FROM t1 IGNORE KEY (PRIMARY) WHERE id < 255;

I still see the same difference.

The EXPLAIN w/o ICP is:

MariaDB [j4]> explain SELECT * FROM t1 FORCE KEY (PRIMARY) WHERE id < 255;
-------------------------------------------------------------------------+

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

-------------------------------------------------------------------------+

SIMPLE

range

PRIMARY

NULL

Using where

-------------------------------------------------------------------------+
1 row in set (0.01 sec)

Show

Sergei Petrunia added a comment - 2013-08-23 17:04 EXPLAIN shows that Index Condition Pushdown is used. If I disable it and run this testcase: set optimizer_switch='index_condition_pushdown=off'; CREATE TABLE t1 (id INT, state VARCHAR(10), INDEX(state), PRIMARY KEY (id)) ENGINE=Aria; ALTER TABLE t1 DISABLE KEYS; INSERT INTO t1 VALUES (1,'California'),(2,'Ohio'); INSERT INTO t1 VALUES (3,'Alabama'),(4,'Jersey'); ALTER TABLE t1 ENABLE KEYS; SELECT * FROM t1 FORCE KEY (PRIMARY) WHERE id < 255; SELECT * FROM t1 IGNORE KEY (PRIMARY) WHERE id < 255; I still see the same difference. The EXPLAIN w/o ICP is: MariaDB [j4] > explain SELECT * FROM t1 FORCE KEY (PRIMARY) WHERE id < 255; ----- ----------- ----- ----- ------------- ------- ------- ---- ---- ------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ----------- ----- ----- ------------- ------- ------- ---- ---- ------------+ 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where ----- ----------- ----- ----- ------------- ------- ------- ---- ---- ------------+ 1 row in set (0.01 sec)

Hide

Permalink

Sergei Petrunia added a comment - 2013-08-23 18:41

The issue is not always repeatable. For example, for the above testcase:

First (i.e. right after ALTER TABLE statement) execution of SELECT .. FORCE KEY query always produces wrong result.
Re-running SELECT ... FORCE KEY query will also produce a wrong result
Running it for the 3rd time may or may not produce a wrong result.

Running "SELECT .... IGNORE KEY..." query "fixes" something - if I run SELECT ... FORCE KEY right after it, it will always produce the correct result.

Show

Sergei Petrunia added a comment - 2013-08-23 18:41 The issue is not always repeatable. For example, for the above testcase: First (i.e. right after ALTER TABLE statement) execution of SELECT .. FORCE KEY query always produces wrong result. Re-running SELECT ... FORCE KEY query will also produce a wrong result Running it for the 3rd time may or may not produce a wrong result. Running "SELECT .... IGNORE KEY..." query "fixes" something - if I run SELECT ... FORCE KEY right after it, it will always produce the correct result.

Hide

Permalink

Sergei Petrunia added a comment - 2013-08-23 18:44

I have ran the SELECT ... FORCE KEY (PRIMARY) ... query side-by side, correct and incorrect execution. The difference comes from inside Aria engine. The execution proceeds as follows:

ha_maria::index_first() = 0 // returns id=1, state=California
ha_maria::index_next()= 0 // returns id=2, state=Ohio
ha_maria::index_next()= 137 // should have returned id=3, state=Alabama. Instead, it returns EOF.

Show

Sergei Petrunia added a comment - 2013-08-23 18:44 I have ran the SELECT ... FORCE KEY (PRIMARY) ... query side-by side, correct and incorrect execution. The difference comes from inside Aria engine. The execution proceeds as follows: ha_maria::index_first() = 0 // returns id=1, state=California ha_maria::index_next()= 0 // returns id=2, state=Ohio ha_maria::index_next()= 137 // should have returned id=3, state=Alabama. Instead, it returns EOF.

Hide

Permalink

Sergei Petrunia added a comment - 2013-08-23 18:47

The difference in execution happens here:
(gdb) wher
#0 trnman_can_read_from (trn=0x7fff9401a4b0, trid=96) at /home/psergey/dev2/5.5/storage/maria/trnman.c:594
#1 0x0000000000a8a848 in _ma_row_visible_transactional_table (info=0x7fff940134e0) at /home/psergey/dev2/5.5/storage/maria/ma_state.c:835
#2 0x0000000000ae26d7 in maria_rnext (info=0x7fff940134e0, buf=0x7fff9400aaa8 "\376\002", inx=0) at /home/psergey/dev2/5.5/storage/maria/ma_rnext.c:109
#3 0x0000000000a9d0b5 in ha_maria::index_next (this=0x7fff9400b248, buf=0x7fff9400aaa8 "\376\002") at /home/psergey/dev2/5.5/storage/maria/ha_maria.cc:2316
#4 0x00000000005a59fe in handler::ha_index_next (this=0x7fff9400b248, buf=0x7fff9400aaa8 "\376\002") at /home/psergey/dev2/5.5/sql/sql_class.h:4226
#5 0x00000000007c25e5 in handler::read_range_next (this=0x7fff9400b248) at /home/psergey/dev2/5.5/sql/handler.cc:4658
#6 0x000000000073d92f in handler::multi_range_read_next (this=0x7fff9400b248, range_info=0x7fffc87c63e0) at /home/psergey/dev2/5.5/sql/multi_range_read.cc:263
#7 0x000000000073dbbc in Mrr_simple_index_reader::get_next (this=0x7fff9400b7c0, range_info=0x7fffc87c63e0) at /home/psergey/dev2/5.5/sql/multi_range_read.cc:328
#8 0x00000000007405ed in DsMrr_impl::dsmrr_next (this=0x7fff9400b688, range_info=0x7fffc87c63e0) at /home/psergey/dev2/5.5/sql/multi_range_read.cc:1392
#9 0x0000000000aa03c6 in ha_maria::multi_range_read_next (this=0x7fff9400b248, range_info=0x7fffc87c63e0) at /home/psergey/dev2/5.5/storage/maria/ha_maria.cc:3766
#10 0x00000000008cbcfe in QUICK_RANGE_SELECT::get_next (this=0x7fff94019950) at /home/psergey/dev2/5.5/sql/opt_range.cc:11148
#11 0x00000000008da548 in rr_quick (info=0x7fff94024f20) at /home/psergey/dev2/5.5/sql/records.cc:345
#12 0x0000000000660af6 in sub_select (join=0x7fff94006f48, join_tab=0x7fff94024e70, end_of_records=false) at /home/psergey/dev2/5.5/sql/sql_select.cc:16624
#13 0x00000000006602f5 in do_select (join=0x7fff94006f48, fields=0x4646eb0, table=0x0, procedure=0x0) at /home/psergey/dev2/5.5/sql/sql_select.cc:16275
#14 0x000000000064039c in JOIN::exec (this=0x7fff94006f48) at /home/psergey/dev2/5.5/sql/sql_select.cc:2848
#15 0x0000000000640b93 in mysql_select (thd=0x46433b0, rref_pointer_array=0x4647008, tables=0x7fff94006598, wild_num=1, fields=..., conds=0x7fff94006d18, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff94006f28, unit=0x46466c8, select_lex=0x4646da0) at /home/psergey/dev2/5.5/sql/sql_select.cc:3068
#16 0x000000000063786f in handle_select (thd=0x46433b0, lex=0x4646618, result=0x7fff94006f28, setup_tables_done_option=0) at /home/psergey/dev2/5.5/sql/sql_select.cc:318

here we are in trnman_can_read_from() at:

if (trid >= trn->trid)

{ /* We have now two cases trid > trn->trid, in which case the row is from a new transaction and not visible, in which case we should return 0. trid == trn->trid in which case the row is from the current transaction and we should return 1 */ => return trid == trn->trid; }

and we have

(gdb) print trid
$111 = 96
(gdb) p trn->trid
$112 = 95

(gdb) fini
Run till exit from #0 trnman_can_read_from (trn=0x7fff9401a4b0, trid=96) at /home/psergey/dev2/5.5/storage/maria/trnman.c:594
_ma_row_visible_transactional_table (info=0x7fff940134e0) at /home/psergey/dev2/5.5/storage/maria/ma_state.c:836
Value returned is $113 = 0
(gdb) fini
Run till exit from #0 _ma_row_visible_transactional_table (info=0x7fff940134e0) at /home/psergey/dev2/5.5/storage/maria/ma_state.c:836
0x0000000000ae26d7 in maria_rnext (info=0x7fff940134e0, buf=0x7fff9400aaa8 "\376\002", inx=0) at /home/psergey/dev2/5.5/storage/maria/ma_rnext.c:109
Value returned is $114 = 0 '\000'

... and from here we go to call _ma_search_next(), which continues until the end of the table and then returns HA_ERR_END_OF_FILE.
Apparently, some transaction_id number is wrong, for some reason.

Show

Sergei Petrunia added a comment - 2013-08-23 18:47 The difference in execution happens here: (gdb) wher #0 trnman_can_read_from (trn=0x7fff9401a4b0, trid=96) at /home/psergey/dev2/5.5/storage/maria/trnman.c:594 #1 0x0000000000a8a848 in _ma_row_visible_transactional_table (info=0x7fff940134e0) at /home/psergey/dev2/5.5/storage/maria/ma_state.c:835 #2 0x0000000000ae26d7 in maria_rnext (info=0x7fff940134e0, buf=0x7fff9400aaa8 "\376\002", inx=0) at /home/psergey/dev2/5.5/storage/maria/ma_rnext.c:109 #3 0x0000000000a9d0b5 in ha_maria::index_next (this=0x7fff9400b248, buf=0x7fff9400aaa8 "\376\002") at /home/psergey/dev2/5.5/storage/maria/ha_maria.cc:2316 #4 0x00000000005a59fe in handler::ha_index_next (this=0x7fff9400b248, buf=0x7fff9400aaa8 "\376\002") at /home/psergey/dev2/5.5/sql/sql_class.h:4226 #5 0x00000000007c25e5 in handler::read_range_next (this=0x7fff9400b248) at /home/psergey/dev2/5.5/sql/handler.cc:4658 #6 0x000000000073d92f in handler::multi_range_read_next (this=0x7fff9400b248, range_info=0x7fffc87c63e0) at /home/psergey/dev2/5.5/sql/multi_range_read.cc:263 #7 0x000000000073dbbc in Mrr_simple_index_reader::get_next (this=0x7fff9400b7c0, range_info=0x7fffc87c63e0) at /home/psergey/dev2/5.5/sql/multi_range_read.cc:328 #8 0x00000000007405ed in DsMrr_impl::dsmrr_next (this=0x7fff9400b688, range_info=0x7fffc87c63e0) at /home/psergey/dev2/5.5/sql/multi_range_read.cc:1392 #9 0x0000000000aa03c6 in ha_maria::multi_range_read_next (this=0x7fff9400b248, range_info=0x7fffc87c63e0) at /home/psergey/dev2/5.5/storage/maria/ha_maria.cc:3766 #10 0x00000000008cbcfe in QUICK_RANGE_SELECT::get_next (this=0x7fff94019950) at /home/psergey/dev2/5.5/sql/opt_range.cc:11148 #11 0x00000000008da548 in rr_quick (info=0x7fff94024f20) at /home/psergey/dev2/5.5/sql/records.cc:345 #12 0x0000000000660af6 in sub_select (join=0x7fff94006f48, join_tab=0x7fff94024e70, end_of_records=false) at /home/psergey/dev2/5.5/sql/sql_select.cc:16624 #13 0x00000000006602f5 in do_select (join=0x7fff94006f48, fields=0x4646eb0, table=0x0, procedure=0x0) at /home/psergey/dev2/5.5/sql/sql_select.cc:16275 #14 0x000000000064039c in JOIN::exec (this=0x7fff94006f48) at /home/psergey/dev2/5.5/sql/sql_select.cc:2848 #15 0x0000000000640b93 in mysql_select (thd=0x46433b0, rref_pointer_array=0x4647008, tables=0x7fff94006598, wild_num=1, fields=..., conds=0x7fff94006d18, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fff94006f28, unit=0x46466c8, select_lex=0x4646da0) at /home/psergey/dev2/5.5/sql/sql_select.cc:3068 #16 0x000000000063786f in handle_select (thd=0x46433b0, lex=0x4646618, result=0x7fff94006f28, setup_tables_done_option=0) at /home/psergey/dev2/5.5/sql/sql_select.cc:318 here we are in trnman_can_read_from() at: if (trid >= trn->trid) { /* We have now two cases trid > trn->trid, in which case the row is from a new transaction and not visible, in which case we should return 0. trid == trn->trid in which case the row is from the current transaction and we should return 1 */ => return trid == trn->trid; } and we have (gdb) print trid $111 = 96 (gdb) p trn->trid $112 = 95 (gdb) fini Run till exit from #0 trnman_can_read_from (trn=0x7fff9401a4b0, trid=96) at /home/psergey/dev2/5.5/storage/maria/trnman.c:594 _ma_row_visible_transactional_table (info=0x7fff940134e0) at /home/psergey/dev2/5.5/storage/maria/ma_state.c:836 Value returned is $113 = 0 (gdb) fini Run till exit from #0 _ma_row_visible_transactional_table (info=0x7fff940134e0) at /home/psergey/dev2/5.5/storage/maria/ma_state.c:836 0x0000000000ae26d7 in maria_rnext (info=0x7fff940134e0, buf=0x7fff9400aaa8 "\376\002", inx=0) at /home/psergey/dev2/5.5/storage/maria/ma_rnext.c:109 Value returned is $114 = 0 '\000' ... and from here we go to call _ma_search_next(), which continues until the end of the table and then returns HA_ERR_END_OF_FILE. Apparently, some transaction_id number is wrong, for some reason.

Hide

Permalink

Sergei Petrunia added a comment - 2013-08-23 18:47

Considering the above, this is Aria storage engine issue.

Show

Sergei Petrunia added a comment - 2013-08-23 18:47 Considering the above, this is Aria storage engine issue.

Hide

Permalink

Michael Widenius added a comment - 2014-01-26 17:27

This was the same bug as in ~~MDEV-4970~~

Show

Michael Widenius added a comment - 2014-01-26 17:27 This was the same bug as in MDEV-4970

People

Assignee:

Michael Widenius

Reporter:

Elena Stepanova

Votes:

0 Vote for this issue

Watchers:

6 Start watching this issue

Dates

Created:

2013-07-28 20:05

Updated:

2014-01-26 17:27

Resolved:

2014-01-26 17:27

Time Tracking

Estimated:

Not Specified

Remaining:

Logged:

Agile

View on Board