Order by not working on some joins not working

The following select on a join with order by with ASA 6.0.3 returns all
columns as NULL

SELECT t603.name, t601.type, t601.usertype, t603.length, t603.status,
t603.prec, t603.scale, t602.type
FROM dbo.syscolumns t603, dbo.systypes t601, dbo.sysobjects t602,
dbo.sysusers t604
WHERE t603.id= t602.id
AND t604.uid = t602.uid
AND t602.name = 'someTableName'
AND t602.type IN ('U', 'S', 'V')
AND t603.usertype *=  t601.usertype
AND t604.name = 'dbo'
ORDER BY t603.colid ASC

I saw a case nbr 41041878 related to this problem.  Is is said to be fixed
in 6.02.2317
Is this a different problem?


0
Andrew
4/5/2000 7:20:49 PM
sybase.sqlanywhere.general 32637 articles. 22 followers. Follow

6 Replies
769 Views

Similar Articles

[PageSpeed] 7

Hey, that's interesting.  I also got mostly-null results.  Looks like a bug to me, but I don't know
if it's been fixed in an ebf.  I tested with 6.0.3. #2747.  If someone else could test with a later
build, this statement has all you need.  Try it with and without the ORDER BY.   Be patient - it
runs several minutes on a db that has many tables.

SELECT t603.name, t601.type, t601.usertype, t603.length, t603.status,
t603.prec, t603.scale, t602.type
FROM dbo.syscolumns t603, dbo.systypes t601, dbo.sysobjects t602,
dbo.sysusers t604
WHERE t603.id= t602.id
AND t604.uid = t602.uid
AND t602.name = 'systypes'
AND t602.type IN ('U', 'S', 'V')
AND t603.usertype *=  t601.usertype
AND t604.name = 'dbo'
ORDER BY t603.colid ASC

Leo Tohill - Team Sybase
>> Please post in newsgroup, not via email <<
0
leotohill
4/6/2000 2:10:00 AM
Have you tried writing the query against the base tables instead of 
through the ASE views?
-- 
Jim Egan [TeamSybase]
Houston, TX

Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm
0
Jim
4/6/2000 2:17:10 AM
Leo,
Works fine in ASA7 build 313.
Think it's the *= style outer join?  I've seen those conjure up some bad
magic..  What happens if this is rewritten using ANSI-style outer joins?

Paul Horan

"Leo Tohill" <leotohill@csi.com> wrote in message
news:38ebf18e.1726405@199.93.177.77...
> Hey, that's interesting.  I also got mostly-null results.  Looks like a
bug to me, but I don't know
> if it's been fixed in an ebf.  I tested with 6.0.3. #2747.  If someone
else could test with a later
> build, this statement has all you need.  Try it with and without the ORDER
BY.   Be patient - it
> runs several minutes on a db that has many tables.
>
> SELECT t603.name, t601.type, t601.usertype, t603.length, t603.status,
> t603.prec, t603.scale, t602.type
> FROM dbo.syscolumns t603, dbo.systypes t601, dbo.sysobjects t602,
> dbo.sysusers t604
> WHERE t603.id= t602.id
> AND t604.uid = t602.uid
> AND t602.name = 'systypes'
> AND t602.type IN ('U', 'S', 'V')
> AND t603.usertype *=  t601.usertype
> AND t604.name = 'dbo'
> ORDER BY t603.colid ASC
>
> Leo Tohill - Team Sybase
> >> Please post in newsgroup, not via email <<


0
Paul
4/6/2000 3:00:38 AM
The statement executes correctly against the 6.0.3ebf 2984 (currently
available from the website).

Jason Hinsperger
Product Quality
Adaptive Server Anywhere



Leo Tohill wrote:
> 
> Hey, that's interesting.  I also got mostly-null results.  Looks like a bug to me, but I don't know
> if it's been fixed in an ebf.  I tested with 6.0.3. #2747.  If someone else could test with a later
> build, this statement has all you need.  Try it with and without the ORDER BY.   Be patient - it
> runs several minutes on a db that has many tables.
> 
> SELECT t603.name, t601.type, t601.usertype, t603.length, t603.status,
> t603.prec, t603.scale, t602.type
> FROM dbo.syscolumns t603, dbo.systypes t601, dbo.sysobjects t602,
> dbo.sysusers t604
> WHERE t603.id= t602.id
> AND t604.uid = t602.uid
> AND t602.name = 'systypes'
> AND t602.type IN ('U', 'S', 'V')
> AND t603.usertype *=  t601.usertype
> AND t604.name = 'dbo'
> ORDER BY t603.colid ASC
> 
> Leo Tohill - Team Sybase
> >> Please post in newsgroup, not via email <<
0
Jason
4/6/2000 1:53:25 PM
Where do I find this on the Web Site.  I recently applied maintenance using
this URL. http://download.sybase.com/esd/sqlany_v603_win32.html
This brought me up to 6.0.3 #2747

Do I need a support contract to get to this patch/maintenance release you
are talking about?

Thanks.


"Jason Hinsperger" <nospam@ever.ever> wrote in message
news:38EC96D5.6065B6F@ever.ever...
> The statement executes correctly against the 6.0.3ebf 2984 (currently
> available from the website).
>
> Jason Hinsperger
> Product Quality
> Adaptive Server Anywhere
>
>
>
> Leo Tohill wrote:
> >
> > Hey, that's interesting.  I also got mostly-null results.  Looks like a
bug to me, but I don't know
> > if it's been fixed in an ebf.  I tested with 6.0.3. #2747.  If someone
else could test with a later
> > build, this statement has all you need.  Try it with and without the
ORDER BY.   Be patient - it
> > runs several minutes on a db that has many tables.
> >
> > SELECT t603.name, t601.type, t601.usertype, t603.length, t603.status,
> > t603.prec, t603.scale, t602.type
> > FROM dbo.syscolumns t603, dbo.systypes t601, dbo.sysobjects t602,
> > dbo.sysusers t604
> > WHERE t603.id= t602.id
> > AND t604.uid = t602.uid
> > AND t602.name = 'systypes'
> > AND t602.type IN ('U', 'S', 'V')
> > AND t603.usertype *=  t601.usertype
> > AND t604.name = 'dbo'
> > ORDER BY t603.colid ASC
> >
> > Leo Tohill - Team Sybase
> > >> Please post in newsgroup, not via email <<


0
Andrew
4/7/2000 1:15:05 PM
You don't need a support contract.  Go to this site:
http://sdn.sybase.com/sdn/mec/mec_home.stm
and click the link for "Emergency Bug Fix (EBF)".
This will take you to a logon screen.  If you don't have an SDN id and
password you will have to register.  Registration is free and can be
done right from the logon page.

Jason Hinsperger
Product Quality
Adaptive Server Anywhere


Andrew Gill wrote:
> 
> Where do I find this on the Web Site.  I recently applied maintenance using
> this URL. http://download.sybase.com/esd/sqlany_v603_win32.html
> This brought me up to 6.0.3 #2747
> 
> Do I need a support contract to get to this patch/maintenance release you
> are talking about?
> 
> Thanks.
> 
> "Jason Hinsperger" <nospam@ever.ever> wrote in message
> news:38EC96D5.6065B6F@ever.ever...
> > The statement executes correctly against the 6.0.3ebf 2984 (currently
> > available from the website).
> >
> > Jason Hinsperger
> > Product Quality
> > Adaptive Server Anywhere
> >
> >
> >
> > Leo Tohill wrote:
> > >
> > > Hey, that's interesting.  I also got mostly-null results.  Looks like a
> bug to me, but I don't know
> > > if it's been fixed in an ebf.  I tested with 6.0.3. #2747.  If someone
> else could test with a later
> > > build, this statement has all you need.  Try it with and without the
> ORDER BY.   Be patient - it
> > > runs several minutes on a db that has many tables.
> > >
> > > SELECT t603.name, t601.type, t601.usertype, t603.length, t603.status,
> > > t603.prec, t603.scale, t602.type
> > > FROM dbo.syscolumns t603, dbo.systypes t601, dbo.sysobjects t602,
> > > dbo.sysusers t604
> > > WHERE t603.id= t602.id
> > > AND t604.uid = t602.uid
> > > AND t602.name = 'systypes'
> > > AND t602.type IN ('U', 'S', 'V')
> > > AND t603.usertype *=  t601.usertype
> > > AND t604.name = 'dbo'
> > > ORDER BY t603.colid ASC
> > >
> > > Leo Tohill - Team Sybase
> > > >> Please post in newsgroup, not via email <<
0
Jason
4/7/2000 2:10:29 PM
Reply: