mikepizzo
commented
8 years ago Added the requirement for the application to cast variant and dynamic columns to the expected type for use in expressions.
Created a separate issue #40 to add a safe-cast function.
if the type of a variant or a dynamic column doesn't match an expression, what should the behavior be? should the source support an implicit cast to the type of the expression? what if the cast fails? should we require an explicit cast?
Do we treat variants differently than dynamic columns?
For comparison, SQL_VARIANT in SQLServer is it's own type, with no implicit cast (even to the instance's base type) and all of its members are strictly ordered. So variant_col + 1 fails even if variant_col is a numeric value (you would have to do cast(variant_col as int) + 1 ), where variant_col > 5 succeeds, even if variant_col is a string (strings are less than numbers). Note that SQLServer does do implicit conversions in other cases, for example '3' < 4 is true, and '2' + 1 is three, but 'a' < 1 is an implicit conversion error.
Python seems not to do an implicit cast; if I try to add "3"+1 I get an error (have to do int("3")+1). Strings sort greater than numbers, so 4 < "3" is true.
C# doesn't allow comparisons of different types ("3",4 is a compile time error) and if one operand is a string and one an integer, casts the integer to a string) ("1"+2 = "12").
The only safe/interoperable thing to do may be to require a cast when using variant or dynamic columns in an expression. Perhaps we should define an escape for a safe cast operator that returns null if instead of a cast exception?