Re: Changes from 5.01 to 5.02?
Joe Lumbley (jlumbley@netcom.com) wrote:
> Folks,
> We recently changed from OnLine 5.01.UD(3?) to 5.02.UC6 and one of my
> sites has a report of different behavior in handling an SQL statement.
> This statement returned multiple rows under 5.01 and only one row in 5.02UC6:
> SELECT shift_number, . . .
> FROM xpt_oper,
> operator
> where oper_number = op_id
> group by shift_number . . .
The "group by" statement in 5.02 UC6 is broken per problem #31126 which
we reported in August. I cringe to think that Informix is still shipping
this version of OnLine as "group by" would hardly be considered an obscure
feature in SQL that few will use. 5.03 UC1 does have this fixed. The
problem may or may not be specific to HP hardware. We encountered it
on the 9000/700 platform running HPUX 9.03.
Some sample SQL for the Stores database is attached. If the two selects
in it don't return the same thing both times then this is your problem.
Give your SE a thrashing for not protecting you from this version.
Ken Miles
Vancouver Division
Hewlett-Packard
kenm@vcd.hp.com
{SQL to duplicate bug #31126 in 5.02 UC6 on the stores database:}
database stores;
select distinct
customer.customer_num cus_num,
orders.customer_num ord_cus_num,
orders.order_num
from
customer,orders
where
customer.customer_num = orders.customer_num and
customer.customer_num < 110
order by
orders.customer_num;
{ Now repeat the above with a group by clause added: }
select distinct
customer.customer_num cus_num,
orders.customer_num ord_cus_num,
orders.order_num
from
customer,orders
where
customer.customer_num = orders.customer_num and
customer.customer_num < 110
group by
customer.customer_num, orders.customer_num, orders.order_num
order by
orders.customer_num;