Grouping Error in query despite proper grouping in PSQL

[liambastlhotse]

Elixir version

1.17.2

Database and Version

PostgreSQL 16.4

Ecto Versions

3.12.3

Database Adapter and Versions (postgrex, myxql, etc)

ecto_ltree 0.3.0, postgrex 0.19.1

Current behavior

I have a table containing lowest level values from a tree structure using an LTREE. The query should return the sum of these values for the direct child nodes of the top node. I'm encountering a grouping error when executing the following query using Ecto and EctoLtree. This issue does not occur when running the same query directly in SQL:

level = 2
from(t in "table",
  group_by: subpath(t.path, 0, ^level),
  select: %{
    value: fragment("SUM(?)", t.value),
    path: subpath(t.path, 0, ^level)
  }
)
|> Repo.all()

( I also tried using the subpath part inside a fragment, which leads to the same error fragment("subpath("?, ?, ?"), t.path, 0, ^level))

This results in the error: ** (Postgrex.Error) ERROR 42803 (grouping_error) column "t.path" must appear in the GROUP BY clause or be used in an aggregate function

The same query in plain PSQL will not lead to a grouping error:

SELECT
  SUM(t.value),
  subpath(t.path, 0, 2)
FROM table AS t
GROUP BY subpath(t.path, 0, 2);

It seems the problem arises from how Ecto interprets the query, leading to the following SQL being generated:

SELECT
  SUM(t0."value"),
  SUBPATH(t0."path", 0, $1)
FROM table AS t
GROUP BY SUBPATH(t.path, 0, $2);

Here, Ecto uses different parameters ($1 in the SELECT clause and $2 in the GROUP BY clause), causing PostgreSQL to treat them as different expressions and triggering the grouping error.

Expected behavior

There should be no grouping error. Is there a way to resolve this in Ecto, or are there underlying reasons why this behavior occurs?

Thanks

[greg-rychlewski]

Hi @liambastlhotse ,

Thank you for the report. The way to resolve this in Ecto is to use selected_as. For example you could do

from(t in "table",
  group_by: selected_as(:something),
  select: %{
    value: fragment("SUM(?)", t.value),
    path: selected_as(subpath(t.path, 0, ^level), :something)
  }

[liambastlhotse]

HI @greg-rychlewski,

thanks for the fast response. Yes that solves the issue. Thanks.