Queries with `EXTRACT (X FROM date_column)` not working with MariaDB

[jumanbar]

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

[krlmlr]

Thanks. Can you please try with dbGetQuery(..., immediate = TRUE) ? This uses a different internal API for sending the query to the server.

[jumanbar]

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]