Comparisons of structured values

[matthew-wozniczka]

Section 3.10.2 (Query Extensions for Structured Columns) states the following:

Two structured values are comparable if, and only if, they have the same structural members. For a comparison operation op between structured values SV1 and SV2, the expression SV1 op SV2 is considered true if, for every member m1 in SV1 and corresponding member m2 in SV2, m1 op m2 is true.

I don't think this is a good definition, as it will not behave how people expect:

1. A != B will evaluate to false if A = (1, 2, 3) and B = (1, 200, 300) because it is not the case that <first element of A> != <first element of B>
2. In general, it's no longer the case that A == B XOR A != B.

It's also a bit unfortunate that <, <=, >, and >= become not very useful under that definition (I think using lexicographical order would useful, if we wanted to define them at all). It may make sense to not even define these operators for structured types, or leave them implementation defined, to reduce burden on the implementation.

[mikepizzo]

I intended a simplified version of ANSI SQL comparison rules, but perhaps I over-simplified. I'll have to review ANSI and we can decide if we want to follow those semantics or not.

[mikepizzo]

Yes; looks like I oversimplified. Here is an excerpt of the actual rules from SQL2015:

If the declared types of XV and YV are row types with degree N, then let Xi, 1 (one) ≤ i ≤ N, denote a whose value and declared type is that of the i-th field of XV and let Yi denote a whose value and declared type is that of the i-th field of YV.

The result of X Y is determined as follows: 1) X = Y is True if and only if Xi = Yi is True for all i. 2) X < Y is True if and only if Xi = Yi is True for all i < n and Xn < Yn for some n. 3) X = Y is False if and only if NOT (Xi = Yi) is True for some i. 4) X < Y is False if and only if X = Y is True or Y < X is True. 5) X Y is Unknown if X Y is neither True nor False.

[mikepizzo]

Updated section to read:

Two structured values are comparable if, and only if, they have the same structural members. For two structured values SV1 and SV2. SV1 = SV2 is true if every member m1 in SV1 equals the corresponding member m2 in SV2, otherwise SV1 = SV2 is false. SV1 < SV2 is true if SV1 != SV2 and every member m1 in SV1 is less than or equal to the corresponding member m2 in SV2, otherwise SV1 < SV2 is false.

[matthew-wozniczka]

That's slightly different than the rules you gave from SQL2015 - for your updated rules, (0, 1) would not be considered less than (0, 0), where in SQL2015 it would. Is that what you meant?