You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Andy Grove <an...@agildata.com> on 2015/11/20 01:16:41 UTC
RelBuilder literal projection with no relation
Hi,
I'm now working on a project to translate some proprietary SQL to a Calcite
relational plan using the RelBuilder API.
I have a question about literal projections. I can do this OK:
// SELECT 123 FROM EMP
builder
.scan("EMP")
.project(builder.literal(123))
.build();
However, if I try and do "SELECT 123" without a relation, it fails with an
ArrayIndexOutOfBoundsException.
// SELECT 123
builder
.project(builder.literal(123))
.build();
Is there an extra step I need to add a fake relation, or is this a bug? I'm
happy to dig in and fix this if it is a bug, but wanted to verify first if
this expected to work or not.
Thanks,
Andy.
--
Andy Grove
Chief Architect
AgilData - Simple Streaming SQL that Scales
www.agildata.com
Re: RelBuilder literal projection with no relation
Posted by Andy Grove <an...@agildata.com>.
That all makes perfect sense, and is very obvious now. I'd be happy to
contribute to the docs to help others with understanding this. I'll have
some time this weekend to put something together.
Thanks,
Andy.
--
Andy Grove
Chief Architect
AgilData - Simple Streaming SQL that Scales
www.agildata.com
On Thu, Nov 19, 2015 at 6:36 PM, Julian Hyde <jh...@apache.org> wrote:
> If you didn’t know to use values(), that tells me we need more
> documentation. Say a section just before
> http://calcite.hydromatic.net/docs/algebra.html#api-summary <
> http://calcite.hydromatic.net/docs/algebra.html#api-summary>.
>
> By the way, I believe that literals in a projection will work just fine.
> The problem in your case is that project needs an input relation, and you
> hadn’t created one. That is the beauty of values() — it creates a relation
> out of nothing (well, out of constants).
>
> The project in option #1 just projects the underlying fields — therefore
> it does nothing. You’re thinking too much in SQL terms, where there has to
> be a SELECT clause (i.e. a project).
>
> In fact RelBuilder will recognize that the project is redundant and do
> nothing, but still, the example we should give is the minimal one.
>
> So, the right answer is
>
> builder
> .values(new String[] {“f0”, “f1”}, 123, “Hello”)
> .build();
>
>
> Julian
>
> PS I’m just waiting for all the Oracle folks to tell me that the right
> answer is
>
> builder
> .scan(“DUAL”)
> .project(123, “Hello”)
> .build();
>
> :)
>
>
> > On Nov 19, 2015, at 5:21 PM, Andy Grove <an...@agildata.com> wrote:
> >
> > There are tests for builder.values() but there are no tests for literals
> in
> > a projection (with, or without a relation), and I didn't know enough to
> > know to look for values() ..
> >
> > I have unit tests for literals in projection. I'm happy to contribute to
> > docs too. I'll get a PR prepared, but before I do that, how would you
> > recommend I model this?
> >
> > Option 1: tuple with literals, then projection references them as fields:
> >
> > builder
> > .values(new String[] { "f0", "f1" }, 123, "Hello")
> > .project(builder.field(0), builder.field(1))
> > .build();
> >
> > Option 2: tuple with literals, then projection has the same literals
> (seems
> > redundant)
> >
> > builder
> > .values(new String[] { "f0", "f1" }, 123, "Hello")
> > .project(builder.literal(123), builder.literal("Hello"))
> >
> > There is an option 3 where I just have a placeholder values() that is
> never
> > referenced, and the projection just has the literals. That seemed too
> hacky
> > though.
> >
> > Thanks,
> >
> > Andy.
> >
> > --
> >
> > Andy Grove
> > Chief Architect
> > AgilData - Simple Streaming SQL that Scales
> > www.agildata.com
> >
> >
> > On Thu, Nov 19, 2015 at 6:01 PM, Julian Hyde <jh...@apache.org> wrote:
> >
> >> I think there are tests for RelBuilder.values already.
> >>
> >> But it sounds as if we need more documentation in
> >> http://calcite.apache.org/docs/algebra.html <
> >> http://calcite.apache.org/docs/algebra.html>. What do you think?
> >>
> >> Julian
> >>
> >>
> >>
> >>> On Nov 19, 2015, at 4:58 PM, Andy Grove <an...@agildata.com>
> wrote:
> >>>
> >>> Got it. Thanks for the detail. I'll submit some unit tests for
> >>> RelBuilderTest to demonstrate this usage for others.
> >>>
> >>>
> >>> Thanks,
> >>>
> >>> Andy.
> >>>
> >>> --
> >>>
> >>> Andy Grove
> >>> Chief Architect
> >>> AgilData - Simple Streaming SQL that Scales
> >>> www.agildata.com
> >>>
> >>>
> >>> On Thu, Nov 19, 2015 at 5:24 PM, Julian Hyde <jh...@apache.org> wrote:
> >>>
> >>>> A Project needs one input. It’s not negotiable.
> >>>>
> >>>> If you want to generate a single row, use RelBuilder.values(). There
> are
> >>>> several overloaded forms, but for your case probably
> >>>>
> >>>> RelBuilder builder;
> >>>> builder.values(“i”, 123)
> >>>> .build();
> >>>>
> >>>> will do the trick. The VALUES relational operator is a leaf: it has no
> >>>> inputs, and produces a constant relation. In SQL terms, you are
> >> generating
> >>>>
> >>>> VALUES (123);
> >>>>
> >>>> Other overloaded forms of the overloaded values method allow multiple
> >>>> columns and multiple rows, e.g.
> >>>>
> >>>> VALUES (123, ‘a’), (234, ‘bcd’);
> >>>>
> >>>> Your mind has probably been poisoned by MySQL and Postgres’
> non-standard
> >>>> FROM-less queries:
> >>>>
> >>>> SELECT 123;
> >>>>
> >>>> Julian
> >>>>
> >>>>
> >>>>
> >>>>> On Nov 19, 2015, at 4:16 PM, Andy Grove <an...@agildata.com>
> >> wrote:
> >>>>>
> >>>>> Hi,
> >>>>>
> >>>>> I'm now working on a project to translate some proprietary SQL to a
> >>>> Calcite
> >>>>> relational plan using the RelBuilder API.
> >>>>>
> >>>>> I have a question about literal projections. I can do this OK:
> >>>>>
> >>>>> // SELECT 123 FROM EMP
> >>>>> builder
> >>>>> .scan("EMP")
> >>>>> .project(builder.literal(123))
> >>>>> .build();
> >>>>>
> >>>>> However, if I try and do "SELECT 123" without a relation, it fails
> with
> >>>> an
> >>>>> ArrayIndexOutOfBoundsException.
> >>>>>
> >>>>> // SELECT 123
> >>>>> builder
> >>>>> .project(builder.literal(123))
> >>>>> .build();
> >>>>>
> >>>>> Is there an extra step I need to add a fake relation, or is this a
> bug?
> >>>> I'm
> >>>>> happy to dig in and fix this if it is a bug, but wanted to verify
> first
> >>>> if
> >>>>> this expected to work or not.
> >>>>>
> >>>>> Thanks,
> >>>>>
> >>>>> Andy.
> >>>>>
> >>>>> --
> >>>>>
> >>>>> Andy Grove
> >>>>> Chief Architect
> >>>>> AgilData - Simple Streaming SQL that Scales
> >>>>> www.agildata.com
> >>>>
> >>>>
> >>
> >>
>
>
Re: RelBuilder literal projection with no relation
Posted by Julian Hyde <jh...@apache.org>.
If you didn’t know to use values(), that tells me we need more documentation. Say a section just before http://calcite.hydromatic.net/docs/algebra.html#api-summary <http://calcite.hydromatic.net/docs/algebra.html#api-summary>.
By the way, I believe that literals in a projection will work just fine. The problem in your case is that project needs an input relation, and you hadn’t created one. That is the beauty of values() — it creates a relation out of nothing (well, out of constants).
The project in option #1 just projects the underlying fields — therefore it does nothing. You’re thinking too much in SQL terms, where there has to be a SELECT clause (i.e. a project).
In fact RelBuilder will recognize that the project is redundant and do nothing, but still, the example we should give is the minimal one.
So, the right answer is
builder
.values(new String[] {“f0”, “f1”}, 123, “Hello”)
.build();
Julian
PS I’m just waiting for all the Oracle folks to tell me that the right answer is
builder
.scan(“DUAL”)
.project(123, “Hello”)
.build();
:)
> On Nov 19, 2015, at 5:21 PM, Andy Grove <an...@agildata.com> wrote:
>
> There are tests for builder.values() but there are no tests for literals in
> a projection (with, or without a relation), and I didn't know enough to
> know to look for values() ..
>
> I have unit tests for literals in projection. I'm happy to contribute to
> docs too. I'll get a PR prepared, but before I do that, how would you
> recommend I model this?
>
> Option 1: tuple with literals, then projection references them as fields:
>
> builder
> .values(new String[] { "f0", "f1" }, 123, "Hello")
> .project(builder.field(0), builder.field(1))
> .build();
>
> Option 2: tuple with literals, then projection has the same literals (seems
> redundant)
>
> builder
> .values(new String[] { "f0", "f1" }, 123, "Hello")
> .project(builder.literal(123), builder.literal("Hello"))
>
> There is an option 3 where I just have a placeholder values() that is never
> referenced, and the projection just has the literals. That seemed too hacky
> though.
>
> Thanks,
>
> Andy.
>
> --
>
> Andy Grove
> Chief Architect
> AgilData - Simple Streaming SQL that Scales
> www.agildata.com
>
>
> On Thu, Nov 19, 2015 at 6:01 PM, Julian Hyde <jh...@apache.org> wrote:
>
>> I think there are tests for RelBuilder.values already.
>>
>> But it sounds as if we need more documentation in
>> http://calcite.apache.org/docs/algebra.html <
>> http://calcite.apache.org/docs/algebra.html>. What do you think?
>>
>> Julian
>>
>>
>>
>>> On Nov 19, 2015, at 4:58 PM, Andy Grove <an...@agildata.com> wrote:
>>>
>>> Got it. Thanks for the detail. I'll submit some unit tests for
>>> RelBuilderTest to demonstrate this usage for others.
>>>
>>>
>>> Thanks,
>>>
>>> Andy.
>>>
>>> --
>>>
>>> Andy Grove
>>> Chief Architect
>>> AgilData - Simple Streaming SQL that Scales
>>> www.agildata.com
>>>
>>>
>>> On Thu, Nov 19, 2015 at 5:24 PM, Julian Hyde <jh...@apache.org> wrote:
>>>
>>>> A Project needs one input. It’s not negotiable.
>>>>
>>>> If you want to generate a single row, use RelBuilder.values(). There are
>>>> several overloaded forms, but for your case probably
>>>>
>>>> RelBuilder builder;
>>>> builder.values(“i”, 123)
>>>> .build();
>>>>
>>>> will do the trick. The VALUES relational operator is a leaf: it has no
>>>> inputs, and produces a constant relation. In SQL terms, you are
>> generating
>>>>
>>>> VALUES (123);
>>>>
>>>> Other overloaded forms of the overloaded values method allow multiple
>>>> columns and multiple rows, e.g.
>>>>
>>>> VALUES (123, ‘a’), (234, ‘bcd’);
>>>>
>>>> Your mind has probably been poisoned by MySQL and Postgres’ non-standard
>>>> FROM-less queries:
>>>>
>>>> SELECT 123;
>>>>
>>>> Julian
>>>>
>>>>
>>>>
>>>>> On Nov 19, 2015, at 4:16 PM, Andy Grove <an...@agildata.com>
>> wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> I'm now working on a project to translate some proprietary SQL to a
>>>> Calcite
>>>>> relational plan using the RelBuilder API.
>>>>>
>>>>> I have a question about literal projections. I can do this OK:
>>>>>
>>>>> // SELECT 123 FROM EMP
>>>>> builder
>>>>> .scan("EMP")
>>>>> .project(builder.literal(123))
>>>>> .build();
>>>>>
>>>>> However, if I try and do "SELECT 123" without a relation, it fails with
>>>> an
>>>>> ArrayIndexOutOfBoundsException.
>>>>>
>>>>> // SELECT 123
>>>>> builder
>>>>> .project(builder.literal(123))
>>>>> .build();
>>>>>
>>>>> Is there an extra step I need to add a fake relation, or is this a bug?
>>>> I'm
>>>>> happy to dig in and fix this if it is a bug, but wanted to verify first
>>>> if
>>>>> this expected to work or not.
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Andy.
>>>>>
>>>>> --
>>>>>
>>>>> Andy Grove
>>>>> Chief Architect
>>>>> AgilData - Simple Streaming SQL that Scales
>>>>> www.agildata.com
>>>>
>>>>
>>
>>
Re: RelBuilder literal projection with no relation
Posted by Andy Grove <an...@agildata.com>.
There are tests for builder.values() but there are no tests for literals in
a projection (with, or without a relation), and I didn't know enough to
know to look for values() ..
I have unit tests for literals in projection. I'm happy to contribute to
docs too. I'll get a PR prepared, but before I do that, how would you
recommend I model this?
Option 1: tuple with literals, then projection references them as fields:
builder
.values(new String[] { "f0", "f1" }, 123, "Hello")
.project(builder.field(0), builder.field(1))
.build();
Option 2: tuple with literals, then projection has the same literals (seems
redundant)
builder
.values(new String[] { "f0", "f1" }, 123, "Hello")
.project(builder.literal(123), builder.literal("Hello"))
There is an option 3 where I just have a placeholder values() that is never
referenced, and the projection just has the literals. That seemed too hacky
though.
Thanks,
Andy.
--
Andy Grove
Chief Architect
AgilData - Simple Streaming SQL that Scales
www.agildata.com
On Thu, Nov 19, 2015 at 6:01 PM, Julian Hyde <jh...@apache.org> wrote:
> I think there are tests for RelBuilder.values already.
>
> But it sounds as if we need more documentation in
> http://calcite.apache.org/docs/algebra.html <
> http://calcite.apache.org/docs/algebra.html>. What do you think?
>
> Julian
>
>
>
> > On Nov 19, 2015, at 4:58 PM, Andy Grove <an...@agildata.com> wrote:
> >
> > Got it. Thanks for the detail. I'll submit some unit tests for
> > RelBuilderTest to demonstrate this usage for others.
> >
> >
> > Thanks,
> >
> > Andy.
> >
> > --
> >
> > Andy Grove
> > Chief Architect
> > AgilData - Simple Streaming SQL that Scales
> > www.agildata.com
> >
> >
> > On Thu, Nov 19, 2015 at 5:24 PM, Julian Hyde <jh...@apache.org> wrote:
> >
> >> A Project needs one input. It’s not negotiable.
> >>
> >> If you want to generate a single row, use RelBuilder.values(). There are
> >> several overloaded forms, but for your case probably
> >>
> >> RelBuilder builder;
> >> builder.values(“i”, 123)
> >> .build();
> >>
> >> will do the trick. The VALUES relational operator is a leaf: it has no
> >> inputs, and produces a constant relation. In SQL terms, you are
> generating
> >>
> >> VALUES (123);
> >>
> >> Other overloaded forms of the overloaded values method allow multiple
> >> columns and multiple rows, e.g.
> >>
> >> VALUES (123, ‘a’), (234, ‘bcd’);
> >>
> >> Your mind has probably been poisoned by MySQL and Postgres’ non-standard
> >> FROM-less queries:
> >>
> >> SELECT 123;
> >>
> >> Julian
> >>
> >>
> >>
> >>> On Nov 19, 2015, at 4:16 PM, Andy Grove <an...@agildata.com>
> wrote:
> >>>
> >>> Hi,
> >>>
> >>> I'm now working on a project to translate some proprietary SQL to a
> >> Calcite
> >>> relational plan using the RelBuilder API.
> >>>
> >>> I have a question about literal projections. I can do this OK:
> >>>
> >>> // SELECT 123 FROM EMP
> >>> builder
> >>> .scan("EMP")
> >>> .project(builder.literal(123))
> >>> .build();
> >>>
> >>> However, if I try and do "SELECT 123" without a relation, it fails with
> >> an
> >>> ArrayIndexOutOfBoundsException.
> >>>
> >>> // SELECT 123
> >>> builder
> >>> .project(builder.literal(123))
> >>> .build();
> >>>
> >>> Is there an extra step I need to add a fake relation, or is this a bug?
> >> I'm
> >>> happy to dig in and fix this if it is a bug, but wanted to verify first
> >> if
> >>> this expected to work or not.
> >>>
> >>> Thanks,
> >>>
> >>> Andy.
> >>>
> >>> --
> >>>
> >>> Andy Grove
> >>> Chief Architect
> >>> AgilData - Simple Streaming SQL that Scales
> >>> www.agildata.com
> >>
> >>
>
>
Re: RelBuilder literal projection with no relation
Posted by Julian Hyde <jh...@apache.org>.
I think there are tests for RelBuilder.values already.
But it sounds as if we need more documentation in http://calcite.apache.org/docs/algebra.html <http://calcite.apache.org/docs/algebra.html>. What do you think?
Julian
> On Nov 19, 2015, at 4:58 PM, Andy Grove <an...@agildata.com> wrote:
>
> Got it. Thanks for the detail. I'll submit some unit tests for
> RelBuilderTest to demonstrate this usage for others.
>
>
> Thanks,
>
> Andy.
>
> --
>
> Andy Grove
> Chief Architect
> AgilData - Simple Streaming SQL that Scales
> www.agildata.com
>
>
> On Thu, Nov 19, 2015 at 5:24 PM, Julian Hyde <jh...@apache.org> wrote:
>
>> A Project needs one input. It’s not negotiable.
>>
>> If you want to generate a single row, use RelBuilder.values(). There are
>> several overloaded forms, but for your case probably
>>
>> RelBuilder builder;
>> builder.values(“i”, 123)
>> .build();
>>
>> will do the trick. The VALUES relational operator is a leaf: it has no
>> inputs, and produces a constant relation. In SQL terms, you are generating
>>
>> VALUES (123);
>>
>> Other overloaded forms of the overloaded values method allow multiple
>> columns and multiple rows, e.g.
>>
>> VALUES (123, ‘a’), (234, ‘bcd’);
>>
>> Your mind has probably been poisoned by MySQL and Postgres’ non-standard
>> FROM-less queries:
>>
>> SELECT 123;
>>
>> Julian
>>
>>
>>
>>> On Nov 19, 2015, at 4:16 PM, Andy Grove <an...@agildata.com> wrote:
>>>
>>> Hi,
>>>
>>> I'm now working on a project to translate some proprietary SQL to a
>> Calcite
>>> relational plan using the RelBuilder API.
>>>
>>> I have a question about literal projections. I can do this OK:
>>>
>>> // SELECT 123 FROM EMP
>>> builder
>>> .scan("EMP")
>>> .project(builder.literal(123))
>>> .build();
>>>
>>> However, if I try and do "SELECT 123" without a relation, it fails with
>> an
>>> ArrayIndexOutOfBoundsException.
>>>
>>> // SELECT 123
>>> builder
>>> .project(builder.literal(123))
>>> .build();
>>>
>>> Is there an extra step I need to add a fake relation, or is this a bug?
>> I'm
>>> happy to dig in and fix this if it is a bug, but wanted to verify first
>> if
>>> this expected to work or not.
>>>
>>> Thanks,
>>>
>>> Andy.
>>>
>>> --
>>>
>>> Andy Grove
>>> Chief Architect
>>> AgilData - Simple Streaming SQL that Scales
>>> www.agildata.com
>>
>>
Re: RelBuilder literal projection with no relation
Posted by Andy Grove <an...@agildata.com>.
Got it. Thanks for the detail. I'll submit some unit tests for
RelBuilderTest to demonstrate this usage for others.
Thanks,
Andy.
--
Andy Grove
Chief Architect
AgilData - Simple Streaming SQL that Scales
www.agildata.com
On Thu, Nov 19, 2015 at 5:24 PM, Julian Hyde <jh...@apache.org> wrote:
> A Project needs one input. It’s not negotiable.
>
> If you want to generate a single row, use RelBuilder.values(). There are
> several overloaded forms, but for your case probably
>
> RelBuilder builder;
> builder.values(“i”, 123)
> .build();
>
> will do the trick. The VALUES relational operator is a leaf: it has no
> inputs, and produces a constant relation. In SQL terms, you are generating
>
> VALUES (123);
>
> Other overloaded forms of the overloaded values method allow multiple
> columns and multiple rows, e.g.
>
> VALUES (123, ‘a’), (234, ‘bcd’);
>
> Your mind has probably been poisoned by MySQL and Postgres’ non-standard
> FROM-less queries:
>
> SELECT 123;
>
> Julian
>
>
>
> > On Nov 19, 2015, at 4:16 PM, Andy Grove <an...@agildata.com> wrote:
> >
> > Hi,
> >
> > I'm now working on a project to translate some proprietary SQL to a
> Calcite
> > relational plan using the RelBuilder API.
> >
> > I have a question about literal projections. I can do this OK:
> >
> > // SELECT 123 FROM EMP
> > builder
> > .scan("EMP")
> > .project(builder.literal(123))
> > .build();
> >
> > However, if I try and do "SELECT 123" without a relation, it fails with
> an
> > ArrayIndexOutOfBoundsException.
> >
> > // SELECT 123
> > builder
> > .project(builder.literal(123))
> > .build();
> >
> > Is there an extra step I need to add a fake relation, or is this a bug?
> I'm
> > happy to dig in and fix this if it is a bug, but wanted to verify first
> if
> > this expected to work or not.
> >
> > Thanks,
> >
> > Andy.
> >
> > --
> >
> > Andy Grove
> > Chief Architect
> > AgilData - Simple Streaming SQL that Scales
> > www.agildata.com
>
>
Re: RelBuilder literal projection with no relation
Posted by Julian Hyde <jh...@apache.org>.
A Project needs one input. It’s not negotiable.
If you want to generate a single row, use RelBuilder.values(). There are several overloaded forms, but for your case probably
RelBuilder builder;
builder.values(“i”, 123)
.build();
will do the trick. The VALUES relational operator is a leaf: it has no inputs, and produces a constant relation. In SQL terms, you are generating
VALUES (123);
Other overloaded forms of the overloaded values method allow multiple columns and multiple rows, e.g.
VALUES (123, ‘a’), (234, ‘bcd’);
Your mind has probably been poisoned by MySQL and Postgres’ non-standard FROM-less queries:
SELECT 123;
Julian
> On Nov 19, 2015, at 4:16 PM, Andy Grove <an...@agildata.com> wrote:
>
> Hi,
>
> I'm now working on a project to translate some proprietary SQL to a Calcite
> relational plan using the RelBuilder API.
>
> I have a question about literal projections. I can do this OK:
>
> // SELECT 123 FROM EMP
> builder
> .scan("EMP")
> .project(builder.literal(123))
> .build();
>
> However, if I try and do "SELECT 123" without a relation, it fails with an
> ArrayIndexOutOfBoundsException.
>
> // SELECT 123
> builder
> .project(builder.literal(123))
> .build();
>
> Is there an extra step I need to add a fake relation, or is this a bug? I'm
> happy to dig in and fix this if it is a bug, but wanted to verify first if
> this expected to work or not.
>
> Thanks,
>
> Andy.
>
> --
>
> Andy Grove
> Chief Architect
> AgilData - Simple Streaming SQL that Scales
> www.agildata.com