Behaviour of collections of SQL_VARIANT w.r.t. structured types

[matthew-wozniczka]

From what I remember (although I can't find anything in the current spec; I think that section 3.11.3 (Response Extensions for Collection-valued Columns) needs to be expanded), the intention was that if you had a column which was a collection whose element type was a structured type, then when you opened the nested handle the collection, you would get a column for every field of the nested structured type, rather than a single column which represented the entire nested structure (and would need to be recursively explored with another SQLGetNestedHandle).

Given that, what's the behaviour of a collection of SQL_VARIANT in the following scenarios:

1. First row it's SQL_INTEGER, second row it's ADDRESS (let's say it has 3 fields, country, city, street addr)
2. First row it's ADDRESS, second row it's integer
3. First row it's ADDRESS, second rows it's COMPANY (let's say it has 2 fields, country ('same' as the field in ADDRESS, at least in all discernible metadata), company name.)

For (1), does the cursor have 4 columns (including the integer column from the first row), or 3 (first row's column goes away), or just 1 (the addr 'nested' column)?

(Assuming the answer wasn't 1) Does (2) change anything from (1)? For (3), does the 'country' column get reused for two different purposes, or does a new column appear.

[mikepizzo]

The issue here (in 1 and 2) is that we are using the same descriptor for singletons as well as structured results. If we said that the client had to call GetNestedHandle for structured members of a collection that would address problem 1 and 2 (at the expense of requiring an extra call to GetNestedHandle for each member of the collection).

Problem 3 is interesting in the general case, as it deals with heterogenous structured results. Because we say that the descriptor is preserved from one row to the next in the structured result, any time the structured result represents a variant we run into the situation where the descriptor "grows" to accommodate all columns previously seen. I.e., if you had a result that contained companies and addresses, then when you read the first row (say, company) you would get all of the columns for that row, and when you read addresses you would get the (disjoint) set of properties for an address. Presumably, the driver can choose whether to "reuse" descriptor records with the same name/type, or just have actually disjoint descriptor records.

That's kind of a weird case, so maybe that's okay, but it does make representing polymorphism a bit less optimal (in the extreme, you get all of the "properties" of the flattened hierarchy).

Rather than optimize for this (hopefully rare) case, perhaps we should treat collections of variants differently; if you have a collection of variants, if the variant represents a structured type (or collection) then you have to call GetNestedHandle on the structured (or collection-valued) property.

[mikepizzo]

Clarified the following behavior in 3.11.3, Response Extensions for Collection-valued Columns:

For typed collections of scalar values, the nested statement handle contains a single descriptor record that describes the scalar value.

For typed collections of structured values, the nested statement handle describes the columns of the structured value.

For typed collections of collections, the nested statement contains a single descriptor record on which the application calls SQLGetNestedHandle in order to get a statement handle for each nested collection.

For collections of variable typed values, the nested statement contains a single descriptor record describing the variable value. Note that this descriptor record may change from row to row within the variable typed collection, and the application should validate any binding information for the current row before calling SQLFetch or SQLFetchScroll, or use SQLGetData to retrieve primitive members of the variable typed collection. For structured or collection-valued members within a variable-typed collection, the application calls SQLGetNestedHandle in order to retrieve a statement handle on which to describe and retrieve the values of the structured or collection-valued member.

[mikepizzo]

Assigning to Dean to handle appropriately in DM.