[EP-tech] Re: Patch: handle NULL values in EPrints::Search::Condition::Comparison

[Alexander 'Leo' Bergolth]

On 07/26/2012 03:32 PM, Tim Brody wrote:
> On Tue, 2012-06-26 at 18:44 +0200, Alexander 'Leo' Bergolth wrote:
>> The attached patch changes SQL output to use "IS" instead of "=" when
>> comparing for equality against undefined values.
>>
>> This fixes a problem (at least with Oracle databases) when using views
>> to browse by creators that have an empty givenname. (The SQL that was
>> created contained something like
>> "EPRINT_CREATORS_NAME"."CREATORS_NAME_GIVEN" = NULL
>> ... which will never match.
>>
>> I believe it could also correct several other problems concerning empty
>> fields, however, I did only stumble across the browse view issue yet.
> 
> I think the creators_name_given should contain "" instead of NULL,
> exactly to avoid this problem. There was an EPrints version that started
> writing NULLs but that was a bug.

Oracle doesn't distinguish between empty strings and NULL.
There is no way to insert an empty string, Oracle will always insert NULL:

-------------------- 8< --------------------
SELECT '' FROM dual;
-------------------- 8< --------------------
... will return NULL.

On the other side, a search for
-------------------- 8< --------------------
SELECT COUNT(*) FROM eprint_creators_name
  WHERE creators_name_given = '';
-------------------- 8< --------------------

will always give zero results while a search for
-------------------- 8< --------------------
SELECT COUNT(*) FROM eprint_creators_name
  WHERE creators_name_given IS NULL;
-------------------- 8< --------------------

... will count empty givennames.

However, Oracle warns not to treat empty strings the same as NULL:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements005.htm

Thus I guess that using a clause like
WHERE creators_name_given = '' OR creators_name_given IS NULL
will be the the safest way to handle the problem.

> Otherwise, a search for "IS NULL" should be using EXact and will create
> an "IsNull" field condition?

My problem is:
A browse view by author uses an EX search for the name and if the
givenname is empty, it will try an EX search that currently translates
to "CREATORS_NAME_GIVEN" = NULL, which will never match on Oracle.

Cheers,
--leo

P.S.:
SELECT '' FROM dual;
Res: NULL

SELECT count(*) FROM
  (SELECT '' c1 FROM dual)
  WHERE c1 = '';
Res: 0

SELECT count(*) FROM
  (SELECT '' c1 FROM dual)
  WHERE c1 IS NULL;
Res: 1

SELECT count(*) FROM
  (SELECT '' c1 FROM dual)
  WHERE c1 = NULL;
Res: 0

-- 
e-mail   ::: Leo.Bergolth (at) wu.ac.at
fax      ::: +43-1-31336-906050
location ::: IT-Services | Vienna University of Economics | Austria