Order of parameters is not preserved when cannonicalizing sql structure

[LostKobrakai]

Mix.install([{:sql, "~> 0.4.0"}])
# Mix.install([{:sql, github: "elixir-dbvisor/sql", branch: "main"}])

defmodule Bug do
  import SQL

  def run do
    name = "alice"
    min_age = 18

    {sql, params} =
      ~SQL[WHERE name = {{name}} SELECT id, {{min_age}} AS threshold FROM users]
      |> SQL.to_sql()

    IO.puts("""
    SQL:    #{sql}
    Params: #{inspect(params)}

    First  ? (SELECT threshold) bound to: #{inspect(Enum.at(params, 0))} — expected: #{inspect(min_age)}
    Second ? (WHERE name)       bound to: #{inspect(Enum.at(params, 1))} — expected: #{inspect(name)}
    """)
  end
end

Bug.run()

SQL:     select id, ? as threshold from users where name = ?
Params: ["alice", 18]

First  ? (SELECT threshold) bound to: "alice" — expected: 18
Second ? (WHERE name)       bound to: 18 — expected: "alice"

[Schultzer]

Thanks for reporting this, it’s an issue I need to deal with, un/fortunately 0.5 will only support Postgres initially. Where it’s not an issue as the parameters are indexed.

I’m currently still going through a major testing phase for the upcoming release, and since this will require further refactoring of the parser to have a pass that reorder parameters I’ll target this for the following release.

Although if this is blocking you and you would like to contribute, then I think the best path forward would be to delay calculating index until a later phase, currently when we’re lexing we calculate the index, but we could instead keep the binding value in the node. We would then have a pass after the parsing phase were we would walk the AST and extract the bindings and swap them. We don’t want to persist the AST where we swapped we only want to use that for SQL generation. It could also simplify how we generate inspect :)