merge.data.table using character column crashing Rstudio with tables above 65536 rows

[Stikus]

Hello, our production code become broken after we one of our dataframes become bigger than 65536 rows.
For about a year, we have a code that merge small data.frame to big data.table with merge. Due to merge.data.table syntax, first argument of merge decided merge manner, and it was data.table, resulting merge.data.table. I understand that it is not correct to mix types, but it was our oversight.

Last week, our "small" data.frame got bigger and Rstudio start to crash not only when merging with big data.table, but even with small ones. We switched to merge.data.frame and all start working again, but in takes 12+ hours for our code to run instead 1+ hour - that's why I'm asking for help.

After some debug I've found that before 65536 rows in data.frame all is good, and after it is not. We tested this example on several machines, and two of our main machines have error, but some others don't. I've posted session infos from broken and working machine FYI.

I've created an example that shows this behavior. As you can see - merge.data.frame working good (a_test and b_test are data.frames), merge.data.table with numeric column working good (a1_test and b1_test are data.tables), merge.data.table with character column fails.

# Minimal reproducible example

library(data.table)

a_test <- setNames(as.data.frame(cbind(1:10, 1:10, 11:20)), c('A', 'A_char',  'B'))
a_test$A_char <- as.character(a_test$A_char)
a1_test <- as.data.table(a_test)
b_test <- setNames(as.data.frame(cbind(1:65600, 1:65600, 21:65620)), c('A', 'A_char', 'C'))
b_test$A_char <- as.character(b_test$A_char)
b1_test <- as.data.table(b_test)
c_test <- merge(a_test, b_test, by = 'A')
c1_test <- merge(a1_test, b1_test, by = 'A')
c1_char_test <- merge(a1_test, b1_test, by = 'A_char')

# Output of sessionInfo()

> sessionInfo()
R version 4.2.2 Patched (2022-11-10 r83330)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 18.04.6 LTS

Matrix products: default
BLAS:   /usr/lib/x86_64-linux-gnu/blas/libblas.so.3.7.1
LAPACK: /usr/lib/x86_64-linux-gnu/lapack/liblapack.so.3.7.1

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
 [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
 [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] data.table_1.14.6

loaded via a namespace (and not attached):
[1] compiler_4.2.2 tools_4.2.2 

# Output of sessionInfo() from machine where all is ok

> sessionInfo()
R version 4.2.1 (2022-06-23)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 18.04.6 LTS

Matrix products: default
BLAS:   /usr/lib/x86_64-linux-gnu/openblas/libblas.so.3
LAPACK: /usr/lib/x86_64-linux-gnu/libopenblasp-r0.2.20.so

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8     LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
 [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                  LC_ADDRESS=C               LC_TELEPHONE=C             LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] data.table_1.14.6

loaded via a namespace (and not attached):
[1] compiler_4.2.1 tools_4.2.1   

I've found somewhat similar issue #4733, that's why I've linked it.

I can produce any other debug information if you tell me how. Looking for help - we cannot afford 12+ hours of analysis in production.

[jangorecki]

Use [ to merge, it will be more efficient. Ensure there are no duplicates in columns you are merging on. The symptoms you describe sounds like a duplicate value sneaked into a column you merge on and that results in many-to-many join.

[Stikus]

@jangorecki As you can see in example this is ranges of numbers - no duplicates can sneak in. And if I use 65500 instead of 65600 in code all working:

[ben-schwen]

Does the problem only appear in Rstudio or also in a normal terminal? Seems like a representation error due to 2^16 = 65536 space.

I can't reproduce it on R 4.2.2 with Ubuntu 20.04.3 LTS

[Stikus]

Thanks for advice, already tested:

> library(data.table)
data.table 1.14.6 using 96 threads (see ?getDTthreads).  Latest news: r-datatable.com
>
> a_test <- setNames(as.data.frame(cbind(1:10, 1:10, 11:20)), c('A', 'A_char',  'B'))
> a_test$A_char <- as.character(a_test$A_char)
> a1_test <- as.data.table(a_test)
> b_test <- setNames(as.data.frame(cbind(1:65600, 1:65600, 21:65620)), c('A', 'A_char', 'C'))
> b_test$A_char <- as.character(b_test$A_char)
> b1_test <- as.data.table(b_test)
> c_test <- merge(a_test, b_test, by = 'A')
> c1_test <- merge(a1_test, b1_test, by = 'A')
> c1_char_test <- merge(a1_test, b1_test, by = 'A_char')

 *** caught segfault ***

 *** caught segfault ***
address 0x55cb019817c0, cause 'memory not mapped'

 *** caught segfault ***
address 0x55cb01bc1384, cause 'memory not mapped'

 *** caught segfault ***
address 0x55cb01881828, cause 'memory not mapped'

 *** caught segfault ***
address 0x55cb019013d8, cause 'memory not mapped'
address 0x55cb018c1674, cause 'memory not mapped'

 *** caught segfault ***
address 0x55cb019c13fc, cause 'memory not mapped'

 *** caught segfault ***
address 0x55cb01b81440, cause 'memory not mapped'
Error: no more error handlers available (recursive errors?); invoking 'abort' restart

 *** caught segfault ***
> address 0x55cb01781540, cause 'memory not mapped'

Possible actions:
1: abort (with core dump, if enabled)
2: normal R exit
3: exit R without saving workspace
4: exit R saving workspace

 *** caught segfault ***
address 0x55cb01d414f4, cause 'memory not mapped'

Possible actions:
1: abort (with core dump, if enabled)
2: normal R exit
3: exit R without saving workspace
4: exit R saving workspace

 *** caught segfault ***
Selection:
 *** caught segfault ***
Selection:
 *** caught segfault ***
address 0x55cb01d015d0, cause 'memory not mapped'

Possible actions:
1: abort (with core dump, if enabled)
2: normal R exit
3: exit R without saving workspace
4: exit R saving workspace
Error: attempt to set index 1/1 in SET_STRING_ELT

 *** caught segfault ***
Selection:
 *** caught segfault ***
address 0x55cb01a414dc, cause 'memory not mapped'

Possible actions:
1: abort (with core dump, if enabled)
2: normal R exit
3: exit R without saving workspace
4: exit R saving workspace

 *** caught segfault ***
address 0x55cb01a819e0, cause 'memory not mapped'
Selection:
 *** caught segfault ***
address 0x55cb01ac195c, cause 'memory not mapped'

Possible actions:
1: abort (with core dump, if enabled)
2: normal R exit
3: exit R without saving workspace
4: exit R saving workspace

 *** caught segfault ***
address 0x55cb01b01480, cause 'memory not mapped'

Possible actions:
1: abort (with core dump, if enabled)
2: normal R exit
3: exit R without saving workspace
4: exit R saving workspace
Selection: Error: no more error handlers available (recursive errors?); invoking 'abort' restart

 *** caught segfault ***
 [Kaddress 0x55cb01681528, cause 'memory not mapped'

Possible actions:
1: abort (with core dump, if enabled)
2: normal R exit
3: exit R without saving workspace
4: exit R saving workspace

 *** caught segfault ***
address 0x55cb016c1a44, cause 'memory not mapped'

Possible actions:
1: abort (with core dump, if enabled)
2: normal R exit
3: exit R without saving workspace
4: exit R saving workspace
Selection: address 0x55cb01d814f8, cause 'memory not mapped'

Possible actions:
1: abort (with core dump, if enabled)
2: normal R exit
3: exit R without saving workspace
4: exit R saving workspace

 *** caught segfault ***
address 0x55cb01741784, cause 'memory not mapped'
address 0x55cb01cc149c, cause 'memory not mapped'
Segmentation fault (core dumped)

[ben-schwen]

@Stikus Could you try to reduce the number of used cores? E.g. halving them?

setDTthreads(threads = 48). Then it might be related #5087. Does merge call sort internally?

[Stikus]

48 threads does fix problem in Rstudio and R console too:

library(data.table)
setDTthreads(threads = 48)

a_test <- setNames(as.data.frame(cbind(1:10, 1:10, 11:20)), c('A', 'A_char',  'B'))
a_test$A_char <- as.character(a_test$A_char)
a1_test <- as.data.table(a_test)
b_test <- setNames(as.data.frame(cbind(1:65600, 1:65600, 21:65620)), c('A', 'A_char', 'C'))
b_test$A_char <- as.character(b_test$A_char)
b1_test <- as.data.table(b_test)
c_test <- merge(a_test, b_test, by = 'A')
c1_test <- merge(a1_test, b1_test, by = 'A')
c1_char_test <- merge(a1_test, b1_test, by = 'A_char')

This example works. Thanks for help, I'll test on real data tomorrow. Should I limit it always to 48 or how can I count correct number?

Does #5087 will be part of 14.7 release (assuming that due to milestone)?

[jangorecki]

PR to fix this issue seems to be already merged so could be checked on master branch. Hard to say which version it will be.

[jangorecki]

merge doesn't call sort but it does call forder

[Stikus]

I've tested on real data and all working now, big thanks for all.
If you think that issue should be closed - feel free to do it.

[jangorecki]

If you can reproduce segfault on any data, unreal or small, using master branch, then there is still something for us to work on.

[ben-schwen]

Could you try to update to dev version version using update.dev.pkg() (or update_dev_pkg() depending on which data.table version you have installed locally) and try if the issue is fixed while using all cores?

[Stikus]

I'm trying to update, but there is certificate problem:

> data.table::update_dev_pkg()
Error in read.dcf(idx, fields = c("Package", field)) : 
  cannot open the connection to 'https://Rdatatable.gitlab.io/data.table/src/contrib/PACKAGES'
In addition: Warning message:
In read.dcf(idx, fields = c("Package", field)) :
  URL 'https://Rdatatable.gitlab.io/data.table/src/contrib/PACKAGES': status was 'Peer certificate cannot be authenticated with given CA certificates'

Looking for solution.

[ben-schwen]

In this case, you might want to try to install from github e.g. with remotes via remotes::install_github("https://github.com/Rdatatable/data.table") instead

[Stikus]

> library(data.table)
> setDTthreads(threads = 72)
> 
> a_test <- setNames(as.data.frame(cbind(1:10, 1:10, 11:20)), c('A', 'A_char',  'B'))
> a_test$A_char <- as.character(a_test$A_char)
> a1_test <- as.data.table(a_test)
> b_test <- setNames(as.data.frame(cbind(1:65600, 1:65600, 21:65620)), c('A', 'A_char', 'C'))
> b_test$A_char <- as.character(b_test$A_char)
> b1_test <- as.data.table(b_test)
> c_test <- merge(a_test, b_test, by = 'A')
> c1_test <- merge(a1_test, b1_test, by = 'A')
> c1_char_test <- merge(a1_test, b1_test, by = 'A_char')
> library(data.table)
> setDTthreads(threads = 96)
> 
> a_test <- setNames(as.data.frame(cbind(1:10, 1:10, 11:20)), c('A', 'A_char',  'B'))
> a_test$A_char <- as.character(a_test$A_char)
> a1_test <- as.data.table(a_test)
> b_test <- setNames(as.data.frame(cbind(1:65600, 1:65600, 21:65620)), c('A', 'A_char', 'C'))
> b_test$A_char <- as.character(b_test$A_char)
> b1_test <- as.data.table(b_test)
> c_test <- merge(a_test, b_test, by = 'A')
> c1_test <- merge(a1_test, b1_test, by = 'A')
> c1_char_test <- merge(a1_test, b1_test, by = 'A_char')

Thanks, our ISP blocked gitlab.io for some reason. All working on GitHub version - I assume we can close issue now?