Re: order by problem

}From: rhlab!kuhn (mjk Michael Kuhn) }Subject: order by problem }Date: 21 Oct 93 16:01:33 GMT }X-Informix-List-Id: <news.4689> } }{ 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. Which version of which engine are you using? I took the example SQL direct from your email and ran it against a 5.01.UC2 OnLine on Sun and duly got an error: SQL -309: ORDER BY column (id) must be in SELECT list. This seems correct behaviour to me, and presumably to you as well. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>