title: iBatis invalid columnName with temporary tables
body: <p>I am getting an invalid column name when I try to run an SQL map from my application.  Basically I have two separate mapping files, which basically do the same thing, except create a column with a different name.  Note that I have left the DROP TABLE out on purpose to illustrate this issue.  I know that this is a bad practice but I am looking for why this happens.  We have since corrected this behavior by explicitly dropping the tables in every SQL map.</p>
  
  <p>Query1:</p>
  
  <pre><code>&lt;select id="Query1"   parameterClass="query1Criteria"&gt;   
  
      IF Object_Id('tempdb..##severity') IS NOT NULL     
      BEGIN
        DROP TABLE ##severity
      END     
  
      CREATE TABLE ##severity( valueCode VARCHAR(255) NOT NULL, displayOrder INT NOT NULL)
      INSERT INTO ##severity
          SELECT DISTINCT valueCode,MAX(displayOrder) FROM DataDictionaryValue
          WHERE zoneID = #zoneID# AND categoryCode = 'SEVERITY'
          GROUP BY valueCode 
  
      SELECT valueCode FROM ##severity
  
  &lt;/select&gt;
  </code></pre>
  
  <p>Query 2:</p>
  
  <pre><code>&lt;select id="Query2" parameterClass="query2Criteria"&gt;   
  
      IF Object_Id('tempdb..##severity') IS NOT NULL     
      BEGIN
        DROP TABLE ##severity
      END     
  
      CREATE TABLE ##severity( severityCode VARCHAR(255) NOT NULL, displayOrder INT NOT NULL)
      INSERT INTO ##severity
          SELECT DISTINCT valueCode,MAX(displayOrder) FROM DataDictionaryValue
          WHERE zoneID = #zoneID# AND categoryCode = 'SEVERITY'
          GROUP BY valueCode 
  
      SELECT severityCode FROM ##severity
  
  &lt;/select&gt;
  </code></pre>
  
  <p>From the application, I run Query1.  Executes fine, and I see the temporary table created in SQL Management Studio.  Then I execute Query2, and get the following stack trace:</p>
  
  <pre><code>org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
  --- The error occurred in com/test/model/safety/reports/Query2Map.xml.  
  --- The error occurred while applying a parameter map.  
  --- Check the Query2.getResults-InlineParameterMap.  
  --- Check the statement (query failed).  
  --- Cause: java.sql.SQLException: Invalid column name 'severityCode'.
      at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:276)
      at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)
      at org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(SqlMapClientTemplate.java:249)
      at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClientTemplate.java:296)
      at com.test.model.BaseSqlMapDao.executeQueryForList(BaseSqlMapDao.java:53)
      at com.test.model.safety.reports.Query2SqlMapDao.getOpenIssues(Query2SqlMapDao.java:124)
      at com.test.model.safety.reports.Query2SqlMapDao.getResults(Query2SqlMapDao.java:50)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
  </code></pre>
  
  <p>If I run the SQL of Query2 in SMS, it will execute correctly.  Both of these queries, in fact, will execute correctly in SMS.  I do not understand how iBatis works in detail, however it seems like iBatis sees the temporary table created in Query1, and validates Query2's syntax against that schema, in which case the invalid column name exception is thrown.  However, this would mean that iBatis is keeping a reference to the temporary table, which contradicts SQL servers paradigm where temporary tables are scoped to the connection.</p>
  
  <p>Can anyone explain what is happening here?  </p>