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
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
