Dates with integer representation cannot be copied to SQL Server

[RasmusSkytte]

In R 4.5, seq.Date() was changed to no longer coerce the internal representation to floating point and instead keeps the dates as integer representation (https://cran.r-project.org/bin/windows/base/old/4.5.0/NEWS.R-4.5.0.html)

Since I use seq.Date() in my own work over at ssi-dk/diseasystore, I started seeing failures in our test workflow that uses SQL Server with errors like:

Error in `db_copy_to(dest$con, name, df, overwrite = overwrite, types = types, 
    temporary = temporary, unique_indexes = unique_indexes, indexes = indexes, 
    analyze = analyze, in_transaction = in_transaction, ...)`: Can't copy data to table "#ds_all_dates_tkhMQuqMpA".
Caused by error in `dplyr::db_write_table()`:
! Can't write table table "#ds_all_dates_tkhMQuqMpA".
Caused by error:
! nanodbc/nanodbc.cpp:2077: 00000
[Microsoft][ODBC Driver 18 for SQL Server]Restricted data type attribute violation

After some digging, I tracked the issue back to the internal representation of dates since R 4.5 and I could fix the issue in my own code base my casting to numeric
as.Date(as.numeric(..))

Since I assume I am not the only person that will encounter this issue, I have been tinkering with a fix here to help others.

I have a proposed solution in PR #953 that converts Integer Dates to numeric Dates in sqlData()

Let me know if you think this is the right place to tackle this problem.

[detule]

Hi - thanks for the report and all the leg work to track this down.

If possible, can you jot down a minimal example showing the issue, preferably only using package:odbc function calls.

Thank you!

[RasmusSkytte]

Here is a minimal example showing the issue:

# Connect to SQL Server
conn <- DBI::dbConnect(
  drv = odbc::odbc(),
  driver = "ODBC Driver 18 for SQL Server",
  server = "localhost",
  database = "master",
  UID = "******",
  PWD = "******",
  TrustServerCertificate = "yes"
)

# Dates created directly with as.Date() normally works well
data <- data.frame("dates" = c(as.Date("2020-01-01"), as.Date("2020-01-02")))

# Internal representation is REALSXP
.Internal(inspect(data))

# Works with no error
DBI::dbWriteTable(conn, "#realsxp_test", data)

# Check table was created
DBI::dbExistsTable(conn, "#realsxp_test")
print(dplyr::tbl(conn, DBI::Id("#realsxp_test")))

# Dates created directly with seq.Date() fails
data <- data.frame(
    "dates" = seq.Date(from = as.Date("2020-01-01"), to = as.Date("2020-01-02"), by = "1 day")
)

# Internal representation is INTSXP
.Internal(inspect(data))

# Fails with error
try(DBI::dbWriteTable(conn, "#intsxp_test", data))

# Check if table was created
DBI::dbExistsTable(conn, "#intsxp_test")
print(dplyr::tbl(conn, DBI::Id("#intsxp_test")))

# Disconnect
DBI::dbDisconnect(conn)

Output

@558774acf490 19 VECSXP g0c1 [OBJ,REF(2),ATT] (len=1, tl=0)
  @558774a93948 14 REALSXP g0c2 [OBJ,REF(6),ATT] (len=2, tl=0) 18262,18263
  ATTRIB:
    @558774aa57d0 02 LISTSXP g0c0 [REF(1)] 
      TAG: @558770add6b0 01 SYMSXP g1c0 [MARK,REF(41741),LCK,gp=0x4000] "class" (has value)
      @558774a9bef8 16 STRSXP g0c1 [REF(65535)] (len=1, tl=0)
	@558770c82000 09 CHARSXP g1c1 [MARK,REF(371),gp=0x61] [ASCII] [cached] "Date"
ATTRIB:
  @558774ad1c80 02 LISTSXP g0c0 [REF(1)] 
    TAG: @558770add100 01 SYMSXP g1c0 [MARK,REF(65535),LCK,gp=0x4000] "names" (has value)
    @558774acf298 16 STRSXP g0c1 [REF(2)] (len=1, tl=0)
      @558774a3eae0 09 CHARSXP g0c1 [REF(12),gp=0x61] [ASCII] [cached] "dates"
    TAG: @558770add6b0 01 SYMSXP g1c0 [MARK,REF(41741),LCK,gp=0x4000] "class" (has value)
    @558774a3cff8 16 STRSXP g0c1 [REF(65535)] (len=1, tl=0)
      @558770b7d058 09 CHARSXP g1c2 [MARK,REF(608),gp=0x61,ATT] [ASCII] [cached] "data.frame"
    TAG: @558770adced0 01 SYMSXP g1c0 [MARK,REF(65535),LCK,gp=0x4000] "row.names" (has value)
    @558774acea80 13 INTSXP g0c1 [REF(1)] (len=2, tl=0) -2147483648,-2

[1] TRUE

# Source:   table<"#realsxp_test"> [?? x 1]
# Database: Microsoft SQL Server 16.00.4215[dbo@bc8002a288a8/master]
  dates     
  <date>    
1 2020-01-01
2 2020-01-02

@5587757c8ba8 19 VECSXP g0c1 [OBJ,REF(2),ATT] (len=1, tl=0)
  @5587757c9eb0 13 INTSXP g0c1 [OBJ,REF(6),ATT] (len=2, tl=0) 18262,18263
  ATTRIB:
    @558776a1a988 02 LISTSXP g0c0 [REF(1)] 
      TAG: @558770add6b0 01 SYMSXP g1c0 [MARK,REF(65535),LCK,gp=0x4000] "class" (has value)
      @558774a9bef8 16 STRSXP g0c1 [MARK,REF(65535)] (len=1, tl=0)
	@558770c82000 09 CHARSXP g1c1 [MARK,REF(449),gp=0x61] [ASCII] [cached] "Date"
ATTRIB:
  @558776a242c0 02 LISTSXP g0c0 [REF(1)] 
    TAG: @558770add100 01 SYMSXP g1c0 [MARK,REF(65535),LCK,gp=0x6000] "names" (has value)
    @5587757c89b0 16 STRSXP g0c1 [REF(2)] (len=1, tl=0)
      @558774a3eae0 09 CHARSXP g0c1 [MARK,REF(56),gp=0x61] [ASCII] [cached] "dates"
    TAG: @558770add6b0 01 SYMSXP g1c0 [MARK,REF(65535),LCK,gp=0x4000] "class" (has value)
    @558774a3cff8 16 STRSXP g0c1 [MARK,REF(65535)] (len=1, tl=0)
      @558770b7d058 09 CHARSXP g1c2 [MARK,REF(1597),gp=0x61,ATT] [ASCII] [cached] "data.frame"
    TAG: @558770adced0 01 SYMSXP g1c0 [MARK,REF(65535),LCK,gp=0x4000] "row.names" (has value)
    @558773e5bfa8 13 INTSXP g0c1 [REF(1)] (len=2, tl=0) -2147483648,-2

Error : nanodbc/nanodbc.cpp:2077: 00000
[Microsoft][ODBC Driver 18 for SQL Server]Restricted data type attribute violation 

[1] TRUE

# Source:   table<"#intsxp_test"> [?? x 1]
# Database: Microsoft SQL Server 16.00.4215[dbo@bc8002a288a8/master]
# ℹ 1 variable: dates <date>

I don't have a local SQL Server to use for development so I rely on Github Actions.
I have pasted a test workflow below that I used to generate the example.

Github Workflow

on:
  push:
    inputs:
      cache_version:
        type: string
        default: '1'

jobs:
  mssql:
    name: "🧪 Tests: SQL Server 2022"
    runs-on: ubuntu-latest

    steps:
      - name: Install a SQL Server suite of tools
        uses: potatoqualitee/mssqlsuite@v1.8
        with:
          install: sqlengine, sqlpackage, sqlclient
          version: 2022
          show-log: true

      - name: Checkout repo
        uses: actions/checkout@v4
        with:
          fetch-depth: 0
          persist-credentials: false

      - name: Setup R
        uses: r-lib/actions/setup-r@v2
        with:
          r-version: 'release'
          use-public-rspm: true

      - name: Install R dependencies
        uses: r-lib/actions/setup-r-dependencies@v2
        with:
          cache-version: ${{ inputs.cache_version }}
          extra-packages: |
            local::.
            any::pak
            any::jsonlite
            any::rcmdcheck
            any::devtools
            any::lintr
            any::covr
            any::roxygen2
            any::pkgdown
            any::rmarkdown
            any::styler
          needs: build, check, coverage, roxygen2, lint, website

      - name: Test INTSXP issue
        run: |
            # Connect to SQL Server
            conn <- DBI::dbConnect(
                drv = odbc::odbc(),
                driver = "ODBC Driver 18 for SQL Server",
                server = "localhost",
                database = "master",
                UID = "SA",
                PWD = "dbatools.I0",
                TrustServerCertificate = "yes"
            )

            # Dates created directly with as.Date() normally works well
            data <- data.frame("dates" = c(as.Date("2020-01-01"), as.Date("2020-01-02")))

            # Internal representation is REALSXP
            .Internal(inspect(data))

            # Works with no error
            DBI::dbWriteTable(conn, "#realsxp_test", data)

            # Check table was created
            DBI::dbExistsTable(conn, "#realsxp_test")
            print(dplyr::tbl(conn, DBI::Id("#realsxp_test")))

            # Dates created directly with seq.Date() fails
            data <- data.frame(
              "dates" = seq.Date(from = as.Date("2020-01-01"), to = as.Date("2020-01-02"), by = "1 day")
            )

            # Internal representation is INTSXP
            .Internal(inspect(data))

            # Fails with error
            try(DBI::dbWriteTable(conn, "#intsxp_test", data))

            # Check if table was created
            DBI::dbExistsTable(conn, "#intsxp_test")
            print(dplyr::tbl(conn, DBI::Id("#intsxp_test")))

            # Disconnect
            DBI::dbDisconnect(conn)

        shell: Rscript {0}

[RasmusSkytte]

I should note that our integration tests runs on

• PostgreSQL
• duckdb
• sqlite
• SQL Server

And it was only SQL Server that failed after R 4.5.

[detule]

Thank you! I can replicate the issue as well.

I think your #953 is great. In #954 I tried to do something similar but without needing to do loop over the columns of the input data. Shares some of the implementation as your PR so I made you a co-author.

If you have a chance, do you mind giving it a try?

[RasmusSkytte]

Thanks for looking into it.

I can confirm that if I merge #954 into my test pipeline, the issue is resolved.