Details
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:
Attachments
Issue Links
- duplicates
-
MDEV-10774 weird result with myisam, group_concat and bit column
-
- Confirmed
-
- relates to
-
-
- Confirmed
-
- links to
