MSSQL conversion of decimal based string into varbinary(10) where 8-byte part exceeds bigint

Question

Preambule

I have MSSQL 2016 instance with CDC enabled. And I stream changes by Debezium kafka connector. Connector in logs and topics refer Log Sequence Number (LSN) in form of 000000aa:af9c3f2d:0f5e1. In MSSQL I have tables like cdc.dbo_Entity_cdc_CT where present columns __$start_lsn, __$start_lsn, __$end_lsn but it has type varbinary(10).

I would match values.

Based mostly on information in Day 11 of 31 Days of Disaster: Converting LSN Formats article which give hint it should be converted in chain convert to binary style 1 -> bigint, I wrote helper functions for that: https://github.com/Hubbitus/sql/commit/4ccf96492f2aba9796ccaecb1b74e9ba53a45895#diff-e495aa591f98a29c28153735379ad6fd

It mostly works. Contains descriptions and even testing conversion doing right.

But there is one problem I can't solve now.

8 byte part exceeded `bigint`

When I convert input varbinary(16) value, I split it into two parts: 2 and 8 bytes length, each convert into bigint, and then into varchar, f.e.:

DECLARE @lsn VARBINARY(10) ...
-- Fill by \0 at left
SET @lsn = CONVERT(varbinary, REPLICATE(char(0), 10 - LEN(@lsn))) + @lsn
DECLARE @lsn_s varchar(max) = CONVERT(varchar, CONVERT(bigint, SUBSTRING(@lsn, 1, 2), 1)) + CONVERT(varchar, CONVERT(bigint, SUBSTRING(@lsn, 3, 8), 1))

It works in most cases (f.e. for test table I see only 4 records with error), f.e. for value like:

DECLARE @lsn varbinary(10) = CONVERT(varbinary(10), '0x025D020F35B80001', 1)

You may check it like:

SELECT dbo.__tmp_td_cdc_decode_check(CONVERT(varbinary(10), '0x025D020F35B80001', 1))

But fails, said on:

DECLARE @lsn varbinary(10) = CONVERT(varbinary(10), '0x0043275D010200440020', 1)
SELECT dbo.__tmp_td_cdc_decode_check(@sql)

Indeed, MSSQL have not unsigned datatypes.

The main question

I do not understand how to work around this problem in line:

DECLARE @lsn_s varchar(max) = CONVERT(varchar, CONVERT(bigint, SUBSTRING(@lsn, 1, 2), 1)) + CONVERT(varchar, CONVERT(bigint, SUBSTRING(@lsn, 3, 8), 1))

How I should correctly transform varbinary(10) into varchar in form of decimal digits for any cases?

What I also do not understand, sometimes I got error "data truncation" on conversions, but sometimes it happened silently. Please look:

DECLARE @lsn varbinary(10) = CONVERT(varbinary(10), '0x0043275D010200440020', 1) -- Bigint overflow
SELECT
    dbo.__tmp_td_cdc_decode_check(@lsn) as ToCheck
    ,CONVERT(varchar, @lsn, 1) as orig_lsn_hex
    ,CONVERT(bigint, @lsn, 1) as orig_lsn_bigint -- In many cases it will lead overflow!
    ,dbo.__tmp_td_cdc_binary_decode_to_string(@lsn) as string
    ,dbo.__tmp_td_cdc_string_decode_to_binary_DECSTRING(dbo.__tmp_td_cdc_binary_decode_to_string(@lsn)) as decimal_string_reverse

Output:

┌─────────┬────────────────────────┬─────────────────────┬─────────────────────────┬────────────────────────┐
│ ToCheck │      orig_lsn_hex      │   orig_lsn_bigint   │         string          │ decimal_string_reverse │
├─────────┼────────────────────────┼─────────────────────┼─────────────────────────┼────────────────────────┤
│ Error   │ 0x0043275D010200440020 │ 2836424448419299360 │ 000012e6:2f707ba4:02580 │ 4838079590083609600    │
└─────────┴────────────────────────┴─────────────────────┴─────────────────────────┴────────────────────────┘

Hexadecimal value 0x0043275D010200440020 should be 1_238_768_277_386_959_257_632 and not 2_836_424_448_419_299_360 what I get from CONVERT(bigint, @lsn, 1)

P.S. Also interesting and helpful materials:

`DECLARE @lsn varbinary(10) = CONVERT(varbinary(10), '0x0043275D010200440020', 1)` can't generate an Overflow error for a `bigint`; there are no `bigint`s in that statement. Also, in some of your code you're declaring a `varchar` without a length; which is a really bad idea. — Thom A, Dec 25 '19 at 23:21
That hex value, however, as a `bigint` would be 1,238,768,277,386,959,257,632. The largest number you can store in a `bigint` is 9,223,372,036,854,775,807. So yes, your hex values is over 100 times too large. — Thom A, Dec 25 '19 at 23:30
The largest value you could store in a `varbinary(10)` would be `0xFFFFFFFFFFFFFFFFFFFF`, which as an integer would be 1,208,925,819,614,629,174,706,175. If you do need to store values as large as that, yours going to need a `decimal(25,0)`. — Thom A, Dec 25 '19 at 23:36
Actually, if summarize, question is: how to properly convert number, said represent by decimal string `4838079590083609600` into `binary(10)`? My [variant](https://github.com/Hubbitus/sql/commit/4ccf96492f2aba9796ccaecb1b74e9ba53a45895#diff-e495aa591f98a29c28153735379ad6fdR22) works almost all times, but in rare cases there `bigint` overflow happened (and conversion *silently* incorrect). — Hubbitus, Dec 26 '19 at 08:50
Like I said, `0x0043275D010200440020` is too big for a `bigint`, you'll need to use a different datatype like `decimal`. However, `binary` values of a `decimal` are not the same as a `int`. The `decimal` `1238768277386959257632` as a `binary(10)`, for example, is `0x19000001200044000201`. — Thom A, Dec 26 '19 at 11:25
Sure, I do not even attempt convert full that value into `bigint`, but split into two parts by 2 and 8 bytes. But problem what `bigint` signed, and sometimes even 8 bytes from binary exceed positive maximum — Hubbitus, Dec 26 '19 at 12:07

score 0 · Answer 1 · answered Dec 26 '19 at 11:42

If you must convert the value like you have using "normal" hexidecimal values, you'll need to be a bit more "clever". This is one idea, using a tally:

USE Sandbox;
GO

SELECT V.YourHex,
       SUM(CONVERT(decimal(25,0),CASE SS.Char WHEN 'A' THEN 10
                                              WHEN 'B' THEN 11
                                              WHEN 'C' THEN 12
                                              WHEN 'D' THEN 13
                                              WHEN 'R' THEN 14
                                              WHEN 'F' THEN 15
                                              ELSE CONVERT(int,SS.Char)
                                 END) * POWER(CONVERT(decimal(25,0),16),20-pos)) AS YourInt
FROM (VALUES(0x0043275D010200440020))V(YourHex)
     CROSS APPLY (VALUES(CONVERT(varchar(20),V.YourHex,2)))C(YourString)
     CROSS APPLY (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20))I(I)
     CROSS APPLY (VALUES(SUBSTRING(C.YourString,I.I, 1),I.I))SS(Char, Pos)
GROUP BY V.YourHex;

I want not int as result. I have that value and provide examples. I need from `my int` (`1238768277386959275408` in you example) reconstruct `varbinary(10)` value which will match to the source — Hubbitus, Dec 26 '19 at 12:12
You don't explain what your after well at all here @Hubbitus. I've told you the why, I've told you the how. If neither of those answer your question, the. Your question isn't the real question. — Thom A, Dec 26 '19 at 12:14

MSSQL conversion of decimal based string into varbinary(10) where 8-byte part exceeds bigint

Preambule

8 byte part exceeded bigint

The main question

1 Answers1

8 byte part exceeded `bigint`