Issue Details (XML | Word | Printable)

Key: CORE-475
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Arno Brinkman
Reporter: Alice F. Bird
Votes: 0
Watchers: 0
Operations

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

ORDER BY has no effect

Created: 08/Nov/00 12:00 AM   Updated: 14/Jun/06 09:39 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 1.5.0

Time Tracking:
Not Specified

SF_ID: 221921


 Description  « Hide
SFID: 221921#
Submitted By: nobody

Take the following example of a self-referential table and a sproc that returns the children of a specified item:

create table "ExampleTable" (
code integer not null primary key,
name varchar(100) not null unique,
parent integer,

foreign key (parent) references "ExampleTable"(code)
);

/* "Children" result is not null if this item has it's own children */
set term !! ;
create procedure "ChildrenOfItem"(par integer) returns (code integer,children integer) as
begin
 for select "MainTypes".code, Min("ChildTypes".code) from
 "ExampleTable" "MainTypes" left join "ExampleTable" "ChildTypes" on "MainTypes".code = "ChildTypes".parent
 where "MainTypes".parent = :par or ("MainTypes".parent is null and :par is null)
 group by "MainTypes".code into :code,:children do
  suspend;
end!!
set term ; !!

insert into "ExampleTable" values (0,'A',null);
insert into "ExampleTable" values (1,'AA',0);
insert into "ExampleTable" values (3,'AB',0);
insert into "ExampleTable" values (4,'AC',0);
insert into "ExampleTable" values (2,'AD',0);
insert into "ExampleTable" values (5,'B',null);
insert into "ExampleTable" values (6,'BA',5);
insert into "ExampleTable" values (7,'BB',5);
insert into "ExampleTable" values (8,'BC',5);
insert into "ExampleTable" values (9,'BD',5);
insert into "ExampleTable" values (10,'BE',5);
insert into "ExampleTable" values (11,'BF',5);

select * from "ChildrenOfItem"(0);
/* Gives 1,2,3,4 as you would expect */


select * from "ChildrenOfItem"(0) inner join "ExampleTable" on "ChildrenOfItem".code = "ExampleTable".code
order by name;
/* gives 'AA','AD','AB','AC' even though it is order on name!! Codes are still 1,2,3,4
HOWEVER, in this example, changing it to ORDER BY NAME DESC correctly returns AD,AC,AB,AA

In my real system however, neither ORDER BY NAME or ORDER BY NAME DESC has any effect */

============================

IB 6.01, W2k SP1, IBConsole 319. Dialect 3 database.
johnsparrowuk@yahoo.com

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alice F. Bird added a comment - 14/Jun/06 09:39 AM
Date: 2000-11-15 08:03
Sender: robocop
After doing the steps in the report, I was able to reproduce the case easily. One warning, though: the paragraph

select * from "ChildrenOfItem"(0);
/* Gives 1,2,3,4 as you would expect */

is suspicious. I believe that a GROUP BY is not required to return the results ordered by the field(s) that make up the grouping, so an explicit ORDER BY is needed. I remember I
showed a case to Ann some weeks ago and she told me:
??The existence or absence of an index, primary or not, does
not control the order of rows returned by the engine. Only
an order by guarantees control of that. Distinct and
group by also return rows in ascending order of the key,
but that's an artifact and should not be relied on.??

Now, coming back to the example:

select * from
"ChildrenOfItem"(0) inner join "ExampleTable"
on "ChildrenOfItem".code = "ExampleTable".code
order by name

produces indeed AA, AD, AB, AC in the "name" column. Anybody that thinks this is ordered in ascending way, please do a sanity check. Personally, I don't like to employ table names, specially when they are quoted due to dialect 3, so I rewrote the above construction as:

select * from "ChildrenOfItem"(0) c
inner join "ExampleTable" e on c.code = e.code
order by e.name

and the plan is:
PLAN MERGE (SORT (E ORDER RDB$32),SORT (SORT (JOIN (MainTypes NATURAL,ChildTypes INDEX (RDB$FOREIGN33)))))

Hmmm, my ignorance about plans is worldwide famous, but something stinks here. Is a MERGE operation supposed to answer this query as a final phase? I think that it isn't. So, I polished the query again to strip uninsteresting fields:

select c.code, e.code, e.name
from "ChildrenOfItem"(0) c
inner join "ExampleTable" e on c.code = e.code
order by e.name

and the plan is still the same. We need to coerce the optimizer to produce the final sort on the field that the ORDER BY is demanding, so let's awake the optimizer:

select c.code, e.code, e.name||''
from "ChildrenOfItem"(0) c
inner join "ExampleTable" e on c.code = e.code
order by 3

and bingo! This time, the plan is:

PLAN SORT (MERGE (SORT (E NATURAL),SORT (SORT (JOIN (MainTypes NATURAL,ChildTypes INDEX (RDB$FOREIGN33))))))

Look at the missing SORT clause that has appeared! Now, the results are AA, AB, AC, AD as expected.

How is this bug different from others? Here, the optimizer produces not a suboptimal plan, but a complete wrong plan that can't answer the query. In the first part of bug 122376 (posted by me), the optimizer comes with a credible (although horrible) plan but it returns correct results. However, in the second case of 122376 (posted by Frank), we see again the issue of a credible plan but that produces incorrect results (changing fields to NULL). And bug 117138 makes IB to crash when trying to prepare the given SQL statement.

C.