Details
-
New Feature
-
Status: In Progress (View Workflow)
-
Critical
-
Resolution: Unresolved
-
None
Description
Add support for oracle's left join syntax - the ( + )
example syntax:
SELECT * FROM table1, table2 WHERE table1.rec_num = table2.fk_table1( + ); |
Update:
See https://www.techonthenet.com/oracle/joins.php for syntax examples
Spec
The task is to convert Oracle outer join operator to regular LEFT JOINs while
keeping the original table order where possible.
Limitations on where the 
operator can be used
The FROM clause of such queries can contain only comma-separated list of
tables (no JOIN operators of any kind or parentheses)
Oracle's outer join operator (+) can only occur in the WHERE clause.
If the WHERE condition has AND at the top, then each argument of the AND is
considered for finding the EXPRESSION operator independently. Otherwise,
the whole WHERE condition is considered for finding the EXPRESSION.
OR are not allowed in the expressions with the operator (TODO: check if AND
is allowed deep inside the EXPRESSION), also the operator is not
allowed in the right side of IN.
The EXPRESSION with the operator can refer only many or 1 table. All fields
of one table should be mentioned with the operator (the OUTER side) the
other tables is the INNER side. If the INNER part is absent (the EXPRESSION
has only constants and the OUTER table) the operator is just ignored.
Need name resolution to interpret the operator
Note that one needs Name Resolution to have been done in order to interpret the query. Example:
select * from t1,t2 where a(+)=b |
Here, we need to know which column column a belongs to.
Solution overview
- In Oracle mode, the parser shall accept the (+) operator, where allowed.
- The parser shall generate special kind of items for (+) (TODO: or is it just Item objects with special flag?)
- Sometime after the Name Resolution, we will find all items marked with (+) and based on them, convert the inner joins into into outer join data structures.
- It should be possible to print back the parse-able LEFT JOIN syntax. This is needed for VIEWs (sql_mode is not saved in the VIEW. it's only saved to PS. So, we can't write (+) when saving VIEW definition.
Generating LEFT JOIN structures from operators
For each table in the FROM clause list we allocate descriptors where we
collect its relations with other tables (list of tables that are
OUTER w.r.t. this one and list of tables which are INNER as well as list
of EXPRESSIONs where the relation was found attached to corespondent OUTER
table and removed from WHERE clause.
Example:
SELECT * |
FROM
|
t1,t2,t3,t4,t5
|
WHERE
|
t1.a=t2.b(+) AND t2.b=t3.c(+) AND |
t4.d=t3.c(+) AND |
t1.a=t5.e
|
Collected info:
Table: t1 order: 0
|
Outer side: t2
|
Table: t2 order: 1
|
On: "test"."t1"."a" = "test"."t2"."b"
|
Inner side: t1
|
Outer side: t3
|
Table: t3 order: 2
|
On: "test"."t2"."b" = "test"."t3"."c"
|
On: "test"."t4"."d" = "test"."t3"."c"
|
Inner side: t2
|
Inner side: t4
|
Table: t4 order: 3
|
Outer side: t3
|
Table: t5 order: 4
|
The relation makes connected graph (cycles found and error issued during the
graphs analyzes).
Above there is only one real connected graph and one degraded:
t1
|
\
|
\
|
t2 t4
|
\ /
|
\/
|
t3 t5
|
Top of each branch are "independent" tables (i.e. they are not OUTER table
for others and so do not have INNER side tables connected to them)
in the examples t1, t3 and t5 are independent in the example.
So the idea is to scan the list of the tables in the order of FROM list and
as we find "independent" table we include it in the new list and than
process its connected graph recursively putting directly OUTER tables just
after this (sorted by position in the original list ("order")). If included
table has INNER side tables from that relation put between the
"independent" table and the OUTER according to its "order" and procedure
processing its relation processed recursively.
We mark processed tables (included in the new list) which allow to detect
possible cyclic relation and issue an error.
If after the scan left unprocessed tables it is ring relations and so an
error.
So after processing we will have new table order, for example it is:
t1, t2, t4, t3, t5
|
so if table has INNER side we "replace" "," before the table with LEFT JOIN
.. ON (...) and if no with just JOIN.
so the above example becomes:
FROM t1 |
LEFT JOIN t2 ON ("test"."t1"."a" = "test"."t2"."b") |
JOIN t4 |
LEFT JOIN t3 ON ("test"."t2"."b" = "test"."t3"."c" AND |
"test"."t4"."d" = "test"."t3"."c") |
JOIN t5 |
WHERE
|
t1.a=t5.e
|
Attachments
Issue Links
- blocks
-
-
- Open
-
- is duplicated by
-
MDEV-20650
sql_mode="oracle" does not support ancient outer join syntax (+)
-
- Closed
-
- is part of
-
MDEV-35973 Oracle Compatibility Project 1 for 2025
-
- In Progress
-
- relates to
-
-
- Open
-
Activity
neted joins are not allowed with oracle outer join operator as expected:
select t1.a, t2.b, t3.c, t4.d, t5.e from t1, (t2, t3), t4, t5 where t1.a = t2.b(+) and t2.b = t3.c(+) and t1.a = t4.d(+) and t4.d=t5.e(+) and 3=t5.e(+)
|
*
|
|
|
ERROR at line 1:
|
ORA-00907: missing right parenthesis
|
Oracle outer join operator in expression with no other table should be ignored:
create table t1 (a int);
|
insert into t1 values (1),(2),(3);
|
create table t2 (b int);
|
insert into t2 values (2),(1),(20);
|
|
|
select t1.a, t2.b from t1, t2 where 1 = t2.b(+);
|
A B
|
1 1
|
2 1
|
3 1
|
|
|
select t1.a, t2.b from t2,t1 where 1 = t2.b(+);
|
|
|
A B
|
1 1
|
2 1
|
3 1
|
|
|
create table t1 (a int);
|
insert into t1 values (1),(2),(3);
|
create table t2 (b int);
|
insert into t2 values (2),(1),(20);
|
|
|
select t1.a,t2.b from t2,t1 where t2.b(+) in (1,2);
|
A B
|
1 2
|
2 2
|
3 2
|
1 1
|
2 1
|
3 1
|
|
|
create table t2 (b int);
|
insert into t2 values (2),(1),(20);
|
|
|
select t2.b from t2 where 1 = t2.b(+);
|
|
|
B
|
1
|
Directed acyclic graphs allowed
create table t1 (a int);
|
insert into t1 values (1),(2),(4),(5),(20),(21),(23);
|
create table t2 (b int);
|
insert into t2 values (1),(4),(6),(7),(8),(23);
|
create table t3 (c int);
|
insert into t3 values (4),(7),(9),(4),(6),(10),(11),(1);
|
create table t4 (d int);
|
insert into t4 values (1),(4),(10),(12),(20),(21),(23);
|
SELECT * FROM t1,t2,t3,t4 WHERE t1.a = t2.b(+) AND t1.a = t3.c(+) AND t2.b=t4.d(+) AND t3.c=t4.d(+);
|
A B C D
|
1 1 1 1
|
4 4 4 4
|
4 4 4 4
|
23 23
|
2
|
21
|
5
|
20
|
many "Inner" relation in one expression allowed
create table t1 (a int);
|
insert into t1 values (1),(2),(4),(5),(20),(21),(23);
|
create table t2 (b int);
|
insert into t2 values (1),(4),(6),(7),(8),(23);
|
create table t3 (c int);
|
insert into t3 values (4),(7),(9),(4),(6),(10),(11),(1);
|
|
|
select * from t1, t2, t3 where (t1.a + t2.b = t3.c(+));
|
|
|
A B C
|
21 1
|
20 6
|
21 7
|
20 8
|
5 23
|
23 23
|
23 6
|
21 8
|
5 8
|
21 4
|
2 23
|
23 1
|
20 4
|
1 23
|
Constants are allowed
create table t1 (a int);insert into t1 values (1),(2),(3);create table t2 (b int);insert into t2 values (2),(1),(20);create table t3 (c int, f int);insert into t3 values (3,2),(10,3),(2,20);create table t4 (d int);insert into t4 values (3),(1),(20);create table t5 (e int);insert into t5 values (3),(2),(20);select t1.a, t2.b, t3.c, t4.d, t5.e from t1, t2, t3, t4, t5 where t1.a = t2.b(+) and t2.b = t3.c(+) and t1.a = t4.d(+) and t4.d=t5.e(+) and 1=t5.e(+);A B C D E1 1 12 2 23 3select t1.a, t2.b, t3.c, t4.d, t5.e from t1, t2, t3, t4, t5 where t1.a = t2.b(+) and t2.b = t3.c(+) and t1.a = t4.d(+) and t4.d=t5.e(+) and 3=t5.e(+);A B C D E3 3 31 1 12 2 2