Transact SQL – Warning – “Type conversion in expression may affect “CardinalityEstimate” in query plan choice”

 

 

Background

Reviewing code and ran into this warning…

Code


use [tempdb]
go

declare @tblRange TABLE
(
      [id] int not null identity(1,1)

    , [min]  numeric(5,2) null
    , [max]  numeric(5,2) null

    , [range]  AS 
            (
                cast
					(
						case 
				            when [min]=[max] then CONVERT([varchar],CONVERT([float],[min])) 
					        else (
                            CONVERT([varchar],CONVERT([float],[min]))
                                +' - '
                                + CONVERT([varchar],CONVERT([float],[max]))
                        )			 
						end
					as varchar(30)
				)
            ) PERSISTED

)

insert into @tblRange
(
    [min], [max] 
)
select 3,3
union
select 0.5,10

select *
from   @tblRange

Query Plan

 

Warning

Image

Textual

Type conversion in expression (CONVERT(varchar(30),CONVERT(float(53),[min],0),0)) may affect “CardinalityEstimate” in query plan choice, Type conversion in expression (CONVERT(varchar(30),CONVERT(float(53),[max],0),0)) may affect “CardinalityEstimate” in query plan choice

Source Code Control

GitHub

DanielAdeniji/SQLServerTypeConversionMayAffectCardinalityEstimate
Link

Summary

This is a tough error as it appears there is no redemptive path.

 

Addendum

Karly Shockley ( 2017-12-28)

Karly Shockley is able to reproduce.

Code

Here is her working code..


set nocount on;
go

set XACT_ABORT on
go

declare @tblRange2 table
( 
	[min] numeric(5,2) null 
) 

select [min] = convert(varchar, [min]) 
from   @tblRange2 

select [min] = a.[min] 
from   @tblRange2 a 
where  convert (varchar, a.[min]) = 'abc' <span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>

Query Plan

Connect Item

As she is able to reproduce, she suggested that I open up a Connect Item…

Defect #11427260 has been filed with our Product group on your behalf. We will not have any knowledge of its status and will not be able to provide updates but if you would like to have more visibility into the progress of this request, you can always file a Connect bug/request here, https://connect.microsoft.com/SQLServer/Feedback

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s