-- 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
*/
--------------------------------------------------------------------------------------------
Comments