[Firebird-devel] [ firebird-Bugs-225283 ] ORDER BY on a VIEW turns values in fields into NULL

[<no...@so...>]

Bugs item #225283, was opened at 2000-12-10 18:28
You can respond by visiting: 
http://sourceforge.net/tracker/?func=detail&atid=109028&aid=225283&group_id=9028

Category: Core Engine
Group: Confirmed Bug
Status: Open
Resolution: None
Priority: 5
Submitted By: Claudio Valderrama C. (robocop)
>Assigned to: Claudio Valderrama C. (robocop)
Summary: ORDER BY on a VIEW turns values in fields into NULL

Initial Comment:
Hello, I've decoupled this issue from Bug #122376 that I logged. Frank appended this case, but I think it's critical so it deserves its own separate report and priority. If a sort makes some fields with values to appear with NULL instead, I think it's a rather serious issue:

----------Begin of original post-----------
From: Jan Bakuwel <jan...@am...> 
Subject: InterBase 6 - serious bug or "feature" ? 
Date: Sun, 12 Nov 2000 20:52:27 +0100 


Hoi all, 

Perhaps it's my lack of knowledge of the SQL92 standard, however I think 
I ran into a very serious bug in InterBase. Any ideas, hints, etc are 
very welcome (this will stop me from applying InterBase successfully). 

Consider a database with 5 tables and a couple of views, one of those 
views is called v_observation. Consider two queries (you can try them 
with IBConsole): 

select * from v_observation 

select * from v_observation order by iobservationagree desc 

These two queries return different (!) results. And not just the 
order.... It seems InterBase has trouble dealing with NULL values that 
are returned and, in the second case, completely omit the infomation 
retrieved from the database. I have the same database in Access ... and 
everything works perfect (exactly like expected) there! 
You don't believe it ? You can download the database here: 

http://www.greenpeace.org/~jbakuwel/db_linux.zip (gzipped) 
http://www.greenpeace.org/~jbakuwel/db_win.zip (zipped) 

Hope to hear from some of you....if not too much trouble please also 
send you reply to my email address... 

kind regards, 
Jan 
Greenpeace- Information Technology 
------------------End of original post-----------

My comments now, extracted from the answer I did in the NGs:

I have narrowed the case to:
select IOBSERVATIONAGREE
from v_observation
order by 1

The same bug can be seen with:
select distinct IOBSERVATIONAGREE
from v_observation

And you get nicer <g> results with:
select IOBSERVATIONAGREE
from v_observation
group by iobservationagree

So the one that works is simply:
select IOBSERVATIONAGREE
from v_observation

In light of the examples I've posted above, I declare this to be a bug... unless I missed something really obvious. At first glance, the only difference when the ORDER BY clause is put is the SORT that appears in the query plan. Otherwise, the rest of the plan, including the nested JOINs, is the same.

I think IB was too tired to do the sorting after so many joins that it optimized the values towards NULL. <g>

This is the ugly workaround: replace the final view v_observation by a selectable stored procedure that does the same:

set term ^;
create procedure test
returns(IOBSERVATIONID int,
ILEAGUEID int,
IUSERID int,
ICATEGORYID int,
DSUBMITTED timestamp,
MOBSERVATION blob sub_type text,
MQUESTION blob sub_type text,
MSUGGESTION blob sub_type text,
MBACKGROUNDINFO blob sub_type text,
MOFFICIALRESPONSE blob sub_type text,
IOBSERVATIONAGREE int,
IOBSERVATIONNEUTRAL int,
IOBSERVATIONDISAGREE int,
IQUESTIONAGREE int,
IQUESTIONNEUTRAL int,
IQUESTIONDISAGREE int,
ISUGGESTIONAGREE int,
ISUGGESTIONNEUTRAL int,
ISUGGESTIONDISAGREE int,
IWORKGROUPAGREE int,
IWORKGROUPNEUTRAL int,
IWORKGROUPDISAGREE int)
as begin
for SELECT
t_Observation.*,
v_ObservationAgree.iObservationAgree,
v_ObservationNeutral.iObservationNeutral,
v_ObservationDisagree.iObservationDisagree,
v_QuestionAgree.iQuestionAgree, v_QuestionNeutral.iQuestionNeutral,
v_QuestionDisagree.iQuestionDisagree,
v_SuggestionAgree.iSuggestionAgree, v_SuggestionNeutral.iSuggestionNeutral,
v_SuggestionDisagree.iSuggestionDisagree,
v_WorkgroupAgree.iWorkgroupAgree, v_WorkgroupNeutral.iWorkgroupNeutral,
v_WorkgroupDisagree.iWorkgroupDisagree
FROM (((((((((((t_Observation
LEFT JOIN v_ObservationAgree ON t_Observation.iObservationID =
v_ObservationAgree.iObservationID)
LEFT JOIN v_ObservationNeutral ON t_Observation.iObservationID =
v_ObservationNeutral.iObservationID)
LEFT JOIN v_ObservationDisagree ON t_Observation.iObservationID =
v_ObservationDisagree.iObservationID)
LEFT JOIN v_QuestionAgree ON t_Observation.iObservationID =
v_QuestionAgree.iObservationID)
LEFT JOIN v_QuestionNeutral ON t_Observation.iObservationID =
v_QuestionNeutral.iObservationID)
LEFT JOIN v_QuestionDisagree ON t_Observation.iObservationID =
v_QuestionDisagree.iObservationID)
LEFT JOIN v_SuggestionAgree ON t_Observation.iObservationID =
v_SuggestionAgree.iObservationID)
LEFT JOIN v_SuggestionNeutral ON t_Observation.iObservationID =
v_SuggestionNeutral.iObservationID)
LEFT JOIN v_SuggestionDisagree ON t_Observation.iObservationID =
v_SuggestionDisagree.iObservationID)
LEFT JOIN v_WorkgroupAgree ON t_Observation.iObservationID =
v_WorkgroupAgree.iObservationID)
LEFT JOIN v_WorkgroupNeutral ON t_Observation.iObservationID =
v_WorkgroupNeutral.iObservationID)
LEFT JOIN v_WorkgroupDisagree ON t_Observation.iObservationID =
v_WorkgroupDisagree.iObservationID
into :IOBSERVATIONID,
:ILEAGUEID,
:IUSERID,
:ICATEGORYID,
:DSUBMITTED,
:MOBSERVATION,
:MQUESTION,
:MSUGGESTION,
:MBACKGROUNDINFO,
:MOFFICIALRESPONSE,
:IOBSERVATIONAGREE,
:IOBSERVATIONNEUTRAL,
:IOBSERVATIONDISAGREE,
:IQUESTIONAGREE,
:IQUESTIONNEUTRAL,
:IQUESTIONDISAGREE,
:ISUGGESTIONAGREE,
:ISUGGESTIONNEUTRAL,
:ISUGGESTIONDISAGREE,
:IWORKGROUPAGREE,
:IWORKGROUPNEUTRAL,
:IWORKGROUPDISAGREE
do suspend;
end ^;
set term ;^

I still think the parenthesis are redundant, since I tried without them and got (apparently) the same results. After all, you have only LEFT JOINs.

Now,
a) select * from test
b) select * from test
order by iobservationagree desc
c) select * from test
order by iobservationagree asc
d) select distinct iobservationagree from test

seem to be all correct, except that IB always sorts NULLs at the tail. This is a known behavior. The trick is to fool the fooler.

If the db is no longer available at
http://www.greenpeace.org/~jbakuwel/db_win.zip
there will be a copy of it at
http://www.cvalde.com/bugs/db_win.zip
where I include the SP "test" that does work.

C.


----------------------------------------------------------------------

Comment By: Claudio Valderrama C. (robocop)
Date: 2001-07-28 19:25

Message:
Logged In: YES 
user_id=62823

Here's a new example I got from the NGs. As I pointed out 
below, it was impossible to get a contact with the person 
that posted the example. I also marked with one-line 
comments the data that I could skim while still reproducing 
the bug, because one row less means a lot less iterations 
to debug at the jrd level:

From: "Jose Mª Perez" <jm...@ce...>
Subject: strange error with order by clause and left join 
views
Date: Miércoles 11 de Julio de 2001 7:25

Hello,

Interbase server is reporting strange error or results when 
I make a query on a view with a left join and an order by 
clause.

In a real aplication I get the following error (using jdbc):
  'conversion error from string "0"'
Interbase error code: 335544334

I tried to reproduce the error on other conditions and I 
can´t get the error mentioned but strange results on 
queries. 


To reproduce the error I used the following sql DDL:

----------------------------------
create table p1 (
  x_p1       numeric(10,0),
  f_entrada  date
);

create view vp1 (
  x_p1,
  f_entrada
) as 
select x_p1, f_entrada from p1;

create table p2 (
  x_p2       numeric(10,0),
  p1_x_p1    numeric(10,0),
  n_one      numeric(10,0),
  n_two      numeric(10,0)
);

create view vp2 (
  p1_x_p1,
  n_one,
  n_two
) as 
select p1_x_p1, sum(n_one), sum(n_two)
from p2 group by p1_x_p1;

create view vvp1 (
  p1_x_p1,
  f_entrada,
  n_one,
  n_two
) as
select p1.x_p1, p1.f_entrada, p2.n_one, p2.n_two
from vp1 p1 left join vp2 p2 on p1.x_p1=p2.p1_x_p1;


CVC: I've simplified the case leaving out some data.
The lesser the easier to debug.

/* INSERT */
insert into p1 values (1,'07/10/2001');
insert into p1 values (2,'07/13/2001');
insert into p1 values (3,'08/12/2001');
--insert into p1 values (4,'08/10/2001');

insert into p2 values (1,1,0,1);
insert into p2 values (2,2,1,0);
insert into p2 values (3,1,0,1);
--insert into p2 values (4,2,0,1);
--insert into p2 values (5,2,1,1);

------------------------------------------

Where I execute the query: "select * from vvp1;"
the result is ok.

1    10/07/01       0       2
2    13/07/01       2       2
3    12/08/01  <null>  <null>
4    10/08/01  <null>  <null>

[
CVC: With only the six insertions not commented above, you 
get
 1 2001-07-10     0         2
 2 2001-07-13     1         0
 3 2001-08-12     <null>    <null>
]

But if I execute the query: 
"select * from vvp1 order by f_entrada"
the result looks wrong

1    10/07/01  <null>  <null>
2    13/07/01  <null>  <null>
3    12/08/01  <null>  <null>
4    10/08/01  <null>  <null>

Where are the results of sum 0,2,2,2

[
CVC: With only the six insertions not commented above, you 
get
 1 2001-07-10     <null>    <null>
 2 2001-07-13     <null>    <null>
 3 2001-08-12     <null>    <null>

that's wrong anyway.
]

Does anyone know what is wrong? Is this a bug?

Please, reply also to my email: jmperez@cedt.junta-
andalucia.es

Thank you

[
CVC: I tried to contact this user two times and never got 
an answer,
so I posted myself the case that I caught from the NGs.
]



----------------------------------------------------------------------

You can respond by visiting: 
http://sourceforge.net/tracker/?func=detail&atid=109028&aid=225283&group_id=9028