title: TEMPORARY TABLE doesn't exist, not created as expected? MYSQL
body: <pre><code>-- --------------------------------------------------------------------------------
  -- Routine DDL
  -- --------------------------------------------------------------------------------
  DELIMITER $$
  
  CREATE DEFINER=`root`@`localhost` PROCEDURE `storage_choices_according_to_criteria`( 
                                          IN storage_calc_usage BIGINT,
                                          IN all_provider_considered BOOLEAN,
                                          IN provider_LIST varchar(200),
                                          IN has_requests BOOLEAN
                                          )
  BEGIN
  
      IF storage_calc_usage &gt; 0 THEN
          drop table if exists `storage_choices_expanded`;
          CREATE TEMPORARY TABLE `storage_choices_expanded` AS
          (select *, 
               usage_each_plan * standard_storage as price_storage, 
               concat_ws(' ',`Provider Name`,`Name`,`region_name`) as group_name
          from 
             (SELECT *, 
                  ( 
                      if( 
                      (quota_band_high is not NULL) and storage_calc_usage&gt;quota_band_high, 
                      quota_band_high, 
                      storage_calc_usage
                      ) - quota_band_low
                  ) as usage_each_plan
              FROM `storage_service_price`
              where storage_calc_usage &gt; quota_band_low
          and if(all_provider_considered, 1, find_in_set(`Provider Name`,provider_LIST))
             ) as storage_usage_each_plan
          );
  
          drop table if exists `request_options_for_storage`;
  
          if has_requests then
  
              CREATE TEMPORARY TABLE `request_options_for_storage` as
              SELECT 
                  price_storage_requests.*,
                  n,
                  (n / per_unit_amount)*request_price as cost
              FROM `storage_request_criterias`
              right join price_storage_requests
              on price_storage_requests.storage_request_name = storage_request_criterias.name
              ;
  
          else
  
              CREATE TEMPORARY TABLE `request_options_for_storage` as
              select *, 
                  0 as cost,
                  0 as n
              from `price_storage_requests`
              where 0=1
              ;
  
          end if;
  
          drop table if exists `choices_storage_summed`;
          CREATE TEMPORARY TABLE `choices_storage_summed` AS
  
          select 
              summed.*,
              total_storage_cost + if(total_requests_cost is NULL,0,total_requests_cost) as 'Total Price',
              total_requests_cost,
              request_type_id_list
          from 
              (
                  select *, 
                    sum(price_storage) as total_storage_cost, 
                    count(group_name) as count 
                  from storage_choices_expanded
                  group by group_name
              ) as summed
          left join
          (
              select 
              sum(cost) as total_requests_cost,
              GROUP_CONCAT(request_options_for_storage.id) as request_type_id_list,
              resource_type_id 
              from `request_options_for_storage`
              group by resource_type_id       
          ) as requests
          on requests.resource_type_id = summed.resource_type_id      
          where 
              (   
                  count=1 
                  and 
                  if(quota_band_high is NULL,1,storage_calc_usage&lt;=quota_band_high) 
              ) 
              or count&gt;1 
          order by 'Total Price' asc
          ;
  
      END IF;
  
  END
  </code></pre>
  
  <p>Above is the PROCEDURE I called, but when I try to select from the table <code>choices_storage_summed</code>, it gives me a <code>Table doesn't exist</code> error.</p>
  
  <p>I called the PROCEDURE programatically via jdbc connection, I'm using the same connection.
  The following code shows how I called the procedure:</p>
  
  <pre><code>private static void calcChoicesStorageSummed(Map&lt;String, Integer&gt; requests, boolean consider_all_provider, String provider_list, Integer storage_calc_usage, Integer daysInUse) throws SQLException {
  
      storage_calc_usage = storage_calc_usage * (daysInUse / DAYS_PER_MONTH);
      Boolean has_requests = insertIntoStorageRequestCriterias(requests); 
  
      CallableStatement cs = conn.prepareCall("call "+db+".storage_choices_according_to_criteria(?,?,?,?);"); 
      cs.setInt("storage_calc_usage", storage_calc_usage);
      cs.setBoolean("all_provider_considered", consider_all_provider);
      cs.setString("provider_LIST", provider_list); 
      cs.setBoolean("has_requests", has_requests);
      cs.executeQuery();
      cs.close();
  } 
  </code></pre>
  
  <p>And here is the code trying to access the temporary table, <code>conn</code> is a global variable which remains the same during the related operations.</p>
  
  <pre><code>    calcChoicesStorageSummed(requests,(provider_name_list == null),provider_name_list,storage_usage,duration);
      /**
       * debug
       */
      sql = "select * from choices_storage_summed ";
      sql += ";";
  
      prest = conn.prepareStatement(sql);     
      rs = prest.executeQuery();
      while (rs.next()) {
          System.out.println(rs.getString("standard_storage")); 
      }
  </code></pre>