Hi Andreas, Thanks for the quick reply.
I think I may have already determined the cause of my stress. I am using H2 in conjunction with Jasper Reports. The report I am running against the database has several subreports, and some of the subreports were using the same name for the CTEs. So, I was intermittently receiving errors related to missing columns because one of the CTEs had some columns that the other one did not. I have a MS SQL Server background and I didn't realize that the CTEs in H2 persisted across multiple sessions/transactions, as this is not how they behave in SQL Server (they are ephemeral). So, with that in mind, I've begun approaching this problem differently. Thanks Rob On Sunday, June 12, 2022 at 12:57:38 AM UTC-5 [email protected] wrote: > 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 > > [image: https://social.cyclelabs.io/linkedin/signature-footer] > <https://social.cyclelabs.io/linkedin/signature-footer> > > -- > 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 > > <https://groups.google.com/d/msgid/h2-database/47c529f9-b624-437b-b4fd-e284577e1affn%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- <https://social.cyclelabs.io/linkedin/signature-footer> -- 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/f0d6edde-66fb-47e7-84f7-39130fb2262bn%40googlegroups.com.
