You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@calcite.apache.org by Eric Berryman <er...@gmail.com> on 2024/06/21 12:43:42 UTC

Sortlimit and limit examples

Hello!

When I add limit to my relbuilder object, the planner gives up. But works
fine without it.

I wasn’t able to find any examples of using limit and pushing the limit
values down to a data source.

Could someone help with some links on this subject?

Thank you!
Eric

Re: Sortlimit and limit examples

Posted by Julian Hyde <jh...@gmail.com>.

Is there a rule that can push a limit (Sort with 0 sort keys) through Union? Hopefully there is, and it can be adapted for any subclass of Sort and Union. 

That rule should create the right plan. Then we can debate whether OP needs to implement a new union operator or reuse EnumerableUnion. 

Julian

> On Jul 19, 2024, at 08:13, Ruben Q L <ru...@gmail.com> wrote:
> 
> Eric, if you want to use EnumerableUnion instead of implementing your own
> Union, then I think you'll need to implement the appropriate Converter [1]
> to transform between conventions LDAP <=> ENUMERABLE
> 
> Best,
> Ruben
> 
> [1]
> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/convert/Converter.java
> 
> 
>> On Fri, Jul 19, 2024 at 3:56 PM Eric Berryman <er...@gmail.com>
>> wrote:
>> 
>> I implement an LDAPUnion and rule to convert from logicalunion, and of
>> course, the error goes away. But I didn’t implement the union correctly, so
>> nothing is returned in my query. Is there a way to have my LDAPUnion
>> use EnumerableUnion,
>> or a way to not have LDAPUnion and take care of this with a rule?
>> 
>> Thank you again!
>> Eric
>> 
>> On Tue, Jul 16, 2024 at 14:24 Eric Berryman <er...@gmail.com>
>> wrote:
>> 
>>> Well, I was wrong. That didn’t solve my problem with :
>>> 
>>> Missing conversion is LogicalUnion[convention: NONE -> LDAP]
>>> 
>>> Do I have to implement my own union? Is there a way I could just use the
>>> EnumerableUnion?
>>> ie. NONE -> Enumerable and skip implementing one for my ldap datastore?
>>> 
>>> Thank you!
>>> Eric
>>> 
>>> On Tue, Jul 16, 2024 at 09:00 Eric Berryman <er...@gmail.com>
>>> wrote:
>>> 
>>>> When I register my tablescan object, and add my rules, I also added a
>>>> removeRule for EnumerableRules.ENUMERABLE_MERGE_UNION_RULE, and now
>>>> everything works with an offset also.
>>>> 
>>>> Although, I don’t know why this is. I noticed the EnumerableMergeUnion
>>>> object in the plan, and thought I would try to remove it, because it was
>>>> different.
>>>> 
>>>> Any explanation is appreciated, thank you!!!
>>>> Eric
>>>> 
>>>> 
>>>> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <er...@gmail.com>
>>>> wrote:
>>>> 
>>>>> Hello!
>>>>> 
>>>>> I seem to have an issue with my new limit rule which pushes down to the
>>>>> datastore. It works fine, unless I add an offset to the fetch. Where I
>> end
>>>>> up with the following error:
>>>>> There are not enough rules… Missing conversion is
>>>>> LogicalUnion[convention: NONE -> LDAP]
>>>>> 
>>>>> Why would this only come up when an offset value is added?
>>>>> 
>>>>> Thank you!
>>>>> Eric
>>>>> 
>>>>> 
>>>>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <er...@gmail.com>
>>>>> wrote:
>>>>> 
>>>>>> That’s perfect
>>>>>> 
>>>>>> 
>> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
>>>>>> 
>>>>>> Thank you for such a quick response!
>>>>>> Eric
>>>>>> 
>>>>>> 
>>>>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <mm...@apache.org> wrote:
>>>>>> 
>>>>>>> Eric,
>>>>>>> 
>>>>>>> Could you give a more specific example of the failure scenario you're
>>>>>>> experiencing?
>>>>>>> 
>>>>>>> For a simple example of how limits can be pushed down, this is done
>> in
>>>>>>> the
>>>>>>> Cassandra adapter with CassandraLimitRule. It matches an
>>>>>>> EnumerableLimit on
>>>>>>> top of a CassandraToEnumerableConverter and then converts that limit
>>>>>>> to a
>>>>>>> CassandraLimit which passes along the limit and offset information to
>>>>>>> CassandraToEnumerableConverter for when the query is executed. The
>>>>>>> EnumerableLimit is then replaced with a CassandraLimit effectively
>> as a
>>>>>>> placeholder to signal that the limit has been handled.
>>>>>>> 
>>>>>>> --
>>>>>>> Michael Mior
>>>>>>> mmior@apache.org
>>>>>>> 
>>>>>>> 
>>>>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <
>> eric.berryman@gmail.com
>>>>>>>> 
>>>>>>> wrote:
>>>>>>> 
>>>>>>>> Hello!
>>>>>>>> 
>>>>>>>> When I add limit to my relbuilder object, the planner gives up. But
>>>>>>> works
>>>>>>>> fine without it.
>>>>>>>> 
>>>>>>>> I wasn’t able to find any examples of using limit and pushing the
>>>>>>> limit
>>>>>>>> values down to a data source.
>>>>>>>> 
>>>>>>>> Could someone help with some links on this subject?
>>>>>>>> 
>>>>>>>> Thank you!
>>>>>>>> Eric
>>>>>>>> 
>>>>>>> 
>>>>>> 
>>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

The RelTraitDefs do have both ConventionTraitDef.INSTANCE and
RelCollationTraitDef.INSTANCE.

Let me see about posting the code to GitHub with the failing test.

Thank you again for your help on this!!
Eric

On Thu, Aug 22, 2024 at 12:06 Ruben Q L <ru...@gmail.com> wrote:

> Hi Eric!
>
> What happens if you try a query with a LIMIT (and/or OFFSET) without an
> ORDER BY? Same result?
> Can you please double check that your RelMdCollation (or whatever handler
> you use for collation) defines a method for EnumerableLimit?
>
> Can you share the code where you create and configure the Volcano planner
> and its rules?
> Are you adding the corresponding RelTraitDefs
> <
> https://github.com/apache/calcite/blob/4252e0ca2a7c57edf5ea8f13d4cf70fe0c2913c7/core/src/main/java/org/apache/calcite/plan/RelOptPlanner.java#L66
> >
> on it? (I assume at least ConventionTraitDef.INSTANCE and
> RelCollationTraitDef.INSTANCE, with especial attention the latter since
> you're having issues with the ordering).
>
> At first glance, it would seem you do have a valid implementation: the
> rel#72 EnumerableProject in Set7/RelSubset7 on the top-right corner of the
> image; however this (which is the originalRoot according to the stack
> trace), is not the root anymore (which is in Set37 at the moment of the
> exception). Maybe you could to debug the Volcano execution to see what's
> going on inside of it step by step...
>
> Ruben
>
>
>
> On Thu, Aug 22, 2024 at 1:44 AM Eric Berryman <er...@gmail.com>
> wrote:
>
> > Is there anymore information I can give to help understand the missing
> > rule?
> >
> > Thank you!
> > Eric
> >
> > On Tue, Aug 20, 2024 at 18:59 Eric Berryman <er...@gmail.com>
> > wrote:
> >
> > > Is there something I’m missing about
> > > EnumerableLimit? It seems like I get an infinite cost anytime it’s in
> my
> > > path?
> > >
> > > Thank you again,
> > > Eric
> > >
> > > On Mon, Aug 19, 2024 at 18:00 Eric Berryman <er...@gmail.com>
> > > wrote:
> > >
> > >> I see the red circles in the svg query plan I added to the gist. I’m
> > just
> > >> not sure what it’s supposed to be telling me.
> > >>
> > >> Thank you!
> > >> Eric
> > >>
> > >> On Mon, Aug 19, 2024 at 11:35 Eric Berryman <er...@gmail.com>
> > >> wrote:
> > >>
> > >>> 1.37.0
> > >>>
> > >>> On Mon, Aug 19, 2024 at 11:28 Ruben Q L <ru...@gmail.com> wrote:
> > >>>
> > >>>> Which Calcite version are you using?
> > >>>>
> > >>>>
> > >>>> On Mon, Aug 19, 2024 at 4:22 PM Eric Berryman <
> > eric.berryman@gmail.com>
> > >>>> wrote:
> > >>>>
> > >>>> > I’ve noticed if I remove the limit, the EnumerableUnion works
> fine.
> > >>>> So it
> > >>>> > seems the infinite cost is coming from the EnumerableLimit outside
> > of
> > >>>> the
> > >>>> > union. I have an LDAPSortLimit which is pushed past the union, and
> > an
> > >>>> > EnumerableLimit
> > >>>> > is left outside the union, as you can see in the gist.
> > >>>> >
> > >>>> > On Mon, Aug 19, 2024 at 11:10 Eric Berryman <
> > eric.berryman@gmail.com>
> > >>>> > wrote:
> > >>>> >
> > >>>> > > Is there anymore information that would be helpful for me to
> give
> > >>>> to aide
> > >>>> > > in troubleshooting this?
> > >>>> > >
> > >>>> > > Thank you!
> > >>>> > > Eric
> > >>>> > >
> > >>>> > > On Thu, Aug 15, 2024 at 16:51 Eric Berryman <
> > >>>> eric.berryman@gmail.com>
> > >>>> > > wrote:
> > >>>> > >
> > >>>> > >>
> > >>>> > >> I seem to be struggling with unions still. Here is a gist with
> > the
> > >>>> > output
> > >>>> > >> and svg of the plan.
> > >>>> > >>
> > >>>> > >>
> > https://gist.github.com/berryma4/c4870dd4e292e0509d1c85c308b52e67
> > >>>> > >>
> > >>>> > >> I need a little help on direction of what the svg is telling
> me.
> > >>>> I see
> > >>>> > >> the cost is infinite, but I don’t see where I’m missing a rule.
> > >>>> > >>
> > >>>> > >>
> > >>>> > >> Thank you for your help,
> > >>>> > >> Eric
> > >>>> > >>
> > >>>> > >>
> > >>>> > >>
> > >>>> > >> On Mon, Jul 29, 2024 at 18:28 Eric Berryman <
> > >>>> eric.berryman@gmail.com>
> > >>>> > >> wrote:
> > >>>> > >>
> > >>>> > >>> That didn't paste well. Here is a gist:
> > >>>> > >>>
> > https://gist.github.com/berryma4/c6c09da050f273295edd23c045c63403
> > >>>> > >>>
> > >>>> > >>>
> > >>>> > >>>
> > >>>> > >>> On Mon, Jul 29, 2024 at 4:58 PM Eric Berryman <
> > >>>> eric.berryman@gmail.com
> > >>>> > >
> > >>>> > >>> wrote:
> > >>>> > >>>
> > >>>> > >>>> I’m back from a long holiday, and seem stuck still on this
> > >>>> scenario.
> > >>>> > >>>> The plan gets created without any issues, but I get this
> error
> > >>>> while
> > >>>> > >>>> executing queries with unions.
> > >>>> > >>>>
> > >>>> > >>>> Thank you again!
> > >>>> > >>>> Eric
> > >>>> > >>>>
> > >>>> > >>>> Here is the plan dump and error after running
> > >>>> > >>>> relRunner.prepareStatement.
> > >>>> > >>>>
> > >>>> > >>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 30.0,
> > >>>> > >>>> cumulative cost = {146.0 rows, 1393.2 cpu, 0.0 io}, id = 213
> > >>>> > >>>> EnumerableLimit(fetch=[100]): rowcount = 30.0, cumulative
> cost
> > =
> > >>>> > {116.0
> > >>>> > >>>> rows, 1333.2 cpu, 0.0 io}, id = 212
> > EnumerableUnion(all=[true]):
> > >>>> > rowcount =
> > >>>> > >>>> 30.0, cumulative cost = {86.0 rows, 1303.2 cpu, 0.0 io}, id =
> > 211
> > >>>> > >>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0,
> > >>>> > cumulative
> > >>>> > >>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 207
> > >>>> > LDAPToEnumerableConverter:
> > >>>> > >>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0
> > >>>> io}, id
> > >>>> > = 206
> > >>>> > >>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount =
> 15.0,
> > >>>> > cumulative
> > >>>> > >>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 205
> > >>>> > >>>> LDAPTableScan(table=[[SCIMUserSchema,
> > cap_internet_ddpmildap]]):
> > >>>> > rowcount =
> > >>>> > >>>> 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu,
> > 0.0
> > >>>> io},
> > >>>> > id =
> > >>>> > >>>> 0 EnumerableProject(UserObject=[$0], id=[$3]): rowcount =
> 15.0,
> > >>>> > cumulative
> > >>>> > >>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 210
> > >>>> > LDAPToEnumerableConverter:
> > >>>> > >>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0
> > >>>> io}, id
> > >>>> > = 209
> > >>>> > >>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount =
> 15.0,
> > >>>> > cumulative
> > >>>> > >>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 208
> > >>>> > >>>> LDAPTableScan(table=[[SCIMUserSchema,
> > prov_internet_ddpmildap]]):
> > >>>> > rowcount
> > >>>> > >>>> = 100.0, cumulative cost = {10.0 rows, 10.100000000000001
> cpu,
> > >>>> 0.0
> > >>>> > io}, id
> > >>>> > >>>> = 2
> > >>>> > >>>>
> > >>>> > >>>>  java.lang.AssertionError: null at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.adapter.enumerable.EnumerableLimit.<init>(EnumerableLimit.java:60)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:84)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:43)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:378)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
> > >>>> > >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580)
> > at
> > >>>> > >>>>
> > >>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:228)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:514)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
> > >>>> > >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580)
> > at
> > >>>> > >>>>
> > >>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> > org.apache.calcite.sql2rel.RelFieldTrimmer.trim(RelFieldTrimmer.java:173)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.sql2rel.SqlToRelConverter.trimUnusedFields(SqlToRelConverter.java:567)
> > >>>> > >>>> at
> > >>>> >
> > org.apache.calcite.prepare.Prepare.trimUnusedFields(Prepare.java:386)
> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1061)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1026)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:687)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:237)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:217)
> > >>>> > >>>> at
> > >>>> > >>>>
> > >>>> >
> > >>>>
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187)
> > >>>> > >>>>
> > >>>> > >>>>
> > >>>> > >>>> On Fri, Jul 19, 2024 at 11:07 Ruben Q L <ru...@gmail.com>
> > >>>> wrote:
> > >>>> > >>>>
> > >>>> > >>>>> Eric, if you want to use EnumerableUnion instead of
> > >>>> implementing your
> > >>>> > >>>>> own
> > >>>> > >>>>> Union, then I think you'll need to implement the appropriate
> > >>>> > Converter
> > >>>> > >>>>> [1]
> > >>>> > >>>>> to transform between conventions LDAP <=> ENUMERABLE
> > >>>> > >>>>>
> > >>>> > >>>>> Best,
> > >>>> > >>>>> Ruben
> > >>>> > >>>>>
> > >>>> > >>>>> [1]
> > >>>> > >>>>>
> > >>>> > >>>>>
> > >>>> >
> > >>>>
> >
> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/convert/Converter.java
> > >>>> > >>>>>
> > >>>> > >>>>>
> > >>>> > >>>>> On Fri, Jul 19, 2024 at 3:56 PM Eric Berryman <
> > >>>> > eric.berryman@gmail.com
> > >>>> > >>>>> >
> > >>>> > >>>>> wrote:
> > >>>> > >>>>>
> > >>>> > >>>>> > I implement an LDAPUnion and rule to convert from
> > >>>> logicalunion, and
> > >>>> > >>>>> of
> > >>>> > >>>>> > course, the error goes away. But I didn’t implement the
> > union
> > >>>> > >>>>> correctly, so
> > >>>> > >>>>> > nothing is returned in my query. Is there a way to have my
> > >>>> > LDAPUnion
> > >>>> > >>>>> > use EnumerableUnion,
> > >>>> > >>>>> > or a way to not have LDAPUnion and take care of this with
> a
> > >>>> rule?
> > >>>> > >>>>> >
> > >>>> > >>>>> > Thank you again!
> > >>>> > >>>>> > Eric
> > >>>> > >>>>> >
> > >>>> > >>>>> > On Tue, Jul 16, 2024 at 14:24 Eric Berryman <
> > >>>> > eric.berryman@gmail.com
> > >>>> > >>>>> >
> > >>>> > >>>>> > wrote:
> > >>>> > >>>>> >
> > >>>> > >>>>> > > Well, I was wrong. That didn’t solve my problem with :
> > >>>> > >>>>> > >
> > >>>> > >>>>> > > Missing conversion is LogicalUnion[convention: NONE ->
> > LDAP]
> > >>>> > >>>>> > >
> > >>>> > >>>>> > > Do I have to implement my own union? Is there a way I
> > could
> > >>>> just
> > >>>> > >>>>> use the
> > >>>> > >>>>> > > EnumerableUnion?
> > >>>> > >>>>> > > ie. NONE -> Enumerable and skip implementing one for my
> > ldap
> > >>>> > >>>>> datastore?
> > >>>> > >>>>> > >
> > >>>> > >>>>> > > Thank you!
> > >>>> > >>>>> > > Eric
> > >>>> > >>>>> > >
> > >>>> > >>>>> > > On Tue, Jul 16, 2024 at 09:00 Eric Berryman <
> > >>>> > >>>>> eric.berryman@gmail.com>
> > >>>> > >>>>> > > wrote:
> > >>>> > >>>>> > >
> > >>>> > >>>>> > >> When I register my tablescan object, and add my rules,
> I
> > >>>> also
> > >>>> > >>>>> added a
> > >>>> > >>>>> > >> removeRule for
> > >>>> EnumerableRules.ENUMERABLE_MERGE_UNION_RULE, and
> > >>>> > >>>>> now
> > >>>> > >>>>> > >> everything works with an offset also.
> > >>>> > >>>>> > >>
> > >>>> > >>>>> > >> Although, I don’t know why this is. I noticed the
> > >>>> > >>>>> EnumerableMergeUnion
> > >>>> > >>>>> > >> object in the plan, and thought I would try to remove
> it,
> > >>>> > because
> > >>>> > >>>>> it was
> > >>>> > >>>>> > >> different.
> > >>>> > >>>>> > >>
> > >>>> > >>>>> > >> Any explanation is appreciated, thank you!!!
> > >>>> > >>>>> > >> Eric
> > >>>> > >>>>> > >>
> > >>>> > >>>>> > >>
> > >>>> > >>>>> > >> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <
> > >>>> > >>>>> eric.berryman@gmail.com>
> > >>>> > >>>>> > >> wrote:
> > >>>> > >>>>> > >>
> > >>>> > >>>>> > >>> Hello!
> > >>>> > >>>>> > >>>
> > >>>> > >>>>> > >>> I seem to have an issue with my new limit rule which
> > >>>> pushes
> > >>>> > down
> > >>>> > >>>>> to the
> > >>>> > >>>>> > >>> datastore. It works fine, unless I add an offset to
> the
> > >>>> fetch.
> > >>>> > >>>>> Where I
> > >>>> > >>>>> > end
> > >>>> > >>>>> > >>> up with the following error:
> > >>>> > >>>>> > >>> There are not enough rules… Missing conversion is
> > >>>> > >>>>> > >>> LogicalUnion[convention: NONE -> LDAP]
> > >>>> > >>>>> > >>>
> > >>>> > >>>>> > >>> Why would this only come up when an offset value is
> > added?
> > >>>> > >>>>> > >>>
> > >>>> > >>>>> > >>> Thank you!
> > >>>> > >>>>> > >>> Eric
> > >>>> > >>>>> > >>>
> > >>>> > >>>>> > >>>
> > >>>> > >>>>> > >>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <
> > >>>> > >>>>> eric.berryman@gmail.com>
> > >>>> > >>>>> > >>> wrote:
> > >>>> > >>>>> > >>>
> > >>>> > >>>>> > >>>> That’s perfect
> > >>>> > >>>>> > >>>>
> > >>>> > >>>>> > >>>>
> > >>>> > >>>>> >
> > >>>> > >>>>>
> > >>>> >
> > >>>>
> >
> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
> > >>>> > >>>>> > >>>>
> > >>>> > >>>>> > >>>> Thank you for such a quick response!
> > >>>> > >>>>> > >>>> Eric
> > >>>> > >>>>> > >>>>
> > >>>> > >>>>> > >>>>
> > >>>> > >>>>> > >>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <
> > >>>> mmior@apache.org>
> > >>>> > >>>>> wrote:
> > >>>> > >>>>> > >>>>
> > >>>> > >>>>> > >>>>> Eric,
> > >>>> > >>>>> > >>>>>
> > >>>> > >>>>> > >>>>> Could you give a more specific example of the
> failure
> > >>>> > scenario
> > >>>> > >>>>> you're
> > >>>> > >>>>> > >>>>> experiencing?
> > >>>> > >>>>> > >>>>>
> > >>>> > >>>>> > >>>>> For a simple example of how limits can be pushed
> down,
> > >>>> this
> > >>>> > is
> > >>>> > >>>>> done
> > >>>> > >>>>> > in
> > >>>> > >>>>> > >>>>> the
> > >>>> > >>>>> > >>>>> Cassandra adapter with CassandraLimitRule. It
> matches
> > an
> > >>>> > >>>>> > >>>>> EnumerableLimit on
> > >>>> > >>>>> > >>>>> top of a CassandraToEnumerableConverter and then
> > >>>> converts
> > >>>> > that
> > >>>> > >>>>> limit
> > >>>> > >>>>> > >>>>> to a
> > >>>> > >>>>> > >>>>> CassandraLimit which passes along the limit and
> offset
> > >>>> > >>>>> information to
> > >>>> > >>>>> > >>>>> CassandraToEnumerableConverter for when the query is
> > >>>> > executed.
> > >>>> > >>>>> The
> > >>>> > >>>>> > >>>>> EnumerableLimit is then replaced with a
> CassandraLimit
> > >>>> > >>>>> effectively
> > >>>> > >>>>> > as a
> > >>>> > >>>>> > >>>>> placeholder to signal that the limit has been
> handled.
> > >>>> > >>>>> > >>>>>
> > >>>> > >>>>> > >>>>> --
> > >>>> > >>>>> > >>>>> Michael Mior
> > >>>> > >>>>> > >>>>> mmior@apache.org
> > >>>> > >>>>> > >>>>>
> > >>>> > >>>>> > >>>>>
> > >>>> > >>>>> > >>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <
> > >>>> > >>>>> > eric.berryman@gmail.com
> > >>>> > >>>>> > >>>>> >
> > >>>> > >>>>> > >>>>> wrote:
> > >>>> > >>>>> > >>>>>
> > >>>> > >>>>> > >>>>> > Hello!
> > >>>> > >>>>> > >>>>> >
> > >>>> > >>>>> > >>>>> > When I add limit to my relbuilder object, the
> > planner
> > >>>> gives
> > >>>> > >>>>> up. But
> > >>>> > >>>>> > >>>>> works
> > >>>> > >>>>> > >>>>> > fine without it.
> > >>>> > >>>>> > >>>>> >
> > >>>> > >>>>> > >>>>> > I wasn’t able to find any examples of using limit
> > and
> > >>>> > >>>>> pushing the
> > >>>> > >>>>> > >>>>> limit
> > >>>> > >>>>> > >>>>> > values down to a data source.
> > >>>> > >>>>> > >>>>> >
> > >>>> > >>>>> > >>>>> > Could someone help with some links on this
> subject?
> > >>>> > >>>>> > >>>>> >
> > >>>> > >>>>> > >>>>> > Thank you!
> > >>>> > >>>>> > >>>>> > Eric
> > >>>> > >>>>> > >>>>> >
> > >>>> > >>>>> > >>>>>
> > >>>> > >>>>> > >>>>
> > >>>> > >>>>> >
> > >>>> > >>>>>
> > >>>> > >>>>
> > >>>> >
> > >>>>
> > >>>
> >
>

Re: Sortlimit and limit examples

Posted by Ruben Q L <ru...@gmail.com>.

Hi Eric!

What happens if you try a query with a LIMIT (and/or OFFSET) without an
ORDER BY? Same result?
Can you please double check that your RelMdCollation (or whatever handler
you use for collation) defines a method for EnumerableLimit?

Can you share the code where you create and configure the Volcano planner
and its rules?
Are you adding the corresponding RelTraitDefs
<https://github.com/apache/calcite/blob/4252e0ca2a7c57edf5ea8f13d4cf70fe0c2913c7/core/src/main/java/org/apache/calcite/plan/RelOptPlanner.java#L66>
on it? (I assume at least ConventionTraitDef.INSTANCE and
RelCollationTraitDef.INSTANCE, with especial attention the latter since
you're having issues with the ordering).

At first glance, it would seem you do have a valid implementation: the
rel#72 EnumerableProject in Set7/RelSubset7 on the top-right corner of the
image; however this (which is the originalRoot according to the stack
trace), is not the root anymore (which is in Set37 at the moment of the
exception). Maybe you could to debug the Volcano execution to see what's
going on inside of it step by step...

Ruben



On Thu, Aug 22, 2024 at 1:44 AM Eric Berryman <er...@gmail.com>
wrote:

> Is there anymore information I can give to help understand the missing
> rule?
>
> Thank you!
> Eric
>
> On Tue, Aug 20, 2024 at 18:59 Eric Berryman <er...@gmail.com>
> wrote:
>
> > Is there something I’m missing about
> > EnumerableLimit? It seems like I get an infinite cost anytime it’s in my
> > path?
> >
> > Thank you again,
> > Eric
> >
> > On Mon, Aug 19, 2024 at 18:00 Eric Berryman <er...@gmail.com>
> > wrote:
> >
> >> I see the red circles in the svg query plan I added to the gist. I’m
> just
> >> not sure what it’s supposed to be telling me.
> >>
> >> Thank you!
> >> Eric
> >>
> >> On Mon, Aug 19, 2024 at 11:35 Eric Berryman <er...@gmail.com>
> >> wrote:
> >>
> >>> 1.37.0
> >>>
> >>> On Mon, Aug 19, 2024 at 11:28 Ruben Q L <ru...@gmail.com> wrote:
> >>>
> >>>> Which Calcite version are you using?
> >>>>
> >>>>
> >>>> On Mon, Aug 19, 2024 at 4:22 PM Eric Berryman <
> eric.berryman@gmail.com>
> >>>> wrote:
> >>>>
> >>>> > I’ve noticed if I remove the limit, the EnumerableUnion works fine.
> >>>> So it
> >>>> > seems the infinite cost is coming from the EnumerableLimit outside
> of
> >>>> the
> >>>> > union. I have an LDAPSortLimit which is pushed past the union, and
> an
> >>>> > EnumerableLimit
> >>>> > is left outside the union, as you can see in the gist.
> >>>> >
> >>>> > On Mon, Aug 19, 2024 at 11:10 Eric Berryman <
> eric.berryman@gmail.com>
> >>>> > wrote:
> >>>> >
> >>>> > > Is there anymore information that would be helpful for me to give
> >>>> to aide
> >>>> > > in troubleshooting this?
> >>>> > >
> >>>> > > Thank you!
> >>>> > > Eric
> >>>> > >
> >>>> > > On Thu, Aug 15, 2024 at 16:51 Eric Berryman <
> >>>> eric.berryman@gmail.com>
> >>>> > > wrote:
> >>>> > >
> >>>> > >>
> >>>> > >> I seem to be struggling with unions still. Here is a gist with
> the
> >>>> > output
> >>>> > >> and svg of the plan.
> >>>> > >>
> >>>> > >>
> https://gist.github.com/berryma4/c4870dd4e292e0509d1c85c308b52e67
> >>>> > >>
> >>>> > >> I need a little help on direction of what the svg is telling me.
> >>>> I see
> >>>> > >> the cost is infinite, but I don’t see where I’m missing a rule.
> >>>> > >>
> >>>> > >>
> >>>> > >> Thank you for your help,
> >>>> > >> Eric
> >>>> > >>
> >>>> > >>
> >>>> > >>
> >>>> > >> On Mon, Jul 29, 2024 at 18:28 Eric Berryman <
> >>>> eric.berryman@gmail.com>
> >>>> > >> wrote:
> >>>> > >>
> >>>> > >>> That didn't paste well. Here is a gist:
> >>>> > >>>
> https://gist.github.com/berryma4/c6c09da050f273295edd23c045c63403
> >>>> > >>>
> >>>> > >>>
> >>>> > >>>
> >>>> > >>> On Mon, Jul 29, 2024 at 4:58 PM Eric Berryman <
> >>>> eric.berryman@gmail.com
> >>>> > >
> >>>> > >>> wrote:
> >>>> > >>>
> >>>> > >>>> I’m back from a long holiday, and seem stuck still on this
> >>>> scenario.
> >>>> > >>>> The plan gets created without any issues, but I get this error
> >>>> while
> >>>> > >>>> executing queries with unions.
> >>>> > >>>>
> >>>> > >>>> Thank you again!
> >>>> > >>>> Eric
> >>>> > >>>>
> >>>> > >>>> Here is the plan dump and error after running
> >>>> > >>>> relRunner.prepareStatement.
> >>>> > >>>>
> >>>> > >>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 30.0,
> >>>> > >>>> cumulative cost = {146.0 rows, 1393.2 cpu, 0.0 io}, id = 213
> >>>> > >>>> EnumerableLimit(fetch=[100]): rowcount = 30.0, cumulative cost
> =
> >>>> > {116.0
> >>>> > >>>> rows, 1333.2 cpu, 0.0 io}, id = 212
> EnumerableUnion(all=[true]):
> >>>> > rowcount =
> >>>> > >>>> 30.0, cumulative cost = {86.0 rows, 1303.2 cpu, 0.0 io}, id =
> 211
> >>>> > >>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0,
> >>>> > cumulative
> >>>> > >>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 207
> >>>> > LDAPToEnumerableConverter:
> >>>> > >>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0
> >>>> io}, id
> >>>> > = 206
> >>>> > >>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0,
> >>>> > cumulative
> >>>> > >>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 205
> >>>> > >>>> LDAPTableScan(table=[[SCIMUserSchema,
> cap_internet_ddpmildap]]):
> >>>> > rowcount =
> >>>> > >>>> 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu,
> 0.0
> >>>> io},
> >>>> > id =
> >>>> > >>>> 0 EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0,
> >>>> > cumulative
> >>>> > >>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 210
> >>>> > LDAPToEnumerableConverter:
> >>>> > >>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0
> >>>> io}, id
> >>>> > = 209
> >>>> > >>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0,
> >>>> > cumulative
> >>>> > >>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 208
> >>>> > >>>> LDAPTableScan(table=[[SCIMUserSchema,
> prov_internet_ddpmildap]]):
> >>>> > rowcount
> >>>> > >>>> = 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu,
> >>>> 0.0
> >>>> > io}, id
> >>>> > >>>> = 2
> >>>> > >>>>
> >>>> > >>>>  java.lang.AssertionError: null at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.adapter.enumerable.EnumerableLimit.<init>(EnumerableLimit.java:60)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:84)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:43)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:378)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
> >>>> > >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580)
> at
> >>>> > >>>>
> >>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:228)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:514)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
> >>>> > >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580)
> at
> >>>> > >>>>
> >>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.sql2rel.RelFieldTrimmer.trim(RelFieldTrimmer.java:173)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.sql2rel.SqlToRelConverter.trimUnusedFields(SqlToRelConverter.java:567)
> >>>> > >>>> at
> >>>> >
> org.apache.calcite.prepare.Prepare.trimUnusedFields(Prepare.java:386)
> >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1061)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1026)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:687)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:237)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:217)
> >>>> > >>>> at
> >>>> > >>>>
> >>>> >
> >>>>
> org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187)
> >>>> > >>>>
> >>>> > >>>>
> >>>> > >>>> On Fri, Jul 19, 2024 at 11:07 Ruben Q L <ru...@gmail.com>
> >>>> wrote:
> >>>> > >>>>
> >>>> > >>>>> Eric, if you want to use EnumerableUnion instead of
> >>>> implementing your
> >>>> > >>>>> own
> >>>> > >>>>> Union, then I think you'll need to implement the appropriate
> >>>> > Converter
> >>>> > >>>>> [1]
> >>>> > >>>>> to transform between conventions LDAP <=> ENUMERABLE
> >>>> > >>>>>
> >>>> > >>>>> Best,
> >>>> > >>>>> Ruben
> >>>> > >>>>>
> >>>> > >>>>> [1]
> >>>> > >>>>>
> >>>> > >>>>>
> >>>> >
> >>>>
> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/convert/Converter.java
> >>>> > >>>>>
> >>>> > >>>>>
> >>>> > >>>>> On Fri, Jul 19, 2024 at 3:56 PM Eric Berryman <
> >>>> > eric.berryman@gmail.com
> >>>> > >>>>> >
> >>>> > >>>>> wrote:
> >>>> > >>>>>
> >>>> > >>>>> > I implement an LDAPUnion and rule to convert from
> >>>> logicalunion, and
> >>>> > >>>>> of
> >>>> > >>>>> > course, the error goes away. But I didn’t implement the
> union
> >>>> > >>>>> correctly, so
> >>>> > >>>>> > nothing is returned in my query. Is there a way to have my
> >>>> > LDAPUnion
> >>>> > >>>>> > use EnumerableUnion,
> >>>> > >>>>> > or a way to not have LDAPUnion and take care of this with a
> >>>> rule?
> >>>> > >>>>> >
> >>>> > >>>>> > Thank you again!
> >>>> > >>>>> > Eric
> >>>> > >>>>> >
> >>>> > >>>>> > On Tue, Jul 16, 2024 at 14:24 Eric Berryman <
> >>>> > eric.berryman@gmail.com
> >>>> > >>>>> >
> >>>> > >>>>> > wrote:
> >>>> > >>>>> >
> >>>> > >>>>> > > Well, I was wrong. That didn’t solve my problem with :
> >>>> > >>>>> > >
> >>>> > >>>>> > > Missing conversion is LogicalUnion[convention: NONE ->
> LDAP]
> >>>> > >>>>> > >
> >>>> > >>>>> > > Do I have to implement my own union? Is there a way I
> could
> >>>> just
> >>>> > >>>>> use the
> >>>> > >>>>> > > EnumerableUnion?
> >>>> > >>>>> > > ie. NONE -> Enumerable and skip implementing one for my
> ldap
> >>>> > >>>>> datastore?
> >>>> > >>>>> > >
> >>>> > >>>>> > > Thank you!
> >>>> > >>>>> > > Eric
> >>>> > >>>>> > >
> >>>> > >>>>> > > On Tue, Jul 16, 2024 at 09:00 Eric Berryman <
> >>>> > >>>>> eric.berryman@gmail.com>
> >>>> > >>>>> > > wrote:
> >>>> > >>>>> > >
> >>>> > >>>>> > >> When I register my tablescan object, and add my rules, I
> >>>> also
> >>>> > >>>>> added a
> >>>> > >>>>> > >> removeRule for
> >>>> EnumerableRules.ENUMERABLE_MERGE_UNION_RULE, and
> >>>> > >>>>> now
> >>>> > >>>>> > >> everything works with an offset also.
> >>>> > >>>>> > >>
> >>>> > >>>>> > >> Although, I don’t know why this is. I noticed the
> >>>> > >>>>> EnumerableMergeUnion
> >>>> > >>>>> > >> object in the plan, and thought I would try to remove it,
> >>>> > because
> >>>> > >>>>> it was
> >>>> > >>>>> > >> different.
> >>>> > >>>>> > >>
> >>>> > >>>>> > >> Any explanation is appreciated, thank you!!!
> >>>> > >>>>> > >> Eric
> >>>> > >>>>> > >>
> >>>> > >>>>> > >>
> >>>> > >>>>> > >> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <
> >>>> > >>>>> eric.berryman@gmail.com>
> >>>> > >>>>> > >> wrote:
> >>>> > >>>>> > >>
> >>>> > >>>>> > >>> Hello!
> >>>> > >>>>> > >>>
> >>>> > >>>>> > >>> I seem to have an issue with my new limit rule which
> >>>> pushes
> >>>> > down
> >>>> > >>>>> to the
> >>>> > >>>>> > >>> datastore. It works fine, unless I add an offset to the
> >>>> fetch.
> >>>> > >>>>> Where I
> >>>> > >>>>> > end
> >>>> > >>>>> > >>> up with the following error:
> >>>> > >>>>> > >>> There are not enough rules… Missing conversion is
> >>>> > >>>>> > >>> LogicalUnion[convention: NONE -> LDAP]
> >>>> > >>>>> > >>>
> >>>> > >>>>> > >>> Why would this only come up when an offset value is
> added?
> >>>> > >>>>> > >>>
> >>>> > >>>>> > >>> Thank you!
> >>>> > >>>>> > >>> Eric
> >>>> > >>>>> > >>>
> >>>> > >>>>> > >>>
> >>>> > >>>>> > >>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <
> >>>> > >>>>> eric.berryman@gmail.com>
> >>>> > >>>>> > >>> wrote:
> >>>> > >>>>> > >>>
> >>>> > >>>>> > >>>> That’s perfect
> >>>> > >>>>> > >>>>
> >>>> > >>>>> > >>>>
> >>>> > >>>>> >
> >>>> > >>>>>
> >>>> >
> >>>>
> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
> >>>> > >>>>> > >>>>
> >>>> > >>>>> > >>>> Thank you for such a quick response!
> >>>> > >>>>> > >>>> Eric
> >>>> > >>>>> > >>>>
> >>>> > >>>>> > >>>>
> >>>> > >>>>> > >>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <
> >>>> mmior@apache.org>
> >>>> > >>>>> wrote:
> >>>> > >>>>> > >>>>
> >>>> > >>>>> > >>>>> Eric,
> >>>> > >>>>> > >>>>>
> >>>> > >>>>> > >>>>> Could you give a more specific example of the failure
> >>>> > scenario
> >>>> > >>>>> you're
> >>>> > >>>>> > >>>>> experiencing?
> >>>> > >>>>> > >>>>>
> >>>> > >>>>> > >>>>> For a simple example of how limits can be pushed down,
> >>>> this
> >>>> > is
> >>>> > >>>>> done
> >>>> > >>>>> > in
> >>>> > >>>>> > >>>>> the
> >>>> > >>>>> > >>>>> Cassandra adapter with CassandraLimitRule. It matches
> an
> >>>> > >>>>> > >>>>> EnumerableLimit on
> >>>> > >>>>> > >>>>> top of a CassandraToEnumerableConverter and then
> >>>> converts
> >>>> > that
> >>>> > >>>>> limit
> >>>> > >>>>> > >>>>> to a
> >>>> > >>>>> > >>>>> CassandraLimit which passes along the limit and offset
> >>>> > >>>>> information to
> >>>> > >>>>> > >>>>> CassandraToEnumerableConverter for when the query is
> >>>> > executed.
> >>>> > >>>>> The
> >>>> > >>>>> > >>>>> EnumerableLimit is then replaced with a CassandraLimit
> >>>> > >>>>> effectively
> >>>> > >>>>> > as a
> >>>> > >>>>> > >>>>> placeholder to signal that the limit has been handled.
> >>>> > >>>>> > >>>>>
> >>>> > >>>>> > >>>>> --
> >>>> > >>>>> > >>>>> Michael Mior
> >>>> > >>>>> > >>>>> mmior@apache.org
> >>>> > >>>>> > >>>>>
> >>>> > >>>>> > >>>>>
> >>>> > >>>>> > >>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <
> >>>> > >>>>> > eric.berryman@gmail.com
> >>>> > >>>>> > >>>>> >
> >>>> > >>>>> > >>>>> wrote:
> >>>> > >>>>> > >>>>>
> >>>> > >>>>> > >>>>> > Hello!
> >>>> > >>>>> > >>>>> >
> >>>> > >>>>> > >>>>> > When I add limit to my relbuilder object, the
> planner
> >>>> gives
> >>>> > >>>>> up. But
> >>>> > >>>>> > >>>>> works
> >>>> > >>>>> > >>>>> > fine without it.
> >>>> > >>>>> > >>>>> >
> >>>> > >>>>> > >>>>> > I wasn’t able to find any examples of using limit
> and
> >>>> > >>>>> pushing the
> >>>> > >>>>> > >>>>> limit
> >>>> > >>>>> > >>>>> > values down to a data source.
> >>>> > >>>>> > >>>>> >
> >>>> > >>>>> > >>>>> > Could someone help with some links on this subject?
> >>>> > >>>>> > >>>>> >
> >>>> > >>>>> > >>>>> > Thank you!
> >>>> > >>>>> > >>>>> > Eric
> >>>> > >>>>> > >>>>> >
> >>>> > >>>>> > >>>>>
> >>>> > >>>>> > >>>>
> >>>> > >>>>> >
> >>>> > >>>>>
> >>>> > >>>>
> >>>> >
> >>>>
> >>>
>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

Is there anymore information I can give to help understand the missing rule?

Thank you!
Eric

On Tue, Aug 20, 2024 at 18:59 Eric Berryman <er...@gmail.com> wrote:

> Is there something I’m missing about
> EnumerableLimit? It seems like I get an infinite cost anytime it’s in my
> path?
>
> Thank you again,
> Eric
>
> On Mon, Aug 19, 2024 at 18:00 Eric Berryman <er...@gmail.com>
> wrote:
>
>> I see the red circles in the svg query plan I added to the gist. I’m just
>> not sure what it’s supposed to be telling me.
>>
>> Thank you!
>> Eric
>>
>> On Mon, Aug 19, 2024 at 11:35 Eric Berryman <er...@gmail.com>
>> wrote:
>>
>>> 1.37.0
>>>
>>> On Mon, Aug 19, 2024 at 11:28 Ruben Q L <ru...@gmail.com> wrote:
>>>
>>>> Which Calcite version are you using?
>>>>
>>>>
>>>> On Mon, Aug 19, 2024 at 4:22 PM Eric Berryman <er...@gmail.com>
>>>> wrote:
>>>>
>>>> > I’ve noticed if I remove the limit, the EnumerableUnion works fine.
>>>> So it
>>>> > seems the infinite cost is coming from the EnumerableLimit outside of
>>>> the
>>>> > union. I have an LDAPSortLimit which is pushed past the union, and an
>>>> > EnumerableLimit
>>>> > is left outside the union, as you can see in the gist.
>>>> >
>>>> > On Mon, Aug 19, 2024 at 11:10 Eric Berryman <er...@gmail.com>
>>>> > wrote:
>>>> >
>>>> > > Is there anymore information that would be helpful for me to give
>>>> to aide
>>>> > > in troubleshooting this?
>>>> > >
>>>> > > Thank you!
>>>> > > Eric
>>>> > >
>>>> > > On Thu, Aug 15, 2024 at 16:51 Eric Berryman <
>>>> eric.berryman@gmail.com>
>>>> > > wrote:
>>>> > >
>>>> > >>
>>>> > >> I seem to be struggling with unions still. Here is a gist with the
>>>> > output
>>>> > >> and svg of the plan.
>>>> > >>
>>>> > >> https://gist.github.com/berryma4/c4870dd4e292e0509d1c85c308b52e67
>>>> > >>
>>>> > >> I need a little help on direction of what the svg is telling me.
>>>> I see
>>>> > >> the cost is infinite, but I don’t see where I’m missing a rule.
>>>> > >>
>>>> > >>
>>>> > >> Thank you for your help,
>>>> > >> Eric
>>>> > >>
>>>> > >>
>>>> > >>
>>>> > >> On Mon, Jul 29, 2024 at 18:28 Eric Berryman <
>>>> eric.berryman@gmail.com>
>>>> > >> wrote:
>>>> > >>
>>>> > >>> That didn't paste well. Here is a gist:
>>>> > >>> https://gist.github.com/berryma4/c6c09da050f273295edd23c045c63403
>>>> > >>>
>>>> > >>>
>>>> > >>>
>>>> > >>> On Mon, Jul 29, 2024 at 4:58 PM Eric Berryman <
>>>> eric.berryman@gmail.com
>>>> > >
>>>> > >>> wrote:
>>>> > >>>
>>>> > >>>> I’m back from a long holiday, and seem stuck still on this
>>>> scenario.
>>>> > >>>> The plan gets created without any issues, but I get this error
>>>> while
>>>> > >>>> executing queries with unions.
>>>> > >>>>
>>>> > >>>> Thank you again!
>>>> > >>>> Eric
>>>> > >>>>
>>>> > >>>> Here is the plan dump and error after running
>>>> > >>>> relRunner.prepareStatement.
>>>> > >>>>
>>>> > >>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 30.0,
>>>> > >>>> cumulative cost = {146.0 rows, 1393.2 cpu, 0.0 io}, id = 213
>>>> > >>>> EnumerableLimit(fetch=[100]): rowcount = 30.0, cumulative cost =
>>>> > {116.0
>>>> > >>>> rows, 1333.2 cpu, 0.0 io}, id = 212 EnumerableUnion(all=[true]):
>>>> > rowcount =
>>>> > >>>> 30.0, cumulative cost = {86.0 rows, 1303.2 cpu, 0.0 io}, id = 211
>>>> > >>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0,
>>>> > cumulative
>>>> > >>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 207
>>>> > LDAPToEnumerableConverter:
>>>> > >>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0
>>>> io}, id
>>>> > = 206
>>>> > >>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0,
>>>> > cumulative
>>>> > >>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 205
>>>> > >>>> LDAPTableScan(table=[[SCIMUserSchema, cap_internet_ddpmildap]]):
>>>> > rowcount =
>>>> > >>>> 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0
>>>> io},
>>>> > id =
>>>> > >>>> 0 EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0,
>>>> > cumulative
>>>> > >>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 210
>>>> > LDAPToEnumerableConverter:
>>>> > >>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0
>>>> io}, id
>>>> > = 209
>>>> > >>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0,
>>>> > cumulative
>>>> > >>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 208
>>>> > >>>> LDAPTableScan(table=[[SCIMUserSchema, prov_internet_ddpmildap]]):
>>>> > rowcount
>>>> > >>>> = 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu,
>>>> 0.0
>>>> > io}, id
>>>> > >>>> = 2
>>>> > >>>>
>>>> > >>>>  java.lang.AssertionError: null at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.adapter.enumerable.EnumerableLimit.<init>(EnumerableLimit.java:60)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:84)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:43)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:378)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
>>>> > >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
>>>> > >>>>
>>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:228)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:514)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
>>>> > >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
>>>> > >>>>
>>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trim(RelFieldTrimmer.java:173)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.sql2rel.SqlToRelConverter.trimUnusedFields(SqlToRelConverter.java:567)
>>>> > >>>> at
>>>> > org.apache.calcite.prepare.Prepare.trimUnusedFields(Prepare.java:386)
>>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1061)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1026)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:687)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:237)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:217)
>>>> > >>>> at
>>>> > >>>>
>>>> >
>>>> org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187)
>>>> > >>>>
>>>> > >>>>
>>>> > >>>> On Fri, Jul 19, 2024 at 11:07 Ruben Q L <ru...@gmail.com>
>>>> wrote:
>>>> > >>>>
>>>> > >>>>> Eric, if you want to use EnumerableUnion instead of
>>>> implementing your
>>>> > >>>>> own
>>>> > >>>>> Union, then I think you'll need to implement the appropriate
>>>> > Converter
>>>> > >>>>> [1]
>>>> > >>>>> to transform between conventions LDAP <=> ENUMERABLE
>>>> > >>>>>
>>>> > >>>>> Best,
>>>> > >>>>> Ruben
>>>> > >>>>>
>>>> > >>>>> [1]
>>>> > >>>>>
>>>> > >>>>>
>>>> >
>>>> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/convert/Converter.java
>>>> > >>>>>
>>>> > >>>>>
>>>> > >>>>> On Fri, Jul 19, 2024 at 3:56 PM Eric Berryman <
>>>> > eric.berryman@gmail.com
>>>> > >>>>> >
>>>> > >>>>> wrote:
>>>> > >>>>>
>>>> > >>>>> > I implement an LDAPUnion and rule to convert from
>>>> logicalunion, and
>>>> > >>>>> of
>>>> > >>>>> > course, the error goes away. But I didn’t implement the union
>>>> > >>>>> correctly, so
>>>> > >>>>> > nothing is returned in my query. Is there a way to have my
>>>> > LDAPUnion
>>>> > >>>>> > use EnumerableUnion,
>>>> > >>>>> > or a way to not have LDAPUnion and take care of this with a
>>>> rule?
>>>> > >>>>> >
>>>> > >>>>> > Thank you again!
>>>> > >>>>> > Eric
>>>> > >>>>> >
>>>> > >>>>> > On Tue, Jul 16, 2024 at 14:24 Eric Berryman <
>>>> > eric.berryman@gmail.com
>>>> > >>>>> >
>>>> > >>>>> > wrote:
>>>> > >>>>> >
>>>> > >>>>> > > Well, I was wrong. That didn’t solve my problem with :
>>>> > >>>>> > >
>>>> > >>>>> > > Missing conversion is LogicalUnion[convention: NONE -> LDAP]
>>>> > >>>>> > >
>>>> > >>>>> > > Do I have to implement my own union? Is there a way I could
>>>> just
>>>> > >>>>> use the
>>>> > >>>>> > > EnumerableUnion?
>>>> > >>>>> > > ie. NONE -> Enumerable and skip implementing one for my ldap
>>>> > >>>>> datastore?
>>>> > >>>>> > >
>>>> > >>>>> > > Thank you!
>>>> > >>>>> > > Eric
>>>> > >>>>> > >
>>>> > >>>>> > > On Tue, Jul 16, 2024 at 09:00 Eric Berryman <
>>>> > >>>>> eric.berryman@gmail.com>
>>>> > >>>>> > > wrote:
>>>> > >>>>> > >
>>>> > >>>>> > >> When I register my tablescan object, and add my rules, I
>>>> also
>>>> > >>>>> added a
>>>> > >>>>> > >> removeRule for
>>>> EnumerableRules.ENUMERABLE_MERGE_UNION_RULE, and
>>>> > >>>>> now
>>>> > >>>>> > >> everything works with an offset also.
>>>> > >>>>> > >>
>>>> > >>>>> > >> Although, I don’t know why this is. I noticed the
>>>> > >>>>> EnumerableMergeUnion
>>>> > >>>>> > >> object in the plan, and thought I would try to remove it,
>>>> > because
>>>> > >>>>> it was
>>>> > >>>>> > >> different.
>>>> > >>>>> > >>
>>>> > >>>>> > >> Any explanation is appreciated, thank you!!!
>>>> > >>>>> > >> Eric
>>>> > >>>>> > >>
>>>> > >>>>> > >>
>>>> > >>>>> > >> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <
>>>> > >>>>> eric.berryman@gmail.com>
>>>> > >>>>> > >> wrote:
>>>> > >>>>> > >>
>>>> > >>>>> > >>> Hello!
>>>> > >>>>> > >>>
>>>> > >>>>> > >>> I seem to have an issue with my new limit rule which
>>>> pushes
>>>> > down
>>>> > >>>>> to the
>>>> > >>>>> > >>> datastore. It works fine, unless I add an offset to the
>>>> fetch.
>>>> > >>>>> Where I
>>>> > >>>>> > end
>>>> > >>>>> > >>> up with the following error:
>>>> > >>>>> > >>> There are not enough rules… Missing conversion is
>>>> > >>>>> > >>> LogicalUnion[convention: NONE -> LDAP]
>>>> > >>>>> > >>>
>>>> > >>>>> > >>> Why would this only come up when an offset value is added?
>>>> > >>>>> > >>>
>>>> > >>>>> > >>> Thank you!
>>>> > >>>>> > >>> Eric
>>>> > >>>>> > >>>
>>>> > >>>>> > >>>
>>>> > >>>>> > >>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <
>>>> > >>>>> eric.berryman@gmail.com>
>>>> > >>>>> > >>> wrote:
>>>> > >>>>> > >>>
>>>> > >>>>> > >>>> That’s perfect
>>>> > >>>>> > >>>>
>>>> > >>>>> > >>>>
>>>> > >>>>> >
>>>> > >>>>>
>>>> >
>>>> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
>>>> > >>>>> > >>>>
>>>> > >>>>> > >>>> Thank you for such a quick response!
>>>> > >>>>> > >>>> Eric
>>>> > >>>>> > >>>>
>>>> > >>>>> > >>>>
>>>> > >>>>> > >>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <
>>>> mmior@apache.org>
>>>> > >>>>> wrote:
>>>> > >>>>> > >>>>
>>>> > >>>>> > >>>>> Eric,
>>>> > >>>>> > >>>>>
>>>> > >>>>> > >>>>> Could you give a more specific example of the failure
>>>> > scenario
>>>> > >>>>> you're
>>>> > >>>>> > >>>>> experiencing?
>>>> > >>>>> > >>>>>
>>>> > >>>>> > >>>>> For a simple example of how limits can be pushed down,
>>>> this
>>>> > is
>>>> > >>>>> done
>>>> > >>>>> > in
>>>> > >>>>> > >>>>> the
>>>> > >>>>> > >>>>> Cassandra adapter with CassandraLimitRule. It matches an
>>>> > >>>>> > >>>>> EnumerableLimit on
>>>> > >>>>> > >>>>> top of a CassandraToEnumerableConverter and then
>>>> converts
>>>> > that
>>>> > >>>>> limit
>>>> > >>>>> > >>>>> to a
>>>> > >>>>> > >>>>> CassandraLimit which passes along the limit and offset
>>>> > >>>>> information to
>>>> > >>>>> > >>>>> CassandraToEnumerableConverter for when the query is
>>>> > executed.
>>>> > >>>>> The
>>>> > >>>>> > >>>>> EnumerableLimit is then replaced with a CassandraLimit
>>>> > >>>>> effectively
>>>> > >>>>> > as a
>>>> > >>>>> > >>>>> placeholder to signal that the limit has been handled.
>>>> > >>>>> > >>>>>
>>>> > >>>>> > >>>>> --
>>>> > >>>>> > >>>>> Michael Mior
>>>> > >>>>> > >>>>> mmior@apache.org
>>>> > >>>>> > >>>>>
>>>> > >>>>> > >>>>>
>>>> > >>>>> > >>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <
>>>> > >>>>> > eric.berryman@gmail.com
>>>> > >>>>> > >>>>> >
>>>> > >>>>> > >>>>> wrote:
>>>> > >>>>> > >>>>>
>>>> > >>>>> > >>>>> > Hello!
>>>> > >>>>> > >>>>> >
>>>> > >>>>> > >>>>> > When I add limit to my relbuilder object, the planner
>>>> gives
>>>> > >>>>> up. But
>>>> > >>>>> > >>>>> works
>>>> > >>>>> > >>>>> > fine without it.
>>>> > >>>>> > >>>>> >
>>>> > >>>>> > >>>>> > I wasn’t able to find any examples of using limit and
>>>> > >>>>> pushing the
>>>> > >>>>> > >>>>> limit
>>>> > >>>>> > >>>>> > values down to a data source.
>>>> > >>>>> > >>>>> >
>>>> > >>>>> > >>>>> > Could someone help with some links on this subject?
>>>> > >>>>> > >>>>> >
>>>> > >>>>> > >>>>> > Thank you!
>>>> > >>>>> > >>>>> > Eric
>>>> > >>>>> > >>>>> >
>>>> > >>>>> > >>>>>
>>>> > >>>>> > >>>>
>>>> > >>>>> >
>>>> > >>>>>
>>>> > >>>>
>>>> >
>>>>
>>>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

Is there something I’m missing about
EnumerableLimit? It seems like I get an infinite cost anytime it’s in my
path?

Thank you again,
Eric

On Mon, Aug 19, 2024 at 18:00 Eric Berryman <er...@gmail.com> wrote:

> I see the red circles in the svg query plan I added to the gist. I’m just
> not sure what it’s supposed to be telling me.
>
> Thank you!
> Eric
>
> On Mon, Aug 19, 2024 at 11:35 Eric Berryman <er...@gmail.com>
> wrote:
>
>> 1.37.0
>>
>> On Mon, Aug 19, 2024 at 11:28 Ruben Q L <ru...@gmail.com> wrote:
>>
>>> Which Calcite version are you using?
>>>
>>>
>>> On Mon, Aug 19, 2024 at 4:22 PM Eric Berryman <er...@gmail.com>
>>> wrote:
>>>
>>> > I’ve noticed if I remove the limit, the EnumerableUnion works fine. So
>>> it
>>> > seems the infinite cost is coming from the EnumerableLimit outside of
>>> the
>>> > union. I have an LDAPSortLimit which is pushed past the union, and an
>>> > EnumerableLimit
>>> > is left outside the union, as you can see in the gist.
>>> >
>>> > On Mon, Aug 19, 2024 at 11:10 Eric Berryman <er...@gmail.com>
>>> > wrote:
>>> >
>>> > > Is there anymore information that would be helpful for me to give to
>>> aide
>>> > > in troubleshooting this?
>>> > >
>>> > > Thank you!
>>> > > Eric
>>> > >
>>> > > On Thu, Aug 15, 2024 at 16:51 Eric Berryman <eric.berryman@gmail.com
>>> >
>>> > > wrote:
>>> > >
>>> > >>
>>> > >> I seem to be struggling with unions still. Here is a gist with the
>>> > output
>>> > >> and svg of the plan.
>>> > >>
>>> > >> https://gist.github.com/berryma4/c4870dd4e292e0509d1c85c308b52e67
>>> > >>
>>> > >> I need a little help on direction of what the svg is telling me.  I
>>> see
>>> > >> the cost is infinite, but I don’t see where I’m missing a rule.
>>> > >>
>>> > >>
>>> > >> Thank you for your help,
>>> > >> Eric
>>> > >>
>>> > >>
>>> > >>
>>> > >> On Mon, Jul 29, 2024 at 18:28 Eric Berryman <
>>> eric.berryman@gmail.com>
>>> > >> wrote:
>>> > >>
>>> > >>> That didn't paste well. Here is a gist:
>>> > >>> https://gist.github.com/berryma4/c6c09da050f273295edd23c045c63403
>>> > >>>
>>> > >>>
>>> > >>>
>>> > >>> On Mon, Jul 29, 2024 at 4:58 PM Eric Berryman <
>>> eric.berryman@gmail.com
>>> > >
>>> > >>> wrote:
>>> > >>>
>>> > >>>> I’m back from a long holiday, and seem stuck still on this
>>> scenario.
>>> > >>>> The plan gets created without any issues, but I get this error
>>> while
>>> > >>>> executing queries with unions.
>>> > >>>>
>>> > >>>> Thank you again!
>>> > >>>> Eric
>>> > >>>>
>>> > >>>> Here is the plan dump and error after running
>>> > >>>> relRunner.prepareStatement.
>>> > >>>>
>>> > >>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 30.0,
>>> > >>>> cumulative cost = {146.0 rows, 1393.2 cpu, 0.0 io}, id = 213
>>> > >>>> EnumerableLimit(fetch=[100]): rowcount = 30.0, cumulative cost =
>>> > {116.0
>>> > >>>> rows, 1333.2 cpu, 0.0 io}, id = 212 EnumerableUnion(all=[true]):
>>> > rowcount =
>>> > >>>> 30.0, cumulative cost = {86.0 rows, 1303.2 cpu, 0.0 io}, id = 211
>>> > >>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0,
>>> > cumulative
>>> > >>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 207
>>> > LDAPToEnumerableConverter:
>>> > >>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io},
>>> id
>>> > = 206
>>> > >>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0,
>>> > cumulative
>>> > >>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 205
>>> > >>>> LDAPTableScan(table=[[SCIMUserSchema, cap_internet_ddpmildap]]):
>>> > rowcount =
>>> > >>>> 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0
>>> io},
>>> > id =
>>> > >>>> 0 EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0,
>>> > cumulative
>>> > >>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 210
>>> > LDAPToEnumerableConverter:
>>> > >>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io},
>>> id
>>> > = 209
>>> > >>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0,
>>> > cumulative
>>> > >>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 208
>>> > >>>> LDAPTableScan(table=[[SCIMUserSchema, prov_internet_ddpmildap]]):
>>> > rowcount
>>> > >>>> = 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0
>>> > io}, id
>>> > >>>> = 2
>>> > >>>>
>>> > >>>>  java.lang.AssertionError: null at
>>> > >>>>
>>> >
>>> org.apache.calcite.adapter.enumerable.EnumerableLimit.<init>(EnumerableLimit.java:60)
>>> > >>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:84)
>>> > >>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:43)
>>> > >>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:378)
>>> > >>>> at
>>> > >>>>
>>> >
>>> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
>>> > >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
>>> > >>>>
>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
>>> > >>>>
>>> >
>>> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
>>> > >>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:228)
>>> > >>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:514)
>>> > >>>> at
>>> > >>>>
>>> >
>>> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
>>> > >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
>>> > >>>>
>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
>>> > >>>>
>>> >
>>> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
>>> > >>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trim(RelFieldTrimmer.java:173)
>>> > >>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.sql2rel.SqlToRelConverter.trimUnusedFields(SqlToRelConverter.java:567)
>>> > >>>> at
>>> > org.apache.calcite.prepare.Prepare.trimUnusedFields(Prepare.java:386)
>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1061)
>>> > >>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1026)
>>> > >>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:687)
>>> > >>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
>>> > >>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
>>> > >>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:237)
>>> > >>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:217)
>>> > >>>> at
>>> > >>>>
>>> >
>>> org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187)
>>> > >>>>
>>> > >>>>
>>> > >>>> On Fri, Jul 19, 2024 at 11:07 Ruben Q L <ru...@gmail.com>
>>> wrote:
>>> > >>>>
>>> > >>>>> Eric, if you want to use EnumerableUnion instead of implementing
>>> your
>>> > >>>>> own
>>> > >>>>> Union, then I think you'll need to implement the appropriate
>>> > Converter
>>> > >>>>> [1]
>>> > >>>>> to transform between conventions LDAP <=> ENUMERABLE
>>> > >>>>>
>>> > >>>>> Best,
>>> > >>>>> Ruben
>>> > >>>>>
>>> > >>>>> [1]
>>> > >>>>>
>>> > >>>>>
>>> >
>>> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/convert/Converter.java
>>> > >>>>>
>>> > >>>>>
>>> > >>>>> On Fri, Jul 19, 2024 at 3:56 PM Eric Berryman <
>>> > eric.berryman@gmail.com
>>> > >>>>> >
>>> > >>>>> wrote:
>>> > >>>>>
>>> > >>>>> > I implement an LDAPUnion and rule to convert from
>>> logicalunion, and
>>> > >>>>> of
>>> > >>>>> > course, the error goes away. But I didn’t implement the union
>>> > >>>>> correctly, so
>>> > >>>>> > nothing is returned in my query. Is there a way to have my
>>> > LDAPUnion
>>> > >>>>> > use EnumerableUnion,
>>> > >>>>> > or a way to not have LDAPUnion and take care of this with a
>>> rule?
>>> > >>>>> >
>>> > >>>>> > Thank you again!
>>> > >>>>> > Eric
>>> > >>>>> >
>>> > >>>>> > On Tue, Jul 16, 2024 at 14:24 Eric Berryman <
>>> > eric.berryman@gmail.com
>>> > >>>>> >
>>> > >>>>> > wrote:
>>> > >>>>> >
>>> > >>>>> > > Well, I was wrong. That didn’t solve my problem with :
>>> > >>>>> > >
>>> > >>>>> > > Missing conversion is LogicalUnion[convention: NONE -> LDAP]
>>> > >>>>> > >
>>> > >>>>> > > Do I have to implement my own union? Is there a way I could
>>> just
>>> > >>>>> use the
>>> > >>>>> > > EnumerableUnion?
>>> > >>>>> > > ie. NONE -> Enumerable and skip implementing one for my ldap
>>> > >>>>> datastore?
>>> > >>>>> > >
>>> > >>>>> > > Thank you!
>>> > >>>>> > > Eric
>>> > >>>>> > >
>>> > >>>>> > > On Tue, Jul 16, 2024 at 09:00 Eric Berryman <
>>> > >>>>> eric.berryman@gmail.com>
>>> > >>>>> > > wrote:
>>> > >>>>> > >
>>> > >>>>> > >> When I register my tablescan object, and add my rules, I
>>> also
>>> > >>>>> added a
>>> > >>>>> > >> removeRule for EnumerableRules.ENUMERABLE_MERGE_UNION_RULE,
>>> and
>>> > >>>>> now
>>> > >>>>> > >> everything works with an offset also.
>>> > >>>>> > >>
>>> > >>>>> > >> Although, I don’t know why this is. I noticed the
>>> > >>>>> EnumerableMergeUnion
>>> > >>>>> > >> object in the plan, and thought I would try to remove it,
>>> > because
>>> > >>>>> it was
>>> > >>>>> > >> different.
>>> > >>>>> > >>
>>> > >>>>> > >> Any explanation is appreciated, thank you!!!
>>> > >>>>> > >> Eric
>>> > >>>>> > >>
>>> > >>>>> > >>
>>> > >>>>> > >> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <
>>> > >>>>> eric.berryman@gmail.com>
>>> > >>>>> > >> wrote:
>>> > >>>>> > >>
>>> > >>>>> > >>> Hello!
>>> > >>>>> > >>>
>>> > >>>>> > >>> I seem to have an issue with my new limit rule which pushes
>>> > down
>>> > >>>>> to the
>>> > >>>>> > >>> datastore. It works fine, unless I add an offset to the
>>> fetch.
>>> > >>>>> Where I
>>> > >>>>> > end
>>> > >>>>> > >>> up with the following error:
>>> > >>>>> > >>> There are not enough rules… Missing conversion is
>>> > >>>>> > >>> LogicalUnion[convention: NONE -> LDAP]
>>> > >>>>> > >>>
>>> > >>>>> > >>> Why would this only come up when an offset value is added?
>>> > >>>>> > >>>
>>> > >>>>> > >>> Thank you!
>>> > >>>>> > >>> Eric
>>> > >>>>> > >>>
>>> > >>>>> > >>>
>>> > >>>>> > >>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <
>>> > >>>>> eric.berryman@gmail.com>
>>> > >>>>> > >>> wrote:
>>> > >>>>> > >>>
>>> > >>>>> > >>>> That’s perfect
>>> > >>>>> > >>>>
>>> > >>>>> > >>>>
>>> > >>>>> >
>>> > >>>>>
>>> >
>>> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
>>> > >>>>> > >>>>
>>> > >>>>> > >>>> Thank you for such a quick response!
>>> > >>>>> > >>>> Eric
>>> > >>>>> > >>>>
>>> > >>>>> > >>>>
>>> > >>>>> > >>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <
>>> mmior@apache.org>
>>> > >>>>> wrote:
>>> > >>>>> > >>>>
>>> > >>>>> > >>>>> Eric,
>>> > >>>>> > >>>>>
>>> > >>>>> > >>>>> Could you give a more specific example of the failure
>>> > scenario
>>> > >>>>> you're
>>> > >>>>> > >>>>> experiencing?
>>> > >>>>> > >>>>>
>>> > >>>>> > >>>>> For a simple example of how limits can be pushed down,
>>> this
>>> > is
>>> > >>>>> done
>>> > >>>>> > in
>>> > >>>>> > >>>>> the
>>> > >>>>> > >>>>> Cassandra adapter with CassandraLimitRule. It matches an
>>> > >>>>> > >>>>> EnumerableLimit on
>>> > >>>>> > >>>>> top of a CassandraToEnumerableConverter and then converts
>>> > that
>>> > >>>>> limit
>>> > >>>>> > >>>>> to a
>>> > >>>>> > >>>>> CassandraLimit which passes along the limit and offset
>>> > >>>>> information to
>>> > >>>>> > >>>>> CassandraToEnumerableConverter for when the query is
>>> > executed.
>>> > >>>>> The
>>> > >>>>> > >>>>> EnumerableLimit is then replaced with a CassandraLimit
>>> > >>>>> effectively
>>> > >>>>> > as a
>>> > >>>>> > >>>>> placeholder to signal that the limit has been handled.
>>> > >>>>> > >>>>>
>>> > >>>>> > >>>>> --
>>> > >>>>> > >>>>> Michael Mior
>>> > >>>>> > >>>>> mmior@apache.org
>>> > >>>>> > >>>>>
>>> > >>>>> > >>>>>
>>> > >>>>> > >>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <
>>> > >>>>> > eric.berryman@gmail.com
>>> > >>>>> > >>>>> >
>>> > >>>>> > >>>>> wrote:
>>> > >>>>> > >>>>>
>>> > >>>>> > >>>>> > Hello!
>>> > >>>>> > >>>>> >
>>> > >>>>> > >>>>> > When I add limit to my relbuilder object, the planner
>>> gives
>>> > >>>>> up. But
>>> > >>>>> > >>>>> works
>>> > >>>>> > >>>>> > fine without it.
>>> > >>>>> > >>>>> >
>>> > >>>>> > >>>>> > I wasn’t able to find any examples of using limit and
>>> > >>>>> pushing the
>>> > >>>>> > >>>>> limit
>>> > >>>>> > >>>>> > values down to a data source.
>>> > >>>>> > >>>>> >
>>> > >>>>> > >>>>> > Could someone help with some links on this subject?
>>> > >>>>> > >>>>> >
>>> > >>>>> > >>>>> > Thank you!
>>> > >>>>> > >>>>> > Eric
>>> > >>>>> > >>>>> >
>>> > >>>>> > >>>>>
>>> > >>>>> > >>>>
>>> > >>>>> >
>>> > >>>>>
>>> > >>>>
>>> >
>>>
>>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

I see the red circles in the svg query plan I added to the gist. I’m just
not sure what it’s supposed to be telling me.

Thank you!
Eric

On Mon, Aug 19, 2024 at 11:35 Eric Berryman <er...@gmail.com> wrote:

> 1.37.0
>
> On Mon, Aug 19, 2024 at 11:28 Ruben Q L <ru...@gmail.com> wrote:
>
>> Which Calcite version are you using?
>>
>>
>> On Mon, Aug 19, 2024 at 4:22 PM Eric Berryman <er...@gmail.com>
>> wrote:
>>
>> > I’ve noticed if I remove the limit, the EnumerableUnion works fine. So
>> it
>> > seems the infinite cost is coming from the EnumerableLimit outside of
>> the
>> > union. I have an LDAPSortLimit which is pushed past the union, and an
>> > EnumerableLimit
>> > is left outside the union, as you can see in the gist.
>> >
>> > On Mon, Aug 19, 2024 at 11:10 Eric Berryman <er...@gmail.com>
>> > wrote:
>> >
>> > > Is there anymore information that would be helpful for me to give to
>> aide
>> > > in troubleshooting this?
>> > >
>> > > Thank you!
>> > > Eric
>> > >
>> > > On Thu, Aug 15, 2024 at 16:51 Eric Berryman <er...@gmail.com>
>> > > wrote:
>> > >
>> > >>
>> > >> I seem to be struggling with unions still. Here is a gist with the
>> > output
>> > >> and svg of the plan.
>> > >>
>> > >> https://gist.github.com/berryma4/c4870dd4e292e0509d1c85c308b52e67
>> > >>
>> > >> I need a little help on direction of what the svg is telling me.  I
>> see
>> > >> the cost is infinite, but I don’t see where I’m missing a rule.
>> > >>
>> > >>
>> > >> Thank you for your help,
>> > >> Eric
>> > >>
>> > >>
>> > >>
>> > >> On Mon, Jul 29, 2024 at 18:28 Eric Berryman <eric.berryman@gmail.com
>> >
>> > >> wrote:
>> > >>
>> > >>> That didn't paste well. Here is a gist:
>> > >>> https://gist.github.com/berryma4/c6c09da050f273295edd23c045c63403
>> > >>>
>> > >>>
>> > >>>
>> > >>> On Mon, Jul 29, 2024 at 4:58 PM Eric Berryman <
>> eric.berryman@gmail.com
>> > >
>> > >>> wrote:
>> > >>>
>> > >>>> I’m back from a long holiday, and seem stuck still on this
>> scenario.
>> > >>>> The plan gets created without any issues, but I get this error
>> while
>> > >>>> executing queries with unions.
>> > >>>>
>> > >>>> Thank you again!
>> > >>>> Eric
>> > >>>>
>> > >>>> Here is the plan dump and error after running
>> > >>>> relRunner.prepareStatement.
>> > >>>>
>> > >>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 30.0,
>> > >>>> cumulative cost = {146.0 rows, 1393.2 cpu, 0.0 io}, id = 213
>> > >>>> EnumerableLimit(fetch=[100]): rowcount = 30.0, cumulative cost =
>> > {116.0
>> > >>>> rows, 1333.2 cpu, 0.0 io}, id = 212 EnumerableUnion(all=[true]):
>> > rowcount =
>> > >>>> 30.0, cumulative cost = {86.0 rows, 1303.2 cpu, 0.0 io}, id = 211
>> > >>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0,
>> > cumulative
>> > >>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 207
>> > LDAPToEnumerableConverter:
>> > >>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io},
>> id
>> > = 206
>> > >>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0,
>> > cumulative
>> > >>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 205
>> > >>>> LDAPTableScan(table=[[SCIMUserSchema, cap_internet_ddpmildap]]):
>> > rowcount =
>> > >>>> 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0
>> io},
>> > id =
>> > >>>> 0 EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0,
>> > cumulative
>> > >>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 210
>> > LDAPToEnumerableConverter:
>> > >>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io},
>> id
>> > = 209
>> > >>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0,
>> > cumulative
>> > >>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 208
>> > >>>> LDAPTableScan(table=[[SCIMUserSchema, prov_internet_ddpmildap]]):
>> > rowcount
>> > >>>> = 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0
>> > io}, id
>> > >>>> = 2
>> > >>>>
>> > >>>>  java.lang.AssertionError: null at
>> > >>>>
>> >
>> org.apache.calcite.adapter.enumerable.EnumerableLimit.<init>(EnumerableLimit.java:60)
>> > >>>> at
>> > >>>>
>> >
>> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:84)
>> > >>>> at
>> > >>>>
>> >
>> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:43)
>> > >>>> at
>> > >>>>
>> >
>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:378)
>> > >>>> at
>> > >>>>
>> >
>> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
>> > >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
>> > >>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
>> at
>> > >>>>
>> >
>> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
>> > >>>> at
>> > >>>>
>> >
>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:228)
>> > >>>> at
>> > >>>>
>> >
>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:514)
>> > >>>> at
>> > >>>>
>> >
>> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
>> > >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
>> > >>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
>> at
>> > >>>>
>> >
>> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
>> > >>>> at
>> > >>>>
>> >
>> org.apache.calcite.sql2rel.RelFieldTrimmer.trim(RelFieldTrimmer.java:173)
>> > >>>> at
>> > >>>>
>> >
>> org.apache.calcite.sql2rel.SqlToRelConverter.trimUnusedFields(SqlToRelConverter.java:567)
>> > >>>> at
>> > org.apache.calcite.prepare.Prepare.trimUnusedFields(Prepare.java:386) at
>> > >>>>
>> >
>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1061)
>> > >>>> at
>> > >>>>
>> >
>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1026)
>> > >>>> at
>> > >>>>
>> >
>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:687)
>> > >>>> at
>> > >>>>
>> >
>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
>> > >>>> at
>> > >>>>
>> >
>> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
>> > >>>> at
>> > >>>>
>> >
>> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:237)
>> > >>>> at
>> > >>>>
>> >
>> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:217)
>> > >>>> at
>> > >>>>
>> >
>> org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187)
>> > >>>>
>> > >>>>
>> > >>>> On Fri, Jul 19, 2024 at 11:07 Ruben Q L <ru...@gmail.com> wrote:
>> > >>>>
>> > >>>>> Eric, if you want to use EnumerableUnion instead of implementing
>> your
>> > >>>>> own
>> > >>>>> Union, then I think you'll need to implement the appropriate
>> > Converter
>> > >>>>> [1]
>> > >>>>> to transform between conventions LDAP <=> ENUMERABLE
>> > >>>>>
>> > >>>>> Best,
>> > >>>>> Ruben
>> > >>>>>
>> > >>>>> [1]
>> > >>>>>
>> > >>>>>
>> >
>> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/convert/Converter.java
>> > >>>>>
>> > >>>>>
>> > >>>>> On Fri, Jul 19, 2024 at 3:56 PM Eric Berryman <
>> > eric.berryman@gmail.com
>> > >>>>> >
>> > >>>>> wrote:
>> > >>>>>
>> > >>>>> > I implement an LDAPUnion and rule to convert from logicalunion,
>> and
>> > >>>>> of
>> > >>>>> > course, the error goes away. But I didn’t implement the union
>> > >>>>> correctly, so
>> > >>>>> > nothing is returned in my query. Is there a way to have my
>> > LDAPUnion
>> > >>>>> > use EnumerableUnion,
>> > >>>>> > or a way to not have LDAPUnion and take care of this with a
>> rule?
>> > >>>>> >
>> > >>>>> > Thank you again!
>> > >>>>> > Eric
>> > >>>>> >
>> > >>>>> > On Tue, Jul 16, 2024 at 14:24 Eric Berryman <
>> > eric.berryman@gmail.com
>> > >>>>> >
>> > >>>>> > wrote:
>> > >>>>> >
>> > >>>>> > > Well, I was wrong. That didn’t solve my problem with :
>> > >>>>> > >
>> > >>>>> > > Missing conversion is LogicalUnion[convention: NONE -> LDAP]
>> > >>>>> > >
>> > >>>>> > > Do I have to implement my own union? Is there a way I could
>> just
>> > >>>>> use the
>> > >>>>> > > EnumerableUnion?
>> > >>>>> > > ie. NONE -> Enumerable and skip implementing one for my ldap
>> > >>>>> datastore?
>> > >>>>> > >
>> > >>>>> > > Thank you!
>> > >>>>> > > Eric
>> > >>>>> > >
>> > >>>>> > > On Tue, Jul 16, 2024 at 09:00 Eric Berryman <
>> > >>>>> eric.berryman@gmail.com>
>> > >>>>> > > wrote:
>> > >>>>> > >
>> > >>>>> > >> When I register my tablescan object, and add my rules, I also
>> > >>>>> added a
>> > >>>>> > >> removeRule for EnumerableRules.ENUMERABLE_MERGE_UNION_RULE,
>> and
>> > >>>>> now
>> > >>>>> > >> everything works with an offset also.
>> > >>>>> > >>
>> > >>>>> > >> Although, I don’t know why this is. I noticed the
>> > >>>>> EnumerableMergeUnion
>> > >>>>> > >> object in the plan, and thought I would try to remove it,
>> > because
>> > >>>>> it was
>> > >>>>> > >> different.
>> > >>>>> > >>
>> > >>>>> > >> Any explanation is appreciated, thank you!!!
>> > >>>>> > >> Eric
>> > >>>>> > >>
>> > >>>>> > >>
>> > >>>>> > >> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <
>> > >>>>> eric.berryman@gmail.com>
>> > >>>>> > >> wrote:
>> > >>>>> > >>
>> > >>>>> > >>> Hello!
>> > >>>>> > >>>
>> > >>>>> > >>> I seem to have an issue with my new limit rule which pushes
>> > down
>> > >>>>> to the
>> > >>>>> > >>> datastore. It works fine, unless I add an offset to the
>> fetch.
>> > >>>>> Where I
>> > >>>>> > end
>> > >>>>> > >>> up with the following error:
>> > >>>>> > >>> There are not enough rules… Missing conversion is
>> > >>>>> > >>> LogicalUnion[convention: NONE -> LDAP]
>> > >>>>> > >>>
>> > >>>>> > >>> Why would this only come up when an offset value is added?
>> > >>>>> > >>>
>> > >>>>> > >>> Thank you!
>> > >>>>> > >>> Eric
>> > >>>>> > >>>
>> > >>>>> > >>>
>> > >>>>> > >>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <
>> > >>>>> eric.berryman@gmail.com>
>> > >>>>> > >>> wrote:
>> > >>>>> > >>>
>> > >>>>> > >>>> That’s perfect
>> > >>>>> > >>>>
>> > >>>>> > >>>>
>> > >>>>> >
>> > >>>>>
>> >
>> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
>> > >>>>> > >>>>
>> > >>>>> > >>>> Thank you for such a quick response!
>> > >>>>> > >>>> Eric
>> > >>>>> > >>>>
>> > >>>>> > >>>>
>> > >>>>> > >>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <
>> mmior@apache.org>
>> > >>>>> wrote:
>> > >>>>> > >>>>
>> > >>>>> > >>>>> Eric,
>> > >>>>> > >>>>>
>> > >>>>> > >>>>> Could you give a more specific example of the failure
>> > scenario
>> > >>>>> you're
>> > >>>>> > >>>>> experiencing?
>> > >>>>> > >>>>>
>> > >>>>> > >>>>> For a simple example of how limits can be pushed down,
>> this
>> > is
>> > >>>>> done
>> > >>>>> > in
>> > >>>>> > >>>>> the
>> > >>>>> > >>>>> Cassandra adapter with CassandraLimitRule. It matches an
>> > >>>>> > >>>>> EnumerableLimit on
>> > >>>>> > >>>>> top of a CassandraToEnumerableConverter and then converts
>> > that
>> > >>>>> limit
>> > >>>>> > >>>>> to a
>> > >>>>> > >>>>> CassandraLimit which passes along the limit and offset
>> > >>>>> information to
>> > >>>>> > >>>>> CassandraToEnumerableConverter for when the query is
>> > executed.
>> > >>>>> The
>> > >>>>> > >>>>> EnumerableLimit is then replaced with a CassandraLimit
>> > >>>>> effectively
>> > >>>>> > as a
>> > >>>>> > >>>>> placeholder to signal that the limit has been handled.
>> > >>>>> > >>>>>
>> > >>>>> > >>>>> --
>> > >>>>> > >>>>> Michael Mior
>> > >>>>> > >>>>> mmior@apache.org
>> > >>>>> > >>>>>
>> > >>>>> > >>>>>
>> > >>>>> > >>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <
>> > >>>>> > eric.berryman@gmail.com
>> > >>>>> > >>>>> >
>> > >>>>> > >>>>> wrote:
>> > >>>>> > >>>>>
>> > >>>>> > >>>>> > Hello!
>> > >>>>> > >>>>> >
>> > >>>>> > >>>>> > When I add limit to my relbuilder object, the planner
>> gives
>> > >>>>> up. But
>> > >>>>> > >>>>> works
>> > >>>>> > >>>>> > fine without it.
>> > >>>>> > >>>>> >
>> > >>>>> > >>>>> > I wasn’t able to find any examples of using limit and
>> > >>>>> pushing the
>> > >>>>> > >>>>> limit
>> > >>>>> > >>>>> > values down to a data source.
>> > >>>>> > >>>>> >
>> > >>>>> > >>>>> > Could someone help with some links on this subject?
>> > >>>>> > >>>>> >
>> > >>>>> > >>>>> > Thank you!
>> > >>>>> > >>>>> > Eric
>> > >>>>> > >>>>> >
>> > >>>>> > >>>>>
>> > >>>>> > >>>>
>> > >>>>> >
>> > >>>>>
>> > >>>>
>> >
>>
>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

1.37.0

On Mon, Aug 19, 2024 at 11:28 Ruben Q L <ru...@gmail.com> wrote:

> Which Calcite version are you using?
>
>
> On Mon, Aug 19, 2024 at 4:22 PM Eric Berryman <er...@gmail.com>
> wrote:
>
> > I’ve noticed if I remove the limit, the EnumerableUnion works fine. So it
> > seems the infinite cost is coming from the EnumerableLimit outside of the
> > union. I have an LDAPSortLimit which is pushed past the union, and an
> > EnumerableLimit
> > is left outside the union, as you can see in the gist.
> >
> > On Mon, Aug 19, 2024 at 11:10 Eric Berryman <er...@gmail.com>
> > wrote:
> >
> > > Is there anymore information that would be helpful for me to give to
> aide
> > > in troubleshooting this?
> > >
> > > Thank you!
> > > Eric
> > >
> > > On Thu, Aug 15, 2024 at 16:51 Eric Berryman <er...@gmail.com>
> > > wrote:
> > >
> > >>
> > >> I seem to be struggling with unions still. Here is a gist with the
> > output
> > >> and svg of the plan.
> > >>
> > >> https://gist.github.com/berryma4/c4870dd4e292e0509d1c85c308b52e67
> > >>
> > >> I need a little help on direction of what the svg is telling me.  I
> see
> > >> the cost is infinite, but I don’t see where I’m missing a rule.
> > >>
> > >>
> > >> Thank you for your help,
> > >> Eric
> > >>
> > >>
> > >>
> > >> On Mon, Jul 29, 2024 at 18:28 Eric Berryman <er...@gmail.com>
> > >> wrote:
> > >>
> > >>> That didn't paste well. Here is a gist:
> > >>> https://gist.github.com/berryma4/c6c09da050f273295edd23c045c63403
> > >>>
> > >>>
> > >>>
> > >>> On Mon, Jul 29, 2024 at 4:58 PM Eric Berryman <
> eric.berryman@gmail.com
> > >
> > >>> wrote:
> > >>>
> > >>>> I’m back from a long holiday, and seem stuck still on this scenario.
> > >>>> The plan gets created without any issues, but I get this error while
> > >>>> executing queries with unions.
> > >>>>
> > >>>> Thank you again!
> > >>>> Eric
> > >>>>
> > >>>> Here is the plan dump and error after running
> > >>>> relRunner.prepareStatement.
> > >>>>
> > >>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 30.0,
> > >>>> cumulative cost = {146.0 rows, 1393.2 cpu, 0.0 io}, id = 213
> > >>>> EnumerableLimit(fetch=[100]): rowcount = 30.0, cumulative cost =
> > {116.0
> > >>>> rows, 1333.2 cpu, 0.0 io}, id = 212 EnumerableUnion(all=[true]):
> > rowcount =
> > >>>> 30.0, cumulative cost = {86.0 rows, 1303.2 cpu, 0.0 io}, id = 211
> > >>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0,
> > cumulative
> > >>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 207
> > LDAPToEnumerableConverter:
> > >>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io}, id
> > = 206
> > >>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0,
> > cumulative
> > >>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 205
> > >>>> LDAPTableScan(table=[[SCIMUserSchema, cap_internet_ddpmildap]]):
> > rowcount =
> > >>>> 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0
> io},
> > id =
> > >>>> 0 EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0,
> > cumulative
> > >>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 210
> > LDAPToEnumerableConverter:
> > >>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io}, id
> > = 209
> > >>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0,
> > cumulative
> > >>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 208
> > >>>> LDAPTableScan(table=[[SCIMUserSchema, prov_internet_ddpmildap]]):
> > rowcount
> > >>>> = 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0
> > io}, id
> > >>>> = 2
> > >>>>
> > >>>>  java.lang.AssertionError: null at
> > >>>>
> >
> org.apache.calcite.adapter.enumerable.EnumerableLimit.<init>(EnumerableLimit.java:60)
> > >>>> at
> > >>>>
> >
> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:84)
> > >>>> at
> > >>>>
> >
> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:43)
> > >>>> at
> > >>>>
> >
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:378)
> > >>>> at
> > >>>>
> >
> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
> > >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
> > >>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
> at
> > >>>>
> >
> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
> > >>>> at
> > >>>>
> >
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:228)
> > >>>> at
> > >>>>
> >
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:514)
> > >>>> at
> > >>>>
> >
> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
> > >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
> > >>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533)
> at
> > >>>>
> >
> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
> > >>>> at
> > >>>>
> > org.apache.calcite.sql2rel.RelFieldTrimmer.trim(RelFieldTrimmer.java:173)
> > >>>> at
> > >>>>
> >
> org.apache.calcite.sql2rel.SqlToRelConverter.trimUnusedFields(SqlToRelConverter.java:567)
> > >>>> at
> > org.apache.calcite.prepare.Prepare.trimUnusedFields(Prepare.java:386) at
> > >>>>
> >
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1061)
> > >>>> at
> > >>>>
> >
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1026)
> > >>>> at
> > >>>>
> >
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:687)
> > >>>> at
> > >>>>
> >
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> > >>>> at
> > >>>>
> >
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> > >>>> at
> > >>>>
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:237)
> > >>>> at
> > >>>>
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:217)
> > >>>> at
> > >>>>
> >
> org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187)
> > >>>>
> > >>>>
> > >>>> On Fri, Jul 19, 2024 at 11:07 Ruben Q L <ru...@gmail.com> wrote:
> > >>>>
> > >>>>> Eric, if you want to use EnumerableUnion instead of implementing
> your
> > >>>>> own
> > >>>>> Union, then I think you'll need to implement the appropriate
> > Converter
> > >>>>> [1]
> > >>>>> to transform between conventions LDAP <=> ENUMERABLE
> > >>>>>
> > >>>>> Best,
> > >>>>> Ruben
> > >>>>>
> > >>>>> [1]
> > >>>>>
> > >>>>>
> >
> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/convert/Converter.java
> > >>>>>
> > >>>>>
> > >>>>> On Fri, Jul 19, 2024 at 3:56 PM Eric Berryman <
> > eric.berryman@gmail.com
> > >>>>> >
> > >>>>> wrote:
> > >>>>>
> > >>>>> > I implement an LDAPUnion and rule to convert from logicalunion,
> and
> > >>>>> of
> > >>>>> > course, the error goes away. But I didn’t implement the union
> > >>>>> correctly, so
> > >>>>> > nothing is returned in my query. Is there a way to have my
> > LDAPUnion
> > >>>>> > use EnumerableUnion,
> > >>>>> > or a way to not have LDAPUnion and take care of this with a rule?
> > >>>>> >
> > >>>>> > Thank you again!
> > >>>>> > Eric
> > >>>>> >
> > >>>>> > On Tue, Jul 16, 2024 at 14:24 Eric Berryman <
> > eric.berryman@gmail.com
> > >>>>> >
> > >>>>> > wrote:
> > >>>>> >
> > >>>>> > > Well, I was wrong. That didn’t solve my problem with :
> > >>>>> > >
> > >>>>> > > Missing conversion is LogicalUnion[convention: NONE -> LDAP]
> > >>>>> > >
> > >>>>> > > Do I have to implement my own union? Is there a way I could
> just
> > >>>>> use the
> > >>>>> > > EnumerableUnion?
> > >>>>> > > ie. NONE -> Enumerable and skip implementing one for my ldap
> > >>>>> datastore?
> > >>>>> > >
> > >>>>> > > Thank you!
> > >>>>> > > Eric
> > >>>>> > >
> > >>>>> > > On Tue, Jul 16, 2024 at 09:00 Eric Berryman <
> > >>>>> eric.berryman@gmail.com>
> > >>>>> > > wrote:
> > >>>>> > >
> > >>>>> > >> When I register my tablescan object, and add my rules, I also
> > >>>>> added a
> > >>>>> > >> removeRule for EnumerableRules.ENUMERABLE_MERGE_UNION_RULE,
> and
> > >>>>> now
> > >>>>> > >> everything works with an offset also.
> > >>>>> > >>
> > >>>>> > >> Although, I don’t know why this is. I noticed the
> > >>>>> EnumerableMergeUnion
> > >>>>> > >> object in the plan, and thought I would try to remove it,
> > because
> > >>>>> it was
> > >>>>> > >> different.
> > >>>>> > >>
> > >>>>> > >> Any explanation is appreciated, thank you!!!
> > >>>>> > >> Eric
> > >>>>> > >>
> > >>>>> > >>
> > >>>>> > >> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <
> > >>>>> eric.berryman@gmail.com>
> > >>>>> > >> wrote:
> > >>>>> > >>
> > >>>>> > >>> Hello!
> > >>>>> > >>>
> > >>>>> > >>> I seem to have an issue with my new limit rule which pushes
> > down
> > >>>>> to the
> > >>>>> > >>> datastore. It works fine, unless I add an offset to the
> fetch.
> > >>>>> Where I
> > >>>>> > end
> > >>>>> > >>> up with the following error:
> > >>>>> > >>> There are not enough rules… Missing conversion is
> > >>>>> > >>> LogicalUnion[convention: NONE -> LDAP]
> > >>>>> > >>>
> > >>>>> > >>> Why would this only come up when an offset value is added?
> > >>>>> > >>>
> > >>>>> > >>> Thank you!
> > >>>>> > >>> Eric
> > >>>>> > >>>
> > >>>>> > >>>
> > >>>>> > >>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <
> > >>>>> eric.berryman@gmail.com>
> > >>>>> > >>> wrote:
> > >>>>> > >>>
> > >>>>> > >>>> That’s perfect
> > >>>>> > >>>>
> > >>>>> > >>>>
> > >>>>> >
> > >>>>>
> >
> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
> > >>>>> > >>>>
> > >>>>> > >>>> Thank you for such a quick response!
> > >>>>> > >>>> Eric
> > >>>>> > >>>>
> > >>>>> > >>>>
> > >>>>> > >>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <
> mmior@apache.org>
> > >>>>> wrote:
> > >>>>> > >>>>
> > >>>>> > >>>>> Eric,
> > >>>>> > >>>>>
> > >>>>> > >>>>> Could you give a more specific example of the failure
> > scenario
> > >>>>> you're
> > >>>>> > >>>>> experiencing?
> > >>>>> > >>>>>
> > >>>>> > >>>>> For a simple example of how limits can be pushed down, this
> > is
> > >>>>> done
> > >>>>> > in
> > >>>>> > >>>>> the
> > >>>>> > >>>>> Cassandra adapter with CassandraLimitRule. It matches an
> > >>>>> > >>>>> EnumerableLimit on
> > >>>>> > >>>>> top of a CassandraToEnumerableConverter and then converts
> > that
> > >>>>> limit
> > >>>>> > >>>>> to a
> > >>>>> > >>>>> CassandraLimit which passes along the limit and offset
> > >>>>> information to
> > >>>>> > >>>>> CassandraToEnumerableConverter for when the query is
> > executed.
> > >>>>> The
> > >>>>> > >>>>> EnumerableLimit is then replaced with a CassandraLimit
> > >>>>> effectively
> > >>>>> > as a
> > >>>>> > >>>>> placeholder to signal that the limit has been handled.
> > >>>>> > >>>>>
> > >>>>> > >>>>> --
> > >>>>> > >>>>> Michael Mior
> > >>>>> > >>>>> mmior@apache.org
> > >>>>> > >>>>>
> > >>>>> > >>>>>
> > >>>>> > >>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <
> > >>>>> > eric.berryman@gmail.com
> > >>>>> > >>>>> >
> > >>>>> > >>>>> wrote:
> > >>>>> > >>>>>
> > >>>>> > >>>>> > Hello!
> > >>>>> > >>>>> >
> > >>>>> > >>>>> > When I add limit to my relbuilder object, the planner
> gives
> > >>>>> up. But
> > >>>>> > >>>>> works
> > >>>>> > >>>>> > fine without it.
> > >>>>> > >>>>> >
> > >>>>> > >>>>> > I wasn’t able to find any examples of using limit and
> > >>>>> pushing the
> > >>>>> > >>>>> limit
> > >>>>> > >>>>> > values down to a data source.
> > >>>>> > >>>>> >
> > >>>>> > >>>>> > Could someone help with some links on this subject?
> > >>>>> > >>>>> >
> > >>>>> > >>>>> > Thank you!
> > >>>>> > >>>>> > Eric
> > >>>>> > >>>>> >
> > >>>>> > >>>>>
> > >>>>> > >>>>
> > >>>>> >
> > >>>>>
> > >>>>
> >
>

Re: Sortlimit and limit examples

Posted by Ruben Q L <ru...@gmail.com>.

Which Calcite version are you using?


On Mon, Aug 19, 2024 at 4:22 PM Eric Berryman <er...@gmail.com>
wrote:

> I’ve noticed if I remove the limit, the EnumerableUnion works fine. So it
> seems the infinite cost is coming from the EnumerableLimit outside of the
> union. I have an LDAPSortLimit which is pushed past the union, and an
> EnumerableLimit
> is left outside the union, as you can see in the gist.
>
> On Mon, Aug 19, 2024 at 11:10 Eric Berryman <er...@gmail.com>
> wrote:
>
> > Is there anymore information that would be helpful for me to give to aide
> > in troubleshooting this?
> >
> > Thank you!
> > Eric
> >
> > On Thu, Aug 15, 2024 at 16:51 Eric Berryman <er...@gmail.com>
> > wrote:
> >
> >>
> >> I seem to be struggling with unions still. Here is a gist with the
> output
> >> and svg of the plan.
> >>
> >> https://gist.github.com/berryma4/c4870dd4e292e0509d1c85c308b52e67
> >>
> >> I need a little help on direction of what the svg is telling me.  I see
> >> the cost is infinite, but I don’t see where I’m missing a rule.
> >>
> >>
> >> Thank you for your help,
> >> Eric
> >>
> >>
> >>
> >> On Mon, Jul 29, 2024 at 18:28 Eric Berryman <er...@gmail.com>
> >> wrote:
> >>
> >>> That didn't paste well. Here is a gist:
> >>> https://gist.github.com/berryma4/c6c09da050f273295edd23c045c63403
> >>>
> >>>
> >>>
> >>> On Mon, Jul 29, 2024 at 4:58 PM Eric Berryman <eric.berryman@gmail.com
> >
> >>> wrote:
> >>>
> >>>> I’m back from a long holiday, and seem stuck still on this scenario.
> >>>> The plan gets created without any issues, but I get this error while
> >>>> executing queries with unions.
> >>>>
> >>>> Thank you again!
> >>>> Eric
> >>>>
> >>>> Here is the plan dump and error after running
> >>>> relRunner.prepareStatement.
> >>>>
> >>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 30.0,
> >>>> cumulative cost = {146.0 rows, 1393.2 cpu, 0.0 io}, id = 213
> >>>> EnumerableLimit(fetch=[100]): rowcount = 30.0, cumulative cost =
> {116.0
> >>>> rows, 1333.2 cpu, 0.0 io}, id = 212 EnumerableUnion(all=[true]):
> rowcount =
> >>>> 30.0, cumulative cost = {86.0 rows, 1303.2 cpu, 0.0 io}, id = 211
> >>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0,
> cumulative
> >>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 207
> LDAPToEnumerableConverter:
> >>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io}, id
> = 206
> >>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0,
> cumulative
> >>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 205
> >>>> LDAPTableScan(table=[[SCIMUserSchema, cap_internet_ddpmildap]]):
> rowcount =
> >>>> 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0 io},
> id =
> >>>> 0 EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0,
> cumulative
> >>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 210
> LDAPToEnumerableConverter:
> >>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io}, id
> = 209
> >>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0,
> cumulative
> >>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 208
> >>>> LDAPTableScan(table=[[SCIMUserSchema, prov_internet_ddpmildap]]):
> rowcount
> >>>> = 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0
> io}, id
> >>>> = 2
> >>>>
> >>>>  java.lang.AssertionError: null at
> >>>>
> org.apache.calcite.adapter.enumerable.EnumerableLimit.<init>(EnumerableLimit.java:60)
> >>>> at
> >>>>
> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:84)
> >>>> at
> >>>>
> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:43)
> >>>> at
> >>>>
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:378)
> >>>> at
> >>>>
> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
> >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
> >>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
> >>>>
> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
> >>>> at
> >>>>
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:228)
> >>>> at
> >>>>
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:514)
> >>>> at
> >>>>
> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
> >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
> >>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
> >>>>
> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
> >>>> at
> >>>>
> org.apache.calcite.sql2rel.RelFieldTrimmer.trim(RelFieldTrimmer.java:173)
> >>>> at
> >>>>
> org.apache.calcite.sql2rel.SqlToRelConverter.trimUnusedFields(SqlToRelConverter.java:567)
> >>>> at
> org.apache.calcite.prepare.Prepare.trimUnusedFields(Prepare.java:386) at
> >>>>
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1061)
> >>>> at
> >>>>
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1026)
> >>>> at
> >>>>
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:687)
> >>>> at
> >>>>
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> >>>> at
> >>>>
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> >>>> at
> >>>>
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:237)
> >>>> at
> >>>>
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:217)
> >>>> at
> >>>>
> org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187)
> >>>>
> >>>>
> >>>> On Fri, Jul 19, 2024 at 11:07 Ruben Q L <ru...@gmail.com> wrote:
> >>>>
> >>>>> Eric, if you want to use EnumerableUnion instead of implementing your
> >>>>> own
> >>>>> Union, then I think you'll need to implement the appropriate
> Converter
> >>>>> [1]
> >>>>> to transform between conventions LDAP <=> ENUMERABLE
> >>>>>
> >>>>> Best,
> >>>>> Ruben
> >>>>>
> >>>>> [1]
> >>>>>
> >>>>>
> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/convert/Converter.java
> >>>>>
> >>>>>
> >>>>> On Fri, Jul 19, 2024 at 3:56 PM Eric Berryman <
> eric.berryman@gmail.com
> >>>>> >
> >>>>> wrote:
> >>>>>
> >>>>> > I implement an LDAPUnion and rule to convert from logicalunion, and
> >>>>> of
> >>>>> > course, the error goes away. But I didn’t implement the union
> >>>>> correctly, so
> >>>>> > nothing is returned in my query. Is there a way to have my
> LDAPUnion
> >>>>> > use EnumerableUnion,
> >>>>> > or a way to not have LDAPUnion and take care of this with a rule?
> >>>>> >
> >>>>> > Thank you again!
> >>>>> > Eric
> >>>>> >
> >>>>> > On Tue, Jul 16, 2024 at 14:24 Eric Berryman <
> eric.berryman@gmail.com
> >>>>> >
> >>>>> > wrote:
> >>>>> >
> >>>>> > > Well, I was wrong. That didn’t solve my problem with :
> >>>>> > >
> >>>>> > > Missing conversion is LogicalUnion[convention: NONE -> LDAP]
> >>>>> > >
> >>>>> > > Do I have to implement my own union? Is there a way I could just
> >>>>> use the
> >>>>> > > EnumerableUnion?
> >>>>> > > ie. NONE -> Enumerable and skip implementing one for my ldap
> >>>>> datastore?
> >>>>> > >
> >>>>> > > Thank you!
> >>>>> > > Eric
> >>>>> > >
> >>>>> > > On Tue, Jul 16, 2024 at 09:00 Eric Berryman <
> >>>>> eric.berryman@gmail.com>
> >>>>> > > wrote:
> >>>>> > >
> >>>>> > >> When I register my tablescan object, and add my rules, I also
> >>>>> added a
> >>>>> > >> removeRule for EnumerableRules.ENUMERABLE_MERGE_UNION_RULE, and
> >>>>> now
> >>>>> > >> everything works with an offset also.
> >>>>> > >>
> >>>>> > >> Although, I don’t know why this is. I noticed the
> >>>>> EnumerableMergeUnion
> >>>>> > >> object in the plan, and thought I would try to remove it,
> because
> >>>>> it was
> >>>>> > >> different.
> >>>>> > >>
> >>>>> > >> Any explanation is appreciated, thank you!!!
> >>>>> > >> Eric
> >>>>> > >>
> >>>>> > >>
> >>>>> > >> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <
> >>>>> eric.berryman@gmail.com>
> >>>>> > >> wrote:
> >>>>> > >>
> >>>>> > >>> Hello!
> >>>>> > >>>
> >>>>> > >>> I seem to have an issue with my new limit rule which pushes
> down
> >>>>> to the
> >>>>> > >>> datastore. It works fine, unless I add an offset to the fetch.
> >>>>> Where I
> >>>>> > end
> >>>>> > >>> up with the following error:
> >>>>> > >>> There are not enough rules… Missing conversion is
> >>>>> > >>> LogicalUnion[convention: NONE -> LDAP]
> >>>>> > >>>
> >>>>> > >>> Why would this only come up when an offset value is added?
> >>>>> > >>>
> >>>>> > >>> Thank you!
> >>>>> > >>> Eric
> >>>>> > >>>
> >>>>> > >>>
> >>>>> > >>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <
> >>>>> eric.berryman@gmail.com>
> >>>>> > >>> wrote:
> >>>>> > >>>
> >>>>> > >>>> That’s perfect
> >>>>> > >>>>
> >>>>> > >>>>
> >>>>> >
> >>>>>
> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
> >>>>> > >>>>
> >>>>> > >>>> Thank you for such a quick response!
> >>>>> > >>>> Eric
> >>>>> > >>>>
> >>>>> > >>>>
> >>>>> > >>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <mm...@apache.org>
> >>>>> wrote:
> >>>>> > >>>>
> >>>>> > >>>>> Eric,
> >>>>> > >>>>>
> >>>>> > >>>>> Could you give a more specific example of the failure
> scenario
> >>>>> you're
> >>>>> > >>>>> experiencing?
> >>>>> > >>>>>
> >>>>> > >>>>> For a simple example of how limits can be pushed down, this
> is
> >>>>> done
> >>>>> > in
> >>>>> > >>>>> the
> >>>>> > >>>>> Cassandra adapter with CassandraLimitRule. It matches an
> >>>>> > >>>>> EnumerableLimit on
> >>>>> > >>>>> top of a CassandraToEnumerableConverter and then converts
> that
> >>>>> limit
> >>>>> > >>>>> to a
> >>>>> > >>>>> CassandraLimit which passes along the limit and offset
> >>>>> information to
> >>>>> > >>>>> CassandraToEnumerableConverter for when the query is
> executed.
> >>>>> The
> >>>>> > >>>>> EnumerableLimit is then replaced with a CassandraLimit
> >>>>> effectively
> >>>>> > as a
> >>>>> > >>>>> placeholder to signal that the limit has been handled.
> >>>>> > >>>>>
> >>>>> > >>>>> --
> >>>>> > >>>>> Michael Mior
> >>>>> > >>>>> mmior@apache.org
> >>>>> > >>>>>
> >>>>> > >>>>>
> >>>>> > >>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <
> >>>>> > eric.berryman@gmail.com
> >>>>> > >>>>> >
> >>>>> > >>>>> wrote:
> >>>>> > >>>>>
> >>>>> > >>>>> > Hello!
> >>>>> > >>>>> >
> >>>>> > >>>>> > When I add limit to my relbuilder object, the planner gives
> >>>>> up. But
> >>>>> > >>>>> works
> >>>>> > >>>>> > fine without it.
> >>>>> > >>>>> >
> >>>>> > >>>>> > I wasn’t able to find any examples of using limit and
> >>>>> pushing the
> >>>>> > >>>>> limit
> >>>>> > >>>>> > values down to a data source.
> >>>>> > >>>>> >
> >>>>> > >>>>> > Could someone help with some links on this subject?
> >>>>> > >>>>> >
> >>>>> > >>>>> > Thank you!
> >>>>> > >>>>> > Eric
> >>>>> > >>>>> >
> >>>>> > >>>>>
> >>>>> > >>>>
> >>>>> >
> >>>>>
> >>>>
>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

I’ve noticed if I remove the limit, the EnumerableUnion works fine. So it
seems the infinite cost is coming from the EnumerableLimit outside of the
union. I have an LDAPSortLimit which is pushed past the union, and an
EnumerableLimit
is left outside the union, as you can see in the gist.

On Mon, Aug 19, 2024 at 11:10 Eric Berryman <er...@gmail.com> wrote:

> Is there anymore information that would be helpful for me to give to aide
> in troubleshooting this?
>
> Thank you!
> Eric
>
> On Thu, Aug 15, 2024 at 16:51 Eric Berryman <er...@gmail.com>
> wrote:
>
>>
>> I seem to be struggling with unions still. Here is a gist with the output
>> and svg of the plan.
>>
>> https://gist.github.com/berryma4/c4870dd4e292e0509d1c85c308b52e67
>>
>> I need a little help on direction of what the svg is telling me.  I see
>> the cost is infinite, but I don’t see where I’m missing a rule.
>>
>>
>> Thank you for your help,
>> Eric
>>
>>
>>
>> On Mon, Jul 29, 2024 at 18:28 Eric Berryman <er...@gmail.com>
>> wrote:
>>
>>> That didn't paste well. Here is a gist:
>>> https://gist.github.com/berryma4/c6c09da050f273295edd23c045c63403
>>>
>>>
>>>
>>> On Mon, Jul 29, 2024 at 4:58 PM Eric Berryman <er...@gmail.com>
>>> wrote:
>>>
>>>> I’m back from a long holiday, and seem stuck still on this scenario.
>>>> The plan gets created without any issues, but I get this error while
>>>> executing queries with unions.
>>>>
>>>> Thank you again!
>>>> Eric
>>>>
>>>> Here is the plan dump and error after running
>>>> relRunner.prepareStatement.
>>>>
>>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 30.0,
>>>> cumulative cost = {146.0 rows, 1393.2 cpu, 0.0 io}, id = 213
>>>> EnumerableLimit(fetch=[100]): rowcount = 30.0, cumulative cost = {116.0
>>>> rows, 1333.2 cpu, 0.0 io}, id = 212 EnumerableUnion(all=[true]): rowcount =
>>>> 30.0, cumulative cost = {86.0 rows, 1303.2 cpu, 0.0 io}, id = 211
>>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0, cumulative
>>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 207 LDAPToEnumerableConverter:
>>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io}, id = 206
>>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0, cumulative
>>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 205
>>>> LDAPTableScan(table=[[SCIMUserSchema, cap_internet_ddpmildap]]): rowcount =
>>>> 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0 io}, id =
>>>> 0 EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0, cumulative
>>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 210 LDAPToEnumerableConverter:
>>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io}, id = 209
>>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0, cumulative
>>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 208
>>>> LDAPTableScan(table=[[SCIMUserSchema, prov_internet_ddpmildap]]): rowcount
>>>> = 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0 io}, id
>>>> = 2
>>>>
>>>>  java.lang.AssertionError: null at
>>>> org.apache.calcite.adapter.enumerable.EnumerableLimit.<init>(EnumerableLimit.java:60)
>>>> at
>>>> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:84)
>>>> at
>>>> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:43)
>>>> at
>>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:378)
>>>> at
>>>> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
>>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
>>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
>>>> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
>>>> at
>>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:228)
>>>> at
>>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:514)
>>>> at
>>>> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
>>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
>>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
>>>> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
>>>> at
>>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trim(RelFieldTrimmer.java:173)
>>>> at
>>>> org.apache.calcite.sql2rel.SqlToRelConverter.trimUnusedFields(SqlToRelConverter.java:567)
>>>> at org.apache.calcite.prepare.Prepare.trimUnusedFields(Prepare.java:386) at
>>>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1061)
>>>> at
>>>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1026)
>>>> at
>>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:687)
>>>> at
>>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
>>>> at
>>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
>>>> at
>>>> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:237)
>>>> at
>>>> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:217)
>>>> at
>>>> org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187)
>>>>
>>>>
>>>> On Fri, Jul 19, 2024 at 11:07 Ruben Q L <ru...@gmail.com> wrote:
>>>>
>>>>> Eric, if you want to use EnumerableUnion instead of implementing your
>>>>> own
>>>>> Union, then I think you'll need to implement the appropriate Converter
>>>>> [1]
>>>>> to transform between conventions LDAP <=> ENUMERABLE
>>>>>
>>>>> Best,
>>>>> Ruben
>>>>>
>>>>> [1]
>>>>>
>>>>> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/convert/Converter.java
>>>>>
>>>>>
>>>>> On Fri, Jul 19, 2024 at 3:56 PM Eric Berryman <eric.berryman@gmail.com
>>>>> >
>>>>> wrote:
>>>>>
>>>>> > I implement an LDAPUnion and rule to convert from logicalunion, and
>>>>> of
>>>>> > course, the error goes away. But I didn’t implement the union
>>>>> correctly, so
>>>>> > nothing is returned in my query. Is there a way to have my LDAPUnion
>>>>> > use EnumerableUnion,
>>>>> > or a way to not have LDAPUnion and take care of this with a rule?
>>>>> >
>>>>> > Thank you again!
>>>>> > Eric
>>>>> >
>>>>> > On Tue, Jul 16, 2024 at 14:24 Eric Berryman <eric.berryman@gmail.com
>>>>> >
>>>>> > wrote:
>>>>> >
>>>>> > > Well, I was wrong. That didn’t solve my problem with :
>>>>> > >
>>>>> > > Missing conversion is LogicalUnion[convention: NONE -> LDAP]
>>>>> > >
>>>>> > > Do I have to implement my own union? Is there a way I could just
>>>>> use the
>>>>> > > EnumerableUnion?
>>>>> > > ie. NONE -> Enumerable and skip implementing one for my ldap
>>>>> datastore?
>>>>> > >
>>>>> > > Thank you!
>>>>> > > Eric
>>>>> > >
>>>>> > > On Tue, Jul 16, 2024 at 09:00 Eric Berryman <
>>>>> eric.berryman@gmail.com>
>>>>> > > wrote:
>>>>> > >
>>>>> > >> When I register my tablescan object, and add my rules, I also
>>>>> added a
>>>>> > >> removeRule for EnumerableRules.ENUMERABLE_MERGE_UNION_RULE, and
>>>>> now
>>>>> > >> everything works with an offset also.
>>>>> > >>
>>>>> > >> Although, I don’t know why this is. I noticed the
>>>>> EnumerableMergeUnion
>>>>> > >> object in the plan, and thought I would try to remove it, because
>>>>> it was
>>>>> > >> different.
>>>>> > >>
>>>>> > >> Any explanation is appreciated, thank you!!!
>>>>> > >> Eric
>>>>> > >>
>>>>> > >>
>>>>> > >> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <
>>>>> eric.berryman@gmail.com>
>>>>> > >> wrote:
>>>>> > >>
>>>>> > >>> Hello!
>>>>> > >>>
>>>>> > >>> I seem to have an issue with my new limit rule which pushes down
>>>>> to the
>>>>> > >>> datastore. It works fine, unless I add an offset to the fetch.
>>>>> Where I
>>>>> > end
>>>>> > >>> up with the following error:
>>>>> > >>> There are not enough rules… Missing conversion is
>>>>> > >>> LogicalUnion[convention: NONE -> LDAP]
>>>>> > >>>
>>>>> > >>> Why would this only come up when an offset value is added?
>>>>> > >>>
>>>>> > >>> Thank you!
>>>>> > >>> Eric
>>>>> > >>>
>>>>> > >>>
>>>>> > >>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <
>>>>> eric.berryman@gmail.com>
>>>>> > >>> wrote:
>>>>> > >>>
>>>>> > >>>> That’s perfect
>>>>> > >>>>
>>>>> > >>>>
>>>>> >
>>>>> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
>>>>> > >>>>
>>>>> > >>>> Thank you for such a quick response!
>>>>> > >>>> Eric
>>>>> > >>>>
>>>>> > >>>>
>>>>> > >>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <mm...@apache.org>
>>>>> wrote:
>>>>> > >>>>
>>>>> > >>>>> Eric,
>>>>> > >>>>>
>>>>> > >>>>> Could you give a more specific example of the failure scenario
>>>>> you're
>>>>> > >>>>> experiencing?
>>>>> > >>>>>
>>>>> > >>>>> For a simple example of how limits can be pushed down, this is
>>>>> done
>>>>> > in
>>>>> > >>>>> the
>>>>> > >>>>> Cassandra adapter with CassandraLimitRule. It matches an
>>>>> > >>>>> EnumerableLimit on
>>>>> > >>>>> top of a CassandraToEnumerableConverter and then converts that
>>>>> limit
>>>>> > >>>>> to a
>>>>> > >>>>> CassandraLimit which passes along the limit and offset
>>>>> information to
>>>>> > >>>>> CassandraToEnumerableConverter for when the query is executed.
>>>>> The
>>>>> > >>>>> EnumerableLimit is then replaced with a CassandraLimit
>>>>> effectively
>>>>> > as a
>>>>> > >>>>> placeholder to signal that the limit has been handled.
>>>>> > >>>>>
>>>>> > >>>>> --
>>>>> > >>>>> Michael Mior
>>>>> > >>>>> mmior@apache.org
>>>>> > >>>>>
>>>>> > >>>>>
>>>>> > >>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <
>>>>> > eric.berryman@gmail.com
>>>>> > >>>>> >
>>>>> > >>>>> wrote:
>>>>> > >>>>>
>>>>> > >>>>> > Hello!
>>>>> > >>>>> >
>>>>> > >>>>> > When I add limit to my relbuilder object, the planner gives
>>>>> up. But
>>>>> > >>>>> works
>>>>> > >>>>> > fine without it.
>>>>> > >>>>> >
>>>>> > >>>>> > I wasn’t able to find any examples of using limit and
>>>>> pushing the
>>>>> > >>>>> limit
>>>>> > >>>>> > values down to a data source.
>>>>> > >>>>> >
>>>>> > >>>>> > Could someone help with some links on this subject?
>>>>> > >>>>> >
>>>>> > >>>>> > Thank you!
>>>>> > >>>>> > Eric
>>>>> > >>>>> >
>>>>> > >>>>>
>>>>> > >>>>
>>>>> >
>>>>>
>>>>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

Is there anymore information that would be helpful for me to give to aide
in troubleshooting this?

Thank you!
Eric

On Thu, Aug 15, 2024 at 16:51 Eric Berryman <er...@gmail.com> wrote:

>
> I seem to be struggling with unions still. Here is a gist with the output
> and svg of the plan.
>
> https://gist.github.com/berryma4/c4870dd4e292e0509d1c85c308b52e67
>
> I need a little help on direction of what the svg is telling me.  I see
> the cost is infinite, but I don’t see where I’m missing a rule.
>
>
> Thank you for your help,
> Eric
>
>
>
> On Mon, Jul 29, 2024 at 18:28 Eric Berryman <er...@gmail.com>
> wrote:
>
>> That didn't paste well. Here is a gist:
>> https://gist.github.com/berryma4/c6c09da050f273295edd23c045c63403
>>
>>
>>
>> On Mon, Jul 29, 2024 at 4:58 PM Eric Berryman <er...@gmail.com>
>> wrote:
>>
>>> I’m back from a long holiday, and seem stuck still on this scenario. The
>>> plan gets created without any issues, but I get this error while executing
>>> queries with unions.
>>>
>>> Thank you again!
>>> Eric
>>>
>>> Here is the plan dump and error after running relRunner.prepareStatement.
>>>
>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 30.0, cumulative
>>> cost = {146.0 rows, 1393.2 cpu, 0.0 io}, id = 213
>>> EnumerableLimit(fetch=[100]): rowcount = 30.0, cumulative cost = {116.0
>>> rows, 1333.2 cpu, 0.0 io}, id = 212 EnumerableUnion(all=[true]): rowcount =
>>> 30.0, cumulative cost = {86.0 rows, 1303.2 cpu, 0.0 io}, id = 211
>>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0, cumulative
>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 207 LDAPToEnumerableConverter:
>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io}, id = 206
>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0, cumulative
>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 205
>>> LDAPTableScan(table=[[SCIMUserSchema, cap_internet_ddpmildap]]): rowcount =
>>> 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0 io}, id =
>>> 0 EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0, cumulative
>>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 210 LDAPToEnumerableConverter:
>>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io}, id = 209
>>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0, cumulative
>>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 208
>>> LDAPTableScan(table=[[SCIMUserSchema, prov_internet_ddpmildap]]): rowcount
>>> = 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0 io}, id
>>> = 2
>>>
>>>  java.lang.AssertionError: null at
>>> org.apache.calcite.adapter.enumerable.EnumerableLimit.<init>(EnumerableLimit.java:60)
>>> at
>>> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:84)
>>> at
>>> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:43)
>>> at
>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:378)
>>> at
>>> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
>>> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
>>> at
>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:228)
>>> at
>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:514)
>>> at
>>> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
>>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
>>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
>>> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
>>> at
>>> org.apache.calcite.sql2rel.RelFieldTrimmer.trim(RelFieldTrimmer.java:173)
>>> at
>>> org.apache.calcite.sql2rel.SqlToRelConverter.trimUnusedFields(SqlToRelConverter.java:567)
>>> at org.apache.calcite.prepare.Prepare.trimUnusedFields(Prepare.java:386) at
>>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1061)
>>> at
>>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1026)
>>> at
>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:687)
>>> at
>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
>>> at
>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
>>> at
>>> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:237)
>>> at
>>> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:217)
>>> at
>>> org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187)
>>>
>>>
>>> On Fri, Jul 19, 2024 at 11:07 Ruben Q L <ru...@gmail.com> wrote:
>>>
>>>> Eric, if you want to use EnumerableUnion instead of implementing your
>>>> own
>>>> Union, then I think you'll need to implement the appropriate Converter
>>>> [1]
>>>> to transform between conventions LDAP <=> ENUMERABLE
>>>>
>>>> Best,
>>>> Ruben
>>>>
>>>> [1]
>>>>
>>>> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/convert/Converter.java
>>>>
>>>>
>>>> On Fri, Jul 19, 2024 at 3:56 PM Eric Berryman <er...@gmail.com>
>>>> wrote:
>>>>
>>>> > I implement an LDAPUnion and rule to convert from logicalunion, and of
>>>> > course, the error goes away. But I didn’t implement the union
>>>> correctly, so
>>>> > nothing is returned in my query. Is there a way to have my LDAPUnion
>>>> > use EnumerableUnion,
>>>> > or a way to not have LDAPUnion and take care of this with a rule?
>>>> >
>>>> > Thank you again!
>>>> > Eric
>>>> >
>>>> > On Tue, Jul 16, 2024 at 14:24 Eric Berryman <er...@gmail.com>
>>>> > wrote:
>>>> >
>>>> > > Well, I was wrong. That didn’t solve my problem with :
>>>> > >
>>>> > > Missing conversion is LogicalUnion[convention: NONE -> LDAP]
>>>> > >
>>>> > > Do I have to implement my own union? Is there a way I could just
>>>> use the
>>>> > > EnumerableUnion?
>>>> > > ie. NONE -> Enumerable and skip implementing one for my ldap
>>>> datastore?
>>>> > >
>>>> > > Thank you!
>>>> > > Eric
>>>> > >
>>>> > > On Tue, Jul 16, 2024 at 09:00 Eric Berryman <
>>>> eric.berryman@gmail.com>
>>>> > > wrote:
>>>> > >
>>>> > >> When I register my tablescan object, and add my rules, I also
>>>> added a
>>>> > >> removeRule for EnumerableRules.ENUMERABLE_MERGE_UNION_RULE, and now
>>>> > >> everything works with an offset also.
>>>> > >>
>>>> > >> Although, I don’t know why this is. I noticed the
>>>> EnumerableMergeUnion
>>>> > >> object in the plan, and thought I would try to remove it, because
>>>> it was
>>>> > >> different.
>>>> > >>
>>>> > >> Any explanation is appreciated, thank you!!!
>>>> > >> Eric
>>>> > >>
>>>> > >>
>>>> > >> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <
>>>> eric.berryman@gmail.com>
>>>> > >> wrote:
>>>> > >>
>>>> > >>> Hello!
>>>> > >>>
>>>> > >>> I seem to have an issue with my new limit rule which pushes down
>>>> to the
>>>> > >>> datastore. It works fine, unless I add an offset to the fetch.
>>>> Where I
>>>> > end
>>>> > >>> up with the following error:
>>>> > >>> There are not enough rules… Missing conversion is
>>>> > >>> LogicalUnion[convention: NONE -> LDAP]
>>>> > >>>
>>>> > >>> Why would this only come up when an offset value is added?
>>>> > >>>
>>>> > >>> Thank you!
>>>> > >>> Eric
>>>> > >>>
>>>> > >>>
>>>> > >>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <
>>>> eric.berryman@gmail.com>
>>>> > >>> wrote:
>>>> > >>>
>>>> > >>>> That’s perfect
>>>> > >>>>
>>>> > >>>>
>>>> >
>>>> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
>>>> > >>>>
>>>> > >>>> Thank you for such a quick response!
>>>> > >>>> Eric
>>>> > >>>>
>>>> > >>>>
>>>> > >>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <mm...@apache.org>
>>>> wrote:
>>>> > >>>>
>>>> > >>>>> Eric,
>>>> > >>>>>
>>>> > >>>>> Could you give a more specific example of the failure scenario
>>>> you're
>>>> > >>>>> experiencing?
>>>> > >>>>>
>>>> > >>>>> For a simple example of how limits can be pushed down, this is
>>>> done
>>>> > in
>>>> > >>>>> the
>>>> > >>>>> Cassandra adapter with CassandraLimitRule. It matches an
>>>> > >>>>> EnumerableLimit on
>>>> > >>>>> top of a CassandraToEnumerableConverter and then converts that
>>>> limit
>>>> > >>>>> to a
>>>> > >>>>> CassandraLimit which passes along the limit and offset
>>>> information to
>>>> > >>>>> CassandraToEnumerableConverter for when the query is executed.
>>>> The
>>>> > >>>>> EnumerableLimit is then replaced with a CassandraLimit
>>>> effectively
>>>> > as a
>>>> > >>>>> placeholder to signal that the limit has been handled.
>>>> > >>>>>
>>>> > >>>>> --
>>>> > >>>>> Michael Mior
>>>> > >>>>> mmior@apache.org
>>>> > >>>>>
>>>> > >>>>>
>>>> > >>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <
>>>> > eric.berryman@gmail.com
>>>> > >>>>> >
>>>> > >>>>> wrote:
>>>> > >>>>>
>>>> > >>>>> > Hello!
>>>> > >>>>> >
>>>> > >>>>> > When I add limit to my relbuilder object, the planner gives
>>>> up. But
>>>> > >>>>> works
>>>> > >>>>> > fine without it.
>>>> > >>>>> >
>>>> > >>>>> > I wasn’t able to find any examples of using limit and pushing
>>>> the
>>>> > >>>>> limit
>>>> > >>>>> > values down to a data source.
>>>> > >>>>> >
>>>> > >>>>> > Could someone help with some links on this subject?
>>>> > >>>>> >
>>>> > >>>>> > Thank you!
>>>> > >>>>> > Eric
>>>> > >>>>> >
>>>> > >>>>>
>>>> > >>>>
>>>> >
>>>>
>>>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

I seem to be struggling with unions still. Here is a gist with the output
and svg of the plan.

https://gist.github.com/berryma4/c4870dd4e292e0509d1c85c308b52e67

I need a little help on direction of what the svg is telling me.  I see the
cost is infinite, but I don’t see where I’m missing a rule.


Thank you for your help,
Eric



On Mon, Jul 29, 2024 at 18:28 Eric Berryman <er...@gmail.com> wrote:

> That didn't paste well. Here is a gist:
> https://gist.github.com/berryma4/c6c09da050f273295edd23c045c63403
>
>
>
> On Mon, Jul 29, 2024 at 4:58 PM Eric Berryman <er...@gmail.com>
> wrote:
>
>> I’m back from a long holiday, and seem stuck still on this scenario. The
>> plan gets created without any issues, but I get this error while executing
>> queries with unions.
>>
>> Thank you again!
>> Eric
>>
>> Here is the plan dump and error after running relRunner.prepareStatement.
>>
>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 30.0, cumulative
>> cost = {146.0 rows, 1393.2 cpu, 0.0 io}, id = 213
>> EnumerableLimit(fetch=[100]): rowcount = 30.0, cumulative cost = {116.0
>> rows, 1333.2 cpu, 0.0 io}, id = 212 EnumerableUnion(all=[true]): rowcount =
>> 30.0, cumulative cost = {86.0 rows, 1303.2 cpu, 0.0 io}, id = 211
>> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0, cumulative
>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 207 LDAPToEnumerableConverter:
>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io}, id = 206
>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0, cumulative
>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 205
>> LDAPTableScan(table=[[SCIMUserSchema, cap_internet_ddpmildap]]): rowcount =
>> 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0 io}, id =
>> 0 EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0, cumulative
>> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 210 LDAPToEnumerableConverter:
>> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io}, id = 209
>> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0, cumulative
>> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 208
>> LDAPTableScan(table=[[SCIMUserSchema, prov_internet_ddpmildap]]): rowcount
>> = 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0 io}, id
>> = 2
>>
>>  java.lang.AssertionError: null at
>> org.apache.calcite.adapter.enumerable.EnumerableLimit.<init>(EnumerableLimit.java:60)
>> at
>> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:84)
>> at
>> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:43)
>> at
>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:378)
>> at
>> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
>> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
>> at
>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:228)
>> at
>> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:514)
>> at
>> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
>> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
>> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
>> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
>> at
>> org.apache.calcite.sql2rel.RelFieldTrimmer.trim(RelFieldTrimmer.java:173)
>> at
>> org.apache.calcite.sql2rel.SqlToRelConverter.trimUnusedFields(SqlToRelConverter.java:567)
>> at org.apache.calcite.prepare.Prepare.trimUnusedFields(Prepare.java:386) at
>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1061)
>> at
>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1026)
>> at
>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:687)
>> at
>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
>> at
>> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
>> at
>> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:237)
>> at
>> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:217)
>> at
>> org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187)
>>
>>
>> On Fri, Jul 19, 2024 at 11:07 Ruben Q L <ru...@gmail.com> wrote:
>>
>>> Eric, if you want to use EnumerableUnion instead of implementing your own
>>> Union, then I think you'll need to implement the appropriate Converter
>>> [1]
>>> to transform between conventions LDAP <=> ENUMERABLE
>>>
>>> Best,
>>> Ruben
>>>
>>> [1]
>>>
>>> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/convert/Converter.java
>>>
>>>
>>> On Fri, Jul 19, 2024 at 3:56 PM Eric Berryman <er...@gmail.com>
>>> wrote:
>>>
>>> > I implement an LDAPUnion and rule to convert from logicalunion, and of
>>> > course, the error goes away. But I didn’t implement the union
>>> correctly, so
>>> > nothing is returned in my query. Is there a way to have my LDAPUnion
>>> > use EnumerableUnion,
>>> > or a way to not have LDAPUnion and take care of this with a rule?
>>> >
>>> > Thank you again!
>>> > Eric
>>> >
>>> > On Tue, Jul 16, 2024 at 14:24 Eric Berryman <er...@gmail.com>
>>> > wrote:
>>> >
>>> > > Well, I was wrong. That didn’t solve my problem with :
>>> > >
>>> > > Missing conversion is LogicalUnion[convention: NONE -> LDAP]
>>> > >
>>> > > Do I have to implement my own union? Is there a way I could just use
>>> the
>>> > > EnumerableUnion?
>>> > > ie. NONE -> Enumerable and skip implementing one for my ldap
>>> datastore?
>>> > >
>>> > > Thank you!
>>> > > Eric
>>> > >
>>> > > On Tue, Jul 16, 2024 at 09:00 Eric Berryman <eric.berryman@gmail.com
>>> >
>>> > > wrote:
>>> > >
>>> > >> When I register my tablescan object, and add my rules, I also added
>>> a
>>> > >> removeRule for EnumerableRules.ENUMERABLE_MERGE_UNION_RULE, and now
>>> > >> everything works with an offset also.
>>> > >>
>>> > >> Although, I don’t know why this is. I noticed the
>>> EnumerableMergeUnion
>>> > >> object in the plan, and thought I would try to remove it, because
>>> it was
>>> > >> different.
>>> > >>
>>> > >> Any explanation is appreciated, thank you!!!
>>> > >> Eric
>>> > >>
>>> > >>
>>> > >> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <
>>> eric.berryman@gmail.com>
>>> > >> wrote:
>>> > >>
>>> > >>> Hello!
>>> > >>>
>>> > >>> I seem to have an issue with my new limit rule which pushes down
>>> to the
>>> > >>> datastore. It works fine, unless I add an offset to the fetch.
>>> Where I
>>> > end
>>> > >>> up with the following error:
>>> > >>> There are not enough rules… Missing conversion is
>>> > >>> LogicalUnion[convention: NONE -> LDAP]
>>> > >>>
>>> > >>> Why would this only come up when an offset value is added?
>>> > >>>
>>> > >>> Thank you!
>>> > >>> Eric
>>> > >>>
>>> > >>>
>>> > >>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <
>>> eric.berryman@gmail.com>
>>> > >>> wrote:
>>> > >>>
>>> > >>>> That’s perfect
>>> > >>>>
>>> > >>>>
>>> >
>>> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
>>> > >>>>
>>> > >>>> Thank you for such a quick response!
>>> > >>>> Eric
>>> > >>>>
>>> > >>>>
>>> > >>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <mm...@apache.org>
>>> wrote:
>>> > >>>>
>>> > >>>>> Eric,
>>> > >>>>>
>>> > >>>>> Could you give a more specific example of the failure scenario
>>> you're
>>> > >>>>> experiencing?
>>> > >>>>>
>>> > >>>>> For a simple example of how limits can be pushed down, this is
>>> done
>>> > in
>>> > >>>>> the
>>> > >>>>> Cassandra adapter with CassandraLimitRule. It matches an
>>> > >>>>> EnumerableLimit on
>>> > >>>>> top of a CassandraToEnumerableConverter and then converts that
>>> limit
>>> > >>>>> to a
>>> > >>>>> CassandraLimit which passes along the limit and offset
>>> information to
>>> > >>>>> CassandraToEnumerableConverter for when the query is executed.
>>> The
>>> > >>>>> EnumerableLimit is then replaced with a CassandraLimit
>>> effectively
>>> > as a
>>> > >>>>> placeholder to signal that the limit has been handled.
>>> > >>>>>
>>> > >>>>> --
>>> > >>>>> Michael Mior
>>> > >>>>> mmior@apache.org
>>> > >>>>>
>>> > >>>>>
>>> > >>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <
>>> > eric.berryman@gmail.com
>>> > >>>>> >
>>> > >>>>> wrote:
>>> > >>>>>
>>> > >>>>> > Hello!
>>> > >>>>> >
>>> > >>>>> > When I add limit to my relbuilder object, the planner gives
>>> up. But
>>> > >>>>> works
>>> > >>>>> > fine without it.
>>> > >>>>> >
>>> > >>>>> > I wasn’t able to find any examples of using limit and pushing
>>> the
>>> > >>>>> limit
>>> > >>>>> > values down to a data source.
>>> > >>>>> >
>>> > >>>>> > Could someone help with some links on this subject?
>>> > >>>>> >
>>> > >>>>> > Thank you!
>>> > >>>>> > Eric
>>> > >>>>> >
>>> > >>>>>
>>> > >>>>
>>> >
>>>
>>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

That didn't paste well. Here is a gist:
https://gist.github.com/berryma4/c6c09da050f273295edd23c045c63403



On Mon, Jul 29, 2024 at 4:58 PM Eric Berryman <er...@gmail.com>
wrote:

> I’m back from a long holiday, and seem stuck still on this scenario. The
> plan gets created without any issues, but I get this error while executing
> queries with unions.
>
> Thank you again!
> Eric
>
> Here is the plan dump and error after running relRunner.prepareStatement.
>
> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 30.0, cumulative
> cost = {146.0 rows, 1393.2 cpu, 0.0 io}, id = 213
> EnumerableLimit(fetch=[100]): rowcount = 30.0, cumulative cost = {116.0
> rows, 1333.2 cpu, 0.0 io}, id = 212 EnumerableUnion(all=[true]): rowcount =
> 30.0, cumulative cost = {86.0 rows, 1303.2 cpu, 0.0 io}, id = 211
> EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0, cumulative
> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 207 LDAPToEnumerableConverter:
> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io}, id = 206
> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0, cumulative
> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 205
> LDAPTableScan(table=[[SCIMUserSchema, cap_internet_ddpmildap]]): rowcount =
> 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0 io}, id =
> 0 EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0, cumulative
> cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 210 LDAPToEnumerableConverter:
> rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io}, id = 209
> LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0, cumulative
> cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 208
> LDAPTableScan(table=[[SCIMUserSchema, prov_internet_ddpmildap]]): rowcount
> = 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0 io}, id
> = 2
>
>  java.lang.AssertionError: null at
> org.apache.calcite.adapter.enumerable.EnumerableLimit.<init>(EnumerableLimit.java:60)
> at
> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:84)
> at
> org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:43)
> at
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:378)
> at
> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
> at
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:228)
> at
> org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:514)
> at
> java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
> at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
> org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
> org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
> at
> org.apache.calcite.sql2rel.RelFieldTrimmer.trim(RelFieldTrimmer.java:173)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.trimUnusedFields(SqlToRelConverter.java:567)
> at org.apache.calcite.prepare.Prepare.trimUnusedFields(Prepare.java:386) at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1061)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1026)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:687)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:237)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:217)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187)
>
>
> On Fri, Jul 19, 2024 at 11:07 Ruben Q L <ru...@gmail.com> wrote:
>
>> Eric, if you want to use EnumerableUnion instead of implementing your own
>> Union, then I think you'll need to implement the appropriate Converter [1]
>> to transform between conventions LDAP <=> ENUMERABLE
>>
>> Best,
>> Ruben
>>
>> [1]
>>
>> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/convert/Converter.java
>>
>>
>> On Fri, Jul 19, 2024 at 3:56 PM Eric Berryman <er...@gmail.com>
>> wrote:
>>
>> > I implement an LDAPUnion and rule to convert from logicalunion, and of
>> > course, the error goes away. But I didn’t implement the union
>> correctly, so
>> > nothing is returned in my query. Is there a way to have my LDAPUnion
>> > use EnumerableUnion,
>> > or a way to not have LDAPUnion and take care of this with a rule?
>> >
>> > Thank you again!
>> > Eric
>> >
>> > On Tue, Jul 16, 2024 at 14:24 Eric Berryman <er...@gmail.com>
>> > wrote:
>> >
>> > > Well, I was wrong. That didn’t solve my problem with :
>> > >
>> > > Missing conversion is LogicalUnion[convention: NONE -> LDAP]
>> > >
>> > > Do I have to implement my own union? Is there a way I could just use
>> the
>> > > EnumerableUnion?
>> > > ie. NONE -> Enumerable and skip implementing one for my ldap
>> datastore?
>> > >
>> > > Thank you!
>> > > Eric
>> > >
>> > > On Tue, Jul 16, 2024 at 09:00 Eric Berryman <er...@gmail.com>
>> > > wrote:
>> > >
>> > >> When I register my tablescan object, and add my rules, I also added a
>> > >> removeRule for EnumerableRules.ENUMERABLE_MERGE_UNION_RULE, and now
>> > >> everything works with an offset also.
>> > >>
>> > >> Although, I don’t know why this is. I noticed the
>> EnumerableMergeUnion
>> > >> object in the plan, and thought I would try to remove it, because it
>> was
>> > >> different.
>> > >>
>> > >> Any explanation is appreciated, thank you!!!
>> > >> Eric
>> > >>
>> > >>
>> > >> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <eric.berryman@gmail.com
>> >
>> > >> wrote:
>> > >>
>> > >>> Hello!
>> > >>>
>> > >>> I seem to have an issue with my new limit rule which pushes down to
>> the
>> > >>> datastore. It works fine, unless I add an offset to the fetch.
>> Where I
>> > end
>> > >>> up with the following error:
>> > >>> There are not enough rules… Missing conversion is
>> > >>> LogicalUnion[convention: NONE -> LDAP]
>> > >>>
>> > >>> Why would this only come up when an offset value is added?
>> > >>>
>> > >>> Thank you!
>> > >>> Eric
>> > >>>
>> > >>>
>> > >>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <
>> eric.berryman@gmail.com>
>> > >>> wrote:
>> > >>>
>> > >>>> That’s perfect
>> > >>>>
>> > >>>>
>> >
>> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
>> > >>>>
>> > >>>> Thank you for such a quick response!
>> > >>>> Eric
>> > >>>>
>> > >>>>
>> > >>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <mm...@apache.org>
>> wrote:
>> > >>>>
>> > >>>>> Eric,
>> > >>>>>
>> > >>>>> Could you give a more specific example of the failure scenario
>> you're
>> > >>>>> experiencing?
>> > >>>>>
>> > >>>>> For a simple example of how limits can be pushed down, this is
>> done
>> > in
>> > >>>>> the
>> > >>>>> Cassandra adapter with CassandraLimitRule. It matches an
>> > >>>>> EnumerableLimit on
>> > >>>>> top of a CassandraToEnumerableConverter and then converts that
>> limit
>> > >>>>> to a
>> > >>>>> CassandraLimit which passes along the limit and offset
>> information to
>> > >>>>> CassandraToEnumerableConverter for when the query is executed. The
>> > >>>>> EnumerableLimit is then replaced with a CassandraLimit effectively
>> > as a
>> > >>>>> placeholder to signal that the limit has been handled.
>> > >>>>>
>> > >>>>> --
>> > >>>>> Michael Mior
>> > >>>>> mmior@apache.org
>> > >>>>>
>> > >>>>>
>> > >>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <
>> > eric.berryman@gmail.com
>> > >>>>> >
>> > >>>>> wrote:
>> > >>>>>
>> > >>>>> > Hello!
>> > >>>>> >
>> > >>>>> > When I add limit to my relbuilder object, the planner gives up.
>> But
>> > >>>>> works
>> > >>>>> > fine without it.
>> > >>>>> >
>> > >>>>> > I wasn’t able to find any examples of using limit and pushing
>> the
>> > >>>>> limit
>> > >>>>> > values down to a data source.
>> > >>>>> >
>> > >>>>> > Could someone help with some links on this subject?
>> > >>>>> >
>> > >>>>> > Thank you!
>> > >>>>> > Eric
>> > >>>>> >
>> > >>>>>
>> > >>>>
>> >
>>
>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

I’m back from a long holiday, and seem stuck still on this scenario. The
plan gets created without any issues, but I get this error while executing
queries with unions.

Thank you again!
Eric

Here is the plan dump and error after running relRunner.prepareStatement.

EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 30.0, cumulative
cost = {146.0 rows, 1393.2 cpu, 0.0 io}, id = 213
EnumerableLimit(fetch=[100]): rowcount = 30.0, cumulative cost = {116.0
rows, 1333.2 cpu, 0.0 io}, id = 212 EnumerableUnion(all=[true]): rowcount =
30.0, cumulative cost = {86.0 rows, 1303.2 cpu, 0.0 io}, id = 211
EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0, cumulative
cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 207 LDAPToEnumerableConverter:
rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io}, id = 206
LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0, cumulative
cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 205
LDAPTableScan(table=[[SCIMUserSchema, cap_internet_ddpmildap]]): rowcount =
100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0 io}, id =
0 EnumerableProject(UserObject=[$0], id=[$3]): rowcount = 15.0, cumulative
cost = {28.0 rows, 636.6 cpu, 0.0 io}, id = 210 LDAPToEnumerableConverter:
rowcount = 15.0, cumulative cost = {13.0 rows, 21.6 cpu, 0.0 io}, id = 209
LDAPFilter(condition=[=(UPPER($5), 'RUDD')]): rowcount = 15.0, cumulative
cost = {11.5 rows, 20.1 cpu, 0.0 io}, id = 208
LDAPTableScan(table=[[SCIMUserSchema, prov_internet_ddpmildap]]): rowcount
= 100.0, cumulative cost = {10.0 rows, 10.100000000000001 cpu, 0.0 io}, id
= 2

 java.lang.AssertionError: null at
org.apache.calcite.adapter.enumerable.EnumerableLimit.<init>(EnumerableLimit.java:60)
at
org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:84)
at
org.apache.calcite.adapter.enumerable.EnumerableLimit.copy(EnumerableLimit.java:43)
at
org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:378)
at
java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
at
org.apache.calcite.sql2rel.RelFieldTrimmer.trimChild(RelFieldTrimmer.java:228)
at
org.apache.calcite.sql2rel.RelFieldTrimmer.trimFields(RelFieldTrimmer.java:514)
at
java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580) at
org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:533) at
org.apache.calcite.sql2rel.RelFieldTrimmer.dispatchTrimFields(RelFieldTrimmer.java:286)
at
org.apache.calcite.sql2rel.RelFieldTrimmer.trim(RelFieldTrimmer.java:173)
at
org.apache.calcite.sql2rel.SqlToRelConverter.trimUnusedFields(SqlToRelConverter.java:567)
at org.apache.calcite.prepare.Prepare.trimUnusedFields(Prepare.java:386) at
org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepare_(CalcitePrepareImpl.java:1061)
at
org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.prepareRel(CalcitePrepareImpl.java:1026)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:687)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:519)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:487)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:237)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:217)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.lambda$unwrap$0(CalciteConnectionImpl.java:187)


On Fri, Jul 19, 2024 at 11:07 Ruben Q L <ru...@gmail.com> wrote:

> Eric, if you want to use EnumerableUnion instead of implementing your own
> Union, then I think you'll need to implement the appropriate Converter [1]
> to transform between conventions LDAP <=> ENUMERABLE
>
> Best,
> Ruben
>
> [1]
>
> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/convert/Converter.java
>
>
> On Fri, Jul 19, 2024 at 3:56 PM Eric Berryman <er...@gmail.com>
> wrote:
>
> > I implement an LDAPUnion and rule to convert from logicalunion, and of
> > course, the error goes away. But I didn’t implement the union correctly,
> so
> > nothing is returned in my query. Is there a way to have my LDAPUnion
> > use EnumerableUnion,
> > or a way to not have LDAPUnion and take care of this with a rule?
> >
> > Thank you again!
> > Eric
> >
> > On Tue, Jul 16, 2024 at 14:24 Eric Berryman <er...@gmail.com>
> > wrote:
> >
> > > Well, I was wrong. That didn’t solve my problem with :
> > >
> > > Missing conversion is LogicalUnion[convention: NONE -> LDAP]
> > >
> > > Do I have to implement my own union? Is there a way I could just use
> the
> > > EnumerableUnion?
> > > ie. NONE -> Enumerable and skip implementing one for my ldap datastore?
> > >
> > > Thank you!
> > > Eric
> > >
> > > On Tue, Jul 16, 2024 at 09:00 Eric Berryman <er...@gmail.com>
> > > wrote:
> > >
> > >> When I register my tablescan object, and add my rules, I also added a
> > >> removeRule for EnumerableRules.ENUMERABLE_MERGE_UNION_RULE, and now
> > >> everything works with an offset also.
> > >>
> > >> Although, I don’t know why this is. I noticed the EnumerableMergeUnion
> > >> object in the plan, and thought I would try to remove it, because it
> was
> > >> different.
> > >>
> > >> Any explanation is appreciated, thank you!!!
> > >> Eric
> > >>
> > >>
> > >> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <er...@gmail.com>
> > >> wrote:
> > >>
> > >>> Hello!
> > >>>
> > >>> I seem to have an issue with my new limit rule which pushes down to
> the
> > >>> datastore. It works fine, unless I add an offset to the fetch. Where
> I
> > end
> > >>> up with the following error:
> > >>> There are not enough rules… Missing conversion is
> > >>> LogicalUnion[convention: NONE -> LDAP]
> > >>>
> > >>> Why would this only come up when an offset value is added?
> > >>>
> > >>> Thank you!
> > >>> Eric
> > >>>
> > >>>
> > >>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <eric.berryman@gmail.com
> >
> > >>> wrote:
> > >>>
> > >>>> That’s perfect
> > >>>>
> > >>>>
> >
> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
> > >>>>
> > >>>> Thank you for such a quick response!
> > >>>> Eric
> > >>>>
> > >>>>
> > >>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <mm...@apache.org>
> wrote:
> > >>>>
> > >>>>> Eric,
> > >>>>>
> > >>>>> Could you give a more specific example of the failure scenario
> you're
> > >>>>> experiencing?
> > >>>>>
> > >>>>> For a simple example of how limits can be pushed down, this is done
> > in
> > >>>>> the
> > >>>>> Cassandra adapter with CassandraLimitRule. It matches an
> > >>>>> EnumerableLimit on
> > >>>>> top of a CassandraToEnumerableConverter and then converts that
> limit
> > >>>>> to a
> > >>>>> CassandraLimit which passes along the limit and offset information
> to
> > >>>>> CassandraToEnumerableConverter for when the query is executed. The
> > >>>>> EnumerableLimit is then replaced with a CassandraLimit effectively
> > as a
> > >>>>> placeholder to signal that the limit has been handled.
> > >>>>>
> > >>>>> --
> > >>>>> Michael Mior
> > >>>>> mmior@apache.org
> > >>>>>
> > >>>>>
> > >>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <
> > eric.berryman@gmail.com
> > >>>>> >
> > >>>>> wrote:
> > >>>>>
> > >>>>> > Hello!
> > >>>>> >
> > >>>>> > When I add limit to my relbuilder object, the planner gives up.
> But
> > >>>>> works
> > >>>>> > fine without it.
> > >>>>> >
> > >>>>> > I wasn’t able to find any examples of using limit and pushing the
> > >>>>> limit
> > >>>>> > values down to a data source.
> > >>>>> >
> > >>>>> > Could someone help with some links on this subject?
> > >>>>> >
> > >>>>> > Thank you!
> > >>>>> > Eric
> > >>>>> >
> > >>>>>
> > >>>>
> >
>

Re: Sortlimit and limit examples

Posted by Ruben Q L <ru...@gmail.com>.

Eric, if you want to use EnumerableUnion instead of implementing your own
Union, then I think you'll need to implement the appropriate Converter [1]
to transform between conventions LDAP <=> ENUMERABLE

Best,
Ruben

[1]
https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/convert/Converter.java


On Fri, Jul 19, 2024 at 3:56 PM Eric Berryman <er...@gmail.com>
wrote:

> I implement an LDAPUnion and rule to convert from logicalunion, and of
> course, the error goes away. But I didn’t implement the union correctly, so
> nothing is returned in my query. Is there a way to have my LDAPUnion
> use EnumerableUnion,
> or a way to not have LDAPUnion and take care of this with a rule?
>
> Thank you again!
> Eric
>
> On Tue, Jul 16, 2024 at 14:24 Eric Berryman <er...@gmail.com>
> wrote:
>
> > Well, I was wrong. That didn’t solve my problem with :
> >
> > Missing conversion is LogicalUnion[convention: NONE -> LDAP]
> >
> > Do I have to implement my own union? Is there a way I could just use the
> > EnumerableUnion?
> > ie. NONE -> Enumerable and skip implementing one for my ldap datastore?
> >
> > Thank you!
> > Eric
> >
> > On Tue, Jul 16, 2024 at 09:00 Eric Berryman <er...@gmail.com>
> > wrote:
> >
> >> When I register my tablescan object, and add my rules, I also added a
> >> removeRule for EnumerableRules.ENUMERABLE_MERGE_UNION_RULE, and now
> >> everything works with an offset also.
> >>
> >> Although, I don’t know why this is. I noticed the EnumerableMergeUnion
> >> object in the plan, and thought I would try to remove it, because it was
> >> different.
> >>
> >> Any explanation is appreciated, thank you!!!
> >> Eric
> >>
> >>
> >> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <er...@gmail.com>
> >> wrote:
> >>
> >>> Hello!
> >>>
> >>> I seem to have an issue with my new limit rule which pushes down to the
> >>> datastore. It works fine, unless I add an offset to the fetch. Where I
> end
> >>> up with the following error:
> >>> There are not enough rules… Missing conversion is
> >>> LogicalUnion[convention: NONE -> LDAP]
> >>>
> >>> Why would this only come up when an offset value is added?
> >>>
> >>> Thank you!
> >>> Eric
> >>>
> >>>
> >>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <er...@gmail.com>
> >>> wrote:
> >>>
> >>>> That’s perfect
> >>>>
> >>>>
> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
> >>>>
> >>>> Thank you for such a quick response!
> >>>> Eric
> >>>>
> >>>>
> >>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <mm...@apache.org> wrote:
> >>>>
> >>>>> Eric,
> >>>>>
> >>>>> Could you give a more specific example of the failure scenario you're
> >>>>> experiencing?
> >>>>>
> >>>>> For a simple example of how limits can be pushed down, this is done
> in
> >>>>> the
> >>>>> Cassandra adapter with CassandraLimitRule. It matches an
> >>>>> EnumerableLimit on
> >>>>> top of a CassandraToEnumerableConverter and then converts that limit
> >>>>> to a
> >>>>> CassandraLimit which passes along the limit and offset information to
> >>>>> CassandraToEnumerableConverter for when the query is executed. The
> >>>>> EnumerableLimit is then replaced with a CassandraLimit effectively
> as a
> >>>>> placeholder to signal that the limit has been handled.
> >>>>>
> >>>>> --
> >>>>> Michael Mior
> >>>>> mmior@apache.org
> >>>>>
> >>>>>
> >>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <
> eric.berryman@gmail.com
> >>>>> >
> >>>>> wrote:
> >>>>>
> >>>>> > Hello!
> >>>>> >
> >>>>> > When I add limit to my relbuilder object, the planner gives up. But
> >>>>> works
> >>>>> > fine without it.
> >>>>> >
> >>>>> > I wasn’t able to find any examples of using limit and pushing the
> >>>>> limit
> >>>>> > values down to a data source.
> >>>>> >
> >>>>> > Could someone help with some links on this subject?
> >>>>> >
> >>>>> > Thank you!
> >>>>> > Eric
> >>>>> >
> >>>>>
> >>>>
>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

I implement an LDAPUnion and rule to convert from logicalunion, and of
course, the error goes away. But I didn’t implement the union correctly, so
nothing is returned in my query. Is there a way to have my LDAPUnion
use EnumerableUnion,
or a way to not have LDAPUnion and take care of this with a rule?

Thank you again!
Eric

On Tue, Jul 16, 2024 at 14:24 Eric Berryman <er...@gmail.com> wrote:

> Well, I was wrong. That didn’t solve my problem with :
>
> Missing conversion is LogicalUnion[convention: NONE -> LDAP]
>
> Do I have to implement my own union? Is there a way I could just use the
> EnumerableUnion?
> ie. NONE -> Enumerable and skip implementing one for my ldap datastore?
>
> Thank you!
> Eric
>
> On Tue, Jul 16, 2024 at 09:00 Eric Berryman <er...@gmail.com>
> wrote:
>
>> When I register my tablescan object, and add my rules, I also added a
>> removeRule for EnumerableRules.ENUMERABLE_MERGE_UNION_RULE, and now
>> everything works with an offset also.
>>
>> Although, I don’t know why this is. I noticed the EnumerableMergeUnion
>> object in the plan, and thought I would try to remove it, because it was
>> different.
>>
>> Any explanation is appreciated, thank you!!!
>> Eric
>>
>>
>> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <er...@gmail.com>
>> wrote:
>>
>>> Hello!
>>>
>>> I seem to have an issue with my new limit rule which pushes down to the
>>> datastore. It works fine, unless I add an offset to the fetch. Where I end
>>> up with the following error:
>>> There are not enough rules… Missing conversion is
>>> LogicalUnion[convention: NONE -> LDAP]
>>>
>>> Why would this only come up when an offset value is added?
>>>
>>> Thank you!
>>> Eric
>>>
>>>
>>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <er...@gmail.com>
>>> wrote:
>>>
>>>> That’s perfect
>>>>
>>>> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
>>>>
>>>> Thank you for such a quick response!
>>>> Eric
>>>>
>>>>
>>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <mm...@apache.org> wrote:
>>>>
>>>>> Eric,
>>>>>
>>>>> Could you give a more specific example of the failure scenario you're
>>>>> experiencing?
>>>>>
>>>>> For a simple example of how limits can be pushed down, this is done in
>>>>> the
>>>>> Cassandra adapter with CassandraLimitRule. It matches an
>>>>> EnumerableLimit on
>>>>> top of a CassandraToEnumerableConverter and then converts that limit
>>>>> to a
>>>>> CassandraLimit which passes along the limit and offset information to
>>>>> CassandraToEnumerableConverter for when the query is executed. The
>>>>> EnumerableLimit is then replaced with a CassandraLimit effectively as a
>>>>> placeholder to signal that the limit has been handled.
>>>>>
>>>>> --
>>>>> Michael Mior
>>>>> mmior@apache.org
>>>>>
>>>>>
>>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <eric.berryman@gmail.com
>>>>> >
>>>>> wrote:
>>>>>
>>>>> > Hello!
>>>>> >
>>>>> > When I add limit to my relbuilder object, the planner gives up. But
>>>>> works
>>>>> > fine without it.
>>>>> >
>>>>> > I wasn’t able to find any examples of using limit and pushing the
>>>>> limit
>>>>> > values down to a data source.
>>>>> >
>>>>> > Could someone help with some links on this subject?
>>>>> >
>>>>> > Thank you!
>>>>> > Eric
>>>>> >
>>>>>
>>>>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

Well, I was wrong. That didn’t solve my problem with :

Missing conversion is LogicalUnion[convention: NONE -> LDAP]

Do I have to implement my own union? Is there a way I could just use the
EnumerableUnion?
ie. NONE -> Enumerable and skip implementing one for my ldap datastore?

Thank you!
Eric

On Tue, Jul 16, 2024 at 09:00 Eric Berryman <er...@gmail.com> wrote:

> When I register my tablescan object, and add my rules, I also added a
> removeRule for EnumerableRules.ENUMERABLE_MERGE_UNION_RULE, and now
> everything works with an offset also.
>
> Although, I don’t know why this is. I noticed the EnumerableMergeUnion
> object in the plan, and thought I would try to remove it, because it was
> different.
>
> Any explanation is appreciated, thank you!!!
> Eric
>
>
> On Mon, Jul 15, 2024 at 18:02 Eric Berryman <er...@gmail.com>
> wrote:
>
>> Hello!
>>
>> I seem to have an issue with my new limit rule which pushes down to the
>> datastore. It works fine, unless I add an offset to the fetch. Where I end
>> up with the following error:
>> There are not enough rules… Missing conversion is
>> LogicalUnion[convention: NONE -> LDAP]
>>
>> Why would this only come up when an offset value is added?
>>
>> Thank you!
>> Eric
>>
>>
>> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <er...@gmail.com>
>> wrote:
>>
>>> That’s perfect
>>>
>>> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
>>>
>>> Thank you for such a quick response!
>>> Eric
>>>
>>>
>>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <mm...@apache.org> wrote:
>>>
>>>> Eric,
>>>>
>>>> Could you give a more specific example of the failure scenario you're
>>>> experiencing?
>>>>
>>>> For a simple example of how limits can be pushed down, this is done in
>>>> the
>>>> Cassandra adapter with CassandraLimitRule. It matches an
>>>> EnumerableLimit on
>>>> top of a CassandraToEnumerableConverter and then converts that limit to
>>>> a
>>>> CassandraLimit which passes along the limit and offset information to
>>>> CassandraToEnumerableConverter for when the query is executed. The
>>>> EnumerableLimit is then replaced with a CassandraLimit effectively as a
>>>> placeholder to signal that the limit has been handled.
>>>>
>>>> --
>>>> Michael Mior
>>>> mmior@apache.org
>>>>
>>>>
>>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <er...@gmail.com>
>>>> wrote:
>>>>
>>>> > Hello!
>>>> >
>>>> > When I add limit to my relbuilder object, the planner gives up. But
>>>> works
>>>> > fine without it.
>>>> >
>>>> > I wasn’t able to find any examples of using limit and pushing the
>>>> limit
>>>> > values down to a data source.
>>>> >
>>>> > Could someone help with some links on this subject?
>>>> >
>>>> > Thank you!
>>>> > Eric
>>>> >
>>>>
>>>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

When I register my tablescan object, and add my rules, I also added a
removeRule for EnumerableRules.ENUMERABLE_MERGE_UNION_RULE, and now
everything works with an offset also.

Although, I don’t know why this is. I noticed the EnumerableMergeUnion
object in the plan, and thought I would try to remove it, because it was
different.

Any explanation is appreciated, thank you!!!
Eric


On Mon, Jul 15, 2024 at 18:02 Eric Berryman <er...@gmail.com> wrote:

> Hello!
>
> I seem to have an issue with my new limit rule which pushes down to the
> datastore. It works fine, unless I add an offset to the fetch. Where I end
> up with the following error:
> There are not enough rules… Missing conversion is LogicalUnion[convention:
> NONE -> LDAP]
>
> Why would this only come up when an offset value is added?
>
> Thank you!
> Eric
>
>
> On Fri, Jun 21, 2024 at 09:25 Eric Berryman <er...@gmail.com>
> wrote:
>
>> That’s perfect
>>
>> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
>>
>> Thank you for such a quick response!
>> Eric
>>
>>
>> On Fri, Jun 21, 2024 at 09:14 Michael Mior <mm...@apache.org> wrote:
>>
>>> Eric,
>>>
>>> Could you give a more specific example of the failure scenario you're
>>> experiencing?
>>>
>>> For a simple example of how limits can be pushed down, this is done in
>>> the
>>> Cassandra adapter with CassandraLimitRule. It matches an EnumerableLimit
>>> on
>>> top of a CassandraToEnumerableConverter and then converts that limit to a
>>> CassandraLimit which passes along the limit and offset information to
>>> CassandraToEnumerableConverter for when the query is executed. The
>>> EnumerableLimit is then replaced with a CassandraLimit effectively as a
>>> placeholder to signal that the limit has been handled.
>>>
>>> --
>>> Michael Mior
>>> mmior@apache.org
>>>
>>>
>>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <er...@gmail.com>
>>> wrote:
>>>
>>> > Hello!
>>> >
>>> > When I add limit to my relbuilder object, the planner gives up. But
>>> works
>>> > fine without it.
>>> >
>>> > I wasn’t able to find any examples of using limit and pushing the limit
>>> > values down to a data source.
>>> >
>>> > Could someone help with some links on this subject?
>>> >
>>> > Thank you!
>>> > Eric
>>> >
>>>
>>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

Hello!

I seem to have an issue with my new limit rule which pushes down to the
datastore. It works fine, unless I add an offset to the fetch. Where I end
up with the following error:
There are not enough rules… Missing conversion is LogicalUnion[convention:
NONE -> LDAP]

Why would this only come up when an offset value is added?

Thank you!
Eric


On Fri, Jun 21, 2024 at 09:25 Eric Berryman <er...@gmail.com> wrote:

> That’s perfect
>
> https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401
>
> Thank you for such a quick response!
> Eric
>
>
> On Fri, Jun 21, 2024 at 09:14 Michael Mior <mm...@apache.org> wrote:
>
>> Eric,
>>
>> Could you give a more specific example of the failure scenario you're
>> experiencing?
>>
>> For a simple example of how limits can be pushed down, this is done in the
>> Cassandra adapter with CassandraLimitRule. It matches an EnumerableLimit
>> on
>> top of a CassandraToEnumerableConverter and then converts that limit to a
>> CassandraLimit which passes along the limit and offset information to
>> CassandraToEnumerableConverter for when the query is executed. The
>> EnumerableLimit is then replaced with a CassandraLimit effectively as a
>> placeholder to signal that the limit has been handled.
>>
>> --
>> Michael Mior
>> mmior@apache.org
>>
>>
>> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <er...@gmail.com>
>> wrote:
>>
>> > Hello!
>> >
>> > When I add limit to my relbuilder object, the planner gives up. But
>> works
>> > fine without it.
>> >
>> > I wasn’t able to find any examples of using limit and pushing the limit
>> > values down to a data source.
>> >
>> > Could someone help with some links on this subject?
>> >
>> > Thank you!
>> > Eric
>> >
>>
>

Re: Sortlimit and limit examples

Posted by Eric Berryman <er...@gmail.com>.

That’s perfect
https://github.com/apache/calcite/blob/main/cassandra/src/main/java/org/apache/calcite/adapter/cassandra/CassandraRules.java#L401

Thank you for such a quick response!
Eric


On Fri, Jun 21, 2024 at 09:14 Michael Mior <mm...@apache.org> wrote:

> Eric,
>
> Could you give a more specific example of the failure scenario you're
> experiencing?
>
> For a simple example of how limits can be pushed down, this is done in the
> Cassandra adapter with CassandraLimitRule. It matches an EnumerableLimit on
> top of a CassandraToEnumerableConverter and then converts that limit to a
> CassandraLimit which passes along the limit and offset information to
> CassandraToEnumerableConverter for when the query is executed. The
> EnumerableLimit is then replaced with a CassandraLimit effectively as a
> placeholder to signal that the limit has been handled.
>
> --
> Michael Mior
> mmior@apache.org
>
>
> On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <er...@gmail.com>
> wrote:
>
> > Hello!
> >
> > When I add limit to my relbuilder object, the planner gives up. But works
> > fine without it.
> >
> > I wasn’t able to find any examples of using limit and pushing the limit
> > values down to a data source.
> >
> > Could someone help with some links on this subject?
> >
> > Thank you!
> > Eric
> >
>

Re: Sortlimit and limit examples

Posted by Michael Mior <mm...@apache.org>.

Eric,

Could you give a more specific example of the failure scenario you're
experiencing?

For a simple example of how limits can be pushed down, this is done in the
Cassandra adapter with CassandraLimitRule. It matches an EnumerableLimit on
top of a CassandraToEnumerableConverter and then converts that limit to a
CassandraLimit which passes along the limit and offset information to
CassandraToEnumerableConverter for when the query is executed. The
EnumerableLimit is then replaced with a CassandraLimit effectively as a
placeholder to signal that the limit has been handled.

--
Michael Mior
mmior@apache.org

On Fri, Jun 21, 2024 at 8:44 AM Eric Berryman <er...@gmail.com>
wrote:

> Hello!
>
> When I add limit to my relbuilder object, the planner gives up. But works
> fine without it.
>
> I wasn’t able to find any examples of using limit and pushing the limit
> values down to a data source.
>
> Could someone help with some links on this subject?
>
> Thank you!
> Eric
>