Inconsistend results from "SELECT COUNT ..."

[GoogleCodeExporter]

The results from a "SELECT COUNT(i) FROM <type> i" search are somewhat 
inconsistent depending on read permissions.  In general, such a search yields a 
list of one integer which is the number of objects that this user is allowed to 
see.  But in some cases an empty list is returned instead.

What steps will reproduce the problem?
# case 1: user has read perms on some of the objects he searches for:
>>> client.search("SELECT COUNT(i) FROM Investigation i")
[2L]
# case 2: user has read perms on all objects of this type, but none exists:
>>> client.search("SELECT COUNT(i) FROM Application i")
[0L]
# case 3: user has in theory read perms on some objects, but none of them 
exists:
>>> client.search("SELECT COUNT(i) FROM InvestigationParameter i")
[0L]
# case 4: user has no read perms on objects of this type at all, some objects 
exist:
>>> client.search("SELECT COUNT(i) FROM Rule i")
[]
# case 5: user has no read perms on objects of this type at all, no objects 
exist:
>>> client.search("SELECT COUNT(i) FROM DataCollection i")
[]

What is the expected output? What do you see instead?
Expect a list of one integer, the number of objects that the user may see, in 
all cases.  In particular, I'd expect "[0L]" in cases 4 and 5.

Please use labels and text to provide additional information.
ICAT version is 4.3.2.  The issue seem to be somewhat similar to Issue 120, but 
I can't tell whether it's related.

Original issue reported on code.google.com by rolf.kr...@helmholtz-berlin.de on 3 Mar 2014 at 3:48

[GoogleCodeExporter]

I agree that it should return a list with 0 rather than an empty list. I am 
accepting this though I have net yet written a test for it.

Original comment by dr.s.m.f...@gmail.com on 10 Mar 2014 at 1:00

• Changed state: Accepted

[GoogleCodeExporter]

I have written a test which fails for the current code. There is also the case 
of SELECT AVG(i.id) FROM Rule i which in SQL returns one result with value 
null. Currently if no authz rule matches an empty list is returned. A fast 
solution is to see if it is a COUNT query and return a list with 0 and for MIN, 
MAX, SUM and AVG return a list with null. In fact you might expect SUM to 
return 0 but both MySQL and Oracle return a list with null.

Original comment by dr.s.m.f...@gmail.com on 9 Apr 2014 at 11:43

[GoogleCodeExporter]

Original comment by dr.s.m.f...@gmail.com on 9 Apr 2014 at 11:43

• Changed state: Started

[GoogleCodeExporter]

It turns out that JAX-WS does not handle returning a list of objects of length 
1 holding a single null value. This potentially affects the results returned by 
MIN, MAX, SUM and AVG. I could either return a list with, for example, and 
empty string or return an empty list. There is no problem with COUNT. I have 
changed the code to return an empty list to represent a list with one null. 
This unfortunate behaviour will be documented.

Original comment by dr.s.m.f...@gmail.com on 9 Apr 2014 at 3:39

• Changed state: CheckedIn

[GoogleCodeExporter]

Original comment by dr.s.m.f...@gmail.com on 14 Jul 2014 at 12:52

• Changed state: Fixed