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

GROUP BY on a view does not sort properly

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Not a Bug
    • Affects Version/s: 5.5.30
    • Fix Version/s: 5.5.32
    • Component/s: None
    • Labels:
      None
    • Environment:
      Fedora 18

      Description

      DROP TABLE IF EXISTS t1;
      DROP VIEW IF EXISTS v1;
      CREATE TABLE t1 (name VARCHAR(32), race VARCHAR(32), number INT); 
      INSERT INTO t1 VALUES ('Bill','cat',1),('Donald','dog', 1),('Donald','fish',3),('John','dog',2),('Kevin','bird',6),('Kevin','cat',2),('Lisbeth','rabbit',2),('Mary','cat',1),('Mary','dog',1);
      CREATE VIEW v1 AS SELECT * FROM t1 ORDER BY race;
      SELECT name,race,sum(number) FROM t1 GROUP BY name,race;
      SELECT name,race,sum(number) FROM v1 GROUP BY name,race;

      The first SELECT query (from the table) returns results sorted by
      name,race as expected:

      +---------+--------+-------------+
      | name    | race   | sum(number) |
      +---------+--------+-------------+
      | Bill    | cat    |           1 |
      | Donald  | dog    |           1 |
      | Donald  | fish   |           3 |
      | John    | dog    |           2 |
      | Kevin   | bird   |           6 |
      | Kevin   | cat    |           2 |
      | Lisbeth | rabbit |           2 |
      | Mary    | cat    |           1 |
      | Mary    | dog    |           1 |
      +---------+--------+-------------+

      The second SELECT (from the view) sorts results by race:

      +---------+--------+-------------+
      | name    | race   | sum(number) |
      +---------+--------+-------------+
      | Kevin   | bird   |           6 |
      | Bill    | cat    |           1 |
      | Mary    | cat    |           1 |
      | Kevin   | cat    |           2 |
      | John    | dog    |           2 |
      | Donald  | dog    |           1 |
      | Mary    | dog    |           1 |
      | Donald  | fish   |           3 |
      | Lisbeth | rabbit |           2 |
      +---------+--------+-------------+

      This looks like a bug.

      According to the manual, a GROUP BY query should sort
      by the groupping fields by default, unless ORDER BY NULL
      is specified.

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration