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

Bad results with join comparing case insensitive VARCHAR/ENUM/SET expression to a _bin ENUM column

    XMLWordPrintable

Details

    Description

      CREATE TABLE t1 (a ENUM('a') CHARACTER SET latin1 PRIMARY KEY);
      INSERT INTO t1 VALUES ('a');
      CREATE TABLE t2 (a ENUM('a','A','b','B','c','C','d','D','e','E') CHARACTER SET latin1 COLLATE latin1_bin);
      INSERT INTO t2 VALUES ('a'),('A');
      -- without the following insert the bug doesn't show, was fixed in MDEV-6978
      INSERT INTO t2 VALUES ('b'),('B'),('c'),('C'),('d'),('D'),('e'),('E');
      ALTER TABLE t2 ADD PRIMARY KEY(a);
      SELECT t1.a res FROM t1 JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;
      SELECT t1.a res FROM t1 LEFT JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;
      DROP TABLE IF EXISTS t1,t2;
      

      result

      SELECT t1.a res FROM t1 JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;
      res
      a
      SELECT t1.a res FROM t1 LEFT JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;
      res
      a
      

      Correct result

      SELECT t1.a res FROM t1 JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;
      res
      a
      a
      SELECT t1.a res FROM t1 LEFT JOIN t2 ON t1.a COLLATE latin1_swedish_ci=t2.a;
      res
      a
      a
      

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              serg Sergei Golubchik
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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