Wrong result with index_merge / sort_union and LEFT JOIN

[Philip Stoev]

The following query

SELECT *
FROM t1
LEFT OUTER JOIN t2 ON t1.b = t2.a
WHERE t1.c > 7 AND t1.e > 1 AND t1.e != 0 AND
t1.e NOT IN ( 2 , 8 ) OR t1.e >= 7 AND t1.e < 8 OR t1.e > 7 ;

returns 4 instead of 5 rows when executed with index_merge / sort_union .

Explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge PRIMARY,e,e_2 e,e_2 5,5 NULL 8 Using sort_union(e,e_2); Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where

test case:

CREATE TABLE t1 ( b int, c int NOT NULL , d int, e int, KEY (e), PRIMARY KEY (c), KEY (e,c,d)) ;
INSERT INTO t1 VALUES (0,58,7,7),(0,63,2,0),(0,64,186974208,8),(0,65,1,'-205389824'),
(0,71,1901395968,'-258670592'),(0,72,321323008,'-749993984'),(0,73,0,3),(0,74,5,74252288),(0,75,5,3);

CREATE TABLE t2 ( a int) ;
INSERT INTO t2 VALUES (1),(1);

SELECT *
FROM t1
LEFT OUTER JOIN t2 ON t1.b = t2.a
WHERE t1.c > 7 AND t1.e > 1 AND t1.e != 0 AND t1.e NOT IN
( 2 , 8 )
 OR t1.e >= 7 AND t1.e < 8 OR t1.e > 7 ;

bzr version-info:

revision-id: <email address hidden>
date: 2011-08-09 10:28:57 +0300
build-date: 2011-08-09 16:40:36 +0300
revno: 3147
branch-nick: maria-5.3

Reproducible in maria-5.3, Not reproducible in maria-5.2, mysql-5.5

[Philip Stoev (pstoev-askmonty)]

A test case without a join:

CREATE TABLE t1 ( a int NOT NULL , b int, c varchar(32), KEY (c,b), PRIMARY KEY (a)) ;
INSERT INTO t1 VALUES (9,7,'s'),(10,1,'j'),(16,1,NULL),(17,1,'r'),(18,9,'v'),(19,1,NULL),(20,5,'r');

SELECT *
FROM t1
WHERE a = 0 AND c LIKE 'l' AND
( a = 134 OR b = 157 )
 OR c < 'j' OR c > 'bj' ;

explain:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index_merge
possible_keys: PRIMARY,col_varchar_key
          key: PRIMARY,col_varchar_key
      key_len: 4,35
          ref: NULL
         rows: 6
        Extra: Using sort_union(PRIMARY,col_varchar_key); Using where
1 row in set (0.00 sec)

incorrect result set:

| 9 | 7 | s |
| 17 | 1 | r |
| 18 | 9 | v |
| 20 | 5 | r |

expected result set:

| 9 | 7 | s |
| 10 | 1 | j |
| 17 | 1 | r |
| 18 | 9 | v |
| 20 | 5 | r |

 bzr version-info
revision-id: <email address hidden>
date: 2011-09-08 09:21:31 -0700
build-date: 2011-09-09 10:07:16 +0300
revno: 3180
branch-nick: maria-5.3