Leading zero's are missing.

[Luuk34]

Description

When I have query:

select 
   -- i,
   '000000'+ CONVERT(VARCHAR(8),i)  as LZ1, 
   right('000000' + CONVERT(VARCHAR(8),i),6) as LZ2
from
   (select i, format(i,'00####') as X from integers where i between 997 and 1002 ) X
;

I get this as answer:

LZ1	LZ2
997	000997
998	000998
999	000999
1000	001000
1001	001001
1002	001002

after uncommenting the --i i get, a CHANGED value for LZ1:

i	LZ1	LZ2
997	000000997	000997
998	000000998	000998
999	000000999	000999
1000	0000001000	001000
1001	0000001001	001001
1002	0000001002	001002

DBeaver Version

Community Edition 24.1.0

Operating System

Windows 11

Database and driver

• Database:
  Microsoft SQL Server 2019 (RTM-GDR) (KB5035434) - 15.0.2110.4 (X64) Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 22631: ) (Hypervisor)

• Driver: SQL Server 12.4.1.x64

Steps to reproduce

The integers table is like this:

CREATE TABLE test.dbo.integers (
    i bigint NOT NULL
    CONSTRAINT integers_pk PRIMARY KEY (i)
);

Additional context

No response

[Luuk34]

P.S. This happens only in DBeaver,
When doing this same statement in SQLCMD the expected results are returned.

[ShadelessFox]

Thanks for the bug report. DBeaver thinks the LZ1 column is an integer and strips leading zeroes when parsed as a number.

I assume the correct output is the following, correct?

|LZ1       |LZ2   |
+----------+------+
|000000997 |000997|
|000000998 |000998|
|000000999 |000999|
|0000001000|001000|
|0000001001|001001|
|0000001002|001002|