SQL Server Performance

I think I found a bug in ISNULL function

Discussion in 'SQL Server 2008 General Developer Questions' started by koenwuyts, Aug 31, 2009.

  1. koenwuyts New Member

    Hi All,
    We just migrated from SQL 2000 to SQL 2008, and on a specific query I'm getting a strange result, I've already work out the it is related to the index.
    If I outer join 2 tables and use an ISNULL statement in the WHERE clause, and a non clustered index is used that doesn't contain the field in the where clause then the ISNULL function will not work properly.
    Here is a script to create the example:/****** Object: Table [dbo].[tblTMP_KOEN2] Script Date: 08/31/2009 18:50:19 ******/SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE
    TABLE [dbo].[tblTMP_KOEN2]([KMDFLG] [char]
    (1) NOT NULL,[KMDEPO] [char]
    (3) NOT NULL,[KMMONO] [char]
    (7) NOT NULL,[KMCLIE] [char]
    (10) NULL,[KMCLOR] [char]
    (17) NULL,[KMCARR] [char]
    (5) NULL,
    CONSTRAINT [PK_tblTMP_KOEN2] PRIMARY KEY CLUSTERED ([KMDFLG]
    ASC,[KMDEPO]
    ASC,[KMMONO]
    ASC)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY])
    ON [PRIMARY]GO
    SET ANSI_PADDING OFF
    GO
    CREATE
    NONCLUSTERED INDEX [IX_KMDFLG_KMCLIE_KMCLOR] ON [dbo].[tblTMP_KOEN2] ([KMDFLG]
    ASC,[KMCLIE]
    ASC,[KMCLOR]
    ASC)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [SECONDARY]GO/****** Object: Table [dbo].[tblTMP_KOEN1] Script Date: 08/31/2009 18:50:19 ******/SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE
    TABLE [dbo].[tblTMP_KOEN1]([KJDFLG] [char]
    (1) NOT NULL,[KJCLIE] [char]
    (10) NOT NULL,[KJCORF] [char]
    (17) NOT NULL,[KJDEPO] [char]
    (3) NOT NULL,[KJCARR] [char]
    (5) NULL,
    CONSTRAINT [PK_tblTMP_KOEN1] PRIMARY KEY NONCLUSTERED ([KJDFLG]
    ASC,[KJCLIE]
    ASC,[KJCORF]
    ASC,[KJDEPO]
    ASC)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY])
    ON [PRIMARY]GO
    SET ANSI_PADDING OFF
    GO
    CREATE
    CLUSTERED INDEX [IX_KJCORF] ON [dbo].[tblTMP_KOEN1] ([KJCORF]
    ASC)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]GO
    INSERT
    [dbo].[tblTMP_KOEN1] ([KJDFLG], [KJCLIE], [KJCORF], [KJDEPO], [KJCARR]) VALUES (N'0', N'LEXMLON01 ', N'3693081 ', N'TUR', N'YENI ')
    Now run this statement, it should not return a record, but it does.SELECT
    TOP (100) PERCENT dbo.tblTMP_KOEN1.KJCORF, ISNULL(dbo.tblTMP_KOEN2.KMCARR, dbo.tblTMP_KOEN1.KJCARR) AS CarrierFROM
    dbo.tblTMP_KOEN1 LEFT OUTER JOINdbo.tblTMP_KOEN2 WITH (INDEX(IX_KMDFLG_KMCLIE_KMCLOR)) ON dbo.tblTMP_KOEN1.KJDFLG = dbo.tblTMP_KOEN2.KMDFLG AND dbo.tblTMP_KOEN1.KJCLIE = dbo.tblTMP_KOEN2.KMCLIE AND dbo
    .tblTMP_KOEN1.KJCORF = dbo.tblTMP_KOEN2.KMCLORWHERE
    (dbo.tblTMP_KOEN1.KJCORF = '3693081') AND (ISNULL(dbo.tblTMP_KOEN2.KMCARR, dbo.tblTMP_KOEN1.KJCARR) = 'ANNUL')
    Anyone seen this before?
    rgds,
    Koen
  2. FrankKalis Moderator

    Hm, I'm on SQL Server 2005 and here I can't reproduce this error. As one would expect no row is returned. Something looks quite weird in your table and constraint definition, but it's maybe too early here as I don't see it.
    But anyway, this is the place to report such issues: http://www.connect.microsoft.com/
  3. moh_hassan20 New Member

    have a look to:
    http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=341659
    http://support.microsoft.com/kb/912389
  4. Adriaan New Member

    Are you positive you have WHERE, not AND? In 2005, indeed no record is returned with WHERE, but one is returned if I replace WHERE with AND.
  5. koenwuyts New Member

    I definitly have WHERE, not AND in the query, maybe it is only in SQL2008.
    I'll wait and see if someone here on the forum can replicate it on SQL 2008, if yes then I will post on the microsoft site.
    Thanks,
    Koen
  6. Adriaan New Member

    Indeed sounds like the criteria in the WHERE clause are getting evaluated too soon - at the same time as the JOIN predicates.
    What happens if you drop the index hint?
  7. koenwuyts New Member

    I put the index hint in to make sure that he uses this index, the production table will automatically use this index, but in my example with only 1 record he just used the clusterex index.
    When I drop the index hint is works, but I already found out that the index is responsible, I rebuild the index with INCLUDE KMCARR and now it always works, but still it should work without al these changes.
    rgds,
    Koen
  8. Adriaan New Member

    Without the index hint, does the query return any row?
  9. mst New Member

    This (http://support.microsoft.com/kb/912389) link is reminiscent of an issue (http://support.microsoft.com/kb/967983/) we hit a week or 2 back; a query returned 0 rows though data DID exist. Furthermore if you did "select top 49 ..." it worked whereas "select top 50..." (or greater) did not. A quick sniff at the execution plan showed that when it was incorrectly returning 0 rows-- it was using an indexed view. If we used an index hint on the query (preventing use of the IVW) all was well.
    The fix per 967983 is Cumulative update package 3 for SQL Server 2005 Service Pack 3
    ,,,which may or may not also resolve this other issue.
  10. Adriaan New Member

    Good point, but the current post is for SQL 2008, not 2005.
  11. koenwuyts New Member

    On my normal table I don't need the index hint because the execution plan will automatically take this index, but in this example, since there is not data in tblTMPKOEN2 we will not automatically use this index. The problem is cause by using the index.
    When I delete the index the problem does not occure.
    Rgds,
    Koen
  12. FrankKalis Moderator

    I would still consider sending it to the Connect site. Worst thing that can happen is that it is closed. But commonly you get at least a good explanation as to why things happen the way they do.
  13. koenwuyts New Member

    I just noticed that we have not installed SP1, this could probably solve the issue. But I'm on holiday for the next 2 weeks, so it will have to wait until after. I'll keep you posted.
    rgds,
    Koen
  14. ddupuis New Member

    Koen, did you end up resolving this issue? I pretty much have the exact same issue. Tomorrow I am going to try applying SP1 and CU2 and see if that remedies this issue.
  15. satya Moderator

Share This Page