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

ORDER BY has no effect [CORE475] #821

Closed
firebird-automations opened this issue Nov 8, 2000 · 4 comments
Closed

ORDER BY has no effect [CORE475] #821

firebird-automations opened this issue Nov 8, 2000 · 4 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Alice F. Bird (firebirds)

Assigned to: @ArnoBrinkman

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.
mailto:johnsparrowuk@yahoo.com

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

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 http://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, http://e.name
from "ChildrenOfItem"(0) c
inner join "ExampleTable" e on c.code = e.code
order by http://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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 10499 ] => Firebird [ 14799 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => 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

1 participant