Bug #102343 Use FOREIGN KEY description for JOIN tables

• Submitted: 22 Jan 2021 11:08
• Modified: 22 Jan 2021 14:05
• Reporter: Александр Ммммммм
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[22 Jan 2021 11:08] Александр Ммммммм

Description:
Example:
CREATE TABLE organizations (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
    , name VARCHAR(191) NOT NULL UNIQUE
)
;
CREATE TABLE documents (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
    , date DATE NOT NULL
    , organization_id_warehouse INT UNSIGNED NOT NULL
    , organization_id_client INT UNSIGNED NOT NULL
    , organization_id_manager INT UNSIGNED
    , CONSTRAINT document_warehouse FOREIGN KEY (organization_id_warehouse) REFERENCES organizations (id)
    , CONSTRAINT document_client FOREIGN KEY (organization_id_client) REFERENCES organizations (id)
    , CONSTRAINT document_manager FOREIGN KEY (organization_id_manager) REFERENCES organizations (id)
)
;

Usual SELECT syntax:
SELECT d.id, d.date, w.name warehouse, cl.name client, m.name manager
FROM documents d
INNER JOIN organizations w ON d.organization_id_warehouse = w.id
INNER JOIN organizations cl ON d.organization_id_client = cl.id
LEFT JOIN organizations m ON d.organization_id_manager = m.id

But all needed information for JOIN we have in FOREIGN KEY description!

New syntax:
SELECT d.id, d.date, w.name warehouse, cl.name client, m.name manager
FROM documents d
INNER JOIN d.document_warehouse w -- Just syntax sugar for "organizations w ON d.organization_id_warehouse = w.id"
INNER JOIN d.document_warehouse cl -- Just syntax sugar for "organizations w ON d.organization_id_warehouse = w.id"
LEFT JOIN d.document_manager m -- Just syntax sugar for "organizations m ON d.organization_id_manager = m.id"

Usual syntax in scalar query:
SELECT d.id, d.date
, (SELECT m.name FROM organizations m WHERE d.organization_id_manager = m.id) manager
FROM documents d

Equivalent next query with new syntax:
SELECT d.id, d.date
, (SELECT m.name FROM d.document_manager m) manager
FROM documents d

What benefits with new syntax:
- less code, code more compact
- named foreign key constraint in SELECT query can better describe the relationship and essence of the data, queries will become more compact and readable
- no need duplicate code (join columns clause) in each query
- if I change FOREIGN KEY declaration (maybe change columns or add new columns), I no need fix all my hundreds SELECT queries in application! SELECT queries will work right with new FOREIGN KEY declaration
- this is not implemented in any RDBMS, it will be a competitive advantage

How to repeat:
see Description

[22 Jan 2021 11:12] Александр Ммммммм

Some missprint. Right version:
New syntax:
SELECT d.id, d.date, w.name warehouse, cl.name client, m.name manager
FROM documents d
INNER JOIN d.document_warehouse w -- Just syntax sugar for "organizations w ON d.organization_id_warehouse = w.id"
INNER JOIN d.document_client cl -- Just syntax sugar for "organizations cl ON d.organization_id_warehouse = cl.id"
LEFT JOIN d.document_manager m -- Just syntax sugar for "organizations m ON d.organization_id_manager = m.id"

[22 Jan 2021 14:05] MySQL Verification Team

Hi Mr. Mmmmmmm,

Thank you for your feature request.

However, we do not find it acceptable. Simply, SQL standard requires a definition of the common column. Some users simply want to get a Cartesian product and not a join, so this non-standard change would affect their applications.

There are some client programs, mostly GUI ones, out there, who are able to create queries based on few choices made. That would be a good feature request for some GUI client of your choice.

Not a feature request for the server.