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

Wrong results in derived_view.test for reexuction of statement

    XMLWordPrintable

    Details

      Description

      This bug was introduced into 10.7, where I noticed the problem, in the merge commit f5c5f8e41ecd4d407022d3772ac43075c16824a5 (merge 10.5 -> 10.6)
      The problem may also be in earlier versions

      The test case of LP BUG#968720 shows wrong results.
      (Two lines of '1' are missing in the last select result).

      Here is an independent test case that shows the issue:
      (This does not have to be added in the fix as the derived_view.test will show if this is fixed)

      CREATE TABLE t1 (a int, INDEX(a));
      INSERT INTO t1 VALUES (1);

      CREATE TABLE t2 (a int, INDEX(a));
      INSERT INTO t2 VALUES (1), (2);

      explain INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN t2 AS s2;

      PREPARE stmt FROM "
      INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN t2 AS s2;
      ";
      EXECUTE stmt;
      EXECUTE stmt;
      SELECT * FROM t1;

      truncate table t1;
      INSERT INTO t1 VALUES (1);
      INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN t2 AS s2;
      INSERT INTO t1 SELECT a FROM (SELECT a FROM test.t1) AS s1 NATURAL JOIN t2 AS s2;
      SELECT * FROM t1;

      The last select has 4 result lines (correct) while the previous select has only 2 result lines.

      Note that in 10.7-selectivity, which is updated to latest 10.7 doesn't have this problem.
      (Don't know why, but it may be that some of the many bug fixes in it solved the issue or it is just using another execution plan that doesn't have this issue)

        Attachments

          Activity

            People

            Assignee:
            shulga Dmitry Shulga
            Reporter:
            monty Michael Widenius
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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