Query failure when FULL JOIN with not-equal predicate pushed down into PostgreSQL

[vlad-lyutenko]

During work on Tests for different types of join, we noticed bug that some datasource (like postgres) not support some types of joins (like FULL OUTER) with some operators (inequality - <> < <= DISTINCT).

So we 'd like to disable some type of joins, based on conditions AND join types

#14841 (comment)

[findepi]

bug that some datasource (like postgres) not support some types of joins (like FULL OUTER) with some operators.

is this bug visible to Trino users?

what's the example problematic query?

[vlad-lyutenko]

In our test for FULL join with inequality operator for postgres
(SELECT r.name, n.name FROM nation n FULL JOIN region r ON n.regionkey <> r.regionkey)
we will get:

io.trino.spi.TrinoException: ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions
   at io.trino.plugin.jdbc.JdbcRecordCursor.handleSqlException(JdbcRecordCursor.java:305)
   at io.trino.plugin.jdbc.JdbcRecordCursor.advanceNextPosition(JdbcRecordCursor.java:179)
   at io.trino.spi.connector.RecordPageSource.getNextPage(RecordPageSource.java:88)
   at io.trino.operator.TableScanOperator.getOutput(TableScanOperator.java:311)
   at io.trino.operator.Driver.processInternal(Driver.java:411)
   at io.trino.operator.Driver.lambda$process$10(Driver.java:314)
   at io.trino.operator.Driver.tryWithLock(Driver.java:706)
   at io.trino.operator.Driver.process(Driver.java:306)
   at io.trino.operator.Driver.processForDuration(Driver.java:277)
   at io.trino.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:739)
   at io.trino.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:164)
   at io.trino.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:515)
   at io.trino.$gen.Trino_testversion____20221103_121138_1.run(Unknown Source)
   at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
   at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
   at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.sql.SQLException: ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions
   at io.trino.plugin.jdbc.JdbcRecordCursor.advanceNextPosition(JdbcRecordCursor.java:159)
   ... 14 more
Caused by: java.util.concurrent.ExecutionException: org.postgresql.util.PSQLException: ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions
   at java.base/java.util.concurrent.FutureTask.report(FutureTask.java:122)
   at java.base/java.util.concurrent.FutureTask.get(FutureTask.java:191)
   at io.trino.plugin.jdbc.JdbcRecordCursor.advanceNextPosition(JdbcRecordCursor.java:154)
   ... 14 more

#14841 (comment)

[findepi]

So it's actually a user visible bug.
Thsanks @vlad-lyutenko for clarifying.
Can you reformualate the issue to "sound like a bug" (eg "Query failure when FULL JOIN with not-equal predicate pushed down into PostgreSQL") and give it the bug label?