Submitted by: Andre van Zuydam (andrevanzuydam)

Duplicates CORE3141

When creating specific views with group by, sub select statements return null values. Please run the test scenario below, the problem is not obvious at first until a group by is added to the view.

--drop view view_test_withoutgroup;
--drop view view_test;
--drop table test;
--drop table source;

create table test (
id integer default 0 not null,
variance numeric (10,2),
anumber integer default 0,
primary key (id)
);

commit;

insert into test (id, variance, anumber)
values (1, 100.00, 10);

insert into test (id, variance, anumber)
values (2, 150.00, 10);

insert into test (id, variance, anumber)
values (3, 150.00, 12);

create table source (
id integer default 0 not null,
primary key (id)
);

commit;

insert into source (id) values (10);
insert into source (id) values (12);

commit;

create view view_test (databasename, testvalue, placer)
as
SELECT id, (select sum (variance) from test where anumber = 10), 10
FROM source
group by id;

commit;

create view view_test_withoutgroup (databasename, testvalue, placer)
as
SELECT id, (select sum (variance) from test where anumber = 10), 10
FROM source;

commit;

--select * from view_test_withoutgroup; --this one works
select * from view_test; --this one doesn't

====== Test Details ======

Test case from this ticked has been added into core_3141.fbt