FULL OUTER JOIN is rendering as LEFT OUTER JOIN

[windiana42]

While testing with various database backends, I noticed, that ibm_db_sa is not rendering FULL OUTER JOIN correctly. Sorry, that I don't give reproducing code here, but it is quite obvious from the implementation that ibm_db_sa cannot render a FULL OUTER JOIN. It does not look at join.full:

https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/base.py#L472-L480

    def visit_join(self, join, asfrom=False, **kwargs):
        # NOTE: this is the same method as that used in mysql/base.py
        # to render INNER JOIN
        return ''.join(
            (self.process(join.left, asfrom=True, **kwargs),
             (join.isouter and " LEFT OUTER JOIN " or " INNER JOIN "),
             self.process(join.right, asfrom=True, **kwargs),
             " ON ",
             self.process(join.onclause, **kwargs)))

[windiana42]

I am currently fixing this problem with a workaround:

# 1) DB2-specific compilation for FULL OUTER JOIN
@compiles(Join, "ibm_db_sa")
def _compile_join_db2(join, compiler, **kwargs):
    # If this is a FULL join, force FULL OUTER JOIN keyword
    kwargs = kwargs.copy()
    kwargs["asfrom"] = True
    if getattr(join, "full", False):
        return ''.join(
            (compiler.process(join.left, **kwargs),
             " FULL OUTER JOIN ",
             compiler.process(join.right, **kwargs),
             " ON ",
             compiler.process(join.onclause, **kwargs)))
    # Otherwise, fall back to default behavior for LEFT/INNER
    return compiler.visit_join(join, **kwargs)

[bchoudhary6415]

Hello @windiana42 ,

Thank you for reporting this issue.

I was able to reproduce the problem, and I’ll be updating the code shortly. I plan to raise a PR that includes support and examples for INNER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN.

Thanks again for bringing this to our attention!

[bchoudhary6415]

Hi @windiana42,

The PR for this issue (#162) has been merged.
I’m closing the issue now.

Thank you!