title: H2 Database and CTE (recursive query)
body: <p>I am using in memory H2 database for my tests. My application is Spring Boot and I am having trouble when running a CTE (Recursive query) from the application.</p>
  
  <p>From the H2 console the query works like a charm but not when it's called from the application (it returns no records, although they are there, I can see from the H2 console using the very same query Hibernate prints in Java console).</p>
  
  <p>I tried to annotate the native query in the repository at first and now I am trying to run it from a Custom repository. None works.</p>
  
  <p>Here is my custom repository:</p>
  
  <pre><code>public class RouteRepositoryImpl implements CustomRouteRepository{
  
      @PersistenceContext
      private EntityManager entityManager;
  
      @SuppressWarnings("unchecked")
      @Override
      public List&lt;Route&gt; findPossibleRoutesByRouteFrom(String name, String routeFrom) {
          StringBuffer sb = new StringBuffer();
          sb.append("WITH LINK(ID ,ROUTE_FROM ,ROUTE_TO,DISTANCE, LOGISTICS_NETWORK_ID ) AS ");
          sb.append("(SELECT ID , ROUTE_FROM ,ROUTE_TO, DISTANCE, LOGISTICS_NETWORK_ID FROM ROUTE WHERE ROUTE_FROM=:routeFrom ");
          sb.append("UNION ALL ");
          sb.append("SELECT ROUTE.ID , ROUTE.ROUTE_FROM , ROUTE.ROUTE_TO, ROUTE.DISTANCE, ROUTE.LOGISTICS_NETWORK_ID ");
          sb.append("FROM LINK INNER JOIN ROUTE ON LINK.ROUTE_TO = ROUTE.ROUTE_FROM) ");
          sb.append("SELECT DISTINCT L.ID, L.ROUTE_FROM, L.ROUTE_TO, L.DISTANCE, L.LOGISTICS_NETWORK_ID ");
          sb.append("FROM LINK L WHERE LOGISTICS_NETWORK_ID = (SELECT L.ID FROM LOGISTICS_NETWORK L WHERE L.NAME=:name) ");
          sb.append("ORDER BY ROUTE_FROM, ROUTE_TO ");
          Query query= entityManager.createNativeQuery(sb.toString(), Route.class);
          query.setParameter("routeFrom", routeFrom);
          query.setParameter("name", name);
          List&lt;Route&gt; list = query.getResultList();
          return list;
      }
  }
  </code></pre>
  
  <p>The parameters are not the problem as I tested with them hard coded into the query.</p>
  
  <p>Data is being loaded into the database before each test with RunScript.execute and being truncated right after the test finishes.</p>
  
  <p>I also tried to save data using regular repositor.save in the test (to make sure data is being saved in the same database instance) and the results are always the same, no matter what I do.</p>
  
  <p>This app is being built as a test before the interview for a job and I am already late because of this. </p>
  
  <p>Any help is much appreciated.</p>
  
  <p>Thanks,
  Paulo</p>