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@sss.pgh.pa.us> wrote:

Desmond Coertzen <patrolliekaptein@gmail.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