How can I get sqlglot to transpile this correctly?

[ikasovitch]

Hello,

Thank you very much for implementing sqlglot. It is very helpful!
I bumped into an issue while testing it out. Maybe I am doing something wrong so please let me know how to fix my code.

import sqlglot
print(sqlglot.transpile("select * from a,b where a.c=b.c(+)", read="oracle", write="postgres")[0])

I got the following result:
SELECT * FROM a, b WHERE a.c = b.c

I was expecting that the output result will have something with a left outer join but as you can see it didn't. See here - https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion#Outer_Joins

Can you advise how to make the transpile function work as I expect?

[georgesittas]

Hey @ikasovitch 👋

This seems reasonable, as we don't have extended support for the old-style joins of Oracle (i.e. (+)) and so transpilation is not very robust for them yet.

I can take a look soon and see if I can fix this easily. Thanks for the report!

[ikasovitch]

I don't think it's possible to apply the operator without qualifying the column name with the table name. This is what I found on oracle documentation: https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm#SQLRF52331

I found this statement:

In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C. Refer to SELECT for the syntax for an outer join.

Not sure if this helps in understanding how the old style join syntax work.

[ikasovitch]

Also found this on the join_clause:

To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, any available statistics for the tables.

From this document https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm#SQLRF52331

[tobymao]

not sure you can do this without a schema, probably better to not spend more time trying to implement this

[georgesittas]

@ikasovitch this seems a bit more complex than initially expected due to the above complications. I think I'll just have the generator print a warning when transpiling the join marker in other dialects for now, so it's easy to spot and fix manually if needed.

This can probably also be solved with a custom transformation, if you're interested in looking into that for your use case.

[ikasovitch]

Thanks for looking into that @georgesittas! Adding a warning is enough for now.