Wrong result (missing rows) with simple join and WHERE with = and ANDs, no subqueries, MyISAM or Aria
Bug #954900 reported by
Peter (Stig) Edwards
This bug affects 1 person
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Igor Babaev |
Bug Description
Thank you for MariaDB 5.3.5-ga
I will try and attach the reproducer, it is 34MB.
MariaDB 5.3.5-ga from mariadb-
There are 5 tables being joined in the reproducer, MariaDB 5.3.5-ga has an EXPLAIN plan that differs from MySQL 5.1.48 and 5.5.17, and Percona Server 5.5.16-rel22.0. MariabDB 5.3.5-ga returns 0 rows, 26 are expected. In particular MariaDB 5.3.5-ga seems to be referencing different tables in the last 2 joins.
Cheers
summary: |
- Wrong result (34MB reproducer) + Wrong result (missing rows) with simple join and WHERE with = and ANDs, + no subqueries, MyISAM or Aria |
Changed in maria: | |
status: | New → In Progress |
Changed in maria: | |
importance: | Undecided → High |
Changed in maria: | |
importance: | High → Critical |
Changed in maria: | |
assignee: | Timour Katchaounov (timour) → Igor Babaev (igorb-seattle) |
Changed in maria: | |
status: | In Progress → Fix Committed |
To post a comment you must log in.
Reduced test case (from the original SQL) below.
bzr version-info
revision-id: <email address hidden>
date: 2012-03-13 13:49:18 -0700
build-date: 2012-03-15 03:12:31 +0400
revno: 3459
Also reproducible on MariaDB 5.5 (revno 3319).
Not reproducible on MariaDB 5.2, MySQL 5.1.61, 5.5.21, trunk.
Reproducible with the default optimizer_switch, as well as with all OFF values, MyISAM or Aria tables.
EXPLAIN with all OFFs (wrong result):
id select_type table type possible_keys key key_len ref rows filtered Extra DT.t_id, db4.D.birthday 1 100.00Using index ,'2004- 07-22' AS `birthday` ,`db4`. `DT`.`dog_ id` AS `dog_id` ,`db4`. `DT`.`t_ id` AS `t_id`, `db4`.` DT`.`birthday` AS `birthday` ,`db4`. `DSA`.` dog_id` AS `dog_id` ,`db4`. `DSA`.` t_id` AS `t_id`, `db4`.` DSA`.`birthday` AS `birthday` ,`db4`. `DSA`.` a_id` AS `a_id`,5918 AS `dog_id` from `db4`.`t5` `DU` join `db4`.`t1` `D` join `db4`.`t4` `DT` join `db4`.`t2` `DSA` join `db4`.`t3` `DSAR` where ((`db4` .`DSA`. `dog_id` = 5918) and (`db4`.`DSA`.`t_id` = `db4`.`DT`.`t_id`) and (`db4`. `DT`.`birthday` = '2004-07-22') and (`db4`. `DSA`.` birthday` = '2004-07-22') and (`db4`. `DT`.`dog_ id` = 5918) and (5918 = 5918))
1 SIMPLE DU system dog_id NULL NULL NULL 1 100.00
1 SIMPLE D system PRIMARY NULL NULL NULL 1 100.00
1 SIMPLE DSAR system NULL NULL NULL NULL 1 100.00
1 SIMPLE DT range t_id t_id 2 NULL 2 100.00 Using where
1 SIMPLE DSA ref PRIMARY PRIMARY 8 const,db4.
Warnings:
Note 1003 select 5918 AS `dog_id`,5918 AS `dog_id`
SELECT * FROM t5 DU, t1 D, t4 DT, t2 DSA, t3 DSAR
EXPLAIN with the default optimizer_switch (wrong result, too):
id select_type table type possible_keys key key_len ref rows filtered Extra DT.t_id, db4.D.birthday 1 100.00Using index ,'2004- 07-22' AS `birthday` ,`db4`. `DT`.`dog_ id` AS `dog_id` ,`db4`. `DT`.`t_ id` AS `t_id`, `db4`.` DT`.`birthday` AS `birthday` ,`db4`. `DSA`.` dog_id` AS `dog_id` ,`db4`. `DSA`.` t_id` AS `t_id`, `db4`.` DSA`.`birthday` AS `birthday` ,`db4`. `DSA`.` a_id` AS `a_id`,5918 AS `dog_id` from `db4`.`t5` `DU` join `db4`.`t1` `D` join `db4`.`t4` `DT` join `db4`.`t2` `DSA` join `db4`.`t3` `DSAR` where ((`db4` .`DSA`. `dog_id` = 5918) and (`db4`.`DSA`.`t_id` = `db4`.`DT`.`t_id`) and (`db4`. `DT`.`birthday` = '2004-07-22') and (`db4`. `DSA`.` birthday` = '2004-07-22') and (`db4`. `DT`.`dog_ id` = 5918) and (5918 = 5918))
1 SIMPLE DU system dog_id NULL NULL NULL 1 100.00
1 SIMPLE D system PRIMARY NULL NULL NULL 1 100.00
1 SIMPLE DSAR system NULL NULL NULL NULL 1 100.00
1 SIMPLE DT range t_id t_id 2 NULL 2 100.00 Using index condition; Using where
1 SIMPLE DSA ref PRIMARY PRIMARY 8 const,db4.
Warnings:
Note 1003 select 5918 AS `dog_id`,5918 AS `dog_id`
SELECT * FROM t5 DU, t1 D, t4 DT, t2 DSA, t3 DSAR
# Test case:
DROP DATABASE IF EXISTS db4;
CREATE DATABASE db4;
USE db4;
CREATE TABLE t1 (
dog_...