Column not found when SELECTing from a recursive Common Table Expression (CTE)

[Robert Faust]

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

[Andreas Reichel]

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

--
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 h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/47c529f9-b624-437b-b4fd-e284577e1affn%40googlegroups.com.

[Robert Faust]

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

[Noel Grandin]

Unfortunately CTE's in H2 are currently pretty bad, and nobody has felt sufficiently motivated to do the surgery that is necessary to accomodate them really well.