June 27, 2012 at 3:35 pm
The title is a bit misleading, it is applying the view where clause but after it does a compute scalar which is causing the error.
We have a table with a hundred generic columns that we use to store a variety of data that has different meta-data.
Each different type of data is identified with a special code.
We have views over this table to pull out data based on that code. We then use these views like we would a table.
just recently I started getting an error within an ETL job that runs a query regarding geography data. The error is indicating that the latitude was not between -90.00 and 90.00. Well that simply is not true, I checked all 250k rows and the data is fine, (the data I see in the view)
After many hours I discovered that within the execution plan we are not simply "reading from the view" it's reading from the base table (without a predicate) and grabbing rows that are not part of the view, then it's trying to apply a geography function to this data. Since this column also contains numbers like 130, 110, .... the function fails.
After the compute scalar it does a filter which includes my view definition where clause.
If I re-produce this on a small scale like 20 records it works fine and I see that in the index scan there is a predicate that acts like my view filter.
So my questions is how to tell that query to use the predicate in the index scan so it's not trying to apply functions to rows I'm not expecting it to?
Here is a script to re-produce the results.
Change the initial value of @counter to alter the number of rows in the base table.
USE myDB
GO
IF OBJECT_ID(N'dbo.abc') IS NOT NULL DROP VIEW dbo.abc;
go
IF OBJECT_ID(N'dbo.latLongBaseTable') IS NOT NULL DROP TABLE dbo.latLongBaseTable;
go
CREATE TABLE [dbo].[latLongBaseTable]
(
[ID] [int] IDENTITY(1, 1)
NOT NULL
,[Type] [varchar](50) NOT NULL
,[sector] [varchar](50) NOT NULL
,[lat] [varchar](50) NOT NULL
,[long] [varchar](50) NOT NULL
,CONSTRAINT [PK_latLongBaseTable] PRIMARY KEY CLUSTERED ([ID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
go
--DECLARE @counter INT = 200000 -- will not use predicate in final select
DECLARE @counter INT = 20 -- will use predicate in final select
WHILE @counter > 0
BEGIN
INSERT INTO dbo.latLongBaseTable
(Type
,sector
,lat
,long)
VALUES ('ABC'
,'016'
,'42.3905'
,'-100.6568')
SET @counter = @counter - 1
END
go
-- put one bogus row under a different type
INSERT INTO dbo.latLongBaseTable
(Type
,sector
,lat
,long)
VALUES ('ZZZ'
,'001'
,'-99.3905'
,'-102.6568')
-------------------------------------------------------------------
go
CREATE VIEW dbo.abc
AS
SELECT ID
,Type
,sector
,lat
,long
FROM dbo.latLongBaseTable
WHERE Type = 'ABC'
go
-- If the Clustered index scan does not use a predicate this fails since it does the compute scalar before the filter.
SELECT
GEOGRAPHY::STGeomFromText('POINT(' + RTRIM(LTRIM(long)) + ' '
+ RTRIM(LTRIM(lat)) + ')', 4326)
,MIN(lat) OVER (PARTITION BY sector) AS minLat
FROM dbo.abc
June 27, 2012 at 11:25 pm
Tom Van Harpen (6/27/2012)
The title is a bit misleading, it is applying the view where clause but after it does a compute scalar which is causing the error.We have a table with a hundred generic columns that we use to store a variety of data that has different meta-data.
Each different type of data is identified with a special code.
We have views over this table to pull out data based on that code. We then use these views like we would a table.
just recently I started getting an error within an ETL job that runs a query regarding geography data. The error is indicating that the latitude was not between -90.00 and 90.00. Well that simply is not true, I checked all 250k rows and the data is fine, (the data I see in the view)
After many hours I discovered that within the execution plan we are not simply "reading from the view" it's reading from the base table (without a predicate) and grabbing rows that are not part of the view, then it's trying to apply a geography function to this data. Since this column also contains numbers like 130, 110, .... the function fails.
After the compute scalar it does a filter which includes my view definition where clause.
If I re-produce this on a small scale like 20 records it works fine and I see that in the index scan there is a predicate that acts like my view filter.
So my questions is how to tell that query to use the predicate in the index scan so it's not trying to apply functions to rows I'm not expecting it to?
probably the best option is to change your query to avoid the troublesome calculation
SELECT CASE WHEN CAST(RTRIM(LTRIM(lat)) AS NUMERIC(10,4)) >= -90
AND CAST(RTRIM(LTRIM(lat)) AS NUMERIC(10,4)) <= 90
THEN GEOGRAPHY::STGeomFromText('POINT(' + RTRIM(LTRIM(long))
+ ' ' + RTRIM(LTRIM(lat)) + ')',
4326)
ELSE NULL
END ,
MIN(lat) OVER ( PARTITION BY sector ) AS minLat
FROM dbo.abc
beyond that, you'll be doing things that will make you feel a little bit dirty inside. For example, using plan guides to force a good plan, or create a filtered covering index, or splitting you query into two parts using a temp table.
FYI, even with 200,000 rows, I can't reproduce your problem in SQL 2012.
This could be a bug in the query optimizer. You may want to open a connect item with microsoft (if one doesn't already exist).
June 28, 2012 at 8:56 am
thanks for taking a look, yeah I would like a cleaner solution.
We have about 40 views over this table and have the potential for this to cause more problems.
It does make me wonder if this was corrected since you are not seeing it in 2012. I attached both my plans so you can see the difference.
I'll do some more digging...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply