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

Wrong result from join with materialized semi-join and splittable derived

Details

    Description

      Under some circumstances a query with materialized subquery give an empty result.

      Testcase to reproduce

      It is likely a variant of MDEV-21614 , which is not fixed with MDEV-21328.

      SET optimizer_switch="split_materialized=on";
       
      drop table if EXISTS t1;
      drop table if exists t2 ;
       
       
      CREATE TABLE `t1` (
      `id` TINYINT(4) NOT NULL,
      `sint1` TINYINT(4) NOT NULL,
      `a1` MEDIUMINT(9) NOT NULL,
      `d1` DATE NOT NULL,
      PRIMARY KEY (`id`, `sint1`, `a1`, `d1`) USING BTREE,
      INDEX `idx0` (`a1`, `d1`) USING BTREE,
      INDEX `idx1` (`sint1`, `a1`, `d1`) USING BTREE
      )
      ;
       
      CREATE TABLE `t2` (
      `a1` MEDIUMINT(9) NOT NULL,
      `sint1` SMALLINT(6) NOT NULL,
      INDEX `idx` (`sint1`, `a1`) USING BTREE
      )
      ;
       
       
      INSERT INTO `t1` (
      with recursive series as (
      select 1 as id union all
      select id +1 as id from series
      where id < 5000)
      select 1,FLOOR(15 + (RAND() * 15)),id, subdate(NOW() ,INTERVAL (FLOOR(1 + (RAND() * 15)) ) DAY) from series);
       
       
       
      INSERT INTO `t2` (
      with recursive series as (
      select 1 as id union all
      select id +1 as id from series
      where id < 5000)
      select id,FLOOR(0 + (RAND() * 2)) from series);
       
      ANALYZE TABLE t1; #without it works
       
      DROP TEMPORARY TABLE IF EXISTS _Tmp;
      CREATE TEMPORARY TABLE _Tmp ( id_a1 INTEGER(8) ) ENGINE=MEMORY;
      INSERT INTO _Tmp (id_a1)
      VALUES (2500),(3000);
      SELECT *
      FROM
      d_maestros.t1 cp
      INNER JOIN
      (
      SELECT
      a1,
      MAX(d1) d1
      FROM
      d_maestros.t1 cp2
      GROUP BY
      a1) fe
      ON
      fe.a1 = cp.a1
      INNER JOIN
      _Tmp AS tmp_0
      ON
      cp.a1 = tmp_0.id_a1
      WHERE cp.a1 IN
      (
      SELECT
      a1
      FROM
      d_maestros.t2 dcp
       
      ); 
      

      It works fine with
      "SET optimizer_switch="split_materialized=off";"
      and/or
      without
      analyze table t1;

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment -

            Repeatable on 10.3-10.5, with Myisam/InnoDB (but to repeat with InnoDB there should be more data in t1, uncomment the second insert)

            create table t1 (id int, a1 int, index (a1), index (id, a1));
            insert into t1 values (17,1),(17,3010),(17,3013),(17,3053),(21,2446),(21,2467),(21,2);
            #insert into t1 values (17,3000),(17,3010),(17,3013),(17,3053),(17,3071),(17,3087),(17,3089),(17,3093),(17,3121),(17,3136),(17,3150),(17,3160),(17,3182),(17,3200),(17,3202),(17,3215),(17,3216),(17,3222),(17,3224),(17,3268),(17,3291),(17,3373),(17,3413),(17,3415),(17,3418),(17,3448),(17,3474),(17,3477),(17,3479),(17,3493),(17,3507),(17,3518),(17,3527),(17,3528),(17,3550),(17,3566),(17,3600),(17,3613),(17,3615),(17,3624),(17,3631),(17,3638),(17,3653),(17,3672),(17,3678),(17,3699),(17,3703),(17,3740),(17,3748),(17,3753),(17,3768),(17,3779),(17,3787),(17,3816),(17,3817),(17,3836),(17,3840),(17,3859),(17,3894),(17,3897),(17,3961),(17,3962),(17,3963),(17,3970),(17,4048),(17,4073),(17,4084),(17,4089),(17,4091),(17,4149),(17,4177),(17,4180),(17,4206),(17,4218),(17,4222),(17,4239),(17,4244),(17,4247),(17,4268),(17,4277),(17,4310),(17,4326),(17,4354),(17,4377),(17,4386),(17,4392),(17,4433),(17,4465),(17,4472),(17,4500),(17,4536),(17,4540),(17,4543),(17,4576),(17,4582),(17,4588),(17,4596),(17,4628),(17,4640),(17,4653),(17,4693),(17,4702),(17,4703),(17,4712),(17,4724),(17,4725),(17,4732),(17,4761),(17,4765),(17,4792),(17,4800),(17,4816),(17,4827),(17,4833),(17,4844),(17,4849),(17,4864),(17,4870),(17,4871),(17,4886),(17,4892),(17,4895),(17,4898),(17,4918),(17,4928),(17,4958),(17,4990),(21,28),(21,47),(21,49),(21,74),(21,83),(21,99),(21,113),(21,115),(21,140),(21,142),(21,173),(21,183),(21,185),(21,197),(21,208),(21,212),(21,239),(21,273),(21,277),(21,296),(21,333),(21,373),(21,380),(21,410),(21,434),(21,452),(21,471),(21,500),(21,501),(21,509),(21,535),(21,548),(21,555),(21,559),(21,603),(21,606),(21,612),(21,624),(21,636),(21,652),(21,669),(21,670),(21,712),(21,717),(21,732),(21,736),(21,739),(21,741),(21,742),(21,761),(21,772),(21,809),(21,813),(21,814),(21,839),(21,883),(21,885),(21,894),(21,913),(21,924),(21,928),(21,935),(21,959),(21,995),(21,1000),(21,1012),(21,1020),(21,1041),(21,1052),(21,1059),(21,1060),(21,1063),(21,1081),(21,1082),(21,1108),(21,1110),(21,1120),(21,1143),(21,1152),(21,1153),(21,1154),(21,1161),(21,1175),(21,1187),(21,1190),(21,1200),(21,1209),(21,1210),(21,1215),(21,1221),(21,1227),(21,1237),(21,1245),(21,1252),(21,1268),(21,1276),(21,1278),(21,1302),(21,1319),(21,1326),(21,1329),(21,1347),(21,1369),(21,1372),(21,1384),(21,1399),(21,1432),(21,1435),(21,1443),(21,1464),(21,1497),(21,1538),(21,1541),(21,1571),(21,1587),(21,1599),(21,1611),(21,1613),(21,1676),(21,1681),(21,1685),(21,1688),(21,1737),(21,1752),(21,1753),(21,1787),(21,1810),(21,1823),(21,1833),(21,1840),(21,1846),(21,1870),(21,1942),(21,1948),(21,1960),(21,2030),(21,2040),(21,2053),(21,2083),(21,2122),(21,2126),(21,2127),(21,2154),(21,2180),(21,2255),(21,2273),(21,2292),(21,2311),(21,2329),(21,2332),(21,2348),(21,2380),(21,2388),(21,2401),(21,2411),(21,2414),(21,2416),(21,2428),(21,2446),(21,2467),(21,2500),(21,2506),(21,2508),(21,2534),(21,2576),(21,2587),(21,2593),(21,2611),(21,2625),(21,2631),(21,2653),(21,2658),(21,2661),(21,2667),(21,2706),(21,2717),(21,2721),(21,2732),(21,2736),(21,2737),(21,2761),(21,2766),(21,2772),(21,2775),(21,2777),(21,2787),(21,2796),(21,2821),(21,2848),(21,2865),(21,2874),(21,2884),(21,2889),(21,2936),(21,2950),(21,2958),(21,2959),(21,2985),(21,3040),(21,3059),(21,3081),(21,3082),(21,3101),(21,3110),(21,3114),(21,3129),(21,3139),(21,3149),(21,3155),(21,3158),(21,3159),(21,3173),(21,3188),(21,3209),(21,3236),(21,3254),(21,3263),(21,3275),(21,3283),(21,3387),(21,3388),(21,3389),(21,3396),(21,3407),(21,3410),(21,3411),(21,3417),(21,3420),(21,3451),(21,3455),(21,3478),(21,3483),(21,3487),(21,3529),(21,3530),(21,3531),(21,3556),(21,3558),(21,3571),(21,3579),(21,3585),(21,3619),(21,3621),(21,3634),(21,3642),(21,3645),(21,3648),(21,3665),(21,3674),(21,3710),(21,3714),(21,3736),(21,3741),(21,3745),(21,3758),(21,3761),(21,3774),(21,3781),(21,3783),(21,3792),(21,3811),(21,3842),(21,3847),(21,3865),(21,3875),(21,3949),(21,3971),(21,3974),(21,3985),(21,3986),(21,3993),(21,4010),(21,4012),(21,4020),(21,4032),(21,4043),(21,4051),(21,4055),(21,4080),(21,4090),(21,4099),(21,4107),(21,4165),(21,4172),(21,4205),(21,4207),(21,4209),(21,4221),(21,4231),(21,4240),(21,4253),(21,4255),(21,4266),(21,4274),(21,4309),(21,4314),(21,4364),(21,4417),(21,4421),(21,4443),(21,4447),(21,4454),(21,4467),(21,4481),(21,4491),(21,4507),(21,4522),(21,4548),(21,4573),(21,4583),(21,4599),(21,4605),(21,4613),(21,4614),(21,4620),(21,4624),(21,4632),(21,4636),(21,4646),(21,4651),(21,4652),(21,4656),(21,4684),(21,4695),(21,4700),(21,4709),(21,4743),(21,4760),(21,4774),(21,4809),(21,4867),(21,4891),(21,4900),(21,4936),(21,4944),(21,4988),(22,2),(22,24),(22,39),(22,40),(22,44),(22,61),(22,70),(22,82),(22,93),(22,95),(22,144),(22,145),(22,162),(22,165),(22,167),(22,184),(22,213),(22,227),(22,244),(22,260),(22,262),(22,314),(22,326),(22,342),(22,343),(22,374),(22,383),(22,385),(22,387),(22,400),(22,417),(22,433),(22,437),(22,445),(22,446),(22,451),(22,459),(22,468),(22,493),(22,536),(22,554),(22,569),(22,576),(22,579),(22,592),(22,593),(22,600),(22,649),(22,656),(22,657),(22,658),(22,671),(22,680),(22,729),(22,740),(22,762),(22,766),(22,769),(22,802),(22,808),(22,832),(22,834),(22,902),(22,905),(22,945),(22,946),(22,983),(22,986),(22,999),(22,1033),(22,1062),(22,1083),(22,1090),(22,1102),(22,1111),(22,1160),(22,1184),(22,1196),(22,1220),(22,1224),(22,1244),(22,1273),(22,1285),(22,1297),(22,1303);
             
            analyze table t1; 
             
            create table t2 (a1 int);
            insert into t2 values (1),(2),(3);
            create  table t3 (id int);
            insert into t3 values (1),(2);
             
            set optimizer_switch="split_materialized=on";
             
            select * from t1  
             join (select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1
             join t3 on t1.a1 = t3.id
            where t1.a1 in (select a1 from t2);
             
            set optimizer_switch="split_materialized=off";
             
            select * from t1  
             join (select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1
             join t3 on t1.a1 = t3.id
            where t1.a1 in (select a1 from t2);
             
            drop table t1,t2,t3;
            

             
            MariaDB [test]> set optimizer_switch="split_materialized=on";
            Query OK, 0 rows affected (0.000 sec)
             
            MariaDB [test]> select * from t1  
                ->  join (select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1
                ->  join t3 on t1.a1 = t3.id
                -> where t1.a1 in (select a1 from t2);
            Empty set (0.005 sec)
             
            MariaDB [test]> set optimizer_switch="split_materialized=off";
            Query OK, 0 rows affected (0.000 sec)
             
            MariaDB [test]> select * from t1  
                ->  join (select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1
                ->  join t3 on t1.a1 = t3.id
                -> where t1.a1 in (select a1 from t2);
            +------+------+------+------+
            | id   | a1   | a1   | id   |
            +------+------+------+------+
            |   17 |    1 |    1 |    1 |
            |   21 |    2 |    2 |    2 |
            +------+------+------+------+
            2 rows in set (0.003 sec)
            

            MariaDB [test]> explain extended select * from t1    join (select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1  join t3 on t1.a1 = t3.id where t1.a1 in (select a1 from t2);
            +------+-----------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+
            | id   | select_type     | table       | type   | possible_keys | key          | key_len | ref        | rows | filtered | Extra                    |
            +------+-----------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+
            |    1 | PRIMARY         | t3          | ALL    | NULL          | NULL         | NULL    | NULL       | 2    |   100.00 | Using where              |
            |    1 | PRIMARY         | t1          | ref    | a1            | a1           | 5       | test.t3.id | 1    |   100.00 |                          |
            |    1 | PRIMARY         | <subquery3> | eq_ref | distinct_key  | distinct_key | 4       | func       | 1    |   100.00 |                          |
            |    1 | PRIMARY         | <derived2>  | ref    | key0          | key0         | 5       | test.t3.id | 2    |   100.00 |                          |
            |    3 | MATERIALIZED    | t2          | ALL    | NULL          | NULL         | NULL    | NULL       | 3    |   100.00 |                          |
            |    2 | LATERAL DERIVED | cp2         | ref    | a1            | a1           | 5       | test.t1.a1 | 1    |   100.00 | Using where; Using index |
            +------+-----------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+
            6 rows in set, 1 warning (0.002 sec)
             
            Note (Code 1003): /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a1` AS `a1`,`dt`.`a1` AS `a1`,`test`.`t3`.`id` AS `id` from `test`.`t1` semi join (`test`.`t2`) join (/* select#2 */ select `test`.`cp2`.`a1` AS `a1` from `test`.`t1` `cp2` where `test`.`cp2`.`a1` = `test`.`t1`.`a1` and `test`.`cp2`.`a1` = `test`.`t2`.`a1` and `test`.`cp2`.`a1` = `test`.`t3`.`id` group by `test`.`cp2`.`a1`) `dt` join `test`.`t3` where `test`.`t1`.`a1` = `test`.`t3`.`id` and `dt`.`a1` = `test`.`t3`.`id`
            MariaDB [test]> set optimizer_switch="split_materialized=off";
            Query OK, 0 rows affected (0.001 sec)
             
            MariaDB [test]> explain extended select * from t1    join (select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1  join t3 on t1.a1 = t3.id where t1.a1 in (select a1 from t2);
            +------+--------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+
            | id   | select_type  | table       | type   | possible_keys | key          | key_len | ref        | rows | filtered | Extra                    |
            +------+--------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+
            |    1 | PRIMARY      | t3          | ALL    | NULL          | NULL         | NULL    | NULL       | 2    |   100.00 | Using where              |
            |    1 | PRIMARY      | t1          | ref    | a1            | a1           | 5       | test.t3.id | 1    |   100.00 |                          |
            |    1 | PRIMARY      | <subquery3> | eq_ref | distinct_key  | distinct_key | 4       | func       | 1    |   100.00 |                          |
            |    1 | PRIMARY      | <derived2>  | ref    | key0          | key0         | 5       | test.t3.id | 2    |   100.00 |                          |
            |    3 | MATERIALIZED | t2          | ALL    | NULL          | NULL         | NULL    | NULL       | 3    |   100.00 |                          |
            |    2 | DERIVED      | cp2         | range  | NULL          | a1           | 5       | NULL       | 8    |   100.00 | Using index for group-by |
            +------+--------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+
            6 rows in set, 1 warning (0.006 sec)
             
            Note (Code 1003): /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a1` AS `a1`,`dt`.`a1` AS `a1`,`test`.`t3`.`id` AS `id` from `test`.`t1` semi join (`test`.`t2`) join (/* select#2 */ select `test`.`cp2`.`a1` AS `a1` from `test`.`t1` `cp2` group by `test`.`cp2`.`a1`) `dt` join `test`.`t3` where `test`.`t1`.`a1` = `test`.`t3`.`id` and `dt`.`a1` = `test`.`t3`.`id`
            

            alice Alice Sherepa added a comment - Repeatable on 10.3-10.5, with Myisam/InnoDB (but to repeat with InnoDB there should be more data in t1, uncomment the second insert) create table t1 (id int , a1 int , index (a1), index (id, a1)); insert into t1 values (17,1),(17,3010),(17,3013),(17,3053),(21,2446),(21,2467),(21,2); # insert into t1 values (17,3000),(17,3010),(17,3013),(17,3053),(17,3071),(17,3087),(17,3089),(17,3093),(17,3121),(17,3136),(17,3150),(17,3160),(17,3182),(17,3200),(17,3202),(17,3215),(17,3216),(17,3222),(17,3224),(17,3268),(17,3291),(17,3373),(17,3413),(17,3415),(17,3418),(17,3448),(17,3474),(17,3477),(17,3479),(17,3493),(17,3507),(17,3518),(17,3527),(17,3528),(17,3550),(17,3566),(17,3600),(17,3613),(17,3615),(17,3624),(17,3631),(17,3638),(17,3653),(17,3672),(17,3678),(17,3699),(17,3703),(17,3740),(17,3748),(17,3753),(17,3768),(17,3779),(17,3787),(17,3816),(17,3817),(17,3836),(17,3840),(17,3859),(17,3894),(17,3897),(17,3961),(17,3962),(17,3963),(17,3970),(17,4048),(17,4073),(17,4084),(17,4089),(17,4091),(17,4149),(17,4177),(17,4180),(17,4206),(17,4218),(17,4222),(17,4239),(17,4244),(17,4247),(17,4268),(17,4277),(17,4310),(17,4326),(17,4354),(17,4377),(17,4386),(17,4392),(17,4433),(17,4465),(17,4472),(17,4500),(17,4536),(17,4540),(17,4543),(17,4576),(17,4582),(17,4588),(17,4596),(17,4628),(17,4640),(17,4653),(17,4693),(17,4702),(17,4703),(17,4712),(17,4724),(17,4725),(17,4732),(17,4761),(17,4765),(17,4792),(17,4800),(17,4816),(17,4827),(17,4833),(17,4844),(17,4849),(17,4864),(17,4870),(17,4871),(17,4886),(17,4892),(17,4895),(17,4898),(17,4918),(17,4928),(17,4958),(17,4990),(21,28),(21,47),(21,49),(21,74),(21,83),(21,99),(21,113),(21,115),(21,140),(21,142),(21,173),(21,183),(21,185),(21,197),(21,208),(21,212),(21,239),(21,273),(21,277),(21,296),(21,333),(21,373),(21,380),(21,410),(21,434),(21,452),(21,471),(21,500),(21,501),(21,509),(21,535),(21,548),(21,555),(21,559),(21,603),(21,606),(21,612),(21,624),(21,636),(21,652),(21,669),(21,670),(21,712),(21,717),(21,732),(21,736),(21,739),(21,741),(21,742),(21,761),(21,772),(21,809),(21,813),(21,814),(21,839),(21,883),(21,885),(21,894),(21,913),(21,924),(21,928),(21,935),(21,959),(21,995),(21,1000),(21,1012),(21,1020),(21,1041),(21,1052),(21,1059),(21,1060),(21,1063),(21,1081),(21,1082),(21,1108),(21,1110),(21,1120),(21,1143),(21,1152),(21,1153),(21,1154),(21,1161),(21,1175),(21,1187),(21,1190),(21,1200),(21,1209),(21,1210),(21,1215),(21,1221),(21,1227),(21,1237),(21,1245),(21,1252),(21,1268),(21,1276),(21,1278),(21,1302),(21,1319),(21,1326),(21,1329),(21,1347),(21,1369),(21,1372),(21,1384),(21,1399),(21,1432),(21,1435),(21,1443),(21,1464),(21,1497),(21,1538),(21,1541),(21,1571),(21,1587),(21,1599),(21,1611),(21,1613),(21,1676),(21,1681),(21,1685),(21,1688),(21,1737),(21,1752),(21,1753),(21,1787),(21,1810),(21,1823),(21,1833),(21,1840),(21,1846),(21,1870),(21,1942),(21,1948),(21,1960),(21,2030),(21,2040),(21,2053),(21,2083),(21,2122),(21,2126),(21,2127),(21,2154),(21,2180),(21,2255),(21,2273),(21,2292),(21,2311),(21,2329),(21,2332),(21,2348),(21,2380),(21,2388),(21,2401),(21,2411),(21,2414),(21,2416),(21,2428),(21,2446),(21,2467),(21,2500),(21,2506),(21,2508),(21,2534),(21,2576),(21,2587),(21,2593),(21,2611),(21,2625),(21,2631),(21,2653),(21,2658),(21,2661),(21,2667),(21,2706),(21,2717),(21,2721),(21,2732),(21,2736),(21,2737),(21,2761),(21,2766),(21,2772),(21,2775),(21,2777),(21,2787),(21,2796),(21,2821),(21,2848),(21,2865),(21,2874),(21,2884),(21,2889),(21,2936),(21,2950),(21,2958),(21,2959),(21,2985),(21,3040),(21,3059),(21,3081),(21,3082),(21,3101),(21,3110),(21,3114),(21,3129),(21,3139),(21,3149),(21,3155),(21,3158),(21,3159),(21,3173),(21,3188),(21,3209),(21,3236),(21,3254),(21,3263),(21,3275),(21,3283),(21,3387),(21,3388),(21,3389),(21,3396),(21,3407),(21,3410),(21,3411),(21,3417),(21,3420),(21,3451),(21,3455),(21,3478),(21,3483),(21,3487),(21,3529),(21,3530),(21,3531),(21,3556),(21,3558),(21,3571),(21,3579),(21,3585),(21,3619),(21,3621),(21,3634),(21,3642),(21,3645),(21,3648),(21,3665),(21,3674),(21,3710),(21,3714),(21,3736),(21,3741),(21,3745),(21,3758),(21,3761),(21,3774),(21,3781),(21,3783),(21,3792),(21,3811),(21,3842),(21,3847),(21,3865),(21,3875),(21,3949),(21,3971),(21,3974),(21,3985),(21,3986),(21,3993),(21,4010),(21,4012),(21,4020),(21,4032),(21,4043),(21,4051),(21,4055),(21,4080),(21,4090),(21,4099),(21,4107),(21,4165),(21,4172),(21,4205),(21,4207),(21,4209),(21,4221),(21,4231),(21,4240),(21,4253),(21,4255),(21,4266),(21,4274),(21,4309),(21,4314),(21,4364),(21,4417),(21,4421),(21,4443),(21,4447),(21,4454),(21,4467),(21,4481),(21,4491),(21,4507),(21,4522),(21,4548),(21,4573),(21,4583),(21,4599),(21,4605),(21,4613),(21,4614),(21,4620),(21,4624),(21,4632),(21,4636),(21,4646),(21,4651),(21,4652),(21,4656),(21,4684),(21,4695),(21,4700),(21,4709),(21,4743),(21,4760),(21,4774),(21,4809),(21,4867),(21,4891),(21,4900),(21,4936),(21,4944),(21,4988),(22,2),(22,24),(22,39),(22,40),(22,44),(22,61),(22,70),(22,82),(22,93),(22,95),(22,144),(22,145),(22,162),(22,165),(22,167),(22,184),(22,213),(22,227),(22,244),(22,260),(22,262),(22,314),(22,326),(22,342),(22,343),(22,374),(22,383),(22,385),(22,387),(22,400),(22,417),(22,433),(22,437),(22,445),(22,446),(22,451),(22,459),(22,468),(22,493),(22,536),(22,554),(22,569),(22,576),(22,579),(22,592),(22,593),(22,600),(22,649),(22,656),(22,657),(22,658),(22,671),(22,680),(22,729),(22,740),(22,762),(22,766),(22,769),(22,802),(22,808),(22,832),(22,834),(22,902),(22,905),(22,945),(22,946),(22,983),(22,986),(22,999),(22,1033),(22,1062),(22,1083),(22,1090),(22,1102),(22,1111),(22,1160),(22,1184),(22,1196),(22,1220),(22,1224),(22,1244),(22,1273),(22,1285),(22,1297),(22,1303);   analyze table t1;   create table t2 (a1 int ); insert into t2 values (1),(2),(3); create table t3 (id int ); insert into t3 values (1),(2); set optimizer_switch= "split_materialized=on" ; select * from t1 join ( select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1 join t3 on t1.a1 = t3.id where t1.a1 in ( select a1 from t2);   set optimizer_switch= "split_materialized=off" ; select * from t1 join ( select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1 join t3 on t1.a1 = t3.id where t1.a1 in ( select a1 from t2);   drop table t1,t2,t3;   MariaDB [test]> set optimizer_switch="split_materialized=on"; Query OK, 0 rows affected (0.000 sec)   MariaDB [test]> select * from t1 -> join (select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1 -> join t3 on t1.a1 = t3.id -> where t1.a1 in (select a1 from t2); Empty set (0.005 sec)   MariaDB [test]> set optimizer_switch="split_materialized=off"; Query OK, 0 rows affected (0.000 sec)   MariaDB [test]> select * from t1 -> join (select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1 -> join t3 on t1.a1 = t3.id -> where t1.a1 in (select a1 from t2); +------+------+------+------+ | id | a1 | a1 | id | +------+------+------+------+ | 17 | 1 | 1 | 1 | | 21 | 2 | 2 | 2 | +------+------+------+------+ 2 rows in set (0.003 sec) MariaDB [test]> explain extended select * from t1 join (select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1 join t3 on t1.a1 = t3.id where t1.a1 in (select a1 from t2); +------+-----------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-----------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+ | 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | | 1 | PRIMARY | t1 | ref | a1 | a1 | 5 | test.t3.id | 1 | 100.00 | | | 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | test.t3.id | 2 | 100.00 | | | 3 | MATERIALIZED | t2 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | | | 2 | LATERAL DERIVED | cp2 | ref | a1 | a1 | 5 | test.t1.a1 | 1 | 100.00 | Using where; Using index | +------+-----------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+ 6 rows in set, 1 warning (0.002 sec)   Note (Code 1003): /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a1` AS `a1`,`dt`.`a1` AS `a1`,`test`.`t3`.`id` AS `id` from `test`.`t1` semi join (`test`.`t2`) join (/* select#2 */ select `test`.`cp2`.`a1` AS `a1` from `test`.`t1` `cp2` where `test`.`cp2`.`a1` = `test`.`t1`.`a1` and `test`.`cp2`.`a1` = `test`.`t2`.`a1` and `test`.`cp2`.`a1` = `test`.`t3`.`id` group by `test`.`cp2`.`a1`) `dt` join `test`.`t3` where `test`.`t1`.`a1` = `test`.`t3`.`id` and `dt`.`a1` = `test`.`t3`.`id` MariaDB [test]> set optimizer_switch="split_materialized=off"; Query OK, 0 rows affected (0.001 sec)   MariaDB [test]> explain extended select * from t1 join (select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1 join t3 on t1.a1 = t3.id where t1.a1 in (select a1 from t2); +------+--------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+ | 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where | | 1 | PRIMARY | t1 | ref | a1 | a1 | 5 | test.t3.id | 1 | 100.00 | | | 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | | | 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | test.t3.id | 2 | 100.00 | | | 3 | MATERIALIZED | t2 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | | | 2 | DERIVED | cp2 | range | NULL | a1 | 5 | NULL | 8 | 100.00 | Using index for group-by | +------+--------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+ 6 rows in set, 1 warning (0.006 sec)   Note (Code 1003): /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a1` AS `a1`,`dt`.`a1` AS `a1`,`test`.`t3`.`id` AS `id` from `test`.`t1` semi join (`test`.`t2`) join (/* select#2 */ select `test`.`cp2`.`a1` AS `a1` from `test`.`t1` `cp2` group by `test`.`cp2`.`a1`) `dt` join `test`.`t3` where `test`.`t1`.`a1` = `test`.`t3`.`id` and `dt`.`a1` = `test`.`t3`.`id`
            igor Igor Babaev added a comment -

            A fix for this bug was pushed into 10.3

            igor Igor Babaev added a comment - A fix for this bug was pushed into 10.3

            People

              igor Igor Babaev
              Richard Richard Stracke
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.