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

Invalid execution plan for select from a view

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 5.5.31, 5.3.13
    • Component/s: None
    • Labels:
      None

      Description

      Create and populate tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16
      with the following commands:

      CREATE TABLE t1 (
        a1 int NOT NULL, a2 int NOT NULL, a3 int DEFAULT NULL, a4 tinyint NOT NULL,
        a5 int NOT NULL, a6 tinyint NOT NULL, a7 tinyint(4) DEFAULT NULL,
        a8 smallint(6) DEFAULT NULL, a9 smallint(6) DEFAULT NULL, a10 tinyint NOT NULL,
        PRIMARY KEY (a1), KEY a2 (a2), KEY a3 (a3), KEY a4 (a4), KEY a6 (a6),
        KEY a5 (a5), KEY a7 (a7), KEY a8 (a8), KEY a9 (a9)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t1 VALUES 
        (3360,5684,2219,1,316832,1,0,NULL,NULL,NULL),
        (3362,2754,597,2,316844,1,0,NULL,NULL,NULL),
        (3363,369,NULL,1,317295,1,0,NULL,NULL,NULL);
       
      CREATE TABLE t2 (
        b1 int NOT NULL, b2 int NOT NULL, PRIMARY KEY (b1,b2), KEY b2 (b2)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      CREATE TABLE t3 (
        c1 int NOT NULL, PRIMARY KEY (c1)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t3 VALUES
        (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
       (1000),(1001),(1002),(1003),(9999);
       
      CREATE TABLE t4 (
        d1 int NOT NULL, PRIMARY KEY (d1)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t4 VALUES (5674),(5676),(5680),(5684),(5685);
       
      CREATE TABLE t5 (
        e1 int NOT NULL, e2 varchar(64) NOT NULL, PRIMARY KEY (e1)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t5 VALUES
        (5684,'51a5de7a9f56314e082094d78f58be082c3cf0c1'),
        (5685,'754dc8292cb9f5eb9ade126fe7e961c62412a349'),
        (5686,'75eeb33f1c819bac21f6d023b4c5b24185eeda5c');
       
      CREATE TABLE t6 (
        f1 int NOT NULL, PRIMARY KEY (f1)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t6 VALUES (5542),(5620),(5686);
       
      CREATE TABLE t7 (
        g1 tinyint NOT NULL DEFAULT '0', g2 varchar(20) NOT NULL, PRIMARY KEY (g1)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      INSERT INTO t7 VALUES
        (1,'60feec2b20ed19f55ad0'),(3,'9ddb18bff7fcbd1e3133'),
        (5,'a05599df9222bb160d11'),(7,'e31bae372f7d01df0589'),
        (9,'8f8372dd7fc8eb46c8a3'),(11,'f8d0e28529e990a09309');
       
      CREATE TABLE t8 (
        h1 tinyint NOT NULL, h2 varchar(128) DEFAULT NULL, PRIMARY KEY (h1)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t8 VALUES (1,'b'),(2,'c'),(3,'d'),(4,'e');
       
      CREATE TABLE t9 (
        i1 tinyint NOT NULL, i2 varchar(7) NOT NULL, i3 varchar(128) NOT NULL,
        PRIMARY KEY (i1,i2), KEY i2 (i2), KEY i3 (i3)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t9 VALUES
        (2,'a','07630d223c7e5f7b1feb19b3caafb0833fd028eb'),
        (3,'b','1ca53dcc50b68af86f4b1b4676dbed917b543c30'),
        (1,'b','2c01ac36c1ce9a7de66be89f85d8aa5f0052e2e8'),
        (4,'a','496c486b3a9edc439477fef7d34cbefdebba86df'),
        (3,'a','98bf72d8d467201058a5f69bd7709bfc74a8637e'),
        (2,'b','9a45425f6160fb59d7f8a02c721498d4ce945302'),
        (4,'b','9c9a7300f3e708f8e430f9f3376d966f5951f583'),
        (1,'a','c0af3f076b905f31cbb51af304b9c7ad539e0861');
       
      CREATE TABLE t10 (
        j1 tinyint NOT NULL, j2 varchar(20) NOT NULL, PRIMARY KEY (j1)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t10 VALUES (1,'b'),(2,'c'),(3,'d');
       
      CREATE TABLE t11 (
        k1 int NOT NULL, k2 datetime DEFAULT NULL, k3 int DEFAULT NULL,
        k4 int DEFAULT NULL, PRIMARY KEY (k1), KEY k3 (k3), KEY k4 (k4)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t11 VALUES
        (317422,'2013-03-18 11:43:03',1,NULL),(317423,'2013-03-18 11:43:11',1,NULL),
        (317424,'2013-03-18 11:52:01',1,1),(317425,'2013-03-18 11:52:01',1,1),
        (317426,'2013-03-18 11:56:38',1,1),(317427,'2013-03-18 12:18:25',1,NULL),
        (317428,'2013-03-18 12:46:28',1,NULL),(317429,'2013-03-18 12:46:28',1,NULL),
        (317430,'2013-03-18 12:46:28',1,NULL),(317431,'2013-03-18 12:46:28',1,NULL),
        (317432,'2013-03-18 12:46:28',1,NULL),(317433,'2013-03-18 12:46:28',1,NULL),
        (317434,'2013-03-18 12:46:28',1,NULL),(317435,'2013-03-18 12:46:28',1,NULL),
        (317436,'2013-03-18 12:46:28',1,NULL),(317437,'2013-03-18 12:46:28',1,NULL),
        (317438,'2013-03-18 12:46:28',1,NULL),(317439,'2013-03-18 12:46:28',1,NULL),
        (317440,'2013-03-18 12:55:20',1,NULL),(317441,'2013-03-18 12:58:29',1,NULL),
        (317442,'2013-03-18 13:06:02',1,NULL),(317443,'2013-03-18 15:23:18',21,NULL);
       
      CREATE TABLE t12 (
        l1 int NOT NULL, l2 varchar(64) NOT NULL, PRIMARY KEY (l1)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t12 VALUES
        (552,'59a498252ef59f96fbdc13a414abe244d8e8bc30'),
        (554,'c6025c7cb2d9dfb1be7ce4a61f35b45bb9e61ba3'),
        (555,'b245bcc672082bb6d10794b2b4ac972dd14b1cf5');
       
      CREATE TABLE t13 (
        m1 int NOT NULL, m2 int NOT NULL, m3 int NOT NULL,
        PRIMARY KEY (m1,m2,m3), KEY m3 (m3), KEY m2 (m2)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t13 VALUES (3324,43,4),(3332,263,1),(3348,27,3);
       
      CREATE TABLE t14 (
        n1 smallint NOT NULL, n2 varchar(64) NOT NULL, PRIMARY KEY (n1)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t14 VALUES
        (21,'685bf7ca576af964c7cff564d5e4473b81499b8b'),
        (23,'b8e42dab1ab952406b3accfb47089c61478138a8'),
        (25,'3fea441e411db8c70bf039b50c8f18f59515be53'),
        (27,'998aecc30fd0e0b8a1cac6590e5eccc2d7822223');
       
      CREATE TABLE t15 (
        o1 smallint NOT NULL, PRIMARY KEY (o1)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t15 VALUES (1),(3);
       
      CREATE TABLE t16 (
        p1 smallint NOT NULL, p2 varchar(7) NOT NULL, p3 varchar(64) NOT NULL,
        PRIMARY KEY (p1,p2)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t16 VALUES
        (1,'a','66bdbb389456f3ae97206da115a7b397c31400e8'),
        (1,'b','66bdbb389456f3ae97206da115a7b397c31400e8'),
        (3,'a','386c10e454278c6e27feb16258089166422f79b4'),
        (3,'b','386c10e454278c6e27feb16258089166422f79b4');

      If to wrap the query $Q

      select t3.c1,t5.e2,t1.a1,t14.n2,t16.p3,t10.j2,t7.g2,t11.k2,l3.l2,
             t9.i3,t12.l2 AS l_l2,l2.l2 AS l2_l2,l4.l1 AS l4_l1,t6.f1
      from
      (
        (
          (
            (
              (
                (
                  (
                    (
                      (
                        (
                          (
                            (
                              (
                                (
                                  (
                                    (
                                      (
                                        (
                                          (
                                            t1
                                            left join t2 on t1.a1 = t2.b1
                                          ) 
                                          left join t3 on t2.b2 = t3.c1
                                        )
                                        left join t4 on t1.a2 = t4.d1
                                      )
                                      left join t5 on t4.d1 = t5.e1
                                   ) 
                                   left join t6 on t1.a3 = t6.f1
                                 ) 
                                 left join t5 e2 on t6.f1 = e2.e1
                               ) 
                               join t7 on t1.a7 = t7.g1
                             ) 
                             join t8 on t1.a4 = t8.h1
                           ) 
                           join t9 on t8.h1 = t9.i1
                         ) 
                         join t10 on t1.a6 = t10.j1
                       )
                       join t11 on t1.a5 = t11.k1
                     ) 
                     left join t12 on t11.k3 = t12.l1
                   ) 
                   left join t12 l2 on t11.k4 = l2.l1
                 ) 
                 left join t13 on t1.a1 = t13.m1 and t13.m3 = 4
               )
               left join t12 l4 on l4.l1 = t13.m2
             ) 
             left join t13 m2 on t1.a1 = m2.m1 and m2.m3 = 3
           )
           left join t12 l3 on l3.l1 = m2.m2
         )
         left join t14 on t1.a8 = t14.n1
       )
       left join t15 on t1.a9 = t15.o1
      )
      left join t16 on t15.o1 = t16.p1
      where t1.a10 = 1;

      into a view v1: create view v1 as $Q
      then the command
      explain select * from v1
      will return an apparently invalid execution plan:

      MariaDB [test]> explain select * from v1;
      +----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------------------------------------------+
      | id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra                                           |
      +----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------------------------------------------+
      |  1 | SIMPLE      | t1    | ALL    | a4,a6,a5,a7   | NULL    | NULL    | NULL        |    3 | Using where                                     |
      |  1 | SIMPLE      | t2    | ref    | PRIMARY       | PRIMARY | 4       | test.t1.a1  |    1 | Using index                                     |
      |  1 | SIMPLE      | t15   | eq_ref | PRIMARY       | PRIMARY | 2       | test.t1.a9  |    1 | Using where; Using index                        |
      |  1 | SIMPLE      | t6    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.a3  |    1 | Using where; Using index                        |
      |  1 | SIMPLE      | t13   | ref    | PRIMARY,m3    | PRIMARY | 4       | test.t1.a1  |    1 | Using where; Using index                        |
      |  1 | SIMPLE      | l4    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t13.m2 |    1 | Using where; Using index                        |
      |  1 | SIMPLE      | m2    | ref    | PRIMARY,m3    | PRIMARY | 4       | test.t1.a1  |    1 | Using where; Using index                        |
      |  1 | SIMPLE      | l3    | eq_ref | PRIMARY       | PRIMARY | 4       | test.m2.m2  |    1 | Using where                                     |
      |  1 | SIMPLE      | t8    | eq_ref | PRIMARY       | PRIMARY | 1       | test.t1.a4  |    1 | Using index                                     |
      |  1 | SIMPLE      | t14   | eq_ref | PRIMARY       | PRIMARY | 2       | test.t1.a8  |    1 | Using where                                     |
      |  1 | SIMPLE      | t16   | ref    | PRIMARY       | PRIMARY | 2       | test.t15.o1 |    1 | Using where                                     |
      |  1 | SIMPLE      | t4    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.a2  |    1 | Using index                                     |
      |  1 | SIMPLE      | t5    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t4.d1  |    1 | Using where                                     |
      |  1 | SIMPLE      | t7    | eq_ref | PRIMARY       | PRIMARY | 1       | test.t1.a7  |    1 |                                                 |
      |  1 | SIMPLE      | t9    | ref    | PRIMARY       | PRIMARY | 1       | test.t1.a4  |    1 |                                                 |
      |  1 | SIMPLE      | t3    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.b2  |    1 | Using where; Using index                        |
      |  1 | SIMPLE      | t11   | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.a5  |    1 |                                                 |
      |  1 | SIMPLE      | t12   | eq_ref | PRIMARY       | PRIMARY | 4       | test.t11.k3 |    1 | Using where                                     |
      |  1 | SIMPLE      | l2    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t11.k4 |    1 | Using where                                     |
      |  1 | SIMPLE      | t10   | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    3 | Using where; Using join buffer (flat, BNL join) |
      +----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------------------------------------------+
      20 rows in set (58.72 sec)

      For example, in any valid execution plan table t15 must be accessed after table t13.

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              igor Igor Babaev
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: