Repository navigation
Bug related to merging on character vs factor column when factor is sorted #5361
Description
Activity
The join seems accurate to me. What were you expecting? The result matches with R
basetoo.library(data.table) some_letters <- rev(letters[1:3]) some_more_letters <- rep(letters[1:3], 2) dt1 <- data.table(x = some_letters, y = 1:3) dt2 <- data.table(x = factor(some_more_letters, levels = some_letters), z = 1:6) dt2 <- setkey(dt2, x, z) # Calls data.table merge dt3 <- merge(dt1, dt2, by = "x") # Calls base merge df3 <- merge(as.data.frame(dt1), as.data.frame(dt2), by="x") fsetdiff(dt3, as.data.table(df3))
I expected
dt3[x %in% "c", ]not to be empty after the merge. In particular, I expectdt3[x %in% "c", ]anddt3[(x %in% "c"), ]to agree.Reacted by avimalluIt seems to be related to setkey. Once you remove the key, the behavior goes back to normal.
> library(data.table) > dt3 <- structure(list(x = c("c", "c", "b", "b", "a", "a"), y = c(1L, 1L, 2L, 2L, 3L, 3L), z = c(3L, 6L, 2L, 5L, 1L, 4L)), sorted = "x", class = c("data.table", "data.frame"), row.names = c(NA, -6L)) > dt3[x %in% c("a", "b", "c"), ] x y z 1: b 2 2 2: b 2 5 > setkey(dt3, NULL) > dt3[x %in% c("a", "b", "c"), ] x y z 1: c 1 3 2: c 1 6 3: b 2 2 4: b 2 5 5: a 3 1 6: a 3 4AFAIR the fast subsetting problem always arises when a
data.tablehas a key attribute although it isn't really sorted according to that key.At the example presented here, there arise multiple different issues and I'm not sure which is the best one to fix:
- the merge of a
characterand afactorcolumn returns a "wrong" result in the sense the that the result has akeyalthough it it not sorted by the `key
dt1 = data.table(x=c("c", "b", "a")) dt2 = data.table(x=factor(c("a", "b", "c"), levels=c("c", "b", "a"))) setkey(dt2, x) dt = dt2[dt1, on="x"]
- fast subset does not work on a keyed
data.tablewhich is actually not sorted by the key setkeyvmight have to check if it subsets thekey, because currentlysortedis not always trustworthy. This is related to keys are wrong/don't update if column names aren't unique #4888 and Inconsistent behavior in keyed/unkeyed joins against duplicate columns #4891
- the merge of a
For 2 & 3, I think we should assume 'sorted' is correct. I don't think we should invest in re-re-checking that holds every time we try and use the attribute, IMO it somewhat defeats the purpose.
- added a commit that references this issue
on May 22, 2026
I see the following behavior which I believe indicates a bug in
merge.data.table:I believe the problem is that
dt3thinks columnxis sorted (and it would be if it was a factor), but it is not as a character. I assume thatdata.tables has an internal optimized%in%operator that uses this information and then gives the wrong result when we attempt to subset onx %in% "c". Finally, I assume that wrapping the subset operation in parenthesis avoids the use ofdata.tables internal%in%, so the subset works correctly as it no longer used the incorrectsortedattribute ondt3. Even if the last two assumptions are wrong, the behavior above seems incorrect.The closest thing I could find is issue #499, but I think is is different.