Wrong query on group-by on lookup fields

Self-Host
1

the generated query is:

select
  count("id") as "count", 
  (select 
    CONCAT('S_' ,
      CONCAT(
        CONCAT(
          CONCAT(
            CONCAT(
              (select 
                "__nc_formula0"."Name" 
                from "phfkuelil8tvz45"."Project" as "__nc_formula0" 
                where "__nc_formula0"."id" = "__nc_gb0"."Project_id"
              )
              ,'_' 
            ) as cz1l4zf8id7m6ky,"__nc_gb0"."Name"
          ) as cz1l4zf8id7m6ky,'_' 
        ) as cz1l4zf8id7m6ky,"__nc_gb0"."id" 
      ) as cz1l4zf8id7m6ky
    ) as cz1l4zf8id7m6ky 
   from "phfkuelil8tvz45"."Series" as "__nc_gb0" 
   where "__nc_gb0"."id" = "phfkuelil8tvz45"."Sample"."Series_id"
  ) as "c19jwv38z0ekwnh" 
from "phfkuelil8tvz45"."Sample" 
group by "c19jwv38z0ekwnh" 
order by "c19jwv38z0ekwnh" asc nulls FIRST limit 25

in order to make it work, all as cz1l4zf8id7m6ky, but the last one, should be removed.

Sometimes is works, sometimes not :frowning:

setup info, selfhosted:
Node: v22.14.0
Arch: x64
Platform: linux
Docker: false
RootDB: pg
PackageVersion: 0.262.3

is there anything I can do to help further?

2

Would be helpful to mention or send the screenshot of the problematic base with base info

3

sure, base info is what I posted just before.

when I try “group by this field” in the view:

Capture d’écran 2025-03-21 à 14.13.23
Capture d’écran 2025-03-21 à 14.13.231920×1056 132 KB

I get the “SQL error”

So checking the logs, it lists:

6cd2b5c196e6 2025-03-21T14:13:35.432073334+01:00 2025-03-21 13:13:35.432 UTC [862] ERROR:  syntax error at or near "as" at character 224
6cd2b5c196e6 2025-03-21T14:13:35.432073334+01:00 2025-03-21 13:13:35.432 UTC [862] STATEMENT:  select count("id") as "count", (select CONCAT('S_' ,CONCAT(CONCAT(CONCAT(CONCAT((select "__nc_formula0"."Name"
 from "p13xr3lsk40q0d5"."Project" as "__nc_formula0" where "__nc_formula0"."id" = "__nc_gb0"."Project_id"),'_' ) as cz8ulnihh3xs0ol,"__nc_gb0"."Name") as cz8ulnihh3xs0ol,'_' ) as cz8ulnihh3xs0ol,"__nc_gb0"
."id" ) as cz8ulnihh3xs0ol) as cz8ulnihh3xs0ol from "p13xr3lsk40q0d5"."Series" as "__nc_gb0" where "__nc_gb0"."id" = "p13xr3lsk40q0d5"."Sample"."Series_id") as "c2jgh30z46g9pyr" from "p13xr3lsk40q0d5"."Sam
ple" group by "c2jgh30z46g9pyr" order by "c2jgh30z46g9pyr" asc nulls FIRST limit 25
4

Capture d’écran 2025-03-21 à 14.13.36
Capture d’écran 2025-03-21 à 14.13.362880×1584 232 KB

5

Issue tracked here : 🐛 Bug: Group by on look up fields fails for this usecase · Issue #10950 · nocodb/nocodb · GitHub

6

i see the problem, while replicating the error for you to see it, i created this base:

In the table Samples, you can see two views “bySeries” and “bySeries2” respectively grouped by the linked tables “Series” and “Series copy”.

“bySeries2” works fine, the “display value” field being a formaly using function “concat”.
“bySeries” fails, the display value being a formula using logic operators, which confuses the SQL query generator for “group by”.

I’ll report the details in github too.

7

Thank you for providing the reproducible copy. Can you also share the base publicly and then share the link.

8

here it is:

9

We’ve fixed this bug in PR #10952. The fix will be included in our next release.

To verify the fix, you can use our PR Docker build:

docker run -d -p 8888:8080 nocodb/nocodb-timely:0.262.3-pr-10952-20250321-1711
1 Like