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

Wrong result with GROUP BY and LEFT OUTER JOIN

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.8
    • 10.0.16
    • Optimizer
    • 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

              psergei Sergei Petrunia
              pivanof Pavel Ivanov
              Votes:
              12 Vote for this issue
              Watchers:
              18 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.