Per the SQL standard:
* Character literals have type CHAR (not VARCHAR)
* The type of a CASE statement is the most general type of any of its branches.
* Spaces are trimmed from CHAR values before comparing them. So, if I compare
the CHAR(8) value ‘non_aero ‘ with the CHAR(7) value ‘non_aero’ the strings are
equal.
I find CHAR values a bit creepy. They are a hangover from the days of COBOL and
punched cards. Even if you use VARCHAR for all of your character columns, CHAR
values will still crop up because of string literals. But in my experience due
to the implicit conversion rules they mostly behave themselves.
Julian
> On Mar 19, 2017, at 4:33 PM, Jess Balint <jbal...@gmail.com> wrote:
>
> Hi,
>
> I'm trying to build a query based on an existing (parsed) SQL query. I'm
> parsing this query:
>
> select
> case room_name
> when 'Meeting room for Aeronautics' then 'is_aero'
> else 'not_aero'
> end room_category
> from meeting_rooms
>
> The RexNode, after SqlToRelConverter has the "is_aero" string literal
> wrapped in a cast to a fixed-length type: CAST('is_aero'):CHAR(8)
>
> This *seems* incorrect as when compared to another value, it will be padded
> out to 'is_aero ' and a comparison with the literal 'is_aero' will fail. Is
> there a way to get this to behave more like VARCHAR earlier in the
> pipeline, possibly in the parser or SqlToRelConverter?
>
> Thanks
> Jess
