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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> > 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 <[email protected]> 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 <[email protected]> > >> 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 > >>>> > >>>> > >> > >> > >
