| Bug #31559 | "group by" changed the implicit ordering | ||
|---|---|---|---|
| Submitted: | 12 Oct 2007 9:23 | Modified: | 12 Oct 2007 17:57 |
| Reporter: | Joerg Bruehe | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Tests | Severity: | S7 (Test Cases) |
| Version: | 5.0.50 | OS: | Any |
| Assigned to: | Matthias Leich | CPU Architecture: | Any |
[12 Oct 2007 11:42]
Martin Hansson
As far as the order of the result is concerned, this is correct. And as far as I can see, the ordering is all that differs from what you had. It is a bit confusing that the columns are not presented in the order they were grouped, but not incorrect. This is most likely because they appear in this order in an index. This is the exact wrong behavior observed in Bug #30596. It is completely safe to re-record this test case. I'm not familiar with the nist test suite so I pass this on.
[12 Oct 2007 15:33]
Matthias Leich
1. The original NIST test itself says only that a result set of 12 rows is expected. There is no printed result set included ! 2. The MySQL manual says in http://dev.mysql.com/doc/refman/5.1/en/select.html If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. Therefore I checked the old expected results and the current results again. Old expected results: SELECT * FROM WORKS GROUP BY PNUM,EMPNUM,HOURS; EMPNUM PNUM HOURS E1 P1 40 E1 P2 20 E1 P3 80 E1 P4 20 E1 P5 12 E1 P6 12 E2 P1 40 <-- (*) E2 P2 80 E3 P2 20 E4 P2 20 E4 P4 40 E4 P5 80 (*) Here start an ordering which violates the statement made within the manual It looks like the result set is ordered by EMPNUM, PNUM, HOURS but not by the order of columns after GROUP BY. That means - Martin Hansson is right - the old file with expected results is wrong. I will fix this.
[12 Oct 2007 17:51]
Matthias Leich
Fix pushed to mysql-test-extra-5.0 and mysql-test-extra-5.1
[12 Oct 2007 17:57]
Matthias Leich
Setting status from "Patch queued" to "Closed", because fixes pushed to mysql-test-extra-nnn do neither cause automatic pushbuild runs nor do they need to be merged in any main tree.
Description: Found in a custom build using the 5.0.50 sources. Test "nist_all" fails on both platforms on which this build was done. This is the select statement generating the data: 5977 SELECT * 5978 FROM WORKS 5979 GROUP BY PNUM,EMPNUM,HOURS; This is the difference in results: ===== @@ -5979,17 +5979,17 @@ GROUP BY PNUM,EMPNUM,HOURS; EMPNUM PNUM HOURS E1 P1 40 -E1 P2 20 -E1 P3 80 -E1 P4 20 -E1 P5 12 -E1 P6 12 E2 P1 40 +E1 P2 20 E2 P2 80 E3 P2 20 E4 P2 20 +E1 P3 80 +E1 P4 20 E4 P4 40 +E1 P5 12 E4 P5 80 +E1 P6 12 SELECT PNUM,EMPNUM FROM WORKS GROUP BY EMPNUM,PNUM,HOURS; mysqltest: Result content mismatch ===== So the "result" file (test reference) assumes an ordering by the first column displayed as a result, but the actual result ("reject" file) contains an ordering by the column listed first in the "group by" clause. How to repeat: Run the "nist" suite. Suggested fix: Check whether the semantics of "group by" changed by intention or by accident; if the first: adapt the result file, if the latter: revert the code change.