Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

The last column in a view is returning as a null value even when it's not [CORE3141] #3518

Closed
firebird-automations opened this issue Sep 21, 2010 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Andre van Zuydam (andrevanzuydam)

Is related to CORE1245
Is duplicated by CORE3159
Is related to QA444

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;

Commits: 1f8342a f7dc73e FirebirdSQL/fbt-repository@b548535

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Component: Engine [ 10000 ]

security: Developers [ 10012 ] =>

Component: API / Client Library [ 10040 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE1245 [ CORE1245 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

It looks like yet another regression introduced after fixing CORE1245. At least it disappears once the creation of the nod_derived_expr node in cmp.cpp are undone.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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\)

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

summary: The last column in a view is returning as a null value even when its not => The last column in a view is returning as a null value even when it's not

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Fix Version: 2.5.1 [ 10333 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE3159 [ CORE3159 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA444 [ QA444 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pmakowski

status: Resolved [ 5 ] => Closed [ 6 ]

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants