CODEWITHSUNDEEP
substringazure-data-lakeu-sqloutofrangeexception
Venkataraman R
Venkataraman R

Reputation: 12959

U-SQL Substring out of range exception

We are trying to publish data from a table using U-SQL in ADLA. We are using below code to get substring for FirstName, as we want to restrict the length of FirstName to 50 characters. 

SELECT (firstName == null || firstName.Length <= 50) ? firstName : firstName.Substring(0, 50) AS FirstName

But, we are getting below error:

E_RUNTIME_USER_EXPRESSION_EVALUATION > E_RUNTIME_USER_SUBSTRING_OUT_RANGE

When we tried to perform substring using custom .NET Code, we are not getting exception. The job is completing successfully. We are not getting any error rows. 

public static string DoSubString(string firstName)
        {
            string subFirstName;

            try
            {
                subFirstName = (firstName == null || firstName.Length <= 50) ? firstName : firstName.Substring(0, 50);

            }
            catch(ArgumentOutOfRangeException ae)
            {
                subFirstName = string.Format("Argument Out of range Error {0} {1}",firstName,ae.Message);
            }
            catch(Exception Ex)
            {
                subFirstName = string.Format("Generic Error {0} {1}",firstName, Ex.Message); 
            }

            return subFirstName;

        }

We are not able to find out the error row. When we look into Profile.xml, we are not getting row dump.

Inner exception from user expression: \nCurrent row dump: "

How to find out the error row causing this exception? How to troubleshoot these kinds of issues ?

Upvotes: 0

Views: 837

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12959

We raised an issue with product group. It seems there is some problem with the substring operation in U-SQL. They asked us to apply the below fix for Substring calculation. We tried it and it is working properly.

SELECT (firstName == null || firstName.Length <= 50) ? firstName : firstName.Substring(0, Math.Min(firstName.Length, 50)) AS FirstName

Upvotes: 2

David Paul Giroux
David Paul Giroux

Reputation: 657

For testing I used 10 characters, revise for 50.

@table = 
SELECT * FROM 
    ( VALUES
    ("appleJackss"),
    ("apple Jacks"),
    ("appleJacks"),
    ("                 "),
    (""),
    ((string)null)
    ) AS T(word);

@result = 
SELECT  //Method 1
        CASE
            WHEN word.Length <= 10 THEN word
            ELSE word.Substring(0, 10)
        END AS justTen,

        // Method 2
        (word.Length <= 10) ? word : word.Substring(0, 10) AS anotherTen
FROM @table;

OUTPUT @result
TO "/Temp/Example1.txt"
USING Outputters.Tsv();

Upvotes: 2

Related Questions