Wrong result with range access and multi-part key

[Philip Stoev]

Not repeatable with maria-5.2,mysql-5.5 . The following query:

SELECT c
FROM t1 AS alias1
WHERE alias1.d = 'q' OR alias1.d >= 'q' OR alias1.a > 97 OR (d IN
( 'j' , 's' , 'i' )
 AND
( alias1.b = 102 ))
 ;

returns 1 row when executed with sort_union(d,PRIMARY) and 2 rows if executed with other plans or other server versions.

EXPLAIN:

| 1 | SIMPLE | alias1 | index_merge | PRIMARY,d | d,PRIMARY | 40,4 | NULL | 6 | Using sort_union(d,PRIMARY); Using where |

optimizer_switch:

 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

revision-id: <email address hidden>
date: 2011-07-22 23:47:28 -0700
build-date: 2011-07-28 10:33:45 +0300
revno: 3134
branch-nick: maria-5.3

test case:

CREATE TABLE t1 ( a int NOT NULL , b int, c int, d varchar(32), KEY (d,b), PRIMARY KEY (a)) ;
INSERT INTO t1 VALUES (7,7,NULL,'e'),(8,1,0,'p'),(9,7,1,'s'),(10,1,1,'j'),(12,2,0,'c'),(13,0,0,'a'),(14,1,1,'q');

SELECT c
FROM t1 AS alias1
WHERE alias1.d = 'q' OR alias1.d >= 'q' OR alias1.a > 97 OR (d IN
( 'j' , 's' , 'i' )
 AND
( alias1.b = 102 ))
 ;

[Sergey Petrunia (sergefp)]

MariaDB [j1]> SELECT * FROM t1 AS alias1 force index(d) WHERE alias1.d = 'q' OR alias1.d >= 'q' OR (d IN ( 'j' , 's' , 'i' ) AND ( alias1.b = 102 ));
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 9 | 7 | 1 | s |
+---+------+------+------+
1 row in set (0.04 sec)

MariaDB [j1]> SELECT * FROM t1 AS alias1 ignore index(d) WHERE alias1.d = 'q' OR alias1.d >= 'q' OR (d IN ( 'j' , 's' , 'i' ) AND ( alias1.b = 102 ));
+----+------+------+------+
| a | b | c | d |
+----+------+------+------+
| 9 | 7 | 1 | s |
| 14 | 1 | 1 | q |
+----+------+------+------+

[Sergey Petrunia (sergefp)]

As the above queries show, the problem can be demonstrated without index_merge optimization, too.

[Sergey Petrunia (sergefp)]

The above queries were run with mrr=off.

debug trace shows that range access scans the following ranges:

quick range select, key d, length: 40
{
  i/102 <= X <= i/102
  j/102 <= X <= j/102
  q/102 <= X < s
  s <= X <= s
  s < X
}

and they don't include the d='q', b='1', a='14' row that we're missing.

[Sergey Petrunia (sergefp)]

In mysql-trunk, I get:
quick range select, key d, length: 40
  i/102 <= X <= i/102
  j/102 <= X <= j/102
  q <= X
other_keys: 0x0:

[Sergey Petrunia (sergefp)]

The problem is repeatable with MariaDB 5.2, too. This is another proof it's not a problem with new DS-MRR.

[Sergey Petrunia (sergefp)]

Repeatable on MySQL 5.1, too:

MySQL [j1]> SELECT * FROM t1 AS alias1 ignore index(d) WHERE alias1.d = 'q' OR alias1.d >= 'q' OR (d IN ( 'j' , 's' , 'i' ) AND ( alias1.b = 102 ));
+----+------+------+------+
| a | b | c | d |
+----+------+------+------+
| 9 | 7 | 1 | s |
| 14 | 1 | 1 | q |
+----+------+------+------+
2 rows in set (0.04 sec)

MySQL [j1]> SELECT * FROM t1 AS alias1 force index(d) WHERE alias1.d = 'q' OR alias1.d >= 'q' OR (d IN ( 'j' , 's' , 'i' ) AND ( alias1.b = 102 ));
+---+------+------+------+
| a | b | c | d |
+---+------+------+------+
| 9 | 7 | 1 | s |
+---+------+------+------+
1 row in set (0.06 sec)

MySQL [j1]> select version();
+------------------+
| version() |
+------------------+
| 5.1.54-debug-log |
+------------------+
1 row in set (0.01 sec)

[Sergey Petrunia (sergefp)]

The problem is fixed by this fix:

revno: 2876.47.174
revision-id: <email address hidden>
parent: <email address hidden>
committer: Jorgen Loland <email address hidden>
branch nick: mysql-trunk-11765831
timestamp: Thu 2011-05-19 14:03:55 +0200
message:
  BUG#11765831: 'RANGE ACCESS' MAY INCORRECTLY FILTER
                        AWAY QUALIFYING ROWS

[Sergey Petrunia (sergefp)]

The following patch is a pre-requisite:

revno: 3363.3.16
revision-id: <email address hidden>
parent: <email address hidden>
committer: Jorgen Loland <email address hidden>
branch nick: mysql-trunk-11765831
timestamp: Fri 2011-05-06 15:26:31 +0200
message:
  BUG#11765831: 'RANGE ACCESS' MAY INCORRECTLY FILTER
                AWAY QUALIFYING ROWS

  Preparation patch (does not include fix for the bug):

   * Extensively document key_or()
   * Remove tab indentations from key_or()
   * Minor code changes like using existing utility functions
     in key_or()

[Sergey Petrunia (sergefp)]

Backported the fix into MariaDB 5.3.