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

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              serg Sergei Golubchik
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration