Re: Wrong result (34MB reproducer)

20120314_mariadb_5_3_5_incorrect_result.sql
LPexportBug954900_20120314_mariadb_5_3_5_incorrect_result.sql

Re: Wrong result (34MB reproducer)
Reduced test case (from the original SQL) below.

bzr version-info
revision-id: igor@askmonty.org-20120313204918-s1iqygsolswn79xo
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

1. 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;

1. End of test case

1. Expected result:
2. dog_id dog_id birthday dog_id t_id birthday dog_id t_id birthday a_id dog_id
3. 5918 5918 2004-07-22 5918 N 2004-07-22 5918 N 2004-07-22 5216551 5918
4. 5918 5918 2004-07-22 5918 N 2004-07-22 5918 N 2004-07-22 5223640 5918
5. 5918 5918 2004-07-22 5918 N 2004-07-22 5918 N 2004-07-22 5389491 5918
6. 5918 5918 2004-07-22 5918 N 2004-07-22 5918 N 2004-07-22 5749434 5918
7. 5918 5918 2004-07-22 5918 N 2004-07-22 5918 N 2004-07-22 5992424 5918

1. Actual result: empty set

Re: Wrong result (missing rows) with simple join and WHERE with = and ANDs, no subqueries, MyISAM or Aria
The following query allows to show the problem in a more stable way across different server versions:

set @@optimizer_switch='index_condition_pushdown=off';

explain extended
SELECT count FROM t5 straight_join t1 straight_join t3 straight_join t4 straight_join t2
WHERE t5.dog_id = t1.dog_id
AND t1.dog_id = t4.dog_id
AND t1.birthday = t4.birthday
AND t4.t_id = t2.t_id
AND t4.birthday = t2.birthday
AND t2.dog_id = t3.dog_id;

ICP is switched off to make comparison easier with 5.2, as well as to rule it out as a possible
cause and simplify the query plan. I will use the above query in the rest of the analysis.

MySQL 5.6.4 shows the closest query plan that works correctly compared to 5.3.

In 5.3 the plan is:

------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------

While in MySQL 5.6.4 the query plan is:

--------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

The only difference in the plans is that the access method for the last table 't4' is
"Using index" in MariaDB, and is "Using where; Using index" in MySQL.

My theory is that 5.3 creates an incorrect "ref" access method, and at the same time possibly
incorrectly decides not to use a filter condition.

Re: Wrong result (missing rows) with simple join and WHERE with = and ANDs, no subqueries, MyISAM or Aria

Parallel debugging that compares execution of MariaDB 5.3 and MySQL 5.6.4
shows the following:

= The second call to sub_select() accesses table 't2'.

= The call:
error= (*join_tab->read_first_record)(join_tab);
returns 0 in MySQL (key found), but 120 in MariaDB (key not found)

= The cause is result of incorrect contents of the key buffer. I think
so because if we manually replace ref.key_buff[4] with the contents
it has in MySQL, then the key is found correctly.

= The key buffer of the ref access method is filled by the following
call chain:

#0 store_key_field::copy_inner
#1 store_key::copy
#2 cp_buffer_from_ref
#3 join_read_always_key
#4 sub_select
...

= Investigation of store_key_field::copy_inner shows that there is a
difference in the second field of the key.

• MySQL copies t4.t_id into t2.t_id.
• MariaDB copies t1.birthday into t2.t_id.

This is clearly wrong. The rest of the analysis will figure out why
store_key_field.copy_field->from_field is incorrect (and possibly the
rest of the store_key objects contained in the join_tab->ref->key_copy
array.

Re: Wrong result (missing rows) with simple join and WHERE with = and ANDs, no subqueries, MyISAM or Aria
Thank you very much. I can confirm the fix stops the problem using the other (large) reproducers I have when building revision 3467.

Myself and others shall continue testing MariaDB 5.3 using:

http://terrier.askmonty.org/archive/pack/5.3/build-1924/kvm-bintar-hardy-amd64/mariadb-5.3.5-ga-Linux-x86_64.tar.gz

from:

http://buildbot.askmonty.org/buildbot/builders/kvm-tarbake-jaunty-x86/builds/1924

Re: Wrong result (missing rows) with simple join and WHERE with = and ANDs, no subqueries, MyISAM or Aria
Fix released in 5.3.6

Launchpad bug id: 954900