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

Wrong result with split materialized query

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.4.13
    • None
    • Optimizer
    • None
    • Q4/2026 Server Maintenance

    Description

      CREATE TABLE t1 (
      n1 int NOT NULL, n2 int NOT NULL, c1 char(1) NOT NULL,
      KEY c1 (c1), KEY n1_c1_n2 (n1,c1,n2), KEY n2k (n2)
      ) ENGINE=InnoDB CHARSET=latin1;
      INSERT INTO t1 VALUES (0,2,'a'), (1,3,'a');
      INSERT INTO t1 SELECT seq+1, seq+2, 'c' FROM seq_1_to_1000;
      CREATE TABLE t0 (a int, b int) ENGINE=InnoDB;
      INSERT INTO t0 VALUES (7,99),(1,3),(0,2);
      ANALYZE TABLE t0,t1;
      

      right result (semijoin)

      SELECT * FROM t0 WHERE t0.b IN
      (SELECT t.n2 FROM t1, (SELECT n1, n2 FROM t1 GROUP BY n1, n2) t
      WHERE t.n1 = t1.n1 AND t1.c1 = 'a');
      a	b
      1	3
      0	2
      

      wrong result (split-materialized)

      SELECT * FROM t0 WHERE t0.b IN
      (SELECT t.n2 FROM t1, (SELECT n1, n2 FROM t1 GROUP BY n1, n2) t
      WHERE t.n1 = t1.n1 AND t1.c1 = 'a' UNION select -1);
      Empty set (0.015 sec)
      

      Attachments

        Issue Links

          Activity

            People

              Johnston Rex Johnston
              Johnston Rex Johnston
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - 5d
                  5d
                  Remaining:
                  Remaining Estimate - 5d
                  5d
                  Logged:
                  Time Spent - Not Specified
                  Not Specified

                  Git Integration

                    Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.