Ecto Query Returns unwanted data

I am trying to write a query that gets all users matching a criteria

The postgresql query is as follows

select id from users WHERE account_id  IS NOT NULL LIMIT 10 OFFSET 10;

in this example, the number of users that match the given criteria are 12, meaning that for this query, only two user ids will be returned since it skips by 10 rows. This brings the correct data.

I have written the same query in ecto as follows

from(u in User, where: not is_nil(u.account_id), limit: 10, offset: 0 , select: u.id) |> Repo.all() 

expected response:

{:ok, [11, 12]}

actual response:

'\f\r'

what is going on?

They are the same, you’re simply seeing them formatted as characters because iex is trying to be helpful.

Run this in iex[11, 12] == '\f\r'

1 Like

Repo.all() returns a “bare” list, not an :ok/:error tuple.

Because you are only selecting ids that are each less than 255, they are being output as a charlist. The IDs in your case happen to correspond to the ascii characters \f and \r.

EDIT: or [11, 12] as @dimitarvp more helpfully said :slight_smile:

1 Like

Oops yeah, OP still needs to make sure the whole thing is wrapped in an :ok tuple indeed.

Wrong, see my next comment.

1 Like

I feel so embarrassed, thank you, it’s been eating me up for hourse

Finally, Ecto.Repo.all does not return stuff in an :ok tuple so you’re getting exactly what you should.

1 Like

It’s a subtle syntactic difference and has been the cause of many questions. If you upgrade to the latest version of Elixir, charlists are now output using the new ~c sigil which is much easier to catch. You would have seen ~c"\f\r".

2 Likes