Greetings! While I am not a developer, but just an H2 user I would like to recommend to provide:
1) the DDL to create table_1 and table_2 and 2) some DML to fill it with data This way we can reproduce your samples easily, with different versions of H2. That said, the CTEs are still considered experimental although I never had problem using those with latest snapshots 2.2.212 - 2.2.219 Best of luck Andreas On Sat, 2022-06-11 at 10:34 -0700, Robert Faust wrote: > Hello all, > > I'm new to the group so I apologize if this question has already been > answered elsewhere. I'm just hoping someone has some ideas of what > could be happening, as I'm running out of ideas. > > Let's suppose I'm retrieving some data from a table that has a > parent/child reference. I'm using a common table expression to SELECT > the starting nodes from this table. Then, I have a second common > table expression to recursively SELECT the parents/ancestors of the > starting nodes (think starting at the leaf of a tree and working > backwards to the root). > > The basic structure looks like this: > /* Initial data retrieval */ > WITH cte AS ( > SELECT table_1.column_1, /* VARCHAR(255) */ > table_1.column_2, /* VARCHAR(255) */ > table_1.column_3, /* INTEGER */ > table_1.node_id, /* VARCHAR(255) */ > table_1.parent_node_id /* VARCHAR(255) */ > FROM table_1 > ), > > /* Recursive CTE */ > recursive_cte(column_1, column_2, node_id, parent_node_id) AS ( > SELECT cte.column_1, > cte.column_2, > cte.node_id, > cte.parent_node_id > FROM cte > UNION ALL > SELECT table_1.column_1, > table_1.column_2, > table_1.node_id, > table_1.parent_node_id > FROM table_1 > JOIN recursive_cte > ON table_1.node_id = recursive_cte.parent_node_id > ) > > SELECT recursive_cte.column_1, > recursive_cte.column_2 > FROM recursive_cte > > The query works great in both of my test databases. Let's call them > database 1 (1000 records in table_1) and database 2 (100,000 records > in table_1). They both use the same schema. > > Now, I'd like to add another column to the output of recursive_cte so > I can do some additional filtering, so I add column_3 (from table_1) > in the highlighted locations: > /* Initial data retrieval */ > WITH cte AS ( > SELECT table_1.column_1, /* VARCHAR(255) */ > table_1.column_2, /* VARCHAR(255) */ > table_1.column_3, /* INTEGER */ > table_1.node_id, /* VARCHAR(255) */ > table_1.parent_node_id /* VARCHAR(255) */ > FROM table_1 > ), > > /* Recursive CTE */ > recursive_cte(column_1, column_2, column_3, node_id, > parent_node_id) AS ( > SELECT cte.column_1, > cte.column_2, > cte.column_3, > cte.node_id, > cte.parent_node_id > FROM cte > UNION ALL > SELECT table_1.column_1, > table_1.column_2, > table_1.column_3, > table_1.node_id, > table_1.parent_node_id > FROM table_1 > JOIN recursive_cte > ON table_1.node_id = recursive_cte.parent_node_id > ) > > SELECT recursive_cte.column_1, > recursive_cte.column_2, > recursive_cte.column_3 > FROM recursive_cte > > This new query works fine in database 1, but errors out in database > 2. The error I get is Column "RECURSIVE_CTE.COLUMN_3" not found, > which leads me to believe it's coming from the last SELECT query at > the bottom. For some reason, that query can't find column_3 in the > recursive CTE, even though I made sure to include it in the > declaration. > > The weird part is that it works in one database but not the other, > even though they share the same schema. It makes me wonder if I'm > hitting some sort of memory limit in H2 Embedded. The weirdest part > is that its always the INTEGER column that seems to disappear, > considering an INTEGER should take up much less space in memory than > a VARCHAR(255). > > Does anyone have any ideas what might cause this? Any insights are > greatly appreciated. > - Rob > > > -- > 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 view this discussion on the web visit > https://groups.google.com/d/msgid/h2-database/47c529f9-b624-437b-b4fd-e284577e1affn%40googlegroups.com > . -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d7b8151636959708ebf919bebf7871c9e58a6497.camel%40manticore-projects.com.
