SQL Anywhere ultralite - Problem with ordering in grouped left outer join

Author

Problem with ordering in grouped left outer join

Markus Hofer

2006-02-21, 9:23 am

While debugging a misbehaviour in my application, I found out that there
seems to be a problem with Ultralite concerning ordering. I'm currently
using ASA 9.02 EBF 3228.

I had a query that works exactly as expected. It is an grouped outer join
with an order by. The result of this query is correctly odered by the field
"StartTime". The correct query reads as follows:

select trip.tripid, StartTime from trip left outer join waypoint on
trip.tripid = waypoint.tripid
group by trip.tripid, StartTime
order by StartTime desc

I then needed to add an additional field, called "EndTime" in the select
list. I also had to include it in the group by clause. The ORDER BY clause
is untouched. Nevertheless the ordering changed and the result set is no
longer correctly ordered. It even seems, that the ORDER BY clause does not
have any effect at all.

select Trip.TripId, StartTime, EndTime from Trip left outer join WayPoint on
Trip.TripId = WayPoint.TripId
group by Trip.TripId, StartTime, EndTime
order by StartTime desc

For my understanding this looks like a bug in Ultralite. Ultralite on
PocketPC behaves the same as the Ultralite ISQL on the PC. If needed I can
provide the database and the queries.

Markus Hofer
Smart Solutions AG

Michael Thode

2006-02-21, 11:23 am

Yes, this looks like a bug. Please open a tech support case for this.

Mike

"Markus Hofer" <mh@smartsol.ch> wrote in message
news:43fb2921@forums
-1-dub...
> While debugging a misbehaviour in my application, I found out that there
> seems to be a problem with Ultralite concerning ordering. I'm currently
> using ASA 9.02 EBF 3228.
>
> I had a query that works exactly as expected. It is an grouped outer join
> with an order by. The result of this query is correctly odered by the
field

> "StartTime". The correct query reads as follows:
>
> select trip.tripid, StartTime from trip left outer join waypoint on
> trip.tripid = waypoint.tripid
> group by trip.tripid, StartTime
> order by StartTime desc
>
>
>
> I then needed to add an additional field, called "EndTime" in the select
> list. I also had to include it in the group by clause. The ORDER BY clause
> is untouched. Nevertheless the ordering changed and the result set is no
> longer correctly ordered. It even seems, that the ORDER BY clause does not
> have any effect at all.
>
> select Trip.TripId, StartTime, EndTime from Trip left outer join WayPoint
on
> Trip.TripId = WayPoint.TripId
> group by Trip.TripId, StartTime, EndTime
> order by StartTime desc
>
>
> For my understanding this looks like a bug in Ultralite. Ultralite on
> PocketPC behaves the same as the Ultralite ISQL on the PC. If needed I can
> provide the database and the queries.
>
>
>
> Markus Hofer
> Smart Solutions AG
>
>

Markus Hofer

2006-02-25, 9:41 am

As you suggested, I opened a case for this problem (case 11206079)

Markus

"Michael Thode" < mthode_no_spam@sybas
e.com> wrote in message
news:43fb57c6@forums
-1-dub...
> Yes, this looks like a bug. Please open a tech support case for this.
>
> Mike
>
> "Markus Hofer" <mh@smartsol.ch> wrote in message
> news:43fb2921@forums
-1-dub...
join[color=darkred]
> field
clause[color=darkred
]
not[color=darkred]
WayPoint[color=darkr
ed]
> on
can[color=darkred]
>
>

Sponsored Links