Issue 2652 - [back-sql] Problem with NOT search requests
Summary: [back-sql] Problem with NOT search requests
Status: VERIFIED SUSPENDED
Alias: None
Product: OpenLDAP
Classification: Unclassified
Component: slapd (show other issues)
Version: unspecified
Hardware: All All
: --- normal
Target Milestone: ---
Assignee: OpenLDAP project
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2003-07-16 10:48 UTC by emmanuel.duru@atosorigin.com
Modified: 2020-06-26 15:05 UTC (History)
0 users

See Also:


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description emmanuel.duru@atosorigin.com 2003-07-16 10:48:19 UTC
Full_Name: Emmanuel Duru
Version: 2.2.0 alpha
OS: solaris 8
URL: 
Submission from: (NULL) (195.68.44.148)


A search request like (!<some attribute>=<some value>) should select
the entries which do not have <some attribute>; with back-sql only
entries which have at least one value for <some attribute> are selected.
The problem is that the request to select candidate entries perform a
join on the attribute in the filter (excluding entries without the
attribute). Consequently, the search request (!<some attribute>=*) does
not select any entry.
[Pierangelo Masarati] This is a known limitation caused by the design of the
backend;
some filters may not generate complete queries.  We might need
to think about a different design, if feasible.


Comment 1 ando@openldap.org 2003-07-17 06:58:01 UTC
> Full_Name: Emmanuel Duru
> Version: 2.2.0 alpha
> OS: solaris 8
> URL:
> Submission from: (NULL) (195.68.44.148)
>
>
> A search request like (!<some attribute>=<some value>) should select the
> entries which do not have <some attribute>; with back-sql only entries
> which have at least one value for <some attribute> are selected. The
> problem is that the request to select candidate entries perform a join
> on the attribute in the filter (excluding entries without the
> attribute). Consequently, the search request (!<some attribute>=*) does
> not select any entry.
> [Pierangelo Masarati] This is a known limitation caused by the design of
> the backend;
> some filters may not generate complete queries.  We might need
> to think about a different design, if feasible.

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.


-- 
Pierangelo Masarati
mailto:pierangelo.masarati@sys-net.it


Comment 2 emmanuel.duru@atosorigin.com 2003-07-17 08:42:00 UTC
>
>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>), 
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.

Emmanuel.

Comment 3 Kurt Zeilenga 2003-08-20 23:24:13 UTC
changed notes
changed state Open to Suspended
Comment 4 Kurt Zeilenga 2003-10-12 15:47:46 UTC
changed notes
moved from Incoming to Contrib
Comment 5 ando@openldap.org 2004-04-18 20:45:21 UTC
> >
>>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.
Comment 6 ando@openldap.org 2004-04-19 22:29:42 UTC
changed notes
moved from Contrib to Software Enhancements
Comment 7 emmanuel.duru@atosorigin.com 2004-04-20 07:12:55 UTC
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.


Comment 8 ando@openldap.org 2004-04-20 07:27:18 UTC
> 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 ?

From the fist query you get people that indeed have a phone number,
because you're joining the "phones" table; this is a common mistake I ran
into many times.  That's why you need two queries: one to select those
that have a phone number that doesn't match, and one for those that don't.
 In the (black magic to me) variety of join mechs there might be an option
that allows to perform all in one (outer join?).

p.

>
> -----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.


-- 
Pierangelo Masarati
mailto:pierangelo.masarati@sys-net.it


Comment 9 ando@openldap.org 2004-04-20 07:35:29 UTC
> 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 ?

After re-reading my previous postings, I concur I was probably nuts at the
moment I wrote them (perhaps too much eating at sunday's dinner? :).

This issue is on a very low priority level for me; however, since in my
(little if any) spare time I try to improve back-sql, it's still open.  If
you consider it a priority I might move it a bit forward because I'm
uncomfortable with incomplete filter semantics.

p.

-- 
Pierangelo Masarati
mailto:pierangelo.masarati@sys-net.it


Comment 10 OpenLDAP project 2014-08-01 21:04:52 UTC
back-sql
(design limitation)