Elixir Repo get_by is not working with some string name option

Questions / Help
1

Hi,

I have found one strange problem while trying to get one of the products with Ecto.Repo.get_by as follows.

Repo.get_by(Operator.Product, name: "3222312724 | 92608 | OP20 | 5188 | Air receiver - Welding Epiroc light side")

This product is definitely present in the DB and we are using PostgreSQL as our DB. I have tried to get other products with the same query and they worked as intended except for this case. I am curious as it seems like an Ecto issue…or I am missing something?

2

Hi @masudme09 I would consider turning on debug logging and verifying that the SQL that is generated is what you expect, and that the parameter being passed to the SQL is what you expect.

Beyond that, perhaps there is some non visible UTF8 character in the actual database row? If you fetch the row by id and then compare record.name == "3222312724 | 92608 | OP20 | 5188 | Air receiver - Welding Epiroc light side" what do you get?

2 Likes
3

One way to debug it is this:

import Ecto.Query

q = from(p in Operator.Product, where: p.name == ^"3222312724 | 92608 | OP20 | 5188 | Air receiver - Welding Epiroc light side")

# in iex:
Ecto.Adapters.SQL.to_sql(:all, Repo, q) |> IO.puts()

And then try the resulting query inside psql.

1 Like
4

Thanks @benwilson512. After comparing with the database…I have found that the name contains a space at the end. And if I compare as below…it gives me true.

iex(20)> product.name == "3222312724 | 92608 | OP20 | 5188 | Air receiver - Welding Epiroc light side "
true

But with the following, it still returns nil:

iex(23)> Repo.get_by(Operator.Product, name: "3222312724 | 92608 | OP20 | 5188 | Air receiver - Welding Epiroc light side ")
nil
5

What if you try with ilike?
https://hexdocs.pm/ecto/Ecto.Query.API.html#ilike/2

6

Something else is wrong here, I tried that string in one of my projects and Repo.get_by works fine.

7

OK so if you do Repo.get_by(Operator.Product, name: product.name) do you get the value or no? Can you show the SQL being generated in this case and in the case of the lookup by ID?

8

So, when get by id:

iex(10)> product = Repo.get(Operator.Product, 610218)
[debug] QUERY OK source="products" db=2.9ms idle=1295.5ms
SELECT p0."id", p0."uid", p0."name", p0."max_production", p0."effective_production", p0."stop_time_min", p0."product_code", p0."barcode", p0."enabled", p0."data", p0."company_id", p0."allowed_workstations", p0."inserted_at", p0."updated_at" FROM "products" AS p0 WHERE (p0."id" = $1) [610218]

%Operator.Product{
  __meta__: #Ecto.Schema.Metadata<:loaded, "products">,
  id: 610218,
  uid: "91985903-2c2e-4002-993b-2c2e72d7e517",
  name: "3222312724 | 92608 | OP20 | 5188 | Air receiver - Welding Epiroc light side ",
  max_production: 0.025,
  effective_production: nil,
  stop_time_min: 40.0,
  product_code: "3222312724",
  barcode: nil,
  enabled: 1,
  data: nil,
  master_product: #Ecto.Association.NotLoaded<association :master_product is not loaded>,
  company_id: 513,
  company: #Ecto.Association.NotLoaded<association :company is not loaded>,
  productions: #Ecto.Association.NotLoaded<association :productions is not loaded>,
  production_product: #Ecto.Association.NotLoaded<association :production_product is not loaded>,
  production_scraps: #Ecto.Association.NotLoaded<association :production_scraps is not loaded>,
  orders: #Ecto.Association.NotLoaded<association :orders is not loaded>,
  allowed_workstations: nil,
  restricted_to_workstations: #Ecto.Association.NotLoaded<association :restricted_to_workstations is not loaded>,
  inserted_at: ~N[2023-09-07 08:44:16],
  updated_at: ~N[2023-09-07 08:44:16]
}

And with get_by:

iex(13)> Repo.get_by(Operator.Product, name: product.name)
nil
iex(14)> [debug] QUERY OK source="products" db=70.6ms queue=1.6ms idle=1326.4ms
SELECT p0."id", p0."uid", p0."name", p0."max_production", p0."effective_production", p0."stop_time_min", p0."product_code", p0."barcode", p0."enabled", p0."data", p0."company_id", p0."allowed_workstations", p0."inserted_at", p0."updated_at" FROM "products" AS p0 WHERE (p0."name" = $1) ["3222312724 | 92608 | OP20 | 5188 | Air receiver - Welding Epiroc light side"]

So, as you can see, Repo.get_by actually trimmed the trailing space.

To be sure, I have manually put trailing space on one of the product names and result is the same:

image
image1200×594 69.8 KB

Using Ecto: {:phoenix_ecto, “~> 4.4”},

9

Weird! Can you show the output of mix deps | grep ecto

10

Here it is:
image

11

Have you tried copying the SQL that Ecto generates and pasting it in psql? I think that can be illuminating.

12

It is already visible in the generated SQL that Repo.get_by is removing the trailing space.

13

I have tried the same scenario in my other project and that worked as intended. Maybe some bug in the ecto version? Working project uses little backdated ecto as below:

image

14

Perhaps, can you try on the latest ecto? (3.10.x)

EDIT: I am on 3.10.3 and do not see this behavior:

> User |> Repo.get_by(name: "foo ")
WHERE (u0."name" = $1) ["foo "]
15

Maybe you have custom ecto type that does trimming?

6 Likes
16

Yap. This was the case, and I was unaware of it.

1 Like