Rdatatable / Rdatatable/data.table

merge inconsistent with merge.data.frame according to precision of numerical column

Open
#4,591 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

consistency
Dominant language
R
Stars
3.9k
Forks
1.1k
Avg merge
14h 4m
Merged PRs (30d)
4

Description

merge.data.table() and merge.data.frame() behave differently when the merge happens on numerical columns that differ on the last 1-2 decimal places:

zscore1 <- c(15.8068722584374992, -3.6299861879091400, 3.1547699616527298)
zscore2 <- c(15.8068722584374690, -3.6299861879091355, 3.1547699616527285)
dt1 <- data.table(id=10000012, allele="G", zscore=zscore1)
dt2 <- data.table(id=10000012, allele="G", zscore=zscore2, chrom=4)
merge(dt1, dt2)
# Empty data.table (0 rows and 4 cols): id,allele,zscore,chrom
merge.data.frame(dt1, dt2)
#         id allele    zscore chrom
# 1 10000012      G -3.629986     4
# 2 10000012      G 15.806872     4
# 3 10000012      G  3.154770     4
abs(zscore1 - zscore2)
# [1] 3.019807e-14 4.440892e-15 1.332268e-15

The level of precision used by either version of merge() is not documented, nor can be controlled. Such tiny differences can appear when interacting with a database (the first is written, and the second is read out), and possibly losing records on merge is unexpected.

# Output of sessionInfo()

R version 3.6.3 (2020-02-29)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 18.04.4 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.12.8

loaded via a namespace (and not attached):
[1] compiler_3.6.3

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by running the supplied merge.data.table() and merge.data.frame() reproduction with the zscore values and comparing their matching behavior. Inspect the merge.data.table and merge.data.frame entry points for how numerical keys are compared, then determine and test the intended precision behavior; done should prevent records from being unexpectedly lost when values differ only in the shown tiny amounts.

Written by the indexing model from the issue text.

Assessment

Tech stack
r
Domain
data
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.