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

INNER JOIN returns incorrect results

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.5.1, 11.6.0
    • 10.5, 10.6, 11.2, 11.4
    • None
    • Ubuntu 20.04.4 LTS (x86)
      Kernel Version: 5.15.0-113-generic

    Description

      Description:
      When I use INNER JOIN to join two tables, I get the wrong result.

      How to repeat:

      CREATE TABLE t0(c0 DOUBLE PRIMARY KEY) ENGINE = MEMORY;
      CREATE TABLE IF NOT EXISTS t1 LIKE t0;
      INSERT INTO t1(c0) VALUES('-0.0');
      INSERT IGNORE INTO t0(c0) VALUES("0");
      INSERT LOW_PRIORITY IGNORE INTO t0(c0) VALUES(1);
       
      SELECT * FROM t0; -- get [0, 1]
      SELECT * FROM t1; -- get [-0]
       
      SELECT ALL t0.c0 AS ref0 FROM t0 INNER JOIN t1 ON t0.c0 = t1.c0; -- get Empty set, but expect [0]
      

      Suggested fix:
      I changed the storage engine to InnoDB, and the result was correct. The problem seemed to be caused by MEMORY.

      Attachments

        Issue Links

          Activity

            People

              Johnston Rex Johnston
              Chenglin Liang Chenglin Liang
              Votes:
              2 Vote for this issue
              Watchers:
              5 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.