Details
Major
Description
I believe there is an issue with the query optimizer in MariaDB v5.5.30 and v10. Zip file contains SQL dump of test database structure and data as well as a SQL file of the offending query (copied below). I can replicate this issue on MariaDB v5.5.30 and v10. I CANNOT replicate this error on MariaDB v5.5.29 nor MySQL. I cannot be certain but based on the commit descriptions, it may be related to 2502.567.70, 2502.567.71, or 2502.567.72.
When the ISNULL() expression is present in the query below, mysqld seizes 100% of available physical memory and continues to allocate further until all available virtual memory is consumed as well, thereby hanging the machine:
1. Running the query as below, with or without EXPLAIN, causes an out-of-memory condition.
2. Commenting out the ISNULL(t4.c11) lets the query succeed.
3. Running SELECT * FROM t4 WHERE ISNULL(t4.c11) succeeds.
4. Removing various parts of the query suggest that this issue is only present with multiple joins and multiple WHERE conditions.
Further iterations suggest that all works until the ISNULL expression is added back to the query. I cannot pull the query plan due to the aforementioned out-of-memory condition.
SELECT t1.c1,t1.c2,t1.c3,t1.c4,t1.c5 FROM t1 JOIN t2 ON t2.c3=t1.c3 LEFT JOIN t3 ON t1.c1=t3.c1 LEFT JOIN t4 ON t4.c1=t3.c1 WHERE t1.c6=0 AND ((t1.c4=1 AND (TIMESTAMPDIFF(SECOND,t3.c7,NOW()) < 300 OR ((t2.c8 & 1) = 1))) OR t1.c4=0) AND (c9<>-1 OR (c9=-1 AND TIMESTAMPDIFF(SECOND,t3.c7,NOW()) < 300)) AND NOW()>=c10 AND (SELECT COUNT(*) FROM t1 dt1 WHERE dt1.c6=1 AND dt1.c3=t1.c3 AND dt1.c1=t1.c1) = 0 AND (t2.c12=0 OR (t2.c12=1 AND (SELECT COUNT(*) FROM t1 dt2 JOIN t2 s USING (c3) WHERE dt2.c1=t3.c1 AND t1.c6=1 AND t2.c12=1) = 0)) AND ( ( ISNULL(t4.c11) OR NOT t4.c13 IN (2,4) OR t2.c14=1 ) OR NOT ( t4.c13 IN (2,4) AND NOW()>t4.c11 AND NOW()<DATE_ADD(t4.c11, INTERVAL t4.c15 MINUTE) ) ) ORDER BY c16 DESC, t1.c10 LIMIT 500;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Excellent, thanks for the quick work! g 
Thanks for the report.
The bug was fixed along with
MDEV-4336andMDEV-4461by 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.
It has already been merged into maria/5.5 tree as revno 3746.
I re-checked your scenario to confirm that the fix covers it too. It fails as described on revno 3745 and passes all right on revno 3746.
The fix will appear in the upcoming 5.5.31 release and will be later merged into maria/10.0 and released with 10.0.3 as well.