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.

MAX() returning NULL with no NULL data

6 posts in Windows NT Last posting was on 1998-02-13 16:36:19.0Z
Vince Posted on 1998-02-05 00:48:49.0Z
From: "Vince" <verceg@intekinfo.com>
Subject: MAX() returning NULL with no NULL data
Date: Wed, 4 Feb 1998 16:48:49 -0800
Lines: 30
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <ZBHhPbcM9GA.206@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5239
Article PK: 1081316

This line of code

SELECT MAX(date_id) FROM vw_report_data

has been selecting data just fine for the past 3 weeks. Yesterday it
started returning NULL (and it returns it immediately -- almost "too
fast."). The tables on which it is based do not contain NULLs anywhere nor
am I introducing them with an outer join or any other way I can think of.
Interestingly, this line returns what I am expecting (e.g., a value not
NULL):

SELECT MAX(date_id) FROM vw_report_data WHERE 1=1

I have tried dropping and recreating the indexes and dropping and recreating
the view. Neither has worked. DBCC does not indicate anything wrong.

Finally, the line SELECT DISTINCT date_id FROM vw_report_data ORDER BY
date_id shows no NULL values.

This has been opened as a Sybase technical issue, but I'd sure like to know
if anyone else can point me somewhere or has some advice...

I am running Sybase System 11.0.2 on NT 4.

Vince Erceg
Intek Information Inc.
San Diego, CA


Bret Halford Posted on 1998-02-05 16:25:01.0Z
Message-ID: <34D9E7DD.4B41@sybase.com>
Date: Thu, 05 Feb 1998 09:25:01 -0700
From: Bret Halford <bret@sybase.com>
Organization: Customer Service & Support
X-Mailer: Mozilla 3.0 (X11; U; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
To: Vince <verceg@intekinfo.com>
Subject: Re: MAX() returning NULL with no NULL data
References: <ZBHhPbcM9GA.206@forums.powersoft.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 38
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5233
Article PK: 1081312


Vince wrote:
>
> This line of code
>
> SELECT MAX(date_id) FROM vw_report_data
>
> has been selecting data just fine for the past 3 weeks. Yesterday it
> started returning NULL (and it returns it immediately -- almost "too
> fast."). The tables on which it is based do not contain NULLs anywhere nor
> am I introducing them with an outer join or any other way I can think of.
> Interestingly, this line returns what I am expecting (e.g., a value not
> NULL):
>
> SELECT MAX(date_id) FROM vw_report_data WHERE 1=1
>
> I have tried dropping and recreating the indexes and dropping and recreating
> the view. Neither has worked. DBCC does not indicate anything wrong.
>
> Finally, the line SELECT DISTINCT date_id FROM vw_report_data ORDER BY
> date_id shows no NULL values.
>
> This has been opened as a Sybase technical issue, but I'd sure like to know
> if anyone else can point me somewhere or has some advice...
>
> I am running Sybase System 11.0.2 on NT 4.
>

Hi Vince,

I haven't heard of this problem before. To get a bit more data,
could you run SET SHOWPLAN ON and then run

SELECT MAX(date_id) FROM vw_report_data
go
SELECT MAX(date_id) FROM vw_report_data where 1=1
go

And see if there is a difference in index usage or other differences
between the query plan?
--
Bret Halford Imagine my disappointment
Sybase Technical Support in learning the true nature
3665 Discovery Drive of rec.humor.oracle...
Boulder, CO 80303


Vince Posted on 1998-02-05 19:57:29.0Z
From: "Vince" <verceg@intekinfo.com>
References: <ZBHhPbcM9GA.206@forums.powersoft.com> <34D9E7DD.4B41@sybase.com>
Subject: Re: MAX() returning NULL with no NULL data
Date: Thu, 5 Feb 1998 11:57:29 -0800
Lines: 81
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <S7crFdmM9GA.316@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5232
Article PK: 1081310

The PLANs for the querys definitely are different. As I thought, the WHERE
1=1 clause is causing the optimizer to not consider any indexes. The
"regular" query is using an index available on this column. Incidentally,
this column was indexed unique, non-clustered with IGNORE_DUP_KEY.

I didn't mention it before, but I am selecting from a view (and a simple
one, at that):

create view vw_report_data
select
ra.*
from
report_app ra,
maps_report_list mrl
where
ra.list_id = mrl.list_id
and ra.key_enterprise_system = mrl.key_enterprise_system
and mrl.list_name NOT LIKE '%TEST%'

No outer joins... No NULL values in the joining columns, nor in the date_id
column I'm looking for (in report_app)... I'm stumped.

As I said before, our DBA has opened/will open an official tech support
issue to resolve this. I'm still open to trying anything, though, in the
meantime.

-- Vince

Bret Halford wrote in message <34D9E7DD.4B41@sybase.com>...
>Vince wrote:
>>
>> This line of code
>>
>> SELECT MAX(date_id) FROM vw_report_data
>>
>> has been selecting data just fine for the past 3 weeks. Yesterday it
>> started returning NULL (and it returns it immediately -- almost "too
>> fast."). The tables on which it is based do not contain NULLs anywhere
nor
>> am I introducing them with an outer join or any other way I can think of.
>> Interestingly, this line returns what I am expecting (e.g., a value not
>> NULL):
>>
>> SELECT MAX(date_id) FROM vw_report_data WHERE 1=1
>>
>> I have tried dropping and recreating the indexes and dropping and
recreating
>> the view. Neither has worked. DBCC does not indicate anything wrong.
>>
>> Finally, the line SELECT DISTINCT date_id FROM vw_report_data ORDER BY
>> date_id shows no NULL values.
>>
>> This has been opened as a Sybase technical issue, but I'd sure like to
know
>> if anyone else can point me somewhere or has some advice...
>>
>> I am running Sybase System 11.0.2 on NT 4.
>>
>
>
>Hi Vince,
>
>I haven't heard of this problem before. To get a bit more data,
>could you run SET SHOWPLAN ON and then run
>
>SELECT MAX(date_id) FROM vw_report_data
>go
>SELECT MAX(date_id) FROM vw_report_data where 1=1
>go
>
>And see if there is a difference in index usage or other differences
>between the query plan?
>--
>Bret Halford Imagine my disappointment
>Sybase Technical Support in learning the true nature
>3665 Discovery Drive of rec.humor.oracle...
>Boulder, CO 80303


Mark A. Parsons Posted on 1998-02-12 00:14:51.0Z
Message-ID: <34E23EFB.6D20@compuserve.com>
Date: Wed, 11 Feb 1998 19:14:51 -0500
From: "Mark A. Parsons" <Iron_Horse@compuserve.com>
Reply-To: Iron_Horse@compuserve.com
Organization: Iron Horse, Inc.
X-Mailer: Mozilla 3.01 (Win95; I)
MIME-Version: 1.0
Subject: Re: MAX() returning NULL with no NULL data
References: <ZBHhPbcM9GA.206@forums.powersoft.com> <34D9E7DD.4B41@sybase.com> <S7crFdmM9GA.316@forums.powersoft.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 26
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5207
Article PK: 1081287


Vince wrote:
>
> The PLANs for the querys definitely are different. As I thought, the WHERE
> 1=1 clause is causing the optimizer to not consider any indexes. The
> "regular" query is using an index available on this column. Incidentally,
> this column was indexed unique, non-clustered with IGNORE_DUP_KEY.
>
> I didn't mention it before, but I am selecting from a view (and a simple
> one, at that):
>
> create view vw_report_data
> select
> ra.*
> from
> report_app ra,
> maps_report_list mrl
> where
> ra.list_id = mrl.list_id
> and ra.key_enterprise_system = mrl.key_enterprise_system
> and mrl.list_name NOT LIKE '%TEST%'
>
>........
>

Just curious ... what do you get if you run:

select * from vw_report_data

I'm wondering if there's actually any data to match your query? No data
===> NULL results for the max() function call. (duh, Mark!?!?!)

--

Mark Parsons
Iron Horse, Inc.
[Team Sybase]


Vince Posted on 1998-02-13 16:36:19.0Z
From: "Vince" <verceg@intekinfo.com>
References: <ZBHhPbcM9GA.206@forums.powersoft.com> <34D9E7DD.4B41@sybase.com> <S7crFdmM9GA.316@forums.powersoft.com> <34E23EFB.6D20@compuserve.com>
Subject: Re: MAX() returning NULL with no NULL data
Date: Fri, 13 Feb 1998 08:36:19 -0800
Lines: 34
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <WkYBBSJO9GA.205@forums.powersoft.com>
Newsgroups: sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5196
Article PK: 1081275

Mark,

I get plenty of data running the query SELECT * from vw_report_data which
makes this all the more troubling.

Over the past two days, I had the opportunity to try all of this out on a
new Adaptive Server 11.5 box we just brought up. Unfortunately, my original
demon-query (SELECT max(date_id) from vw_report_data) never returns any
results -- it literally runs and runs and runs... (it used to return in
under a second).

The entire purpose of the view was to filter out unwanted "test" data. What
I ended up doing was just deleting the test data from the table and killing
the view. It cures only the symptom and not the problem, I know, but at
least things are "normal" again. My DBA has told me he opened a call with
Sybase support, so I'm just gonna sit back and put out other fires :)

-- Vince

>Just curious ... what do you get if you run:
>
> select * from vw_report_data
>
>I'm wondering if there's actually any data to match your query? No data
>===> NULL results for the max() function call. (duh, Mark!?!?!)
>
>--
>
>Mark Parsons
>Iron Horse, Inc.
>[Team Sybase]


John McVicker Posted on 1998-02-07 04:27:40.0Z
Message-ID: <34DBE2BC.5E714209@sybase.com>
Date: Fri, 06 Feb 1998 23:27:40 -0500
From: John McVicker <mcvicker@sybase.com>
Reply-To: mcvicker@sybase.com
Organization: Sybase Professional Services
X-Mailer: Mozilla 4.04 [en] (Win95; U)
MIME-Version: 1.0
To: Vince <verceg@intekinfo.com>
Subject: Re: MAX() returning NULL with no NULL data
References: <ZBHhPbcM9GA.206@forums.powersoft.com> <34D9E7DD.4B41@sybase.com> <S7crFdmM9GA.316@forums.powersoft.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 94
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.sqlserver.nt:5229
Article PK: 1081308

Vince,
Try doing an sp_spaceused on the tables from the View. It may be
that one of the tables show 0 rows in them, and the View may think
that no data will be found since the equijoin exists in the View. You
could try to UPDATE STATISTICS on the two tables and then
sp_recompile the two tables. That should possibly change the
processing plan of the View.

I have seen this type of bug back in 4.9.2. If the above item
doesn't fix things - definitely open a Tech Support case. Have them
track down cases with similar issues on 11.0.2 - also, is it possible
for you to upgrade to 11.0.3 or higher for this? That may be
a suggestion that the Tech Support people give you.

Vince wrote:

> The PLANs for the querys definitely are different. As I thought, the WHERE
> 1=1 clause is causing the optimizer to not consider any indexes. The
> "regular" query is using an index available on this column. Incidentally,
> this column was indexed unique, non-clustered with IGNORE_DUP_KEY.
>
> I didn't mention it before, but I am selecting from a view (and a simple
> one, at that):
>
> create view vw_report_data
> select
> ra.*
> from
> report_app ra,
> maps_report_list mrl
> where
> ra.list_id = mrl.list_id
> and ra.key_enterprise_system = mrl.key_enterprise_system
> and mrl.list_name NOT LIKE '%TEST%'
>
> No outer joins... No NULL values in the joining columns, nor in the date_id
> column I'm looking for (in report_app)... I'm stumped.
>
> As I said before, our DBA has opened/will open an official tech support
> issue to resolve this. I'm still open to trying anything, though, in the
> meantime.
>
> -- Vince
>
> Bret Halford wrote in message <34D9E7DD.4B41@sybase.com>...
> >Vince wrote:
> >>
> >> This line of code
> >>
> >> SELECT MAX(date_id) FROM vw_report_data
> >>
> >> has been selecting data just fine for the past 3 weeks. Yesterday it
> >> started returning NULL (and it returns it immediately -- almost "too
> >> fast."). The tables on which it is based do not contain NULLs anywhere
> nor
> >> am I introducing them with an outer join or any other way I can think of.
> >> Interestingly, this line returns what I am expecting (e.g., a value not
> >> NULL):
> >>
> >> SELECT MAX(date_id) FROM vw_report_data WHERE 1=1
> >>
> >> I have tried dropping and recreating the indexes and dropping and
> recreating
> >> the view. Neither has worked. DBCC does not indicate anything wrong.
> >>
> >> Finally, the line SELECT DISTINCT date_id FROM vw_report_data ORDER BY
> >> date_id shows no NULL values.
> >>
> >> This has been opened as a Sybase technical issue, but I'd sure like to
> know
> >> if anyone else can point me somewhere or has some advice...
> >>
> >> I am running Sybase System 11.0.2 on NT 4.
> >>
> >
> >
> >Hi Vince,
> >
> >I haven't heard of this problem before. To get a bit more data,
> >could you run SET SHOWPLAN ON and then run
> >
> >SELECT MAX(date_id) FROM vw_report_data
> >go
> >SELECT MAX(date_id) FROM vw_report_data where 1=1
> >go
> >
> >And see if there is a difference in index usage or other differences
> >between the query plan?
> >--
> >Bret Halford Imagine my disappointment
> >Sybase Technical Support in learning the true nature
> >3665 Discovery Drive of rec.humor.oracle...
> >Boulder, CO 80303

--
John McVicker
Principal Consultant, District Lead Architect
Sybase Professional Services
Pennsylvania/New Jersey District
301-896-1765
mcvicker@sybase.com, mcvicker@bellatlantic.net, JohnMcVicker@compuserve.com

http://www.sybase.com
http://www.powersoft.com
http://www.isug.com