COALESCE() query yield different result with MJ vs. NLJ/HJ

From: Qingqing Zhou <zhouqq(dot)postgres(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: COALESCE() query yield different result with MJ vs. NLJ/HJ
Date: 2015-04-04 01:00:37
Message-ID: CAJjS0u0EQgKq=wRgnKu9GVOo+L8ebLXL1=8TvuFiWTf5dsgkHg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

The symptom is that the same join query yield different results with
MJ and NLJ/HJ. Here is a repro:

---
create table t1(a int);create table t2(b int);
insert into t1 values(10); insert into t2 values(2);
analyze t1; analyze t2;
set enable_mergejoin=on; set enable_nestloop=off; set enable_hashjoin=off;
explain analyze select a, b from t1 left join t2 on coalesce(a, 1) =
coalesce(b,1) where (coalesce(b,1))>0
set enable_mergejoin=off; set enable_nestloop=on; set enable_hashjoin=off;
explain analyze select a, b from t1 left join t2 on coalesce(a, 1) =
coalesce(b,1) where (coalesce(b,1))>0
---

A possible explanation is that in fix_join_expr_mutator(), we optimize
with the case that if child node already compute an expression then
upper node shall reuse it. In MJ, as coalesce() already computed in
sort node, thus the NULL is directly used for ExecQual(>0) for join
filter.

If we take out this optimization the problem is solved but may looks
like an overkill. What's a better fix?

Thanks,
Qingqing

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2015-04-04 03:20:17 Re: Abbreviated keys for Numeric
Previous Message Noah Misch 2015-04-04 00:32:08 Re: Sloppy SSPI error reporting code