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

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

    XMLWordPrintable

    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

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              Richard Richard Stracke
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: