search

icatproject / icat.server

The ICAT server offering both SOAP and "RESTlike" interfaces to a metadata catalog.
Other
1 stars 5 forks source link

Unexpected behavior with "select sum(...)" queries #193

Closed jodysalt closed 7 years ago

jodysalt commented 7 years ago

If you run:

select sum(datafile.fileSize) from Datafile datafile, datafile.dataset as dataset where dataset.id = 103100

you get [null] - I would expect [0]. However, if you run:

select count(datafile) from Datafile datafile, datafile.dataset as dataset where dataset.id = 103100

you get [0] as expected. I suspect this could cause problems with the IDS getSize implementation.

fisherab commented 7 years ago

It is actually behaving as the spec. See last bullet in 10.2.7.4 of http://docs.oracle.com/html/E13946_04/ejb3_langref.html which says:

SUM returns Long when applied to state-fields of integral types (other than BigInteger); Double when applied to state-fields of floating point types; BigInteger when applied to state-fields of type BigInteger; and BigDecimal when applied to state-fields of type BigDecimal. If SUM, AVG, MAX, or MIN is used, and there are no values to which the aggregate function can be applied, the result of the aggregate function is NULL. If COUNT is used, and there are no values to which COUNT can be applied, the result of the aggregate function is 0.

With the corresponding SQL queries both MySQL and Oracle exhibit the same behaviour.

However you are correct in saying that the IDS getSize will fail sometimes because of this and I will add an IDS issue.