[MDEV-31959] GROUP BY with GROUP_CONCAT breaks BIT field in MyISAM Created: 2023-08-19  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 10.11.5, 11.0.3
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0

Type: Bug Priority: Major
Reporter: Thomas van Gulick Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Debian bookworm on amd64

Attachments: File MDEV-31959.sql     File test.sql    
Issue Links:
Duplicate
duplicates MDEV-10774 weird result with myisam, group_conca... Confirmed
Relates
relates to MDEV-24360 MYISAM: Return wrong value with group by Confirmed

 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

 Comments   
Comment by Alice Sherepa [ 2023-09-19 ]

Thank you very much! I repeated on 10.4-11.1 with Myisam and Aria, InnoDB returns correct result.
I simplified a test, attached as MDEV-31959.sql
Please run it as mysql --binary-as-hex :

MariaDB [test]> SELECT  id,b, GROUP_CONCAT(b) FROM t1 GROUP BY id;
 
+------+------------+------+
 
| id   | b          | GROUP_CONCAT(b)                  |
 
+------+------------+------+
 
|    1 | 0x00       | 0x00                             |
 
|    2 | 0x01       | 0x00                             |
 
|    3 | 0x01       | 0x01                             |
 
+------+------------+------+
 
3 rows in set (0,000 sec)
 
 
 
MariaDB [test]> SELECT  id,b FROM t1 GROUP BY id;
 
+------+------------+
 
| id   | b          |
 
+------+------------+
 
|    1 | 0x00       |
 
|    2 | 0x00       |
 
|    3 | 0x01       |
 
+------+------------+
 
3 rows in set (0,000 sec)

Note that for id=2 b=0X01 , while it is in fact should be 0x00

Generated at Thu Feb 08 10:27:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.