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

[emmanuel.duru@atosorigin.com]

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.