Commented by: @asfernandes

And why you say the bug is in FB 3?

This equivalent query works without error in Oracle:

select t.dummy, (select 1 from dual where t.dummy='1'), count(*)
from dual t
group by t.dummy;

Commented by: Alex Bekhtin (afgm)

> And why you say the bug is in FB 3?

1. I don't know what sql standard says about it
2. I can't explain result of this query, but i think of it as a two virtual steps:
with a as (
select t.rdb$field_name, (select 1 from rdb$database where t.rdb$system_flag=1) as sub_res
from rdb$types t
)
select a.rdb$field_name, a.sub_res, count(*)
from a
group by a.rdb$field_name
3. What about this:
select t.rdb$field_name, (select t.rdb$system_flag /*<<<*/ from rdb$database where t.rdb$system_flag=1), count(*)
from rdb$types t
group by t.rdb$field_name
?

Commented by: @asfernandes

This topic was discussed in fb-devel. If you don't know how it should work, nor want to test others DBMS, search the list.

Commented by: Alex Bekhtin (afgm)

>This topic was discussed in fb-devel.
I can't find this topic. Can give me a link, please?

SQL 2008, SQL 2014 return error for this:

declare @TBL table(fld1 int, fld2 int)

select t.fld1, (select top(1) 1 from @TBL where t.fld2 = 1), count(*)
from @TBL t
group by t.fld1
---------------
Column '@TBL.fld2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

PostgreSQL 9.3

create table test_table (
fld1 int,
fld2 int
)

select t.fld1, (select 1 from test_table where t.fld2 = 1), count(*)
from test_table t
group by t.fld1
---------------
ERROR: subquery uses ungrouped column "t.fld2" from outer query Position: 48

Commented by: @hvlad

Adriano,
your query is correct but is not equivalent to the query in ticket.

In the ticket query field t.rdb$system_flag not present in GROUP BY clause and therefore can't be used in SELECT list as is (only as argument of some aggregation function).
In your query field t.dummy is present in GROUP BY clause and therefore can be used in SELECT list.

I think we have a regression in FB3

Commented by: @asfernandes

Reopening after Vlad's observation.