setDF should drop indices

[OfekShilon]

ff2.zip

Attached is a small zip archive of a saved data.table that demonstrates a problem where sometimes the [ operator gives misleading results.

load("./ff2")   # from the path you extracted the archive to
dim(ff[symbol == "ZM" & date == 20210128])
#  [1] 0 2     <---- wrong results, this subset is not empty

One way to show the correct result:

> dim(ff[symbol == "ZM"][date == 20210128])
# [1] 98  2

Many seemingly no-ops fix the data.table state. One example:

ff[, symbol:=symbol]
dim(ff[symbol == "ZM" & date == 20210128])
# [1] 98  2

Tested on both Windows and Ubuntu, and data.table versions 1.13.6 and 1.11.8 correspondingly. (the sessionInfo below is of the windows machine)

# Output of sessionInfo()
R version 3.6.1 (2019-07-05)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19042)

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252 LC_CTYPE=English_United States.1252 LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C LC_TIME=English_United States.1252

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

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

loaded via a namespace (and not attached):
[1] bit_4.0.4 compiler_3.6.1 tools_3.6.1 bit64_4.0.5

[tlapak]

I don't know what happened, but your saved data is at fault here. The data.table in there has an index on c('date', 'symbol') which gets used for the subset. The problem is that the index is empty. Overriding one of the columns contained in the index drops it. It's not a general issue with saving data at least on Windows 10 and R 4.0.3:

library('data.table')

load('ff2')

ff[symbol == "ZM" & date == 20210128]
#> Empty data.table (0 rows and 2 cols): date,symbol

indices(ff)
#> [1] "date__symbol"
attr(attr(ff, 'index'), '__date__symbol')
#> integer(0)

setindex(ff, NULL)
setindex(ff, 'date', 'symbol')
attr(attr(ff, 'index'), '__date__symbol')
#>     [1]    129    130   1278   3681   3682   3683   3684   4204   4205   4206
# [removed additional output]

ff[symbol == "ZM" & date == 20210128]
#>         date symbol
#>  1: 20210128     ZM
#>  2: 20210128     ZM
#>  3: 20210128     ZM
# [removed additional output]
#>         date symbol

gg <- copy(ff)

save(gg, file = 'gg')
load('gg')

attr(attr(gg, 'index'), '__date__symbol')
#>     [1]    129    130   1278   3681   3682   3683   3684   4204   4205   4206
# [removed additional output]

gg[symbol == "ZM" & date == 20210128]
#>         date symbol
#>  1: 20210128     ZM
#>  2: 20210128     ZM
#>  3: 20210128     ZM
# [removed additional output]
#>         date symbol

^{Created on 2021-02-03 by the reprex package (v1.0.0)}

Not relevant here, but it is highly recommended to always call setDT on any data.table loaded from disk to get it properly over allocated and get everything working as expected.

[OfekShilon]

@tlapak Thank you very much for your quick and informative response!
I believe it is possible to reproduce the formation of an empty index even with the original data (probably any data for that matter):

library('data.table')
load('ff2')
setindex(ff, NULL)  # data should be coherent now

ff[symbol == "ZM" & date == 20210128]  # <-- this line creates an 'invalid' index?
#...

indices(ff)
#> [1] "date__symbol"
attr(attr(ff, 'index'), '__date__symbol')
#> integer(0)

This will (probably) no longer reproduce the symptom of falsely-empty subset, but does regenerate what you describe as an invalid index.
Can you please say if you're able to reproduce these results?

[tlapak]

Sorry, I should have been clearer here. The index is already invalid once you load the data from disk. Probably due to whatever went wrong for you before. I cannot reproduce the final output (integer(0)) on Windows 10, R 4.0.3 and data.table 1.13.6. I get

attr(attr(ff, 'index'), '__date__symbol')
#>     [1]    129    130   1278   3681   3682   3683   3684   4204   4205   4206

and so everything works as expected for me. I'd expect the next subset to fail for you again.
Unfortunately, I can't test this under an older R version at the moment to see if it's something there that causes this.

I should add that when an index is set to integer(0) that means the data.table is supposed to be already sorted by those columns. Hard to tell what's going wrong there without being able to reproduce, sorry.

Edit: I played around with it some more and when I change the order of the subset conditions I get an index "symbol__date" that is integer(0), i.e. the table is ordered by those columns in that order. However, your freshly loaded dataset still fails to return the correct result and nothing breaks for me again after repairing it.

[OfekShilon]

@tlapak I'm still trying to isolate the creation of bad data, but noticed things that may be relevant:
(1)

library(data.table)
rm(ff)
#> Warning in rm(ff): object 'ff' not found
load("C:/temp/ff1")
setindex(ff, NULL)  # fix the initial state

invisible(ff[symbol == "AMT" & date == 20200915])  # symbol first:
indices(ff)
#> [1] "date__symbol"

setindex(ff, NULL)
invisible(ff[date == 20200915 & symbol == "AMT"]) # date first:
indices(ff)
#> [1] "symbol__date"

setindex(ff, NULL)
invisible(ff[symbol == "AMT" & date == 20200915])  # symbol first
invisible(ff[date == 20200915 & symbol == "AMT"]) # this *doesn't change* the order of indices!
indices(ff)
#> [1] "date__symbol"

^{Created on 2021-02-04 by the reprex package (v1.0.0)}
Is this behaviour safe? Is it by design?

(2)
setDF clears a few DT-only attributes, but not index. That's very risky:

dt <- data.table(***)
# ... do stuff that creates indices
setDF(dt)
# ... do stuff that changes data, breaking the index
setDT(dt)
#  index attribute is still there, but the data doesn't match.

[jangorecki]

After loading data table from a binary format one should call setDT on it. It is described in FAQ. Does it solve your problem?

[OfekShilon]

@jangorecki it doesn't matter. See below.
@tlapak I think I have a better repro. Extract and load the attached ff5:
ff5.zip

rm(ff)
load("./ff5") # or wherever you extracted the file
ff <- TMP.OBJECT

setDT(ff)   # per @jangorecki comment, but doesn't matter here

# Do stuff that creates indices:
invisible(ff[symbol == "ADBE" & date == 20200915])
setDF(ff)

# Do stuff that changes data, making the index invalid:
ff[1:50, "date"] <- as.integer(ff[1:50, "date"] + 10)

setDT(ff)
#  index attribute is unchanged through all of this - displayed results are broken!
dim(ff[symbol == "ADBE" & date == 20200915])   # 0 2

# manual fix:
setindex(ff,NULL)
dim(ff[symbol == "ADBE" & date == 20200915])  # 27 2

[jangorecki]

Thanks, looks clearly like a bug then.

We could probably remove any indices or keys in case of updating column with <-:

ff[1:50, "date"] <- as.integer(ff[1:50, "date"] + 10)

which is not a common way of updating values in a column for data.table, where := is recommended. Then in results it will be only a slow down and not risk of having invalid index and incorrect answer.

[tdeenes]

I think the problem is with setDF and not with the assignment. From the manual of setDF:

All data.table attributes including any keys of the input data.table are stripped off.

The correct behaviour (and the adjusted doc) should read as:

All data.table attributes including any keys and indices of the input data.table are stripped off.

d <- data.table(x = 1)
setindex(d, "x")
indices(d)
# [1] "x"

setDF(d)
indices(d)
# [1] "x" -> should have been stripped off

[OfekShilon]

I agree with @tdeenes, raised this in the 2nd part of my comment above:

(2)
setDF clears a few DT-only attributes, but not index. That's very risky:

dt <- data.table(***)
# ... do stuff that creates indices
setDF(dt)
# ... do stuff that changes data, breaking the index
setDT(dt)
#  index attribute is still there, but the data doesn't match.

The 1st half still worries me too, but it might be a different bug.

Also @jangorecki note that the update that uses <- is performed on a data.frame, not data.table.

[OfekShilon]

Self contained repro (without loading anything, suitable for unit tests):

d <- data.table(a=1:100, b=1:100)
invisible(d[a == 50])   

setDF(d)
d[1:50, "a"] <- d[51:100, "a"]
setDT(d)

print(d[a == 99])    # one line, wrong result

# manual fix:
setindex(d,NULL)
print(d[a == 99])    # two lines, right result

[OfekShilon]

I created a single line PR for this