Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Cruel bug in ASE15?

11 posts in General Discussion Last posting was on 2009-10-01 03:41:26.0Z
tartampion Posted on 2009-09-29 18:17:35.0Z
Sender: 42b6.4ac119e7.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.general
Subject: Cruel bug in ASE15?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ac24f3f.71b5.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 29 Sep 2009 11:17:35 -0700
X-Trace: forums-1-dub 1254248255 10.22.241.41 (29 Sep 2009 11:17:35 -0700)
X-Original-Trace: 29 Sep 2009 11:17:35 -0700, 10.22.241.41
Lines: 13
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28380
Article PK: 77624

In Adaptive Server Enterprise/15.0.3/EBF 16746 ESD#2
The query
select top 10 * from tb1: bring first 10 records of the
table
While teh following
select top 10 Legalname2 from tb1: bring up 10 rows from the
table randomly.
same bad experience is repeated with set rowcount 10.

Both queries in 12.5 render the correct 10 first records of
the table

is this a bug in ASE 15 or I miss something?


Michael Peppler [Team Sybase] Posted on 2009-09-29 18:30:36.0Z
From: "Michael Peppler [Team Sybase]" <mpeppler@peppler.org>
Organization: Peppler Consulting SARL
Subject: Re: Cruel bug in ASE15?
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2009.09.29.18.30.35.256505@peppler.org>
Newsgroups: sybase.public.ase.general
References: <4ac24f3f.71b5.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 29 Sep 2009 11:30:36 -0700
X-Trace: forums-1-dub 1254249036 10.22.241.152 (29 Sep 2009 11:30:36 -0700)
X-Original-Trace: 29 Sep 2009 11:30:36 -0700, vip152.sybase.com
Lines: 30
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28381
Article PK: 77625


On Tue, 29 Sep 2009 11:17:35 -0700, tartampion wrote:

> In Adaptive Server Enterprise/15.0.3/EBF 16746 ESD#2
> The query
> select top 10 * from tb1: bring first 10 records of the
> table
> While teh following
> select top 10 Legalname2 from tb1: bring up 10 rows from the
> table randomly.
> same bad experience is repeated with set rowcount 10.
>
> Both queries in 12.5 render the correct 10 first records of
> the table
>
> is this a bug in ASE 15 or I miss something?

Define "first" :-) (remember that a table is a set, and hence
fundamentally un-ordered)

So one should never rely on the physical ordering of rows in a table.

I'm guessing that the new optimizer does something fancy in 15 when you
only select one column.

However, although the behavior is weird I wouldn't personally consider
this a bug, as the order of rows is "undefined" unless you include an
ORDER BY clause.

Michael


tartampion Posted on 2009-09-29 20:46:06.0Z
Sender: 724d.4ac251d1.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.general
Subject: Re: Cruel bug in ASE15?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ac2720e.7880.1681692777@sybase.com>
References: <pan.2009.09.29.18.30.35.256505@peppler.org>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 29 Sep 2009 13:46:06 -0700
X-Trace: forums-1-dub 1254257166 10.22.241.41 (29 Sep 2009 13:46:06 -0700)
X-Original-Trace: 29 Sep 2009 13:46:06 -0700, 10.22.241.41
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28385
Article PK: 77628

Agreed on the fancy aspect of the new optimizer, probabaly
you are right the new optimizer does something fancy to
bring rows which are not the first ones, but why? How I
explain the behavior to my users?
I also like the new optimizer for all DMLs. nevertheless;
select on considerable amount(where you combine a few tables
and views) are slower in 15 than previous versions. The DML
are all much faster, new optimizer for DML and transactional
work is outstanding, for queries I am not sure.

tartampion

> On Tue, 29 Sep 2009 11:17:35 -0700, tartampion wrote:
>
> > In Adaptive Server Enterprise/15.0.3/EBF 16746 ESD#2
> > The query
> > select top 10 * from tb1: bring first 10 records of the
> > table
> > While teh following
> > select top 10 Legalname2 from tb1: bring up 10 rows from
> > the table randomly.
> > same bad experience is repeated with set rowcount 10.
> >
> > Both queries in 12.5 render the correct 10 first records
> > of the table
> >
> > is this a bug in ASE 15 or I miss something?
>
> Define "first" :-) (remember that a table is a set, and
> hence fundamentally un-ordered)
>
> So one should never rely on the physical ordering of rows
> in a table.
>
> I'm guessing that the new optimizer does something fancy
> in 15 when you only select one column.
>
> However, although the behavior is weird I wouldn't
> personally consider this a bug, as the order of rows is
> "undefined" unless you include an ORDER BY clause.
>
> Michael
>


"Mark A. Parsons" <iron_horse Posted on 2009-09-29 18:38:13.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cruel bug in ASE15?
References: <4ac24f3f.71b5.1681692777@sybase.com>
In-Reply-To: <4ac24f3f.71b5.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090924-0, 09/24/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac25415$1@forums-1-dub>
Date: 29 Sep 2009 11:38:13 -0700
X-Trace: forums-1-dub 1254249493 10.22.241.152 (29 Sep 2009 11:38:13 -0700)
X-Original-Trace: 29 Sep 2009 11:38:13 -0700, vip152.sybase.com
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28382
Article PK: 77626

If you run the problematic query multiple times do you get the same, or different, results?

Have you ruled out other processes making modifications to the target table while you're running your tests?

Are the 12.5 and 15.0.3 tables the same (ie, same locking scheme, same (non)clustered indexes, same partitioning scheme)?

Have you looked at the query plans for both the 'good' and 'bad' queries for a hint of what's going on?

Do you have a reproducible example?

What's the exact ASE 12.5 version?

tartampion wrote:
> In Adaptive Server Enterprise/15.0.3/EBF 16746 ESD#2
> The query
> select top 10 * from tb1: bring first 10 records of the
> table
> While teh following
> select top 10 Legalname2 from tb1: bring up 10 rows from the
> table randomly.
> same bad experience is repeated with set rowcount 10.
>
> Both queries in 12.5 render the correct 10 first records of
> the table
>
> is this a bug in ASE 15 or I miss something?


tartampion Posted on 2009-09-29 20:37:47.0Z
Sender: 724d.4ac251d1.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.general
Subject: Re: Cruel bug in ASE15?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ac2701b.77fe.1681692777@sybase.com>
References: <4ac25415$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 29 Sep 2009 13:37:47 -0700
X-Trace: forums-1-dub 1254256667 10.22.241.41 (29 Sep 2009 13:37:47 -0700)
X-Original-Trace: 29 Sep 2009 13:37:47 -0700, 10.22.241.41
Lines: 47
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28384
Article PK: 77627


> If you run the problematic query multiple times do you get
> the same, or different, results?
Yes, the same results
>
> Have you ruled out other processes making modifications to
> the target table while you're running your tests?
>
yes of course
> Are the 12.5 and 15.0.3 tables the same (ie, same locking
> scheme, same (non)clustered indexes, same partitioning
> scheme)?

yes, we try to replace 12.5 by 15, afte rload we update the
satts and run all other maintenance job.

> Have you looked at the query plans for both the 'good' and
> 'bad' queries for a hint of what's going on?
Yes,


>
> Do you have a reproducible example?
yes, I am afraid; I can not post my data here. My guess is
anyone can reproduce what I have seen.

>
> What's the exact ASE 12.5 version?
12.5.3

tartampion.

>
> tartampion wrote:
> > In Adaptive Server Enterprise/15.0.3/EBF 16746 ESD#2
> > The query
> > select top 10 * from tb1: bring first 10 records of the
> > table
> > While teh following
> > select top 10 Legalname2 from tb1: bring up 10 rows from
> > the table randomly.
> > same bad experience is repeated with set rowcount 10.
> >
> > Both queries in 12.5 render the correct 10 first records
> > of the table
> >
> > is this a bug in ASE 15 or I miss something?


"Mark A. Parsons" <iron_horse Posted on 2009-09-29 21:17:18.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cruel bug in ASE15?
References: <4ac25415$1@forums-1-dub> <4ac2701b.77fe.1681692777@sybase.com>
In-Reply-To: <4ac2701b.77fe.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090924-0, 09/24/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac2795e$1@forums-1-dub>
Date: 29 Sep 2009 14:17:18 -0700
X-Trace: forums-1-dub 1254259038 10.22.241.152 (29 Sep 2009 14:17:18 -0700)
X-Original-Trace: 29 Sep 2009 14:17:18 -0700, vip152.sybase.com
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28386
Article PK: 77629


tartampion wrote:
>> Have you looked at the query plans for both the 'good' and
>> 'bad' queries for a hint of what's going on?
> Yes,

... and ?

What did the query plans show? Were different indexes being used between 12.5 and 15.0.3? Were there worker threads
pulling results from a partitioned table? Does 15 show a sort step that's not in the 12.5 plan?

How about posting the query plans?

>> Do you have a reproducible example?
> yes, I am afraid; I can not post my data here. My guess is
> anyone can reproduce what I have seen.

By 'reproducible example' I mean an example that others can test on their own. So, what's the example?

I tried the following queries against 12.5.4 and 15.0.3 dataservers and got the same results:

===============================
use tempdb
go
select top 10 * from sysobjects
select top 10 uid from sysobjects
go
===============================

Too simple of an example? Perhaps, but unless you provide some facts and/or examples it's a little hard to diagnose the
issue.

Have you opened a case with Sybase TechSupport?


Sherlock, Kevin [TeamSybase] Posted on 2009-09-29 21:19:31.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4ac25415$1@forums-1-dub> <4ac2701b.77fe.1681692777@sybase.com>
Subject: Re: Cruel bug in ASE15?
Lines: 61
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac279e3$1@forums-1-dub>
Date: 29 Sep 2009 14:19:31 -0700
X-Trace: forums-1-dub 1254259171 10.22.241.152 (29 Sep 2009 14:19:31 -0700)
X-Original-Trace: 29 Sep 2009 14:19:31 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28387
Article PK: 77631

The order of a result set is NEVER guaranteed unless you include an "ORDER
BY" clause period. That was true in 12.x and in every release of ASE prior
to and including 15.

create any covering non-clustered index on a single column, run your select
query which only includes that column, drop the index and try again. Create
the index in descending order, partition the table, etc, etc. Many
physical reasons why one query's result set might be ordered differently
from another.

<tartampion> wrote in message news:4ac2701b.77fe.1681692777@sybase.com...
>
>> If you run the problematic query multiple times do you get
>> the same, or different, results?
> Yes, the same results
>>
>> Have you ruled out other processes making modifications to
>> the target table while you're running your tests?
>>
> yes of course
>> Are the 12.5 and 15.0.3 tables the same (ie, same locking
>> scheme, same (non)clustered indexes, same partitioning
>> scheme)?
>
> yes, we try to replace 12.5 by 15, afte rload we update the
> satts and run all other maintenance job.
>
>> Have you looked at the query plans for both the 'good' and
>> 'bad' queries for a hint of what's going on?
> Yes,
>
>
>>
>> Do you have a reproducible example?
> yes, I am afraid; I can not post my data here. My guess is
> anyone can reproduce what I have seen.
>
>>
>> What's the exact ASE 12.5 version?
> 12.5.3
>
> tartampion.
>
>>
>> tartampion wrote:
>> > In Adaptive Server Enterprise/15.0.3/EBF 16746 ESD#2
>> > The query
>> > select top 10 * from tb1: bring first 10 records of the
>> > table
>> > While teh following
>> > select top 10 Legalname2 from tb1: bring up 10 rows from
>> > the table randomly.
>> > same bad experience is repeated with set rowcount 10.
>> >
>> > Both queries in 12.5 render the correct 10 first records
>> > of the table
>> >
>> > is this a bug in ASE 15 or I miss something?


Jeff Tallman [Sybase] Posted on 2009-09-30 02:54:49.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cruel bug in ASE15?
References: <4ac25415$1@forums-1-dub> <4ac2701b.77fe.1681692777@sybase.com>
In-Reply-To: <4ac2701b.77fe.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac2c879$1@forums-1-dub>
Date: 29 Sep 2009 19:54:49 -0700
X-Trace: forums-1-dub 1254279289 10.22.241.152 (29 Sep 2009 19:54:49 -0700)
X-Original-Trace: 29 Sep 2009 19:54:49 -0700, vip152.sybase.com
Lines: 270
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28389
Article PK: 77632

Works fine for me....I'm afraid we are going to need to know more
details - such as whether or not there is an index on that column (don't
think it makes a diff as it didn't in my examples):

1>
2> use pubstune
1>
2> select @@version





---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Adaptive Server Enterprise/15.0.3/EBF 16738 ESD#2/P/NT (IX86)/Windows
2003/ase1503/2708/32-bit/OPT/Mon Jul 27 20:19:56 2009



(1 row affected)
1> select count(*) from salesdetail

-----------
1350255

(1 row affected)
1> select top 10 *
2> from sales
stor_id ord_num date
------- -------------------- --------------------------
5023 5023-19980605 Jun 5 1998 5:00PM
5023 5023-19980612 Jun 12 1998 5:00PM
5023 5023-19980619 Jun 19 1998 5:00PM
5023 5023-19980626 Jun 26 1998 5:00PM
5023 5023-19980703 Jul 3 1998 5:00PM
5023 5023-19980710 Jul 10 1998 5:00PM
5023 5023-19980717 Jul 17 1998 5:00PM
5023 5023-19980724 Jul 24 1998 5:00PM
5023 5023-19980731 Jul 31 1998 5:00PM
5023 5023-19980807 Aug 7 1998 5:00PM

(10 rows affected)
1>
2> select top 10 stor_id
3> from sales
stor_id
-------
5023
5023
5023
5023
5023
5023
5023
5023
5023
5023

(10 rows affected)
1>
2> select top 10 ord_num
3> from sales
ord_num
--------------------
5023-19980605
5023-19980612
5023-19980619
5023-19980626
5023-19980703
5023-19980710
5023-19980717
5023-19980724
5023-19980731
5023-19980807

(10 rows affected)
1>
2> select top 10 date
3> from sales
date
--------------------------
Jun 5 1998 5:00PM
Jun 12 1998 5:00PM
Jun 19 1998 5:00PM
Jun 26 1998 5:00PM
Jul 3 1998 5:00PM
Jul 10 1998 5:00PM
Jul 17 1998 5:00PM
Jul 24 1998 5:00PM
Jul 31 1998 5:00PM
Aug 7 1998 5:00PM

(10 rows affected)
1>
2> select count(*) from titles

-----------
5018

(1 row affected)
1> select top 10 *
2> from titles
title_id title
type pub_id price
advance total_sales
notes

pubdate
contract
--------
--------------------------------------------------------------------------------
------------ ------ ------------------------ ------------------------
-----------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------- --------
BU1032 The Busy Executive's Database Guide
business 1389 19.99
5,000.00 4095
An overview of available database systems with emphasis on common
business applications. Illustrated.

Jun 12 1986 12:00AM 1
BU1111 Cooking with Computers: Surreptitious Balance Sheets
business 1389 11.95
5,000.00 3876
Helpful hints on how to use your electronic resources to the best
advantage.

Jun 9 1988 12:00AM 1
BU2075 You Can Combat Computer Stress!
business 0736 2.99
10,125.00 15722
The latest medical and psychological techniques for living with the
electronic office. Easy-to-understand explanations.
Jun
30 1985 12:00AM 1
BU7832 Straight Talk About Computers
business 1389 19.99
5,000.00 4095
Annotated analysis of what computers can do for you: a no-hype guide
for the critical user.
Jun
22 1987 12:00AM 1
MC2222 Silicon Valley Gastronomic Treats
mod_cook 0877 19.99
0.00 2032
Favorite recipes for quick, easy, and elegant meals, tried and tested
by people who never have time to eat, let alone cook.
Jun
9 1989 12:00AM 1
MC3021 The Gourmet Microwave
mod_cook 0877 2.99
15,000.00 22246
Traditional French gourmet recipes adapted for modern microwave
cooking.

Jun 18 1985 12:00AM 1
MC3026 The Psychology of Computer Cooking
UNDECIDED 0877 NULL
NULL NULL
NULL

Jul
12 2006 1:22PM 0
PC1035 But Is It User Friendly?
popular_comp 1389 22.95
7,000.00 8780
A survey of software for the naive user, focusing on the
'friendliness' of each.

Jun 30 1986 12:00AM 1
PC8888 Secrets of Silicon Valley
popular_comp 1389 20.00
8,000.00 3795
Muckraking reporting by two courageous womsn on the world's largest
computer hardware and software manufacturers.
Jun
12 1987 12:00AM 1
PC9999 Net Etiquette
popular_comp 1389 NULL
NULL NULL
A must-read for computer conferencing debutantes!

Jul
12 2006 1:22PM 0

(10 rows affected)
1> select top 10 title_id
2> from titles
title_id
--------
BU1032
BU1111
BU2075
BU7832
MC2222
MC3021
MC3026
PC1035
PC8888
PC9999

(10 rows affected)
1> select top 10 total_sales
2> from titles
total_sales
-----------
4095
3876
15722
4095
2032
22246
NULL
8780
3795
NULL

(10 rows affected)

Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

tartampion wrote:
>> If you run the problematic query multiple times do you get
>> the same, or different, results?
> Yes, the same results
>> Have you ruled out other processes making modifications to
>> the target table while you're running your tests?
>>
> yes of course
>> Are the 12.5 and 15.0.3 tables the same (ie, same locking
>> scheme, same (non)clustered indexes, same partitioning
>> scheme)?
>
> yes, we try to replace 12.5 by 15, afte rload we update the
> satts and run all other maintenance job.
>
>> Have you looked at the query plans for both the 'good' and
>> 'bad' queries for a hint of what's going on?
> Yes,
>
>
>> Do you have a reproducible example?
> yes, I am afraid; I can not post my data here. My guess is
> anyone can reproduce what I have seen.
>
>> What's the exact ASE 12.5 version?
> 12.5.3
>
> tartampion.
>
>> tartampion wrote:
>>> In Adaptive Server Enterprise/15.0.3/EBF 16746 ESD#2
>>> The query
>>> select top 10 * from tb1: bring first 10 records of the
>>> table
>>> While teh following
>>> select top 10 Legalname2 from tb1: bring up 10 rows from
>>> the table randomly.
>>> same bad experience is repeated with set rowcount 10.
>>>
>>> Both queries in 12.5 render the correct 10 first records
>>> of the table
>>>
>>> is this a bug in ASE 15 or I miss something?


tartampion Posted on 2009-09-30 14:31:50.0Z
Sender: 724d.4ac251d1.1804289383@sybase.com
From: tartampion
Newsgroups: sybase.public.ase.general
Subject: Re: Cruel bug in ASE15?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ac36bd6.230e.1681692777@sybase.com>
References: <4ac2c879$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 30 Sep 2009 07:31:50 -0700
X-Trace: forums-1-dub 1254321110 10.22.241.41 (30 Sep 2009 07:31:50 -0700)
X-Original-Trace: 30 Sep 2009 07:31:50 -0700, 10.22.241.41
Lines: 310
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28392
Article PK: 77636

Hello Jeff and all others responding to my post,
I agree, in case of a simple query on a table like
sysobjects the results are identical for ASE 12.5 and 15.

I have used the set rowcount 10, or top 10 on a complicated
view where several noclustered indexes are used, indeed in
that case the results are not identical. Perhaps I should
not expect the same outcome from the two version, though it
is hard not to expect the same results.
You see my query plan has got a ROOT:EMIT Operator (VA =
56), it is not as simple as selecting from sysobjects.

I would very much like to post more details about my query,
I shall talk to my client and see if I can do so.
tartampion

> Works fine for me....I'm afraid we are going to need to
> know more details - such as whether or not there is an
> index on that column (don't think it makes a diff as it
> didn't in my examples):
>
> 1>
> 2> use pubstune
> 1>
> 2> select @@version
>
>
>
>
>
> ----------------------------------------------------------
> ----------------------------------------------------------
> ----------------------------------------------------------
> ----------------------------------------------------------
> -----------------------
>
> Adaptive Server Enterprise/15.0.3/EBF 16738 ESD#2/P/NT
> (IX86)/Windows 2003/ase1503/2708/32-bit/OPT/Mon Jul 27
> 20:19:56 2009
>
>
>
> (1 row affected)
> 1> select count(*) from salesdetail
>
> -----------
> 1350255
>
> (1 row affected)
> 1> select top 10 *
> 2> from sales
> stor_id ord_num date
> ------- -------------------- --------------------------
> 5023 5023-19980605 Jun 5 1998 5:00PM
> 5023 5023-19980612 Jun 12 1998 5:00PM
> 5023 5023-19980619 Jun 19 1998 5:00PM
> 5023 5023-19980626 Jun 26 1998 5:00PM
> 5023 5023-19980703 Jul 3 1998 5:00PM
> 5023 5023-19980710 Jul 10 1998 5:00PM
> 5023 5023-19980717 Jul 17 1998 5:00PM
> 5023 5023-19980724 Jul 24 1998 5:00PM
> 5023 5023-19980731 Jul 31 1998 5:00PM
> 5023 5023-19980807 Aug 7 1998 5:00PM
>
> (10 rows affected)
> 1>
> 2> select top 10 stor_id
> 3> from sales
> stor_id
> -------
> 5023
> 5023
> 5023
> 5023
> 5023
> 5023
> 5023
> 5023
> 5023
> 5023
>
> (10 rows affected)
> 1>
> 2> select top 10 ord_num
> 3> from sales
> ord_num
> --------------------
> 5023-19980605
> 5023-19980612
> 5023-19980619
> 5023-19980626
> 5023-19980703
> 5023-19980710
> 5023-19980717
> 5023-19980724
> 5023-19980731
> 5023-19980807
>
> (10 rows affected)
> 1>
> 2> select top 10 date
> 3> from sales
> date
> --------------------------
> Jun 5 1998 5:00PM
> Jun 12 1998 5:00PM
> Jun 19 1998 5:00PM
> Jun 26 1998 5:00PM
> Jul 3 1998 5:00PM
> Jul 10 1998 5:00PM
> Jul 17 1998 5:00PM
> Jul 24 1998 5:00PM
> Jul 31 1998 5:00PM
> Aug 7 1998 5:00PM
>
> (10 rows affected)
> 1>
> 2> select count(*) from titles
>
> -----------
> 5018
>
> (1 row affected)
> 1> select top 10 *
> 2> from titles
> title_id title
> type pub_id price
> advance total_sales
> notes
>
>
> pubdate
> contract
> --------
> ----------------------------------------------------------
> ---------------------- ------------ ------
> ------------------------ ------------------------
> -----------
>
> ----------------------------------------------------------
> ----------------------------------------------------------
> ----------------------------------------------------------
> -------------------------- --------------------------
> --------
> BU1032 The Busy Executive's Database Guide
> business 1389
> 19.99
> 5,000.00 4095
> An overview of available database systems with
> emphasis on common business applications. Illustrated.
>
> Jun 12 1986 12:00AM 1
> BU1111 Cooking with Computers: Surreptitious Balance
> Sheets
> business 1389
> 11.95
> 5,000.00 3876
> Helpful hints on how to use your electronic resources
> to the best advantage.
>
> Jun 9 1988 12:00AM 1
> BU2075 You Can Combat Computer Stress!
> business 0736
> 2.99
> 10,125.00 15722
> The latest medical and psychological techniques for
> living with the electronic office. Easy-to-understand
> explanations.
>
> Jun 30 1985 12:00AM 1
> BU7832 Straight Talk About Computers
> business 1389
> 19.99
> 5,000.00 4095
> Annotated analysis of what computers can do for you:
> a no-hype guide for the critical user.
>
> Jun 22 1987 12:00AM 1
> MC2222 Silicon Valley Gastronomic Treats
> mod_cook 0877
> 19.99
> 0.00 2032
> Favorite recipes for quick, easy, and elegant meals,
> tried and tested by people who never have time to eat,
> let alone cook.
>
> Jun 9 1989 12:00AM 1
> MC3021 The Gourmet Microwave
> mod_cook 0877
> 2.99
> 15,000.00 22246
> Traditional French gourmet recipes adapted for modern
> microwave cooking.
>
> Jun 18 1985 12:00AM 1
> MC3026 The Psychology of Computer Cooking
> UNDECIDED 0877
> NULL
> NULL NULL
> NULL
>
>
> Jul 12 2006 1:22PM 0
> PC1035 But Is It User Friendly?
> popular_comp 1389
> 22.95
> 7,000.00 8780
> A survey of software for the naive user, focusing on
> the 'friendliness' of each.
>
> Jun 30 1986 12:00AM 1
> PC8888 Secrets of Silicon Valley
> popular_comp 1389
> 20.00
> 8,000.00 3795
> Muckraking reporting by two courageous womsn on the
> world's largest computer hardware and software
> manufacturers.
>
> Jun 12 1987 12:00AM 1
> PC9999 Net Etiquette
> popular_comp 1389
> NULL
> NULL NULL
> A must-read for computer conferencing debutantes!
>
>
> Jul 12 2006 1:22PM 0
>
> (10 rows affected)
> 1> select top 10 title_id
> 2> from titles
> title_id
> --------
> BU1032
> BU1111
> BU2075
> BU7832
> MC2222
> MC3021
> MC3026
> PC1035
> PC8888
> PC9999
>
> (10 rows affected)
> 1> select top 10 total_sales
> 2> from titles
> total_sales
> -----------
> 4095
> 3876
> 15722
> 4095
> 2032
> 22246
> NULL
> 8780
> 3795
> NULL
>
> (10 rows affected)
>
> Jeff Tallman
> Enterprise Data Management Products Technical Evangelism
> jeff.tallman@sybase.com
> http://blogs.sybase.com/database
>
> tartampion wrote:
> >> If you run the problematic query multiple times do you
> get >> the same, or different, results?
> > Yes, the same results
> >> Have you ruled out other processes making modifications
> to >> the target table while you're running your tests?
> >>
> > yes of course
> >> Are the 12.5 and 15.0.3 tables the same (ie, same
> locking >> scheme, same (non)clustered indexes, same
> partitioning >> scheme)?
> >
> > yes, we try to replace 12.5 by 15, afte rload we update
> > the satts and run all other maintenance job.
> >
> >> Have you looked at the query plans for both the 'good'
> and >> 'bad' queries for a hint of what's going on?
> > Yes,
> >
> >
> >> Do you have a reproducible example?
> > yes, I am afraid; I can not post my data here. My guess
> > is anyone can reproduce what I have seen.
> >
> >> What's the exact ASE 12.5 version?
> > 12.5.3
> >
> > tartampion.
> >
> >> tartampion wrote:
> >>> In Adaptive Server Enterprise/15.0.3/EBF 16746 ESD#2
> >>> The query
> >>> select top 10 * from tb1: bring first 10 records of
> the >>> table
> >>> While teh following
> >>> select top 10 Legalname2 from tb1: bring up 10 rows
> from >>> the table randomly.
> >>> same bad experience is repeated with set rowcount 10.
> >>>
> >>> Both queries in 12.5 render the correct 10 first
> records >>> of the table
> >>>
> >>> is this a bug in ASE 15 or I miss something?


"Mark A. Parsons" <iron_horse Posted on 2009-09-30 14:50:15.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cruel bug in ASE15?
References: <4ac2c879$1@forums-1-dub> <4ac36bd6.230e.1681692777@sybase.com>
In-Reply-To: <4ac36bd6.230e.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090924-0, 09/24/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac37027$1@forums-1-dub>
Date: 30 Sep 2009 07:50:15 -0700
X-Trace: forums-1-dub 1254322215 10.22.241.152 (30 Sep 2009 07:50:15 -0700)
X-Original-Trace: 30 Sep 2009 07:50:15 -0700, vip152.sybase.com
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28393
Article PK: 77637


tartampion wrote:
> I have used the set rowcount 10, or top 10 on a complicated
> view where several noclustered indexes are used, indeed in
> that case the results are not identical. Perhaps I should
> not expect the same outcome from the two version, though it
> is hard not to expect the same results.
> You see my query plan has got a ROOT:EMIT Operator (VA =
> 56), it is not as simple as selecting from sysobjects.

Without details it sounds like you've probably got different query plans being generated (not uncommon when comparing
12.5.x and 15.x).

With potential differences in table ordering, index selection, join methods, worktable sorting, etc ... and without an
ORDER BY clause ... I'm not surprised that you see different result sets.

If you add the same ORDER BY clause to the 12.5.x and 15.x tests ... do you get the same results? [If not then this
sounds like a bug ... assuming the same data and deterministic query is in use.]

In 15.x, do you get the same results when you use 'select top 10' vs 'set rowcount 10'? [If not then this sounds like a
bug ... assuming the same data and deterministic query is in use.]


Jeff Tallman [Sybase] Posted on 2009-10-01 03:41:26.0Z
From: "Jeff Tallman [Sybase]" <jeff.tallman@sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cruel bug in ASE15?
References: <4ac2c879$1@forums-1-dub> <4ac36bd6.230e.1681692777@sybase.com>
In-Reply-To: <4ac36bd6.230e.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac424e6$1@forums-1-dub>
Date: 30 Sep 2009 20:41:26 -0700
X-Trace: forums-1-dub 1254368486 10.22.241.152 (30 Sep 2009 20:41:26 -0700)
X-Original-Trace: 30 Sep 2009 20:41:26 -0700, vip152.sybase.com
Lines: 348
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28394
Article PK: 77639

Okay - so ....

#1 - We have a view with some number of joins.....very likely, the joins
are using different strategies and therefore you are getting the rows in
just about what ever order the joins are materialized. For example, a
hash join would return in just about any order whereas a merge join
might return the rows in more sorted order (I forget whether this was
your problem or not)....an NLJ would return the rows in the order of the
index traversal from the outer table in the join - which can change
based on optimizer decisions about join order costing...and this may be
plaguing you.

#2 - Since you can't put an order by in the view definition, I think if
you are wanting rows in a particular order, you are going to have to add
the order by to the top 10 query you are issuing against the
view.....since you are after the *first* 10 rows, this gets kinda fun as
there likely is a reason they are after those first 10 rows (an implied
order they didn't tell you??)...you may have to dig with the developers
to find out what the hidden order by clause needs to be.

Can you post the view definition??? What about the output from:

set statistics plancost, resource, io, time on
go
select top 10 ....
from view
go
set statistics plancost, resource, io, time off
go

Sounds like you already have the plancost tree due to the VA=56 comment
(unless that was from set option show output).


Jeff Tallman
Enterprise Data Management Products Technical Evangelism
jeff.tallman@sybase.com
http://blogs.sybase.com/database

tartampion wrote:
> Hello Jeff and all others responding to my post,
> I agree, in case of a simple query on a table like
> sysobjects the results are identical for ASE 12.5 and 15.
>
> I have used the set rowcount 10, or top 10 on a complicated
> view where several noclustered indexes are used, indeed in
> that case the results are not identical. Perhaps I should
> not expect the same outcome from the two version, though it
> is hard not to expect the same results.
> You see my query plan has got a ROOT:EMIT Operator (VA =
> 56), it is not as simple as selecting from sysobjects.
>
> I would very much like to post more details about my query,
> I shall talk to my client and see if I can do so.
> tartampion
>> Works fine for me....I'm afraid we are going to need to
>> know more details - such as whether or not there is an
>> index on that column (don't think it makes a diff as it
>> didn't in my examples):
>>
>> 1>
>> 2> use pubstune
>> 1>
>> 2> select @@version
>>
>>
>>
>>
>>
>> ----------------------------------------------------------
>> ----------------------------------------------------------
>> ----------------------------------------------------------
>> ----------------------------------------------------------
>> -----------------------
>>
>> Adaptive Server Enterprise/15.0.3/EBF 16738 ESD#2/P/NT
>> (IX86)/Windows 2003/ase1503/2708/32-bit/OPT/Mon Jul 27
>> 20:19:56 2009
>>
>>
>>
>> (1 row affected)
>> 1> select count(*) from salesdetail
>>
>> -----------
>> 1350255
>>
>> (1 row affected)
>> 1> select top 10 *
>> 2> from sales
>> stor_id ord_num date
>> ------- -------------------- --------------------------
>> 5023 5023-19980605 Jun 5 1998 5:00PM
>> 5023 5023-19980612 Jun 12 1998 5:00PM
>> 5023 5023-19980619 Jun 19 1998 5:00PM
>> 5023 5023-19980626 Jun 26 1998 5:00PM
>> 5023 5023-19980703 Jul 3 1998 5:00PM
>> 5023 5023-19980710 Jul 10 1998 5:00PM
>> 5023 5023-19980717 Jul 17 1998 5:00PM
>> 5023 5023-19980724 Jul 24 1998 5:00PM
>> 5023 5023-19980731 Jul 31 1998 5:00PM
>> 5023 5023-19980807 Aug 7 1998 5:00PM
>>
>> (10 rows affected)
>> 1>
>> 2> select top 10 stor_id
>> 3> from sales
>> stor_id
>> -------
>> 5023
>> 5023
>> 5023
>> 5023
>> 5023
>> 5023
>> 5023
>> 5023
>> 5023
>> 5023
>>
>> (10 rows affected)
>> 1>
>> 2> select top 10 ord_num
>> 3> from sales
>> ord_num
>> --------------------
>> 5023-19980605
>> 5023-19980612
>> 5023-19980619
>> 5023-19980626
>> 5023-19980703
>> 5023-19980710
>> 5023-19980717
>> 5023-19980724
>> 5023-19980731
>> 5023-19980807
>>
>> (10 rows affected)
>> 1>
>> 2> select top 10 date
>> 3> from sales
>> date
>> --------------------------
>> Jun 5 1998 5:00PM
>> Jun 12 1998 5:00PM
>> Jun 19 1998 5:00PM
>> Jun 26 1998 5:00PM
>> Jul 3 1998 5:00PM
>> Jul 10 1998 5:00PM
>> Jul 17 1998 5:00PM
>> Jul 24 1998 5:00PM
>> Jul 31 1998 5:00PM
>> Aug 7 1998 5:00PM
>>
>> (10 rows affected)
>> 1>
>> 2> select count(*) from titles
>>
>> -----------
>> 5018
>>
>> (1 row affected)
>> 1> select top 10 *
>> 2> from titles
>> title_id title
>> type pub_id price
>> advance total_sales
>> notes
>>
>>
>> pubdate
>> contract
>> --------
>> ----------------------------------------------------------
>> ---------------------- ------------ ------
>> ------------------------ ------------------------
>> -----------
>>
>> ----------------------------------------------------------
>> ----------------------------------------------------------
>> ----------------------------------------------------------
>> -------------------------- --------------------------
>> --------
>> BU1032 The Busy Executive's Database Guide
>> business 1389
>> 19.99
>> 5,000.00 4095
>> An overview of available database systems with
>> emphasis on common business applications. Illustrated.
>>
>> Jun 12 1986 12:00AM 1
>> BU1111 Cooking with Computers: Surreptitious Balance
>> Sheets
>> business 1389
>> 11.95
>> 5,000.00 3876
>> Helpful hints on how to use your electronic resources
>> to the best advantage.
>>
>> Jun 9 1988 12:00AM 1
>> BU2075 You Can Combat Computer Stress!
>> business 0736
>> 2.99
>> 10,125.00 15722
>> The latest medical and psychological techniques for
>> living with the electronic office. Easy-to-understand
>> explanations.
>>
>> Jun 30 1985 12:00AM 1
>> BU7832 Straight Talk About Computers
>> business 1389
>> 19.99
>> 5,000.00 4095
>> Annotated analysis of what computers can do for you:
>> a no-hype guide for the critical user.
>>
>> Jun 22 1987 12:00AM 1
>> MC2222 Silicon Valley Gastronomic Treats
>> mod_cook 0877
>> 19.99
>> 0.00 2032
>> Favorite recipes for quick, easy, and elegant meals,
>> tried and tested by people who never have time to eat,
>> let alone cook.
>>
>> Jun 9 1989 12:00AM 1
>> MC3021 The Gourmet Microwave
>> mod_cook 0877
>> 2.99
>> 15,000.00 22246
>> Traditional French gourmet recipes adapted for modern
>> microwave cooking.
>>
>> Jun 18 1985 12:00AM 1
>> MC3026 The Psychology of Computer Cooking
>> UNDECIDED 0877
>> NULL
>> NULL NULL
>> NULL
>>
>>
>> Jul 12 2006 1:22PM 0
>> PC1035 But Is It User Friendly?
>> popular_comp 1389
>> 22.95
>> 7,000.00 8780
>> A survey of software for the naive user, focusing on
>> the 'friendliness' of each.
>>
>> Jun 30 1986 12:00AM 1
>> PC8888 Secrets of Silicon Valley
>> popular_comp 1389
>> 20.00
>> 8,000.00 3795
>> Muckraking reporting by two courageous womsn on the
>> world's largest computer hardware and software
>> manufacturers.
>>
>> Jun 12 1987 12:00AM 1
>> PC9999 Net Etiquette
>> popular_comp 1389
>> NULL
>> NULL NULL
>> A must-read for computer conferencing debutantes!
>>
>>
>> Jul 12 2006 1:22PM 0
>>
>> (10 rows affected)
>> 1> select top 10 title_id
>> 2> from titles
>> title_id
>> --------
>> BU1032
>> BU1111
>> BU2075
>> BU7832
>> MC2222
>> MC3021
>> MC3026
>> PC1035
>> PC8888
>> PC9999
>>
>> (10 rows affected)
>> 1> select top 10 total_sales
>> 2> from titles
>> total_sales
>> -----------
>> 4095
>> 3876
>> 15722
>> 4095
>> 2032
>> 22246
>> NULL
>> 8780
>> 3795
>> NULL
>>
>> (10 rows affected)
>>
>> Jeff Tallman
>> Enterprise Data Management Products Technical Evangelism
>> jeff.tallman@sybase.com
>> http://blogs.sybase.com/database
>>
>> tartampion wrote:
>>>> If you run the problematic query multiple times do you
>> get >> the same, or different, results?
>>> Yes, the same results
>>>> Have you ruled out other processes making modifications
>> to >> the target table while you're running your tests?
>>> yes of course
>>>> Are the 12.5 and 15.0.3 tables the same (ie, same
>> locking >> scheme, same (non)clustered indexes, same
>> partitioning >> scheme)?
>>> yes, we try to replace 12.5 by 15, afte rload we update
>>> the satts and run all other maintenance job.
>>>
>>>> Have you looked at the query plans for both the 'good'
>> and >> 'bad' queries for a hint of what's going on?
>>> Yes,
>>>
>>>
>>>> Do you have a reproducible example?
>>> yes, I am afraid; I can not post my data here. My guess
>>> is anyone can reproduce what I have seen.
>>>
>>>> What's the exact ASE 12.5 version?
>>> 12.5.3
>>>
>>> tartampion.
>>>
>>>> tartampion wrote:
>>>>> In Adaptive Server Enterprise/15.0.3/EBF 16746 ESD#2
>>>>> The query
>>>>> select top 10 * from tb1: bring first 10 records of
>> the >>> table
>>>>> While teh following
>>>>> select top 10 Legalname2 from tb1: bring up 10 rows
>> from >>> the table randomly.
>>>>> same bad experience is repeated with set rowcount 10.
>>>>>
>>>>> Both queries in 12.5 render the correct 10 first
>> records >>> of the table
>>>>> is this a bug in ASE 15 or I miss something?