| Bug #70337 | SELECT returning inappopriate results | ||
|---|---|---|---|
| Submitted: | 13 Sep 2013 16:05 | Modified: | 13 Sep 2013 23:59 |
| Reporter: | Saverio Miroddi | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.6.13 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Sep 2013 23:59]
MySQL Verification Team
Thank you for the bug report. Please read: http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html "MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. .........A similar MySQL extension applies to the HAVING clause......" c:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --prompt="mysql 5.6 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.14 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.6 > CREATE DATABASE D4; Query OK, 1 row affected (0.01 sec) mysql 5.6 > USE D4 Database changed mysql 5.6 > CREATE TABLE _parents ( -> id int NOT NULL AUTO_INCREMENT, -> section_id int, -> aggregate_sum decimal(8,2), -> PRIMARY KEY (id), -> KEY index_parents_on_section_id (section_id) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.45 sec) mysql 5.6 > INSERT INTO _parents VALUES (2,5,50.00),(3,5,50.00),(5,5,50.00),(6,5,20.00),(7,5,10.00),(8,5,10.00),(9,5,10.00),(10,5,20.00); Query OK, 8 rows affected (0.03 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql 5.6 > CREATE TABLE _childs ( -> id int NOT NULL AUTO_INCREMENT, -> value decimal(8,2), -> parent_id int, -> PRIMARY KEY (id), -> KEY index_childs_on_parent_id (parent_id) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.48 sec) mysql 5.6 > INSERT INTO _childs VALUES (17345,36.00,3),(17373,14.00,3),(18274,20.00,2),(18577,30.00,2),(21102,20.00,6),(21113,41.00,5),(21786,9.00,5),(22342,20.00,10),(22662,10.00,8),(22663, 10.00,7),(22671,10.00,9); Query OK, 11 rows affected (0.05 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql 5.6 > SELECT r.id _id, -> r.section_id _section_id, -> CAST( r.aggregate_sum AS DECIMAL(10,2))_aggregate_sum, -> SUM( p.value ) _childs_value -> FROM _parents r -> JOIN _childs p ON r.id = p.parent_id -> GROUP BY r.id -> HAVING _aggregate_sum != _childs_value -> ORDER BY _section_id -> ; +-----+-------------+----------------+---------------+ | _id | _section_id | _aggregate_sum | _childs_value | +-----+-------------+----------------+---------------+ | 5 | 5 | 50.00 | 50.00 | | 6 | 5 | 20.00 | 20.00 | | 9 | 5 | 10.00 | 10.00 | +-----+-------------+----------------+---------------+ 3 rows in set (0.00 sec) mysql 5.6 > SET SQL_MODE = ONLY_FULL_GROUP_BY; Query OK, 0 rows affected (0.00 sec) mysql 5.6 > SELECT r.id _id, -> r.section_id _section_id, -> CAST( r.aggregate_sum AS DECIMAL(10,2))_aggregate_sum, -> SUM( p.value ) _childs_value -> FROM _parents r -> JOIN _childs p ON r.id = p.parent_id -> GROUP BY r.id -> HAVING _aggregate_sum != _childs_value -> ORDER BY _section_id -> ; ERROR 1055 (42000): 'd4.r.section_id' isn't in GROUP BY mysql 5.6 >
Description: With a specific data set, a certain query returns a result that I can't explain. Minor changes to the query, which don't change its logic, yield the correct result. How to repeat: DROP TABLE IF EXISTS _parents; CREATE TABLE _parents ( id int NOT NULL AUTO_INCREMENT, section_id int, aggregate_sum decimal(8,2), PRIMARY KEY (id), KEY index_parents_on_section_id (section_id) ) ENGINE=InnoDB; INSERT INTO _parents VALUES (2,5,50.00),(3,5,50.00),(5,5,50.00),(6,5,20.00),(7,5,10.00),(8,5,10.00),(9,5,10.00),(10,5,20.00); DROP TABLE IF EXISTS _childs; CREATE TABLE _childs ( id int NOT NULL AUTO_INCREMENT, value decimal(8,2), parent_id int, PRIMARY KEY (id), KEY index_childs_on_parent_id (parent_id) ) ENGINE=InnoDB; INSERT INTO _childs VALUES (17345,36.00,3),(17373,14.00,3),(18274,20.00,2),(18577,30.00,2),(21102,20.00,6),(21113,41.00,5),(21786,9.00,5),(22342,20.00,10),(22662,10.00,8),(22663,10.00,7),(22671,10.00,9); # Gives wacky result. # # The HAVING clause is ignored: in the result, _aggregate_sum equals # _childs_value. # # +-----+-------------+------------------+------------------+ # | _id | _section_id | _aggregate_sum | _childs_value | # +-----+-------------+------------------+------------------+ # | 5 | 5 | 50.00 | 50.00 | # | 6 | 5 | 20.00 | 20.00 | # | 9 | 5 | 10.00 | 10.00 | # +-----+-------------+------------------+------------------+ # # Minor changes to the query solve the issue, e.g.: # # - removing the CAST # - removing the ORDER BY # - adding a WHERE r.section_id = 5 # SELECT r.id _id, r.section_id _section_id, CAST( r.aggregate_sum AS DECIMAL(10,2))_aggregate_sum, SUM( p.value ) _childs_value FROM _parents r JOIN _childs p ON r.id = p.parent_id GROUP BY r.id HAVING _aggregate_sum != _childs_value ORDER BY _section_id ;