Duplicate column name error

Off-Topic
1

I can execute a query like the following which has non-unique column names:

select t_author.id, t_book.id
from   t_author
join   t_book on t_author.id = t_book.author_id

But as soon as I use a set operator like UNION or INTERSECT together with this query I will get a Duplicate column name 'id' error. Example:

select t_author.id, t_book.id
from   t_author
join   t_book on t_author.id = t_book.author_id
union
select t_author.id, t_book.id
from   t_author
join   t_book on t_author.id = t_book.author_id

The same applies to expressions where a column name must be “derived”:

select 1, 1
from   dual
union
select 2, 2
from   dual

Also in the previous example both queries work fine when executed on their own, but not when combined using a set operator like UNION.

Would you consider this a bug or is this a known limitation? When writing complex SQL I find this rather limiting and I am not aware of any other RDBMS which has this limitation (and I have tested this query against quite a few others, including MySQL 5.x and 8.x).

2

This is an unfortunate known limitation due to how UNION is implemented in MemSQL today. It is a limitation we are planning on removing. However, I’m not aware of a similar limitation with INTERSECT and could not reproduce it locally. Do you have an example INTERSECT query which hits this?

3

Good to hear that you already have plans to fix this.

In both provided examples I can substitute UNION with EXCEPT or INTERSECT (and of also UNION ALL and MINUS) and I get the same error.