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.
> 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
> >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.
changed notes changed state Open to Suspended
changed notes moved from Incoming to Contrib
> > >>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.
changed notes moved from Contrib to Software Enhancements
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.
> 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
> 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
back-sql (design limitation)