[Sqllab] Error: no such column When there are same fields in two tables with a join

[Narcissus7]

Make sure these boxes are checked before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if any
• I have reproduced the issue with at least the latest released version of superset
• I have checked the issue tracker for the same issue and I haven't found one similar

Superset version

master

Actual results

but in Explore, can't get the field id__1

Steps to reproduce

Select * [choose two tables with same fields]
Run query
Explore

[mistercrunch]

Which db engine is this? Looks like databases and drivers do different things to manage duplicated column names.

[Narcissus7]

Above db engine is SQLite. I have just tested MySQL.

What can i do to avoid this error?

[mistercrunch]

The workaround is to alias your fields uniquely as in:

SELECT a.id as foo_id, b.id as bar_id

[Narcissus7]

Thanks.
But there may be many fields. For instance:(tables and table_columns are original tables of superset)
SELECT * FROM tables a LEFT JOIN table_columns b ON a.id=b.table_id
I still want to find a way to use SELECT *

[villebro]

@Narcissus7 As Superset wraps the SQL Lab query into a subquery, I don't think there is any way for Superset to select the deduplicated column without explicit aliases as @mistercrunch suggested. One approach would be to somehow reconstruct the the SELECT *... into explicit SELECT a.col as col, b.col as col__1..., but I don't think that's really feasible. I think it might be better to raise an Exception in SQL Lab if there are duplicates.

[Narcissus7]

@villebro I see. Thanks a lot

[mistercrunch]

Here's what the query in the explore view looks like

SELECT id AS id,
       id__1 AS id__1
FROM
  (SELECT a.id,
          b.id
   FROM ab_view_menu a
   LEFT JOIN ab_permission_view b ON a.id = b.view_menu_id) AS expr_qry
LIMIT 1000

[mistercrunch]

We could try to detect the double underscore __ when users click the Explore button and tell people to alias their columns properly.

[mistercrunch]

#5778