BUG #7593: Unexpected query result combining or and in operators - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      7593
Logged by:          DE MURCIA Julien
Email address:      demurcia@ockham-solutions.fr
PostgreSQL version: 9.2.1
Operating system:   Windows 7
Description:        =


Hello,

I am trying to run this query:

select n.numero_id, h.service_id, h.groupe_id
from identite i, numero n, habilitation h =

where =

(h.service_id=3D100 or h.groupe_id=3D50) =

and n.numero_id in (25393, 25384)
and i.procedure_id=3Dh.procedure_id =

and i.numero_id=3Dn.numero_id;

At first I get 4 results (as expected) with this plan:
"Nested Loop  (cost=3D15.99..73.40 rows=3D1 width=3D24)"
"  Join Filter: (i.procedure_id =3D h.procedure_id)"
"  ->  Hash Join  (cost=3D15.99..46.76 rows=3D1 width=3D16)"
"        Hash Cond: (i.numero_id =3D n.numero_id)"
"        ->  Seq Scan on identite i  (cost=3D0.00..25.10 rows=3D1510 width=
=3D16)"
"        ->  Hash  (cost=3D15.96..15.96 rows=3D2 width=3D8)"
"              ->  Bitmap Heap Scan on numero n  (cost=3D8.55..15.96 rows=
=3D2
width=3D8)"
"                    Recheck Cond: (numero_id =3D ANY
('{25393,25384}'::bigint[]))"
"                    ->  Bitmap Index Scan on numero_pkey  (cost=3D0.00..8.=
55
rows=3D2 width=3D0)"
"                          Index Cond: (numero_id =3D ANY
('{25393,25384}'::bigint[]))"
"  ->  Seq Scan on habilitation h  (cost=3D0.00..26.50 rows=3D11 width=3D24=
)"
"        Filter: ((service_id =3D 100) OR (groupe_id =3D 50))"


After a little while (probably after the analyze), the query plan is changed
and only 2 results are returned !
"Merge Join  (cost=3D33.31..39.69 rows=3D1 width=3D24)"
"  Merge Cond: (i.numero_id =3D n.numero_id)"
"  ->  Sort  (cost=3D33.31..33.35 rows=3D17 width=3D24)"
"        Sort Key: i.numero_id"
"        ->  Hash Join  (cost=3D26.64..32.96 rows=3D17 width=3D24)"
"              Hash Cond: (i.procedure_id =3D h.procedure_id)"
"              ->  Seq Scan on identite i  (cost=3D0.00..5.02 rows=3D302
width=3D16)"
"              ->  Hash  (cost=3D26.50..26.50 rows=3D11 width=3D24)"
"                    ->  Seq Scan on habilitation h  (cost=3D0.00..26.50
rows=3D11 width=3D24)"
"                          Filter: ((service_id =3D 100) OR (groupe_id =3D
50))"
"  ->  Index Only Scan using numero_pkey on numero n  (cost=3D0.00..12.57
rows=3D2 width=3D8)"
"        Index Cond: (numero_id =3D ANY ('{25393,25384}'::bigint[]))"


Below is the script that creates the test data:

CREATE TABLE numero
(
  numero_id bigint NOT NULL, =

  numero character varying(50) NOT NULL,
  primary key (numero_id)
);


CREATE TABLE identite
(
  identite_id bigint NOT NULL,
  numero_id bigint NOT NULL,
  procedure_id bigint NOT NULL,
  primary key (identite_id)
);

CREATE TABLE habilitation
(
  habilitation_id bigint NOT NULL,
  procedure_id bigint NOT NULL,
  type integer,
  groupe_id bigint,
  service_id bigint,
  utilisateur_id bigint,
  primary key (habilitation_id)
);


CREATE OR REPLACE FUNCTION FILL_WITH_FAKE_DATA() RETURNS VARCHAR AS $$  =

declare =

  i bigint;
begin
   for i in 1..35000 loop
     insert into numero(numero_id,numero) values (i, '123');
   end loop;
   for i in 1..300 loop
     insert into identite(identite_id,numero_id,procedure_id ) values
(i,i,1);
   end loop;

   for i in 1..30 loop
     insert into habilitation(habilitation_id, procedure_id, groupe_id,
service_id) values (i,2,50,null); =

   end loop;


return ' FILL_WITH_FAKE_DATA DONE'; =

end; =

$$ LANGUAGE plpgsql; =


select FILL_WITH_FAKE_DATA();
drop FUNCTION FILL_WITH_FAKE_DATA();

insert into identite(identite_id,numero_id,procedure_id ) values
(25393,25393,1);
insert into identite(identite_id,numero_id,procedure_id ) values
(25384,25384,1);
insert into habilitation(habilitation_id, procedure_id, groupe_id,
service_id) values (31,1,50,null); =

insert into habilitation(habilitation_id, procedure_id, groupe_id,
service_id) values (32,1,null,100); =