Details
Minor
Description
drop table if exists t1;
create table t1 (
id text not null
,qty int not null
) engine=innodb charset=utf8;
insert into t1(id,qty) values ('a',2),('a',2),('a',3),('a',4),('b',2),('c',1),('c',2);
expected output
select a.*, b.* from t1 a left outer join t1 b on a.id = b.id and a.qty = b.qty;
+----+-----+------+------+ | id | qty | id | qty | +----+-----+------+------+ | a | 2 | a | 2 | | a | 2 | a | 2 | | a | 2 | a | 2 | | a | 2 | a | 2 | | a | 3 | a | 3 | | a | 4 | a | 4 | | b | 2 | b | 2 | | c | 1 | c | 1 | | c | 2 | c | 2 | +----+-----+------+------+ 9 rows in set (0.01 sec)
buggy query
select a.*, b.* from ( select id, qty from t1 group by id,qty ) a left outer join ( select id, qty from t1 group by id,qty ) b on a.id = b.id and a.qty = b.qty;
+----+-----+------+------+ | id | qty | id | qty | +----+-----+------+------+ | a | 2 | NULL | NULL | | a | 3 | NULL | NULL | | a | 4 | NULL | NULL | | b | 2 | NULL | NULL | | c | 1 | NULL | NULL | | c | 2 | NULL | NULL | +----+-----+------+------+ 6 rows in set (0.01 sec)
inner join not affected
select a.*, b.* from ( select id, qty from t1 group by id,qty ) a inner join ( select id, qty from t1 group by id,qty ) b on a.id = b.id and a.qty = b.qty;
+----+-----+----+-----+ | id | qty | id | qty | +----+-----+----+-----+ | a | 2 | a | 2 | | a | 3 | a | 3 | | a | 4 | a | 4 | | b | 2 | b | 2 | | c | 1 | c | 1 | | c | 2 | c | 2 | +----+-----+----+-----+ 6 rows in set (0.01 sec)
strcmp resolves
select a.*, b.* from ( select id, qty from t1 group by id,qty ) a left outer join ( select id, qty from t1 group by id,qty ) b on strcmp(a.id,b.id) = 0 and a.qty = b.qty;
+----+-----+------+------+ | id | qty | id | qty | +----+-----+------+------+ | a | 2 | a | 2 | | a | 3 | a | 3 | | a | 4 | a | 4 | | b | 2 | b | 2 | | c | 1 | c | 1 | | c | 2 | c | 2 | +----+-----+------+------+ 6 rows in set (0.01 sec)
create identicial table with varchar id field
drop table if exists t2;
create table t2 (
id varchar(255) not null
,qty int not null
) engine=innodb charset=utf8;
insert into t2(id,qty) values ('a',2),('a',2),('a',3),('a',4),('b',2),('c',1),('c',2);
buggy query is resolved with varchar
select a.*, b.* from ( select id, qty from t2 group by id,qty ) a left outer join ( select id, qty from t2 group by id,qty ) b on a.id = b.id and a.qty = b.qty;
+----+-----+------+------+ | id | qty | id | qty | +----+-----+------+------+ | a | 2 | a | 2 | | a | 3 | a | 3 | | a | 4 | a | 4 | | b | 2 | b | 2 | | c | 1 | c | 1 | | c | 2 | c | 2 | +----+-----+------+------+ 6 rows in set (0.01 sec)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Attached my.cnf file
Strangely, I cannot reproduce the problem locally. However, InnoDB execution plans are known to be indeterministic.
Could you please run EXPLAIN EXTENDED on the buggy query, and SHOW WARNINGS afterwards, and paste the results here?
Thanks.
The version that displays this bug is 5.5.25, not 5.5.35. My mistake.
This bug appears to be resolved as of 5.5.37.
Yes, I was eventually able to reproduce it on 5.5.35; but it is not persistent, so there is no guarantee that it was actually fixed in 5.5.37 until we know which exact revision fixed it. I am working on it.
There was a long story around this bug, but everything points at it being finally fixed in 5.5.34. So, if you did not see it on 5.5.35, I will assume my observation about 5.5.35 was a human error, and it was actually an earlier version which could happen. I cannot reproduce it (any longer) on 5.5.35.
The following revision fixed it on 5.3 tree – it is reliably reproducible before this patch, but not since then:
revno: 3699 revision-id: sanja@askmonty.org-20130925123013-qbytshoda82jzqkn parent: bar@mnogosearch.org-20130916120355-atoza9gj16selqtp committer: sanja@askmonty.org branch nick: work-maria-5.3-MDEV-5039 timestamp: Wed 2013-09-25 15:30:13 +0300 message: MDEV-5039: incorrect Item_func_regex::update_used_tables() Other fix of maybe_null problem and revert of revno: 3608 "MDEV-3873 & MDEV-3876 & MDEV-3912 : Wrong result (extra rows) with ALL subquery from a MERGE view."
It was merged into 5.5 before 5.5.34 release.
On 5.5 tree though, the provided test case stopped failing (reliably?) even earlier, since this merge (happened before 5.5.30 release):
revno: 3673 [merge]
revision-id: psergey@askmonty.org-20130301074410-qc0qxs677qzgeh32
parent: sergii@pisem.net-20130228225617-p9m624uq9wzo8b6r
parent: psergey@askmonty.org-20130301042335-x16j7uo1dwxxgqs9
committer: Sergey Petrunya <psergey@askmonty.org>
branch nick: 5.5
timestamp: Fri 2013-03-01 11:44:10 +0400
message:
Merge 5.3->5.5
...
revno: 2502.574.1
revision-id: igor@askmonty.org-20130228223546-wz17znfoub8u958b
parent: igor@askmonty.org-20130228175535-vfkbu1y2neelqbq4
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.3-mdev4209
timestamp: Thu 2013-02-28 14:35:46 -0800
message:
Fixed bug mdev-4209
Do not include BLOB fields into the key to access the temporary
table created for a materialized view/derived table.
BLOB components are not allowed in keys.
The fix definitely has something to do with the failure, since the difference between the "good" and the "bad" plan is this:
Bad plan (wrong result):
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 100.00 1 PRIMARY <derived3> ref key0 key0 16 a.id,a.qty 2 100.00 3 DERIVED t1 ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort 2 DERIVED t1 ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort Warnings: Note 1003 select `a`.`id` AS `id`,`a`.`qty` AS `qty`,`b`.`id` AS `id`,`b`.`qty` AS `qty` from (select `test`.`t1`.`id` AS `id`,`test`.`t1`.`qty` AS `qty` from `test`.`t1` group by `test`.`t1`.`id`,`test`.`t1`.`qty`) `a` left join (select `test`.`t1`.`id` AS `id`,`test`.`t1`.`qty` AS `qty` from `test`.`t1` group by `test`.`t1`.`id`,`test`.`t1`.`qty`) `b` on
Good plan (correct result):
id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 100.00 1 PRIMARY <derived3> ref key0 key0 5 a.qty 2 100.00 Using where 3 DERIVED t1 ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort 2 DERIVED t1 ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort Warnings: Note 1003 select `a`.`id` AS `id`,`a`.`qty` AS `qty`,`b`.`id` AS `id`,`b`.`qty` AS `qty` from (select `test`.`t1`.`id` AS `id`,`test`.`t1`.`qty` AS `qty` from `test`.`t1` group by `test`.`t1`.`id`,`test`.`t1`.`qty`) `a` left join (select `test`.`t1`.`id` AS `id`,`test`.`t1`.`qty` AS `qty` from `test`.`t1` group by `test`.`t1`.`id`,`test`.`t1`.`qty`) `b` on(((`b`.`id` = `a`.`id`) and (`b`.`qty` = `a`.`qty`))) where 1
Given the above, I'm closing it as fixed in 5.3.12 and 5.5.34.
Please comment to re-open if you observe it again.
Hi Ryan,
Thanks for the report. Could you please also provide your cnf file(s)?