group by not return correct value

[Former Member]

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)

[lbreddemann]

Hi Syni,

what revision you're using here?

What to do to exactly reproduce this?

- Lars

[Former Member]

Hi Lars ,

Thanks for your replay , It is easy to reproduce the issue by the following steps.

I use the free trial version SPS6 on the cloudshare.com , the exact version is 1.00.68.384084

Step to reproduce the issue :

--Step 1: create table

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

--Step 2: craete procedure to load data into the table

CREATE  PROCEDURE t1_load() LANGUAGE SQLSCRIPT AS

begin

    declare i int;

    declare j int;

    for i in 1..1000 do

            for j in 1..12 do

                insert into t1 values(sin(3.14159265*j*30/180));

            end for;

end for;

end;

-- Step 3 : execute the procedure for laod data.

call t1_load;

-- Step 4 : verify the result .

hdbsql=>

>

> select count(0) from t1;

| COUNT(0)             |

| -------------------- |

|                12000 |

1 row selected (overall time 6727 usec; server time 311 usec)

hdbsql=> select count(0),id from t1 group by id;

| COUNT(0)             | ID        |

| -------------------- | --------- |

|                 2000 |      0.50 |

|                 2000 |      0.87 |

|                 1000 |      1.00 |

|                 2000 |      0.00 |

|                 5000 |     -0.50 |

5 rows selected (overall time 20.102 msec; server time 16.785 msec)

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

| COUNT(0)             | ID        |

| -------------------- | --------- |

|                 2000 |     -0.87 |

1 row selected (overall time 4911 usec; server time 2382 usec)

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

| COUNT(0)             | ID        |

| -------------------- | --------- |

|                 2000 |     -0.50 |

1 row selected (overall time 10.818 msec; server time 3174 usec)

hdbsql=> select top 12 * from t1;

| ID        |

| --------- |

|      0.50 |

|      0.87 |

|      1.00 |

|      0.87 |

|      0.50 |

|      0.00 |

|     -0.50 |

|     -0.87 |

|     -1.00 |

|     -0.87 |

|     -0.50 |

|      0.00 |

12 rows selected (overall time 6436 usec; server time 288 usec)

hdbsql=>

[Former Member]

Hi All ,

I think it is due to the table data type decimal(5,2) .

If i change the data type to double , and change the procedure t1_load the following row with round function .

insert into t1 values(round(sin(3.14159265*j*30/180),2));

back to test and run the same query , it will work well .

Thanks

Syni

[lbreddemann]

This doesn't reproduce in Rev 70 on column tables:

select count(*),id from t1 group by id

COUNT(*)|ID   
1000    |-1.00
502000  |-0.87
3752000 |-0.50
1002000 |0.00 
2000    |0.50 
2000    |0.87 
1000    |1.00 

neither does it on row tables:

create row table t2 like t1 with data
select count(*),id from t2 group by id;

COUNT(*)|ID   
1002000 |0.00 
1000    |1.00 
2000    |0.50 
2000    |0.87 
3752000 |-0.50
502000  |-0.87
1000    |-1.00

Your suspicion about the decimal was not bad, because rounding effects are easily and often overlooked, but even with rounding there cannot ever be a difference between listing the values and the groups for aggregate functions as the data conversion in your example already took place during the insert.

In short: this seems to have been a bug in rev. 68 (maybe only with a special table type?) but it's not present in the current revision.

- Lars

[Former Member]

Hi Lars

Thanks for your test on the current revision , and it is already be fixed .

The issue reproduced only on row store table in rev.68 , column store table doesn't have the issue.

Thanks

Syni