[MDEV-4822] Wrong result (missing row) with index scan over Aria table Created: 2013-07-28  Updated: 2014-01-26  Resolved: 2014-01-26

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.3, 5.5.32
Fix Version/s: 5.5.35

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: optimizer

Issue Links:
Relates

 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))



 Comments   
Comment by Sergei Petrunia [ 2013-08-23 ]

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)

Comment by Sergei Petrunia [ 2013-08-23 ]

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)

Comment by Sergei Petrunia [ 2013-08-23 ]

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)

Comment by Sergei Petrunia [ 2013-08-23 ]

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.
Comment by Sergei Petrunia [ 2013-08-23 ]

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.

Comment by Sergei Petrunia [ 2013-08-23 ]

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.

Comment by Sergei Petrunia [ 2013-08-23 ]

Considering the above, this is Aria storage engine issue.

Comment by Michael Widenius [ 2014-01-26 ]

This was the same bug as in MDEV-4970

Generated at Thu Feb 08 06:59:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.