no function clause matching in Mariaex.Protocol.handle_error/3 if SQL has whitespace at beginning or end

[geofflane]

Using Mariaex 0.7.7, MySQL 5.7.13 Running against a legacy schema that has a lot of columns that need to be quoted (not sure if that's related). I run the following query which works in MySQL. But using Mariaex and/or Ecto it fails with an error.

sql = """
      SELECT p.id `play-id`, p.rps, p.`ydspostcontact`, p.`yds`, pi.`missed_tackle` `missed-tackle`, rt.type `run-type`, ct.description `run-concept`, ps.success success
      FROM plays p
      JOIN `play-success` ps ON ps.`play-id` = p.id
      JOIN `success` ON success.`success-id` = ps.`success-id`
      JOIN `play-info` pi ON pi.`play-id` = p.id
      LEFT JOIN `run-type` rt ON rt.`id` = p.`runpos-id`
      LEFT JOIN `concept-type` ct ON ct.id=p.`run-concept-id`
      WHERE p.id IN (1,2,3) AND success.`off-def` = 'O'
    """
 Ecto.Adapters.SQL.query(__MODULE__, sql, [])

// Also tried Mariex directly and it has the same issue
{:ok, p} = Mariaex.start_link(username: "root", database: "foo_focus_live")
Mariaex.query(p, query)

The error is the following:

     test/controllers/run_concepts_controller_test.exs:4
     ** (FunctionClauseError) no function clause matching in Mariaex.Protocol.handle_error/3
     stacktrace:
       (mariaex) lib/mariaex/protocol.ex:332: Mariaex.Protocol.handle_error({:packet, 1, 1, {:column_count, 8}, "\b"}, %Mariaex.Query{connection_ref: nil, name: "", parameter_types: [], reserved?: false, statement: "  SELECT p.id `play-id`, p.rps, p.`ydspostcontact`, p.`yds`, pi.`missed_tackle` `missed-tackle`, rt.type `run-type`, ct.description `run-concept`, ps.success success\n  FROM plays p\n  JOIN `play-success` ps ON ps.`play-id` = p.id\n  JOIN `success` ON success.`success-id` = ps.`success-id`\n  JOIN `play-info` pi ON pi.`play-id` = p.id\n  LEFT JOIN `run-type` rt ON rt.`id` = p.`runpos-id`\n  LEFT JOIN `concept-type` ct ON ct.id=p.`run-concept-id`\n  WHERE p.id IN (123,234) AND success.`off-def` = 'O'\n", statement_id: nil, type: :text, types: []}, %Mariaex.Protocol{buffer: "", cache: 5542120, catch_eof: false, connection_id: #PID<0.541.0>, connection_ref: #Reference<0.0.7.3109>, lru_cache: {100, 5546220}, opts: [socket_options: [], sock_type: :tcp, cache_size: 100, pool: DBConnection.Poolboy, otp_app: :pat, repo: Pat.Repo, adapter: Ecto.Adapters.MySQL, username: "root", password: "", database: "foo_focus_live", hostname: "localhost", pool_timeout: 5000, timeout: 15000, adapter: Ecto.Adapters.MySQL, username: "root", password: "", database: "foo_focus_live", hostname: "localhost", port: 3306], protocol57: true, rows: [], seqnum: 0, sock: {Mariaex.Connection.Tcp, #Port<0.25665>}, state: :column_count, state_data: nil, timeout: 15000})
       (ecto) lib/ecto/adapters/sql/sandbox.ex:323: Ecto.Adapters.SQL.Sandbox.Connection.proxy/3
       (db_connection) lib/db_connection.ex:750: DBConnection.handle/4
       (db_connection) lib/db_connection.ex:866: DBConnection.describe_execute/4
       (db_connection) lib/db_connection.ex:930: anonymous fn/4 in DBConnection.run_meter/5
       (db_connection) lib/db_connection.ex:987: DBConnection.run_begin/3
       (db_connection) lib/db_connection.ex:463: DBConnection.prepare_execute/4
       (ecto) lib/ecto/adapters/mysql/connection.ex:32: Ecto.Adapters.MySQL.Connection.execute/4
       (ecto) lib/ecto/adapters/sql.ex:228: Ecto.Adapters.SQL.sql_call/6
       (pat) lib/pat/legacy_repo.ex:385: Pat.LegacyRepo.all/2
       (pat) web/controllers/run_concept_controller.ex:5: Pat.RunConceptController.index/2
       (pat) web/controllers/run_concept_controller.ex:1: Pat.RunConceptController.action/2
       (pat) web/controllers/run_concept_controller.ex:1: Pat.RunConceptController.phoenix_controller_pipeline/2
       (pat) lib/pat/endpoint.ex:1: Pat.Endpoint.instrument/4
       (pat) lib/phoenix/router.ex:261: Pat.Router.dispatch/2
       (pat) web/router.ex:1: Pat.Router.do_call/2
       (pat) lib/pat/endpoint.ex:1: Pat.Endpoint.phoenix_pipeline/1
       (pat) lib/pat/endpoint.ex:1: Pat.Endpoint.call/2
       (phoenix) lib/phoenix/test/conn_test.ex:224: Phoenix.ConnTest.dispatch/5
       test/controllers/run_concepts_controller_test.exs:5

[geofflane]

It seems like it has something to do with the heredoc string. If I strip all of the tabs, newlines and spaces from around the SQL then it works.

e.g.

    sql = sql |> String.strip() # trim in Elixir 1.3

[liveforeverx]

Yes, probably I need to add to documentation, that query should be sanitized before executing.

That is done for performance reasons. You can always sanitize it in client code, when you need, but if you sanitize it in a library code, than it will always have this performance drawback. By the way, ecto should generate queries without whitespaces...

Anyway, you are definitely right, crash is not a good description for a problem, I will check it.

[nichochar]

This is an issue I also ran into. It's not the triple quotes, it's the whitespace, you can reproduce with a whitespace at the origin. @liveforeverx I think it's fine that the library decides to push the optimization to the client, but I think the error message could be more clear....

I had a hard time debugging this issue with the "unsupported parameterized query message...

Maybe once the error has been raised, check for the whitespace and change the error at that point?

[fishcakez]

This issue should be resolved by #183.