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

GROUP BY with GROUP_CONCAT breaks BIT field in MyISAM

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.11.5, 11.0.3, 10.4(EOL), 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
    • 10.5, 10.6, 10.11
    • Server
    • None
    • Debian bookworm on amd64

    Description

      If you run these queries on the same data:

      SELECT  PARTYID, ACCEPTED, GROUP_CONCAT(ASSOCID) AS ORGIDS
      FROM party_test
      LEFT JOIN connect_test
             ON MAINTYPE = "party"
            AND MAINID = PARTYID
            AND ASSOCTYPE = "organization"
      WHERE STAMP BETWEEN 1683277200 AND 1683363600
      GROUP BY PARTYID
      ORDER BY PARTYID;

      SELECT  PARTYID, ACCEPTED, ASSOCID AS ORGID
      FROM party_test
      LEFT JOIN connect_test
             ON MAINTYPE = "party"
            AND MAINID = PARTYID
            AND ASSOCTYPE = "organization"
      WHERE STAMP BETWEEN 1683277200 AND 1683363600
      ORDER BY PARTYID;

      Looking only at the top two rows, makes it clear, MariaDB somehow does strange things to the ACCEPTED bit field, since the horizontal bar (pipe character |) is shifted in the first result, signifying one row has b'1', other row has b'0', while the second top 2 results show they both should be b'0', no shifted bar:

      GROUP_CONCAT query:

      +---------+----------+----------------------------+
      | PARTYID | ACCEPTED | ORGIDS                     |
      +---------+----------+----------------------------+
      |  430921 |          | NULL                       |
      |  436261 |         | NULL                       |
       +---------+----------+----------------------------+

      Second query without GROUP_CONCAT:

      +---------+----------+-------+
      | PARTYID | ACCEPTED | ORGID |
      +---------+----------+-------+
      |  430921 |          |  NULL |
      |  436261 |          |  NULL |
      +---------+----------+-------+

      Can be solved by specifying 0+ACCEPTED in this case, but you have to continuously aware of the problem, and it changes the type of the field, which might not be desirable.

      Problem exists in both 10.11.5 and 11.0.3 which I tested.

      I've included a small test.sql with the two required tables and some test data to exactly replicate the above situation.

      I'm not entirely sure, but this problem does seem a bit similar to this very old MySQL bug:

      https://bugs.mysql.com/bug.php?id=30219

      Attachments

        1. MDEV-31959.sql
          0.2 kB
          Alice Sherepa
        2. test.sql
          16 kB
          Thomas van Gulick

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              t0mm1e Thomas van Gulick
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.