hibernate
HHH-18447 Try using native cast for string to boolean
By submitting this pull request, I confirm that my contribution is made under the terms of the Apache 2.0 license and can be relicensed under the terms of the LGPL v2.1 license in the future at the maintainers' discretion. For more information on licensing, please check here.
https://hibernate.atlassian.net/browse/HHH-18447
I don't get it: how is this going to result in portable behavior across databases?
This was just an experiment to see if databases support casting our "supported" strings to booleans, but it looks like not all of them do. Given that H2, DB2 and PostgreSQL work fine with cast(? as boolean), I am considering to change the implementation of String -> Boolean casting for these for though. It's an open question though if we want to add support for true/false and 1/0 strings for other databases as well.
wdyt @sebersole
Given that H2, DB2 and PostgreSQL work fine with cast(? as boolean), I am considering to change the implementation of String -> Boolean casting for these for though.
That's probably a bad idea. I would much prefer to tell people to write:
str='true'
or:
case str when 'true' then true else false end
instead of telling them to rely on unportable functionality which only works on three databases.
It's an open question though if we want to add support for true/false and 1/0 strings for other databases as well.
I mean that would be completely fine if we could do it in a sensible way. But I don't think there's any reasonable way to do it without producing truly horrible SQL on most databases. The query translator can't possibly know upfront what is the format of this string, whether it's true / false, T / F, Y / N, or something else.
[I already mentioned all this in the discussion on the issue.]
But I don't think there's any reasonable way to do it without producing truly horrible SQL on most databases.
Actually I retract that. Your suggested SQL on the issue isn't that terrible.
See my comment here: https://hibernate.atlassian.net/browse/HHH-18447?focusedCommentId=116227
I think we should IMO fail a query like cast would do if one tries to cast an unsupported string value to a boolean, so I crafted a special query expression which will decode values but fail (by returning more than one value from a subquery) if the string value does not match any of the allowed values.
It's not pretty, but does the job. Let me know what you think about that @gavinking @sebersole
It might be messy, but we can always clean things up after. I'm going to apply this as I am trying to get 6.6.1 released and I think this is a good change to get in. I pulled it locally to do the rebase.
