Wrong result with index_merge / sort_union and LEFT JOIN

Bug #823301 reported by Philip Stoev
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
Critical
Igor Babaev

Bug Description

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

Changed in maria:
milestone: none → 5.3
assignee: nobody → Igor Babaev (igorb-seattle)
Changed in maria:
importance: Undecided → Critical
status: New → Confirmed
Changed in maria:
status: Confirmed → In Progress
Revision history for this message
Philip Stoev (pstoev-askmonty) wrote :

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

Changed in maria:
status: In Progress → Fix Committed
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.