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

INNER JOIN returns incorrect results

    XMLWordPrintable

Details

    Description

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

      How to repeat:

      CREATE TABLE t0(c0 TINYTEXT) ;
      		 
      CREATE TABLE t1 LIKE t0;
      		 
      CREATE INDEX i1 ON t0(c0(1) ASC);
      		 
      INSERT INTO t1(c0) VALUES(19), (6);
      		 
      INSERT INTO t0(c0) VALUES("㏫"), (61);
      		 
      INSERT INTO t0(c0) VALUES(29);
      		 
      INSERT INTO t0(c0) VALUES(99);
      		 
       
      		 
      SELECT * FROM t0; -- get [㏫,61,29,99]
      		 
      SELECT * FROM t1; -- get [19,6]
      		 
       
      		 
      SELECT ALL t1.c0 AS ref0 FROM t1 INNER JOIN t0 ON t1.c0 >= t0.c0; -- get[6,6], but expect [19,6,6]

      Suggested fix:
      The problem may lie in the optimization of INNER JOIN by storage engine InnoDB.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8625 Bad result set with ignorable characters when using a prefix key

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8626 Bad result set with expansions when using a prefix key

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20884 Different resultset when using prefix index and utf8mb4

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34006 Incorrect query result

          • Critical - Crashes, loss of data, severe memory leak.
          • Confirmed

          Activity

            People

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