Order by woes

[Ivan Smahin]

Hello Mr.,

Wednesday, January 30, 2008, 6:30:13 PM, you wrote:

> Hi List, Ruslan



> This query:
> select
>       objptr_jobticket_holder->user_lastname as 'Sales Agent Lastname',
>       objptr_jobticket_holder->user_Firstname as 'Sales Agent Firstname',
>       objptr_jobticket_holder->user_mailaddress as 'email',
>       objptr_jobticket_holder->user_logonname as 'Logonname',
>       objptr_jobticket_holder->user_password as 'Password',
>       tbl_jobtickets.objptr_cells->cell_name AS 'Cellname',
>       objptr_cells
> from
>     tbl_jobticket_holders inner join tbl_jobtickets on objptr_jobticket = tbl_jobtickets.RecID
> order by
>       objptr_cells


> when in the select the last field 'objptr_cells' is not included the  
> cursor is not ordered as expected (I think he is not ordered at all  
> but not sure).
> Nothing is reported in the console.

Well  -  I  think  it is pretty hard to say about correct order or not
without any field from objptr_cells table selected.

> If ordered by needs the field 'objptr_cells' in order to order (sorry
> could not resist) then it should complain
> If ordered by does not need this field (because it is already  
> mentioned in tbl_jobtickets.objptr_cells) it should order (which it  
> does not)

It was few improvements in the area - so in some cases you can ordered
results  by  not  selected fields now. And you will get an exception in the
rest of cases.

I have tried to reproduce but no luck

CREATE TABLE t1 ( f1 LONG );
INSERT INTO "t1" ("f1") VALUES (1);
INSERT INTO "t1" ("f1") VALUES (5);

CREATE  TABLE  t2  ( ptr OBJECTPTR  CONSTRAINT "l1" REFERENCES "t1" ON
DELETE RESTRICT INDEXED);

INSERT INTO "t2" ("ptr") VALUES (1);
INSERT INTO "t2" ("ptr") VALUES (2);
INSERT INTO "t2" ("ptr") VALUES (NULL);


--
select ptr->f1 from t2 order by ptr desc
--
5
1
NULL

No problem detected.


-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com