db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1338017 - in /db/derby/code/trunk/java: engine/org/apache/derby/catalog/ engine/org/apache/derby/impl/sql/catalog/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting/function...
Date Mon, 14 May 2012 01:02:57 GMT
Author: mamta
Date: Mon May 14 01:02:56 2012
New Revision: 1338017

URL: http://svn.apache.org/viewvc?rev=1338017&view=rev
Log:
DERBY-4115 Provide a way to drop statistics information

The details of all the changes in this commit are listed below. 
1)Added a new routine SYSCS_DROP_STATISTICS, with public access similar to SYSCS_UPDATE_STATISTICS. This happens in DataDictionaryImpl, where SYSCS_DROP_STATISTICS is added to the list of public access procedures in sysUtilProceduresWithPublicAccess 
2)The new stored procedure implementation is similar to update statistics, ie allow the routine to go through ALTER TABLE where permission/privilege checking, table/schema/index name validations happen automatically and we implement the routine logic through extension of ALTER TABLE syntax. This new syntax for ALTER TABLE syntax(same as we did for update statistics) is an internal syntax only and won't be available to an end user directly. 
3)This commit changes sqlgrammar.jj to recognize the following internal syntaxes for ALTER TABLE 
a)ALTER TABLE tablename ALL DROP STATISTICS 
The existing(corresponding syntax) for update statistics is as follows 
ALTER TABLE tablename ALL UPDATE STATISTICS 
b)ALTER TABLE tablename STATISTICS DROP indexname 
The existing(corresponding syntax) for update statistics is as follows 
ALTER TABLE tablename UPDATE STATISTICS indexname 
Notice the two syntaxes for index level statistics are different for drop vs update.(the reason for the syntax difference is explained above) 
4)After the statistics are dropped, we send invalidation signal to dependent statements so they would get recompiled when they are executed next time. This will make sure that they pick the correct plan given the statistics for the table. 
5)The commit takes care of some of the test failures(expected failures because of the addition of a new system procedure). 
6)The commit adds basic upgrade test for the new procedure. This test ensures that drop statistics procedure is available only after hard upgrade. 
7)While writing the upgrade tests, I found that a meaningful test for drop statistics could only be written for Derby releases 10.5 and higher. We have found that when constraints end up sharing same backing index, Derby won't create statistics for them. This is issue DERBY-5702. But if we run update statistics on that constraint, we will be able to get the statistics for such a constraint. Later, when the constraint is dropped, because of DERBY-5681, the statistics row for such a constraint(one that shares it's backing index with another constraint) is never dropped. We can use drop statistics procedure introduced in this jira to take care of such hanging indexes. But since update statistics procedure is only available in 10.5 and higher, I couldn't demonstrate use of drop statistics to drop hanging statistics rows. 



Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DDLStatementNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_2.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java?rev=1338017&r1=1338016&r2=1338017&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java Mon May 14 01:02:56 2012
@@ -733,7 +733,7 @@ public class SystemProcedures  {
      *                      index. If null, then update the statistics for all
      *                      the indexes for the given table name.
      *
-	 * @exception  StandardException  Standard exception policy.
+	 * @exception  SQLException
      **/
     public static void SYSCS_UPDATE_STATISTICS(
     	    String  schemaname,
@@ -756,6 +756,45 @@ public class SystemProcedures  {
 
         conn.close();
     }
+    
+    /**
+     * Drop the statistics for 
+     * 1)all the indexes or
+     * 2)a specific index on a table.
+     * 
+     * @param schemaname    schema name of the table/index(es) whose 
+     *                      statistics will be dropped. Must be non-null, 
+     *                      no default is used.
+     * @param tablename     table name of the index(es) whose statistics will
+     *                      be dropped. Must be non-null.
+     * @param indexname     Can be null. If not null or emptry string then the
+     *                      user wants to drop the statistics for only this
+     *                      index. If null, then drop the statistics for all
+     *                      the indexes for the given table name.
+     *
+	 * @exception  SQLException
+     */
+    public static void SYSCS_DROP_STATISTICS(
+    String  schemaname,
+    String  tablename,
+    String  indexname)
+        throws SQLException
+    {
+        String escapedSchema = IdUtil.normalToDelimited(schemaname);
+        String escapedTableName = IdUtil.normalToDelimited(tablename);
+        String query = "alter table " + escapedSchema + "." + escapedTableName;
+        if (indexname == null)
+        	query = query + " all drop statistics ";
+        else
+        	query = query + " statistics drop " + IdUtil.normalToDelimited(indexname);
+        Connection conn = getDefaultConn();
+
+        PreparedStatement ps = conn.prepareStatement(query);
+        ps.executeUpdate();
+        ps.close();
+
+        conn.close();
+    }
 
     /**
      * Compress the table.

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=1338017&r1=1338016&r2=1338017&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Mon May 14 01:02:56 2012
@@ -464,6 +464,7 @@ public final class	DataDictionaryImpl
 												"SYSCS_COMPRESS_TABLE",
 												"SYSCS_UPDATE_STATISTICS",
 												"SYSCS_MODIFY_PASSWORD",
+												"SYSCS_DROP_STATISTICS", 
 												};
 	
 	/**
@@ -13218,6 +13219,33 @@ public final class	DataDictionaryImpl
                 newlyCreatedRoutines,
                 tc);
         }
+
+        // void SYSCS_UTIL.SYSCS_DROP_STATISTICS(varchar(128), varchar(128), varchar(128))
+        {
+            // procedure argument names
+            String[] arg_names = {"SCHEMANAME", "TABLENAME", "INDEXNAME"};
+
+            // procedure argument types
+            TypeDescriptor[] arg_types = {
+                    CATALOG_TYPE_SYSTEM_IDENTIFIER,
+                    CATALOG_TYPE_SYSTEM_IDENTIFIER,
+                    CATALOG_TYPE_SYSTEM_IDENTIFIER
+
+            };
+
+            createSystemProcedureOrFunction(
+                "SYSCS_DROP_STATISTICS",
+                sysUtilUUID,
+                arg_names,
+                arg_types,
+                0,
+                0,
+                RoutineAliasInfo.MODIFIES_SQL_DATA,
+                false,
+                (TypeDescriptor) null,
+                newlyCreatedRoutines,
+                tc);
+        }
     }
 
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java?rev=1338017&r1=1338016&r2=1338017&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java Mon May 14 01:02:56 2012
@@ -70,11 +70,22 @@ public class AlterTableNode extends DDLS
 	 */
 	private	boolean				updateStatisticsAll = false;
 	/**
-	 * If statistic is getting updated for just one index, then 
-	 * indexNameForUpdateStatistics will tell the name of the specific index 
-	 * whose statistics need to be updated.
+	 * dropStatistics will indicate that we are here for dropping the
+	 * statistics. It could be statistics of just one index or all the
+	 * indexes on a given table. 
 	 */
-	private	String				indexNameForUpdateStatistics;
+	private	    boolean					    dropStatistics;
+	/**
+	 * The flag dropStatisticsAll will tell if we are going to drop the 
+	 * statistics of all indexes or just one index on a table. 
+	 */
+	private	    boolean					    dropStatisticsAll;
+	/**
+	 * If statistic is getting updated/dropped for just one index, then 
+	 * indexNameForStatistics will tell the name of the specific index 
+	 * whose statistics need to be updated/dropped.
+	 */
+	private	String				indexNameForStatistics;
 	
 	public	boolean				compressTable = false;
 	public	boolean				sequential = false;
@@ -116,33 +127,6 @@ public class AlterTableNode extends DDLS
 		truncateTable = true;
 		schemaDescriptor = getSchemaDescriptor();
 	}
-
-	/**
-	 * Initializer for a AlterTableNode for updating the statistics. The user
-	 * can ask for update statistic of all the indexes or only a specific index
-	 *
-	 * @param objectName		The name of the table whose index(es) will have
-	 *                          their statistics updated.
-	 * @param updateStatisticsAll	If true then update the statistics of all 
-	 *                          the indexes on the table. If false, then update
-	 *                          the statistics of only the index provided as
-	 *                          3rd parameter here
-	 * @param indexName			Only used if updateStatisticsAll is set to 
-	 *                          false. 
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	public void init(Object objectName,
-			Object updateStatisticsAll,
-			Object indexName)
-	throws StandardException
-	{
-		initAndCheck(objectName);
-		this.updateStatisticsAll = ((Boolean) updateStatisticsAll).booleanValue();
-		this.indexNameForUpdateStatistics = (String)indexName;
-		schemaDescriptor = getSchemaDescriptor();
-		updateStatistics = true;
-	}
 	
 	/**
 	 * Initializer for a AlterTableNode for COMPRESS using temporary tables
@@ -194,39 +178,67 @@ public class AlterTableNode extends DDLS
 	}
 
 	/**
-	 * Initializer for a AlterTableNode
-	 *
+	 * Initializer for a AlterTableNode. The parameter values have different
+	 *  meanings based on what kind of ALTER TABLE is taking place. 
+	 *  
 	 * @param objectName		The name of the table being altered
-	 * @param tableElementList	The alter table action
-	 * @param lockGranularity	The new lock granularity, if any
-	 * @param changeType		ADD_TYPE or DROP_TYPE
-	 * @param behavior			If drop column is CASCADE or RESTRICTED
+	 * @param changeType		ADD_TYPE or DROP_TYPE or UPDATE_STATISTICS or
+	 *                          or DROP_STATISTICS
+	 * @param param1 			For ADD_TYPE or DROP_TYPE, param1 gives the
+	 *                          elements impacted by ALTER TABLE.
+	 *                          For UPDATE_STATISTICS or or DROP_STATISTICS,
+	 *                          param1 is boolean - true means update or drop
+	 *                          the statistics of all the indexes on the table.
+	 *                          False means, update or drop the statistics of
+	 *                          only the index name provided by next parameter.
+	 * @param param2 			For ADD_TYPE or DROP_TYPE, param2 gives the
+	 *                          new lock granularity, if any
+	 *                          For UPDATE_STATISTICS or DROP_STATISTICS,
+	 *                          param2 can be the name of the specific index
+	 *                          whose statistics will be dropped/updated. This
+	 *                          param is used only if param1 is set to false
+	 * @param param3			For DROP_TYPE, param3 can indicate if the drop
+	 *                          column is CASCADE or RESTRICTED. This param is
+	 *                          ignored for all the other changeType.
 	 *
 	 * @exception StandardException		Thrown on error
 	 */
-
 	public void init(
 							Object objectName,
-							Object tableElementList,
-							Object lockGranularity,
 							Object changeType,
-							Object behavior )
+							Object param1,
+							Object param2,
+							Object param3 )
 		throws StandardException
 	{
 		initAndCheck(objectName);
-		this.tableElementList = (TableElementList) tableElementList;
-		this.lockGranularity = ((Character) lockGranularity).charValue();
 
-		int[]	ct = (int[]) changeType, bh = (int[]) behavior;
+		
+		int[]	ct = (int[]) changeType;
 		this.changeType = ct[0];
-		this.behavior = bh[0];
+		
 		switch ( this.changeType )
 		{
 		    case ADD_TYPE:
 		    case DROP_TYPE:
 		    case MODIFY_TYPE:
 		    case LOCKING_TYPE:
+				this.tableElementList = (TableElementList) param1;
+				this.lockGranularity = ((Character) param2).charValue();
+				int[]	bh = (int[]) param3;
+				this.behavior = bh[0];
+				break;
+
+		    case UPDATE_STATISTICS:
+				this.updateStatisticsAll = ((Boolean) param1).booleanValue();
+				this.indexNameForStatistics = (String)param2;
+				updateStatistics = true;
+				break;
 
+		    case DROP_STATISTICS:
+				this.dropStatisticsAll = ((Boolean) param1).booleanValue();
+				this.indexNameForStatistics = (String)param2;
+				dropStatistics = true;
 				break;
 
 		    default:
@@ -259,8 +271,10 @@ public class AlterTableNode extends DDLS
 				"truncateEndOfTable: " + truncateEndOfTable + "\n" +
 				"updateStatistics: " + updateStatistics + "\n" +
 				"updateStatisticsAll: " + updateStatisticsAll + "\n" +
-				"indexNameForUpdateStatistics: " +
-				     indexNameForUpdateStatistics + "\n";
+				"dropStatistics: " + dropStatistics + "\n" +
+				"dropStatisticsAll: " + dropStatisticsAll + "\n" +
+				"indexNameForStatistics: " +
+				indexNameForStatistics + "\n";
 		}
 		else
 		{
@@ -433,20 +447,20 @@ public String statementToString()
         // must be done after resolving the datatypes of the generation clauses
         if (tableElementList != null) { tableElementList.validatePrimaryKeyNullability(); }
 
-		//Check if we are in alter table to update the statistics. If yes, then
-		//check if we are here to update the statistics of a specific index. If
-		//yes, then verify that the indexname provided is a valid one.
-		if (updateStatistics && !updateStatisticsAll)
+		//Check if we are in alter table to update/drop the statistics. If yes,
+		// then check if we are here to update/drop the statistics of a specific
+		// index. If yes, then verify that the indexname provided is a valid one.
+		if ((updateStatistics && !updateStatisticsAll) || (dropStatistics && !dropStatisticsAll))
 		{
 			ConglomerateDescriptor	cd = null;
 			if (schemaDescriptor.getUUID() != null) 
-				cd = dd.getConglomerateDescriptor(indexNameForUpdateStatistics, schemaDescriptor, false);
+				cd = dd.getConglomerateDescriptor(indexNameForStatistics, schemaDescriptor, false);
 
 			if (cd == null)
 			{
 				throw StandardException.newException(
 						SQLState.LANG_INDEX_NOT_FOUND, 
-						schemaDescriptor.getSchemaName() + "." + indexNameForUpdateStatistics);
+						schemaDescriptor.getSchemaName() + "." + indexNameForStatistics);
 			}			
 		}
 
@@ -500,7 +514,9 @@ public String statementToString()
  										     truncateEndOfTable,
  										     updateStatistics,
  										     updateStatisticsAll,
- 										     indexNameForUpdateStatistics);
+ 										     dropStatistics,
+ 										     dropStatisticsAll,
+ 										     indexNameForStatistics);
 	}
 
 	/**

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DDLStatementNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DDLStatementNode.java?rev=1338017&r1=1338016&r2=1338017&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DDLStatementNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DDLStatementNode.java Mon May 14 01:02:56 2012
@@ -61,6 +61,8 @@ abstract class DDLStatementNode extends 
 	public	static	final	int	DROP_TYPE = 2;
 	public	static	final	int	MODIFY_TYPE = 3;
 	public	static	final	int	LOCKING_TYPE = 4;
+	public	static	final	int	UPDATE_STATISTICS = 5;
+	public	static	final	int DROP_STATISTICS = 6;
 
 
 	/////////////////////////////////////////////////////////////////////////

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=1338017&r1=1338016&r2=1338017&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Mon May 14 01:02:56 2012
@@ -13098,54 +13098,151 @@ alterTableBody(TableName tableName) thro
 		return sn;
 	}
 |
-//This means update the statistics of all the indexes on the table
-	<ALL> <UPDATE> <STATISTICS>
+//This means update or drop the statistics of all the indexes on the table
+	<ALL>
+	(
+		sn = dropStatistics(tableName)
+		|
+		sn = updateStatistics(tableName)
+	)
+	{
+		return sn;
+	}
+|
+//This means update the statistics of the index name supplied for the table
+	<UPDATE> <STATISTICS>
+        (
+		indexName =  identifier(Limits.MAX_IDENTIFIER_LENGTH, true)
+        )
 	{
 		checkVersion( DataDictionary.DD_VERSION_DERBY_10_5, "SYSCS_UPDATE_STATISTICS");
 		//This will make sure that this ALTER TABLE...syntax can't be called directly.
 		//This sql can only be generated internally (right now it is done for
 		//syscs_util.SYSCS_UPDATE_STATISTICS procedure
 		checkInternalFeature("UPDATE STATISTICS");
+		changeType[0] = DDLStatementNode.UPDATE_STATISTICS;
 		return (StatementNode) nodeFactory.getNode(
 							C_NodeTypes.ALTER_TABLE_NODE,
 							tableName,
-							Boolean.TRUE,
+							changeType,
+							Boolean.FALSE,
+							indexName,
 							null,
 							getContextManager());
 	}
 |
-//This means update the statistics of the index name supplied for the table
-	<UPDATE> <STATISTICS>
+	<STATISTICS> <DROP>
         (
 		indexName =  identifier(Limits.MAX_IDENTIFIER_LENGTH, true)
         )
 	{
-		checkVersion( DataDictionary.DD_VERSION_DERBY_10_5, "SYSCS_UPDATE_STATISTICS");
+		checkVersion( DataDictionary.DD_VERSION_DERBY_10_9, "SYSCS_UPDATE_STATISTICS");
 		//This will make sure that this ALTER TABLE...syntax can't be called directly.
 		//This sql can only be generated internally (right now it is done for
 		//syscs_util.SYSCS_UPDATE_STATISTICS procedure
-		checkInternalFeature("UPDATE STATISTICS");
+		checkInternalFeature("DROP STATISTICS");
+		changeType[0] = DDLStatementNode.DROP_STATISTICS;
 		return (StatementNode) nodeFactory.getNode(
 							C_NodeTypes.ALTER_TABLE_NODE,
 							tableName,
+							changeType,
 							Boolean.FALSE,
 							indexName,
+							null,
 							getContextManager());
 	}
 |
+	<DROP> 
+        (
+		sn = dropColumnDefinition(tableName, tableElementList, changeType, behavior)
+		|
+		sn = dropTableConstraintDefinitionCore(tableName, tableElementList, changeType, behavior)
+        )
+	    {
+		     return sn;
+        }
+|
 	lockGranularity = alterTableAction( tableElementList, changeType, behavior )
 	{
 		return (StatementNode) nodeFactory.getNode(
 							C_NodeTypes.ALTER_TABLE_NODE,
 							tableName,
+							changeType,
 							tableElementList,
 							new Character(lockGranularity),
-							changeType,
 							behavior,
 							getContextManager());
 	}
 }
 
+/*
+ * Called for ALTER TABLE ALL DROP STATISTICS. This is an internal
+ *  syntax and can't be invoked by a user directly. DERBY-4115. This
+ *  will drop all the statistics for the given table name
+ *
+ * By the time we get here, we've parsed
+ *    ALTER TABLE tablename ALL 
+ * and here we parse DROP STATISTICS clause
+ */
+StatementNode
+dropStatistics(TableName tableName) throws StandardException :
+{
+}
+{
+	<DROP> <STATISTICS>
+	{
+		checkVersion( DataDictionary.DD_VERSION_DERBY_10_9, "SYSCS_DROP_STATISTICS");
+		//This will make sure that this ALTER TABLE...syntax can't be called directly.
+		//This sql can only be generated internally (right now it is done for
+		//syscs_util.SYSCS_DROP_STATISTICS procedure
+		checkInternalFeature("DROP STATISTICS");
+		int[] changeType = new int[1];
+		changeType[0] = DDLStatementNode.DROP_STATISTICS;
+		return (StatementNode) nodeFactory.getNode(
+							C_NodeTypes.ALTER_TABLE_NODE,
+							tableName,
+							changeType,
+							Boolean.TRUE,
+							null,
+							null,
+							getContextManager());
+	}
+}
+
+/*
+ * Called for ALTER TABLE ALL UPDATE STATISTICS. This is an internal
+ *  syntax and can't be invoked by a user directly. DERBY-269. This
+ *  will update all the statistics for the given table name
+ *
+ * By the time we get here, we've parsed
+ *    ALTER TABLE tablename ALL 
+ * and here we parse UPDATE STATISTICS clause
+ */
+StatementNode
+updateStatistics(TableName tableName) throws StandardException :
+{
+}
+{
+	<UPDATE> <STATISTICS>
+	{
+		checkVersion( DataDictionary.DD_VERSION_DERBY_10_5, "SYSCS_UPDATE_STATISTICS");
+		//This will make sure that this ALTER TABLE...syntax can't be called directly.
+		//This sql can only be generated internally (right now it is done for
+		//syscs_util.SYSCS_UPDATE_STATISTICS procedure
+		checkInternalFeature("UPDATE STATISTICS");
+		int[] changeType = new int[1];
+		changeType[0] = DDLStatementNode.UPDATE_STATISTICS;
+		return (StatementNode) nodeFactory.getNode(
+							C_NodeTypes.ALTER_TABLE_NODE,
+							tableName,
+							changeType,
+							Boolean.TRUE,
+							null,
+							null,
+							getContextManager());
+	}
+}
+
 StatementNode
 inplaceCompress(TableName tableName) throws StandardException :
 {
@@ -13278,18 +13375,6 @@ alterTableAction(TableElementList tableE
 		return lockGranularity;
 	}
 |
-	<DROP>
-	(
-		tableElement = dropColumnDefinition(behavior)
-		|
-		tableElement = dropTableConstraintDefinition()
-	)
-	{
-		changeType[0] = DDLStatementNode.DROP_TYPE;
-		tableElementList.addTableElement(tableElement);
-		return lockGranularity;
-	}
-|
 	lockGranularity = DB2lockGranularityClause()
 	{
 		changeType[0] = DDLStatementNode.LOCKING_TYPE;
@@ -13302,8 +13387,8 @@ alterTableAction(TableElementList tableE
  *
  *    ALTER TABLE tablename DROP [ COLUMN ] columnname [ CASCADE | RESTRICT ]
  */
-TableElementNode
-dropColumnDefinition(int []behavior) throws StandardException :
+StatementNode
+dropColumnDefinition(TableName tableName, TableElementList tableElementList, int[] changeType, int[] behavior) throws StandardException :
 {
 	String columnName;
 	TableElementNode tableElement;
@@ -13312,13 +13397,24 @@ dropColumnDefinition(int []behavior) thr
 	[ <COLUMN> ] columnName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true)
 				 dropColumnReferentialAction(behavior)
 	{
-		return (TableElementNode) nodeFactory.getNode(
+		tableElement = (TableElementNode) nodeFactory.getNode(
 						C_NodeTypes.DROP_COLUMN_NODE,
 						columnName, null,
 						null, null,
 						getContextManager());
+		changeType[0] = DDLStatementNode.DROP_TYPE;
+		tableElementList.addTableElement(tableElement);
+		return (StatementNode) nodeFactory.getNode(
+							C_NodeTypes.ALTER_TABLE_NODE,
+							tableName,
+							changeType,
+							tableElementList,
+							new Character('\0'),
+							behavior,
+							getContextManager());
 	}
 }
+
 void
 dropColumnReferentialAction(int []behavior) :
 {
@@ -13348,7 +13444,7 @@ addColumnDefinition(TableElementList tab
 /*
  * Various variants of the ALTER TABLE ALTER COLUMN statement.
  *
- * By the type we get here, we've parsed
+ * By the time we get here, we've parsed
  *    ALTER TABLE tablename ALTER [COLUMN] columnname
  * and here we parse the remainder of the ALTER COLUMN clause, one of:
  *		SET DATA TYPE data_type
@@ -13450,6 +13546,26 @@ columnAlterClause(String columnName) thr
 	}
 }
 
+StatementNode
+dropTableConstraintDefinitionCore(TableName tableName, TableElementList tableElementList, int[] changeType, int[] behavior) throws StandardException :
+{
+	TableElementNode tableElement;
+}
+{
+		tableElement = dropTableConstraintDefinition()
+		{
+		changeType[0] = DDLStatementNode.DROP_TYPE;
+		tableElementList.addTableElement(tableElement);
+		return (StatementNode) nodeFactory.getNode(
+							C_NodeTypes.ALTER_TABLE_NODE,
+							tableName,
+							changeType,
+							tableElementList,
+							new Character('\0'),
+							behavior,
+							getContextManager());
+		}
+}
 
 TableElementNode
 dropTableConstraintDefinition() throws StandardException :

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=1338017&r1=1338016&r2=1338017&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java Mon May 14 01:02:56 2012
@@ -131,11 +131,23 @@ class AlterTableConstantAction extends D
 	 */
     private	    boolean					    updateStatisticsAll;
 	/**
-	 * If statistic is getting updated for just one index, then 
-	 * indexNameForUpdateStatistics will tell the name of the specific index 
-	 * whose statistics need to be updated.
+	 * dropStatistics will indicate that we are here for dropping the
+	 * statistics. It could be statistics of just one index or all the
+	 * indexes on a given table. 
 	 */
-    private	    String						indexNameForUpdateStatistics;
+    private	    boolean					    dropStatistics;
+	/**
+	 * The flag dropStatisticsAll will tell if we are going to drop the 
+	 * statistics of all indexes or just one index on a table. 
+	 */
+    private	    boolean					    dropStatisticsAll;
+	/**
+	 * If statistic is getting updated/dropped for just one index, then 
+	 * indexNameForStatistics will tell the name of the specific index 
+	 * whose statistics need to be updated/dropped.
+	 */
+    private	    String						indexNameForStatistics;
+
     
     // Alter table compress and Drop column
     private     boolean					    doneScan;
@@ -196,8 +208,14 @@ class AlterTableConstantAction extends D
 	 *  @param updateStatisticsAll	TRUE means we are here to update statistics
 	 *  	of all the indexes. False means we are here to update statistics of
 	 *  	only one index.
-	 *  @param indexNameForUpdateStatistics	Will name the index whose statistics
-	 *  	will be updated
+	 *  @param dropStatistics		TRUE means we are here to drop statistics
+	 *  @param dropStatisticsAll	TRUE means we are here to drop statistics
+	 *  	of all the indexes. False means we are here to drop statistics of
+	 *  	only one index.
+	 *  @param indexNameForStatistics	Will name the index whose statistics
+	 *  	will be updated/dropped. This param is looked at only if 
+	 *  	updateStatisticsAll/dropStatisticsAll is set to false and
+	 *  	updateStatistics/dropStatistics is set to true.
 	 */
 	AlterTableConstantAction(
     SchemaDescriptor            sd,
@@ -217,7 +235,9 @@ class AlterTableConstantAction extends D
     boolean                     truncateEndOfTable,
     boolean                     updateStatistics,
     boolean                     updateStatisticsAll,
-    String	                    indexNameForUpdateStatistics)
+    boolean                     dropStatistics,
+    boolean                     dropStatisticsAll,
+    String                      indexNameForStatistics)
 	{
 		super(tableId);
 		this.sd                     = sd;
@@ -236,7 +256,9 @@ class AlterTableConstantAction extends D
 		this.truncateEndOfTable     = truncateEndOfTable;
 		this.updateStatistics     	= updateStatistics;
 		this.updateStatisticsAll    = updateStatisticsAll;
-		this.indexNameForUpdateStatistics = indexNameForUpdateStatistics;
+		this.dropStatistics     	= dropStatistics;
+		this.dropStatisticsAll    = dropStatisticsAll;
+		this.indexNameForStatistics = indexNameForStatistics;
 
 		if (SanityManager.DEBUG)
 		{
@@ -330,6 +352,11 @@ class AlterTableConstantAction extends D
             updateStatistics();
             return;
 		}
+
+        if (dropStatistics) {
+            dropStatistics();
+            return;
+		}
 		/*
 		** Inform the data dictionary that we are about to write to it.
 		** There are several calls to data dictionary "get" methods here
@@ -650,6 +677,29 @@ class AlterTableConstantAction extends D
     }
 
 	/**
+	 * Drop statistics of either all the indexes on the table or only one
+	 * specific index depending on what user has requested.
+	 * 
+	 * @throws StandardException
+	 */
+    private void dropStatistics()
+            throws StandardException {
+        td = dd.getTableDescriptor(tableId);
+
+        dd.startWriting(lcc);
+        dm.invalidateFor(td, DependencyManager.UPDATE_STATISTICS, lcc);
+
+        if (dropStatisticsAll) {
+            dd.dropStatisticsDescriptors(td.getUUID(), null, tc);
+        } else {
+            ConglomerateDescriptor cd = 
+                dd.getConglomerateDescriptor(
+                    indexNameForStatistics, sd, false);
+            dd.dropStatisticsDescriptors(td.getUUID(), cd.getUUID(), tc);
+        }
+    }
+
+	/**
 	 * Update statistics of either all the indexes on the table or only one
 	 * specific index depending on what user has requested.
 	 * 
@@ -665,7 +715,7 @@ class AlterTableConstantAction extends D
         } else {
             cds = new ConglomerateDescriptor[1];
             cds[0] = dd.getConglomerateDescriptor(
-                    indexNameForUpdateStatistics, sd, false);
+                    indexNameForStatistics, sd, false);
         }
         dd.getIndexStatsRefresher(false).runExplicitly(
                                                 lcc, td, cds, "ALTER TABLE");

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java?rev=1338017&r1=1338016&r2=1338017&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java Mon May 14 01:02:56 2012
@@ -137,9 +137,15 @@ public class GenericConstantActionFactor
 	 *  @param updateStatisticsAll	TRUE means we are here to update statistics
 	 *  	of all the indexes. False means we are here to update statistics of
 	 *  	only one index.
-	 *  @param indexNameForUpdateStatistics	Will name the index whose statistics
-	 *  	will be updated. This param is looked at only if updateStatisticsAll
-	 *  	is set to false.
+	 *  @param dropStatistics		TRUE means we are here to drop statistics
+	 *  @param dropStatisticsAll	TRUE means we are here to drop statistics
+	 *  	of all the indexes. False means we are here to drop statistics of
+	 *  	only one index.
+	 *  @param indexNameForStatistics	Will name the index whose statistics
+	 *  	will be updated/dropped. This param is looked at only if 
+	 *  	updateStatisticsAll/dropStatisticsAll is set to false and
+	 *  	updateStatistics/dropStatistics is set to true.
+	 *  .
 	 */
 	public	ConstantAction	getAlterTableConstantAction
 	(
@@ -161,7 +167,9 @@ public class GenericConstantActionFactor
 		boolean						truncateEndOfTable,
 		boolean						updateStatistics,
 		boolean						updateStatisticsAll,
-		String						indexNameForUpdateStatistics
+		boolean						dropStatistics,
+		boolean						dropStatisticsAll,
+		String						indexNameForStatistics
     )
 	{
 		return new	AlterTableConstantAction( sd, tableName, tableId, tableConglomerateId, 
@@ -171,7 +179,9 @@ public class GenericConstantActionFactor
 											  purge, defragment, truncateEndOfTable,
 											  updateStatistics, 
 											  updateStatisticsAll,
-											  indexNameForUpdateStatistics);
+											  dropStatistics, 
+											  dropStatisticsAll,
+											  indexNameForStatistics);
 	}
 
 	/**

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java?rev=1338017&r1=1338016&r2=1338017&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java Mon May 14 01:02:56 2012
@@ -7235,6 +7235,7 @@ public final class GrantRevokeDDLTest ex
             {"PUBLIC", "TEST_DBO", "N"},
             {"PUBLIC", "TEST_DBO", "N"},
             {"PUBLIC", "TEST_DBO", "N"},
+            {"PUBLIC", "TEST_DBO", "N"},
         };
         
         JDBC.assertFullResultSet(rs, expRS, true);
@@ -7282,6 +7283,7 @@ public final class GrantRevokeDDLTest ex
             {"PUBLIC", "TEST_DBO", "N"},
             {"PUBLIC", "TEST_DBO", "N"},
             {"PUBLIC", "TEST_DBO", "N"},
+            {"PUBLIC", "TEST_DBO", "N"},
             {"USER2", "USER1", "N"}
         };
         

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java?rev=1338017&r1=1338016&r2=1338017&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java Mon May 14 01:02:56 2012
@@ -599,9 +599,9 @@ public class RolesTest extends BaseJDBCT
 
         assertSysColPermsRowCount(0, 2, 2);
 
-        assertSysRoutinePermsRowCount(8, // 8 pre-existing grants to PUBLIC
-                                      9,
-                                      9);
+        assertSysRoutinePermsRowCount(9, // 9 pre-existing grants to PUBLIC
+                                      10,
+                                      10);
 
         /*
          * DROP ROLE
@@ -629,12 +629,12 @@ public class RolesTest extends BaseJDBCT
                                   // to admin is de facto to a user
                                   // named admin:
                                   2);
-        assertSysRoutinePermsRowCount(8, 8,
+        assertSysRoutinePermsRowCount(9, 9,
                                       //  nonDbo run: role admin
                                       // has been dropped, so this
                                       // run's grant to admin is de
                                       // facto to a user named admin:
-                                      9);
+                                      10);
 
         doStmt("drop role \"NONE\"",
                sqlAuthorizationRequired, null , roleDboOnly);
@@ -656,7 +656,7 @@ public class RolesTest extends BaseJDBCT
                                     1,
                                     0);
         assertSysColPermsRowCount(0,0,0);
-        assertSysRoutinePermsRowCount(8,8,8);
+        assertSysRoutinePermsRowCount(9,9,9);
 
         // roles foo and bar survive to nonDbo run and beyond:
         assertSysRolesRowCount(0, 5, 5);

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java?rev=1338017&r1=1338016&r2=1338017&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/UpdateStatisticsTest.java Mon May 14 01:02:56 2012
@@ -39,6 +39,8 @@ import org.apache.derbyTesting.junit.SQL
 /**
  * Tests for updating the statistics of one index or all the indexes on a
  * table DERBY-269, DERBY-3788.
+ * Tests for dropping the statistics of one index or all the indexes on a
+ * table DERBY-4115.
  */
 public class UpdateStatisticsTest extends BaseJDBCTestCase {
 
@@ -61,23 +63,73 @@ public class UpdateStatisticsTest extend
     }
 
     /**
+     * Test that parser can work with column and index named STATISTICS and
+     *  does not get confused with non-reserved keyword STATISTICS used by
+     *  UPDATE and DROP STATISTICS syntax generated internally for
+     *  SYSCS_DROP_STATISTICS and SYSCS_UPDATE_STATISTICS
+     */
+    public void testIndexAndColumnNamedStatistics() throws SQLException {
+        // Helper object to obtain information about index statistics.
+        IndexStatsUtil stats = new IndexStatsUtil(openDefaultConnection());
+        Statement s = createStatement();
+
+        //Notice the name of one of the columns is STATISTICS
+        s.executeUpdate("CREATE TABLE t1 (c11 int, statistics int not null)");
+        //Notice that the name of the index is STATISTICS which is same as 
+        // one of the column names
+        s.executeUpdate("CREATE INDEX statistIcs ON t1(c11)");
+        s.executeUpdate("INSERT INTO t1 VALUES(1,1)");
+        stats.assertNoStats();
+        //Drop the column named STATISTICS and make sure parser doesn't
+        // throw an error
+        s.executeUpdate("ALTER TABLE t1 DROP statistics");
+        //Should still be able to call update/drop statistics on index 
+        // STATISTICS
+        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','STATISTICS')");
+        stats.assertStats(1);
+        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1','STATISTICS')");
+        stats.assertNoStats();
+        //Add the column named STATISTICS back
+        s.executeUpdate("ALTER TABLE t1 ADD COLUMN statistics int");
+        stats.assertNoStats();
+        //Update or drop statistics for index named STATISTICS. Note that there
+        // is also a column named STATISTICS in the table
+        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1','STATISTICS')");
+        stats.assertNoStats();
+        s.executeUpdate("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','STATISTICS')");
+        stats.assertStats(1);
+        s.executeUpdate("DROP TABLE t1");
+    }
+
+    /**
      * Test for update statistics
      */
-    public void testUpdateStatistics() throws SQLException {
+    public void testUpdateAndDropStatistics() throws SQLException {
         // Helper object to obtain information about index statistics.
         IndexStatsUtil stats = new IndexStatsUtil(openDefaultConnection());
         Statement s = createStatement();
-        //following should fail because table APP.T1 does not exist
+
+        //Calls to update and drop statistics below should fail because 
+        // table APP.T1 does not exist
+        assertStatementError("42Y55", s, 
+            "CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1',null)");
         assertStatementError("42Y55", s, 
             "CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1',null)");
+
         s.executeUpdate("CREATE TABLE t1 (c11 int, c12 varchar(128))");
         //following will pass now because we have created APP.T1
+        s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1',null)");
         s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1',null)");
+        
         //following should fail because index I1 does not exist on table APP.T1
         assertStatementError("42X65", s, 
-            "CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','I1')");
+                "CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1','I1')");
+        assertStatementError("42X65", s, 
+                "CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','I1')");
+        
         s.executeUpdate("CREATE INDEX i1 on t1(c12)");
         //following will pass now because we have created index I1 on APP.T1
+        s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1','I1')");
         s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','I1')");
 
         //The following set of subtest will ensure that when an index is
@@ -98,29 +150,59 @@ public class UpdateStatisticsTest extend
         s.executeUpdate("INSERT INTO T1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d')");
         s.executeUpdate("CREATE INDEX i2 ON t1(c11)");
         stats.assertStats(1);
+        //Drop the statistics on index I2 and then add it back by calling 
+        // update statistics
+        s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1','I2')");
+        //Since we dropped the only statistics that existed for table T1, there
+        // will no stats found at this point
+        stats.assertNoStats();
+        s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','I2')");
+        //The statistics for index I2 has been added back
+        stats.assertStats(1);
         //Now update the statistics for the old index I1 using the new stored
         //procedure. Doing this should add a row for it in sysstatistics table
         s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','I1')");
         stats.assertStats(2);
+        //Drop the statistics on index I1 and then add it back by calling 
+        // update statistics
+        s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1','I1')");
+        stats.assertStats(1);
+        s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','I1')");
+        stats.assertStats(2);
+        //Drop all the statistics on table T1 and then recreate all the 
+        // statisitcs back again
+        s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T1',null)");
+        stats.assertNoStats();
+        s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1',null)");
+        stats.assertStats(2);
+        //Dropping the index should get rid of it's statistics
+        s.executeUpdate("DROP INDEX I1");
+        stats.assertStats(1);
 
-        //calls to system procedure for update statistics is internally
-        //converted into ALTER TABLE ... sql but that generated sql format
-        //is not available to end user to issue directly. Write a test case
-        //for that sql syntax
+        //calls to system procedure for update and drop statistics are
+        // internally converted into ALTER TABLE ... sql but that generated
+        // sql format is not available to end user to issue directly. Write a 
+        // test case for these internal sql syntaxes
         assertStatementError("42X01", s, 
             "ALTER TABLE APP.T1 ALL UPDATE STATISTICS");
         assertStatementError("42X01", s, 
             "ALTER TABLE APP.T1 UPDATE STATISTICS I1");
+        assertStatementError("42X01", s, 
+                "ALTER TABLE APP.T1 ALL DROP STATISTICS");
+        assertStatementError("42X01", s, 
+                "ALTER TABLE APP.T1 STATISTICS DROP I1");
         //cleanup
         s.executeUpdate("DROP TABLE t1");
 
-        //Try update statistics on global temporary table
+        //Try update and drop statistics on global temporary table
 		s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
 		s.executeUpdate("insert into session.t1 values(11, 1)");
-        //following should fail because update statistics can't be issued on
-		//global temporary tables
+        //following should fail because update/drop statistics can't be issued
+		// on global temporary tables
+        assertStatementError("42995", s, 
+                "CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('SESSION','T1',null)");
         assertStatementError("42995", s, 
-            "CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('SESSION','T1',null)");
+                "CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('SESSION','T1',null)");
         
         //Following test will show that updating the statistics will make a
         //query pickup better index compare to prior to statistics availability.
@@ -175,10 +257,24 @@ public class UpdateStatisticsTest extend
 
         //Rerunning the query "SELECT * FROM t2 WHERE c21=? AND c22=?" and
         //looking at it's plan will show that this time it picked up more
-        //efficient index which is T2I2. 
+        //efficient index which is T2I2.
         JDBC.assertDrainResults(ps.executeQuery());
 		rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
 		assertTrue(rtsp.usedSpecificIndexForIndexScan("T2","T2I2"));
+
+		//Drop statistics for T2I2 and we should see that we go back to using
+		// T2I1 rather than T2I2
+		s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T2','T2I2')");
+        stats.assertIndexStats("T2I2", 0);
+
+        //Rerunning the query "SELECT * FROM t2 WHERE c21=? AND c22=?" and
+        // looking at it's plan will show that this time it picked up T2I1
+        // rather than more efficient index T2I2  because no stats exists
+        // for T2I2
+        JDBC.assertDrainResults(ps.executeQuery());
+		rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
+		assertTrue(rtsp.usedSpecificIndexForIndexScan("T2","T2I1"));
+
         //cleanup
         s.executeUpdate("DROP TABLE t2");
         //End of test case for better index selection after statistics
@@ -360,14 +456,14 @@ public class UpdateStatisticsTest extend
                 "ADD CONSTRAINT TEST_TAB_2_PK_1 "+
         		"PRIMARY KEY (c21)");
         stats.assertTableStats("TEST_TAB_2",1);
-        //Add a foreign key constraint and now we should find 2 rows of 
-        // statistics for TEST_TAB_2 - 1 for primary key and other for
+        //DERBY-5702 Add a foreign key constraint and now we should find 2 rows
+        // of statistics for TEST_TAB_2 - 1 for primary key and other for
         // foreign key constraint
         s.executeUpdate("ALTER TABLE TEST_TAB_2 "+
                 "ADD CONSTRAINT TEST_TAB_2_FK_1 "+
         		"FOREIGN KEY(c21) REFERENCES TEST_TAB_1(c11)");
-        //Like primary key earlier, adding foreign key constraint didn't
-        // automatically add a statistics row for it. Have to run update
+        //DERBY-5702 Like primary key earlier, adding foreign key constraint
+        // didn't automatically add a statistics row for it. Have to run update
         // statistics manually to get a row added for it's stat
         stats.assertTableStats("TEST_TAB_2",1);
         s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)");
@@ -382,6 +478,10 @@ public class UpdateStatisticsTest extend
         stats.assertTableStats("TEST_TAB_2",1);
         s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)");
         stats.assertTableStats("TEST_TAB_2",1);
+        s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)");
+        //After DERBY-4115 is implemented, we will see no statistics 
+        // for TEST_TAB_2 after calling SYSCS_DROP_STATISTICS on it.
+        stats.assertNoStatsTable("TEST_TAB_2");
         s.execute("drop table TEST_TAB_2");
         s.execute("drop table TEST_TAB_1");
         stats.release();

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java?rev=1338017&r1=1338016&r2=1338017&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/BasicSetup.java Mon May 14 01:02:56 2012
@@ -32,6 +32,8 @@ import org.apache.derbyTesting.junit.JDB
 import org.apache.derbyTesting.junit.TestConfiguration;
 import org.apache.derbyTesting.junit.XML;
 
+import org.apache.derbyTesting.junit.IndexStatsUtil;
+
 import junit.framework.Assert;
 import junit.framework.Test;
 import junit.framework.TestSuite;
@@ -704,6 +706,117 @@ public class BasicSetup extends UpgradeC
         //Clean data for next test
     	s.executeUpdate("delete from BKUP1_5044_5120");
 	}
+
+    /**
+     * DERBY-4115(Provide a way to drop statistics information) and 
+     *  DERBY-5681(When a foreign key constraint on a table is dropped, 
+     *  the associated statistics row for the conglomerate). Test that
+     *  the statisitcs row left behind by DERBY-5681 can be dropped
+     *  using DERBY-4115
+     *  
+     *  DERBY-5702(Creating a foreign key constraint does not automatically
+     *   create a statistics row if foreign key constraint will share a
+     *   backing index created for a primay key) is causing a problem for
+     *   us to test the hanging statistics row with 10.4 and prior releases.
+     *   Following test relies on having hanging statistics rows which should
+     *   have been dropped when the constraint owing it was dropped. The test
+     *   then goes ahead and uses the new drop statisitcs procedure to drop
+     *   the hanging statistics rows. But because of DERBY-5702, when a
+     *   constraint is added which will reuse an existing backing index,
+     *   no statistics row is created for that constraint unless a user were
+     *   to say use an update statistics stored procedure to create the
+     *   statistics for that constraint. And later when that constraint is
+     *   dropped, we will find that because of DERBY-5681, the statistics
+     *   row never gets dropped. But update statistics stored procedure was
+     *   not introduced up until 10.5 and because of that, we can't really
+     *   test for hanging index created through constraints sharing the same
+     *   backing index prior to 10.5
+     *  
+     */
+    public void testDERBY4115AndDERBY5681ForStatistics() throws Exception {
+    	// Update statisitcs procedure SYSCS_UPDATE_STATISTICS is not available
+    	//  prior to 10.5 and hence we can't cause the hanging statistics to 
+    	//  appear in order to test the drop statistics after hard upgrade
+    	if (!oldAtLeast(10, 5)) return;
+
+    	// Helper object to obtain information about index statistics.
+        IndexStatsUtil stats = new IndexStatsUtil(openDefaultConnection());
+        Statement s = createStatement();
+    	
+        switch (getPhase())
+        {
+        case PH_CREATE:
+            s.executeUpdate("CREATE TABLE TEST_TAB_1 (c11 int not null,"+
+                    "c12 int not null, c13 int)");
+            s.executeUpdate("INSERT INTO TEST_TAB_1 VALUES(1,1,1),(2,2,2)");
+            s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
+                    "ADD CONSTRAINT TEST_TAB_1_PK_1 "+
+            		"PRIMARY KEY (c11)");
+            //The statistics for primary key constraint has been added
+            stats.assertTableStats("TEST_TAB_1",1);
+            
+            s.executeUpdate("CREATE TABLE TEST_TAB_2 (c21 int not null)");
+            s.executeUpdate("INSERT INTO TEST_TAB_2 VALUES(1),(2)");
+            s.executeUpdate("ALTER TABLE TEST_TAB_2 "+
+                    "ADD CONSTRAINT TEST_TAB_2_PK_1 "+
+            		"PRIMARY KEY (c21)");
+            stats.assertTableStats("TEST_TAB_2",1);
+            //DERBY-5702 Add a foreign key constraint and now we should find 2 rows
+            // of statistics for TEST_TAB_2 - 1 for primary key and other for
+            // foreign key constraint
+            s.executeUpdate("ALTER TABLE TEST_TAB_2 "+
+                    "ADD CONSTRAINT TEST_TAB_2_FK_1 "+
+            		"FOREIGN KEY(c21) REFERENCES TEST_TAB_1(c11)");
+            //DERBY-5702 Like primary key earlier, adding foreign key constraint
+            // didn't automatically add a statistics row for it. Have to run update
+            // statistics manually to get a row added for it's stat
+            stats.assertTableStats("TEST_TAB_2",1);
+            //Need to do a compress table to create the statistics for foreign
+            // key constraint. Update statisitcs procedure is only available
+            // in 10.5 and upwards and hence can't use that procedure here
+            // since we are testing older releases too.
+            s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)");
+            //s.execute("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP','TEST_TAB_2',1)");
+            stats.assertTableStats("TEST_TAB_2",2);
+            s.executeUpdate("ALTER TABLE TEST_TAB_2 "+
+                    "DROP CONSTRAINT TEST_TAB_2_FK_1");
+            //Dropping the foreign key constraint does not remove it's 
+            // statistics row because of DERBY-5681. This jira is fixed
+            // in 10.9 and higher and there we will not see the behavior
+        	if (oldAtLeast(10, 9))
+        		stats.assertTableStats("TEST_TAB_2",1);
+        	else
+        		stats.assertTableStats("TEST_TAB_2",2);
+            assertStatementError("42Y03", s,
+               		"CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)");
+            break;
+
+        case PH_SOFT_UPGRADE:
+        case PH_POST_SOFT_UPGRADE:
+            assertStatementError("42Y03", s,
+               		"CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)");
+            break;
+
+        case PH_HARD_UPGRADE:
+        	if (oldAtLeast(10, 9))
+        		stats.assertTableStats("TEST_TAB_2",1);
+        	else
+        		stats.assertTableStats("TEST_TAB_2",2);
+            s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)");
+            stats.assertNoStatsTable("TEST_TAB_2");
+            s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)");
+    		stats.assertTableStats("TEST_TAB_2",1);
+            break;
+
+        case PH_POST_HARD_UPGRADE:
+        	//Make sure that the new procedure is still available
+            s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)");
+            s.executeUpdate("DROP TABLE TEST_TAB_1");
+            s.executeUpdate("DROP TABLE TEST_TAB_2");
+            break;
+        }
+
+	}
     
     /**
      * DERBY-5044(ALTER TABLE DROP COLUMN will not detect triggers defined 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_2.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_2.java?rev=1338017&r1=1338016&r2=1338017&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_2.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_2.java Mon May 14 01:02:56 2012
@@ -332,6 +332,7 @@ public class Changes10_2 extends Upgrade
             
             JDBC.assertFullResultSet(rs, new String[][]
                     {{"SYSCS_COMPRESS_TABLE"},
+                    {"SYSCS_DROP_STATISTICS"},
                     {"SYSCS_GET_RUNTIMESTATISTICS"},
                     {"SYSCS_INPLACE_COMPRESS_TABLE"},
                     {"SYSCS_MODIFY_PASSWORD"},

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java?rev=1338017&r1=1338016&r2=1338017&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_9.java Mon May 14 01:02:56 2012
@@ -111,6 +111,38 @@ public class Changes10_9 extends Upgrade
     ///////////////////////////////////////////////////////////////////////////////////
 
     /**
+     * Make sure that the drop statistics procedure only appears after 
+     * hard-upgrade.
+     */
+    public  void    testDropStatisticsProc()  throws Exception
+    {
+        Statement s = createStatement();
+
+        switch ( getPhase() )
+        {
+        case PH_CREATE: // create with old version
+            s.execute("CREATE TABLE dropStatsT1 (c11 int, c12 int) ");
+            vetProcs(s, "call syscs_util.syscs_drop_statistics( 'APP', 'DROPSTATST1', null )", false);
+            break;
+            
+        case PH_SOFT_UPGRADE: // boot with new version and soft-upgrade
+            vetProcs(s, "call syscs_util.syscs_drop_statistics( 'APP', 'DROPSTATST1', null )", false);
+            break;
+            
+        case PH_POST_SOFT_UPGRADE: // soft-downgrade: boot with old version after soft-upgrade
+            vetProcs(s, "call syscs_util.syscs_drop_statistics( 'APP', 'DROPSTATST1', null )", false);
+            break;
+
+        case PH_HARD_UPGRADE: // boot with new version and hard-upgrade
+            vetProcs(s, "call syscs_util.syscs_drop_statistics( 'APP', 'DROPSTATST1', null )", true);
+            s.execute("DROP TABLE dropStatsT1");
+            break;
+        }
+        
+        s.close();
+    	
+    }
+    /**
      * Make sure that the catalogs and procedures for NATIVE authentication
      * only appear after hard-upgrade.
      */
@@ -143,24 +175,10 @@ public class Changes10_9 extends Upgrade
         
         s.close();
     }
-    private void    vetSYSUSERS( Statement s, boolean shouldExist ) throws Exception
-    {
-        ResultSet   rs = s.executeQuery( "select count(*) from sys.systables where tablename = 'SYSUSERS'" );
-        rs.next();
-
-        int expectedValue = shouldExist ? 1 : 0;
-
-        assertEquals( expectedValue, rs.getInt( 1 ) );
-
-        rs.close();
-    }
-    private void    vetNativeProcs( Statement s, boolean shouldExist ) throws Exception
+    private void    vetProcs( Statement s, String procCall, boolean shouldExist ) throws Exception
     {
-        // make sure that an authentication algorithm has been set
-        String  defaultDigestAlgorithm = pushAuthenticationAlgorithm( s );
-
         try {
-            s.execute( "call syscs_util.syscs_create_user( 'FRED', 'fredpassword' )" );
+            s.execute( procCall );
             
             if ( !shouldExist )
             {
@@ -177,7 +195,23 @@ public class Changes10_9 extends Upgrade
                 assertSQLState( "42Y03", se );
             }
         }
+    }
+    private void    vetSYSUSERS( Statement s, boolean shouldExist ) throws Exception
+    {
+        ResultSet   rs = s.executeQuery( "select count(*) from sys.systables where tablename = 'SYSUSERS'" );
+        rs.next();
+
+        int expectedValue = shouldExist ? 1 : 0;
+
+        assertEquals( expectedValue, rs.getInt( 1 ) );
 
+        rs.close();
+    }
+    private void    vetNativeProcs( Statement s, boolean shouldExist ) throws Exception
+    {
+        // make sure that an authentication algorithm has been set
+        String  defaultDigestAlgorithm = pushAuthenticationAlgorithm( s );
+        vetProcs(s, "call syscs_util.syscs_create_user( 'FRED', 'fredpassword' )", shouldExist);
         // restore the authentication algorithm if we changed it
         popAuthenticationAlgorithm( s, defaultDigestAlgorithm );
     }



Mime
View raw message