search

lukefitzwolfgang / icatproject

Automatically exported from code.google.com/p/icatproject
0 stars 0 forks source link

Problem with JPQL/SQL lower() and upper() functions in search request queries #133

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
I was trying to make a case-insensitive search on a character string field of 
Icat DatasetType entity with the following queries:

1) DatasetType [ LOWER(name) = 'test' ]
and
2) DatasetType [ name = LOWER('TEST') ]

and ICAT.search(arg) returned the following error messages:

1) "Expected token from types [>, <, !=, =, <>, >=, <=, IN, BETWEEN] at token ( 
in [ LOWER < ( > name )"
2) "Expected token from types [KET] at token ( in = LOWER < ( > TEST )"

I also tried to make similar requests using JPQL:

3) SELECT dt FROM DatasetType dt WHERE LOWER(dt.name) = 'test'
4) SELECT dt FROM DatasetType dt WHERE dt.name = LOWER('TEST')

and they both return with the message "path LOWER mentioned in WHERE clause 
contains no dots".

Could you please check if LOWER() and UPPER() functions are supported in ICAT 
search queries?

Original issue reported on code.google.com by anton.te...@gmail.com on 31 Mar 2014 at 2:07

GoogleCodeExporter commented 9 years ago 
The JPQL one should work - and will be fixed. The old syntax is limited and 
will probably not be fixed - unless it is really easy to do ;-)

Original comment by dr.s.m.f...@gmail.com on 31 Mar 2014 at 2:14

GoogleCodeExporter commented 9 years ago 
I have now written a test to confirm the bad behaviour

Original comment by dr.s.m.f...@gmail.com on 8 Apr 2014 at 5:21

GoogleCodeExporter commented 9 years ago 
This has been fixed for the JPQL type queries. For the old style queries only 
the aggregate functions: MIN, MAX, AVG, COUNT and SUM are supported.

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

GoogleCodeExporter commented 9 years ago 
Thank you very much for the fix!
Could you please point out in which dist version the fix will be available?

Original comment by anton.te...@gmail.com on 9 Apr 2014 at 8:27

GoogleCodeExporter commented 9 years ago 
It will be released in 4.3.3 - quite soon

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

GoogleCodeExporter commented 9 years ago 
Fixed in ICAT 4.3.3

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