[HACKERS] Query returning tuples that does not satisfy the WHERE clause

[Manuel Sugawara]

Hi all,

I'm running PostgreSQL v 8.1.4 and found a query that returns tuples
that does not satisfy the WHERE clause, the query is:

select * into errores_20071 from (
   select r.id, r.trayectoria_id, r.grupo_id, 
regacd.insc_registra_grupo(trayectoria_id, grupo_id, true, false, true) as 
error 
     from regacd.registro r join regacd.grupo g on (g.id = r.grupo_id)
   where g.año_semestre = 20071 and g.tipo_id = 'a') x 
 where error is not null;

A self-contained database schema is here:

schema-registro.sql
Description: Binary data

Unfortunately I cannot post the data set but I'm willing to give
access to my machine to test the problem.

While trying to create a self contained test case I found that the
query returns the correct answer before analyzing:

                                                     QUERY PLAN
  
----------------------------------------------------------------------------------------------------------------
   Nested Loop  (cost=41.61..31193.44 rows=36 width=12)
     ->  Index Scan using "AsignaturClaveGrupoÚnicaPorAñoSemestre2" on grupo g  
(cost=0.00..14.03 rows=3 width=4)
           Index Cond: (("año_semestre" = 20071) AND (tipo_id = 'a'::"char"))
     ->  Bitmap Heap Scan on registro r  (cost=41.61..10305.22 rows=7031 
width=12)
           Recheck Cond: ("outer".id = r.grupo_id)
           Filter: (insc_registra_grupo(trayectoria_id, grupo_id, true, false, 
true) IS NOT NULL)
           ->  Bitmap Index Scan on registro_grupo  (cost=0.00..41.61 rows=7031 
width=0)
                 Index Cond: ("outer".id = r.grupo_id)
  (8 filas)

but does not after I run analyze:

                                                     QUERY PLAN
  
----------------------------------------------------------------------------------------------------------------
   Hash Join  (cost=1166.75..44109.74 rows=34184 width=12)
     Hash Cond: ("outer".grupo_id = "inner".id)
     ->  Seq Scan on registro r  (cost=0.00..28538.85 rows=1397684 width=12)
           Filter: (insc_registra_grupo(trayectoria_id, grupo_id, true, false, 
true) IS NOT NULL)
     ->  Hash  (cost=1159.54..1159.54 rows=2883 width=4)
           ->  Bitmap Heap Scan on grupo g  (cost=31.30..1159.54 rows=2883 
width=4)
                 Recheck Cond: (("año_semestre" = 20071) AND (tipo_id = 
'a'::"char"))
                 ->  Bitmap Index Scan on 
"AsignaturClaveGrupoÚnicaPorAñoSemestre2"  (cost=0.00..31.30 rows=2883 width=0)
                       Index Cond: (("año_semestre" = 20071) AND (tipo_id = 
'a'::"char"))
  (9 filas)

Using the second plan the query is returning tuples where
  
   año_semestre <> 20071

Any help will be appreciated.

Best regards,
Manuel.




---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq