Apparent Bug in SE 5.0 using group by and order by
Platform specs:
Unix att sysV R 3.2 i386
Informix SE 5.00
ISQL 4
There seems to be a bug in Informix 5.0 concerning the GROUP BY sub clause
while SELECTing data in conjunction with the ORDER BY clause. It seems that
after the GROUP BY does it's duplicate elimination process the ORDER BY never
gets executed.
The attached printouts produced the problem. The first part shows the sql
statements used to run the tests and a dump of the tables. The second part
shows the results of the tests.
Used to create two tables:
create table feature_point
(
feature_no integer not null,
sequence integer not null,
point_number integer not null
);
create table pract_feature
(
practice_key integer not null,
feature_type char(6) not null,
feature_sta float not null,
feature_no integer not null
);
List of records in the feature_point table.
feature_no sequence point_number
1 1 1
1 2 3
1 3 5
1 4 8
2 1 1
2 2 2
3 1 3
3 2 4
4 1 5
4 2 6
5 1 7
5 2 8
List of records in the pract_feature table.
practice_key feature_type feature_sta feature_no
1 PROFLE 0.00 1
1 XSECT 0.00 2
1 XSECT 50.00 3
1 XSECT 100.00 4
1 XSECT 75.00 5
Used to select records from the tables:
select count (*) , pract_feature.feature_sta, pract_feature.feature_no
from feature_point, pract_feature
where feature_type = "XSECT"
and pract_feature.feature_no = feature_point.feature_no
and pract_feature.practice_key = 1
group by pract_feature.feature_sta, pract_feature.feature_no
order by pract_feature.feature_sta;
Results of running in an Informix 4.0 environment with ISQL 4.00.UH1
(count(*)) feature_sta feature_no
2 0.00 2
2 50.00 3
2 75.00 5
2 100.00 4
Note: The feature_sta with a value of 75.00 is correctly ordered.
Results of running in an Informix 5.0 environment with ISQL 4.00.UD2
(count(*)) feature_sta feature_no
2 0.00 2
2 50.00 3
2 100.00 4
2 75.00 5
Note: The feature_sta with a value of 75.00 is not correctly ordered.
Could anyone provide an explanation please?
---
Email address jasr@ftc.scs.ag.gov (JASon Restad)