Weird isnull(constant-column) results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Weird isnull(constant-column) results

Author

Topic

ggross
Starting Member

4 Posts

Posted - 2004-07-30 : 04:48:13

Hi,
First question to the forum. Hope this works... ;)
I'm having different query results in two (supposedly identical) execution enviroments. The problem is equivalent to the one described below (for C&P purposes):

The schema:
-----------
CREATE TABLE [dbo].[A] (
[A_PK_KEY] [numeric](18, 0) NOT NULL ,
[A_FK_MAIN] [numeric](18, 0) NULL ,
[AMOUNT] [numeric](10, 2) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MAIN] (
[MAIN_PK_KEY] [numeric](18, 0) NOT NULL ,
[NAME] [varchar] (50) COLLATE Modern_Spanish_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[A] ADD
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[A_PK_KEY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MAIN] ADD
CONSTRAINT [PK_MAIN] PRIMARY KEY CLUSTERED
(
[MAIN_PK_KEY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[A] ADD
CONSTRAINT [FK_A_MAIN] FOREIGN KEY
(
[A_FK_MAIN]
) REFERENCES [dbo].[MAIN] (
[MAIN_PK_KEY]
)
GO

The data:
---------
INSERT INTO MAIN (MAIN_PK_KEY, NAME)
VALUES (1, 'My name')
go

The query:
----------
SELECT MAIN.NAME,
A_AMOUNT.AMOUNT_TYPE,
ISNULL(A_AMOUNT.AMOUNT_TYPE, 'None') AS TYPE,
A_AMOUNT.AMOUNT
FROM MAIN LEFT OUTER JOIN
(SELECT A_FK_MAIN,
AMOUNT,
'Type A' AS AMOUNT_TYPE
FROM A) A_AMOUNT
ON MAIN.MAIN_PK_KEY = A_AMOUNT.A_FK_MAIN
go

The results:
------------
Machine 1: My name <NULL> None <NULL>
Machine 2: My name <NULL> Type A <NULL>

The comments:
-------------
Note that the above is just a simplified representation of the problem, not the problem itself which is much more complex.

The ideas:
----------
1- Differences in execution plans:
M1: Compute Scalar = isnull([ExprXXX],'None')
M2: Compute Scalar = isnull('Type A','None')
Why? How to prevent M2? Which configuration options I'm missing?

Thanks a lot in advance.
GG

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-30 : 14:57:42

Are you absolutely sure that there are no matching records in table A on machine 2? If there are, even if the other fields are NULL, you could get these results. Your example, if executed fresh, will produce no matching rows in the joined table, and should produce the results on Machine 1.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

ggross
Starting Member

4 Posts

Posted - 2004-08-17 : 05:25:48

Thanks for the reply. Absolutely sure no matching records are found. What do you get in your enviroment?. The results in M1 are the sort of ones I want to have. It seems that M2 is making some kind of non-desired shortcut evaluation with the constant 'Type A'. As the execution plan in M2 appears to indicate, it does not wait what the results are in the left join side to evaluate the isnull.
Anybody gets the results of M2 with the example?
Thanks.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-17 : 06:18:29

I get the same results as M1 on one server with SQL 2000 Service Pack 3a. But another machine which has SQL 2000 (no service pack) returns the same results as M2. I think this is a bug that might have been fixed with SP3. Does M2 have SP3 installed?

OS

ggross
Starting Member

4 Posts

Posted - 2004-08-17 : 06:30:43

Yes, it's supposed to be the same version and same updates. The @@VERSION says:
"Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)".
If you ever upgrade to SP3 the second machine, could you please tell me if the behaviour disappears?. May be a re-installation could be a solution but I must avoid that as long as I haven't got direct access to M2 and it's in the client's data center.
Thank you very much.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-17 : 08:06:34

That is certainly strange. I will keep that in mind, its about time to upgrade the SP on that machine.

OS

ggross
Starting Member

4 Posts

Posted - 2004-08-27 : 03:17:49

Solution found following your indications: reinstalling and applying the SP3a solves the problem.
Thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources