[MDEV-3462] LP:817363 - Wrong result with range access and multi-part key Created: 2011-07-28  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug817363.xml    

 Description   

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

 Comments   
Comment by Sergei Petrunia [ 2011-08-02 ]

Re: Wrong result with sort_union and multipart key in maria-5.3
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

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

Comment by Sergei Petrunia [ 2011-08-02 ]

Re: Wrong result with sort_union and multipart key in maria-5.3
As the above queries show, the problem can be demonstrated without index_merge optimization, too.

Comment by Sergei Petrunia [ 2011-08-02 ]

Re: Wrong result with sort_union and multipart key in maria-5.3
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.

Comment by Sergei Petrunia [ 2011-08-02 ]

Re: Wrong result with sort_union and multipart key in maria-5.3
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:

Comment by Sergei Petrunia [ 2011-08-02 ]

Re: Wrong result with sort_union and multipart key in maria-5.3
The problem is repeatable with MariaDB 5.2, too. This is another proof it's not a problem with new DS-MRR.

Comment by Sergei Petrunia [ 2011-08-02 ]

Re: Wrong result with sort_union and multipart key in maria-5.3
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)

Comment by Sergei Petrunia [ 2011-08-03 ]

Re: Wrong result with sort_union and multipart key in maria-5.3
The problem is fixed by this fix:

revno: 2876.47.174
revision-id: jorgen.loland@oracle.com-20110519120355-qn7eprkad9jqwu5j
parent: mayank.prasad@oracle.com-20110518143645-bdxv4udzrmqsjmhq
committer: Jorgen Loland <jorgen.loland@oracle.com>
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

Comment by Sergei Petrunia [ 2011-08-03 ]

Re: Wrong result with sort_union and multipart key in maria-5.3
The following patch is a pre-requisite:

revno: 3363.3.16
revision-id: jorgen.loland@oracle.com-20110506132631-5wickj6dvrh1dpj6
parent: alexander.nozdrin@oracle.com-20110506132138-46459va9vcbd4nz0
committer: Jorgen Loland <jorgen.loland@oracle.com>
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()
Comment by Sergei Petrunia [ 2011-08-05 ]

Re: Wrong result with sort_union and multipart key in maria-5.3
Backported the fix into MariaDB 5.3.

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 817363

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