Skip to Content
0
Former Member
Dec 17, 2013 at 06:46 AM

group by not return correct value

25 Views

This is very interesting for run a sql that not return the correct value via group by clause .

for example , the following sql can not return the id value -0.87 , but it work well when i use the predicate "id=-0.87" ,

anybody know this is why ?

create table t1(id decimal(5,2));

hdbsql=> \al on
Aligned output mode switched ON
hdbsql=> \mu on
Multiline mode switched ON

hdbsql=> select count(0),id from t1 where id=-0.87 group by id;
| COUNT(0) | ID |
| -------------------- | --------- |
| 5000000 | -0.87 |

1 row selected (overall time 6871.102 msec; server time 6860.700 msec)

hdbsql=> select count(0),id from t1 group by id;
| COUNT(0) | ID |
| -------------------- | --------- |
| 5000000 | 0.50 |
| 5000000 | 0.87 |
| 2500000 | 1.00 |
| 5000000 | 0.00 |
| 12500000 | -0.50 |

5 rows selected (overall time 20.883945 sec; server time 20.872502 sec)

-->>> why it is not return the id=-0.87

hdbsql=> select count(0),id from t1 where id=-0.50 group by id;
| COUNT(0) | ID |
| -------------------- | --------- |
| 5000000 | -0.50 |

1 row selected (overall time 6329.068 msec; server time 6321.500 msec)

hdbsql=> select count(0) from t1;
| COUNT(0) |
| -------------------- |
| 30000000 |

1 row selected (overall time 17.687 msec; server time 11.485 msec)