OpenLDAP
Up to top level
Build   Contrib   Development   Documentation   Historical   Incoming   Software Bugs   Software Enhancements   Web  

Logged in as guest

Viewing Software Enhancements/2652
Full headers

From: emmanuel.duru@atosorigin.com
Subject: [back-sql] Problem with NOT search requests
Compose comment
Download message
State:
1 replies: 1
5 followups: 1 2 3 4 5

Major security issue: yes  no

Notes:

Notification:


Date: Wed, 16 Jul 2003 10:48:19 GMT
From: emmanuel.duru@atosorigin.com
To: openldap-its@OpenLDAP.org
Subject: [back-sql] Problem with NOT search requests
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.



Followup 1

Download message
Date: Thu, 17 Jul 2003 08:58:01 +0200 (CEST)
Subject: Re: [back-sql] Problem with NOT search requests (ITS#2652)
From: "Pierangelo Masarati" <ando@sys-net.it>
To: <emmanuel.duru@atosorigin.com>
Cc: <openldap-its@OpenLDAP.org>
> 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




Followup 2

Download message
From: "Emmanuel Duru" <emmanuel.duru@atosorigin.com>
To: <ando@sys-net.it>
Cc: <openldap-its@OpenLDAP.org>
Subject: RE : [back-sql] Problem with NOT search requests (ITS#2652)
Date: Thu, 17 Jul 2003 10:42:00 +0200
>
>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.



Followup 3

Download message
From: "Emmanuel Duru" <emmanuel.duru@atosorigin.com>
To: "'Pierangelo Masarati'" <openldap-its@OpenLDAP.org>
Subject: RE : [back-sql] Problem with NOT search requests (ITS#2652)
Date: Tue, 20 Apr 2004 09:12:55 +0200
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.




Followup 4

Download message
Date: Tue, 20 Apr 2004 09:27:18 +0200 (CEST)
Subject: Re: RE : [back-sql] Problem with NOT search requests (ITS#2652)
From: "Pierangelo Masarati" <ando@sys-net.it>
To: <emmanuel.duru@atosorigin.com>
Cc: <openldap-its@OpenLDAP.org>
> 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




Followup 5

Download message
Date: Tue, 20 Apr 2004 09:35:29 +0200 (CEST)
Subject: Re: RE : [back-sql] Problem with NOT search requests (ITS#2652)
From: "Pierangelo Masarati" <ando@sys-net.it>
To: <emmanuel.duru@atosorigin.com>
Cc: <openldap-its@OpenLDAP.org>
> 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




Reply 1

Resend
From: Pierangelo Masarati <openldap-its@OpenLDAP.org>
To: emmanuel.duru@atosorigin.com
Subject: Re: [back-sql] Problem with NOT search requests (ITS#2652)
Date: Sun Apr 18 20:45:21 2004
> >
>>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.

Up to top level
Build   Contrib   Development   Documentation   Historical   Incoming   Software Bugs   Software Enhancements   Web  

Logged in as guest


The OpenLDAP Issue Tracking System uses a hacked version of JitterBug

______________
© Copyright 2013, OpenLDAP Foundation, info@OpenLDAP.org