MariaDB

  1. Bug #954900
  2. Comment #2

Comment 2 for bug 954900

Revision history for this message
Elena Stepanova (elenst) wrote : Re: Wrong result (34MB reproducer)

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
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.DT.t_id,db4.D.birthday 1 100.00Using index
Warnings:
Note 1003 select 5918 AS `dog_id`,5918 AS `dog_id`,'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))
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
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.DT.t_id,db4.D.birthday 1 100.00Using index
Warnings:
Note 1003 select 5918 AS `dog_id`,5918 AS `dog_id`,'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))
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_id int(10),
  birthday date,
  PRIMARY KEY (dog_id,birthday)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (5918,'2004-07-22');
CREATE TABLE t2 (
  dog_id int(10) unsigned,
  t_id char(1),
  birthday date,
  a_id int(10),
  PRIMARY KEY (dog_id,t_id,birthday,a_id)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES (5918,'N','2004-07-22',5216551);
INSERT INTO t2 VALUES (5918,'N','2004-07-22',5223640);
INSERT INTO t2 VALUES (5918,'N','2004-07-22',5389491);
INSERT INTO t2 VALUES (5918,'N','2004-07-22',5749434);
INSERT INTO t2 VALUES (5918,'N','2004-07-22',5992424);
INSERT INTO t2 VALUES (5922,'N','2005-06-30',5076957);
INSERT INTO t2 VALUES (5924,'N','2000-08-11',20264);
INSERT INTO t2 VALUES (5924,'N','2000-08-11',64251);
INSERT INTO t2 VALUES (5924,'N','2000-08-11',74748);
INSERT INTO t2 VALUES (5924,'N','2000-08-11',87590);
INSERT INTO t2 VALUES (5924,'N','2000-08-11',104695);
INSERT INTO t2 VALUES (5924,'N','2000-08-11',133136);
INSERT INTO t2 VALUES (5924,'N','2000-08-11',5027806);
INSERT INTO t2 VALUES (5924,'N','2000-08-11',5076957);
INSERT INTO t2 VALUES (5924,'N','2000-08-11',5166821);
INSERT INTO t2 VALUES (5924,'N','2000-08-11',5181896);
INSERT INTO t2 VALUES (5924,'N','2000-08-11',5217908);
INSERT INTO t2 VALUES (5924,'N','2000-08-11',5220812);
INSERT INTO t2 VALUES (5924,'N','2000-08-11',5226473);
INSERT INTO t2 VALUES (5924,'N','2000-08-11',5339111);
INSERT INTO t2 VALUES (5925,'N','2005-02-10',19227);
INSERT INTO t2 VALUES (5925,'N','2005-02-10',74529);
INSERT INTO t2 VALUES (5925,'N','2005-02-10',74748);
INSERT INTO t2 VALUES (5927,'N','2005-08-18',20264);
INSERT INTO t2 VALUES (5927,'N','2005-08-18',58364);
INSERT INTO t2 VALUES (5929,'N','2005-01-19',58364);
INSERT INTO t2 VALUES (5935,'N','2006-03-10',19227);
INSERT INTO t2 VALUES (5935,'N','2006-03-10',64251);
INSERT INTO t2 VALUES (5935,'N','2006-03-10',5222400);
INSERT INTO t2 VALUES (5935,'N','2006-03-10',5226473);
INSERT INTO t2 VALUES (5936,'N','2004-10-29',5015032);
INSERT INTO t2 VALUES (5937,'N','2002-04-05',11237);
INSERT INTO t2 VALUES (5937,'N','2002-04-05',23911);
INSERT INTO t2 VALUES (5937,'N','2002-04-05',112133);
INSERT INTO t2 VALUES (5937,'N','2002-04-05',169721);
INSERT INTO t2 VALUES (5937,'N','2002-04-05',170650);
INSERT INTO t2 VALUES (5937,'N','2002-04-05',5014494);
INSERT INTO t2 VALUES (5937,'N','2002-04-05',5166009);
INSERT INTO t2 VALUES (5937,'N','2002-04-05',5181871);
INSERT INTO t2 VALUES (5937,'N','2002-04-05',5213380);
INSERT INTO t2 VALUES (5937,'N','2002-04-05',5214875);
INSERT INTO t2 VALUES (5937,'N','2002-04-05',5895062);
INSERT INTO t2 VALUES (5938,'N','2006-03-24',11237);
INSERT INTO t2 VALUES (5938,'N','2006-03-24',19227);
INSERT INTO t2 VALUES (5938,'N','2006-03-24',23911);
INSERT INTO t2 VALUES (5938,'N','2006-03-24',58364);
INSERT INTO t2 VALUES (5938,'N','2006-03-24',64251);
INSERT INTO t2 VALUES (5938,'N','2006-03-24',111716);
INSERT INTO t2 VALUES (5938,'N','2006-03-24',112702);
INSERT INTO t2 VALUES (5938,'N','2006-03-24',133136);
INSERT INTO t2 VALUES (5938,'N','2006-03-24',168718);
INSERT INTO t2 VALUES (5938,'N','2006-03-24',5137136);
INSERT INTO t2 VALUES (5938,'N','2006-03-24',5161519);
INSERT INTO t2 VALUES (5938,'N','2006-03-24',5168120);
INSERT INTO t2 VALUES (5938,'N','2006-03-24',5219034);
INSERT INTO t2 VALUES (6234,'N','2006-06-02',103058);
INSERT INTO t2 VALUES (6234,'N','2006-06-02',5146844);
INSERT INTO t2 VALUES (6235,'N','2006-06-01',12900);
INSERT INTO t2 VALUES (6235,'N','2006-06-01',20264);
INSERT INTO t2 VALUES (6235,'N','2006-06-01',64251);
INSERT INTO t2 VALUES (6235,'N','2006-06-01',75160);
INSERT INTO t2 VALUES (6235,'N','2006-06-01',5014494);
INSERT INTO t2 VALUES (6235,'N','2006-06-01',5181638);
INSERT INTO t2 VALUES (6236,'N','2006-06-06',112595);
INSERT INTO t2 VALUES (6236,'N','2006-06-06',5219601);
INSERT INTO t2 VALUES (6236,'N','2006-06-06',5808374);
CREATE TABLE t3 (
  dog_id int(10) unsigned
) ENGINE=MyISAM;
INSERT INTO t3 VALUES (5918);
CREATE TABLE t4 (
  dog_id int(10),
  t_id char(1),
  birthday date,
  KEY (t_id)
) ENGINE=MyISAM;
INSERT INTO t4 VALUES (5918,'N','2004-07-22');
INSERT INTO t4 VALUES (5919,'N','2004-07-20');
CREATE TABLE t5 (
  dog_id int(10) unsigned,
  UNIQUE KEY (dog_id)
) ENGINE=MyISAM;
INSERT INTO t5 VALUES (5918);
SELECT * FROM t5 DU, t1 D, t4 DT, t2 DSA, t3 DSAR
WHERE DU.dog_id = D.dog_id
  AND D.dog_id = DT.dog_id
  AND D.birthday = DT.birthday
  AND DT.t_id = DSA.t_id
  AND DT.birthday = DSA.birthday
  AND DSA.dog_id = DSAR.dog_id;

# End of test case

# Expected result:
# dog_id dog_id birthday dog_id t_id birthday dog_id t_id birthday a_id dog_id
# 5918 5918 2004-07-22 5918 N 2004-07-22 5918 N 2004-07-22 5216551 5918
# 5918 5918 2004-07-22 5918 N 2004-07-22 5918 N 2004-07-22 5223640 5918
# 5918 5918 2004-07-22 5918 N 2004-07-22 5918 N 2004-07-22 5389491 5918
# 5918 5918 2004-07-22 5918 N 2004-07-22 5918 N 2004-07-22 5749434 5918
# 5918 5918 2004-07-22 5918 N 2004-07-22 5918 N 2004-07-22 5992424 5918

# Actual result: empty set