UTF8_Bug_35606671_SSMS-Tests

-- These examples support the following bug report for SQL Server 2019 CTP 2.0:
-- https://feedback.azure.com/forums/908035-sql-server/suggestions/35606671-utf-8-in-sql-2019-an-error-occurred-while-executi

-- By: Solomon Rutzky
-- On: 2018-10-15
-- https://SqlQuantumLeap.com/

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

-- !! HIGHLIGHT EACH QUERY AND THE "USE" STATEMENT ABOVE IT TO EXECUTE INDIVIDUALLY !!

USE [UTF8];

;WITH nums AS
(
SELECT TOP (1) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [num]
FROM [master].[sys].[columns] col
)
SELECT
CONVERT(CHAR(1), NULL),
CONVERT(BIT, 1),
CONVERT(BIT, 1),
4,
CONVERT(BIT, 1),
--CONVERT(BIT, 1),
--CONVERT(BIT, 1),
CONVERT(BIT, 1),
CONVERT(BIT, 1),
CONVERT(BIT, 1)
FROM nums chr





USE [UTF8];

;WITH nums AS
(
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [num]
FROM [master].[sys].[columns] col
)
SELECT
CONVERT(CHAR(1), NULL),
CONVERT(BIT, 1),--1,
CONVERT(BIT, 1),--2,
4,
5,--CONVERT(BIT, 1),--3,
CONVERT(BIT, 1),--4,
CONVERT(BIT, 1),--5,
CONVERT(BIT, 1),--
CONVERT(BIT, 1),--6,
CONVERT(BIT, 1) --7
FROM nums chr
-- ā  NULL    NULL    1280    16843008    1   1   1   1   1




USE [UTF8];

;WITH nums AS
(
SELECT TOP (20) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [num]
FROM [master].[sys].[columns] col
)
SELECT
CONVERT(CHAR(1), Null) AS [NULL UTF8],
CONVERT(int, 1) AS [1],
CONVERT(int, 0) AS [2],
0 AS [3],
0 AS [4],
NEWID() AS [5],
CONVERT(INT, 1) AS [6],
CONVERT(BIGINT, 1) AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM nums chr
-- An error occurred while executing batch. Error message is: Offset and length were out of bounds for the array or count is greater than the number of elements from index to the end of the source collection.





USE [UTF8];

;WITH nums AS
(
SELECT TOP (20) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [num]
FROM [master].[sys].[columns] col
)
SELECT
CONVERT(CHAR(1), Null) AS [NULL UTF8],
CONVERT(BIGINT, 1) AS [1],
CONVERT(INT, 0) AS [2],
0 AS [3],
0 AS [4],
NEWID() AS [5],
CONVERT(INT, 1) AS [6],
CONVERT(BIGINT, 1) AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM nums chr
-- An error occurred while executing batch. Error message is: Invalid array size.



--------------------------
-- In each of the following tests (which moves the [NULL UTF8] column incrementally from first to last position), columns to the left of [NULL UTF8] display correctly, while [NULL UTF8] displays as an empty string, and columns to the right of [NULL UTF8] display incorrectly in varying ways depending on the position of the [NULL UTF8] column and sometimes even the particular execution.

USE [UTF8];

SELECT TOP (20)
CONVERT(CHAR(1), NULL) AS [NULL UTF8],
CONVERT(INT, 1) AS [1],
CONVERT(INT, 0) AS [2],
0 AS [3],
0 AS [4],
NEWID() AS [5],
CONVERT(INT, 1) AS [6],
CONVERT(BIGINT, 1) AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM [master].[sys].[columns] col
-- An error occurred while executing batch. Error message is: Offset and length were out of bounds for the array or count is greater than the number of elements from index to the end of the source collection.

---------------------------------------------

USE [UTF8];

SELECT TOP (20)
CONVERT(INT, 1) AS [1],
CONVERT(CHAR(1), NULL) AS [NULL UTF8],
CONVERT(INT, 0) AS [2],
0 AS [3],
0 AS [4],
NEWID() AS [5],
CONVERT(INT, 1) AS [6],
CONVERT(BIGINT, 1) AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM [master].[sys].[columns] col
-- An error occurred while executing batch. Error message is: Invalid array size.


---------------------------------------------
USE [UTF8];

SELECT TOP (20)
CONVERT(INT, 1) AS [1],
CONVERT(INT, 0) AS [2],
CONVERT(CHAR(1), NULL) AS [NULL UTF8],
0 AS [3],
0 AS [4],
NEWID() AS [5],
CONVERT(INT, 1) AS [6],
CONVERT(BIGINT, 1) AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM [master].[sys].[columns] col
-- An error occurred while executing batch. Error message is: Offset and length were out of bounds for the array or count is greater than the number of elements from index to the end of the source collection.

---------------------------------------------
USE [UTF8];

SELECT TOP (20)
CONVERT(INT, 1) AS [1],
CONVERT(INT, 0) AS [2],
CONVERT(CHAR(1), NULL) AS [NULL UTF8],
0 AS [3],
0 AS [4],
NEWID() AS [5],
CONVERT(INT, 1) AS [6],
CONVERT(BIGINT, 1) AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM [master].[sys].[columns] col
-- An error occurred while executing batch. Error message is: Invalid array size.

---------------------------------------------
USE [UTF8];

SELECT TOP (20)
CONVERT(INT, 1) AS [1],
CONVERT(INT, 0) AS [2],
0 AS [3],
CONVERT(CHAR(1), NULL) AS [NULL UTF8],
0 AS [4],
NEWID() AS [5],
CONVERT(INT, 1) AS [6],
CONVERT(BIGINT, 1) AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM [master].[sys].[columns] col
-- An error occurred while executing batch. Error message is: Invalid array size.


---------------------------------------------

USE [UTF8];

SELECT TOP (20)
CONVERT(INT, 1) AS [1],
CONVERT(INT, 0) AS [2],
0 AS [3],
0 AS [4],
CONVERT(CHAR(1), NULL) AS [NULL UTF8],
NEWID() AS [5],
CONVERT(INT, 1) AS [6],
CONVERT(BIGINT, 1) AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM [master].[sys].[columns] col
-- (query never returns; had to close query tab, answer "yes" to "cancel the query?")


---------------------------------------------
USE [UTF8];

SELECT TOP (20)
CONVERT(INT, 1) AS [1],
CONVERT(INT, 0) AS [2],
0 AS [3],
0 AS [4],
NEWID() AS [5],
CONVERT(CHAR(1), NULL) AS [NULL UTF8],
CONVERT(INT, 1) AS [6],
CONVERT(BIGINT, 1) AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM [master].[sys].[columns] col
-- An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 4

---------------------------------------------
USE [UTF8];

SELECT TOP (20)
CONVERT(INT, 1) AS [1],
CONVERT(INT, 0) AS [2],
0 AS [3],
0 AS [4],
NEWID() AS [5],
CONVERT(INT, 1) AS [6],
CONVERT(CHAR(1), NULL) AS [NULL UTF8],
CONVERT(BIGINT, 1) AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM [master].[sys].[columns] col
-- An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 0


---------------------------------------------

USE [UTF8];

SELECT TOP (20)
CONVERT(INT, 1) AS [1],
CONVERT(INT, 0) AS [2],
0 AS [3],
0 AS [4],
NEWID() AS [5],
CONVERT(INT, 1) AS [6],
CONVERT(BIGINT, 1) AS [7],
CONVERT(CHAR(1), NULL) AS [NULL UTF8],
CONVERT(BIGINT, 1) AS [8]
FROM [master].[sys].[columns] col
-- An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 122

---------------------------------------------
USE [UTF8];

SELECT TOP (20)
CONVERT(INT, 1) AS [1],
CONVERT(INT, 0) AS [2],
0 AS [3],
0 AS [4],
NEWID() AS [5],
CONVERT(INT, 1) AS [6],
CONVERT(BIGINT, 1) AS [7],
CONVERT(BIGINT, 1) AS [8],
CONVERT(CHAR(1), NULL) AS [NULL UTF8]
FROM [master].[sys].[columns] col
-- (query never returns; had to close query tab, answer "yes" to "cancel the query?")

---------------------------------------------
---------------------------------------------


USE [UTF8];

SELECT TOP (100)
CONVERT(BIT, 1) AS [1],
CONVERT(BIT, 1) AS [2],
CONVERT(BIT, 1) AS [3],
CONVERT(BIT, 1) AS [4],
CONVERT(CHAR(1), NULL) AS [NULL],
CONVERT(BIT, 1) AS [5],
NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6],
'abcdefghijklmnoptuvwxyz' AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM [master].[sys].[columns] col
-- Returned 1 row of the following, and errored with the message shown below the result row:
-- 1    1   1   1   앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  1   f❵𐑪.abcdefghijklmnoptuvwxyzĈ  fghijklmnoptuvwxyzĈ    8430866047665467136
-- An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 0



USE [UTF8];

SELECT TOP (100)
CONVERT(BIT, 1) AS [1],
CONVERT(int, 1) AS [2], -- changed to "int"
CONVERT(BIT, 1) AS [3],
CONVERT(BIT, 1) AS [4],
CONVERT(CHAR(1), NULL) AS [NULL],
CONVERT(BIT, 1) AS [5],
NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6],
'abcdefghijklmnoptuvwxyz' AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM [master].[sys].[columns] col;
-- 1    1   1   1   앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  1   ก䤀李昀甀ħ櫘⻜愀戀挀搀攀昀最栀椀樀欀氀洀渀漀瀀琀甀瘀眀砀礀稀ࠀ
 āā앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  NULL



USE [UTF8];

SELECT TOP (100)
CONVERT(BIT, 1) AS [1],
CONVERT(BIT, 1) AS [2], -- changed back to "bit"
CONVERT(BIT, 1) AS [3],
CONVERT(int, 4) AS [4], -- changed to "int" / 4
CONVERT(CHAR(1), NULL) AS [NULL],
CONVERT(BIT, 1) AS [5],
NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6],
'abcdefghijklmnoptuvwxyz' AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM [master].[sys].[columns] col
-- 1    1   1   4   앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  NULL        앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  NULL



USE [UTF8];

SELECT TOP (100)
CONVERT(BIT, 1) AS [1],
CONVERT(BIT, 1) AS [2],
CONVERT(BIT, 1) AS [3],
4 AS [4],  -- removed explicit CONVERT()
CONVERT(CHAR(1), NULL) AS [NULL],
CONVERT(BIT, 1) AS [5],
NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6],
'abcdefghijklmnoptuvwxyz' AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM [master].[sys].[columns] col
-- Returned 25 rows (not 100) of the following, saying "query completed successfully"
-- 1    1   1   4   앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  1   앉仪gf❵𐑪 abcdefghijklmnoptuvwxyz 1


---------------------------------------------
---------------------------------------------

-- THE FOLLOWING TEST PROVES THAT THE ISSUE IS NOT TIED TO THE DEFAULT COLLATION OF THE CURRENT
-- DATABASE. SINCE YOU AREN'T SUPPOSED TO BE ABLE TO USE A UTF8 COLLATION AT THE INSTANCE LEVEL,
-- [master] SHOULD BE GUARANTEED TO NOT BE USING A "_UTF8" COLLATION.

-- AND THE ISSUE IS NOT THE SIZE OF THE COLUMN SINCE "CHAR(10)" SHOULD BE PLENTY BIG ENOUGH.


USE [master];

SELECT TOP (100)
CONVERT(BIT, 1) AS [1],
CONVERT(int, 1) AS [2], -- changed to "int"
CONVERT(BIT, 1) AS [3],
CONVERT(int, 1) AS [4], -- changed to "int"
CONVERT(CHAR(10), NULL) COLLATE Latin1_General_100_CI_AS_SC_UTF8 AS [NULL], -- added COLLATE
CONVERT(BIT, 1) AS [5],
NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6],
'abcdefghijklmnoptuvwxyz' AS [7],
CONVERT(BIGINT, 1) AS [8]
FROM [master].[sys].[columns] col;
-- Returned over 13 rows of the following (the 13th row was the only row that was different):
/*
1   2   3   4   NULL                               5      6   7    8
-------------------------------------------------------------------------------------
1   1   1   1   앉仪gf❵𐑪扡摣晥桧橩汫湭灯畴睶祸ࡺ
   NULL      
   NULL
....
1   1   1   1   앉仪gf❵𐑪扡摣晥桧橩汫湭灯畴睶祸

   0               NULL
*/

--------------------------------------------------------------------------------------------