Comparing strings with numbers in the relational API
krlmlr opened this issue · 2 comments
krlmlr commented
Behavior differs between relational and SQL. @Tmonster: why would that be?
Relational
duckdb <- asNamespace("duckdb")
con <- DBI::dbConnect(duckdb::duckdb())
experimental <- FALSE
invisible(DBI::dbExecute(con, "CREATE MACRO \"!=\"(x, y) AS x <> y"))
df1 <- data.frame(a = 1)
rel1 <- duckdb$rel_from_df(con, df1, experimental = experimental)
rel2 <- duckdb$rel_filter(
rel1,
list(
duckdb$expr_function(
"!=",
list(
duckdb$expr_reference("a"),
if ("experimental" %in% names(formals(duckdb$expr_constant))) {
duckdb$expr_constant("", experimental = experimental)
} else {
duckdb$expr_constant("")
}
)
)
)
)
rel2
#> DuckDB Relation:
#> ---------------------
#> --- Relation Tree ---
#> ---------------------
#> Filter [!=(a, '')]
#> r_dataframe_scan(0x152320908)
#>
#> ---------------------
#> -- Result Columns --
#> ---------------------
#> - a (DOUBLE)
duckdb$rel_to_altrep(rel2)
#> [1] a
#> <0 rows> (or 0-length row.names)
Created on 2023-11-16 with reprex v2.0.2
SQL
✗ echo 'CREATE MACRO "!="(x, y) AS x <> y; SELECT 1.0 != '"''"' AS a' | build/debug/duckdb
Error: near line 1: Conversion Error: Could not convert string "" to DECIMAL(2,1)
✗ echo 'CREATE MACRO "!="(x, y) AS x <> y; SELECT '"''"' != 1.0 AS a' | build/debug/duckdb
Error: near line 1: Conversion Error: Could not convert string "" to DECIMAL(2,1)
Tmonster commented
Maybe the constant "" is getting registered as null?
I'll take a look
krlmlr commented
Will be fixed by extension.