slice_sample() does not interact correctly with set.seed() when working with an SQLite database.

[stephenashton-dhsc]

I'm not too sure if this issue sits within dbplyr or RSQLite, but the slice_sample() function does not appear to be taking account of a seed set via set.seed() when using an SQLite database.

If I set a seed, then run slice_sample(), then later set the same seed, and run slice_sample() again, the sampled table is different.

For comparison, when undertaking these sample operations using a Microsoft SQL Server database, the expected behaviour is observed (i.e., the two sampled tables are identical).

library(DBI)
library(RSQLite)
library(dplyr)
library(dbplyr)


tbl <- tibble(n = 1:30)
conn <- dbConnect(SQLite(), ":memory:")
dbWriteTable(conn, "test_table", tbl)

set.seed(100)
sample1 <- slice_sample(tbl(conn, "test_table"), n = 3)

set.seed(100)
sample2 <- slice_sample(tbl(conn, "test_table"), n = 3)

identical(sample1, sample2)

[stephenashton-dhsc]

If this is out of the control of R packages (for example, if the issue is with SQLite itself), would it be possible to return a warning to inform the user?

[mgirlich]

set.seed() can't affect the database (how could it as it doesn't even have any argument related to the database). This is true for SQLite and SQL Server and any other database. If slice_sample() consistently samples the same rows for SQL Server this would be a bug.
A warning doesn't make much sense here and would mostly annoy users.

Sorry, but I can't help here.

[krlmlr]

There are ways to make RAND() deterministic in SQL Server: https://database.guide/how-to-make-rand-deterministic-in-sql-server/ .

[nathanhaigh]

Same with Snowflake: https://docs.snowflake.com/en/sql-reference/constructs/sample#fraction-based-block-sampling-with-seeds