How to delay convert() evaluation until after join
3
votes
2
answers
581
views
The following query converts a single string of packed CSV that represents 13k rows by 2 columns. Column A is a bigint. Column B is a smallint.
declare
@dataCsv nvarchar(max) = '29653,36,19603,36,19604,36,29654,36'; -- abbreviated, actually 13k rows
with Input as
(
select Value,
Row = row_number() over (order by (select null)) - 1
from string_split(@dataCsv, ',') o
)
--insert into PubCache.TableName
select 78064 as CacheId,
convert(bigint, i.Value) as ObjectId,
convert(smallint, i2.Value) as BrandId
from Input i
inner hash join Input i2 -- hash to encourage string_split() only once per column
on i2.Row = i.Row + 1
where i.Row % 2 = 0
order by i.Row
Execution Plan: https://www.brentozar.com/pastetheplan/?id=By0hYPmd6
As shown in the plan, the evaluation of the convert() is occurring before the join so sometimes (depending on length of input), it fails with
> The conversion of the nvarchar value '37645' overflowed an INT2
> column. Use a larger integer column.
Temporarily changing the conversion from smallint to int allows the query to complete and inspecting the BrandId column output shows that it always just contains the value '36' for this example.
Is there a simple way to delay the convert(smallint, i2.Value) until the after the join so that only those expected CSV positions are converted?
I realize there are other ways to pack a string stream (such as using multiple variables, or interlacing different split chars, etc) but am not interested in solving this example that way for purposes of this question. Thanks!
Asked by crokusek
(2110 rep)
Jan 3, 2024, 11:21 PM
Last activity: Jan 4, 2024, 04:01 PM
Last activity: Jan 4, 2024, 04:01 PM