`copy_to` creates tables with invalid column names

[jensmassberg]

I use dbplyr with a database where some column names are invalid.
If a table with invalid column name is copied to the data base, invalid
names are changed to valid ones. Thus when using copy_to, the table in the
database might have column names than are different from the original data frame.

This was no problem in dbplyr 2.1.1, but in dbplyr 2.2.1 the returned tbl object
contains the old (invalid) column names instead of the new correct ones. This leads
to bugs when working with the table.

A closer look into the code showed that the following line led to the problem:
https://github.com/tidyverse/dbplyr/blame/main/R/verb-copy-to.R#L86

Before the line was changes, the column names were read from the table. Now it is
assumed that they are the same as the ones in the data frame. This assumption isn't
true in my case and I think that there are also other data bases where this isn't
necessarily true (random example: https://www.techtalkcorner.com/parquet-files-column-names-with-spaces/).

Can we either

• get back to the old behaviour in copy_to to read the column names from the table or
• add a parameter to switch the behavior (use column names from data frame or read them from table)?

[mgirlich]

It feels quite unexpected to me that the database changes the column names. So, I'm not a big fan of the old behaviour. At least, this should be a warning. So it also does not make sense to me to add such a parameter. Instead you should rather rename the columns before copying.
But the current behaviour is not that good either. Though, it feels like quite an edge case.

@hadley What's your opinion on that?

[hadley]

Hmmmmmm, given that some databases do this, and we can't really protect it, it seems like it would be safest to ask the database for the column names, and then maybe warn the user if they're changed from what they supplied?

[krlmlr]

Can we:

• add an argument query_column_names = NA
• if NA, column names are only queried if they don't match [a-z_]+
• otherwise, we respect the user's preference

[mgirlich]

This seems reasonable to me. It avoids the roundtrip in most cases and should still be safe.

[hadley]

This seems like a lot of work for a situation that seems to occur rarely. @jensmassberg can you give us more information about which database you're using and how the names are transformed?

[mgirlich]

Closing as we are missing more details here. Happy to reopen if we get more information or this comes up more frequently.

[MichaelChirico]

Taking up the issue as Jens is no longer working on this.

Yes, there's a backend issue where our copy_to() method emits a file with a "normalized" name, onto which the specified table is defined.

We correct the issue in our own copy_to() method by double-checking (*) names(collect(new_tbl, n=0)) after set-up, i.e. exactly Hadley's suggestion here:

Hmmmmmm, given that some databases do this, and we can't really protect it, it seems like it would be safest to ask the database for the column names, and then maybe warn the user if they're changed from what they supplied?

Since it seems rare enough / hasn't affected any other backends yet, I think our current workaround by double-checking things didn't get mangled is good, not sure there's anything for {dbplyr} to do centrally, except perhaps offer an "official" way to overwrite the dimnames of a tbl_lazy, for now I am hacking the object which feels fragile:

new_tbl$lazy_query$vars <- new_colnames

Whereas if I could do this with

# equiv., colnames<-
dimnames(tbl) <- new_colnames

I'd feel slightly happier.

* Actually, I just found a way to avoid this "expensive" step, but it does work on generic backends.