repro.sql

application/octet-stream

Download

begin;

drop table if exists inttest;
drop type if exists myint cascade;

create type myint;

create function myintin(cstring) returns myint strict immutable language
  internal as 'int4in';

create function myintout(myint) returns cstring strict immutable language
  internal as 'int4out';

create function myinthash(myint) returns integer strict immutable language
  internal as 'hashint4';

create type myint (input = myintin, output = myintout, like = int4);

create cast (int4 as myint) without function;
create cast (myint as int4) without function;

create function myinteq(myint, myint) returns bool as $$
begin
  if $1 is null and $2 is null then
    return true;
  else
    return $1::int = $2::int;
  end if;
end;
$$ language plpgsql immutable;

create function myintne(myint, myint) returns bool as $$
begin
  return not myinteq($1, $2);
end;
$$ language plpgsql immutable;

create operator = (
  leftarg    = myint,
  rightarg   = myint,
  commutator = =,
  negator    = <>,
  procedure  = myinteq,
  restrict   = eqsel,
  join       = eqjoinsel,
  merges
);

create operator <> (
  leftarg    = myint,
  rightarg   = myint,
  commutator = <>,
  negator    = =,
  procedure  = myintne,
  restrict   = eqsel,
  join       = eqjoinsel,
  merges
);

create operator class myint_ops
default for type myint using hash as
  operator    1   = (myint, myint),
  function    1   myinthash(myint);

create table inttest (a myint);
insert into inttest values (1::myint), (null);

select 'hash_in_is_null' as case_name,
       ((a in
          (0::myint,1::myint,2::myint,3::myint,4::myint,
           5::myint,6::myint,7::myint,8::myint,9::myint)) is null) as ok
from inttest where a is null;

select 'nonhash_in_is_null' as case_name,
       ((a in
          (0::myint,1::myint,2::myint,3::myint,4::myint,5::myint)) is null) as ok
from inttest where a is null;

select 'hash_not_in_is_null' as case_name,
       ((a not in
          (0::myint,1::myint,2::myint,3::myint,4::myint,
           5::myint,6::myint,7::myint,8::myint,9::myint)) is null) as ok
from inttest where a is null;

select 'nonhash_not_in_is_null' as case_name,
       ((a not in
          (0::myint,1::myint,2::myint,3::myint,4::myint,5::myint)) is null) as ok
from inttest where a is null;

rollback;