[MDEV-24360] MYISAM: Return wrong value with group by Created: 2020-12-07  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - MyISAM
Affects Version/s: 5.5, 10.0, 10.1, 10.3.23, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: nhokworm Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: MyISAM, innodb
Environment:

Debian 8.0 (Jessie)

Attachments: PNG File INNODB.png     PNG File MYISAM.png    
Issue Links:
Relates
relates to MDEV-31959 GROUP BY with GROUP_CONCAT breaks BIT... Confirmed

 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.

 Comments   
Comment by Alice Sherepa [ 2020-12-08 ]

Thank you for the report!
The problem is repeatable on 5.5-10.5.

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 ( id INT, a BIT, parent_id INT) engine=myisam;
 
INSERT INTO t1 (id, a, parent_id) VALUE (1, 1, NULL),(2, 0, 1),(3, 0, NULL);
 
 
--exec $MYSQL -e "SELECT pt.a, group_concat(ct.id) child_ids, pt.a div 1 FROM test.t1 pt LEFT JOIN test.t1 ct ON ct.parent_id = pt.id WHERE pt.parent_id IS NULL GROUP BY pt.id;"
 
alter table t1 engine=innodb;
 
--exec $MYSQL -e "SELECT pt.a, group_concat(ct.id) child_ids, pt.a div 1 FROM test.t1 pt LEFT JOIN test.t1 ct ON ct.parent_id = pt.id WHERE pt.parent_id IS NULL GROUP BY pt.id;"


CREATE TABLE t1 ( id INT, a BIT, parent_id INT) engine=myisam;
 
INSERT INTO t1 (id, a, parent_id) VALUE (1, 1, NULL),(2, 0, 1),(3, 0, NULL);
 
a	child_ids	pt.a div 1
 
\0	2	1
 
\0	NULL	0
 
alter table t1 engine=innodb;
 
a	child_ids	pt.a div 1
 
	2	1
 
\0	NULL	0

Comment by Elena Stepanova [ 2020-12-08 ]

The queries above (both in the description and in the comment) can cause some controversy because they use unmatching SELECT and GROUP BY lists (not all non-aggregated fields from SELECT are present in GROUP BY). In this case it doesn't matter, here is an example based on Alice's test which shows a similar problem while using GROUP BY accurately:

--source include/have_innodb.inc
 
 
CREATE TABLE t1 (id INT, a BIT) engine=myisam;
 
INSERT INTO t1 (id, a) VALUE (1, 1),(2, 0),(3, 0);
 
 
 
--exec $MYSQL -e "SELECT a, HEX(a) h, group_concat(id) ids FROM test.t1 GROUP BY a, h;"
 
alter table t1 engine=innodb;
 
--exec $MYSQL -e "SELECT a, HEX(a) h, group_concat(id) ids FROM test.t1 GROUP BY a, h;"


a	h	ids
 
	0	2,3
 
	1	1
 
alter table t1 engine=innodb;
 
a	h	ids
 
\0	0	2,3
 
	1	1

It doesn't quite explain why nhokworm is getting "0" vs "1" in the result set, maybe it's specific to the UI in use.

Comment by nhokworm [ 2020-12-08 ]

The above images is that I have taken from Mysql Workbench. Similarly, when I got results from Go, the value of flag also equals to 0.
So, I have used CAST(pt.flag AS INT) to fix this issue. Thanks to your support.

Generated at Thu Feb 08 09:29:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.