Hi Yannick, You are testing a use case of WITH-CTE's that I never thought about.
Under the hood, the CTE's are implemented as temporary session views which get removed after the initial WITH expression has been used (used once, I believe). So for this implementation's use in your scenario is almost never going to work. :-( You need an implementation where WITH CTE's can be are expected to i) persist as part of a permanent view (maybe the WITH statement CTE's can dynamically re-create themselves when needed OR be scoped to live longer but not so as to allow their names to collide with other user's table or view objects.) ii) be repeatably invoked every time the view is used (not just once) This will take some time to think about - but I am very appreciative that I have your test case to focus my thoughts on. Thanks for taking the time to report it! stumc On Mon, Oct 9, 2017 at 5:19 AM, Yannick Tailliez <[email protected] > wrote: > Hi, > > In my case, I do : > > URL : jdbc:h2:tcp://127.0.0.1:9092/database/test;MODE= > MSSQLServer;MVCC=TRUE;DEFAULT_LOCK_TIMEOUT=5000 > > CREATE TABLE my_tree ( > id INTEGER, > parent_fk INTEGER > ); > > INSERT INTO my_tree ( id, parent_fk) VALUES ( 1, NULL ); > INSERT INTO my_tree ( id, parent_fk) VALUES ( 11, 1 ); > INSERT INTO my_tree ( id, parent_fk) VALUES ( 111, 11 ); > INSERT INTO my_tree ( id, parent_fk) VALUES ( 12, 1 ); > INSERT INTO my_tree ( id, parent_fk) VALUES ( 121, 12 ); > > CREATE OR REPLACE VIEW v_my_tree AS > WITH RECURSIVE tree_cte (sub_tree_root_id, tree_level, parent_fk, > child_fk) AS ( > SELECT mt.ID AS sub_tree_root_id, CAST(0 AS INT) AS tree_level, > mt.parent_fk, mt.id > FROM my_tree mt > UNION ALL > SELECT sub_tree_root_id, mtc.tree_level + 1 AS tree_level, > mtc.parent_fk, mt.id > FROM my_tree mt > INNER JOIN tree_cte mtc ON mtc.child_fk = mt.parent_fk > ) > SELECT sub_tree_root_id, tree_level, parent_fk, child_fk FROM tree_cte; > > First time, the CREATE VIEW work well. > > A second call return to me a NullPointerException : > > Error: General error: "java.lang.NullPointerException"; SQL statement: > CREATE OR REPLACE VIEW v_my_tree [...] [50000-196] > SQLState: HY000 > ErrorCode: 50000 > > > And a third (and all following) call return to me : > > Error: Timeout trying to lock table "SYS"; SQL statement: > CREATE OR REPLACE VIEW v_my_tree [...] [50200-196] > SQLState: HYT00 > ErrorCode: 50200 > > > -- > 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 https://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
