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
--
<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.
