Re: Postgres_fdw join pushdown - wrong results with whole-row reference

On 2016/06/21 16:27, Rushabh Lathia wrote:
> Now I was under impression the IS NOT NULL should be always in inverse of
> IS NULL, but clearly here its not the case with wholerow. But further
> looking at
> the document its saying different thing for wholerow:
>
> https://www.postgresql.org/docs/9.5/static/functions-comparison.html
>
> Note: If the expression is row-valued, then IS NULL is true when the row
> expression
> itself is null or when all the row's fields are null, while IS NOT NULL is
> true
> when the row expression itself is non-null and all the row's fields are
> non-null.
> Because of this behavior, IS NULL and IS NOT NULL do not always return
> inverse
> results for row-valued expressions, i.e., a row-valued expression that
> contains
> both NULL and non-null values will return false for both tests. This
> definition
> conforms to the SQL standard, and is a change from the inconsistent behavior
> exhibited by PostgreSQL versions prior to 8.2.
>
>
> And as above documentation clearly says that IS NULL and IS NOT NULL do not
> always return inverse results for row-valued expressions. So need to change
> the
> deparse logic into postgres_fdw - how ? May be to use IS NULL rather then IS
> NOT NULL?
>
> Input/thought?

Perhaps - NOT expr IS NULL? Like in the attached.

explain verbose select e, e.empno, d.deptno, d.dname from f_emp e left
join f_dept d on e.deptno = d.deptno and e.sal > 3000 order by 2, 3 limit 10;

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
Limit (cost=100.00..136.86 rows=10 width=236)
Output: e.*, e.empno, d.deptno, d.dname
-> Foreign Scan (cost=100.00..2304.10 rows=598 width=236)
Output: e.*, e.empno, d.deptno, d.dname
Relations: (public.f_emp e) LEFT JOIN (public.f_dept d)
Remote SQL: SELECT CASE WHEN NOT r1.* IS NULL THEN ROW(r1.empno,
r1.ename, r1.job, r1.mgr, r1.hiredate, r1.sal, r1.comm, r1.deptno) END,
r1.empno, r2.deptno
, r2.dname FROM (public.emp r1 LEFT JOIN public.dept r2 ON (((r1.sal >
3000::numeric)) AND ((r1.deptno = r2.deptno)))) ORDER BY r1.empno ASC
NULLS LAST, r2.deptno AS
C NULLS LAST
(6 rows)

select e, e.empno, d.deptno, d.dname from f_emp e left join f_dept d on
e.deptno = d.deptno and e.sal > 3000 order by 2, 3 limit 10;
e | empno | deptno | dname
-----------------------------------------------------------+-------+--------+------------
(7369,SMITH,CLERK,7902,1980-12-17,800.00,,20) | 7369 | |
(7499,ALLEN,SALESMAN,7698,1981-02-20,1600.00,300.00,30) | 7499 | |
(7521,WARD,SALESMAN,7698,1981-02-22,1250.00,500.00,30) | 7521 | |
(7566,JONES,MANAGER,7839,1981-04-02,2975.00,,20) | 7566 | |
(7654,MARTIN,SALESMAN,7698,1981-09-28,1250.00,1400.00,30) | 7654 | |
(7698,BLAKE,MANAGER,7839,1981-05-01,2850.00,,30) | 7698 | |
(7782,CLARK,MANAGER,7839,1981-06-09,2450.00,,10) | 7782 | |
(7788,SCOTT,ANALYST,7566,1987-04-19,3000.00,,20) | 7788 | |
(7839,KING,PRESIDENT,,1981-11-17,5000.00,,10) | 7839 |
10 | ACCOUNTING
(7844,TURNER,SALESMAN,7698,1981-09-08,1500.00,0.00,30) | 7844 | |
(10 rows)

Thanks,
Amit