Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6116

Wrong result (missing row) with materialization+semijoin, HAVING

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 5.5.37
        • Fix Version/s: 5.5.38, 10.0.11, 5.3.13
        • Component/s: None
        • Labels:

          Description

          The problem appeared in 5.3 tree with the following revision:

          revno: 3766
revision-id: igor@askmonty.org-20140306215634-4gm5hv023ynkxfzq
parent: igor@askmonty.org-20140221052733-ypg4dpfcmy5l0b3q
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.3
timestamp: Thu 2014-03-06 13:56:34 -0800
message:
  Fixed bug mdev-5686.
  The calls of the function remove_eq_conds() may change the and/or structure
  of the where conditions. So JOIN::equal_cond should be updated for non-recursive
  calls of remove_eq_conds().

          Test case 1 (with MyISAM)

          SET optimizer_switch = 'materialization=on,semijoin=on';

CREATE TABLE t1 (f_key VARCHAR(1), f_nokey VARCHAR(1), INDEX(f_key)) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('v','v'),('s','s');

CREATE TABLE t2 (f_int INT, f_key VARCHAR(1), INDEX(f_key)) ENGINE=InnoDB;
INSERT INTO t2 VALUES 
(4,'j'),(6,'v'),(3,'c'),(5,'m'),(3,'d'),(2,'d'),(2,'y'),
(9,'t'),(3,'d'),(8,'s'),(1,'r'),(8,'m'),(8,'b'),(5,'x');

SELECT t2.f_int FROM t1 INNER JOIN t2 ON (t2.f_key = t1.f_nokey) 
WHERE t1.f_nokey IN ( 
  SELECT t1.f_key FROM t1, t2 WHERE t1.f_key = t2.f_key 
) HAVING t2.f_int >= 0 AND t2.f_int != 0;

          Result:

          f_int
6

          Expected result:

          f_int
6
8

          EXPLAIN:

          id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
1	PRIMARY	t2	ref	f_key	f_key	4	test.t1.f_key	2	100.00	Using index condition
2	MATERIALIZED	t1	index	f_key	f_key	4	NULL	2	100.00	Using where; Using index
2	MATERIALIZED	t2	ref	f_key	f_key	4	test.t1.f_key	2	100.00	Using index
Warnings:
Note	1003	select `test`.`t2`.`f_int` AS `f_int` from `test`.`t1` semi join (`test`.`t1` join `test`.`t2`) join `test`.`t2` where ((`test`.`t2`.`f_key` = `test`.`t1`.`f_key`) and (`test`.`t2`.`f_key` = `test`.`t1`.`f_nokey`)) having ((`test`.`t2`.`f_int` >= 0) and (`test`.`t2`.`f_int` <> 0))

          Test case 2 (with InnoDB)

          
--source include/have_innodb.inc

CREATE TABLE t1 (f_key VARCHAR(1) NOT NULL, f_nokey VARCHAR(1), INDEX(f_key)) ENGINE=InnoDB;
INSERT INTO t1 VALUES 
('v','v'),('s','s'),('l','l'),('y','y'),('c','c'),('i','i'),('h','h'),('q','q');

CREATE TABLE t2 (f_int INT, f_key VARCHAR(1), INDEX(f_key)) ENGINE=InnoDB;
INSERT INTO t2 VALUES 
(4,'j'),(6,'v'),(3,'c'),(5,'m'),(3,'d'),(2,'d'),(2,'y'),(9,'t'),(3,'d'),(8,'s'),
(1,'r'),(8,'m'),(8,'b'),(5,'x'),(7,'g'),(5,'p'),(1, 'q'),(6,'w'),(2,'d'),(9,'e');

SELECT t2.f_int FROM t1 INNER JOIN t2 ON (t2.f_key = t1.f_nokey) 
WHERE t1.f_nokey IN ( 
  SELECT t1.f_key FROM t2, t1 WHERE t1.f_key = t2.f_key  
) HAVING t2.f_int >= 0 AND t2.f_int != 0;

          Result:

          f_int
2

          Expected result:

          f_int
6
8
2
3
1

          Test case 3 (same as test case 2, only without NOT NULL):

          
--source include/have_innodb.inc

CREATE TABLE t1 (f_key VARCHAR(1), f_nokey VARCHAR(1), INDEX(f_key)) ENGINE=InnoDB;
INSERT INTO t1 VALUES 
('v','v'),('s','s'),('l','l'),('y','y'),('c','c'),('i','i'),('h','h'),('q','q');

CREATE TABLE t2 (f_int INT, f_key VARCHAR(1), INDEX(f_key)) ENGINE=InnoDB;
INSERT INTO t2 VALUES 
(4,'j'),(6,'v'),(3,'c'),(5,'m'),(3,'d'),(2,'d'),(2,'y'),(9,'t'),(3,'d'),(8,'s'),
(1,'r'),(8,'m'),(8,'b'),(5,'x'),(7,'g'),(5,'p'),(1, 'q'),(6,'w'),(2,'d'),(9,'e');

SELECT t2.f_int FROM t1 INNER JOIN t2 ON (t2.f_key = t1.f_nokey) 
WHERE t1.f_nokey IN ( 
  SELECT t1.f_key FROM t2, t1 WHERE t1.f_key = t2.f_key  
) HAVING t2.f_int >= 0 AND t2.f_int != 0;

          Result (empty set): 

          f_int

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                igor Igor Babaev added a comment -

                The fix for this bug was pushed into the 5.3 tree.

                Show
                igor Igor Babaev added a comment - The fix for this bug was pushed into the 5.3 tree.

                  People

                  • Assignee:
                    igor Igor Babaev
                    Reporter:
                    elenst Elena Stepanova
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: