SqlQuantumLeap

UTF8_Bug_35606671_SSMS-Tests

Oct 15th, 2018
116
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.0:
  2. -- https://feedback.azure.com/forums/908035-sql-server/suggestions/35606671-utf-8-in-sql-2019-an-error-occurred-while-executi
  3.  
  4. -- By: Solomon Rutzky
  5. -- On: 2018-10-15
  6. -- https://SqlQuantumLeap.com/
  7.  
  8. -----------------------------------------------------------------------
  9. IF (DB_ID(N'UTF8') IS NULL)
  10. BEGIN
  11.    CREATE DATABASE [UTF8] COLLATE Latin1_General_100_CI_AS_SC_UTF8;
  12.    ALTER DATABASE [UTF8] SET RECOVERY SIMPLE;
  13. END;
  14. GO
  15. -----------------------------------------------------------------------
  16.  
  17. -- !! HIGHLIGHT EACH QUERY AND THE "USE" STATEMENT ABOVE IT TO EXECUTE INDIVIDUALLY !!
  18.  
  19. USE [UTF8];
  20.  
  21. ;WITH nums AS
  22. (
  23. SELECT TOP (1) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [num]
  24. FROM [master].[sys].[columns] col
  25. )
  26. SELECT
  27. CONVERT(CHAR(1), NULL),
  28. CONVERT(BIT, 1),
  29. CONVERT(BIT, 1),
  30. 4,
  31. CONVERT(BIT, 1),
  32. --CONVERT(BIT, 1),
  33. --CONVERT(BIT, 1),
  34. CONVERT(BIT, 1),
  35. CONVERT(BIT, 1),
  36. CONVERT(BIT, 1)
  37. FROM nums chr
  38.  
  39.  
  40.  
  41.  
  42.  
  43. USE [UTF8];
  44.  
  45. ;WITH nums AS
  46. (
  47. SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [num]
  48. FROM [master].[sys].[columns] col
  49. )
  50. SELECT
  51. CONVERT(CHAR(1), NULL),
  52. CONVERT(BIT, 1),--1,
  53. CONVERT(BIT, 1),--2,
  54. 4,
  55. 5,--CONVERT(BIT, 1),--3,
  56. CONVERT(BIT, 1),--4,
  57. CONVERT(BIT, 1),--5,
  58. CONVERT(BIT, 1),--
  59. CONVERT(BIT, 1),--6,
  60. CONVERT(BIT, 1) --7
  61. FROM nums chr
  62. -- ā  NULL    NULL    1280    16843008    1   1   1   1   1
  63.  
  64.  
  65.  
  66.  
  67. USE [UTF8];
  68.  
  69. ;WITH nums AS
  70. (
  71. SELECT TOP (20) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [num]
  72. FROM [master].[sys].[columns] col
  73. )
  74. SELECT
  75. CONVERT(CHAR(1), Null) AS [NULL UTF8],
  76. CONVERT(int, 1) AS [1],
  77. CONVERT(int, 0) AS [2],
  78. 0 AS [3],
  79. 0 AS [4],
  80. NEWID() AS [5],
  81. CONVERT(INT, 1) AS [6],
  82. CONVERT(BIGINT, 1) AS [7],
  83. CONVERT(BIGINT, 1) AS [8]
  84. FROM nums chr
  85. -- 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.
  86.  
  87.  
  88.  
  89.  
  90.  
  91. USE [UTF8];
  92.  
  93. ;WITH nums AS
  94. (
  95. SELECT TOP (20) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [num]
  96. FROM [master].[sys].[columns] col
  97. )
  98. SELECT
  99. CONVERT(CHAR(1), Null) AS [NULL UTF8],
  100. CONVERT(BIGINT, 1) AS [1],
  101. CONVERT(INT, 0) AS [2],
  102. 0 AS [3],
  103. 0 AS [4],
  104. NEWID() AS [5],
  105. CONVERT(INT, 1) AS [6],
  106. CONVERT(BIGINT, 1) AS [7],
  107. CONVERT(BIGINT, 1) AS [8]
  108. FROM nums chr
  109. -- An error occurred while executing batch. Error message is: Invalid array size.
  110.  
  111.  
  112.  
  113. --------------------------
  114. -- 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.
  115.  
  116. USE [UTF8];
  117.  
  118. SELECT TOP (20)
  119. CONVERT(CHAR(1), NULL) AS [NULL UTF8],
  120. CONVERT(INT, 1) AS [1],
  121. CONVERT(INT, 0) AS [2],
  122. 0 AS [3],
  123. 0 AS [4],
  124. NEWID() AS [5],
  125. CONVERT(INT, 1) AS [6],
  126. CONVERT(BIGINT, 1) AS [7],
  127. CONVERT(BIGINT, 1) AS [8]
  128. FROM [master].[sys].[columns] col
  129. -- 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.
  130.  
  131. ---------------------------------------------
  132.  
  133. USE [UTF8];
  134.  
  135. SELECT TOP (20)
  136. CONVERT(INT, 1) AS [1],
  137. CONVERT(CHAR(1), NULL) AS [NULL UTF8],
  138. CONVERT(INT, 0) AS [2],
  139. 0 AS [3],
  140. 0 AS [4],
  141. NEWID() AS [5],
  142. CONVERT(INT, 1) AS [6],
  143. CONVERT(BIGINT, 1) AS [7],
  144. CONVERT(BIGINT, 1) AS [8]
  145. FROM [master].[sys].[columns] col
  146. -- An error occurred while executing batch. Error message is: Invalid array size.
  147.  
  148.  
  149. ---------------------------------------------
  150. USE [UTF8];
  151.  
  152. SELECT TOP (20)
  153. CONVERT(INT, 1) AS [1],
  154. CONVERT(INT, 0) AS [2],
  155. CONVERT(CHAR(1), NULL) AS [NULL UTF8],
  156. 0 AS [3],
  157. 0 AS [4],
  158. NEWID() AS [5],
  159. CONVERT(INT, 1) AS [6],
  160. CONVERT(BIGINT, 1) AS [7],
  161. CONVERT(BIGINT, 1) AS [8]
  162. FROM [master].[sys].[columns] col
  163. -- 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.
  164.  
  165. ---------------------------------------------
  166. USE [UTF8];
  167.  
  168. SELECT TOP (20)
  169. CONVERT(INT, 1) AS [1],
  170. CONVERT(INT, 0) AS [2],
  171. CONVERT(CHAR(1), NULL) AS [NULL UTF8],
  172. 0 AS [3],
  173. 0 AS [4],
  174. NEWID() AS [5],
  175. CONVERT(INT, 1) AS [6],
  176. CONVERT(BIGINT, 1) AS [7],
  177. CONVERT(BIGINT, 1) AS [8]
  178. FROM [master].[sys].[columns] col
  179. -- An error occurred while executing batch. Error message is: Invalid array size.
  180.  
  181. ---------------------------------------------
  182. USE [UTF8];
  183.  
  184. SELECT TOP (20)
  185. CONVERT(INT, 1) AS [1],
  186. CONVERT(INT, 0) AS [2],
  187. 0 AS [3],
  188. CONVERT(CHAR(1), NULL) AS [NULL UTF8],
  189. 0 AS [4],
  190. NEWID() AS [5],
  191. CONVERT(INT, 1) AS [6],
  192. CONVERT(BIGINT, 1) AS [7],
  193. CONVERT(BIGINT, 1) AS [8]
  194. FROM [master].[sys].[columns] col
  195. -- An error occurred while executing batch. Error message is: Invalid array size.
  196.  
  197.  
  198. ---------------------------------------------
  199.  
  200. USE [UTF8];
  201.  
  202. SELECT TOP (20)
  203. CONVERT(INT, 1) AS [1],
  204. CONVERT(INT, 0) AS [2],
  205. 0 AS [3],
  206. 0 AS [4],
  207. CONVERT(CHAR(1), NULL) AS [NULL UTF8],
  208. NEWID() AS [5],
  209. CONVERT(INT, 1) AS [6],
  210. CONVERT(BIGINT, 1) AS [7],
  211. CONVERT(BIGINT, 1) AS [8]
  212. FROM [master].[sys].[columns] col
  213. -- (query never returns; had to close query tab, answer "yes" to "cancel the query?")
  214.  
  215.  
  216. ---------------------------------------------
  217. USE [UTF8];
  218.  
  219. SELECT TOP (20)
  220. CONVERT(INT, 1) AS [1],
  221. CONVERT(INT, 0) AS [2],
  222. 0 AS [3],
  223. 0 AS [4],
  224. NEWID() AS [5],
  225. CONVERT(CHAR(1), NULL) AS [NULL UTF8],
  226. CONVERT(INT, 1) AS [6],
  227. CONVERT(BIGINT, 1) AS [7],
  228. CONVERT(BIGINT, 1) AS [8]
  229. FROM [master].[sys].[columns] col
  230. -- An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 4
  231.  
  232. ---------------------------------------------
  233. USE [UTF8];
  234.  
  235. SELECT TOP (20)
  236. CONVERT(INT, 1) AS [1],
  237. CONVERT(INT, 0) AS [2],
  238. 0 AS [3],
  239. 0 AS [4],
  240. NEWID() AS [5],
  241. CONVERT(INT, 1) AS [6],
  242. CONVERT(CHAR(1), NULL) AS [NULL UTF8],
  243. CONVERT(BIGINT, 1) AS [7],
  244. CONVERT(BIGINT, 1) AS [8]
  245. FROM [master].[sys].[columns] col
  246. -- An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 0
  247.  
  248.  
  249. ---------------------------------------------
  250.  
  251. USE [UTF8];
  252.  
  253. SELECT TOP (20)
  254. CONVERT(INT, 1) AS [1],
  255. CONVERT(INT, 0) AS [2],
  256. 0 AS [3],
  257. 0 AS [4],
  258. NEWID() AS [5],
  259. CONVERT(INT, 1) AS [6],
  260. CONVERT(BIGINT, 1) AS [7],
  261. CONVERT(CHAR(1), NULL) AS [NULL UTF8],
  262. CONVERT(BIGINT, 1) AS [8]
  263. FROM [master].[sys].[columns] col
  264. -- An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 122
  265.  
  266. ---------------------------------------------
  267. USE [UTF8];
  268.  
  269. SELECT TOP (20)
  270. CONVERT(INT, 1) AS [1],
  271. CONVERT(INT, 0) AS [2],
  272. 0 AS [3],
  273. 0 AS [4],
  274. NEWID() AS [5],
  275. CONVERT(INT, 1) AS [6],
  276. CONVERT(BIGINT, 1) AS [7],
  277. CONVERT(BIGINT, 1) AS [8],
  278. CONVERT(CHAR(1), NULL) AS [NULL UTF8]
  279. FROM [master].[sys].[columns] col
  280. -- (query never returns; had to close query tab, answer "yes" to "cancel the query?")
  281.  
  282. ---------------------------------------------
  283. ---------------------------------------------
  284.  
  285.  
  286. USE [UTF8];
  287.  
  288. SELECT TOP (100)
  289. CONVERT(BIT, 1) AS [1],
  290. CONVERT(BIT, 1) AS [2],
  291. CONVERT(BIT, 1) AS [3],
  292. CONVERT(BIT, 1) AS [4],
  293. CONVERT(CHAR(1), NULL) AS [NULL],
  294. CONVERT(BIT, 1) AS [5],
  295. NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6],
  296. 'abcdefghijklmnoptuvwxyz' AS [7],
  297. CONVERT(BIGINT, 1) AS [8]
  298. FROM [master].[sys].[columns] col
  299. -- Returned 1 row of the following, and errored with the message shown below the result row:
  300. -- 1    1   1   1   앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  1   f❵𐑪.abcdefghijklmnoptuvwxyzĈ  fghijklmnoptuvwxyzĈ    8430866047665467136
  301. -- An error occurred while executing batch. Error message is: Internal connection fatal error. Error state: 15, Token : 0
  302.  
  303.  
  304.  
  305. USE [UTF8];
  306.  
  307. SELECT TOP (100)
  308. CONVERT(BIT, 1) AS [1],
  309. CONVERT(int, 1) AS [2], -- changed to "int"
  310. CONVERT(BIT, 1) AS [3],
  311. CONVERT(BIT, 1) AS [4],
  312. CONVERT(CHAR(1), NULL) AS [NULL],
  313. CONVERT(BIT, 1) AS [5],
  314. NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6],
  315. 'abcdefghijklmnoptuvwxyz' AS [7],
  316. CONVERT(BIGINT, 1) AS [8]
  317. FROM [master].[sys].[columns] col;
  318. -- 1    1   1   1   앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  1   ก䤀李昀甀ħ櫘⻜愀戀挀搀攀昀最栀椀樀欀氀洀渀漀瀀琀甀瘀眀砀礀稀ࠀ āā앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  NULL
  319.  
  320.  
  321.  
  322. USE [UTF8];
  323.  
  324. SELECT TOP (100)
  325. CONVERT(BIT, 1) AS [1],
  326. CONVERT(BIT, 1) AS [2], -- changed back to "bit"
  327. CONVERT(BIT, 1) AS [3],
  328. CONVERT(int, 4) AS [4], -- changed to "int" / 4
  329. CONVERT(CHAR(1), NULL) AS [NULL],
  330. CONVERT(BIT, 1) AS [5],
  331. NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6],
  332. 'abcdefghijklmnoptuvwxyz' AS [7],
  333. CONVERT(BIGINT, 1) AS [8]
  334. FROM [master].[sys].[columns] col
  335. -- 1    1   1   4   앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  NULL        앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  NULL
  336.  
  337.  
  338.  
  339. USE [UTF8];
  340.  
  341. SELECT TOP (100)
  342. CONVERT(BIT, 1) AS [1],
  343. CONVERT(BIT, 1) AS [2],
  344. CONVERT(BIT, 1) AS [3],
  345. 4 AS [4],  -- removed explicit CONVERT()
  346. CONVERT(CHAR(1), NULL) AS [NULL],
  347. CONVERT(BIT, 1) AS [5],
  348. NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6],
  349. 'abcdefghijklmnoptuvwxyz' AS [7],
  350. CONVERT(BIGINT, 1) AS [8]
  351. FROM [master].[sys].[columns] col
  352. -- Returned 25 rows (not 100) of the following, saying "query completed successfully"
  353. -- 1    1   1   4   앉仪gf❵𐑪.abcdefghijklmnoptuvwxyzĈ  1   앉仪gf❵𐑪 abcdefghijklmnoptuvwxyz 1
  354.  
  355.  
  356. ---------------------------------------------
  357. ---------------------------------------------
  358.  
  359. -- THE FOLLOWING TEST PROVES THAT THE ISSUE IS NOT TIED TO THE DEFAULT COLLATION OF THE CURRENT
  360. -- DATABASE. SINCE YOU AREN'T SUPPOSED TO BE ABLE TO USE A UTF8 COLLATION AT THE INSTANCE LEVEL,
  361. -- [master] SHOULD BE GUARANTEED TO NOT BE USING A "_UTF8" COLLATION.
  362.  
  363. -- AND THE ISSUE IS NOT THE SIZE OF THE COLUMN SINCE "CHAR(10)" SHOULD BE PLENTY BIG ENOUGH.
  364.  
  365.  
  366. USE [master];
  367.  
  368. SELECT TOP (100)
  369. CONVERT(BIT, 1) AS [1],
  370. CONVERT(int, 1) AS [2], -- changed to "int"
  371. CONVERT(BIT, 1) AS [3],
  372. CONVERT(int, 1) AS [4], -- changed to "int"
  373. CONVERT(CHAR(10), NULL) COLLATE Latin1_General_100_CI_AS_SC_UTF8 AS [NULL], -- added COLLATE
  374. CONVERT(BIT, 1) AS [5],
  375. NCHAR(50505) + NCHAR(20202) + N'gf' + NCHAR(10101) + NCHAR(66666) AS [6],
  376. 'abcdefghijklmnoptuvwxyz' AS [7],
  377. CONVERT(BIGINT, 1) AS [8]
  378. FROM [master].[sys].[columns] col;
  379. -- Returned over 13 rows of the following (the 13th row was the only row that was different):
  380. /*
  381. 1   2   3   4   NULL                               5      6   7    8
  382. -------------------------------------------------------------------------------------
  383. 1   1   1   1   앉仪gf❵𐑪扡摣晥桧橩汫湭灯畴睶祸ࡺ   NULL         NULL
  384. ....
  385. 1   1   1   1   앉仪gf❵𐑪扡摣晥桧橩汫湭灯畴睶祸   0               NULL
  386. */
  387.  
  388. --------------------------------------------------------------------------------------------
RAW Paste Data