MariaDB

Wrong result with RIGHT JOIN + derived_merge

Bug #884184 reported by Philip Stoev
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
Medium
Sergey Petrunia
MariaDB 5.2

Bug Description

The following query:

SELECT alias1.*
FROM (
 SELECT t1.*
 FROM t1
 LEFT JOIN t2
 ON ( t1.a = t2.a )
  WHERE t2.a <> 0
) AS alias1
RIGHT JOIN t3
ON ( t3.a = alias1.b );

returns the following incorrect result when executed with derived_merge=on:

a b
0 g

the correct result is (NULL , NULL) as the subquery does not return any rows and therefore the RIGHT JOIN between the subquery and a table can not include any rows from the subquery.

explain:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t3 system NULL NULL NULL NULL 1 100.00
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where

 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t3` left join (`test`.`t1`) on(((`test`.`t2`.`a` <> 0) and (`test`.`t1`.`b` = 'g'))) where 1

minimal switch: derived_merge=on
full switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=on,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

bzr version-info
revision-id: <email address hidden>
date: 2011-10-28 12:38:36 +0400
build-date: 2011-10-31 13:55:00 +0200
revno: 3258
branch-nick: maria-5.3

test case:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int(11), b varchar(1)) ;
INSERT IGNORE INTO t1 VALUES (0,'g');

DROP TABLE IF EXISTS t3;
CREATE TABLE t3 ( a varchar(1)) ;
INSERT IGNORE INTO t3 VALUES ('g');

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a)) ;

SET SESSION optimizer_swtich='derived_merge=on';

SELECT alias1.*
FROM (
 SELECT t1.*
 FROM t1
 LEFT JOIN t2
 ON ( t1.a = t2.a )
  WHERE t2.a <> 0
) AS alias1
RIGHT JOIN t3
ON ( t3.a = alias1.b );

Philip Stoev (pstoev-askmonty)
Changed in maria:
assignee: nobody → Igor Babaev (igorb-seattle)
milestone: none → 5.3
Revision history for this message
Sergey Petrunia (sergefp) wrote :

Changed query from RIGHT JOIN to LEFT JOIN (the bug repeats):

SELECT alias1.*
FROM
t3
LEFT JOIN
(
  SELECT t1.*
  FROM t1 LEFT JOIN t2 ON ( t1.a = t2.a )
  WHERE t2.a <> 0
) AS alias1
ON ( t3.a = alias1.b );

MariaDB Buildbot (buildbot-askmonty)
Changed in maria:
status: New → Confirmed
Igor Babaev (igorb-seattle)
Changed in maria:
importance: Undecided → Medium
milestone: 5.3 → 5.2
assignee: Igor Babaev (igorb-seattle) → Sergey Petrunia (sergefp)
Revision history for this message
Igor Babaev (igorb-seattle) wrote :
Download full text (3.4 KiB)

This bug can be reproduced as well with:
 - LEFT JOIN instead of RIGHT JOIN
 - view instead of derived table.

The bug is reproducible in 5.2 (and most probably in 5.1):

igor@sophia:~/maria/maria-5.2/mysql-test> ../client/mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

MariaDB [test]> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [test]> CREATE TABLE t1 ( a int(11), b varchar(1)) ;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> INSERT IGNORE INTO t1 VALUES (0,'g');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]>
MariaDB [test]> DROP TABLE IF EXISTS t3;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [test]> CREATE TABLE t3 ( a varchar(1)) ;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> INSERT IGNORE INTO t3 VALUES ('g');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]>
MariaDB [test]> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [test]> CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a)) ;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> create view v1 as SELECT t1.* FROM t1 LEFT JOIN t2 ON ( t1.a = t2.a ) WHERE t2.a <> 0;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );
+------+------+
| a | b |
 +------+------+
| 0 | g |
 +------+------+
1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );
+----+-------------+-------+--------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 +----+-------------+-------+--------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t3 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
 +----+-------------+-------+--------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

This is a bug of table elimination:

MariaDB [test]> set optimizer_switch='table_elimination=off';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b );
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 +----+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+
| 1 | SIMPLE | t3 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.a | 1 | Using where; Using index |
 +----+-------------+-------+--------+--------------...

Read more...

Sergey Petrunia (sergefp)
Changed in maria:
status: Confirmed → Fix Committed
Daniel Bartholomew (dbart)
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.
Subscribing...

Other bug subscribers

Remote bug watches

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