union should explicitly specify table name in casts

[wearpants]

Bigquery gets confused by tables that have a column with the same name in CASTs, and will try to cast the entire table instead of the column.

-- causes error, as foo is a struct
select cast(`foo` as STRING) as `foo` 
from `data-playground-1234`.`myschema`.`foo`

-- works when table name is specified
select cast(`foo`.`foo` as STRING) as `foo` 
from `data-playground-1234`.`myschema`.`foo`

I think this is a simple as adding table.name here, like:

{%- set col_name = adapter.quote(table.name) + '.' + adapter.quote(col_name) if col_name in table_columns[table] else 'null' %}

[clausherther]

I would think that's a general problem with BigQuery and would have to be addressed everywhere you cast a column, not just in this macro? The other obvious "fix" is to not have columns with the same name as a table?

[wearpants]

In my real world, it's a pre-existing table with a single column (plus id) so changing it will be difficult I'm not sure I understand why needing to change it elsewhere is an argument against changing it here?

…

On Wed, Nov 6, 2019, 10:39 AM Claus Herther ***@***.***> wrote: I would think that's a general problem with BigQuery and would have to be addressed everywhere you cast a column, not just in this macro? The other obvious "fix" is to not have columns with the same name as a table? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#173>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAD3MAANSQZSMQQWOXXUNQLQSLQMPANCNFSM4JJNCLAQ> .

[wearpants]

I'll try to send a PR in the next day or two if you'd be willing to accept it

…

On Wed, Nov 6, 2019, 1:40 PM Pete Fein ***@***.***> wrote: In my real world, it's a pre-existing table with a single column (plus id) so changing it will be difficult I'm not sure I understand why needing to change it elsewhere is an argument against changing it here? On Wed, Nov 6, 2019, 10:39 AM Claus Herther ***@***.***> wrote: > I would think that's a general problem with BigQuery and would have to be > addressed everywhere you cast a column, not just in this macro? The other > obvious "fix" is to not have columns with the same name as a table? > > — > You are receiving this because you authored the thread. > Reply to this email directly, view it on GitHub > <#173>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/AAD3MAANSQZSMQQWOXXUNQLQSLQMPANCNFSM4JJNCLAQ> > . >

[drewbanin]

I said as much in the linked PR, but to follow up on @clausherther's comment above: a good approach to address this issue on BQ might be to alias the table name instead of changing the source table/column names. I do wish that BigQuery was a little bit smarter here.... alas.

So, this works:

with data as (
  select 1 as data
)

select cast(data.data as string) from data;

and this also works:

with data as (
  select 1 as data
)

select cast(data as string) from data as data______dbt_generated______;

but this does not:

with data as (
  select 1 as data
)

select cast(data as string) from data;
--> Invalid cast from STRUCT<data INT64> to STRING at [7:13]

All things considered, I think i like the table alias more that the column qualification. It can be really challenging to get quoting/capitalization exactly right on different databases in different contexts. I think it's straightforward here (nice work on that fix @wearpants) but it other contexts, it may be preferable to alias tables instead.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

[adrian3ka]

Hi @drewbanin, I also face this issue in my development with BigQuery, please help me to review the changes if it's make sense to you.
Thanks!

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

[dbeatty10]

The same underlying behavior in BigQuery might be the underlying root cause of all of the following reports:

• union should explicitly specify table name in casts #173
• unique schema test will fail on BigQuery if tested on column with name identical to model dbt-core#2061
• [CT-396] [Bug] Generic tests aren't valid in BigQuery when the struct and table name are the same  dbt-core#4824
• [Bug] Test for uniqueness passes when duplicates are present in BigQuery column dbt-core#11067