RE : [back-sql] Problem with NOT search requests (ITS#2652)

It seems to me that the result of the second query is included in the result
of the first query.
All persons who do not have a given phone number include persons who do not
have a phone number at all.
Don't you think so ?
-----Message d'origine-----
De : Pierangelo Masarati [mailto:openldap-its@OpenLDAP.org]
Envoyé : dimanche 18 avril 2004 22:45
À : emmanuel.duru@atosorigin.com
Objet : Re: [back-sql] Problem with NOT search requests (ITS#2652)
> >
>>I don't think this problem can be easily overcome.
>>I can easily figure out a simple query that does this
>>if the filter is of the type (!<ava>); if the filter
>>is (!<complex filter>) things get much more involved.
>>We might have to live with it.
>>
>>p.
>
> Did you consider nested queries ?
> For example, for the search filter (!telephonenumber=<a value>),
My mistake: the filter is rather (!(telephonenumber=<a value>)),
> we would get candidates with the following query :
> 'select distinct persons.id from persons where not persons.id
> in (select persons.id from persons, phones where
> phones.phone='<a value>' and persons.id=phones.id)'
> This may be a generic way to process not filters, though this may
> generate complex SQL queries.
This is not likely to work, because a filter of the form
(!(telephonenumber=<a value>))
should match not only rows resulting from
select
distinct persons.id
from
persons
where
persons.id not in (select
persons.id
from
persons,phones
where
phones.phone='<a value>'
and persons.id=phones.id)
but also rows resulting from
select
distinct
persons.id
from persons
where
persons.id not in (select
phones.i
from
phones)
which could be obtained by
select
distinct persons.id
from
persons
where
persons.id not in (select
persons.id
from
persons,phones
where
phones.phone='<a value>'
and persons.id=phones.id)
and persons.id not in (select
phones.id
from
phones)
This would make things quite cumbersome for more involved queries.
Maybe it's worth trying; I'll have a look at this later.
p.