State of the recursive CTE feature

[Lukas Eder]

Hello,

We're currently integrating support for CTE in jOOQ and we're wondering if we should support CTE for H2 at all. H2 has experimental CTE support, if I'm not mistaken:

http://www.h2database.com/html/advanced.html?highlight=recursive&search=recursive#recursive_queries

This would be one of our test cases, and it seems to work for H2:

with recursive "t1"("f1", "f2") as (

  select 

    1, 

    'a'

  from dual

union all

  select 

    ("t1"."f1" + 1), 

    ("t1"."f2" || 'a')

  from "t1"

  where "t1"."f1" < 10

)

select 

  "t1"."f1", 

  "t1"."f2"

from "t1"

However, in standard SQL, I can declare several tables in the WITH clause, e.g.:

with recursive "t1"("f1", "f2") as (...),

               "t2"("g1", "g2") as (...),

               ...

select ...

This doesn't seem to work right now for H2.

Am I right in thinking that:

1. Only RECURSIVE CTE are currently supported, although I can tweak a synthetic UNION ALL clause into the query to make H2 believe that we have the required syntax (see below)

2. The RECURSIVE keyword seems to be optional - probably to be Oracle compatible as in Oracle, recursiveness is implicit

3. Only single-table CTE are currently supported

4. This is currently still not a priority for the H2 maintenance team? (as this question occasionally pops up on the user-group) :-)

[From 1] Non-recursive tweak to comply with H2 syntax requirements:

with "t1"("f1", "f2") as (

  select 

    1, 

    'a'

  from dual

union all

  select null, null

  where false

)

select 

  "t1"."f1", 

  "t1"."f2"

from "t1"

Any feedback is very welcome.

Cheers

Lukas

[Thomas Mueller]

Hi,

Yes, support for recursive queries in H2 is very very limited. All limitations should be documented. Right now improving support is not a high priority for me, sorry.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

No worries, just checking. We'll simply not support them for H2 on our side.

Thanks for your responses,

Lukas

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/O-CqOve05RM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.