SNOW-174004: No way to get row results containing duplicate column names

[BenBirt]

trafficstars

As far as I can tell there is currently no way of getting result sets which contain duplicated column names.

Consider the following query:

select
  *
from (select 'a' as key, 1 as foo) as table1
join (select 'a' as key, 2 as foo) as table2
on table1.key = table2.key

When run in the Snowflake console, it returns 4 columns (KEY, FOO, KEY, FOO`). However, when run using snowflake-sdk, it's impossible to fetch this data because the SDK returns row objects whose keys are set to column names (this making duplicate columns impossible to fetch, or even to know whether there were duplicate column names returned at all).

FWIW, a nice example of a client library that does support this is the pg library for Node. It lets the user configure the 'row mode', which can be used to return row values in an array rather than an object. https://node-postgres.com/features/queries

[RStuczynski]

I'm experiencing this as well, where a query selecting the same column twice is only showing the second selection. Is this something that can be resolved?

[tom-james-watson]

We're also hitting this. I don't see any way of solving this other than adding a pg-style rowMode kind of option.

[tom-james-watson]

Our team is open to trying to provide a PR for this functionality. Before we attempt that, it would be good to know:

a) If anyone is already working on this? I see @sfc-gh-kterada self-assigned this. b) If a submitted patch has any chance of getting accepted and merged. @SimbaGithub maybe?

[MasterOdin]

We're running into this same issue, and it looks like a PR was made that would perhaps fix this issue, but no response on maintainers on it. Is there anything we, the community, can help see this functionality merged and released, to avoid having to make our own fork of the repo with just that PR applied.

[sfc-gh-dszmolka]

hi everyone and as first order of business, i'd like to make a massive apology for us leaving this issue unattended for such a long time. this won't happen going forward. second, this might not be even an enhancement but a bug - checking with other Snowflake drivers (ODBC) , the current GUI, and CLI applications (SnowSQL , built on Python Connector); all of them seems to be able to preserve the information of having 2x KEY and 2x FOO columns by default; contrary to snowflake-sdk.

we'll take a look and i'll keep this issue updated on the progress. finally, i'd like to thank you all for your contribution so far, will be definitely helpful !

[sfc-gh-dszmolka]

#591

[sfc-gh-dszmolka]

the PR is merged and will be part of the next release, expected towards end of August.

since it brings a new feature and new options, perhaps a little explanation on the usage would be helpful. We'll also officially document it in the Snowflake docs, but it's a bit longer process so putting it here as a comment.

---

As an example, consider the following query:

select *
from (select 'a' as key, 1 as foo, 3 as name) as table1
join (select 'a' as key, 2 as foo, 3 as name2) as table2 on table1.key = table2.key
join (select 'a' as key, 3 as foo) as table3 on table1.key = table3.key

The output will be different, depending on how the newly introduced rowMode option is set.

1. not set, or set to object (default behaviour, and behaviour with and prior to version 1.7.0)

object = {KEY: 'a', FOO: 3, NAME: 3, NAME2: 3};

2. set to array

array = ['a', 1, 3, 'a', 2, 3, 'a', 3];

3. set to object_with_renamed_duplicated_columns to keep the default object mapping, but suffix the column names to make them unique (similar behaviour to Snowsight GUI)

objectWithRenamedDuplicatedColumns = {KEY: 'a', FOO: 1, NAME: 3, KEY_2: 'a', FOO_2: 2, NAME2: 3, KEY_3: 'a', FOO_3: 3};

This newly introduced rowMode option can be set on two levels:

• on connection configuration level:

snowflake.createConnection({
  account: account,
  username: username,
  ...
  rowMode: 'array'})

• on statement configuration level

connection.execute({ 
  sqlText: sql, 
  rowMode: 'array',
  ...
  )}

Note: If you configure in both possible places, the statement level value will be used.

Valid values for rowMode configuration are:

• object (also the default option when rowMode is not set)
• array
• object_with_renamed_duplicated_columns

[sfc-gh-dszmolka]

fix released with version 1.8.0