Thread: Optimizer failed to generate plan

Hi PostgreSQL community,
    Hello guys，I am a DBA and found the flowing issue when doing test on
postgresql. is there any plan to fix this issue？Thanks and feedback are
welcome.


xianliang.ji
Company: esgyn
Email: xianliang.ji@esgyn.cn


[postgres@mdw postgresql]$ /home/postgres/postgresql/install/bin/psql
psql (15.2)
Type "help" for help.

postgres=# select version();
                                                  version
---------------------------------------------------------------------------------------------------------
  PostgreSQL 15.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

postgres=# CREATE TABLE dept(
postgres(#     deptno int,
postgres(#     dname VARCHAR(14),
postgres(#     loc VARCHAR(13)
postgres(#   );
CREATE TABLE
postgres=# CREATE TABLE emp(
postgres(#   empno int,
postgres(#   ename VARCHAR(10),
postgres(#   job VARCHAR(9),
postgres(#   mgr int,
postgres(#   hiredate DATE,
postgres(#   sal double precision,
postgres(#   comm double precision,
postgres(#   deptno int,
postgres(#   jobno int
postgres(# );
CREATE TABLE
postgres=# select a.*,b.* from EMP a FULL JOIN DEPT b on
a.deptno=b.deptno OR upper(ename)='BLAKE' where a.job='MANAGER' OR
b.dname='SALES';
ERROR:  FULL JOIN is only supported with merge-joinable or hash-joinable
join conditions
postgres=#

On Tue, 14 Feb 2023 at 00:18, <xianliang.ji@esgyn.cn> wrote:
> postgres=# select a.*,b.* from EMP a FULL JOIN DEPT b on
> a.deptno=b.deptno OR upper(ename)='BLAKE' where a.job='MANAGER' OR
> b.dname='SALES';
> ERROR:  FULL JOIN is only supported with merge-joinable or hash-joinable

It's not a bug. The problem is the join condition has OR conditions
and we can do a full Hash or Merge join for joins like that.

I'm a bit unclear on what you need exactly, but maybe you could get it
by repeating the query once for each branch of the OR and then
performing a UNION ALL.

David