Possible bug? - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Possible bug?

Author

Topic

jshepler
Yak Posting Veteran

60 Posts

Posted - 2007-04-20 : 14:44:22

I made a ticket system for our support staff that allows to dynamically define schemas of information to track. Tickets are a collection of one or more tracks, tracks are a collection of one or more details, and details are a collection of one or more items.

The values for each item are stored as a varchar, even though some items use the values as numbers. For example, a list of checkboxes is stored internally as a single number where each bit represents a checkbox in the list.

The web app allows the creation of searches which dynamically creates the SQL to return matching tracks. When filtering by an item that is a checkboxlist, the value needs to be converted from varchar to bigint to do the boolean logic on it to see if a specific bit is set. Obviously, since the values for other items could be strings, I need to make sure when doing the converting that only numbers are being converted. Restricting by the itemID for the checkboxlist would (more or less) ensure that.

It was developed and deployed on SQL2000 and worked perfectly. When we moved it to SQL2005, it stopped working, giving the error "Error converting data type varchar to bigint." However, when changing the order of the WHERE clause restrictions, it worked perfectly again.

Knowing that I was going to post here, I wrote up sample tables and data to represent the problem. My sample worked instead of showing the error. That made me think that maybe the actual data was bad. I verified that the values the real query is converting are all numbers.

Below is the orginal query that broke followed by the updated query that does work (change the order of the where clause) followed by the test sample tables/data/query.

Here's the query that gives the error (along with its execution plan):


SELECT  DISTINCT tr.TrackID
FROM    Tracks tr WITH(NOLOCK)
WHERE   EXISTS
        (
            SELECT  1
            FROM    TrackObjects WITH(NOLOCK)
            WHERE   TrackID = tr.TrackID
                    AND DetailID = 6
        )
        AND EXISTS
        (
            SELECT  1
            FROM    TrackObjects tob WITH(NOLOCK)
                    INNER JOIN ObjectValues ov WITH(NOLOCK) ON tob.TrackObjectID = ov.TrackObjectID
            WHERE   tob.TrackID = tr.TrackID
                    AND ov.ItemID = 68
                    AND (CONVERT(bigint, ov.Value) & 16) = 16
        )


StmtText
  |--Stream Aggregate(GROUP BY:([tr].[TrackID]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([tob].[TrackID]))
            |--Filter(WHERE:((CONVERT(bigint,[Tickets].[dbo].[ObjectValues].[Value] as [ov].[Value],0)&(16))=(16) AND [Tickets].[dbo].[ObjectValues].[ItemID] as [ov].[ItemID]=(68)))
            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([ov].[ObjectValueID], [Expr1014]) WITH ORDERED PREFETCH)
            |         |--Nested Loops(Inner Join, OUTER REFERENCES:([tob].[TrackObjectID], [Expr1013]) WITH ORDERED PREFETCH)
            |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Tickets].[dbo].[TrackObjects].[TrackID], [Expr1012]) WITH ORDERED PREFETCH)
            |         |    |    |--Sort(DISTINCT ORDER BY:([Tickets].[dbo].[TrackObjects].[TrackID] ASC))
            |         |    |    |    |--Clustered Index Scan(OBJECT:([Tickets].[dbo].[TrackObjects].[PK_Objects]), WHERE:([Tickets].[dbo].[TrackObjects].[DetailID]=(6)))
            |         |    |    |--Index Seek(OBJECT:([Tickets].[dbo].[TrackObjects].[IX_TrackObjects_TrackID] AS [tob]), SEEK:([tob].[TrackID]=[Tickets].[dbo].[TrackObjects].[TrackID]) ORDERED FORWARD)
            |         |    |--Index Seek(OBJECT:([Tickets].[dbo].[ObjectValues].[IX_ObjectValues_TrackObjectID] AS [ov]), SEEK:([ov].[TrackObjectID]=[Tickets].[dbo].[TrackObjects].[TrackObjectID] as [tob].[TrackObjectID]) ORDERED FORWARD)
            |         |--Clustered Index Seek(OBJECT:([Tickets].[dbo].[ObjectValues].[PK_ObjectValues] AS [ov]), SEEK:([ov].[ObjectValueID]=[Tickets].[dbo].[ObjectValues].[ObjectValueID] as [ov].[ObjectValueID]) LOOKUP ORDERED FORWARD)
            |--Clustered Index Seek(OBJECT:([Tickets].[dbo].[Tracks].[PK_Tracks_TrackID] AS [tr]), SEEK:([tr].[TrackID]=[Tickets].[dbo].[TrackObjects].[TrackID] as [tob].[TrackID]) ORDERED FORWARD)

When swapping the two EXISTS()s, it works perfectly again:


SELECT  DISTINCT tr.TrackID
FROM    Tracks tr WITH(NOLOCK)
WHERE   EXISTS
        (
            SELECT  1
            FROM    TrackObjects tob WITH(NOLOCK)
                    INNER JOIN ObjectValues ov WITH(NOLOCK) ON tob.TrackObjectID = ov.TrackObjectID
            WHERE   tob.TrackID = tr.TrackID
                    AND ov.ItemID = 68
                    AND (CONVERT(bigint, ov.Value) & 16) = 16
        )
        AND EXISTS
        (
            SELECT  1
            FROM    TrackObjects WITH(NOLOCK)
            WHERE   TrackID = tr.TrackID
                    AND DetailID = 6
        )


StmtText
  |--Stream Aggregate(GROUP BY:([tr].[TrackID]))
       |--Filter(WHERE:([Tickets].[dbo].[TrackObjects].[DetailID]=(6)))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([Tickets].[dbo].[TrackObjects].[TrackObjectID]))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([tr].[TrackID]))
                 |    |--Sort(DISTINCT ORDER BY:([tr].[TrackID] ASC))
                 |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tob].[TrackID]))
                 |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([ov].[TrackObjectID]))
                 |    |         |    |--Clustered Index Scan(OBJECT:([Tickets].[dbo].[ObjectValues].[PK_ObjectValues] AS [ov]), WHERE:([Tickets].[dbo].[ObjectValues].[ItemID] as [ov].[ItemID]=(68) AND (CONVERT(bigint,[Tickets].[dbo].[ObjectValues].[Value] as [ov].[Value],0)&(16))=(16)))
                 |    |         |    |--Clustered Index Seek(OBJECT:([Tickets].[dbo].[TrackObjects].[PK_Objects] AS [tob]), SEEK:([tob].[TrackObjectID]=[Tickets].[dbo].[ObjectValues].[TrackObjectID] as [ov].[TrackObjectID]) ORDERED FORWARD)
                 |    |         |--Clustered Index Seek(OBJECT:([Tickets].[dbo].[Tracks].[PK_Tracks_TrackID] AS [tr]), SEEK:([tr].[TrackID]=[Tickets].[dbo].[TrackObjects].[TrackID] as [tob].[TrackID]) ORDERED FORWARD)
                 |    |--Index Seek(OBJECT:([Tickets].[dbo].[TrackObjects].[IX_TrackObjects_TrackID]), SEEK:([Tickets].[dbo].[TrackObjects].[TrackID]=[Tickets].[dbo].[Tracks].[TrackID] as [tr].[TrackID]) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([Tickets].[dbo].[TrackObjects].[PK_Objects]), SEEK:([Tickets].[dbo].[TrackObjects].[TrackObjectID]=[Tickets].[dbo].[TrackObjects].[TrackObjectID]) LOOKUP ORDERED FORWARD)

Here's the test sample tables/data/query I wrote for this post. This is modeled after the first query that generates the error, but this query works.

My thinking is that because the sample is much smaller than the actual data and the sample has no idexes, the execution plan is different and hence the "bug" doesn't come up. I don't know how to read execution plans, so I don't really get anything out of them. I included them here in case they help.


SET NOCOUNT ON
DECLARE @tracks TABLE (trackID int)
DECLARE @trackObjects TABLE (trackObjectID int, trackID int, detailID int)
DECLARE @objectValues TABLE (objectValueID int, trackObjectID int, itemID int, value varchar(50))

INSERT INTO @tracks (trackID)
SELECT 1
UNION SELECT 2
UNION SELECT 3

INSERT INTO @trackObjects (trackObjectID, trackID, detailID)
SELECT  1, 1, 1
UNION SELECT 2, 1, 2
UNION SELECT 3, 2, 1
UNION SELECT 4, 2, 2
UNION SELECT 5, 3, 1
UNION SELECT 6, 3, 2

INSERT INTO @objectValues (objectValueID, trackObjectID, itemID, value)
SELECT 1, 1, 1, 'A'
UNION SELECT 2, 1, 2, '16'
UNION SELECT 3, 2, 3, 'B'
UNION SELECT 4, 2, 4, '2'
UNION SELECT 5, 3, 1, 'C'
UNION SELECT 6, 3, 2, '3'
UNION SELECT 7, 4, 3, 'D'
UNION SELECT 8, 4, 4, '4'
UNION SELECT 9, 5, 1, 'E'
UNION SELECT 10, 5, 2, '5'
UNION SELECT 11, 6, 3, 'F'
UNION SELECT 12, 6, 4, '6'

SELECT  DISTINCT t.trackID
FROM    @tracks t
WHERE   EXISTS
        (
            SELECT  1
            FROM    @trackObjects
            WHERE   trackID = t.trackID
                    AND detailID = 1
        )
        AND EXISTS
        (
            SELECT  1
            FROM    @trackObjects tob
                    INNER JOIN @objectValues ov ON tob.trackObjectID = ov.trackObjectID
            WHERE   tob.trackID = t.trackID
                    AND ov.itemID = 2
                    AND (CONVERT(bigint, ov.value) & 16) = 16
        )


StmtText
  |--Stream Aggregate(GROUP BY:([t].[trackID]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([tob].[trackID]))
            |--Stream Aggregate(GROUP BY:([tob].[trackID]))
            |    |--Nested Loops(Inner Join, WHERE:(@objectValues.[trackObjectID] as [ov].[trackObjectID]=@trackObjects.[trackObjectID] as [tob].[trackObjectID]))
            |         |--Sort(ORDER BY:([tob].[trackID] ASC))
            |         |    |--Table Scan(OBJECT:(@trackObjects AS [tob]))
            |         |--Table Scan(OBJECT:(@objectValues AS [ov]), WHERE:(@objectValues.[itemID] as [ov].[itemID]=(2) AND (CONVERT(bigint,@objectValues.[value] as [ov].[value],0)&(16))=(16)))
            |--Stream Aggregate(GROUP BY:([Bmk1000]) DEFINE:([t].[trackID]=ANY(@tracks.[trackID] as [t].[trackID])))
                 |--Nested Loops(Inner Join)
                      |--Table Scan(OBJECT:(@tracks AS [t]), WHERE:(@trackObjects.[trackID] as [tob].[trackID]=@tracks.[trackID] as [t].[trackID]))
                      |--Table Scan(OBJECT:(@trackObjects), WHERE:([trackID]=@trackObjects.[trackID] as [tob].[trackID] AND [detailID]=(1)))

So, is this some obscure bug in SQL2005?

Since I can't control the order that the users construct their searches, is there a different way I can construct the query? Remember, this is dynamically generated by the search that is constructed by users - adding table joins would be rather difficult.

/jeff

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-20 : 16:04:39

My first thought is that SQL Server might be smart enough to short circut and not do a conversion to BIGINT when the first EXISTS evaluates to false. So, it may be that you have a string that is too big to convert to BIGINT. Unless you already investigated the data, I'd start there.

You might want to verify that all the values for this query can convert to BIGINT. Something like:

SELECT 
	COUNT(*)
FROM
	TrackObjects tob WITH(NOLOCK)
INNER JOIN 
	ObjectValues ov WITH(NOLOCK) 
	ON tob.TrackObjectID = ov.TrackObjectID
WHERE   
     ov.ItemID = 68
     AND LEN(ov.Value) >= 19 -- 19 being the max length of BIGINT (9,223,372,036,854,775,807)

-Ryan

jshepler
Yak Posting Veteran

60 Posts

Posted - 2007-04-20 : 16:22:33

Yes, I verified all rows where itemID = 68 are convertable to bigint.

/jeff

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-20 : 17:25:41

Ahh, It looks like the query that is erroring is converting to big int first:

            |--Filter(WHERE:((CONVERT(bigint,[Tickets].[dbo].[ObjectValues].[Value] as [ov].[Value],0)&(16))=(16) AND [Tickets].[dbo].[ObjectValues].[ItemID] as [ov].[ItemID]=(68)))

Can you move the restriction out of the WHERE clause to the JOIN condition?

Something like:

        AND EXISTS
        (
            SELECT  1
            FROM    TrackObjects tob WITH(NOLOCK)
                    INNER JOIN ObjectValues ov WITH(NOLOCK) ON tob.TrackObjectID = ov.TrackObjectID
                    AND ov.ItemID = 68
            WHERE   tob.TrackID = tr.TrackID
                    AND (CONVERT(bigint, ov.Value) & 16) = 16
        )

That might help the query so that it will only try converting ov.Value after the ItemID has been restricted. Or, possibly, you might have to use a derived table to select only those values there ItemID = 68 before trying to convert.

jshepler
Yak Posting Veteran

60 Posts

Posted - 2007-04-20 : 17:40:05

I changed the query to do that and it still failed.


SELECT  DISTINCT tr.TrackID
FROM    Tracks tr WITH(NOLOCK)
WHERE   EXISTS
        (
            SELECT  1
            FROM    TrackObjects WITH(NOLOCK)
            WHERE   TrackID = tr.TrackID
                    AND DetailID = 6
        )
        AND EXISTS
        (
            SELECT  1
            FROM    TrackObjects tob WITH(NOLOCK)
                    INNER JOIN ObjectValues ov WITH(NOLOCK) ON tob.TrackObjectID = ov.TrackObjectID AND ov.ItemID = 68
            WHERE   tob.TrackID = tr.TrackID
                    AND (CONVERT(bigint, ov.Value) & 16) = 16
        )

StmtText
  |--Stream Aggregate(GROUP BY:([tr].[TrackID]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([tob].[TrackID]))
            |--Filter(WHERE:((CONVERT(bigint,[Tickets].[dbo].[ObjectValues].[Value] as [ov].[Value],0)&(16))=(16) AND [Tickets].[dbo].[ObjectValues].[ItemID] as [ov].[ItemID]=(68)))
            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([ov].[ObjectValueID], [Expr1014]) WITH ORDERED PREFETCH)
            |         |--Nested Loops(Inner Join, OUTER REFERENCES:([tob].[TrackObjectID], [Expr1013]) WITH ORDERED PREFETCH)
            |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Tickets].[dbo].[TrackObjects].[TrackID], [Expr1012]) WITH ORDERED PREFETCH)
            |         |    |    |--Sort(DISTINCT ORDER BY:([Tickets].[dbo].[TrackObjects].[TrackID] ASC))
            |         |    |    |    |--Clustered Index Scan(OBJECT:([Tickets].[dbo].[TrackObjects].[PK_Objects]), WHERE:([Tickets].[dbo].[TrackObjects].[DetailID]=(6)))
            |         |    |    |--Index Seek(OBJECT:([Tickets].[dbo].[TrackObjects].[IX_TrackObjects_TrackID] AS [tob]), SEEK:([tob].[TrackID]=[Tickets].[dbo].[TrackObjects].[TrackID]) ORDERED FORWARD)
            |         |    |--Index Seek(OBJECT:([Tickets].[dbo].[ObjectValues].[IX_ObjectValues_TrackObjectID] AS [ov]), SEEK:([ov].[TrackObjectID]=[Tickets].[dbo].[TrackObjects].[TrackObjectID] as [tob].[TrackObjectID]) ORDERED FORWARD)
            |         |--Clustered Index Seek(OBJECT:([Tickets].[dbo].[ObjectValues].[PK_ObjectValues] AS [ov]), SEEK:([ov].[ObjectValueID]=[Tickets].[dbo].[ObjectValues].[ObjectValueID] as [ov].[ObjectValueID]) LOOKUP ORDERED FORWARD)
            |--Clustered Index Seek(OBJECT:([Tickets].[dbo].[Tracks].[PK_Tracks_TrackID] AS [tr]), SEEK:([tr].[TrackID]=[Tickets].[dbo].[TrackObjects].[TrackID] as [tob].[TrackID]) ORDERED FORWARD)

It still is doing the conversion before evaluting "itemID = 68".

Why is SQL server changing the order of evaluation???

/jeff

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-20 : 17:45:20

quote:
Originally posted by jshepler

I changed the query to do that and it still failed.

Humm, that sucks. The only onther thing that comes to mind is a derived table:

AND EXISTS
        (
            SELECT  1
            FROM    
				(
					SELECT tob.TrackID, ov.Value
					FROM TrackObjects tob WITH(NOLOCK)
                    INNER JOIN ObjectValues ov WITH(NOLOCK) ON tob.TrackObjectID = ov.TrackObjectID
                    WHERE ov.ItemID = 68
				) t
            WHERE   t.TrackID = tr.TrackID
                    AND (CONVERT(bigint, t.Value) & 16) = 16
        )

jshepler
Yak Posting Veteran

60 Posts

Posted - 2007-04-20 : 17:58:39

SQL server is determined to do that conversion first... :


SELECT  DISTINCT tr.TrackID
FROM    Tracks tr WITH(NOLOCK)
WHERE   EXISTS
        (
            SELECT  1
            FROM    TrackObjects WITH(NOLOCK)
            WHERE   TrackID = tr.TrackID
                    AND DetailID = 6
        )
        AND EXISTS
        (
            SELECT  1
            FROM    (
                        SELECT  tob.TrackID, ov.Value
                        FROM    TrackObjects tob WITH(NOLOCK)
                                INNER JOIN ObjectValues ov WITH(NOLOCK) ON tob.TrackObjectID = ov.TrackObjectID
                        WHERE   ov.ItemID = 68
                    ) t
            WHERE   t.TrackID = tr.TrackID
                    AND (CONVERT(bigint, t.Value) & 16) = 16
        )

StmtText
  |--Stream Aggregate(GROUP BY:([tr].[TrackID]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([tob].[TrackID]))
            |--Filter(WHERE:((CONVERT(bigint,[Tickets].[dbo].[ObjectValues].[Value] as [ov].[Value],0)&(16))=(16) AND [Tickets].[dbo].[ObjectValues].[ItemID] as [ov].[ItemID]=(68)))
            |    |--Nested Loops(Inner Join, OUTER REFERENCES:([ov].[ObjectValueID], [Expr1014]) WITH ORDERED PREFETCH)
            |         |--Nested Loops(Inner Join, OUTER REFERENCES:([tob].[TrackObjectID], [Expr1013]) WITH ORDERED PREFETCH)
            |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Tickets].[dbo].[TrackObjects].[TrackID], [Expr1012]) WITH ORDERED PREFETCH)
            |         |    |    |--Sort(DISTINCT ORDER BY:([Tickets].[dbo].[TrackObjects].[TrackID] ASC))
            |         |    |    |    |--Clustered Index Scan(OBJECT:([Tickets].[dbo].[TrackObjects].[PK_Objects]), WHERE:([Tickets].[dbo].[TrackObjects].[DetailID]=(6)))
            |         |    |    |--Index Seek(OBJECT:([Tickets].[dbo].[TrackObjects].[IX_TrackObjects_TrackID] AS [tob]), SEEK:([tob].[TrackID]=[Tickets].[dbo].[TrackObjects].[TrackID]) ORDERED FORWARD)
            |         |    |--Index Seek(OBJECT:([Tickets].[dbo].[ObjectValues].[IX_ObjectValues_TrackObjectID] AS [ov]), SEEK:([ov].[TrackObjectID]=[Tickets].[dbo].[TrackObjects].[TrackObjectID] as [tob].[TrackObjectID]) ORDERED FORWARD)
            |         |--Clustered Index Seek(OBJECT:([Tickets].[dbo].[ObjectValues].[PK_ObjectValues] AS [ov]), SEEK:([ov].[ObjectValueID]=[Tickets].[dbo].[ObjectValues].[ObjectValueID] as [ov].[ObjectValueID]) LOOKUP ORDERED FORWARD)
            |--Clustered Index Seek(OBJECT:([Tickets].[dbo].[Tracks].[PK_Tracks_TrackID] AS [tr]), SEEK:([tr].[TrackID]=[Tickets].[dbo].[TrackObjects].[TrackID] as [tob].[TrackID]) ORDERED FORWARD)

same error...

/jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources