(WIP-259) windowing functions ?

[Cyrille Chépélov]

Hello,

I'm currently attempting to use Lingual (wip-257 and wip-259) over a medium-sized dataset (7 GiB CSV)

Simple queries work.

slightly more complex cases also work:

INSERT INTO merged select source, fields from (
        select 'A' as source, fields from (select distinct fields from A)
        select 'B' as source, fields from (select distinct fields from B)
        select 'C' as source, fields from (select distinct fields from C)
        select 'D' as source, fields from (select distinct fields from D)
        select 'E' as source, fields from (select distinct fields from E)
        select 'F' as source, fields from (select distinct fields from F)
) -- OK

... even if the resulting chain of reducers seems a bit slow (the dataset is still workable with a "sort -u+awk+cat" pipeline for the moment, to produce "merged")

GROUP BY queries appear to work
    select sum(foo), bar from titi group by bar -- OK

However, windowed queries fail with the following message / stack:
    select sum(foo) over (partition by bar order by whatever), bar from titi

java.lang.RuntimeException: cannot translate expression COUNT($t11) OVER (PARTITION BY $t6 ORDER BY $t0 ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
        at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate0(RexToLixTranslator.java:283)
        at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate(RexToLixTranslator.java:127)
        at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate0(RexToLixTranslator.java:258)
        at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate(RexToLixTranslator.java:127)
        at net.hydromatic.optiq.rules.java.RexToLixTranslator.translate(RexToLixTranslator.java:123)
        at net.hydromatic.optiq.rules.java.RexToLixTranslator.translateList(RexToLixTranslator.java:366)
        at net.hydromatic.optiq.rules.java.RexImpTable.implementCall(RexImpTable.java:512)
        ....
        at net.hydromatic.optiq.rules.java.RexToLixTranslator.translateList(RexToLixTranslator.java:366)
        at net.hydromatic.optiq.rules.java.RexToLixTranslator.translateProjects(RexToLixTranslator.java:116)
        at cascading.lingual.optiq.CalcProjectUtil.addFunction(CalcProjectUtil.java:242)
        at cascading.lingual.optiq.CalcProjectUtil.addProgram(CalcProjectUtil.java:152)
        at cascading.lingual.optiq.CalcProjectUtil.resolveBranch(CalcProjectUtil.java:91)
        at cascading.lingual.optiq.CascadingProjectRel.visitChild(CascadingProjectRel.java:84)
        at cascading.lingual.optiq.CascadingEnumerableRel.implement(CascadingEnumerableRel.java:83)
        at net.hydromatic.optiq.rules.java.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:69)
        at net.hydromatic.optiq.prepare.OptiqPrepareImpl$OptiqPreparingStmt.implement(OptiqPrepareImpl.java:541)
        at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:227)
        at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:131)
        at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare2_(OptiqPrepareImpl.java:256)
        at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare_(OptiqPrepareImpl.java:196)
        at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepareSql(OptiqPrepareImpl.java:169)
        at net.hydromatic.optiq.jdbc.OptiqStatement.parseQuery(OptiqStatement.java:402)
        at net.hydromatic.optiq.jdbc.OptiqStatement.execute(OptiqStatement.java:192)
        at cascading.lingual.jdbc.LingualStatement.execute(LingualStatement.java:167)

(the "cannot translate expression" always refer to COUNT(...) OVER (PARTITION BY ...) no matter which summary function is  actually used).

Apparently, this is caused by the dependency to optiq-0.4.11, which didn seem to fully support windowed queries.

I've attempted to update the dependency from lingual to optiq-0.4.11, to optiq-core-0.4.12.

The gradle build now fails with an "IllegalArgumentException" in just about any test case that tries to run some SQL.

here's the top of stack:

java.lang.IllegalArgumentException: no vertex CASCADING
        at net.hydromatic.optiq.util.graph.DefaultDirectedGraph.addEdge(DefaultDirectedGraph.java:57)
        at net.hydromatic.optiq.util.graph.DefaultDirectedGraph.addEdge(DefaultDirectedGraph.java:25)
        at org.eigenbase.relopt.ConventionTraitDef.registerConverterRule(ConventionTraitDef.java:94)
        at org.eigenbase.relopt.volcano.VolcanoPlanner.addRule(VolcanoPlanner.java:415)
        at cascading.lingual.optiq.CascadingTableAccessRel.registerRules(CascadingTableAccessRel.java:72)
        at cascading.lingual.optiq.CascadingTableAccessRel.register(CascadingTableAccessRel.java:64)
        at org.eigenbase.relopt.AbstractRelOptPlanner.registerClass(AbstractRelOptPlanner.java:200)

I guess optiq-core 0.4.12 now requires some extra initialization to work?

I would really love to help, at least test the integration of more recent version of optiq within Lingual, in order to perform tests using analytic functions.

Anyway, thanks for the hard work done so far, and whichever help is available.

    -- Cyrille

[Julian Hyde]

Cyrille,

You've pretty much figured it out. Windowing functions is a new feature in optiq-0.4.12, and lingual is still on optiq-0.4.11 (it's only about 3 weeks behind optiq, not too bad!). We'll need to upgrade lingual (and based on the stack there, it looks like I need to fix a bug to do the upgrade) and run some tests before we declare that windowed aggregations work. That probably won't happen for a few weeks.

If you'd like to do some testing, you could try regular builtin functions like CASE and arithmetic operators, ORDER BY ... NULLS FIRST / NULLS LAST, and sub-queries (especially IN and EXISTS). The Optiq site has a list [ https://github.com/julianhyde/optiq/blob/master/REFERENCE.md ] of the SQL syntax that Optiq supports; what Lingual supports is similar, but not guaranteed the same.

Julian

--
You received this message because you are subscribed to the Google Groups "Lingual User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lingual-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.