Issue with data table Select statement

Question

The following VB line, where _DSversionInfo is a DataSet, returns no rows:

_DSversionInfo.Tables("VersionInfo").Select("FileID=88")

but inspection shows that the table contains rows with FileID's of 92, 93, 94, 90, 88, 89, 215, 216. The table columns are all of type string.

Further investigation showed that using the ID of 88, 215 and 216 will only return rows if the number is quoted.

ie _DSversionInfo.Tables("VersionInfo").Select("FileID='88'")

All other rows work regardless of whether the number is quoted or not.

Anyone got an explanation of why this would happen for some numbers but not others? I understand that the numbers should be quoted just not why some work and others don't?

I discovered this in some VB.NET code but (despite my initial finger pointing) don't think it is VB.NET specific.

Answer 1

According to the MSDN documentation on building expressions, strings should always be quoted. Failing to do so produces some bizarro unpredictable behavior... You should quote your number strings to get predictable and proper behavior like the documentation says.

I've encounted what you're describing in the past, and kinda tried to figure it out - here, pop open your favorite .NET editor and try the following:

Create a DataTable, and into a string column 'Stuff' of that DataSet, insert rows in the following order: "6", "74", "710", and Select with the filter expression "Stuff = 710". You will get 1 row back. Now, change the first row into any number greater than 7 - suddenly, you get 0 rows back.

As long as the numbers are ordered in proper descending order using string ordering logic (i.e., 7 comes after 599) the unquoted query appears to work.

My guess is that this is a limitation of how DataSet filter expressions are parsed, and it wasn't meant to work this way...

The Code:

            // Unquoted filter string bizzareness.
            var table = new DataTable();

            table.Columns.Add(new DataColumn("NumbersAsString", typeof(String)));

            var row1 = table.NewRow(); row1["NumbersAsString"] = "9"; table.Rows.Add(row1); // Change to '66
            var row2 = table.NewRow(); row2["NumbersAsString"] = "74"; table.Rows.Add(row2);
            var row4 = table.NewRow(); row4["NumbersAsString"] = "90"; table.Rows.Add(row4);
            var row3 = table.NewRow(); row3["NumbersAsString"] = "710"; table.Rows.Add(row3);

            var results = table.Select("NumbersAsString = 710"); // Returns 0 rows.
            var results2 = table.Select("NumbersAsString = 74"); // Throws exception "Min (1) must be less than or equal to max (-1) in a Range object." at System.Data.Select.GetBinaryFilteredRecords()

Conclusion: Based on the exception text in that last case, there appears to be some wierd casting going on inside filter expressions that is not guaranteed to be safe. Explicitely putting single quotes around the value for which you're querying avoids this problem by letting .NET know that this is a literal.

Answer 2

DataTable builds an index on the columns to make Select() queries fast. That index is sorted by value, then it uses a binary search to select the range of records that matches the query expression.

So the records will be sorted like this 215,216,88,89,90,92,93,94. A binary search is done treating them as integer (as per our filter expression) cannot locate certain records because, it is designed to only search properly sorted collections.

It indexes the data as string and Binary search searches as number. See the below explanation.

        string[] strArr = new string[] { "115", "118", "66", "77", "80", "81", "82" };
        int[] intArr = new int[] { 215, 216, 88, 89, 90, 92, 93, 94 };
        int i88 = Array.BinarySearch(intArr, 88); //returns -ve index
        int i89 = Array.BinarySearch(intArr, 89); //returns +ve index

This should be a bug in the framework.