Data conversion error while using a VIEW with WHERE clause even though VIEW does not return row with offending data

Question

Hi All,

I am observing unexpected behavior when using a view. I am getting an unexpected conversion error when I use a WHERE clause. I do not get the error if I omit the WHERE clause. The view is purposely defined to exclude the offending row, so it appears that the WHERE clause is being evaluated against the whole underlying table, even though the view previously excluded the row.

I am running SQL Server 2017 RTM-CU20 Enterprise Edition.

drop table if exists  test;
drop view  if exists  test_view;
go

create table test
(
    id      integer identity (1,1),
    value   varchar(10)
);
go

insert into test values ('abc01'),('def02'),('ghi03'),('jklmn');
go

create view test_view as
with cte as (select id, value from test where value not like 'jkl%')
select id, value, n = convert(integer,right(value,2)) from cte ;
go

/** SUCCESS **/
select * from test;
select * from test_view;

go
/** FAILURE **/
select * from test_view where n between 2 and 3;
go

drop table if exists test;
drop view if  exists test_view;

The view definition explicitly excludes row 4 (value like 'jkl%').

The select * from test_view properly executes and displays the data. It is only after I use a WHERE clause that the view fails.

All other statements are successful until the final SELECT statement. I don't understand why. Any explanation would be much appreciated.

Thank you!

--Dan

Answer 1

Hi Cooldadtx,

Thanks for your response. Perhaps I didn't ask my question clearly.

Why does SELECT * from TEST_VIEW work?
Why does SELECT * from TEST_VIEW WHERE n BETWEEN 2 and 3 fail?

Both queries have to evaluate the rules defined by the VIEW. What am I missing here?

Thanks!

--Dan

Answer 2

This phenomenon is not unheard of. It is due to that the optimizer may find it more efficient to evaluate the expressions in the SELECT list before it applies the filter. You should here keep in mind that the view is essentially a a macro, and the optimizer works with the expanded query.

One can argue that this is a bug, and I can't say that I like this behaviour. I have suggested to Microsoft that they should retain the error row(s) and only if they reach the final operator, an error should be raised. But I realise that this may not be simple to implement.

Anyway, there is a very simple fix to the problem. Change convert to try_convert. try_convert never raises an error if conversion fails, but returns NULL.

Answer 3

The fact that you're using a view isn't relevant. SQL will take the view query and combine it with the select query's where to produce the final query to run. If it didn't do that then SQL would first have to run the query for the view to get the initial set of data and then run the select query to get the subset of that. For performance reasons SQL just tries to run a single query. Hence there is no difference here. Here's a summary discussion on SO about it.

The correct solution here is to adjust your outer where to filter the bad rows as well. But you might also be able to use a persisted view to break it up, I have never tried that. You might also be able to use a UDF for the same reason but, again, haven't tried it.

Yet another approach might be to create a computed column on the table that does the same calculation as the CTE and then filter on that in your actual query.

Answer 4

Hi @DJAdan ,

Agreed with Viorel and you could refer to his comment.

Adding to what other experts said, you could also update your view like below to add one more judgment inside the view:

 create view test_view as  
 with cte as (select id, value from test where value not like 'jkl%')  
 select id, value, n = case when ISNUMERIC(right(value,2))=1 then convert(integer,right(value,2)) else null end from cte ;  

Best regards,
Melissa

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

You cannot control the order of execution of the WHERE clause. Just because you have it set to ignore non-numbers, does not mean it will execute that first.

As Erland said, the optimizer will decide what order is best to evaluate the filters. It may run 1 million times one way without error, and then an index is big enough, it will flip to a different execution plan and error.

PS. A CTE does not define a sub-select. It is not always executing the CTE first, and then the rest of the query. CTEs are just shorthand string macros.