title: org.h2.jdbc.JdbcSQLException: Column "X" not found on view + H2 Database 1.4.190 + Hibernate 4.2.0.Final
body: <p>I was running my unit tests directly with the targetted Database which is : Oracle 10g and i was asked to run them under H2 Database.</p>
  
  <p>So, after generating the differents SQL scripts and configured the H2 database (through XML config. with Spring), i realized that some of my tests failed whereas some of them passed...</p>
  
  <p>I have the following view (a view of a view) which returns different columns :</p>
  
  <pre><code>CREATE OR REPLACE FORCE VIEW ##.VIEW_OLB_SAT_COVERAGE
  (
     SAT_CODE,
     ORBITAL_POSITION,
     COVERAGE_DOWN,
     COVERAGE_UP
  )
  AS
       SELECT DISTINCT sat_code,
                       orbital_position,
                       coverage_down,
                       coverage_up
         FROM ##.VIEW_OLB_TXP_COVERAGE
     ORDER BY sat_code ASC;
  </code></pre>
  
  <p>The view "VIEW_OLB_TXP_COVERAGE" is the following : </p>
  
  <pre><code>CREATE OR REPLACE FORCE VIEW ##.VIEW_OLB_TXP_COVERAGE
  (
     SAT_CODE,
     ORBITAL_POSITION,
     TXP_NO,
     COVERAGE_DOWN,
     COVERAGE_UP,
     POLARIZATION_DOWN,
     POLARIZATION_UP
  )
  AS
       SELECT DISTINCT ##.OLB_AVAILABLE_TXP.SAT_CODE,
                       ###.SAT_DEP.ORBIT_LOC / 3600 ORBITAL_POSITION,
                       ##.OLB_AVAILABLE_TXP.TXP_NO,
                       ###.TXP.CUR_DN_COVERAGE COVERAGE_DOWN,
                       ###.TXP.CUR_UP_COVERAGE COVERAGE_UP,
                       ###.TXP.TXP_DN_POL,
                       ###.TXP.TXP_UP_POL
  
        ... The rest is omitted for clarity
  </code></pre>
  
  <p>The Hibernate entities are the following : </p>
  
  <pre><code>@Component(value = "viewOlbSatCoverage")
  @Scope("prototype")
  @Entity
  @Table(name = "VIEW_OLB_SAT_COVERAGE", schema = "##")
  public class ViewOlbSatCoverage implements Serializable {
  
      private static final long serialVersionUID = -6728959649786852446L;
  
      @Id
      private ViewOlbSatCoverageId viewOlbSatCoverageId;
  
      public ViewOlbSatCoverageId getViewOlbSatCoverageId() {
          return viewOlbSatCoverageId;
      }
  
      public void setViewOlbSatCoverageId(ViewOlbSatCoverageId viewOlbSatCoverageId) {
          this.viewOlbSatCoverageId = viewOlbSatCoverageId;
      }
  
      @Override
      public String toString() {
          return "ViewOlbSatCoverage : [viewOlbSatCoverageId=" + viewOlbSatCoverageId == null ? "null"
                  : viewOlbSatCoverageId.toString() + "]";
      }
  }
  
  @Component(value = "viewOlbSatCoverageId")
  @Scope("prototype")
  @Embeddable
  public class ViewOlbSatCoverageId implements Serializable {
  
      private static final long serialVersionUID = 2822316442843031126L;
  
      @Column(name = "COVERAGE_DOWN")
      private String coverageDown;
  
      @Column(name = "COVERAGE_UP")
      private String coverageUp;
  
      @Column(name = "SAT_CODE")
      private String satCode;
  
      @Column(name = "ORBITAL_POSITION")
      private BigDecimal orbitalPosition;
  
      public ViewOlbSatCoverageId() {
  
      }
  
      public ViewOlbSatCoverageId(String satCode, BigDecimal orbitalPosition, String coverageDown, String coverageUp) {
          setSatCode(satCode);
          setOrbitalPosition(orbitalPosition);
          setCoverageDown(coverageDown);
          setCoverageUp(coverageUp);
      }
  
  // Getters, setters omitted for clarity ...
  </code></pre>
  
  <p>}</p>
  
  <p>As I mentionned, the targetted database is Oracle 10g and all works fine with it (when the app is running). 
  But when I run H2 database for unit testing purpose, the following method ( findDistinctAllOrbitalPositions) doesn't work :</p>
  
  <pre><code>    @Repository
      public class ViewOlbSatCoverageDaoImpl extends GenericDaoImpl&lt;ViewOlbSatCoverage&gt; implements ViewOlbSatCoverageDao {
  
          public ViewOlbSatCoverageDaoImpl() {
              setGenericClass(ViewOlbSatCoverage.class);
              setDaoLogger(ViewOlbSatCoverageDaoImpl.class);
          }
  
          @Override
          @SuppressWarnings("unchecked")
          public List&lt;BigDecimal&gt; findDistinctAllOrbitalPositions() {
              Query query = sessionFactory.getCurrentSession().createQuery(
                      "select distinct(viewOlbSatCoverageId.orbitalPosition) from " + genericClass.getSimpleName()
                      + " order by viewOlbSatCoverageId.orbitalPosition asc"); // genericClass is ViewOlbSatCoverage
              return query.list();
          }
  
  }
  </code></pre>
  
  <p>As you can see, i'm using HQL but also the Criteria API.</p>
  
  <p>When i launch maven from command line : </p>
  
  <pre><code>mvn clean -Dtest=ViewOlbSatCoverageDaoImplTestCase test
  </code></pre>
  
  <p>It gives me the following : </p>
  
  <pre><code>Caused by: org.h2.jdbc.JdbcSQLException: Column "VIEWOLBSAT0_.ORBITAL_POSITION" not found; SQL state
  ment:
  select distinct viewolbsat0_.ORBITAL_POSITION as col_0_0_ from ##.VIEW_OLB_SAT_COVERAGE viewolbsat0_
   order by viewolbsat0_.ORBITAL_POSITION asc [42122-190]
          at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
          at org.h2.message.DbException.get(DbException.java:179)
          at org.h2.message.DbException.get(DbException.java:155)
          at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:147)
          at org.h2.expression.Alias.optimize(Alias.java:51)
          at org.h2.command.dml.Select.prepare(Select.java:835)
          at org.h2.command.Parser.prepareCommand(Parser.java:246)
          at org.h2.engine.Session.prepareLocal(Session.java:460)
          at org.h2.engine.Session.prepareCommand(Session.java:402)
          at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)
          at org.h2.jdbc.JdbcPreparedStatement.&lt;init&gt;(JdbcPreparedStatement.java:72)
          at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:276)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
          at java.lang.reflect.Method.invoke(Method.java:606)
          at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(Connec
  tionProxyHandler.java:138)
          ... 64 more
  </code></pre>
  
  <p>Here is a sample of my applicationContext-test.xml :</p>
  
  <pre><code>    &lt;jdbc:embedded-database id="h2TestDataSource" type="H2"/&gt;
          &lt;jdbc:initialize-database data-source="h2TestDataSource"&gt;
      &lt;jdbc:script location="classpath:com/eutelsat/olb/sql/create/views/VIEW_OLB_SAT_COVERAGE.sql" /&gt;
  &lt;jdbc:script location="classpath:com/eutelsat/olb/sql/create/views/VIEW_OLB_TXP_COVERAGE.sql" /&gt;
          &lt;!-- Other .sql files omitted for clarity --&gt;
          &lt;/jdbc:initialize-database&gt;
  
                                         &lt;!-- Connection pool --&gt;
  
      &lt;bean id="pooledDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"&gt;       
          &lt;property name="driverClass" value="org.h2.Driver" /&gt;
          &lt;property name="jdbcUrl" value="jdbc:h2:mem:h2TestDataSource;INIT=RUNSCRIPT FROM 'com/eutelsat/olb/sql/create/schemas/INIT.sql'" /&gt;
          &lt;property name="user" value="sa" /&gt;
          &lt;property name="password" value="" /&gt;
          &lt;property name="acquireIncrement" value="1" /&gt;
          &lt;property name="minPoolSize" value="1" /&gt;
          &lt;property name="maxPoolSize" value="5" /&gt;
          &lt;property name="maxIdleTime" value="30" /&gt;
          &lt;property name="maxIdleTimeExcessConnections" value="10" /&gt;
          &lt;property name="numHelperThreads" value="3" /&gt;
          &lt;property name="unreturnedConnectionTimeout" value="0" /&gt;
      &lt;/bean&gt;
  
                                         &lt;!-- Session factory --&gt;
  
      &lt;bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean"&gt;
        &lt;property name="dataSource" ref="h2TestDataSource" /&gt;
        &lt;property name="packagesToScan" value="com.eutelsat.olb.server.model" /&gt;
        &lt;property name="hibernateProperties"&gt;
           &lt;props&gt;
              &lt;prop key="hibernate.hbm2ddl.auto"&gt;none&lt;/prop&gt;
              &lt;prop key="hibernate.jdbc.batch_size"&gt;20&lt;/prop&gt;
              &lt;prop key="hibernate.use_outer_join"&gt;true&lt;/prop&gt;
              &lt;prop key="hibernate.order_inserts"&gt;true&lt;/prop&gt;
              &lt;prop key="hibernate.order_updates"&gt;true&lt;/prop&gt;
              &lt;prop key="hibernate.dialect"&gt;org.hibernate.dialect.H2Dialect&lt;/prop&gt;
              &lt;prop key="hibernate.show_sql"&gt;true&lt;/prop&gt;
              &lt;prop key="format_sql"&gt;true&lt;/prop&gt;
              &lt;prop key="hibernate.id.new_generator_mappings"&gt;true&lt;/prop&gt;
           &lt;/props&gt;
        &lt;/property&gt;
     &lt;/bean&gt;
  
                                      &lt;!-- Transaction Manager --&gt;
  
      &lt;bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager"&gt;
          &lt;property name="sessionFactory" ref="sessionFactory" /&gt;
      &lt;/bean&gt;
  </code></pre>
  
  <p>I have read a lot of things but I can't find what the problem is (incorrect SQL script which needs to be adapted to fit in H2 database or maybe an H2 database config. with column alias matters ?).</p>
  
  <p>NB : schemas have been obfuscated.</p>
  
  <p>Thanks in advance for pointers.</p>