Issue Details (XML | Word | Printable)

Key: CORE-3141
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Adriano dos Santos Fernandes
Reporter: Andre van Zuydam
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

The last column in a view is returning as a null value even when it's not

Created: 21/Sep/10 01:59 PM   Updated: 04/Jun/13 07:59 AM
Component/s: Engine
Affects Version/s: 2.5 RC3
Fix Version/s: 2.5.1

Time Tracking:
Not Specified

Environment: Linux & Windows XP, Firebird 2.5 Release Candidate 3
Issue Links:
Duplicate
 
Relate
 

Planning Status: Unspecified


 Description  « Hide
When selecting values from a view with sub selects the last column is returning a null even though it is not, it is possibly an API problem as the problem is duplicated in PHP compilation for 2.5RC3 and in Flamerobin. I've tested this on Windows 2.5 RC 3 & Linux and the problem replicates on both platforms. The reliability of views is compromised if the values returned are not trust worthy.

--drop table testvalues;

create table testvalues (id integer,
                         datum date,
                         sfield varchar (10),
                         num1 numeric (10,2),
                         num2 numeric (10,2),
                         primary key (id)
                        );
commit;
                         
insert into testvalues values (1, 'now', 'A', 10, 0);
insert into testvalues values (2, 'now', 'B', 0, 10);
insert into testvalues values (3, 'now', 'A', 20, 0);
insert into testvalues values (4, 'now', 'B', 0, 20);

create view test_values
as

select
    t.datum,
    iif ((select sum(num1-num2) from testvalues where sfield = 'A' and datum <= t.datum) is null, 0, (select sum(num1-num2) from testvalues where sfield = 'A' and datum <= t.datum)) A,
    iif ((select sum(num1-num2) from testvalues where sfield = 'A' and datum <= t.datum) is null, 0, (select sum(num1-num2) from testvalues where sfield = 'B' and datum <= t.datum)) B,
    iif ((select sum(num1-num2) from testvalues where sfield = 'A' and datum <= t.datum) is null, 0, (select sum(num1-num2) from testvalues where sfield = 'A' and datum <= t.datum)) A1,
    iif ((select sum(num1-num2) from testvalues where sfield = 'A' and datum <= t.datum) is null, 0, (select sum(num1-num2) from testvalues where sfield = 'B' and datum <= t.datum)) B1
from testvalues t
group by t.datum;

create view test_values2
as
select
    t.datum,
    0 as A,
    0 as B,
    0 as A1,
    10 as B1
from testvalues t
group by t.datum;

commit;

select t.* from test_values t;
select t.* from test_values2 t;

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 24/Sep/10 09:06 AM
It looks like yet another regression introduced after fixing CORE-1245. At least it disappears once the creation of the nod_derived_expr node in cmp.cpp are undone.

Adriano dos Santos Fernandes added a comment - 24/Sep/10 03:28 PM
Looks like this is simple fix. As the view has a group, allStreams should be passed to not ignore streams without dbkeys. But I'll test it more.

--- a/src/jrd/cmp.cpp
+++ b/src/jrd/cmp.cpp
@@ -3892,7 +3892,7 @@ jrd_nod* CMP_pass1(thread_db* tdbb, CompilerScratch* csb, jrd_nod* node)
                                        sub->nod_type != nod_dbkey)
                                {
                                        NodeStack stack;
- expand_view_nodes(tdbb, csb, stream, stack, nod_dbkey, false);
+ expand_view_nodes(tdbb, csb, stream, stack, nod_dbkey, true);
                                        const UCHAR streamCount = (UCHAR) stack.getCount();

                                        if (streamCount)