[Firebird-devel] [ firebird-Bugs-514186 ] ORDER BY makes fields NULL

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

Bugs item #514186, was opened at 2002-02-07 02:00
You can respond by visiting: 
http://sourceforge.net/tracker/?func=detail&atid=109028&aid=514186&group_id=9028

Category: Core Engine
>Group: Fixed v1.0
>Status: Closed
>Resolution: Fixed
Priority: 5
Submitted By: Sergey Bazilevich (basergey)
Assigned to: Claudio Valderrama C. (robocop)
Summary: ORDER BY makes fields NULL

Initial Comment:
Similar to request 221925: "Left joining table to sproc: ORDER BY 
makes fields NULL"

Bug also present on only one field when left 
joining table to VIEW.

OS: NT4 server SP6a
FB: VI-T6.2.706 
Firebird Release Candidate 2; ODS version 
10.10

Details:

/* Table: ROOT */
CREATE TABLE ROOT (
    
ROOT_ID INTEGER NOT NULL,
    ROOT_TEXT VARCHAR(50));
/* Primary 
keys definition */
ALTER TABLE ROOT ADD PRIMARY KEY 
(ROOT_ID);


/* Table: CHILD_ONE */
CREATE TABLE CHILD_ONE 
(
    CHILD_ID INTEGER NOT NULL,
    ROOT_ID INTEGER);
/* Primary 
keys definition */
ALTER TABLE CHILD_ONE ADD PRIMARY KEY 
(CHILD_ID);


/* Table: CHILD_TWO */
CREATE TABLE 
CHILD_TWO (
    CHILD_ID INTEGER NOT NULL,
    ROOT_ID INTEGER);
/* 
Primary keys definition */
ALTER TABLE CHILD_TWO ADD PRIMARY KEY 
(CHILD_ID);


/* View: ONE_QNT */
CREATE VIEW ONE_QNT(
    
ROOT_ID,
    QNT)
AS
select root_id, count(child_id) as qnt 
from child_one group by root_id;


/* View: TWO_QNT 
*/
CREATE VIEW TWO_QNT(
    ROOT_ID,
    QNT)
AS
select 
root_id, count(child_id) as qnt from child_two group by 
root_id;


/* test data */

insert into ROOT(ROOT_ID, 
ROOT_TEXT) values(1, 'text 1');
insert into ROOT(ROOT_ID, 
ROOT_TEXT) values(2, 'text 2');
insert into ROOT(ROOT_ID, 
ROOT_TEXT) values(3, 'text 3');

insert into 
CHILD_ONE(CHILD_ID, ROOT_ID) values(1, 1);
insert into 
CHILD_ONE(CHILD_ID, ROOT_ID) values(2, 1);
insert into 
CHILD_ONE(CHILD_ID, ROOT_ID) values(3, 1);
insert into 
CHILD_ONE(CHILD_ID, ROOT_ID) values(4, 1);
insert into 
CHILD_ONE(CHILD_ID, ROOT_ID) values(5, 1);
insert into 
CHILD_ONE(CHILD_ID, ROOT_ID) values(6, 1);
insert into 
CHILD_ONE(CHILD_ID, ROOT_ID) values(7, 1);
insert into 
CHILD_ONE(CHILD_ID, ROOT_ID) values(8, 2);
insert into 
CHILD_ONE(CHILD_ID, ROOT_ID) values(9, 2);
insert into 
CHILD_ONE(CHILD_ID, ROOT_ID) values(10, 2);

insert into 
CHILD_TWO(CHILD_ID, ROOT_ID) values(1, 2);
insert into 
CHILD_TWO(CHILD_ID, ROOT_ID) values(2, 2);
insert into 
CHILD_TWO(CHILD_ID, ROOT_ID) values(3, 2);
insert into 
CHILD_TWO(CHILD_ID, ROOT_ID) values(4, 2);
insert into 
CHILD_TWO(CHILD_ID, ROOT_ID) values(5, 2);
insert into 
CHILD_TWO(CHILD_ID, ROOT_ID) values(6, 2);
insert into 
CHILD_TWO(CHILD_ID, ROOT_ID) values(7, 2);
insert into 
CHILD_TWO(CHILD_ID, ROOT_ID) values(8, 3);
insert into 
CHILD_TWO(CHILD_ID, ROOT_ID) values(9, 3);
insert into 
CHILD_TWO(CHILD_ID, ROOT_ID) values(10, 3);


/* test 
queryes */

/* this query returns valid results */
select r.*, 
oq.qnt oq_qnt, tq.qnt tq_qnt
from root r
left join one_qnt oq on 
(r.root_id = oq.root_id)
left join two_qnt tq on (r.root_id = 
tq.root_id)

/*             HERE IS THE BUG                */
/* after adding ORDER BY - 
OQ_QNT ALWAYS NULL */
/* BUT TQ_QNT IS OK                           */
select r.*, oq.qnt 
oq_qnt, tq.qnt tq_qnt
from root r
left join one_qnt oq on 
(r.root_id = oq.root_id)
left join two_qnt tq on (r.root_id = 
tq.root_id)
order by r.root_id

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

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