[BUG] Using `DBInterface.execute` Does Not "Just Work" with MySQL

[TheCedarPrince]

Hi all,

I seem to be having a strange issue where the following code does not work as expected:

From(tab) |> Select(Get.foo) |> Limit(1) |> q -> render(q, dialect = :mysql) |> x -> DBInterface.execute(conn, x) |> DataFrame

ERROR: MethodError: no method matching query(::MySQL.API.MYSQL, ::FunSQL.SQLString)
Closest candidates are:
  query(::MySQL.API.MYSQL, ::String) at ~/.julia/packages/MySQL/0vHyV/src/api/capi.jl:1219
Stacktrace:
 [1] execute(conn::MySQL.Connection, sql::FunSQL.SQLString, params::Tuple{}; mysql_store_result::Bool, mysql_date_and_time::Bool)
   @ MySQL ~/.julia/packages/MySQL/0vHyV/src/execute.jl:158
 [2] execute(conn::MySQL.Connection, sql::FunSQL.SQLString, params::Tuple{}) (repeats 2 times)
   @ MySQL ~/.julia/packages/MySQL/0vHyV/src/execute.jl:155
 [3] (::var"#8#10")(x::FunSQL.SQLString)
   @ Main ./REPL[16]:1
 [4] |>(x::FunSQL.SQLString, f::var"#8#10")
   @ Base ./operators.jl:966
 [5] (::var"#7#9")(q::FunSQL.SQLNode)
   @ Main ./REPL[16]:1
 [6] |>(x::FunSQL.SQLNode, f::var"#7#9")
   @ Base ./operators.jl:966
 [7] top-level scope
   @ REPL[16]:1

Where my connection looks like this:

conn = DBInterface.connect(MySQL.Connection, "my.host.com", "username", "password"; db = "database", port = 3306, unix_socket = MySQL.API.MYSQL_DEFAULT_SOCKET, client_flag=MySQL.API.CLIENT_MULTI_STATEMENTS, opts = Dict())

A workaround to this I found was doing the following:

From(tab) |> Select(Get.foo) |> Limit(1) |> q -> render(q, dialect = :mysql) |> x -> DBInterface.execute(conn, String(x)) |> DataFrame

Which returned the expected result. This seems like a bug where FunSQL.SQLString is not accepted when I expected it to be. Is this a bug?

Details:

FunSQL Version: [cf6cc811] FunSQL v0.10.1 Julia Version: 1.7.0 OS: Mac OSX

[xitology]

This an issue with MySQL.jl not accepting an arbitrary AbstractString value as a SQL query: https://github.com/JuliaDatabases/MySQL.jl/issues/189

Note that if you create a connection object for FunSQL.DB{MySQL.Connection}, you could apply DBInterface.execute directly to the FunSQL query object:

conn = DBInterface.connect(FunSQL.DB{MySQL.Connection}, "my.host.com", "username", "password"; db = "database", port = 3306, unix_socket = MySQL.API.MYSQL_DEFAULT_SOCKET, client_flag=MySQL.API.CLIENT_MULTI_STATEMENTS, opts = Dict())

From(tab) |> Select(Get.foo) |> Limit(1) |> q -> DBInterface.execute(conn, q) |> DataFrame

[xitology]

Fixed in https://github.com/JuliaDatabases/MySQL.jl/pull/190.