[MDEV-4336] left join hangs in optimizing state, consumes all memory and leads to crash Created: 2013-03-27  Updated: 2013-05-21  Resolved: 2013-05-07

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.30
Fix Version/s: 10.0.3, 5.5.31, 5.3.13

Type: Bug Priority: Critical
Reporter: Martijn Otto Assignee: Igor Babaev
Resolution: Fixed Votes: 3
Labels: optimizer
Environment:

Linux 3.2.0-38-generic

Issue Links:
Relates
relates to MDEV-4461 Server uses all memory and CPU in rem... Closed

 Description   

Create a table like this

CREATE TABLE `foo` (
 
  `id` int(11) NOT NULL,
 
  `modified` datetime NOT NULL,
 
  PRIMARY KEY (`id`)
 
);

Then run the following query to crash the server:

select a.* from foo a left join foo b on a.id = b.id where a.modified > b.modified or b.modified is null;

The query will enter the optimizing state and start consuming memory (and swap, if applicable) until the server runs out of memory and mysqld is killed.

Note that it does not matter whether the table is an InnoDB or MyISAM table. MySQL 5.5.29 does not exhibit this behavior.

 Comments   
Comment by Elena Stepanova [ 2013-03-27 ]

It's a regression which came to 5.5.30 with the following revision:
revno: 3671 [merge]
revision-id: sergii@pisem.net-20130228214729-t0xhegqa9uzsr1k9
parent: sergii@pisem.net-20130228191953-70sigyi7o2viwe53
parent: sergii@pisem.net-20130228204847-vlit40812mz8af3u
committer: Sergei Golubchik <sergii@pisem.net>
branch nick: 5.5
timestamp: Thu 2013-02-28 22:47:29 +0100
message:
5.3->5.5 merge

I couldn't however reproduce it on 5.3 (neither on the current one nor on the version from that time), so I'm keeping it 5.5-only for now.
Reproducible as described, it doesn't matter whether the table is empty or not.
EXPLAIN exhibits the same behavior.

Comment by Elena Stepanova [ 2013-05-01 ]

See also MDEV-4461

Comment by Elena Stepanova [ 2013-05-07 ]

Fixed by the following revision:

------------------------------------------------------------
revno: 3655
revision-id: igor@askmonty.org-20130504054645-5geeszh105eh7017
parent: igor@askmonty.org-20130504014520-zupkoo5m4b91assl
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.3-bugs
timestamp: Fri 2013-05-03 22:46:45 -0700
message:
Fixed bug mdev-4336.
When iterating over a list of conditions using List_iterator
the function remove_eq_conds should skip all predicates that
replace a condition from the list. Otherwise it can come to
an infinite recursion.

Comment by Martijn Otto [ 2013-05-21 ]

Could I get a diff of the fix so I can apply it to the current 5.5.30 series? I have been unable to find the commit in launchpad.

Generated at Thu Feb 08 06:55:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.