[MDEV-7492] Dereferencing foreign keys / automating joins Created: 2015-01-22  Updated: 2015-02-18  Resolved: 2015-02-04

Status: Closed
Project: MariaDB Server
Component/s: Parser
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Olaf van der Spek Assignee: Jan Lindström (Inactive)
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

Suppose you've got a posts table with a uid foreign key into the users table.
Wouldn't it be nice if you could say
select *, uid->name, uid->avatar from posts
instead of
select p.*, u.name, u.avatar from posts p left join users u using (uid) ?

It'd save typing a lot of joins and significantly simplifies writing and reading queries.

Note the syntax isn't the important part, I simply used the C way of dereferencing a pointer.

 Comments   
Comment by Jan Lindström (Inactive) [ 2015-02-04 ]

I understant your idea but what you suggest is not part of SQL-standard. In my opinion we should not extend SQL syntax on this case.

Comment by Olaf van der Spek [ 2015-02-04 ]

Why not?
How is SQL ever going to improve if nobody extends it?

Comment by Jan Lindström (Inactive) [ 2015-02-04 ]

There is SQL-standard committee who improves and extentds the SQL-standard. SQL is abstract query language where implementation details are not specified, thus adding some kind of travelsal from table to another table using a foreing key implemented as some structure does not really make sense.

Comment by Olaf van der Spek [ 2015-02-04 ]

Why does it not make sense?
Foreign keys are used for joins all the time, simplifying those queries seems like a good goal to me.

Comment by Jan Lindström (Inactive) [ 2015-02-04 ]

Using foreign keys on joins is natural. What is not natural is the travelsal a->b. In SQL-level there is no knowledge that there is foreign key between users and posts, and there is no need to know it, Selection of used index (if present) is done at optimizer, not on parsing.

Comment by Olaf van der Spek [ 2015-02-04 ]

That's not true, foreign key relations are defined like :
alter table xbt_files_users add foreign key (fid) references xbt_files (fid) on delete cascade;

Comment by Jan Lindström (Inactive) [ 2015-02-04 ]

I know but we are talking different things here. Lets take your example select *, uid->name, uid->avatar from posts, how parser could know that name column is found from users table? There could be several foreign keys between columns uid and name from different tables, how to select the correct one ?

Comment by Olaf van der Spek [ 2015-02-04 ]

Not true, posts.uid references one and only one key/table.
users.uid could be referenced by multiple foreign keys but that's not a problem as we're starting with posts.uid.

Comment by Jan Lindström (Inactive) [ 2015-02-04 ]

That might be true for your database, but in general case a->b from t1 it is not trivial to find out correct table for column b.

Comment by Olaf van der Spek [ 2015-02-04 ]

Hmm, what's the syntax to create one foreign key that references two different things?

Comment by Jan Lindström (Inactive) [ 2015-02-04 ] 

create table t1(a int not null primary key) engine=innodb;
 
create table t2(a int not null primary key, b int foreign key references t1(a)) engine=innodb;
 
create table t3(a int not null primary key, b int foreign key references t1(a)) engine=innodb; 
select a->b from t1; -- ??? t2 or t3

Comment by Olaf van der Spek [ 2015-02-04 ]

t2.a and t3.a still reference only one table.. and t1.a is not a foreign key.

Comment by Jan Lindström (Inactive) [ 2015-02-04 ] 

create table t1(a int not null primary key, b int not null, unique index (b)) engine=innodb;
 
create table t3(a int not null primary key, b int not null, unique index (b)) engine=innodb;
 
create table t2(a int not null primary key, b int, foreign key (b) references t1(a), foreign key (b) references t3(b)) engine=innodb;
 
select b->b from t2; -- ?? t1 or t3

Comment by Olaf van der Spek [ 2015-02-04 ]

Since t1.a is not a foreign key it can't be dereferenced with this proposed feature.

Comment by Jan Lindström (Inactive) [ 2015-02-04 ]

Sorry, bug on select, t2.b is a foreign key.

Comment by Olaf van der Spek [ 2015-02-04 ]

Didn't know that was legal. So, only allow dereferencing for single / deterministic references.

Comment by Olaf van der Spek [ 2015-02-10 ]

So could you reopen this?

Comment by Jan Lindström (Inactive) [ 2015-02-18 ]

Sorry, I really do not see enough reason to implement what you are suggesting.

Generated at Thu Feb 08 07:20:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.