[MDEV-31623] Unexpected behaviour of FULL JOIN Created: 2023-07-04  Updated: 2023-07-05  Resolved: 2023-07-05

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 11.0.2
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Ralf Adams Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Docker under Debian

Attachments: Text File error_report_fulljoin.txt    

 Description   

The option FULL JOIN works as an INNER JOIN. This is missleading, because you expect the result of a FULL OUTER JOIN or an error message.
RIGHT OUTER JOIN -> Ok
RIGHT JOIN -> Ok
LEFT OUTER JOIN -> Ok
LEFT JOIN -> Ok
FULL OUTER JOIN -> Ok, error message
FULL JOIN -> Not Ok, like INNER JOIN
MySQL Community Server 8.0.33 has the same missleading behaviour.

 Comments   
Comment by Alice Sherepa [ 2023-07-05 ]

There is no FULL join in MariaDb ( https://mariadb.com/kb/en/join-syntax/ ), so the word FULL was interpreted as an alias for the table- so no error, just inner join. In the second example - FULL OUTER JOIN - again FULL-alias, but then OUTER - it should be LEFT OUTER or RIGHT OUTER , otherwise - sql syntax error.

Comment by Ralf Adams [ 2023-07-05 ]

Thanks for this quick answer. This explains the behaviour. But I think it is very missleading in this case. It looks like a short version of a FULL OUTER JOIN because OUTER is optional and by not receiving an error message you may think that this is a FULL OUTER JOIN result.

Generated at Thu Feb 08 10:25:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.