I am writing a Firebird adapter.
For example, in PostgreSQL, the following SQL statement is generated when :limit and :offset are specified.
select a, b from c limit LIMIT_VALUE offset OFFSET_VALUE
In Firebird, limit and offset do not exist, so I would like to do the following.
select top LIMIT_VALUE skip OFFSET_VALUE a, b from c
However, the parameters are in the wrong order.
opened 04:36PM - 25 Mar 24 UTC
bug
help wanted
Hi,
As the subject says, there seems to be a problem with the order of the pa… rameter values in this case..
I can do this ( I simplified the original query to make the code shorter ) :
```elixir
numero = "123"
query = from s in SocioTd,
join: sc in assoc(s, :sucursal),
where: s.numero == ^numero,
preload: [sucursal: sc],
offset: 0,
limit: 40
Repo.all( query )
```
I will have the expected records. the generated SQL will be something like this:
```sql
"SELECT FIRST 40 SKIP 0 v0.\"id\", v0.\"numero\", v0.\"nombre\"
FROM \"sociostd\" AS v0 INNER JOIN \"sucursales\" AS s1 ON s1.\"id\" = v0.\"idsucursal\"
WHERE (v0.\"numero_socio\" = ?)"
```
The previos query will have this param list values:
```elixir
["123"]
```
But the following won't work
```elixir
numero = "123"
offset = 0
limit = 40
query = from s in SocioTd,
join: sc in assoc(s, :sucursal),
where: s.numero == ^numero,
preload: [sucursal: sc],
offset: ^offset,
limit: ^limit
Repo.all( query )
```
the generated SQL will be something like this:
```sql
"SELECT FIRST ? SKIP ? v0.\"id\", v0.\"numero\", v0.\"nombre\"
FROM \"sociostd\" AS v0 INNER JOIN \"sucursales\" AS s1 ON s1.\"id\" = v0.\"idsucursal\"
WHERE (v0.\"numero_socio\" = ?)"
```
The previous query will have this param list values:
```elixir
["123", 40, 0]
```
So I will get neither results nor error.
Testing directly with Firebirdex, changing the param list values order to :
```elixir
[40, 0, "123"]
```
the query will give the expected results
edit: typos
This is because the order in which arguments are expanded is fixed, independent of the adapter.
opened 04:36PM - 25 Mar 24 UTC
bug
help wanted
Hi,
As the subject says, there seems to be a problem with the order of the pa… rameter values in this case..
I can do this ( I simplified the original query to make the code shorter ) :
```elixir
numero = "123"
query = from s in SocioTd,
join: sc in assoc(s, :sucursal),
where: s.numero == ^numero,
preload: [sucursal: sc],
offset: 0,
limit: 40
Repo.all( query )
```
I will have the expected records. the generated SQL will be something like this:
```sql
"SELECT FIRST 40 SKIP 0 v0.\"id\", v0.\"numero\", v0.\"nombre\"
FROM \"sociostd\" AS v0 INNER JOIN \"sucursales\" AS s1 ON s1.\"id\" = v0.\"idsucursal\"
WHERE (v0.\"numero_socio\" = ?)"
```
The previos query will have this param list values:
```elixir
["123"]
```
But the following won't work
```elixir
numero = "123"
offset = 0
limit = 40
query = from s in SocioTd,
join: sc in assoc(s, :sucursal),
where: s.numero == ^numero,
preload: [sucursal: sc],
offset: ^offset,
limit: ^limit
Repo.all( query )
```
the generated SQL will be something like this:
```sql
"SELECT FIRST ? SKIP ? v0.\"id\", v0.\"numero\", v0.\"nombre\"
FROM \"sociostd\" AS v0 INNER JOIN \"sucursales\" AS s1 ON s1.\"id\" = v0.\"idsucursal\"
WHERE (v0.\"numero_socio\" = ?)"
```
The previous query will have this param list values:
```elixir
["123", 40, 0]
```
So I will get neither results nor error.
Testing directly with Firebirdex, changing the param list values order to :
```elixir
[40, 0, "123"]
```
the query will give the expected results
edit: typos
It would be good if the value of exprs could be changed for each adapter.
defp traverse_exprs(query, operation, acc, fun) do
exprs =
case operation do
:all -> @all_exprs
:insert_all -> @all_exprs
:update_all -> @update_all_exprs
:delete_all -> @delete_all_exprs
end
Enum.reduce exprs, {query, acc}, fn {kind, key}, {query, acc} ->
{traversed, acc} = fun.(kind, query, Map.fetch!(query, key), acc)
{%{query | key => traversed}, acc}
end
end
It would be possible to get the value corresponding to @all_exprs from an adapter function rather than an attribute in query/planner.ex, so that it works correctly in Firebird.
But, can this be solved by just modifying the adapter, without modifying the Ecto source code?
1 Like
