order by problem
{ Here is a problem that I ran into in a 4GL program that had me
going for a bit. It may be of interest to someone.
Bascially in using "order by" on select statement the following
logic is used to find the column(s) to order on:
If table.columnname in select list then use it
else
if columnname in select list then use it
else
generate error.
The problem is this may not give the desired effect as
demonstrated in the following example:
create temp table mastertmp (id integer, val char(1));
create temp table sorttmp (id integer, mastertmp_id integer);
insert into mastertmp values (15, "B");
insert into mastertmp values (21, "D");
insert into mastertmp values (56, "A");
insert into mastertmp values (10, "C");
insert into mastertmp values (32, "E");
insert into sorttmp values (1, 56);
insert into sorttmp values (2, 15);
insert into sorttmp values (3, 10);
insert into sorttmp values (4, 21);
insert into sorttmp values (5, 32);
select m.* from mastertmp m, sorttmp s
where m.id = s.mastertmp_id
order by s.id;
sorttmp.id is accidently left out of the select list.
"sorttmp.id" is the intended order by column, however, because the
column names are identical in the two tables Informix SQL will
choose mastertmp.id to sort on.
According to Tech Support this is not a bug. It is definitely
an inconsistency, in my opinion, because mastertmp.id and
sorttmp.id are two distinct columns and should be treated as such.
This coding error should generate an error but it does not.
--
Michael J. Kuhn Consultant phone:410-254-7060
Email: rhlab!kuhn@uunet.uu.net or uunet!rhlab!kuhn
c/o Baltimore Rh Typing Laboratory, Inc. phone:410-225-9595