On Wed, Jul 21, 2010 at 9:17 PM, Mathieu De Zutter <mathieu@dezutter.org> wrote:
> On Wed, Jul 21, 2010 at 5:59 AM, Howard Rogers <hjr@diznix.com> wrote:
>> It's also easy to find records which have either some yellow or some
>> orange (or both) in them:
>>
>> select * from coloursample where colour & 10>0;
>>
>> But how do I find records which are ONLY yellow and orange, and
>> exclude records which have some other colour mixed in, in one simple
>> query without a lot of 'not this, not that' additions, and without
>> using multiple separate AND tests to nail it down?
>
> What about:
> WHERE colour & ~10 = 0
>
> Kind regards,
> Mathieu
>
Hi Mathieu:
Yes, that works for the simple case I gave by way of illustration (as
does the bitwise XOR -#- function), so thanks. But neither work very
obviously for the more realistic example I gave. If the stored value
is 21205 and I'm 'probing' it with 4098, the record should not be
returned, because 21205 implies a '1' bit is set, not the '2' bit. But
the # and ~ functions cause a non-negative result to be returned, so I
am none the wiser as to how to spot when or when not to return the
record:
In other words, this one is 'wrong' and should not be in the resultset:
ims=# select 21205 & ~ 4098;
?column?
----------
17109
(1 row)
But this one is 'right' and should be returned:
ims=# select 21205 & ~ 4097;
?column?
----------
17108
(1 row)
But looking at the outcome of both queries, there's nothing that
particularly leaps out at me that screams '17108' is right and '17109'
is wrong.
If I was 'probing' with a simple value (1,2,4,8 etc), then I can just
test for a non-zero return: if it's non-zero, the probe value is
implied by the stored value and the record should be returned. So,
right:
ims=# select 21205 & 1;
?column?
----------
1
(1 row)
And wrong:
ims=# select 21205 & 2;
?column?
----------
0
(1 row)
The minute you start probing with a complex value, however, (that is,
a probe value which is made up of multiple basic values, for example
4098, which is 4096 + 2) interpreting the output of the bitwise
operations becomes more than my head can cope with!
I fear I'm going to have to decompose the probe value supplied by a
user and perform multiple simple probes that match their meaning...
but with 15 possible attributes to deal with, that could get a lot
slower than I was hoping.