MDEV-13817: Add support for oracle's left join syntax - the ( + ) in sql_mode=oracle

[iqbal-rsec]

trafficstars

• [x] The Jira issue number for this PR is: MDEV-13817

Description

Transform the select query if the WHERE clause contains (+) operator into ANSI join syntax (LEFT OUTER JOIN). i.e. SELECT * FROM t1, t2 WHERE t1.a = t2.a (+) AND t1.b = t2.b (+) is transformed into SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.a = t2.a AND t1.b = t2.b

Not all combinations of (+) operator are allowed, the following are not allowed:

• (+) operator with IN operator
• (+) operator with OR operator
  • Exception to this is when both operands of the OR operator contain the same table marked with (+)
• (+) operator with multiple tables in the same predicate
• All tables has fields marked with (+) operator (i.e. no tables without the (+) operator)

In addition, using (+) operator with ANSI joins is not allowed. Limitations above are similar to Oracle's limitations.

Release Notes

Add support for Oracle's left join syntax (+) in sql_mode=oracle

• https://mariadb.com/kb/en/sql_modeoracle/

How can this PR be tested?

mysql-test/mtr oracle_outer_join

Basing the PR against the correct MariaDB version

• [x] This is a new feature or a refactoring, and the PR is based against the main branch.
• [ ] This is a bug fix, and the PR is based against the earliest maintained branch in which the bug can be reproduced.

PR quality check

• [x] I checked the CODING_STANDARDS.md file and my PR conforms to this where appropriate.
• [x] For any trivial modifications to the PR, I am ok with the reviewer making the changes themselves.