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

Erroneous results with Left Outer Join temporary table text fields

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 5.3.12, 5.5.33a
    • 5.5.34, 5.3.13
    • None
    • None

    Description

      drop table if exists t1;
      create table t1 (
       id text not null
       ,qty int not null
      ) engine=innodb charset=utf8;
      insert into t1(id,qty) values ('a',2),('a',2),('a',3),('a',4),('b',2),('c',1),('c',2);

      expected output

      select a.*, b.*
      from t1 a
      left outer join t1 b
      	on a.id = b.id
      	and a.qty = b.qty;

      +----+-----+------+------+
      | id | qty | id   | qty  |
      +----+-----+------+------+
      | a  |   2 | a    |    2 |
      | a  |   2 | a    |    2 |
      | a  |   2 | a    |    2 |
      | a  |   2 | a    |    2 |
      | a  |   3 | a    |    3 |
      | a  |   4 | a    |    4 |
      | b  |   2 | b    |    2 |
      | c  |   1 | c    |    1 |
      | c  |   2 | c    |    2 |
      +----+-----+------+------+
      9 rows in set (0.01 sec)

      buggy query

      select a.*, b.* from (
      	select
      	id, qty
      	from t1
      	group by id,qty
      ) a
      left outer join (
      	select
      	id, qty
      	from t1
      	group by id,qty
      ) b
      	on a.id = b.id
      	and a.qty = b.qty;

      +----+-----+------+------+
      | id | qty | id   | qty  |
      +----+-----+------+------+
      | a  |   2 | NULL | NULL |
      | a  |   3 | NULL | NULL |
      | a  |   4 | NULL | NULL |
      | b  |   2 | NULL | NULL |
      | c  |   1 | NULL | NULL |
      | c  |   2 | NULL | NULL |
      +----+-----+------+------+
      6 rows in set (0.01 sec)

      inner join not affected

      select a.*, b.* from (
      	select
      	id, qty
      	from t1
      	group by id,qty
      ) a
      inner join (
      	select
      	id, qty
      	from t1
      	group by id,qty
      ) b
      	on a.id = b.id
      	and a.qty = b.qty;

      +----+-----+----+-----+
      | id | qty | id | qty |
      +----+-----+----+-----+
      | a  |   2 | a  |   2 |
      | a  |   3 | a  |   3 |
      | a  |   4 | a  |   4 |
      | b  |   2 | b  |   2 |
      | c  |   1 | c  |   1 |
      | c  |   2 | c  |   2 |
      +----+-----+----+-----+
      6 rows in set (0.01 sec)

      strcmp resolves

      select a.*, b.* from (
      	select
      	id, qty
      	from t1
      	group by id,qty
      ) a
      left outer join (
      	select
      	id, qty
      	from t1
      	group by id,qty
      ) b
      	on strcmp(a.id,b.id) = 0
      	and a.qty = b.qty;

      +----+-----+------+------+
      | id | qty | id   | qty  |
      +----+-----+------+------+
      | a  |   2 | a    |    2 |
      | a  |   3 | a    |    3 |
      | a  |   4 | a    |    4 |
      | b  |   2 | b    |    2 |
      | c  |   1 | c    |    1 |
      | c  |   2 | c    |    2 |
      +----+-----+------+------+
      6 rows in set (0.01 sec)

      create identicial table with varchar id field

      drop table if exists t2;
      create table t2 (
       id varchar(255) not null
       ,qty int not null
      ) engine=innodb charset=utf8;
      insert into t2(id,qty) values ('a',2),('a',2),('a',3),('a',4),('b',2),('c',1),('c',2);

      buggy query is resolved with varchar

      select a.*, b.* from (
      	select
      	id, qty
      	from t2
      	group by id,qty
      ) a
      left outer join (
      	select
      	id, qty
      	from t2
      	group by id,qty
      ) b
      	on a.id = b.id
      	and a.qty = b.qty;

      +----+-----+------+------+
      | id | qty | id   | qty  |
      +----+-----+------+------+
      | a  |   2 | a    |    2 |
      | a  |   3 | a    |    3 |
      | a  |   4 | a    |    4 |
      | b  |   2 | b    |    2 |
      | c  |   1 | c    |    1 |
      | c  |   2 | c    |    2 |
      +----+-----+------+------+
      6 rows in set (0.01 sec)

      Attachments

        1. my.cnf
          2 kB
          Ryan Peterson

        Activity

          People

            Unassigned Unassigned
            ryanpeterson Ryan Peterson
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.