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

Question

question

DJAdan-4490 asked Nov 2, '21 ErlandSommarskog commented Nov 3, '21

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

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

sql-server-general sql-server-transact-sql

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-11-02T21:20:21.1Z

DJAdan-4490 answered Nov 2, '21 Viorel-1 edited Nov 3, '21

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

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx · Nov 02, 2021 at 09:30 PM

The first query is just running your view. Since your view is handling the bad data it is expected to work.

The second query however is combining the two queries into one. As part of that join SQL seems to be doing a scan of the whole table and when it runs across the bad row it doesn't like it and fails. The best way to understand why this is happening is to take a look at the execution plan that SQL is running. It should ideally show you the point where things are going wrong. I don't know how CTEs are put together during the querying planning but when I look at the query plan in my version of SQL here's what I see which is ultimately what appears to fail.

CONVERT(int,right([master].[dbo].[test].[value],(2)),0)>=CONVERT_IMPLICIT(int,[@1],0) AND CONVERT(int,right([master].[dbo].[test].[value],(2)),0)<=CONVERT_IMPLICIT(int,[@2],0) AND NOT [master].[dbo].[test].[value] like 'jkl%'

Notice it does the conversion to int BEFORE it does the CTE's filter. Hence the failure.

0 ·

Viorel-1 · Nov 02, 2021 at 10:29 PM

According to various articles, "boolean operator short-circuiting is not guaranteed". In your case you have an expanded query like 'select * from test
where value not like 'jkl%' and convert(integer, right(value, 2)) between 2 and 3' (which fails if executed manually too). The AND operator is not short-circuited in this case (i.e. the second condition is evaluated even if the first one is false), because "SQL is a declarative, not procedural language" and "it is implementation-dependent whether expressions are actually evaluated left to right".

Unfortunately, it seems that there are no query hints or option to control this behaviour of views when it looks like an anomaly.

0 ·

Answer 2 · 2021-11-02T21:06:03.99Z

cooldadtx answered Nov 2, '21

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2021-11-02T22:22:35.287Z

ErlandSommarskog answered Nov 2, '21

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 4 · 2021-11-03T06:11:37.08Z

MelissaMa-msft answered Nov 3, '21 ErlandSommarskog commented Nov 3, '21

Hi @DJAdan-4490,

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.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog · Nov 03, 2021 at 09:53 PM

Please don't suggest isnumeric. isnumeric returns 1 if the value can be converted to any numeric data type, so convert to int could still fail. The correct solution is to use try_convert. (Which was added in SQL 2012, so users on SQL 2008 and earlier will need to use inferior options. But DJAdan is on SQL 2017.)

0 ·

Answer 5 · 2021-11-03T11:53:14.553Z

TomPhillips-1744 answered Nov 3, '21 TomPhillips-1744 edited Nov 3, '21

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question