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

Wrong result upon GROUP BY with orderby_uses_equalities=on

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.1
    • Fix Version/s: 10.6.0
    • Component/s: Optimizer
    • Labels:
      None

      Description

      Originally reported at StackOverflow

      Test case

      CREATE TABLE person (
        PersonID MEDIUMINT(8) UNSIGNED AUTO_INCREMENT,
        FullName VARCHAR(100),
        Furigana VARCHAR(100),
        Sex ENUM('M','F'),
        HouseholdID MEDIUMINT(8) UNSIGNED DEFAULT 0,
        Relation VARCHAR(6),
        Title VARCHAR(6),
        CellPhone VARCHAR(30),
        Email VARCHAR(70),
        Birtdate DATE DEFAULT '0000-00-00',
        Country VARCHAR(30),
        URL VARCHAR(150),
        Organization TINYINT(1) DEFAULT 0,
        Remarks TEXT,
        Photo TINYINT(1) DEFAULT 0,
        UpdDate DATE DEFAULT '0000-00-00',
        PRIMARY KEY (PersonID),
        INDEX(Furigana),
        INDEX(FullName),
        INDEX(Email),
        INDEX(Organization,Furigana)
      ) ;
       
      CREATE TABLE percat (
        PersonID MEDIUMINT(8) DEFAULT 0,
        CategoryID MEDIUMINT(8) DEFAULT 0,
        PRIMARY KEY (PersonID, CategoryID),
        INDEX (CategoryID)
      ) ;
       
      CREATE TABLE action (
        ActionID MEDIUMINT(8) UNSIGNED AUTO_INCREMENT,
        PersonID MEDIUMINT(8) UNSIGNED DEFAULT 0,
        ActionTypeID MEDIUMINT(8) UNSIGNED DEFAULT 0,
        ActionDate DATE DEFAULT '0000-00-00',
        Description TEXT,
        PRIMARY KEY (ActionID),
        INDEX (PersonID),
        INDEX (ActionDate),
        INDEX (ActionTypeID)
      ) ;
       
      INSERT INTO person (PersonID) VALUES 
      (58),(96),(273),(352);
       
      INSERT INTO percat VALUES 
      (58,9),(273,1),(273,9),(273,14),(352,1),(352,13);
       
      INSERT INTO action (PersonID, ActionTypeID) VALUES
      (58,3),(96,3),(273,3),(352,3);
       
      SELECT person.PersonID,
      GROUP_CONCAT(CategoryID ORDER BY CategoryID SEPARATOR ',') AS categories
      FROM person LEFT JOIN percat ON person.PersonID=percat.PersonID
      WHERE person.PersonID IN (SELECT PersonID FROM action WHERE ActionTypeID=3)
      GROUP BY person.PersonID;
       
      DROP TABLE action, percat, person;
      

      Result with orderby_uses_equalities=on

      SELECT person.PersonID,
      GROUP_CONCAT(CategoryID ORDER BY CategoryID SEPARATOR ',') AS categories
      FROM person LEFT JOIN percat ON person.PersonID=percat.PersonID
      WHERE person.PersonID IN (SELECT PersonID FROM action WHERE ActionTypeID=3)
      GROUP BY person.PersonID;
      PersonID	categories
      352	1,1,1,1,13,13,13,13
      

      Result with orderby_uses_equalities=off

      SELECT person.PersonID,
      GROUP_CONCAT(CategoryID ORDER BY CategoryID SEPARATOR ',') AS categories
      FROM person LEFT JOIN percat ON person.PersonID=percat.PersonID
      WHERE person.PersonID IN (SELECT PersonID FROM action WHERE ActionTypeID=3)
      GROUP BY person.PersonID;
      PersonID	categories
      58	9
      96	NULL
      273	1,9,14
      352	1,13
      

      The second result is correct.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              varun Varun Gupta (Inactive)
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: