Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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 ))
;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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)
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
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()
Re: Wrong result with sort_union and multipart key in maria-5.3
Backported the fix into MariaDB 5.3.
Launchpad bug id: 817363
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 ));
--
-------------+--
-------------+--
-------------+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 ));
---
-------------+---
-------------+---
-------------+