MariaDB

  1. Bug #611382
  2. Comment #3

Comment 3 for bug 611382

Revision history for this message
Philip Stoev (pstoev-askmonty) wrote : Re: RQG: Query returns extra rows when executed with semijoin=off

The test case from the first comment is still repeatable in maria-5.3 with materialization=on . It is not repeatable in maria-5.3-mwl89, however the plan there is different.

Test case:

CREATE TABLE t1 ( f4 varchar(1)) ENGINE=MyISAM;
INSERT IGNORE INTO t1 VALUES (NULL);

CREATE TABLE t2 ( f2 date, f3 varchar(1), f4 varchar(1)) ;
INSERT IGNORE INTO t2 VALUES ('2005-05-03','c','c'),('1900-01-01','d','d');

CREATE TABLE t3 ( f3 varchar(1)) ;
INSERT IGNORE INTO t3 VALUES ('c');

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

SELECT t1.f4
FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;

Explain in maria-5.3:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t3 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2

Explain in maria-5.3-mwl89:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t3 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where