dbListFields error on table with JSON column

[LeeMendelowitz]

DBI::dbListFields does not work on a table that has a JSON column. I get an error message "Error: Unimplemented MAX_NO_FIELD_TYPES". I do not get the same error if I try using an RMySQL connection.

I am connecting to MySQL Server version: 8.0.26.

---

1. Create a table with a JSON column.
2. Try to list fields on that table.

library(DBI)
library(RMariaDB)
library(RMySQL)

con = dbConnect(MariaDB(), "test")
dbExecute(con, "CREATE TABLE test_json (
    pk INTEGER PRIMARY KEY,
    col_json JSON
)")

# This fails
# I get the error message: "Error: Unimplemented MAX_NO_FIELD_TYPES"
dbListFields(con, "test_json")

# This works
con_rmysql  = dbConnect(MySQL(), "test")
dbListFields(con_rmysql, "test_json")

---

> sessionInfo()
R version 4.0.4 (2021-02-15)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 20.04.2 LTS

Matrix products: default
BLAS:   /usr/lib/x86_64-linux-gnu/openblas-pthread/libblas.so.3
LAPACK: /usr/lib/x86_64-linux-gnu/openblas-pthread/liblapack.so.3

locale:
 [1] LC_CTYPE=C.UTF-8       LC_NUMERIC=C           LC_TIME=C.UTF-8
 [4] LC_COLLATE=C.UTF-8     LC_MONETARY=C.UTF-8    LC_MESSAGES=C.UTF-8
 [7] LC_PAPER=C.UTF-8       LC_NAME=C              LC_ADDRESS=C
[10] LC_TELEPHONE=C         LC_MEASUREMENT=C.UTF-8 LC_IDENTIFICATION=C

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

other attached packages:
[1] RMySQL_0.10.22 RMariaDB_1.2.1 DBI_1.1.2

loaded via a namespace (and not attached):
 [1] bit_4.0.4        compiler_4.0.4   ellipsis_0.3.2   generics_0.1.0
 [5] hms_1.1.0        tools_4.0.4      Rcpp_1.0.7       bit64_4.0.5
 [9] lubridate_1.7.10 vctrs_0.3.8      lifecycle_1.0.0  pkgconfig_2.0.3
[13] rlang_0.4.11     renv_0.14.0

[krlmlr]

Thanks. This works for me on 10.6.4-MariaDB, we also have a test after #127 that is tested on GitHub Actions with various combinations of libmariadb/libmysql and database servers:

RMariaDB/tests/testthat/test-dbWriteTable.R

Lines 75 to 85 in fe56f2a

	test_that("writing and reading JSON (#127)", {
	con <- mariadbDefault()
	on.exit(dbDisconnect(con))
	x <- data.frame(col1 = "[1,2,3]", stringsAsFactors = FALSE)
	dbWriteTable(con, "t1", x, field.types = c(col1 = "json"), overwrite = TRUE, temporary = TRUE)
	dbReadTable(con, "t1")
	expect_equal(dbReadTable(con, "t1"), x)
	})

On the other hand, the error seems to originate from:

RMariaDB/src/MariaTypes.cpp

Line 54 in fe56f2a

throw std::runtime_error("Unimplemented MAX_NO_FIELD_TYPES");

I wonder what type is on input in this function when it fails. Could you run a debugging version from a branch on GitHub on your system to find out?

[LeeMendelowitz]

I added some output to function

RMariaDB/src/MariaTypes.cpp

Line 7 in fe56f2a

MariaFieldType variable_type_from_field_type(enum_field_types type, bool binary, bool length1) {

.

The JSON column is coming back with type 245:

r$> dbListFields(con, "test_json")
type: 3
type: 245
Error: Unimplemented MAX_NO_FIELD_TYPES

[LeeMendelowitz]

This lines up with what is in MySQL's field_types.h:

https://github.com/mysql/mysql-server/blob/6846e6b2f72931991cc9fd589dc9946ea2ab58c9/include/field_types.h#L80

[krlmlr]

Is this still an issue with the most recent version?

[LeeMendelowitz]

This is no longer an issue! Everything is working as expected with the latest version.