Thread: Query returning tuples that does not satisfy the WHERE clause

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)
aserror  
     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:


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.

Manuel Sugawara <masm@fciencias.unam.mx> writes:
> Using the second plan the query is returning tuples where
>    a=F1o_semestre <> 20071

You seem to have worse problems than that, because as given the
insc_registra_grupo function never returns non-NULL, and so the
query ought not be returning any tuples at all.  I suspect pilot
error --- perhaps looking at the wrong schema or some such?
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> You seem to have worse problems than that, because as given the
> insc_registra_grupo function never returns non-NULL

Actually the function (and the database) is quite complex and was
trimed just to test the problem.

> I suspect pilot error --- perhaps looking at the wrong schema or
> some such?

I don't think so, search_path has its default value and also I'm able
to reproduce it in a fresh cluster (tried 3 different machines to
discard hardware problems).

Regards,
Manuel.

Manuel Sugawara <masm@fciencias.unam.mx> writes:
> I don't think so, search_path has its default value and also I'm able
> to reproduce it in a fresh cluster (tried 3 different machines to
> discard hardware problems).

Hm, well I'm willing to take a look if you can provide me access to the
problem database running on a debug-enabled Postgres build.  The
fresh-cluster test seems to rule out my other idea about a corrupt
index (though that was shaky anyway considering both plans use the
same index...)
        regards, tom lane