You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Jacques Nadeau <ja...@apache.org> on 2016/09/27 18:11:39 UTC
RFC: Adding Double Colon Cast Syntax
Hey Everybody,
I'd like to propose adding the double colon cast syntax to Calcite (e.g.
'1'::INTEGER instead of CAST('1' as INTEGER). It's something that Postgres,
DB2, Vertica (PG deriv), RedShift (PG deriv) and a number of other systems
support. Has anybody explored this or previously opened a jira (I searched
and didn't find any)? Any thoughts on lexical ambiguities?
thanks,
Jacques
Re: RFC: Adding Double Colon Cast Syntax
Posted by Jacques Nadeau <ja...@apache.org>.
I opened https://issues.apache.org/jira/browse/CALCITE-1432 to track any
progress.
Jordan, do you mind sharing your code there?
Julien: I don't remember offhand which db2 supports this. I feel like it
may have formed part of the basis for their JSON/Mongo integration a few
years ago but this is all from dim memories...
thanks,
jacques
On Wed, Sep 28, 2016 at 3:44 PM, Jordan Halterman <
jordan.halterman@gmail.com> wrote:
> Interesting history lesson! I'm certainly not one to deny code to the open
> source community...
>
> On Tue, Sep 27, 2016 at 12:35 PM, Julian Hyde <jh...@apache.org> wrote:
>
> > For the younger folks… it came from a crazy time in the mid 90s when
> > “object-oriented database” was the holy grail, and someone had the bright
> > idea of making SQL syntax look like C++. Stonebraker and co. added
> > object-oriented features to Ingres, called it Illustra, sold Illustra to
> > Informix, and Informix choked digesting too large a meal, and IBM picked
> up
> > the pieces. So, I consider it a vestige, and the only advantage of
> > supporting it is for users who have existing SQL.
> >
> > I think we should do it, as long as it is enabled using a conformance
> > setting (disabled by default), and if we can do it without making other
> > parts of the code more complex to maintain.
> >
> > I’d also like to see an analysis of the scope of the task before we
> start.
> > Is it just cosmetic sugar for CAST, or are there semantic differences?
> For
> > example are there some types that can only be converted to using :: or
> > CAST? Does :: fit neatly into the existing parser?
> >
> > Jacques, Is double-colon supported on all variants of DB2? From what I
> can
> > tell, it an alternative to CAST only on some variants of DB2 (presumably
> > those derived from Informix/Illustra).
> >
> > Jordan, Would you consider contributing back? It will make your life
> > easier as we maintain code, and you know it’s the right thing to do. :)
> >
> > Julian
> >
> >
> > > On Sep 27, 2016, at 11:46 AM, jordan.halterman@gmail.com wrote:
> > >
> > > We added the double-colon syntax to our own fork of the Calcite grammar
> > to placate our analysts and their addiction to Redshift. TBH it was not
> > easy, and our implementation still doesn't support things like casting
> from
> > a scalar subquery. Essentially, you can cast identifiers and function
> > results. But I'm sure the folks around here could figure it out.
> > >
> > >> On Sep 27, 2016, at 11:11 AM, Jacques Nadeau <ja...@apache.org>
> > wrote:
> > >>
> > >> Hey Everybody,
> > >>
> > >> I'd like to propose adding the double colon cast syntax to Calcite
> (e.g.
> > >> '1'::INTEGER instead of CAST('1' as INTEGER). It's something that
> > Postgres,
> > >> DB2, Vertica (PG deriv), RedShift (PG deriv) and a number of other
> > systems
> > >> support. Has anybody explored this or previously opened a jira (I
> > searched
> > >> and didn't find any)? Any thoughts on lexical ambiguities?
> > >>
> > >> thanks,
> > >> Jacques
> >
> >
>
Re: RFC: Adding Double Colon Cast Syntax
Posted by Jordan Halterman <jo...@gmail.com>.
Interesting history lesson! I'm certainly not one to deny code to the open
source community...
On Tue, Sep 27, 2016 at 12:35 PM, Julian Hyde <jh...@apache.org> wrote:
> For the younger folks… it came from a crazy time in the mid 90s when
> “object-oriented database” was the holy grail, and someone had the bright
> idea of making SQL syntax look like C++. Stonebraker and co. added
> object-oriented features to Ingres, called it Illustra, sold Illustra to
> Informix, and Informix choked digesting too large a meal, and IBM picked up
> the pieces. So, I consider it a vestige, and the only advantage of
> supporting it is for users who have existing SQL.
>
> I think we should do it, as long as it is enabled using a conformance
> setting (disabled by default), and if we can do it without making other
> parts of the code more complex to maintain.
>
> I’d also like to see an analysis of the scope of the task before we start.
> Is it just cosmetic sugar for CAST, or are there semantic differences? For
> example are there some types that can only be converted to using :: or
> CAST? Does :: fit neatly into the existing parser?
>
> Jacques, Is double-colon supported on all variants of DB2? From what I can
> tell, it an alternative to CAST only on some variants of DB2 (presumably
> those derived from Informix/Illustra).
>
> Jordan, Would you consider contributing back? It will make your life
> easier as we maintain code, and you know it’s the right thing to do. :)
>
> Julian
>
>
> > On Sep 27, 2016, at 11:46 AM, jordan.halterman@gmail.com wrote:
> >
> > We added the double-colon syntax to our own fork of the Calcite grammar
> to placate our analysts and their addiction to Redshift. TBH it was not
> easy, and our implementation still doesn't support things like casting from
> a scalar subquery. Essentially, you can cast identifiers and function
> results. But I'm sure the folks around here could figure it out.
> >
> >> On Sep 27, 2016, at 11:11 AM, Jacques Nadeau <ja...@apache.org>
> wrote:
> >>
> >> Hey Everybody,
> >>
> >> I'd like to propose adding the double colon cast syntax to Calcite (e.g.
> >> '1'::INTEGER instead of CAST('1' as INTEGER). It's something that
> Postgres,
> >> DB2, Vertica (PG deriv), RedShift (PG deriv) and a number of other
> systems
> >> support. Has anybody explored this or previously opened a jira (I
> searched
> >> and didn't find any)? Any thoughts on lexical ambiguities?
> >>
> >> thanks,
> >> Jacques
>
>
Re: RFC: Adding Double Colon Cast Syntax
Posted by Julian Hyde <jh...@apache.org>.
For the younger folks… it came from a crazy time in the mid 90s when “object-oriented database” was the holy grail, and someone had the bright idea of making SQL syntax look like C++. Stonebraker and co. added object-oriented features to Ingres, called it Illustra, sold Illustra to Informix, and Informix choked digesting too large a meal, and IBM picked up the pieces. So, I consider it a vestige, and the only advantage of supporting it is for users who have existing SQL.
I think we should do it, as long as it is enabled using a conformance setting (disabled by default), and if we can do it without making other parts of the code more complex to maintain.
I’d also like to see an analysis of the scope of the task before we start. Is it just cosmetic sugar for CAST, or are there semantic differences? For example are there some types that can only be converted to using :: or CAST? Does :: fit neatly into the existing parser?
Jacques, Is double-colon supported on all variants of DB2? From what I can tell, it an alternative to CAST only on some variants of DB2 (presumably those derived from Informix/Illustra).
Jordan, Would you consider contributing back? It will make your life easier as we maintain code, and you know it’s the right thing to do. :)
Julian
> On Sep 27, 2016, at 11:46 AM, jordan.halterman@gmail.com wrote:
>
> We added the double-colon syntax to our own fork of the Calcite grammar to placate our analysts and their addiction to Redshift. TBH it was not easy, and our implementation still doesn't support things like casting from a scalar subquery. Essentially, you can cast identifiers and function results. But I'm sure the folks around here could figure it out.
>
>> On Sep 27, 2016, at 11:11 AM, Jacques Nadeau <ja...@apache.org> wrote:
>>
>> Hey Everybody,
>>
>> I'd like to propose adding the double colon cast syntax to Calcite (e.g.
>> '1'::INTEGER instead of CAST('1' as INTEGER). It's something that Postgres,
>> DB2, Vertica (PG deriv), RedShift (PG deriv) and a number of other systems
>> support. Has anybody explored this or previously opened a jira (I searched
>> and didn't find any)? Any thoughts on lexical ambiguities?
>>
>> thanks,
>> Jacques
Re: RFC: Adding Double Colon Cast Syntax
Posted by "jordan.halterman@gmail.com" <jo...@gmail.com>.
We added the double-colon syntax to our own fork of the Calcite grammar to placate our analysts and their addiction to Redshift. TBH it was not easy, and our implementation still doesn't support things like casting from a scalar subquery. Essentially, you can cast identifiers and function results. But I'm sure the folks around here could figure it out.
> On Sep 27, 2016, at 11:11 AM, Jacques Nadeau <ja...@apache.org> wrote:
>
> Hey Everybody,
>
> I'd like to propose adding the double colon cast syntax to Calcite (e.g.
> '1'::INTEGER instead of CAST('1' as INTEGER). It's something that Postgres,
> DB2, Vertica (PG deriv), RedShift (PG deriv) and a number of other systems
> support. Has anybody explored this or previously opened a jira (I searched
> and didn't find any)? Any thoughts on lexical ambiguities?
>
> thanks,
> Jacques