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

Wrong result upon GROUP BY with orderby_uses_equalities=on

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.1, 10.2, 10.3, 10.4, 10.5
    • 10.6.0
    • Optimizer
    • 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

              varun Varun Gupta (Inactive)
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.