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
Comments
Commented by: Alice F. Bird (firebirds) Date: 2000-11-15 08:03 select * from "ChildrenOfItem"(0); 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 Now, coming back to the example: select * from 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 and the plan is: 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 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||'' 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. |
Modified by: @pcisarWorkflow: jira [ 10499 ] => Firebird [ 14799 ] |
Modified by: @pavel-zotovQA Status: No test |
Modified by: @pavel-zotovstatus: Closed [ 6 ] => Closed [ 6 ] QA Status: No test => Done successfully |
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
The text was updated successfully, but these errors were encountered: