db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <derby-...@db.apache.org>
Subject [jira] Resolved: (DERBY-424) Queryplan for a query using SESSION schema view is incorrectly put in statement cache. This could cause incorrect plan getting executed later if a temp. table is created with that name.
Date Sat, 22 Oct 2005 18:27:06 GMT
     [ http://issues.apache.org/jira/browse/DERBY-424?page=all ]
     
Mamta A. Satoor resolved DERBY-424:
-----------------------------------

    Fix Version: 10.2.0.0
                 10.1.2.0
     Resolution: Fixed

The fix went into both 10.2 and 10.1.2. 
Some additional comments on the fix
1)The reason for adding a new method referencesSessionSchema(QueryTreeNode qt) in GenericPreparedStatement----
If you look at the GenericStatement's prepMinion method, towards the beginning(line 167 onwards
in the review package), we look for the statement in the cache. If it is found there, we set
foundInCache to true. After that, we check if the statement found in the cache might be referencing
SESSION schema object and if yes, then we do not want to use the statement plan from the cache,
instead we want to build it again. The check to see if statement references SESSION schema
is done by following code in GenericStatement's prepMinion method 
    if (foundInCache) {
     if (preparedStmt.referencesSessionSchema()) {
      // cannot use this state since it is private to a connection.
      // switch to a new statement.
      foundInCache = false; 
      preparedStmt = new GenericPreparedStatement(this);
      break;
     }
    }
GenericPreparedStatement or GenericStatement at the time of the check above donot have the
query tree object and hence we can't simply call qt.referencesSessionSchema. For this reason,
I had to add 
public boolean referencesSessionSchema(QueryTreeNode qt) to GenericPreparedStatement(which
gets called by the compile phase after the qt object is constructed) and the method saves
the qt object's referencesSessionSchema() status in it's own local variable referencesSessionSchema.
This local information is what will be used by the other referencesSessionSchema method to
determine if statement found in cache should be discarded and compile phase should be re-executed.

 
So, to summarize, the new method after the bind phase, extracts the referencesSessionSchema
information from the passed qt object and saves it in the local variable.
 public boolean referencesSessionSchema(QueryTreeNode qt)
 throws StandardException {
  //If the query references a SESSION schema table (temporary or permanent), then mark so
in this statement
  referencesSessionSchema = qt.referencesSessionSchema();
  return(referencesSessionSchema);
 }
 
And, the existing referencesSessionSchema() method as shown below, uses the local variable
(which was filled correctly last time the statement was compiled) to let the compile phase
know if it should ignore the plan found in the cache and reconstruct the plan because the
old plan had references to SESSION schema objects. 
 public boolean referencesSessionSchema()
 {
  return referencesSessionSchema;
 }

3)How information about SESSION schema object reference gets lost during optimization phase-----
At the end of the bind phase for select * from session.st1; GenericStatement's qt (QueryTreeNode
object which in this case is CursorNode) object has it's resultSet object as a SelectNode
which has a fromList object with referencesSessionSchema field set to true because it was
referencing an object from SESSION schema. 
When the optimize code is called on this bound qt object, the optimizer replaces the SelectNode
resultSet object with a ProjectRestrictNode and in that process, we loose the referencesSessionSchema
information which was part of the SelectNode's FromList object. Rather than trying to have
that information some how be transferred to the new ResultSet object, it is more efficient
to use the information right after bind phase and remove the plan from the statement cache.


> Queryplan for a query using SESSION schema view is incorrectly put in statement cache.
This could cause incorrect plan getting executed later if a temp. table is created with that
name.
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-424
>          URL: http://issues.apache.org/jira/browse/DERBY-424
>      Project: Derby
>         Type: Bug
>   Components: SQL
>     Versions: 10.1.1.0, 10.2.0.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Assignee: Mamta A. Satoor
>     Priority: Minor
>      Fix For: 10.2.0.0, 10.1.2.0
>  Attachments: Derby424SessionSchemaCaching.txt
>
> See DERBY-405 for some discussion related this issue. I suspect this is because of statement
caching. Derby doesn't seem to recognise we have a temporary table that just overloaded an
existing physical table. 
> It would have been good to avoid permanent tables/views/synonyms in SESSION schema. Not
sure what we should do now about this, though. 
> ij> create view session.st1 as select * from t; 
> 0 rows inserted/updated/deleted 
> ij> select * from session.st1; 
> I |J |K 
> ----------------------------------- 
> 1 |1 |NULL 
> 2 |2 |NULL 
> 3 |3 |NULL 
> 4 |4 |NULL 
> 4 rows selected 
> ij> select * from t; 
> I |J |K 
> ----------------------------------- 
> 1 |1 |NULL 
> 2 |2 |NULL 
> 3 |3 |NULL 
> 4 |4 |NULL 
> 4 rows selected 
> ij> declare global temporary table st1(c11 int, c12 int) on commit preserve rows 
>  not logged; 
> 0 rows inserted/updated/deleted 
> ij> select * from session.st1; 
> I |J |K 
> ----------------------------------- 
> 1 |1 |NULL 
> 2 |2 |NULL 
> 3 |3 |NULL 
> 4 |4 |NULL 
> 4 rows selected 
> ij> select * from session.st1; <==== This statement has an extra space between
FROM and session.st1 
> C11 |C12 
> ----------------------- 
> 0 rows selected 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message