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

WHERE filter is ignored by DISTINCT IFNULL(GROUP_CONCAT(X), Y) with GROUP BY + ORDER BY

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.5, 10.2, 10.3, 10.4
    • Fix Version/s: 10.2.24, 10.3.15, 10.4.5
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      docker container

      Description

      Run the attached sql and you will find a query that returns rows that where explicitly filtered.

      … WHERE group = 2 …
      group	values
      1	A
      2	B
      3	1
      

      The issue relates to versions, including 10.2.[5-18] 10.3 10.4 but not 10.0.[26-38], 10.1 and 5.5 (tested with the latest docker releases). It does not appear in all mysql versions (5.5, 5.6, 5.7) but mysql_5.7 returns the following error:

      ERROR 1055 (42000) at line 28: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'test.bug.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
      

      (line 28 is the beginning of the SELECT stmt.

        Attachments

        1. test.sql
          0.8 kB
          Berengar Lehr

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            Berengar Berengar Lehr
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: