r-dbi
Queries with `EXTRACT (X FROM date_column)` not working with MariaDB
Specifically, when I use the EXTRACT (YEAR FROM date), I get:
Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM date_col) AS Year FROM mytable' at line 1 [1064]
(In this context, date_col is the name of the column from mytable, and is of type date.)
The SQL that I'm trying to execute is:
SELECT EXTRACT (YEAR FROM date_col) AS Year FROM mytable LIMIT 3;
Thankfully, the alternative works, which is:
SELECT YEAR(date_col) AS Year FROM mytable LIMIT 3;
But regardless, the previous one should work as well (and it does, when I run it in DBeaver). In my case, it would be very convenient, also, because I sort of need to use the same query for different databases, one is MariaDB, and the other Postgres, which doesn't support the YEAR(date_col) syntax (maybe because it's an old version of PG).
To reproduce what I'm getting, I think that this are the steps (executed inside RStudio, in my case):
con <- RMariaDB::dbConnect(RMariaDB::MariaDB(),
group = "mygroup",
default.file = "~/.my.cnf")
df <- DBI::dbGetQuery(
con,
"SELECT c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
FROM information_schema.`COLUMNS` c
WHERE TABLE_NAME = 'mytable' AND COLUMN_NAME = 'date_col';")
cat("\nColumn info:\n")
print(df)
df <- DBI::dbGetQuery(con, "SELECT date_col FROM mytable LIMIT 3;")
cat("\nQUERY 1:\n")
print(df)
df <- DBI::dbGetQuery(con, "SELECT YEAR(date_col) AS Year FROM mytable LIMIT 3;")
cat("\nQUERY 2:\n")
print(df)
df <- DBI::dbGetQuery(con, "SELECT EXTRACT (YEAR FROM date_col) AS Year FROM mytable LIMIT 3;")
cat("\nQUERY 3:\n")
print(df)
Output:
Column info:
TABLE_NAME COLUMN_NAME DATA_TYPE
1 mytable date_col date
QUERY 1:
date_col
1 2024-01-15
2 2014-01-03
3 2014-01-03
QUERY 2:
Year
1 2024
2 2014
3 2014
Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM date_col) AS Year FROM mytable LIMIT 3' at line 1 [1064]
Session info:
> sessionInfo()
R version 3.5.1 (2018-07-02)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19045)
Matrix products: default
locale:
[1] LC_COLLATE=Spanish_Uruguay.1252 LC_CTYPE=Spanish_Uruguay.1252 LC_MONETARY=Spanish_Uruguay.1252 LC_NUMERIC=C
[5] LC_TIME=Spanish_Uruguay.1252
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] devtools_2.3.1 usethis_1.6.1
loaded via a namespace (and not attached):
[1] Rcpp_1.0.4.6 rstudioapi_0.17.1 magrittr_2.0.1 hms_1.1.3 bit_1.1-15.2 pkgload_1.0.2 R6_2.4.1
[8] rlang_1.1.6 tools_3.5.1 pkgbuild_1.1.0 sessioninfo_1.1.1 cli_3.3.0 DBI_1.2.3 withr_2.4.1
[15] ellipsis_0.3.2 remotes_2.2.0 bit64_0.9-7 digest_0.6.25 assertthat_0.2.1 rprojroot_1.3-2 lifecycle_1.0.0
[22] crayon_1.4.2 processx_3.5.2 RMariaDB_1.2.2 callr_3.7.0 vctrs_0.6.5 fs_1.4.1 ps_1.3.2
[29] testthat_2.3.2 memoise_1.1.0 glue_1.6.2 compiler_3.5.1 generics_0.0.2 desc_1.2.0 backports_1.1.6
[36] prettyunits_1.1.1 lubridate_1.8.0 pkgconfig_2.0.3
MariaDB version: 10.5.15-MariaDB-0+deb11u1-log
Thanks for your time, Juan Manuel
Thanks. Can you please try with dbGetQuery(..., immediate = TRUE) ? This uses a different internal API for sending the query to the server.
Hi!
It didn't seem to change the result:
> df <- DBI::dbGetQuery(con, "SELECT EXTRACT (YEAR FROM date_col) AS Year FROM mytable LIMIT 3;", immediate = TRUE)
Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM date_col) AS Year FROM mytable LIMIT 3' at line 1 [1064]