Postgres does not support a `Full` join with `Between` condition

[radeusgd]

The Postgres connector currently fails when performing a Full join with a Between condition, see the following repro:

from Standard.Base import all
from Standard.Table import all
from Standard.Database import all

run_between connection =
    a = (Table.new [["X", [1, 2, 3]]]).select_into_database_table connection "a"+Random.uuid temporary=True
    b = (Table.new [["lo", [2, 100]], ["hi", [4, 101]]]).select_into_database_table connection "b"+Random.uuid temporary=True
    r = a.join b join_kind=Join_Kind.Full on=(Join_Condition.Between "X" "lo" "hi")
    IO.println r
    r.print

main =
    run_between (Database.connect (SQLite In_Memory))
    run_between (connect_to_postgres)


## Connects to the Postgres DB as configured for Enso Unit Tests.
connect_to_postgres =
    hostname = Environment.get "ENSO_DATABASE_TEST_HOST"
    db_name = Environment.get "ENSO_DATABASE_TEST_DB_NAME"
    db_host_port = hostname.if_nothing "localhost" . split ':'
    db_host = db_host_port.at 0
    db_port = if db_host_port.length == 1 then 5432 else Integer.parse (db_host_port.at 1)
    db_user = Environment.get "ENSO_DATABASE_TEST_DB_USER"
    db_password = Environment.get "ENSO_DATABASE_TEST_DB_PASSWORD"
    case db_name.is_nothing of
        True ->
            IO.println "PostgreSQL test database is not configured. See README.md for instructions."
        False ->
            connection = Panic.rethrow <|
                IO.println "Connecting to PostgreSQL test database at "+hostname
                Database.connect (Postgres db_host db_port db_name credentials=(Credentials.Username_And_Password db_user db_password))
            connection

Running it gives:

(Database Table a03dc96a0-4c98-4e0a-a66a-655bf577995a_b857bebd0-8ba2-4034-80f0-dc243b4623d6)
 X       | lo      | hi
---------+---------+---------
 1       | Nothing | Nothing
 2       | 2       | 4
 3       | 2       | 4
 Nothing | 100     | 101

Connecting to PostgreSQL test database at 172.28.138.202
(Database Table abe05d9a6-1219-4dbb-8997-75f0167a77f3_b1300323f-162c-452a-b9ac-)
(Error: There was an SQL error: ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions. [Query was: SELECT "abe05d9a6-1219-4dbb-8997-75f0167a77f3"."X" AS "X", "b1300323f-162c-452 �])

[radeusgd]

The proposed solution is to essentially union a Left Outer join together with an anti-join:

from Standard.Base import all
from Standard.Table import all
from Standard.Database import all

run_between connection =
    a = (Table.new [["X", [1, 2, 3]]]).select_into_database_table connection "a"+Random.uuid temporary=True
    b = (Table.new [["lo", [2, 100]], ["hi", [4, 101]]]).select_into_database_table connection "b"+Random.uuid temporary=True
    r = a.join b join_kind=Join_Kind.Full on=(Join_Condition.Between "X" "lo" "hi")
    IO.println r
    
    case r.read.is_error of
        False -> r.print
        True ->
            # workaround
            c = Join_Condition.Between "X" "lo" "hi"
            r1 = a.join b join_kind=Join_Kind.Left_Outer on=c
            r2 = a.join b join_kind=Join_Kind.Right_Exclusive on=c
            r3 = r1.union r2
            IO.println r3
            r3.print

main =
    run_between (Database.connect (SQLite In_Memory))
    run_between (connect_to_postgres)

This shows that this would indeed yield the right results:

(Database Table ac7370746-3988-4d3f-aa44-93baa7d804ce_b18516d79-b1e7-417a-9510-82bc8c4596e4)
 X       | lo      | hi
---------+---------+---------
 1       | Nothing | Nothing
 2       | 2       | 4
 3       | 2       | 4
 Nothing | 100     | 101

Connecting to PostgreSQL test database at 172.28.138.202
(Database Table a562c56a4-2bfe-407c-947d-b555a07aa4c0_b1467c0d2-9a0b-4076-a33c-)
(Database Table a562c56a4-2bfe-407c-947d-b555a07aa4c0_b1467c0d2-9a0b-4076-a33 1)
 X       | lo      | hi
---------+---------+---------
 1       | Nothing | Nothing
 2       | 2       | 4
 3       | 2       | 4
 Nothing | 100     | 101

Now, of course a full solution must ensure column naming is handled correctly.

The workaround of relying on an UNION of 2 joins should only trigger in the Postgres backend and only if the join_kind == Full and on contains a Between condition. In all other cases, the 'classic' FULL join should be used because it is simpler and most likely will be more efficient.