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-issue-importer opened this issue Sep 21, 2010 · 11 comments

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 24, 2010

Modified by: @dyemanov

Component: Engine [ 10000 ]

security: Developers [ 10012 ] =>

Component: API / Client Library [ 10040 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 24, 2010

Modified by: @dyemanov

Link: This issue is related to CORE1245 [ CORE1245 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 24, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 24, 2010

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 24, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 25, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 25, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 5, 2010

Modified by: @dyemanov

Link: This issue is duplicated by CORE3159 [ CORE3159 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 24, 2011

Modified by: @pcisar

Link: This issue is related to QA444 [ QA444 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 4, 2013

Modified by: @pmakowski

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

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jul 19, 2015

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
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants