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

Wrong result with GROUP BY and LEFT OUTER JOIN

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.8
    • Fix Version/s: 10.0.16
    • Component/s: Optimizer
    • Labels:
      None

      Description

      Here's the mysql session log:

      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 5
      Server version: 10.0.8-MariaDB-log Source distribution
       
      Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> create database test;
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [(none)]> use test;
      Database changed
      MariaDB [test]> CREATE TABLE t (id bigint(20), name varchar(30),
      parent bigint(20), primary key (id)) ENGINE=InnoDB;
      Query OK, 0 rows affected (0.27 sec)
       
      MariaDB [test]> INSERT INTO t values (1, 'a', NULL), (4, 'b', 1), (5,
      'c', 4), (10, 'd', 1);
      Query OK, 4 rows affected (0.04 sec)
      Records: 4  Duplicates: 0  Warnings: 0

      MariaDB [test]> SELECT t1.id, t1.name, t2.id, t2.name FROM t t1 LEFT
      OUTER JOIN t t2 ON (t1.parent = t2.id and t2.name <> 'a') GROUP BY
      t1.id;
      +----+------+------+------+
      | id | name | id   | name |
      +----+------+------+------+
      |  1 | a    | NULL | NULL |
      |  4 | b    | NULL | NULL |
      |  5 | c    | NULL | NULL |
      | 10 | d    | NULL | NULL |
      +----+------+------+------+
      4 rows in set (0.00 sec)

      MariaDB [test]> SELECT t1.id, t1.name, t2.id, t2.name FROM t t1 LEFT
      OUTER JOIN t t2 ON (t1.parent = t2.id and t2.name <> 'a');
      +----+------+------+------+
      | id | name | id   | name |
      +----+------+------+------+
      |  1 | a    | NULL | NULL |
      |  4 | b    | NULL | NULL |
      |  5 | c    |    4 | b    |
      | 10 | d    | NULL | NULL |
      +----+------+------+------+
      4 rows in set (0.00 sec)

      MariaDB [test]> SELECT t1.id, t1.name, t2.id, t2.name FROM t t1 LEFT
      OUTER JOIN t t2 ON (t1.parent = t2.id and t2.name <> 'a') GROUP BY
      t1.id, t1.name;
      +----+------+------+------+
      | id | name | id   | name |
      +----+------+------+------+
      |  1 | a    | NULL | NULL |
      |  4 | b    | NULL | NULL |
      |  5 | c    |    4 | b    |
      | 10 | d    | NULL | NULL |
      +----+------+------+------+
      4 rows in set (0.00 sec)

      MariaDB [test]> SELECT t1.id, t1.name, t2.id, t2.name FROM t t1 LEFT
      OUTER JOIN t t2 ON (t1.parent = t2.id) GROUP BY t1.id;
      +----+------+------+------+
      | id | name | id   | name |
      +----+------+------+------+
      |  1 | a    | NULL | NULL |
      |  4 | b    |    1 | a    |
      |  5 | c    |    4 | b    |
      | 10 | d    |    1 | a    |
      +----+------+------+------+
      4 rows in set (0.00 sec)

      As you see the first query returns NULLs instead of real values which
      can be seen in second query without GROUP BY. And if I add another
      column to GROUP BY or remove one condition from JOIN I can see real
      values again. I've also tested with the table without PRIMARY KEY and
      it also returned real values in the first query.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              pivanof Pavel Ivanov
              Votes:
              12 Vote for this issue
              Watchers:
              18 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: