UTF8_Bug_36136810_SilentTruncation

-- These examples support the following bug report for SQL Server 2019 CTP 2.1:
-- https://feedback.azure.com/forums/908035-sql-server/suggestions/36136810-utf-8-in-sql-2019-collation-precedence-allows-for

-- By: Solomon Rutzky
-- On: 2018-11-28
-- https://SqlQuantumLeap.com/


------------------------------------------------------------------------------
-- SETUP:
USE [master];

IF (DB_ID(N'SilentTruncationTest') IS NULL)
BEGIN
    CREATE DATABASE [SilentTruncationTest] COLLATE Ukrainian_100_CI_AS;
    ALTER DATABASE [SilentTruncationTest] SET RECOVERY SIMPLE;
END;
GO



-- !! HIGHLIGHT EACH "TEST" SECTION BELOW AND EXECUTE IT INDIVIDUALLY !!

-------------------------------------------------------
-- TEST 1:
USE [SilentTruncationTest];

SELECT COLLATIONPROPERTY(N'Ukrainian_100_CI_AS', 'CodePage'); -- 1251
SELECT CHAR(212); -- Ф



-- Convert to UTF-8:
SELECT 'Ф' COLLATE Ukrainian_100_CI_AS_SC_UTF8;
-- {empty string}

SELECT SQL_VARIANT_PROPERTY('Ф' COLLATE Latin1_General_100_CI_AS_SC_UTF8, 'MaxLength');
-- 1 (but it should be 2)

SELECT CONVERT(VARBINARY(4), 'Ф' COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [Bytes],
       DATALENGTH('Ф' COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [NumBytes];
-- 0x   0



-- Convert to UTF-8 again, but add a character to make it 2 bytes:
SELECT 'ФX' COLLATE Latin1_General_100_CI_AS_SC_UTF8;
-- Ф (the "X" is missing)

SELECT SQL_VARIANT_PROPERTY('ФX' COLLATE Latin1_General_100_CI_AS_SC_UTF8, 'MaxLength');
-- 2 (but it should be 3)

SELECT CONVERT(VARBINARY(4), 'ФX' COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [Bytes],
       DATALENGTH('ФX' COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [NumBytes];
-- 0xD0A4   2
GO


------------------------------------------------------------------------------
-- TEST 2:
ALTER DATABASE [SilentTruncationTest] COLLATE Hebrew_100_CI_AS_SC;


SELECT COLLATIONPROPERTY(N'Hebrew_100_CI_AS_SC', 'CodePage'); -- 1255
SELECT CHAR(243); -- ף



-- Convert to UTF-8:
SELECT 'ף' COLLATE Hebrew_100_CI_AS_SC_UTF8;
-- {empty string}

SELECT SQL_VARIANT_PROPERTY('ף' COLLATE Hebrew_100_CI_AS_SC_UTF8, 'MaxLength');
-- 1 (but it should be 2)

SELECT CONVERT(VARBINARY(4), 'ף' COLLATE Hebrew_100_CI_AS_SC_UTF8) AS [Bytes],
       DATALENGTH('ף' COLLATE Hebrew_100_CI_AS_SC_UTF8) AS [NumBytes];
-- 0x   0



-- Convert to UTF-8 again, but add a character to make it 2 bytes:
SELECT 'ףX' COLLATE Hebrew_100_CI_AS_SC_UTF8;
-- ף (the "X" is missing)

SELECT SQL_VARIANT_PROPERTY('ףX' COLLATE Hebrew_100_CI_AS_SC_UTF8, 'MaxLength');
-- 2 (but it should be 3)

SELECT CONVERT(VARBINARY(4), 'ףX' COLLATE Hebrew_100_CI_AS_SC_UTF8) AS [Bytes],
       DATALENGTH('ףX' COLLATE Hebrew_100_CI_AS_SC_UTF8) AS [NumBytes];
-- 0xD7A3   2



------------------------------------------------------------------------------
-- TEST 3:

-- DROP TABLE #Test;
CREATE TABLE #Test
(
    VC VARCHAR(20) COLLATE Hebrew_100_CI_AS_SC_UTF8,
    NVC NVARCHAR(20) COLLATE Hebrew_100_CI_AS_SC_UTF8,
);

INSERT INTO #Test ([VC], [NVC]) VALUES ('Test VARCHAR:', N'Test NVARCHAR:');

SELECT CHAR(224) + CHAR(225) + CHAR(226) + CHAR(227) + CHAR(228); -- אבגדה

SELECT [VC] + 'אבגדה' AS [VARCHAR],
       [NVC] + 'אבגדה' AS [NVARCHAR]
FROM   #Test;
-- Test VARCHAR:אב    Test NVARCHAR:אבגדה

DECLARE @TestVar VARCHAR(5) = CHAR(224) + CHAR(225) + CHAR(226) + CHAR(227) + CHAR(228);
SELECT @TestVar; -- אבגדה

SELECT [VC] + @TestVar AS [VARCHAR],
       [NVC] + @TestVar AS [NVARCHAR]
FROM   #Test;
-- Test VARCHAR:אב    Test NVARCHAR:אבגדה


-- Same string as @TestVar, but variable is 1 byte larger:
DECLARE @TestVar2 VARCHAR(6) = CHAR(224) + CHAR(225) + CHAR(226) + CHAR(227) + CHAR(228);
SELECT @TestVar2; -- אבגדה

SELECT [VC] + @TestVar2 AS [VARCHAR],
       [NVC] + @TestVar2 AS [NVARCHAR]
FROM   #Test;
-- Test VARCHAR:אבג  Test NVARCHAR:אבגדה






/*
USE [master];
DROP DATABASE [SilentTruncationTest];
*/