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