[MDEV-2528] LP:823301 - Wrong result with index_merge / sort_union and LEFT JOIN Created: 2011-08-09  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: Critical
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug823301.xml    

 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

 Comments   
Comment by Philip Stoev (Inactive) [ 2011-09-09 ]

Re: Wrong result with index_merge / sort_union and LEFT JOIN
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: igor@askmonty.org-20110908162131-y1ddcj1sfxzlayah
date: 2011-09-08 09:21:31 -0700
build-date: 2011-09-09 10:07:16 +0300
revno: 3180
branch-nick: maria-5.3

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

Launchpad bug id: 823301

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