SqlQuantumLeap

UTF8_Bug_36136810_SilentTruncation

Nov 29th, 2018
57
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- These examples support the following bug report for SQL Server 2019 CTP 2.1:
  2. -- https://feedback.azure.com/forums/908035-sql-server/suggestions/36136810-utf-8-in-sql-2019-collation-precedence-allows-for
  3.  
  4. -- By: Solomon Rutzky
  5. -- On: 2018-11-28
  6. -- https://SqlQuantumLeap.com/
  7.  
  8.  
  9. ------------------------------------------------------------------------------
  10. -- SETUP:
  11. USE [master];
  12.  
  13. IF (DB_ID(N'SilentTruncationTest') IS NULL)
  14. BEGIN
  15.     CREATE DATABASE [SilentTruncationTest] COLLATE Ukrainian_100_CI_AS;
  16.     ALTER DATABASE [SilentTruncationTest] SET RECOVERY SIMPLE;
  17. END;
  18. GO
  19.  
  20.  
  21.  
  22. -- !! HIGHLIGHT EACH "TEST" SECTION BELOW AND EXECUTE IT INDIVIDUALLY !!
  23.  
  24. -------------------------------------------------------
  25. -- TEST 1:
  26. USE [SilentTruncationTest];
  27.  
  28. SELECT COLLATIONPROPERTY(N'Ukrainian_100_CI_AS', 'CodePage'); -- 1251
  29. SELECT CHAR(212); -- Ф
  30.  
  31.  
  32.  
  33. -- Convert to UTF-8:
  34. SELECT 'Ф' COLLATE Ukrainian_100_CI_AS_SC_UTF8;
  35. -- {empty string}
  36.  
  37. SELECT SQL_VARIANT_PROPERTY('Ф' COLLATE Latin1_General_100_CI_AS_SC_UTF8, 'MaxLength');
  38. -- 1 (but it should be 2)
  39.  
  40. SELECT CONVERT(VARBINARY(4), 'Ф' COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [Bytes],
  41.        DATALENGTH('Ф' COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [NumBytes];
  42. -- 0x   0
  43.  
  44.  
  45.  
  46. -- Convert to UTF-8 again, but add a character to make it 2 bytes:
  47. SELECT 'ФX' COLLATE Latin1_General_100_CI_AS_SC_UTF8;
  48. -- Ф (the "X" is missing)
  49.  
  50. SELECT SQL_VARIANT_PROPERTY('ФX' COLLATE Latin1_General_100_CI_AS_SC_UTF8, 'MaxLength');
  51. -- 2 (but it should be 3)
  52.  
  53. SELECT CONVERT(VARBINARY(4), 'ФX' COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [Bytes],
  54.        DATALENGTH('ФX' COLLATE Latin1_General_100_CI_AS_SC_UTF8) AS [NumBytes];
  55. -- 0xD0A4   2
  56. GO
  57.  
  58.  
  59. ------------------------------------------------------------------------------
  60. -- TEST 2:
  61. ALTER DATABASE [SilentTruncationTest] COLLATE Hebrew_100_CI_AS_SC;
  62.  
  63.  
  64. SELECT COLLATIONPROPERTY(N'Hebrew_100_CI_AS_SC', 'CodePage'); -- 1255
  65. SELECT CHAR(243); -- ף
  66.  
  67.  
  68.  
  69. -- Convert to UTF-8:
  70. SELECT 'ף' COLLATE Hebrew_100_CI_AS_SC_UTF8;
  71. -- {empty string}
  72.  
  73. SELECT SQL_VARIANT_PROPERTY('ף' COLLATE Hebrew_100_CI_AS_SC_UTF8, 'MaxLength');
  74. -- 1 (but it should be 2)
  75.  
  76. SELECT CONVERT(VARBINARY(4), 'ף' COLLATE Hebrew_100_CI_AS_SC_UTF8) AS [Bytes],
  77.        DATALENGTH('ף' COLLATE Hebrew_100_CI_AS_SC_UTF8) AS [NumBytes];
  78. -- 0x   0
  79.  
  80.  
  81.  
  82. -- Convert to UTF-8 again, but add a character to make it 2 bytes:
  83. SELECT 'ףX' COLLATE Hebrew_100_CI_AS_SC_UTF8;
  84. -- ף (the "X" is missing)
  85.  
  86. SELECT SQL_VARIANT_PROPERTY('ףX' COLLATE Hebrew_100_CI_AS_SC_UTF8, 'MaxLength');
  87. -- 2 (but it should be 3)
  88.  
  89. SELECT CONVERT(VARBINARY(4), 'ףX' COLLATE Hebrew_100_CI_AS_SC_UTF8) AS [Bytes],
  90.        DATALENGTH('ףX' COLLATE Hebrew_100_CI_AS_SC_UTF8) AS [NumBytes];
  91. -- 0xD7A3   2
  92.  
  93.  
  94.  
  95. ------------------------------------------------------------------------------
  96. -- TEST 3:
  97.  
  98. -- DROP TABLE #Test;
  99. CREATE TABLE #Test
  100. (
  101.     VC VARCHAR(20) COLLATE Hebrew_100_CI_AS_SC_UTF8,
  102.     NVC NVARCHAR(20) COLLATE Hebrew_100_CI_AS_SC_UTF8,
  103. );
  104.  
  105. INSERT INTO #Test ([VC], [NVC]) VALUES ('Test VARCHAR:', N'Test NVARCHAR:');
  106.  
  107. SELECT CHAR(224) + CHAR(225) + CHAR(226) + CHAR(227) + CHAR(228); -- אבגדה
  108.  
  109. SELECT [VC] + 'אבגדה' AS [VARCHAR],
  110.        [NVC] + 'אבגדה' AS [NVARCHAR]
  111. FROM   #Test;
  112. -- Test VARCHAR:אב    Test NVARCHAR:אבגדה
  113.  
  114. DECLARE @TestVar VARCHAR(5) = CHAR(224) + CHAR(225) + CHAR(226) + CHAR(227) + CHAR(228);
  115. SELECT @TestVar; -- אבגדה
  116.  
  117. SELECT [VC] + @TestVar AS [VARCHAR],
  118.        [NVC] + @TestVar AS [NVARCHAR]
  119. FROM   #Test;
  120. -- Test VARCHAR:אב    Test NVARCHAR:אבגדה
  121.  
  122.  
  123. -- Same string as @TestVar, but variable is 1 byte larger:
  124. DECLARE @TestVar2 VARCHAR(6) = CHAR(224) + CHAR(225) + CHAR(226) + CHAR(227) + CHAR(228);
  125. SELECT @TestVar2; -- אבגדה
  126.  
  127. SELECT [VC] + @TestVar2 AS [VARCHAR],
  128.        [NVC] + @TestVar2 AS [NVARCHAR]
  129. FROM   #Test;
  130. -- Test VARCHAR:אבג  Test NVARCHAR:אבגדה
  131.  
  132.  
  133.  
  134.  
  135.  
  136.  
  137. /*
  138. USE [master];
  139. DROP DATABASE [SilentTruncationTest];
  140. */
RAW Paste Data