Bug #69470 Use "group by", but no aggregate function, get weird result

• Submitted: 14 Jun 2013 9:33
• Modified: 14 Jun 2013 13:08
• Reporter: Yves Yuan
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6.10
• OS: Linux
• CPU Architecture: Any
• Tags: GROUP BY, no Aggregate function, null, weird result

[14 Jun 2013 9:33] Yves Yuan

Description:
In group by statement, if we query some column but without aggregate function, we should get the first value in the group.

But in 5.6.10, sometimes we just get null.

How to repeat:
CREATE TABLE test.self_ref_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ref_id INT NOT NULL
)ENGINE = INNODB DEFAULT CHARSET utf8;

Use this statement to insert 50 records.(May be we need more to repeat this problem)
INSERT INTO test.self_ref_table(ref_id) VALUES (ROUND(RAND() * 50));

SELECT
a.id, a.ref_id, b.id
FROM
test.self_ref_table a
LEFT JOIN
test.self_ref_table b
ON a.ref_id = b.id
GROUP BY a.id

I can get one record with null value of "b.id", which should be not null.

[14 Jun 2013 11:06] Jørgen Løland

Verified as described. Possibly a duplicate of BUG#68897

[14 Jun 2013 11:08] Jørgen Løland

CREATE TABLE self_ref_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ref_id INT NOT NULL
)ENGINE = INNODB DEFAULT CHARSET utf8;
INSERT INTO self_ref_table(ref_id) VALUES (ROUND(RAND() * 50));
INSERT INTO self_ref_table(ref_id) VALUES (ROUND(RAND() * 50));
...

SELECT a.id, a.ref_id, b.id
FROM self_ref_table a LEFT JOIN self_ref_table b ON a.ref_id = b.id
GROUP BY a.id;
id      ref_id  id
1       8       8
...
47      45      45                 <- there obviously is a row with id=47
48      47      NULL               <- but here we seemingly get no join match

[14 Jun 2013 13:08] Jørgen Løland

Confirmed duplicate of BUG#68897