Thread: BUG #15244: Inherited table queries return null lines that do notexist

The following bug has been logged on the website:

Bug reference:      15244
Logged by:          Gabriel Nuernberg Biazoto
Email address:      biazotogabriel@gmail.com
PostgreSQL version: 10.4
Operating system:   Windows 10 64bits
Description:

I was working with table inheritance and started having problems with my
queries.
I noticed that every time I perform a query on a parent table of an
inheritance and perform a query on any table then with more records than the
preceding table, postgres returns me rows with all records in null that do
not exist in the table that I'm consulting.
CREATE TABLE cfe (idcfe serial, fantasia varchar(30), datahora timestamp);
CREATE TABLE cli (tipo_cliente integer, situacao_praca integer) inherits
(cfe);
CREATE TABLE  "for" (tipo_fornecedor integer, debito numeric(15,6)) inherits
(cfe);
alter table cfe add constraint pk_cfe primary key (idcfe);

CREATE TABLE cfe2 (idcfe serial, fantasia varchar(30), datahora
timestamp);
alter table cfe2 add constraint pk_cfe2 primary key (idcfe);
create table cli2 (idcfe serial, idcli serial, tipo_cliente integer,
situacao_praca integer);
alter table cli2 add constraint pk_cli2 primary key (idcfe, idcli);

create table for2 (idcfe serial, idfor serial, tipo_fornecedor integer,
debito numeric(15,6)) 
alter table for2 add constraint pk_for2 primary key (idcfe, idfor);

insert into cli (fantasia, datahora, tipo_cliente, situacao_praca)
"many records"
insert into "for" (fantasia, datahora, tipo_fornecedor, debito)
"many records"

insert into cfe2
"many records"
insert into cli2 (idcfe, tipo_cliente, situacao_praca)
"many records"
insert into for2 (idcfe, tipo_fornecedor, debito)
"many records"

select * from cfe2 (the data returns normal)
select * from cfe  (the data returns normal)
select * from cfe2 (>>>>return rows with "null" records that do not exist in
table<<<<<)
From here any table queried that has many records will return the null lines
that do not exist.

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> I was working with table inheritance and started having problems with my
> queries.
> I noticed that every time I perform a query on a parent table of an
> inheritance and perform a query on any table then with more records than the
> preceding table, postgres returns me rows with all records in null that do
> not exist in the table that I'm consulting.

FWIW, I tried this case with 1000000 somewhat-random records per table,
and couldn't see any misbehavior.   I wonder how much is "many records"
for you.  Also, what client-side code are you using?  If it's not psql,
can you reproduce the problem in psql?

            regards, tom lane