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

Crash with splitting from constant mergeable derived table

Details

    Description

      Select from complex view using where clause gets SIGNAL 11 crash with derived_merge=on in versions 10.6 - 10.9. The issue does not happen in 10.5 or 10.10. If derived_merge=off then the issue does not happen. If derived_merge=on and there is no where clause used against the view, the issue does not happen. The statement below causes the issue every time when derived_merge=on.

      A customer reported this and does not want to have to turn derived_merge off because they don't want to lose out on query performance. They noticed the issue after upgrading from 10.5 to 10.6. I tested out the other versions to verify the scope of the issue.
      Recreation steps:

      1. Load the attached sql script to create the schema, and populate it with data
      2. Run the below select statement
        This should return

        ERROR 2013 (HY000): Lost connection to server during query
        

        In the error log it reports a SIGNAL 11.

      SELECT this_.WORKFLOW_ID as y0_, this_.WORKFLOW_TYPE_ID as y1_, this_.COMPLETED_BY as y2_, this_.STATUS as y3_, this_.WORKFLOW as y4_, this_.WORKFLOW_NAME as y5_ 
      FROM ABC_CAM_VW_WORKFLOW_TAB this_ 
      WHERE this_.WORKFLOW_ID = 7 
      ORDER BY y0_ ASC 
      LIMIT 1; 
      

      Attachments

        1. CS0457258.err
          9 kB
        2. MDEV-30081.test
          583 kB
        3. schema.sql
          620 kB

        Issue Links

          Activity

            igor Igor Babaev added a comment -

            It would be better if the test case provided with the fix looked like this:

              CREATE TABLE t1 ( id int PRIMARY KEY ) ENGINE=MyISAM;
            INSERT INTO t1 VALUES (3),(4),(7);
             
            CREATE TABLE t2 (
              id int,  id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid)
            ) ENGINE=MyISAM;
            INSERT INTO t2 VALUES (4,4,6),(7,7,7);
             
            CREATE TABLE t3 (
              wid int, wtid int, otid int, oid int,
              PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid)
            ) ENGINE=MyISAM;
            INSERT INTO t3 VALUES (6,30,6,6),(7,17,7,7);
             
            CREATE TABLE t4 ( id int, a int, PRIMARY KEY (id), KEY (a) )  ENGINE=MyISAM;
            INSERT INTO t4 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
             
            CREATE TABLE t5 (
              id int, id1 int, PRIMARY KEY (id), KEY id1 (id1)
            ) ENGINE=MyISAM ;
            INSERT INTO t5 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
             
            ANALYZE TABLE t1,t2,t3,t4,t5;
             
            CREATE VIEW v1 AS (SELECT id1 FROM t5 GROUP BY id1);
             
            SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.*
            FROM 
              t1, t2, t3
              LEFT JOIN
              (SELECT t4.* FROM t4 WHERE t4.a=3) dt
              ON t3.oid = dt.id AND t3.otid = 14
              LEFT JOIN v1
              ON (v1.id1 = dt.a)
            WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7;
             
            EXPLAIN
            SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.*
            FROM 
              t1, t2, t3
              LEFT JOIN
              (SELECT t4.* FROM t4 WHERE t4.a=3) dt
              ON t3.oid = dt.id AND t3.otid = 14
              LEFT JOIN v1
              ON (v1.id1 = dt.a)
            WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7;
             
            DROP VIEW v1;
            DROP TABLE t1,t2,t3,t4,t5;
            

            igor Igor Babaev added a comment - It would be better if the test case provided with the fix looked like this: CREATE TABLE t1 ( id int PRIMARY KEY ) ENGINE=MyISAM; INSERT INTO t1 VALUES (3),(4),(7);   CREATE TABLE t2 ( id int , id1 int , wid int , PRIMARY KEY (id), KEY (id1), KEY (wid) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (4,4,6),(7,7,7);   CREATE TABLE t3 ( wid int , wtid int , otid int , oid int , PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid) ) ENGINE=MyISAM; INSERT INTO t3 VALUES (6,30,6,6),(7,17,7,7);   CREATE TABLE t4 ( id int , a int , PRIMARY KEY (id), KEY (a) ) ENGINE=MyISAM; INSERT INTO t4 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);   CREATE TABLE t5 ( id int , id1 int , PRIMARY KEY (id), KEY id1 (id1) ) ENGINE=MyISAM ; INSERT INTO t5 VALUES (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);   ANALYZE TABLE t1,t2,t3,t4,t5;   CREATE VIEW v1 AS ( SELECT id1 FROM t5 GROUP BY id1);   SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.* FROM t1, t2, t3 LEFT JOIN ( SELECT t4.* FROM t4 WHERE t4.a=3) dt ON t3.oid = dt.id AND t3.otid = 14 LEFT JOIN v1 ON (v1.id1 = dt.a) WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7;   EXPLAIN SELECT t3.*, t1.id AS t1_id, t2.id AS t2_id, dt.*, v1.* FROM t1, t2, t3 LEFT JOIN ( SELECT t4.* FROM t4 WHERE t4.a=3) dt ON t3.oid = dt.id AND t3.otid = 14 LEFT JOIN v1 ON (v1.id1 = dt.a) WHERE t3.oid = t1.id AND t3.oid = t2.id AND t3.wid = 7;   DROP VIEW v1; DROP TABLE t1,t2,t3,t4,t5;
            igor Igor Babaev added a comment -

            psergei
            See my analysis in my comments. If you don't mind I could take this bug.

            igor Igor Babaev added a comment - psergei See my analysis in my comments. If you don't mind I could take this bug.

            igor yes, please take over.

            psergei Sergei Petrunia added a comment - igor yes, please take over.

            OK to push

            sanja Oleksandr Byelkin added a comment - OK to push
            igor Igor Babaev added a comment -

            A fix for this bug was pushed into 10.3. It should be merged upstream as it is.

            igor Igor Babaev added a comment - A fix for this bug was pushed into 10.3. It should be merged upstream as it is.

            People

              igor Igor Babaev
              kyle.hutchinson Kyle Hutchinson
              Votes:
              1 Vote for this issue
              Watchers:
              8 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.