Ecto: Error when using custom query and Mariaex

[nathanjohnson320]

When I run
iex> Ecto.Adapters.SQL.query(MyRepo, "SELECT $1 + $2", [40, 2])
with the mariaex (mysql) adapter I get the following error

22:33:02.924 [debug] SELECT $1 + $2 [40, 2] (239.1ms)
** (Mariaex.Error) (1054): Unknown column '$1' in 'field list'
               (ecto) lib/ecto/adapters/sql/worker.ex:26: Ecto.Adapters.SQL.Worker.query!/4
               (ecto) lib/ecto/adapters/sql.ex:187: Ecto.Adapters.SQL.use_worker/3
    (model_generator) lib/model_generator.ex:13: Generator.Repo.log/2

[laurocaetano]

Hi!

Unfortunately the MySQL adapter doesn't support the query parameters in this format $1, $2 and etc.. Instead, you have to write in ? form, example: Ecto.Adapters.SQL.query(MyRepo, "SELECT ? + ?", [40, 2]).

I know it's not a consistent behaviour and maybe that's something that we might have to fix in the future.

[jasisz]

@laurocaetano I think this should be included in documentation... I was struggling with it for an hour and was about to file an issue when I've finally found this old issue cloes without any action ;/

[ericmj]

@jasisz Unfortunately we can't fix this on the ecto side without including an SQL parser which is out of scope for this project. A pull request improving the documentation would be appreciated.

[OvermindDL1]

@jasisz This is just a MySQL'ism ( https://stackoverflow.com/questions/45934923/unknown-column-1-in-field-list among other). When calling a raw SQL query each database tends to have slightly different formats. The $1 and so forth convention is a PostgreSQL/Oracle/Others convention https://www.postgresql.org/docs/9.5/static/xfunc-sql.html (although I think it supports ? as well, I prefer specific positioning so I don't have to worry about changing the order of the arguments if the SQL changes).