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

MYISAM: Return wrong value with group by

    XMLWordPrintable

    Details

      Description

      I have created the following table with MYISAM engine:

      CREATE DATABASE IF NOT EXISTS test_db;
       
      CREATE TABLE IF NOT EXISTS test_db.test (
          id                				INT,
      	flag			                BIT,
          parent_id						INT,
          PRIMARY KEY (id)
      )  ENGINE=MYISAM;
       
      INSERT INTO test_db.test
      (id, flag, parent_id)
      VALUE
      (1, 1, NULL),
      (2, 0, 1),
      (3, 0, NULL)
      ON DUPLICATE KEY UPDATE
      	flag = VALUES(flag);
       
      SELECT
          pt.id,
          pt.flag,
          GROUP_CONCAT(ct.id) child_ids
      FROM test_db.test pt
      LEFT JOIN test_db.test ct ON ct.parent_id = pt.id
      WHERE
      	pt.parent_id IS NULL
      GROUP BY
      	pt.id
      

      Please see the attached image for the result. But when I run this sql with the INNODB table, I get difference value of flag column. I think the sql with INNODB returned correct records.

        Attachments

          Activity

            People

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

              Dates

              Created:
              Updated: