Details
-
New Feature
-
Status: In Review (View Workflow)
-
Critical
-
Resolution: Unresolved
-
None
-
Q2/2025 Development
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 table 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
-
-
-
- Open
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link | This issue is part of MDEV-10872 [ MDEV-10872 ] |
| Labels | Compatibility |
| Epic Link | MDEV-10872 [ 58182 ] |
| Link | This issue is part of MDEV-10872 [ MDEV-10872 ] |
| Workflow | MariaDB v3 [ 82606 ] | MariaDB v4 [ 130715 ] |
| Description |
add support for oracle's left join syntax - the ( + )
example syntax: SELECT * FROM table1, table2 WHERE table1.rec_num = table2.fk_table1( + ); |
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 |
| Link |
This issue is duplicated by |
| Link | This issue is part of MDEV-19162 [ MDEV-19162 ] |
| Link | This issue is part of MDEV-19162 [ MDEV-19162 ] |
| Parent | MDEV-19162 [ 74215 ] | |
| Issue Type | Task [ 3 ] | Technical task [ 7 ] |
| Epic Link | MDEV-10872 [ 58182 ] |
So far the idea is to mark Item_field on parsing and bring the parking to TABLE_LIST during name resolution when Item_field finds its table.
Should it work for outer fields? (I think no).
Also cases of duplicationg and conflicting settings should be processed (several as well as with LEFT/RIGHT/FULL join usual syntax)
| Link | This issue blocks MENT-2086 [ MENT-2086 ] |
| Link | This issue blocks MDEV-34323 [ MDEV-34323 ] |
| Link | This issue is part of MENT-2216 [ MENT-2216 ] |
| Link | This issue is part of MDEV-35973 [ MDEV-35973 ] |
| Link | This issue is part of MENT-2216 [ MENT-2216 ] |
| Labels | Compatibility | Compatibility Oracle |
| Assignee | Sergei Golubchik [ serg ] |
| Fix Version/s | 11.9 [ 29945 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Sergei Golubchik [ serg ] | Alexander Barkov [ bar ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Labels | Compatibility Oracle | Compatibility Oracle contribution |
| Assignee | Alexander Barkov [ bar ] | Oleksandr Byelkin [ sanja ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
LEFT (RIGHT) outer join flag of tables checked only in optimizer and CTE check it on PREPARE after HAVING processed.
Also according to https://docs.oracle.com/en/database/oracle//oracle-database/18/sqlrf/Joins.html#GUID-29A4584C-0741-4E6A-A89B-DCFAA222994A :
You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.
|
So we can reorder table list to make LEFT joins.
Such thing is OK:
select t1.a , t2.a from t1, t2, t3 where t1.a+ t2.a(+) and 22/(t1.a + t3.a(+)); |
and this is not (it looks like only one operator per AND:
select t1.a , t2.a from t1, t2, t3 where t1.a+ t2.a(+) + t3.a(+)
|
*
|
|
|
ERROR at line 1:
|
ORA-01468: a predicate may reference only one outer-joined table
|
It looks like fields of the same table should always go with the operator if mentioned several times:
select t1.a , t2.a from t1, t2, t3 where t1.a = t2.a(+) and (t1.a = (t3.a + t3.a(+)))
|
*
|
|
|
ERROR at line 1:
|
ORA-01416: two tables cannot be outer-joined to each other
|
|
|
select t1.a , t2.a from t1, t2, t3 where t1.a = t2.a(+) and (t1.a = (t3.b + t3.a(+)))
|
*
|
|
|
ERROR at line 1:
|
ORA-01416: two tables cannot be outer-joined to each other
|
This is OK:
select t1.a , t2.a from t1, t2, t3 where t1.a = t2.a(+) and (t1.a = (t3.a(+) + t3.a(+)));
|
select t1.a , t2.a from t1, t2, t3 where t1.a = t2.a(+) and (t1.a = (t3.b(+) + t3.a(+)));
|
"Chain" is OK and "tree" is also OK:
select t1.a , t2.a, t3.a from t1, t2, t3 where t1.a = t2.a(+) and t2.a = t3.a(+);
|
select t1.a , t2.a, t3.a from t1, t2, t3 where t1.a = t2.a(+) and t1.a = t3.a(+);
|
create table t1 (a int);
|
insert into t1 values (1),(2),(3);
|
create table t2 (a int);
|
insert into t2 values (2),(1),(20);
|
create table t3 (a int, b int);
|
insert into t3 values (3,2),(10,3),(2,20);
|
select t1.a , t2.a, t3.a from t1, t2, t3 where t1.a = t2.a(+) and t2.a = t3.a(+) and t3.a = t1.a;
|
No cycles
select t1.a , t2.a, t3.a from t1, t2, t3 where t1.a = t2.a(+) and t2.a = t3.a(+) and t3.a = t1.a (+)
|
*
|
|
|
ERROR at line 1:
|
ORA-01416: two tables cannot be outer-joined to each other
|
Oracle:
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, e int);
|
insert into t3 values (3,2),(10,3),(2,20);
|
create table t4 (d int);
|
insert into t4 values (3),(1),(20);
|
select t1.a, t4.d, t2.b, t3.c from t1, t2, t3, t4 where t1.a = t2.b(+) and t1.a = t3.c(+) and t4.d = t2.b(+) and t4.d = t3.c(+);
|
A D B C
|
3 3 3
|
3 1
|
2 20
|
2 3
|
2 1
|
3 20
|
1 3
|
1 1 1
|
1 20
|
above should be translated to "chain":
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, e int); |
insert into t3 values (3,2),(10,3),(2,20); |
create table t4 (d int); |
insert into t4 values (3),(1),(20); |
select t1.a, t4.d, t2.b, t3.c from (t1,t4) left join (t2 , t3) on (t1.a = t2.b and t1.a = t3.c and t4.d = t2.b and t4.d = t3.c);
|
a d b c
|
1 3 NULL NULL |
2 3 NULL NULL |
3 3 NULL NULL |
1 1 NULL NULL |
2 1 NULL NULL |
3 1 NULL NULL |
1 20 NULL NULL |
2 20 NULL NULL |
3 20 NULL NULL |
select t1.a, t4.d, t2.b, t3.c from (t1,t4) left join t2 on (t1.a = t2.b and t4.d = t2.b) left join t3 on (t1.a = t3.c and t4.d = t3.c);
|
a d b c
|
3 3 NULL 3 |
1 1 1 NULL |
1 3 NULL NULL |
2 3 NULL NULL |
2 1 NULL NULL |
3 1 NULL NULL |
1 20 NULL NULL |
2 20 NULL NULL |
3 20 NULL NULL |
select t1.a, t4.d, t2.b, t3.c from (t1,t4) left join t3 on (t1.a = t3.c and t4.d = t3.c) left join t2 on (t1.a = t2.b and t4.d = t2.b) ;
|
a d b c
|
1 1 1 NULL |
3 3 NULL 3 |
1 3 NULL NULL |
2 3 NULL NULL |
2 1 NULL NULL |
3 1 NULL NULL |
1 20 NULL NULL |
2 20 NULL NULL |
3 20 NULL NULL |
several tables on the "inner" side of outer join is OK
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, e int);
|
insert into t3 values (3,2),(10,3),(2,20);
|
create table t4 (d int);
|
insert into t4 values (3),(1),(20);
|
select t1.a, t4.d, t2.b, t3.c from t1, t2, t3, t4 where t1.a + t3.c = t2.b(+) and t1.a = t3.c(+) and t4.d = t2.b(+) and t4.d = t3.c(+);
|
A D B C
|
3 3 3
|
3 1
|
2 1
|
1 1
|
2 3
|
1 3
|
2 20
|
3 20
|
1 20
|
| Link | This issue relates to MDEV-36055 [ MDEV-36055 ] |
expression with OR prohibited on any level
elect t1.a, t4.d, t2.b, t3.c from t1, t2, t3, t4 where (t1.a OR t2.b(+)) and t1.a = t3.c(+) and t4.d = t2.b(+) and t4.d = t3.c(+)
|
*
|
|
|
ERROR at line 1:
|
ORA-25556: A predicate without outer join operator (+) not allowed in operand
|
of OR or IN when outer join operator (+) is present
|
With IN it is more complex, it does not allowed in IN list bit allowed in IN left argument:
select t1.a, t4.d, t2.b, t3.c from t1, t2, t3, t4 where (t2.b(+) in (t1.a, t1.a+1)) and t1.a = t3.c(+) and t4.d = t2.b(+) and t4.d = t3.c(+);
|
A D B C
|
1 1 1
|
3 1
|
2 1
|
3 3 3
|
2 3
|
1 3
|
2 20
|
3 20
|
1 20
|
select t1.a, t4.d, t2.b, t3.c from t1, t2, t3, t4 where (t1.a in (t2.b(+), 1)) and t1.a = t3.c(+) and t4.d = t2.b(+) and t4.d = t3.c(+)
|
*
|
|
|
ERROR at line 1:
|
ORA-25556: A predicate without outer join operator (+) not allowed in operand
|
of OR or IN when outer join operator (+) is present
|
|
Something complex
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 t3.c=t5.e(+);
|
A B C D E
|
3 3 |
2 2 2 |
1 1 1 |
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 E
|
1 1 1
|
2 2 2
|
3 3
|
|
|
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(+);
|
A B C D E
|
3 3 3
|
1 1 1
|
2 2 2
|
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
|
1 6 7
|
5 4 9
|
2 7 9
|
1 8 9
|
5 1 6
|
2 4 6
|
4 6 10
|
2 8 10
|
5 6 11
|
4 7 11
|
23 4
|
21 6
|
20 7
|
4 23
|
20 23
|
23 8
|
21 23
|
23 7
|
1 1
|
20 1
|
4 4
|
2 6
|
1 7
|
5 7
|
4 8
|
4 1
|
1 4
|
2 1
|
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
|
| Summary | add support for oracle's left join syntax - the ( + ) | add support for oracle left join syntax - the ( + ) |
| 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 |
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 {code} The task is to convert Oracle outer join operator to regular LEFT JOINs while keeping the original table order where possible. 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. 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 corrspondent 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 {code} |
| Parent | MDEV-19162 [ 74215 ] | |
| Issue Type | Technical task [ 7 ] | New Feature [ 2 ] |
| 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 {code} The task is to convert Oracle outer join operator to regular LEFT JOINs while keeping the original table order where possible. 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. 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 corrspondent 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 {code} |
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 The task is to convert Oracle outer join operator to regular LEFT JOINs while keeping the original table order where possible. 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. 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 corrspondent OUTER table and removed from WHERE clause. Example: {code:sql} 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 {code} Collected info: {code} 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 {code} 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: {code} t1 \ \ t2 t4 \ / \/ t3 t5 {code} 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: {code} t1, t2, t4, t3, t5 {code} 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: {code:sql} 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 {code} |
| 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 The task is to convert Oracle outer join operator to regular LEFT JOINs while keeping the original table order where possible. 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. 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 corrspondent OUTER table and removed from WHERE clause. Example: {code:sql} 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 {code} Collected info: {code} 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 {code} 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: {code} t1 \ \ t2 t4 \ / \/ t3 t5 {code} 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: {code} t1, t2, t4, t3, t5 {code} 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: {code:sql} 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 {code} |
Add support for oracle's left join syntax - the ( + )
example syntax: {code:sql} SELECT * FROM table1, table2 WHERE table1.rec_num = table2.fk_table1( + ); {code} Update: See https://www.techonthenet.com/oracle/joins.php for syntax examples h2. Implementation The task is to convert Oracle outer join operator to regular LEFT JOINs while keeping the original table order where possible. 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. 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 corrspondent OUTER table and removed from WHERE clause. Example: {code:sql} 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 {code} Collected info: {code} 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 {code} 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: {code} t1 \ \ t2 t4 \ / \/ t3 t5 {code} 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: {code} t1, t2, t4, t3, t5 {code} 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: {code:sql} 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 {code} |
| Description |
Add support for oracle's left join syntax - the ( + )
example syntax: {code:sql} SELECT * FROM table1, table2 WHERE table1.rec_num = table2.fk_table1( + ); {code} Update: See https://www.techonthenet.com/oracle/joins.php for syntax examples h2. Implementation The task is to convert Oracle outer join operator to regular LEFT JOINs while keeping the original table order where possible. 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. 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 corrspondent OUTER table and removed from WHERE clause. Example: {code:sql} 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 {code} Collected info: {code} 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 {code} 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: {code} t1 \ \ t2 t4 \ / \/ t3 t5 {code} 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: {code} t1, t2, t4, t3, t5 {code} 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: {code:sql} 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 {code} |
Add support for oracle's left join syntax - the ( + )
example syntax: {code:sql} SELECT * FROM table1, table2 WHERE table1.rec_num = table2.fk_table1( + ); {code} Update: See https://www.techonthenet.com/oracle/joins.php for syntax examples h2. Spec The task is to convert Oracle outer join operator to regular LEFT JOINs while keeping the original table order where possible. h3. 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. h2. Solution overview Note that one needs Name Resolution to have been done in order to interpret the query. Example: {code:sql} select * from t1,t2 where a(+)=b {code} Here, we need to know which column column a belongs to. * 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. h2. 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: {code:sql} 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 {code} Collected info: {code} 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 {code} 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: {code} t1 \ \ t2 t4 \ / \/ t3 t5 {code} 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: {code} t1, t2, t4, t3, t5 {code} 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: {code:sql} 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 {code} |
| Description |
Add support for oracle's left join syntax - the ( + )
example syntax: {code:sql} SELECT * FROM table1, table2 WHERE table1.rec_num = table2.fk_table1( + ); {code} Update: See https://www.techonthenet.com/oracle/joins.php for syntax examples h2. Spec The task is to convert Oracle outer join operator to regular LEFT JOINs while keeping the original table order where possible. h3. 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. h2. Solution overview Note that one needs Name Resolution to have been done in order to interpret the query. Example: {code:sql} select * from t1,t2 where a(+)=b {code} Here, we need to know which column column a belongs to. * 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. h2. 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: {code:sql} 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 {code} Collected info: {code} 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 {code} 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: {code} t1 \ \ t2 t4 \ / \/ t3 t5 {code} 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: {code} t1, t2, t4, t3, t5 {code} 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: {code:sql} 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 {code} |
Add support for oracle's left join syntax - the ( + )
example syntax: {code:sql} SELECT * FROM table1, table2 WHERE table1.rec_num = table2.fk_table1( + ); {code} Update: See https://www.techonthenet.com/oracle/joins.php for syntax examples h2. Spec The task is to convert Oracle outer join operator to regular LEFT JOINs while keeping the original table order where possible. h3. 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. h3. Need name resolution to interpret the (+) operator Note that one needs Name Resolution to have been done in order to interpret the query. Example: {code:sql} select * from t1,t2 where a(+)=b {code} Here, we need to know which column column a belongs to. h2. 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. h2. 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: {code:sql} 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 {code} Collected info: {code} 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 {code} 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: {code} t1 \ \ t2 t4 \ / \/ t3 t5 {code} 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: {code} t1, t2, t4, t3, t5 {code} 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: {code:sql} 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 {code} |
I missed important test from the pull request
CREATE TABLE tj1(a int, b int);
|
CREATE TABLE tj2(c int, d int);
|
|
|
INSERT INTO tj1 VALUES (1, 1);
|
INSERT INTO tj1 VALUES (2, 2);
|
INSERT INTO tj2 VALUES (2, 3);
|
|
|
SELECT * FROM tj1,tj2 WHERE tj1.a = tj2.c(+) AND (tj2.d(+)=3 OR tj2.c(+)=1);
|
| Fix Version/s | 12.1 [ 29992 ] | |
| Fix Version/s | 12.0 [ 29945 ] |
commit 6d60a36d53ee35af431af664a804cda651daf8c4 (HEAD -> bb-main-MDEV-13817, origin/bb-main-MDEV-13817)
|
Author: Oleksandr Byelkin <sanja@mariadb.com>
|
Date: Wed Feb 26 15:41:49 2025 +0100
|
MDEV-13817 add support for oracle left join syntax - the ( + )
|
|
Parser changes made by Alexander Barkov <bar@mariadb.com>.
|
|
Part of the tests made by Iqbal Hassan <iqbal@hasprime.com>.
|
Initially marking with ORA_JOIN flag made also by
|
Iqbal Hassan <iqbal@hasprime.com>.
|
|
Main idea is that parser mark fields with (+) with a flag
|
(ORA_JOIN).
|
|
During Prepare the flag bring to the new created items if needed.
|
|
Later after preparing (fix_firlds()) WHERE confition the
|
relations betweel the tables analyzed and tables reordered
|
so to make JOIN/LEFT JOIN operators in chain equivalent to
|
the query with oracle outer join operator (+).
|
|
Then the flags of (+) removed.
|
commit 0c6d9df14873233d371c24a240e02dfbf2f46e7f
|
Author: Oleksandr Byelkin <sanja@mariadb.com>
|
Date: Tue Mar 18 17:35:08 2025 +0100
|
Add Flags to Item::walk.
|
|
Removed hack passing flags through parametes.
|
|
Order of arguments changed to prevent uncontrolled merge
|
(also this order looks better)
|
| Assignee | Oleksandr Byelkin [ sanja ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
commit 554c6bd3005da9cc0b61de5d3ef74fe29ac89dc3 (HEAD -> bb-main-MDEV-13817, origin/bb-main-MDEV-13817)
|
Author: Oleksandr Byelkin <sanja@mariadb.com>
|
Date: Wed Feb 26 15:41:49 2025 +0100
|
MDEV-13817 add support for oracle left join syntax - the ( + )
|
|
Parser changes made by Alexander Barkov <bar@mariadb.com>.
|
|
Part of the tests made by Iqbal Hassan <iqbal@hasprime.com>.
|
Initially marking with ORA_JOIN flag made also by
|
Iqbal Hassan <iqbal@hasprime.com>.
|
|
Main idea is that parser mark fields with (+) with a flag
|
(ORA_JOIN).
|
|
During Prepare the flag bring to the new created items if needed.
|
|
Later after preparing (fix_firlds()) WHERE confition the
|
relations betweel the tables analyzed and tables reordered
|
so to make JOIN/LEFT JOIN operators in chain equivalent to
|
the query with oracle outer join operator (+).
|
|
Then the flags of (+) removed.
|
commit 418358d3a44649405874d9fa93ba0c83c82a2e2f
|
Author: Oleksandr Byelkin <sanja@mariadb.com>
|
Date: Tue Mar 18 17:35:08 2025 +0100
|
Add Flags to Item::walk.
|
|
Removed hack passing flags through parametes.
|
|
Order of arguments changed to prevent uncontrolled merge
|
(also this order looks better)
|
| Due Date | 2025-05-01 |
Notes from discussion about the parser with Bar:
The parser is modified to accept column references with , like
"table.column". They look similar to "dbname.function(arg)", so
Item_join_operator_plus was introduced to represent the . It is
only used at parser level. After the parser, names marked with
are Item_field objects with ORA_JOIN flag bit.
Need to add comments about this at
- make_item_func_call_generic() (it may return Item_field now)
- LEX::mark_item_ident_for_ora_join
TODO what happens for SP with this:
CREATE TABLE t1 (col1 int);
CREATE PROCEDURE p1()
BEGIN
DECLARE col1 AS ....;
SELECT ... FROM t1... WHERE .. col1 ...
is col1 considered to be an SP var reference or table column?
| Summary | add support for oracle left join syntax - the ( + ) | add support for oracle outer join syntax - the ( + ) |
| Description |
Add support for oracle's left join syntax - the ( + )
example syntax: {code:sql} SELECT * FROM table1, table2 WHERE table1.rec_num = table2.fk_table1( + ); {code} Update: See https://www.techonthenet.com/oracle/joins.php for syntax examples h2. Spec The task is to convert Oracle outer join operator to regular LEFT JOINs while keeping the original table order where possible. h3. 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. h3. Need name resolution to interpret the (+) operator Note that one needs Name Resolution to have been done in order to interpret the query. Example: {code:sql} select * from t1,t2 where a(+)=b {code} Here, we need to know which column column a belongs to. h2. 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. h2. 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: {code:sql} 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 {code} Collected info: {code} 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 {code} 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: {code} t1 \ \ t2 t4 \ / \/ t3 t5 {code} 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: {code} t1, t2, t4, t3, t5 {code} 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: {code:sql} 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 {code} |
Add support for oracle's left join syntax - the ( + )
example syntax: {code:sql} SELECT * FROM table1, table2 WHERE table1.rec_num = table2.fk_table1( + ); {code} Update: See https://www.techonthenet.com/oracle/joins.php for syntax examples h2. Spec The task is to convert Oracle outer join operator to regular LEFT JOINs while keeping the original table order where possible. h3. 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. h3. Need name resolution to interpret the (+) operator Note that one needs Name Resolution to have been done in order to interpret the query. Example: {code:sql} select * from t1,t2 where a(+)=b {code} Here, we need to know which table column a belongs to. h2. 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. h2. 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: {code:sql} 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 {code} Collected info: {code} 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 {code} 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: {code} t1 \ \ t2 t4 \ / \/ t3 t5 {code} 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: {code} t1, t2, t4, t3, t5 {code} 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: {code:sql} 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 {code} |
| Link | This issue relates to MDEV-36522 [ MDEV-36522 ] |
| Priority | Critical [ 2 ] | Major [ 3 ] |
| Sprint | Server 12.1 dev sprint [ 793 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Rank | Ranked lower |
| Rank | Ranked higher |
i tried and failed to add epic link "Oracle Compatibility" (MDEV-10872)