MIN function not working against virtual table

[patrickbachmann]

Hi folks,

When querying a very simple SQL from dev server to prod server via a virtual table it seems the MIN function is not working.  Are there limits to what functions can be used via virtual table or could it be due to the fact that our dev is rev 82 and our prod is rev 68?  For example the following should return a valid date (ie: in this case 20140803)

select ebeln, ebelp, min(eindt) from "VIRTUAL_REMOTE_SCHEMA"."VIRTUAL_TABLE_EKET"

                               where ebeln = '123456789'

                               group by ebeln, ebelp

Instead of a valid date (20140803) it returns the number 20.  Yet if I run this same exact SQL directly in the production server it returns the correct valid date 20140803.  Or if I remove the MIN function completely I get a list of 18 different dates for this same EBELN, EBELP purchase order and indeed I see the date 20140803 in the list.

Thanks,

-Patrick

[patrickbachmann]

Ok just to test my theory that it could be due to different rev's I tried a virtual connection from our dev (rev82) to stage (rev82) and not only does the MIN not work but I get an error; 

'[129]: transaction rolled back by an internal error: exception 1000002: Allocation failed ; $size$=4918291432952823809; $name$=Pool/RowEngine/QueryExecution; $type$=pool; $inuse_count$=7; $allocated_size$=56896; $alignment$=16'

If I remove the MIN function it works and I get a list of many dates....

[patrickbachmann]

Ok the allocation error happens if the rows returned are not the same;  ie:

select ebeln, ebelp, min(eindt) from "VIRTUAL_REMOTE_SCHEMA"."VIRTUAL_TABLE_EKET" where ebeln = '1000' group by ebeln, ebelp;

EBELN    EBELP   EINDT

1000   1         20140101

1000   2         20140102

RESULT = ALLOCATION ERROR

If I choose an example where the first two fields are identical I don't get error but I get strange 20 result;

select ebeln, ebelp, min(eindt) from "VIRTUAL_REMOTE_SCHEMA"."VIRTUAL_TABLE_EKET" where ebeln = '2000' group by ebeln, ebelp;

EBELN    EBELP   EINDT

2000   1         20140101

2000   1         20140102

EXPECTED RESULT = 20140101

ACTUAL RESULT = 20

If I do not use virtual table and perform the SQL directly against source table in source system I get correct result.

PS: I get 20 no matter which value I query for EBELN.  This is same from rev82 virtual table from rev82 system and from rev68 virtual table to rev82 system.  The problem seems to be with using MIN on virtual table.