dbQuoteLiteral() should return typed NULL

[mgirlich]

Currently, dbQuoteLiteral() simply returns NULL for missing values

DBI::dbQuoteLiteral(con, c(1L, NA_integer_))
#> <SQL> 1::int4
#> <SQL> NULL

While this is usually okay, this doesn't work well when creating a table via a values clause

DBI::dbGetQuery(
  con,
r"{WITH tbl1 AS (
 SELECT NULL AS "country", NULL AS "date" WHERE false
), tbl2 AS (
  SELECT NULL AS "country", NULL AS "date" WHERE false
  UNION ALL
  VALUES
    ('de', '2021-11-19'::date)
)
SELECT tbl1.*
FROM tbl1
INNER JOIN tbl2
ON (tbl1.country = tbl2.country AND tbl1.date = tbl2.date)}"
)

#> Error: Failed to prepare query: ERROR:  operator does not exist: text = date
#> LINE 15: ON (tbl1.country = tbl2.country, tbl1.date = tbl2.date)

With a typed NULL this works nicely

DBI::dbGetQuery(
  con,
r"{WITH tbl1 AS (
 SELECT NULL::text AS "country", NULL::date AS "date" WHERE false
), tbl2 AS (
  SELECT NULL AS "country", NULL AS "date" WHERE false
  UNION ALL
  VALUES
    ('de', '2021-11-19'::date)
)
SELECT tbl1.*
FROM tbl1
INNER JOIN tbl2
ON (tbl1.country = tbl2.country AND tbl1.date = tbl2.date)}"
)

This is an issue for the db_values() function which hopefully soon comes to dbplyr (see tidyverse/dbplyr#678)

[krlmlr]

Thanks. This is just the tip of the iceberg, type safety with dbQuoteLiteral() is next to nowhere in the other backends. It's worth fixing here to set a standard for good behavior (and perhaps add a DBItest test).

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.