Ecto not allowing string interpolation in fragments?

Have you tried do the string interpolation ahead of building the query.
e.g.

 other_table = ...
 frag = "exists(SELECT 1 FROM #{other_table} WHERE o.column_name = ?)"
 from (a in My.Scheme,
  where: fragment(frag, ^a.my_field)

I haven’t tested it myself, but it’s the first thing I’d try

Are you by chance using PostgreSQL? If so then SELECT 1 FROM table WHERE pattern LIMIT 1 will be equally performant. Unfortunately there is no way to do string interpolation in fragment as IIRC it is compile-time checked for correctness. There was fragment_unsafe for brief moment but IIRC it was removed.

No, this does not seem to work either.

Thanks, but unfortunately this is actually meant to operate on an existing query that is being passed in, kind of like so (though this is greatly simplified):

  def apply_grant_scoping(query, schema) do
    other_table = Other.Schema.__schema__(:source)
    query
    |> where([resource: a], fragment("exists (
        SELECT 1
        FROM #{other_table} o
        WHERE o.column_name = ?)", ^a.my_field)
    )
  end

It is basically an implementation of a grant-based authorization system. It runs on a lot of queries we make so performance is paramount. Honestly I guess I can find some way to get all the different possible strings constructed at compile time, but this is pretty frustrating. I feel like as an API it should simply accept the string I give it, or at least have some sort of escape hatch available.

The more we try to optimize our system for performance, the more we have become frustrated with Ecto. It is an excellent library, but it appears geared towards smaller-scale applications that treat the database more like simple data store rather than an equal and essential part of solving complex engineering problems. I of course knew that hand-writing SQL would be necessary eventually, but I just assumed ecto wouldn’t do things like this to prevent me from constructing queries manually, otherwise I would have gone for bare postgrex from the beginning.

Ecto supports subqueries natively in joins. Is there anything preventing you from refactoring the query to be joined instead of being a where condition?

Is there anything preventing you from refactoring the query to be joined instead of being a where condition?

Yes, unfortunately, in order to accomplish this with joins we have to use joins, conditions, and even sometimes unions that end up having significantly worse performance. Initially this logic was all correctly implemented using joins without any fragments. I am only now trying to rewrite things in order to save performance. I have tried a lot of different combinations to try to get the query planner to use various indexes but so far using where exists and where not exists is the only strategy that has actually worked. The problem seems to come up a lot with left joins and conditions that exclude some of the resulting null values, along with a few other things. I want to avoid fragment usage wherever possible so I always try to use joins first, but sometimes I am forced to turn to fragments.

Bottom line seems to be that sometimes using exists with a subquery is significantly more performant than equivalent logic implemented with joins, though I can’t say I know exactly why. I’m sure some of it depends on what version of Postgres, but I’ve tried updating to the latest and still run into the same issues.

For instance, this blog explains one case where using exists gives a 20x speedup over the equivalent non-exists SQL: SQL Performance of Join and Where Exists

There are a lot of other situations that arise like that one that are hard to generalize. You just sometimes run into them and need to switch over to using exists.

Have you benchmarked it on your own with newer PSQL? You can also use LATERAL in JOIN to simulate EXISTS with current Ecto.

Hi @Frogglet! We don’t allow interpolation because it can be unsafe and lead to SQL injection attacks.

Have you tried passing the table name as fragment interpolation: fragment(“... FROM ? ....”, ^table_name)?

Hey, Jose, thanks for taking the time to help!

Have you tried passing the table name as fragment interpolation: fragment(“... FROM ? ....”, ^table_name) ?

Yeah, unfortunately that doesn’t seem to work. I get this syntax error: (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "$1"

The sql in question is:

WHERE (exists (
        SELECT 1
        FROM $1
        WHERE 1 = 1))

It doesn’t seem to like using an argument in the FROM clause.