SQL Editor: duplicate column does not show up in rows API response

[ericpgreen2]

The following query works in the DuckDB console:

The same query (minus the limit) does not return data for the duplicate id column (id_1) in Rill Developer's Preview Table:

Here's the rows payload:

Rill Developer's table profile information shows that the column does exist and that it has the same data as the original id column.

[egor-ryashin]

sqlx MapScan cannot process duplicate names

[egor-ryashin]

@begelundmuller I think it needs a design decision, for example, we hardly can make duplicate columns take names like id_1,id_2, ... on Runtime side, as request for them to DuckDB will fail.

[jaroet]

I have a somewhat similar issue that is probably related to this although not about the API.

I created a source where I join 2 tables using a key with the same name.The select * from does not work as Rill complains about a column already existing. The query is working in every other SQL tool I have installed (DBeaver, DBgate, TablePlus and more) showing the name twice (tablePlus) or adding an identifier like fieldname_2 . Only Rill is complaining about the duplicate field and not executing the query.

I can write out (generate) the complete set of fields (25 in total in my case) but it seems counterintuitive to do so. Specially as we use Rill developer for ad-hoc data exploration. So most of the time a simple select * with a few joins is used to quickly analyse the data.

[egor-ryashin]

@jaroet could you post a sample SQL here?

[egor-ryashin]

If we make it possible to render a model with duplicate names it's only a starter for additional usability problems, for example, having a model with columns A,B,A how one should refer to a second A in another model or in a dashboard?

[jaroet]

The sql that does not work is the following. The joinkey is available in both tables. In regular SQL editors it works and the second joinkey is shown as joinkey2. If aliases are used (like in my example) I personally would prefer an automatic name like d_joinkey. At least no duplicate names should be generated.

select 
    *
from export.logging l
join export.logging_detail d on l.joinkey = d.joinkey

I know I can create 2 source files and join in the model step but then there is a possibility that both tables have minor differences as they are not updated on the exact same time (not very probably but still).