Re: [PgFoundry] Unsigned Data Types [1 of 2]

Hello Tom,

On Tue, Sep 9, 2008 at 5:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Ryan Bradetich" <rbradetich@gmail.com> writes:
>> I am assuming you are seeing this error in the uint_test1.sql:
>>     ERROR:  could not find hash function for hash operator 16524
>> I can bypass the error in uint_test1.sql by disabling the hash joins.
>> I am going to dig in and figure out why the hashjoin operation is broken.
>
> Well, the cause of that one would've been marking an operator as HASHES
> without providing a hash opclass to back it up.

Actually I did provide a hash operator class in the patch:

CREATE OPERATOR CLASS uint4_ops
   DEFAULT FOR TYPE uint4 USING HASH AS
      OPERATOR 1 =,
      FUNCTION 1 hashuint4(uint4);

This only provides the operator class for uint4 eq uint4.   Jaime's test case
was uint4 eq int4 which I did not have an operator class for.  I was able to fix
this test case by adding the int4 eq uint4 operator like this:

CREATE OPERATOR CLASS uint4_ops
   DEFAULT FOR TYPE uint4 USING HASH FAMILY unsigned_integer_ops AS
      OPERATOR 1 =,
      FUNCTION 1 hashuint4(uint4);

ALTER OPERATOR FAMILY unsigned_integer_ops USING HASH ADD
   OPERATOR 1 = (int4, uint4),
   FUNCTION 1 hashuint4_from_int4(int4);

I tested uint4 eq int4 and int4 eq uint4 and this one additional hash operator
handles them both.

[NOTE: The other solution was to cast foo to the uint4 data type.]

I am working on adding support for the int4 eq uint2 and int4 eq uint1 cases
as well.   I am running into an error when I add support for these hash operator
classes that I am not quite ready to post about yet (I want to look a
bit more first).

> IIRC the test case involved ">"?  That shouldn't even be marked HASHES
> anyway ...

That error was in the uint_test2 test case Jaime provided.

This test case looks like:

drop table if exists t1_uint4;
create table t1_uint4 (f1 uint4  primary key);
insert into t1_uint4 select generate_series(1, 255);
analyze t1_uint4;
select * from t1_uint4, generate_series(1, 10) as foo where t1_uint4.f1 = foo;

Thanks,

- Ryan