Wrong result with range access and multi-part key
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Sergey Petrunia |
Bug 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(
EXPLAIN:
| 1 | SIMPLE | alias1 | index_merge | PRIMARY,d | d,PRIMARY | 40,4 | NULL | 6 | Using sort_union(
optimizer_switch:
index_
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,
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 ))
;
summary: |
- Wrong result with sort_union and multipart key + Wrong result with sort_union and multipart key in maria-5.3 |
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Igor Babaev (igorb-seattle) |
description: | updated |
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
Changed in maria: | |
assignee: | Igor Babaev (igorb-seattle) → Sergey Petrunia (sergefp) |
Changed in maria: | |
status: | Fix Committed → Fix Released |
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 |
+----+-