SQL Server Bug: Using scalar functions within a subquery ON Clause

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Jason Thorn (JasonThorn_at_discussions.microsoft.com)
Date: 01/18/05 

Date: Mon, 17 Jan 2005 16:23:02 -0800

/*
I've been working around this bug for a long time now, however I am hoping
the someone at Microsoft might see this and get it fixed in the next Service
Pack. I have not seen it reported in the knowledge base, however it's hard to
know exactly what to search for when investigating these kinds of problems.
Since I am not a Beta-Tester, and I'll be darned if I can find another place
to report bugs on their site, I will post this here and hopefully somone will
take up the torch.
*/

USE Pubs
GO

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'Lookup_MaxSales' and xtype
= 'FN')
   DROP FUNCTION Lookup_MaxSales
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'title_store_sales' and
xtype = 'V')
   DROP VIEW title_store_sales
GO

PRINT 'Creating function ...'
GO
CREATE FUNCTION
   Lookup_MaxSales
(
   @title_id varchar(6)
) RETURNS char(4) AS
BEGIN
   DECLARE @stor_id int

   SELECT TOP 1
      @stor_id = stor_id
   FROM
      sales
   WHERE
      title_id = @title_id

   RETURN @stor_id
END
GO

PRINT 'Attempting to use the function is a sub-select ... (yields failure)'
GO
SELECT
   A.*
FROM
   (
      SELECT
         s.*
      FROM
         titles t
         JOIN stores s ON
            s.stor_id = dbo.Lookup_MaxSales(t.title_id)
   ) A
GO

PRINT 'Creating a surrogate view to use in the sub-query ...'
GO
CREATE VIEW
   title_store_sales
AS
SELECT
   dbo.Lookup_MaxSales(t.title_id) stor_id,
   T.*
FROM
   titles t
GO

PRINT 'Retrying the query with a surrogate view ... (success)'
GO
SELECT
   A.*
FROM
   (
      SELECT
         s.*
      FROM
         title_store_sales t
         JOIN stores s ON
            s.stor_id = t.stor_id
   ) A
GO

PRINT 'Cleaning up the mess ...'
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'Lookup_MaxSales' and xtype
= 'FN')
   DROP FUNCTION Lookup_MaxSales
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'title_store_sales' and
xtype = 'V')
   DROP VIEW title_store_sales
GO

Relevant Pages