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

Strange combination of JOIN with NULL crash the server

    Details

      Description

      I had updated from MariaDB 10.1.38 to MariaDB 10.3.13 via WHM. In the process it updated to MariaDB 10.2.23 first. After the process ends, a single application has bugged. After dig a lot I discover what happen, but not why.

      Run this code:

      # SETUP
      CREATE TABLE IF NOT EXISTS `test` (
          `id` INT(10) UNSIGNED NOT NULL,
          PRIMARY KEY (`id`)
      )
      ENGINE=InnoDB;
       
      # CRASHER CODE
      SELECT test.id
      FROM   test
       
      LEFT JOIN test AS join2
           LEFT JOIN test AS join3
                   LEFT JOIN test AS join4
                        ON NULL AND
                           join4.id
                ON FALSE
           ON FALSE
       
      LEFT JOIN test AS join1
           ON FALSE;
       
      # CLEAN
      DROP TABLE `test`;
      

      The original query is very long, so I tried to simplificate this test case to the best that as I could.

      If you run this code the server will crash with the following error:

      SQL Error (2013): Lost connection to MySQL server during query

      And the my.err will be (full log is attached):

      Thread pointer: 0x151f73f5d98
      mysqld.exe!JOIN::fix_all_splittings_in_plan()[opt_split.cc:1152]
      mysqld.exe!JOIN::optimize_inner()[sql_select.cc:1905]
      mysqld.exe!JOIN::optimize()[sql_select.cc:1453]
      mysqld.exe!mysql_select()[sql_select.cc:4225]
      mysqld.exe!handle_select()[sql_select.cc:373]
      mysqld.exe!execute_sqlcom_select()[sql_parse.cc:6551]
      mysqld.exe!mysql_execute_command()[sql_parse.cc:3772]
      

      I just run the server with: mysqld without any additional parameters.

      I did it based on my original code that crashes, so please consider the following changes (I did it to simplificate, but crashes happen anyway):

      • The original code have five tables and is used at ON clauses to make sense. But set all ON to FALSE still will crashes;
      • The original code have only a single AS because the join1 and join2 tables are the same. All other tables are not aliases because are differents tables;

      I did a lot of tests based on this simplified code that solves the crash, but will change the behaviour of query:

      • Dropping the PRIMARY_KEY from id will solve;
      • Dropping any JOIN or unwrapper it will solve;
      • Dropping NULL AND will solve;
      • Replacing NULL AND with NULL OR will solve;
      • Replacing join4.id to join3.id will solve;
      • Replacing NULL with any other value will solve;
      • Moving join1 over join2 will solve;
      • Moving join1 to inside of join2, join3 or join4 will solve;
      • Moving join2, join3 or join4 to inside of join1 will solve;
      • Using other engines than not InnoDB or MRG_MyISAM will solve;
      • Adding parentheses to JOIN will not solve;
      • Adding PRIMARY_KEY to id will not solve;
      • Swapping join4.id with NULL will not solve;
      • Replacing NULL with NOT NULL will not solve;

      I test some previous versions, and the problems starts in MariaDB 10.3.11 and still affects MariaDB 10.3.12 and MariaDB 10.3.13 (current).

      Actually, the problem seems to be related with the `NULL AND`. For some reason it crashes the server.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                igor Igor Babaev
                Reporter:
                rentalhost David Rodrigues
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated: