as.integer64 compability for SQL SERVER

[tidyverse]

as.integer64 compability for SQL SERVER

@batpigandme commented on Jan 16, 2018, 11:16 PM UTC:

See #1092

@jnolis commented on Jan 16, 2018, 11:22 PM UTC:

Thanks for the link. I'm not sure I fully understand though, if dplyr doesn't support 64 bit integers, why does extracting an existing SQL Server table with bigints in it create tables with integer64 columns? It is just partially supported somehow?

Here is an example where it creates an integer64:

1. In SQL Server:

CREATE TABLE Example2 (Number bigint)
INSERT INTO Example2 SELECT 3

2. In R:

tbl(con,"Example2")

3. Results in:

# Source: table<Example2> [?? x 1]
# Database: Microsoft SQL Server 12.00.1000[jadler@lenatianalytics/MecMember]
  Number         
  <S3: integer64>
1 3         

Sorry if this is coming across as pedantic! I'm just trying to understand since I have some tables with bigints and once I bring them into R joining is causing problems (since some are ints and some are int64s). Thanks!

@batpigandme commented on Jan 16, 2018, 11:36 PM UTC:

I'm not totally sure (someone more knowledgeable will know, cc @edgararuiz), but based on the code I've linked to below, it looks like integer64 is considered a "known class" when collected. But as.integer64() isn't a function in base or dplyr (the function from bit64 is used in tests.

dplyr/tests/testthat/helper-combine.R

Line 180 in b3837f5

| | special_non_vector_classes <- c( |

@edgararuiz commented on Jan 17, 2018, 2:53 AM UTC:

Ok, I think the right answer is to get as.integer64() to translate to sql_cast("BIGINT"). I need to check on the other DB translations to see how they should handle it.

@batpigandme - Can we mark this as database feature please?

Thanks all!

@hadley commented on May 20, 2018, 2:35 PM UTC:

@edgararuiz do you want to have a go at this one too?

@edgararuiz commented on May 22, 2018, 11:00 PM UTC:

Yes, I'll take it