You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Piotr Nowojski <pi...@data-artisans.com> on 2018/09/06 11:03:37 UTC
VARCHAR literals
Hi,
We have small problem with CHAR type in Flink. Officially we do not support it and all input/output columns are of type VARCHAR. Because of that, nobody has ever thought about CHAR semantic (for example correctly handling padding in comparisons or other functions). However this collides with a teeny tiny problem that in Calcite string literals are of type CHAR. This leads to not so funny inconsistencies in queries and incorrect results.
I wonder if we could provide a switch in Calcite to change the type of String literals to VARCHAR? I know that this is against the SQL standard, however quite a few databases are doing so for various reasons . One of them is that providing proper CHAR support can be tricky and nowadays it doesn’t provide much value to the user. I have seen quite often the pattern that some new db starts without CHAR support at all and add it only later (if ever). Providing such switch in Calcite would allow Calcite users do the same thing.
I was thinking about alternative workaround - rewriting query plan and changing all of the CHAR types to VARCHAR on our side, but this seems like not that easy thing to do. But maybe I’m wrong and there is an easy way to do so on our side?
Thanks, Piotrek
Re: VARCHAR literals
Posted by Andrew Pilloud <ap...@google.com.INVALID>.
Just ran into CALCITE-2321 in Beam, this is something we would be
interested in as well.
Andrew
On Tue, Sep 11, 2018 at 7:47 AM Piotr Nowojski <pi...@data-artisans.com>
wrote:
> Thanks! We will look into it. If we decide to go this path we will create
> a JIRA ticket for this.
>
> Piotrek
>
> > On 10 Sep 2018, at 19:12, Julian Hyde <jh...@apache.org> wrote:
> >
> > Yes, as long as it is fully tested and documented. This is not a one
> line fix. It’s quite a major feature, and if it’s not done properly Calcite
> will be picking up the pieces for years.
> >
> > Julian
> >
> >
> >> On Sep 10, 2018, at 1:26 AM, Piotr Nowojski <pi...@data-artisans.com>
> wrote:
> >>
> >> Julian, would you be ok if we provided a contribution containing a
> configurable switch for string literal type?
> >>
> >> Piotrek
> >>
> >>> On 6 Sep 2018, at 20:47, Piotr Nowojski <pi...@data-artisans.com>
> wrote:
> >>>
> >>> We know about this switch. Unfortunately it only solves/hides one of
> the problems.
> >>>
> >>>> On 6 Sep 2018, at 20:02, Julian Hyde <jh...@apache.org> wrote:
> >>>>
> >>>> Does https://issues.apache.org/jira/browse/CALCITE-2321 <
> https://issues.apache.org/jira/browse/CALCITE-2321> help?
> >>>>
> >>>>
> >>>>> On Sep 6, 2018, at 4:03 AM, Piotr Nowojski <pi...@data-artisans.com>
> wrote:
> >>>>>
> >>>>> Hi,
> >>>>>
> >>>>> We have small problem with CHAR type in Flink. Officially we do not
> support it and all input/output columns are of type VARCHAR. Because of
> that, nobody has ever thought about CHAR semantic (for example correctly
> handling padding in comparisons or other functions). However this collides
> with a teeny tiny problem that in Calcite string literals are of type CHAR.
> This leads to not so funny inconsistencies in queries and incorrect results.
> >>>>>
> >>>>> I wonder if we could provide a switch in Calcite to change the type
> of String literals to VARCHAR? I know that this is against the SQL
> standard, however quite a few databases are doing so for various reasons .
> One of them is that providing proper CHAR support can be tricky and
> nowadays it doesn’t provide much value to the user. I have seen quite often
> the pattern that some new db starts without CHAR support at all and add it
> only later (if ever). Providing such switch in Calcite would allow Calcite
> users do the same thing.
> >>>>>
> >>>>> I was thinking about alternative workaround - rewriting query plan
> and changing all of the CHAR types to VARCHAR on our side, but this seems
> like not that easy thing to do. But maybe I’m wrong and there is an easy
> way to do so on our side?
> >>>>>
> >>>>> Thanks, Piotrek
> >>>>
> >>>
> >>
> >
>
>
Re: VARCHAR literals
Posted by Piotr Nowojski <pi...@data-artisans.com>.
Thanks! We will look into it. If we decide to go this path we will create a JIRA ticket for this.
Piotrek
> On 10 Sep 2018, at 19:12, Julian Hyde <jh...@apache.org> wrote:
>
> Yes, as long as it is fully tested and documented. This is not a one line fix. It’s quite a major feature, and if it’s not done properly Calcite will be picking up the pieces for years.
>
> Julian
>
>
>> On Sep 10, 2018, at 1:26 AM, Piotr Nowojski <pi...@data-artisans.com> wrote:
>>
>> Julian, would you be ok if we provided a contribution containing a configurable switch for string literal type?
>>
>> Piotrek
>>
>>> On 6 Sep 2018, at 20:47, Piotr Nowojski <pi...@data-artisans.com> wrote:
>>>
>>> We know about this switch. Unfortunately it only solves/hides one of the problems.
>>>
>>>> On 6 Sep 2018, at 20:02, Julian Hyde <jh...@apache.org> wrote:
>>>>
>>>> Does https://issues.apache.org/jira/browse/CALCITE-2321 <https://issues.apache.org/jira/browse/CALCITE-2321> help?
>>>>
>>>>
>>>>> On Sep 6, 2018, at 4:03 AM, Piotr Nowojski <pi...@data-artisans.com> wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> We have small problem with CHAR type in Flink. Officially we do not support it and all input/output columns are of type VARCHAR. Because of that, nobody has ever thought about CHAR semantic (for example correctly handling padding in comparisons or other functions). However this collides with a teeny tiny problem that in Calcite string literals are of type CHAR. This leads to not so funny inconsistencies in queries and incorrect results.
>>>>>
>>>>> I wonder if we could provide a switch in Calcite to change the type of String literals to VARCHAR? I know that this is against the SQL standard, however quite a few databases are doing so for various reasons . One of them is that providing proper CHAR support can be tricky and nowadays it doesn’t provide much value to the user. I have seen quite often the pattern that some new db starts without CHAR support at all and add it only later (if ever). Providing such switch in Calcite would allow Calcite users do the same thing.
>>>>>
>>>>> I was thinking about alternative workaround - rewriting query plan and changing all of the CHAR types to VARCHAR on our side, but this seems like not that easy thing to do. But maybe I’m wrong and there is an easy way to do so on our side?
>>>>>
>>>>> Thanks, Piotrek
>>>>
>>>
>>
>
Re: VARCHAR literals
Posted by Julian Hyde <jh...@apache.org>.
Yes, as long as it is fully tested and documented. This is not a one line fix. It’s quite a major feature, and if it’s not done properly Calcite will be picking up the pieces for years.
Julian
> On Sep 10, 2018, at 1:26 AM, Piotr Nowojski <pi...@data-artisans.com> wrote:
>
> Julian, would you be ok if we provided a contribution containing a configurable switch for string literal type?
>
> Piotrek
>
>> On 6 Sep 2018, at 20:47, Piotr Nowojski <pi...@data-artisans.com> wrote:
>>
>> We know about this switch. Unfortunately it only solves/hides one of the problems.
>>
>>> On 6 Sep 2018, at 20:02, Julian Hyde <jh...@apache.org> wrote:
>>>
>>> Does https://issues.apache.org/jira/browse/CALCITE-2321 <https://issues.apache.org/jira/browse/CALCITE-2321> help?
>>>
>>>
>>>> On Sep 6, 2018, at 4:03 AM, Piotr Nowojski <pi...@data-artisans.com> wrote:
>>>>
>>>> Hi,
>>>>
>>>> We have small problem with CHAR type in Flink. Officially we do not support it and all input/output columns are of type VARCHAR. Because of that, nobody has ever thought about CHAR semantic (for example correctly handling padding in comparisons or other functions). However this collides with a teeny tiny problem that in Calcite string literals are of type CHAR. This leads to not so funny inconsistencies in queries and incorrect results.
>>>>
>>>> I wonder if we could provide a switch in Calcite to change the type of String literals to VARCHAR? I know that this is against the SQL standard, however quite a few databases are doing so for various reasons . One of them is that providing proper CHAR support can be tricky and nowadays it doesn’t provide much value to the user. I have seen quite often the pattern that some new db starts without CHAR support at all and add it only later (if ever). Providing such switch in Calcite would allow Calcite users do the same thing.
>>>>
>>>> I was thinking about alternative workaround - rewriting query plan and changing all of the CHAR types to VARCHAR on our side, but this seems like not that easy thing to do. But maybe I’m wrong and there is an easy way to do so on our side?
>>>>
>>>> Thanks, Piotrek
>>>
>>
>
Re: VARCHAR literals
Posted by Piotr Nowojski <pi...@data-artisans.com>.
Julian, would you be ok if we provided a contribution containing a configurable switch for string literal type?
Piotrek
> On 6 Sep 2018, at 20:47, Piotr Nowojski <pi...@data-artisans.com> wrote:
>
> We know about this switch. Unfortunately it only solves/hides one of the problems.
>
>> On 6 Sep 2018, at 20:02, Julian Hyde <jh...@apache.org> wrote:
>>
>> Does https://issues.apache.org/jira/browse/CALCITE-2321 <https://issues.apache.org/jira/browse/CALCITE-2321> help?
>>
>>
>>> On Sep 6, 2018, at 4:03 AM, Piotr Nowojski <pi...@data-artisans.com> wrote:
>>>
>>> Hi,
>>>
>>> We have small problem with CHAR type in Flink. Officially we do not support it and all input/output columns are of type VARCHAR. Because of that, nobody has ever thought about CHAR semantic (for example correctly handling padding in comparisons or other functions). However this collides with a teeny tiny problem that in Calcite string literals are of type CHAR. This leads to not so funny inconsistencies in queries and incorrect results.
>>>
>>> I wonder if we could provide a switch in Calcite to change the type of String literals to VARCHAR? I know that this is against the SQL standard, however quite a few databases are doing so for various reasons . One of them is that providing proper CHAR support can be tricky and nowadays it doesn’t provide much value to the user. I have seen quite often the pattern that some new db starts without CHAR support at all and add it only later (if ever). Providing such switch in Calcite would allow Calcite users do the same thing.
>>>
>>> I was thinking about alternative workaround - rewriting query plan and changing all of the CHAR types to VARCHAR on our side, but this seems like not that easy thing to do. But maybe I’m wrong and there is an easy way to do so on our side?
>>>
>>> Thanks, Piotrek
>>
>
Re: VARCHAR literals
Posted by Piotr Nowojski <pi...@data-artisans.com>.
We know about this switch. Unfortunately it only solves/hides one of the problems.
> On 6 Sep 2018, at 20:02, Julian Hyde <jh...@apache.org> wrote:
>
> Does https://issues.apache.org/jira/browse/CALCITE-2321 <https://issues.apache.org/jira/browse/CALCITE-2321> help?
>
>
>> On Sep 6, 2018, at 4:03 AM, Piotr Nowojski <pi...@data-artisans.com> wrote:
>>
>> Hi,
>>
>> We have small problem with CHAR type in Flink. Officially we do not support it and all input/output columns are of type VARCHAR. Because of that, nobody has ever thought about CHAR semantic (for example correctly handling padding in comparisons or other functions). However this collides with a teeny tiny problem that in Calcite string literals are of type CHAR. This leads to not so funny inconsistencies in queries and incorrect results.
>>
>> I wonder if we could provide a switch in Calcite to change the type of String literals to VARCHAR? I know that this is against the SQL standard, however quite a few databases are doing so for various reasons . One of them is that providing proper CHAR support can be tricky and nowadays it doesn’t provide much value to the user. I have seen quite often the pattern that some new db starts without CHAR support at all and add it only later (if ever). Providing such switch in Calcite would allow Calcite users do the same thing.
>>
>> I was thinking about alternative workaround - rewriting query plan and changing all of the CHAR types to VARCHAR on our side, but this seems like not that easy thing to do. But maybe I’m wrong and there is an easy way to do so on our side?
>>
>> Thanks, Piotrek
>
Re: VARCHAR literals
Posted by Julian Hyde <jh...@apache.org>.
Does https://issues.apache.org/jira/browse/CALCITE-2321 <https://issues.apache.org/jira/browse/CALCITE-2321> help?
> On Sep 6, 2018, at 4:03 AM, Piotr Nowojski <pi...@data-artisans.com> wrote:
>
> Hi,
>
> We have small problem with CHAR type in Flink. Officially we do not support it and all input/output columns are of type VARCHAR. Because of that, nobody has ever thought about CHAR semantic (for example correctly handling padding in comparisons or other functions). However this collides with a teeny tiny problem that in Calcite string literals are of type CHAR. This leads to not so funny inconsistencies in queries and incorrect results.
>
> I wonder if we could provide a switch in Calcite to change the type of String literals to VARCHAR? I know that this is against the SQL standard, however quite a few databases are doing so for various reasons . One of them is that providing proper CHAR support can be tricky and nowadays it doesn’t provide much value to the user. I have seen quite often the pattern that some new db starts without CHAR support at all and add it only later (if ever). Providing such switch in Calcite would allow Calcite users do the same thing.
>
> I was thinking about alternative workaround - rewriting query plan and changing all of the CHAR types to VARCHAR on our side, but this seems like not that easy thing to do. But maybe I’m wrong and there is an easy way to do so on our side?
>
> Thanks, Piotrek