Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4498

Out-of-Memory errors when planning query with ISNULL in predicates

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.2, 5.5.30
    • 10.0.3, 5.5.31, 5.3.13
    • None
    • Windows Server 2008 x64

    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;

      Attachments

        Activity

          People

            Unassigned Unassigned
            ryan99fl Ryan Southwell
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.