If you have a table with a varchar column containing different kinds of values (int, date, string, float) all converted to a string and you then want to join the int value to a different table. I'm getting errors like "Cannot convert "some date value" to a numeric".
Setup the tables:
The result I am looking for:
Result 1, 2, 3 and 4:
Result 5: This doesn't crash but it doesn't give the result I am looking for (as expected because IsDate('1') returns true)
Extra note: In the actual case I'm using this for I could really use the available PK/FK indexes for performance reasons and I would rather not create additional indexes like "cast(JoinId.Id as varchar)" as suggested in Volkers answer.
Tested with ASA 126.96.36.1993
What version do you use?
With 188.8.131.5203, all 5 queries do succeed and do return the desired result (although queries 2 and 3 look similar to me...).
That being said, in general when the query engine seems to try to do undesired casts and fails, I'd usually try to adapt the "comparison goal" accordingly, such as
select * from TestData left outer join JoinId on TestData.Fieldname = 'Id' and cast(JoinId.Id as varchar) = TestData.TestData
That would even work if you would attempt to join over a different row that has no int value, such as TestData.Fieldname = 'Date'.
While a little late to the thread . . . A simpler rewrite might be this one:
The CASTing (or alternatively a Convert()) of JoinId.Id to a character string avoids the implicit/automatic conversion that is exposing this issue.
I'm assuming 12.0.x did something similar when evaluating the original predicate.
Both types of automatic conversion are feasible here, ie:
and since this predicate 'could operate' either way (as a numeric comparison or a character comparison) there seems to be no preferred bias to either.
I'm not quite certain if this is exactly a bug or not. Treating all such comparisons as numeric comparisons whenever possible/feasible can be significantly more efficient than assuming every such expression to be a character string comparison.
answered 11 Jul '16, 17:53
Nick Elson S...