Skip to content

Instantly share code, notes, and snippets.

@danielrbradley
Last active March 2, 2021 10:38
Show Gist options
  • Save danielrbradley/2953f71d8f9bee5d8e78331d98fad9d0 to your computer and use it in GitHub Desktop.
Save danielrbradley/2953f71d8f9bee5d8e78331d98fad9d0 to your computer and use it in GitHub Desktop.
Reproduction of Stream Analytics Query UDF integer overflow
{
"$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"storageAccounts_intrepro_name": {
"type": "String"
},
"streamingjobs_int_repro_name": {
"type": "String"
}
},
"variables": {},
"resources": [
{
"type": "Microsoft.Storage/storageAccounts",
"apiVersion": "2020-08-01-preview",
"name": "[parameters('storageAccounts_intrepro_name')]",
"location": "uksouth",
"sku": {
"name": "Standard_LRS",
"tier": "Standard"
},
"kind": "BlobStorage",
"properties": {
"minimumTlsVersion": "TLS1_2",
"allowBlobPublicAccess": true,
"networkAcls": {
"bypass": "AzureServices",
"virtualNetworkRules": [],
"ipRules": [],
"defaultAction": "Allow"
},
"supportsHttpsTrafficOnly": true,
"encryption": {
"services": {
"file": {
"keyType": "Account",
"enabled": true
},
"blob": {
"keyType": "Account",
"enabled": true
}
},
"keySource": "Microsoft.Storage"
},
"accessTier": "Hot"
}
},
{
"type": "Microsoft.StreamAnalytics/streamingjobs",
"apiVersion": "2017-04-01-preview",
"name": "[parameters('streamingjobs_int_repro_name')]",
"location": "UK South",
"identity": {
"principalId": "82074428-5d3d-482f-97e9-f87c6d500d99",
"tenantId": "78ade5b2-2582-4cdd-b7c6-587ba2187324",
"type": "SystemAssigned"
},
"properties": {
"sku": {
"name": "Standard"
},
"outputStartMode": "CustomTime",
"outputStartTime": "2021-03-02T09:00:00Z",
"eventsOutOfOrderPolicy": "Adjust",
"outputErrorPolicy": "Stop",
"eventsOutOfOrderMaxDelayInSeconds": 0,
"eventsLateArrivalMaxDelayInSeconds": 5,
"dataLocale": "en-US",
"compatibilityLevel": "1.2",
"contentStoragePolicy": "SystemAccount",
"jobType": "Cloud"
}
},
{
"type": "Microsoft.Storage/storageAccounts/blobServices",
"apiVersion": "2020-08-01-preview",
"name": "[concat(parameters('storageAccounts_intrepro_name'), '/default')]",
"dependsOn": [
"[resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccounts_intrepro_name'))]"
],
"sku": {
"name": "Standard_LRS",
"tier": "Standard"
},
"properties": {
"cors": {
"corsRules": []
},
"deleteRetentionPolicy": {
"enabled": false
}
}
},
{
"type": "Microsoft.Storage/storageAccounts/tableServices",
"apiVersion": "2020-08-01-preview",
"name": "[concat(parameters('storageAccounts_intrepro_name'), '/default')]",
"dependsOn": [
"[resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccounts_intrepro_name'))]"
],
"properties": {
"cors": {
"corsRules": []
}
}
},
{
"type": "Microsoft.StreamAnalytics/streamingjobs/functions",
"apiVersion": "2017-04-01-preview",
"name": "[concat(parameters('streamingjobs_int_repro_name'), '/intTest')]",
"dependsOn": [
"[resourceId('Microsoft.StreamAnalytics/streamingjobs', parameters('streamingjobs_int_repro_name'))]"
],
"properties": {
"type": "Scalar",
"properties": {
"inputs": [
{
"dataType": "any"
}
],
"output": {
"dataType": "bigint"
},
"binding": {
"type": "Microsoft.StreamAnalytics/JavascriptUdf",
"properties": {
"script": "// Sample UDF which returns sum of two values.\nfunction main(arg1) {\n return Number.parseInt(`4147483647`);\n}"
}
}
}
}
},
{
"type": "Microsoft.StreamAnalytics/streamingjobs/inputs",
"apiVersion": "2017-04-01-preview",
"name": "[concat(parameters('streamingjobs_int_repro_name'), '/input')]",
"dependsOn": [
"[resourceId('Microsoft.StreamAnalytics/streamingjobs', parameters('streamingjobs_int_repro_name'))]"
],
"properties": {
"type": "Stream",
"datasource": {
"type": "Microsoft.Storage/Blob",
"properties": {
"sourcePartitionCount": 1,
"storageAccounts": [
{
"accountName": "intrepro"
}
],
"container": "input",
"pathPattern": "{date}/{time}",
"dateFormat": "yyyy-MM-dd",
"timeFormat": "HH"
}
},
"compression": {
"type": "None"
},
"serialization": {
"type": "Csv",
"properties": {
"fieldDelimiter": ",",
"encoding": "UTF8"
}
}
}
},
{
"type": "Microsoft.StreamAnalytics/streamingjobs/outputs",
"apiVersion": "2017-04-01-preview",
"name": "[concat(parameters('streamingjobs_int_repro_name'), '/output')]",
"dependsOn": [
"[resourceId('Microsoft.StreamAnalytics/streamingjobs', parameters('streamingjobs_int_repro_name'))]"
],
"properties": {
"datasource": {
"type": "Microsoft.Storage/Blob",
"properties": {
"storageAccounts": [
{
"accountName": "intrepro"
}
],
"container": "outupt",
"pathPattern": "{date}/{time}",
"dateFormat": "yyyy-MM-dd",
"timeFormat": "HH",
"authenticationMode": "ConnectionString"
}
},
"serialization": {
"type": "Json",
"properties": {
"encoding": "UTF8",
"format": "LineSeparated"
}
}
}
},
{
"type": "Microsoft.Storage/storageAccounts/blobServices/containers",
"apiVersion": "2020-08-01-preview",
"name": "[concat(parameters('storageAccounts_intrepro_name'), '/default/input')]",
"dependsOn": [
"[resourceId('Microsoft.Storage/storageAccounts/blobServices', parameters('storageAccounts_intrepro_name'), 'default')]",
"[resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccounts_intrepro_name'))]"
],
"properties": {
"defaultEncryptionScope": "$account-encryption-key",
"denyEncryptionScopeOverride": false,
"publicAccess": "None"
}
},
{
"type": "Microsoft.Storage/storageAccounts/blobServices/containers",
"apiVersion": "2020-08-01-preview",
"name": "[concat(parameters('storageAccounts_intrepro_name'), '/default/outupt')]",
"dependsOn": [
"[resourceId('Microsoft.Storage/storageAccounts/blobServices', parameters('storageAccounts_intrepro_name'), 'default')]",
"[resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccounts_intrepro_name'))]"
],
"properties": {
"defaultEncryptionScope": "$account-encryption-key",
"denyEncryptionScopeOverride": false,
"publicAccess": "None"
}
}
]
}
We can make this file beautiful and searchable if this error is corrected: No commas found in this CSV file in line 0.
input
1
2
3
4
5
6
7
8
9
10
{"input":"1","UdfOut":-147483649}
{"input":"2","UdfOut":-147483649}
{"input":"3","UdfOut":-147483649}
{"input":"4","UdfOut":-147483649}
{"input":"5","UdfOut":-147483649}
{"input":"6","UdfOut":-147483649}
{"input":"7","UdfOut":-147483649}
{"input":"8","UdfOut":-147483649}
{"input":"9","UdfOut":-147483649}
{"input":"10","UdfOut":-147483649}
[
{
"input": "10",
"UdfOut": 4147483647
},
{
"input": "9",
"UdfOut": 4147483647
},
{
"input": "8",
"UdfOut": 4147483647
},
{
"input": "7",
"UdfOut": 4147483647
},
{
"input": "6",
"UdfOut": 4147483647
},
{
"input": "5",
"UdfOut": 4147483647
},
{
"input": "4",
"UdfOut": 4147483647
},
{
"input": "3",
"UdfOut": 4147483647
},
{
"input": "2",
"UdfOut": 4147483647
},
{
"input": "1",
"UdfOut": 4147483647
}
]

When returning a number from a UDF function which is greater than 2,147,483,647 but less than long.MaxValue and bound to a Stream Analytics bigint type then the number is shown as a negative which indicates that the integer has been overflowed. This error does not happen when using the local or portal query tester.

The workaround we've used to return the value as a string. However, this behaviour does not match the documentation: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-javascript-user-defined-functions#stream-analytics-and-javascript-type-conversion

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment