[MDEV-5897] Erroneous results with Left Outer Join temporary table text fields Created: 2014-03-18  Updated: 2014-04-21  Resolved: 2014-04-21

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.12, 5.5.33a
Fix Version/s: 5.5.34, 5.3.13

Type: Bug Priority: Minor
Reporter: Ryan Peterson Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None

Attachments: File my.cnf    

 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)



 Comments   
Comment by Elena Stepanova [ 2014-03-18 ]

Hi Ryan,

Thanks for the report. Could you please also provide your cnf file(s)?

Comment by Ryan Peterson [ 2014-03-18 ]

Attached my.cnf file

Comment by Elena Stepanova [ 2014-03-23 ]

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.

Comment by Ryan Peterson [ 2014-04-21 ]

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.

Comment by Elena Stepanova [ 2014-04-21 ]

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.

Comment by Elena Stepanova [ 2014-04-21 ]

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

Comment by Elena Stepanova [ 2014-04-21 ]

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.

Generated at Thu Feb 08 07:07:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.