Re: Subselect left join / not exists()

Hi Tom,

Yes I did not provide nearly enough information. I was cheating in hope for
a quick answer of something anyone may have encountered before me of the
same nature.

I have been unable to reproduce the effect in an isolated test case, only
on my live production setup. I came a bit closer to what the problem may be.

I did not see this behaviour on 8.4.22 until I started with partial
indexing on a large table. A typical index was:

create index indx_lp_contract_iscash_true on loan_Payments (ContractKey,
sp_payment_iscash(DKey)) where sp_payment_iscash(DKey) = true;

I know you would need more info, but please bare with me. When I dropped
this type of index from this table, the broken effect went away and I got
healthy results from my sub selects.

I started building a test system based on Postgres 9.3.11. I took the SQL
dumps from my 8.4.22 setup and started restoring it on the 9.3.11 setup. In
the log, I started seeing this during restore:

ERROR: could not open relation with OID 36212
CONTEXT: SQL statement "SELECT exists(select * from loan_Payments lp
left join loan_payment_detail_nupay lpdn on
lpdn.loan_payment_id = lp.DKey
left join loan_payment_detail_bank_deposit lpdbd on
lpdbd.loan_payment_id = lp.DKey
left join loan_payment_detail_mctdebit lpdmct on
lpdmct.loan_payment_id = lp.DKey
left join loan_payment_detail_cashbook lpdcb on
lpdcb.loan_payment_id = lp.DKey
where lp.DKey = apaymentid and (lp.Type = 0 or
lpdn.loan_payment_id = apaymentid or lpdbd.loan_payment_id = apaymentid or
lpdmct.loan_payment_id = apaymentid or lpdcb.loan_payment_id = apaymentid)
and lp.Payment <> 0
)"
PL/pgSQL function sp_payment_iscash(bigint) line 3 at RETURN
STATEMENT: CREATE INDEX indx_lp_iscash_true ON loan_payments USING btree
(sp_payment_iscash((dkey)::bigint)) WHERE
(sp_payment_iscash((dkey)::bigint) = true);

This log message in 9.3.11 put me on the path to drop all the partial index
referencing my boolean function sp_payment_iscash on the 8.4.22 live setup.
This returned sanity to my reports.

I am working on my test case to try to invoke the behaviour on both 8.4.22
and 9.3.11.

I have an idea the problem may be that the function accepts int8 as a
parameter while the table is of int4 primary key type, where the primary
key of the table is passed to the function during partial index. More
testing and info will follow.

Regards.

On Fri, Feb 26, 2016 at 5:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Desmond Coertzen <patrolliekaptein(at)gmail(dot)com> writes:
> > On Postgres 8.4.22.
>
> You realize of course that 8.4.x has been out of support for more than
> a year ...
>
> > The first form of the query looked like:
>
> > select lots, of, stuff,
> > (select max(ls2.fiscal_ts)::date
> > from long_story ls2
> > where ls2.contract_id = ls.contract_id and ls2.tr_value > 0 and
> > sp_tr_is_cash(ls2.primary_key_id)
> > and not exists(select * from long_story ls2r where
> ls2r.reverse_of_pk_id =
> > ls2.primary_key_id)
> > ) as last_cash_tr_ts
> > from long_story ls
> > where ls.create_ts >= current_date and ls.tr_type_id = 4;
>
> > The subselect columm "last_cash_tr_ts" produces null or bogus result.
>
> You haven't provided nearly enough detail for anyone to judge whether
> this is actually a bug or just your wrong expectation of what should
> happen. If you'd like people to look into it, please provide a
> self-contained test case: not only the query but table definitions
> and sample data. (Ideally, a SQL script that reproduces the problem
> starting from an empty database would make it easy for people to test.
> We're not likely to take the time to try to reverse-engineer context
> from an incomplete bug report.)
>
> If it is a bug, it will not get fixed in 8.4.x anyway, because there
> will never be any more 8.4.x releases. However, if the bug still exists
> in newer release branches, we'd definitely endeavor to fix it there.
>
> regards, tom lane
>