db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r768597 [4/4] - in /db/derby/code/trunk/java: engine/org/apache/derby/ engine/org/apache/derby/catalog/ engine/org/apache/derby/iapi/sql/conn/ engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/iapi/sql/execute/ engine/org...
Date Sat, 25 Apr 2009 20:21:04 GMT
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java?rev=768597&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java Sat Apr 25 20:21:03 2009
@@ -0,0 +1,1647 @@
+/*
+
+   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.XplainStatisticsTest
+
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.PreparedStatement;
+import java.sql.Timestamp;
+
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.Date;
+import java.util.HashSet;
+import java.util.Iterator;
+import java.util.List;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derby.shared.common.sanity.SanityManager;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
+import org.apache.derbyTesting.junit.SQLUtilities;
+
+/**
+ * This suite contains a set of tests for the new XPLAIN style of
+ * runtime statistics capturing which was added as part of DERBY-2487.
+ *
+ * There are a large number of tests which follow the pattern of:
+ * - enable capture
+ * - execute a single statement
+ * - stop capture
+ * - verify that the right stuff was captured
+ *
+ * FIXME -- some general to-do items that I don't want to forget:
+ * - should resultSetNumber be its own column in sysxplain_resultsets?
+ * - need tests of xplain-only mode
+ * - need a test of external sorting/merging
+ * - need to cross-check the result set types, and verify that they're
+ *   all tested at least once
+ */
+public class XplainStatisticsTest extends BaseJDBCTestCase {
+
+	public XplainStatisticsTest(String name) {
+		super(name);
+	}
+	
+	public static Test suite() {
+            timeSuiteStarted = (new Date()).getTime();
+            TestSuite allTests = new TestSuite(XplainStatisticsTest.class,
+                                    "XplainStatisticsTest");
+		return new CleanDatabaseTestSetup(allTests) {
+			protected void decorateSQL(Statement s)
+				throws SQLException
+			{
+				createSchemaObjects(s);
+			}
+		};
+	}
+        private static long timeSuiteStarted;
+
+	/**
+	 * Creates a variety of tables used by the various tests.
+         * The tests use the same basic schema as the 'toursdb' sample
+         * database, with a much smaller set of data. We really only
+         * populate the COUNTRIES table; the other tables have few or no rows.
+         *
+	 * @throws SQLException
+	 */
+	private static void createSchemaObjects(Statement st)
+		throws SQLException
+	{
+		st.executeUpdate(
+"CREATE TABLE AIRLINES (" +
+"      AIRLINE CHAR(2) NOT NULL ," +
+"      AIRLINE_FULL VARCHAR(24)," +
+"      BASIC_RATE DOUBLE PRECISION," +
+"      DISTANCE_DISCOUNT DOUBLE PRECISION," +
+"      BUSINESS_LEVEL_FACTOR DOUBLE PRECISION," +
+"      FIRSTCLASS_LEVEL_FACTOR DOUBLE PRECISION," +
+"      ECONOMY_SEATS INTEGER," +
+"      BUSINESS_SEATS INTEGER," +
+"      FIRSTCLASS_SEATS INTEGER)");
+		st.executeUpdate(
+"ALTER TABLE AIRLINES" +
+"   ADD CONSTRAINT AIRLINES_PK Primary Key (AIRLINE)");
+		st.executeUpdate(
+"CREATE TABLE COUNTRIES (" +
+"      COUNTRY VARCHAR(26) NOT NULL," +
+"      COUNTRY_ISO_CODE CHAR(2) NOT NULL ," +
+"      REGION VARCHAR(26))");
+		st.executeUpdate(
+"ALTER TABLE COUNTRIES" +
+"   ADD CONSTRAINT COUNTRIES_PK Primary Key (COUNTRY_ISO_CODE)");
+		st.executeUpdate(
+"ALTER TABLE COUNTRIES" +
+"   ADD CONSTRAINT COUNTRIES_UNQ_NM Unique (COUNTRY)");
+		st.executeUpdate(
+"CREATE TABLE CITIES (" +
+"      CITY_ID INTEGER NOT NULL ," +
+"      CITY_NAME VARCHAR(24) NOT NULL," +
+"	COUNTRY VARCHAR(26) NOT NULL," +
+"	AIRPORT VARCHAR(3)," +
+"	LANGUAGE  VARCHAR(16)," +
+"      COUNTRY_ISO_CODE CHAR(2) )");
+		st.executeUpdate(
+"ALTER TABLE CITIES" +
+"   ADD CONSTRAINT CITIES_PK Primary Key (CITY_ID)");
+		st.executeUpdate(
+"ALTER TABLE CITIES" +
+"   ADD CONSTRAINT COUNTRIES_FK Foreign Key (COUNTRY_ISO_CODE)" +
+"   REFERENCES COUNTRIES (COUNTRY_ISO_CODE)");
+		st.executeUpdate(
+"CREATE TABLE FLIGHTS (" +
+"      FLIGHT_ID CHAR(6) NOT NULL ," +
+"      SEGMENT_NUMBER INTEGER NOT NULL ," +
+"      ORIG_AIRPORT CHAR(3)," +
+"      DEPART_TIME TIME," +
+"      DEST_AIRPORT CHAR(3)," +
+"      ARRIVE_TIME TIME," +
+"      MEAL CHAR(1)," +
+"      FLYING_TIME DOUBLE PRECISION," +
+"      MILES INTEGER," +
+"      AIRCRAFT VARCHAR(6))");
+		st.executeUpdate(
+"CREATE INDEX DESTINDEX ON FLIGHTS (DEST_AIRPORT) ");
+		st.executeUpdate(
+"CREATE INDEX ORIGINDEX ON FLIGHTS (ORIG_AIRPORT) ");
+		st.executeUpdate(
+"ALTER TABLE FLIGHTS" +
+"   ADD CONSTRAINT FLIGHTS_PK Primary Key (FLIGHT_ID, SEGMENT_NUMBER)");
+		st.executeUpdate(
+"CREATE TABLE FLIGHTAVAILABILITY (" +
+"      FLIGHT_ID CHAR(6) NOT NULL ," +
+"      SEGMENT_NUMBER INTEGER NOT NULL ," +
+"      FLIGHT_DATE DATE NOT NULL ," +
+"      ECONOMY_SEATS_TAKEN INTEGER DEFAULT 0," +
+"      BUSINESS_SEATS_TAKEN INTEGER DEFAULT 0," +
+"      FIRSTCLASS_SEATS_TAKEN INTEGER DEFAULT 0)");
+		st.executeUpdate(
+"ALTER TABLE FLIGHTAVAILABILITY" +
+"   ADD CONSTRAINT FLIGHTAVAIL_PK Primary Key " +
+"       (FLIGHT_ID, SEGMENT_NUMBER, FLIGHT_DATE)");
+		st.executeUpdate(
+"ALTER TABLE FLIGHTAVAILABILITY" +
+"   ADD CONSTRAINT FLIGHTS_FK2 Foreign Key (FLIGHT_ID, SEGMENT_NUMBER)" +
+"   REFERENCES FLIGHTS (FLIGHT_ID, SEGMENT_NUMBER)");
+		st.executeUpdate(
+"CREATE TABLE MAPS (" +
+"      MAP_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY " +
+"             (START WITH 1, INCREMENT BY 1)," +
+"      MAP_NAME VARCHAR(24) NOT NULL," +
+"      REGION VARCHAR(26)," +
+"      AREA DECIMAL(8,4) NOT NULL," +
+"      PHOTO_FORMAT VARCHAR(26) NOT NULL," +
+"      PICTURE BLOB(102400)," +
+"      UNIQUE (MAP_ID, MAP_NAME))");
+		st.executeUpdate(
+"CREATE TABLE FLIGHTS_HISTORY (" +
+"      FLIGHT_ID CHAR(6)," +
+"      SEGMENT_NUMBER INTEGER," +
+"      ORIG_AIRPORT CHAR(3)," +
+"      DEPART_TIME TIME," +
+"      DEST_AIRPORT CHAR(3)," +
+"      ARRIVE_TIME TIME," +
+"      MEAL CHAR(1)," +
+"      FLYING_TIME DOUBLE PRECISION," +
+"      MILES INTEGER," +
+"      AIRCRAFT VARCHAR(6), " +
+"      STATUS VARCHAR (20))");
+
+		st.executeUpdate(
+"insert into FLIGHTS values ('AA1111',1,'ABQ','09:00:00','LAX','09:19:00','S',1.328,664,'B747')"); 
+		st.executeUpdate(
+"insert into FLIGHTS values ('AA1112',1,'LAX','09:00:00','ABQ','11:19:00','S',1.328,664,'B747')"); 
+		st.executeUpdate(
+"insert into FLIGHTS values ('AA1113',1,'ABQ','09:00:00','PHX','09:39:00','S',0.658,329,'B747')"); 
+		st.executeUpdate(
+"insert into FLIGHTS values ('AA1114',1,'PHX','09:00:00','ABQ','09:39:00','S',0.658,329,'B747')"); 
+		st.executeUpdate(
+"insert into FLIGHTS values ('AA1115',1,'ABQ','09:00:00','OKC','11:02:00','B',1.034,517,'B747')"); 
+		st.executeUpdate(
+"insert into FLIGHTS values ('AA1116',1,'OKC','09:00:00','ABQ','09:02:00','B',1.034,517,'B747')"); 
+		st.executeUpdate(
+"insert into FLIGHTS values ('AA1117',1,'AKL','09:00:00','HNL','18:48:00','L',8.804,4402,'B747')"); 
+		st.executeUpdate(
+"insert into FLIGHTS values ('AA1118',1,'HNL','13:30:00','AKL','21:18:00','D',8.804,4402,'DC10')"); 
+		st.executeUpdate(
+"insert into FLIGHTS values ('AA1119',1,'AKL','09:00:00','NRT','15:59:00','L',10.996,5498,'B747')"); 
+		st.executeUpdate(
+"insert into FLIGHTS values ('AA1120',1,'NRT','09:00:00','AKL','23:59:00','L',10.996,5498,'B747')"); 
+
+		st.executeUpdate(
+"insert into COUNTRIES values ( 'Afghanistan','AF','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ( 'Albania','AL','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Algeria','DZ','North Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('American Samoa','AS','Pacific Islands')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Angola','AO','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Argentina','AR','South America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Armenia','AM','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Australia','AU','Australia and New Zealand')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Austria','AT','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Azerbaijan','AZ','Central Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Bahamas','BS','Caribbean')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Bangladesh','BD','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Barbados','BB','Caribbean')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Belgium','BE','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Belize','BZ','Central America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Bermuda','BM','Caribbean')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Bolivia','BO','South America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Botswana','BW','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Brazil','BR','South America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Bulgaria','BG','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Cambodia','KH','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Cameroon','CM','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Canada','CA','North America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Cape Verde','CV','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Chile','CL','South America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('China','CN','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Colombia','CO','South America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Congo','CG','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Costa Rica','CR','Central America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Cote d''Ivoire','CI','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Cuba','CU','Caribbean')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Czech Republic','CZ','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Denmark','DK','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Dominical Republic','DO','Caribbean')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Ecuador','EC','South America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Egypt','EG','North Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('El Salvador','SV','Central America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Ethiopia','ET','North Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Falkland Islands','FK','South America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Fiji','FJ','Pacific Islands')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Finland','FI','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('France','FR','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Georgia','GE','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Germany','DE','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Ghana','GH','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Greece','GR','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Guadeloupe','GP','Caribbean')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Guatemala','GT','Central America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Honduras','HN','Central America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Hungary','HU','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Iceland','IS','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('India','IN','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Indonesia','ID','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Iran','IR','Middle East')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Iraq','IQ','Middle East')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Ireland','IE','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Israel','IL','Middle East')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Italy','IT','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Jamaica','JM','Caribbean')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Japan','JP','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Jordan','JO','Middle East')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Kenya','KE','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Korea, Republic of','KR','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Lebanon','LB','Middle East')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Lithuania','LT','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Madagascar','MG','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Malaysia','MY','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Mali','ML','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Mexico','MX','North America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Morocco','MA','North Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Mozambique','MZ','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Nepal','NP','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Netherlands','NL','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('New Zealand','NZ','Australia and New Zealand')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Nicaragua','NI','Central America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Nigeria','NG','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Norway','NO','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Pakistan','PK','Central Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Paraguay','PY','South America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Peru','PE','South America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Philippines','PH','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Poland','PL','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Portugal','PT','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Russia','RU','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Samoa','WS','Pacific Islands')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Senegal','SN','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Sierra Leone','SL','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Singapore','SG','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Slovakia','SK','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('South Africa','ZA','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Spain','ES','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Sri Lanka','LK','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Sudan','SD','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Sweden','SE','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Switzerland','CH','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Syrian Arab Republic','SY','Middle East')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Tajikistan','TJ','Central Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Tanzania','TZ','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Thailand','TH','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Trinidad and Tobago','TT','Caribbean')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Tunisia','TN','North Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Turkey','TR','Middle East')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Ukraine','UA','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('United Kingdom','GB','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('United States','US','North America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Uruguay','UY','South America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Uzbekistan','UZ','Central Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Venezuela','VE','South America')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Viet Nam','VN','Asia')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Virgin Islands (British)','VG','Caribbean')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Virgin Islands (U.S.)','VI','Caribbean')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Yugoslavia','YU','Europe')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Zaire','ZR','Africa')");
+		st.executeUpdate(
+"insert into COUNTRIES values ('Zimbabwe','ZW','Africa')");
+
+                // This table 't' sets up a case where you can visit a
+                // deleted row via 'select x from t'
+		st.executeUpdate(
+"create table t (x int not null primary key, y char(250))");
+		st.executeUpdate(
+"insert into t values (1, 'a'), (2,'b'), (3,'c'), (4,'d')");
+		st.executeUpdate(
+"delete from t where x = 3");
+	}
+    private boolean hasTable(String schemaName, String tableName)
+        throws SQLException
+    {
+        ResultSet rs = getConnection().getMetaData().getTables((String)null,
+                schemaName, tableName,  new String[] {"TABLE"});
+        boolean tableFound = rs.next();
+        rs.close();
+        return tableFound;
+    }
+    private String []tableNames = {
+        "SYSXPLAIN_STATEMENTS",
+        "SYSXPLAIN_STATEMENT_TIMINGS",
+        "SYSXPLAIN_RESULTSETS",
+        "SYSXPLAIN_RESULTSET_TIMINGS",
+        "SYSXPLAIN_SORT_PROPS",
+        "SYSXPLAIN_SCAN_PROPS",
+    };
+    private void enableXplainStyle(Statement s)
+            throws SQLException
+    {
+        verifyXplainUnset(s);
+        for (int i = 0; i < tableNames.length; i++)
+            if (hasTable("XPLTEST", tableNames[i]))
+                s.execute("delete from XPLTEST." + tableNames[i]);
+        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+        s.execute("call syscs_util.syscs_set_xplain_schema('XPLTEST')");
+        s.execute("call syscs_util.syscs_set_xplain_mode(0)");
+    }
+    private void enableXplainStyleWithTiming(Statement s)
+            throws SQLException
+    {
+        enableXplainStyle(s);
+        s.execute("call syscs_util.syscs_set_statistics_timing(1)");
+    }
+    private void disableXplainStyle(Statement s)
+        throws SQLException
+    {
+        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
+    }
+    private void verifyXplainUnset(Statement s)
+        throws SQLException
+    {
+    	JDBC.assertFullResultSet(
+            s.executeQuery("values SYSCS_UTIL.syscs_get_xplain_schema()"),
+            new String[][]{{""}});
+    	JDBC.assertFullResultSet(
+            s.executeQuery("values SYSCS_UTIL.syscs_get_xplain_mode()"),
+            new String[][]{{"0"}});
+    }
+    private void verifyNonNullDRDA_ID(Statement s)
+        throws SQLException
+    {
+        ResultSet rs;
+        rs = s.executeQuery("select drda_id from xpltest.sysxplain_statements");
+        while (rs.next())
+        {
+            String drda_id = rs.getString("DRDA_ID");
+            if (rs.wasNull() || drda_id == null || drda_id.trim().length() == 0)
+                fail("While running in a network-client configuration, " +
+                        "DRDA_ID was null or blank.");
+        }
+        rs.close();
+    }
+
+    // Can be used internally when diagnosing failed tests.
+    //
+    private void dumpResultSets(Statement s)
+        throws SQLException
+    {
+        ResultSet rs;
+        rs = s.executeQuery("select * from xpltest.sysxplain_resultsets");
+        while (rs.next())
+        {
+            System.out.println(
+                    rs.getString("rs_id")+","+
+                    rs.getString("op_identifier")+","+
+                    rs.getString("op_details")+","+
+                    rs.getString("no_opens")+","+
+                    rs.getString("no_index_updates")+","+
+                    rs.getString("lock_mode")+","+
+                    rs.getString("lock_granularity")+","+
+                    rs.getString("parent_rs_id")+","+
+                    rs.getString("est_row_count")+","+
+                    rs.getString("est_cost")+","+
+                    rs.getString("affected_rows")+","+
+                    rs.getString("deferred_rows")+","+
+                    rs.getString("input_rows")+","+
+                    rs.getString("seen_rows")+","+
+                    rs.getString("seen_rows_right")+","+
+                    rs.getString("filtered_rows")+","+
+                    rs.getString("returned_rows")+","+
+                    rs.getString("empty_right_rows")+","+
+                    rs.getString("index_key_opt")+","+
+                    rs.getString("scan_rs_id")+","+
+                    rs.getString("sort_rs_id")+","+
+                    rs.getString("stmt_id")+","+
+                    rs.getString("timing_id"));
+        }
+        rs.close();
+    }
+        /**
+          * Verify that XPLAIN style captures basic statistics and timings.
+          *
+          * This test runs
+          *
+          *   SELECT Country FROM Countries WHERE Region = 'Central America'
+          *
+          * and verifies that there are some reasonable values captured
+          * into the XPLAIN system tables.
+          */
+    public void testSimpleQuery() throws SQLException
+    {
+        Statement s = createStatement();
+
+        enableXplainStyleWithTiming(s);
+
+        String selectStatement = 
+            "SELECT country from countries WHERE region = 'Central America'";
+        JDBC.assertUnorderedResultSet(
+                s.executeQuery(selectStatement),
+            new String[][] {  {"Belize"}, {"Costa Rica"}, {"El Salvador"},
+                {"Guatemala"}, {"Honduras"}, {"Nicaragua"} } );
+
+        disableXplainStyle(s);
+
+        // The statement should have been executed as a PROJECTION
+        // wrapped around a TABLESCAN. The TABLESCAN should have had
+        // scan properties. The TABLESCAN should have filtered 114 rows
+        // down to 6 rows, and the PROJECTION should have no parent RS.
+        //
+        // THe XPLAIN system tables should contain the following rows:
+        // STATEMENTS: 1 row, 
+        // STATEMENT_TIMINGS: 1 row
+        // RESULTSETS: 2 rows
+        // RESULTSET_TIMINGS: 2 rows
+        // SCAN_PROPS: 1 row
+        // SORT PROPS: no rows
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_statements"), "1");
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_statement_timings"), "1");
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_resultsets"), "2");
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_resultset_timings"), "2");
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_scan_props"), "1");
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_sort_props"), "0");
+
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_statements " +
+            " where stmt_text like '%from countries%' "), "1");
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier from xpltest.sysxplain_resultsets"),
+                new String[][] { {"PROJECTION"}, {"TABLESCAN"} } );
+        // Statement type is 'S' for Select, Xplain_mode is 'F' for FULL
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select stmt_type, stmt_text, xplain_mode " +
+                    " from xpltest.sysxplain_statements"),
+                new String[][] { {"S",selectStatement, "F"} } );
+        if (! usingDerbyNetClient())
+            JDBC.assertSingleValueResultSet(s.executeQuery(
+                "select drda_id from xpltest.sysxplain_statements"), null);
+        else
+            verifyNonNullDRDA_ID(s);
+
+        // We should have opened the inner TABLESCAN resultset once, and
+        // it should have seen 6 rows.
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+                    "select no_opens from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'TABLESCAN'"), "1");
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select no_opens, seen_rows, returned_rows, " +
+                    "       lock_mode, lock_granularity " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'TABLESCAN'"),
+                new String[][] { {"1", "6", "6", "IS", "R"} } );
+        // The TABLESCAN should have scanned the COUNTRIES heap using
+        // read-committed isolation, and it should have visited 114 rows
+        // on 2 pages and qualified 6 of those rows.
+        // Columns {0, 2} should have been fetched.
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select sp.scan_object_name, sp.scan_object_type, " +
+                    "sp.scan_type, sp.isolation_level, " +
+                    "sp.no_visited_rows, sp.no_qualified_rows, "+
+                    "sp.no_visited_pages, " +
+                    "sp.no_fetched_columns, sp.bitset_of_fetched_columns " +
+                    "from xpltest.sysxplain_scan_props sp " +
+                    "join xpltest.sysxplain_resultsets rs " +
+                    "on sp.scan_rs_id = rs.scan_rs_id " +
+                    "where rs.op_identifier='TABLESCAN'"),
+                new String[][] { {"COUNTRIES", "T", "HEAP",
+                    "RC", "114", "6", "2", "2", "{0, 2}"} } );
+        // Verify that the ID values can be used to join the rows:
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+                    "select sp.no_visited_rows " +
+                    "from xpltest.sysxplain_scan_props sp, " +
+                    "     xpltest.sysxplain_resultsets rs, " +
+                    "     xpltest.sysxplain_statements st " +
+                    "where st.stmt_id = rs.stmt_id and " +
+                    "     rs.scan_rs_id = sp.scan_rs_id and " +
+                    "     rs.op_identifier = 'TABLESCAN' and " +
+                    "     sp.scan_object_name = 'COUNTRIES'"),
+                "114");
+        
+        verifySensibleStatementTimings(s);
+        verifySensibleResultSetTimings(s);
+    }
+
+    /**
+      * Make some basic first-order checks on the STATEMENT_TIMINGS rows.
+      *
+      * This method runs through the rows in the STATEMENT_TIMINGS table
+      * and makes some very high-level reasonableness checks:
+      *
+      * 1) The values for PARSE_TIME, BIND_TIME, OPTIMIZE_TIME,
+      *    GENERATE_TIME, COMPILE_TIME, and EXECUTE_TIME should all
+      *    be non-negative integer values.
+      * 2) The values of PARSE_TIME, BIND_TIME, OPTIMIZE_TIME, and
+      *    GENERATE_TIME, summed together, should equal the value
+      *    of COMPILE_TIME.
+      * 3) The values of BEGIN_COMP_TIME, END_COMP_TIME, BEGIN_EXE_TIME,
+      *    and END_EXE_TIME should all be valid times, and should all
+      *    be greater than the time when we started running these tests.
+      * 4) END_COMP_TIME should be G.E. BEGIN_COMP_TIME,
+      *    BEGIN_EXE_TIME should be G.E. END_COMP_TIME, and
+      *    END_EXE_TIME should be G.E. BEGIN_EXE_TIME
+      */
+    private void verifySensibleStatementTimings(Statement s)
+        throws SQLException
+    {
+        ResultSet rs = s.executeQuery(
+                "select * from xpltest.sysxplain_statement_timings");
+        while (rs.next())
+        {
+            long parseTime = getNonNegativeLong(rs, "PARSE_TIME");
+            long bindTime = getNonNegativeLong(rs, "BIND_TIME");
+            long optimizeTime = getNonNegativeLong(rs, "OPTIMIZE_TIME");
+            long generateTime = getNonNegativeLong(rs, "GENERATE_TIME");
+            long compileTime = getNonNegativeLong(rs, "COMPILE_TIME");
+            long executeTime = getNonNegativeLong(rs, "EXECUTE_TIME");
+
+            // Due to rounding errors, this is not always exact? I think that
+            // the largest rounding error should be 2, because there are
+            // 4 sub-elements which each may be rounded off by 0.5 millis?
+            //
+            // So we'll accept a difference of 1 or 2 in the overall comp time
+            //
+            long compTimeRoundingError = 
+                compileTime - (parseTime+bindTime+optimizeTime+generateTime);
+            if (compTimeRoundingError < 0 || compTimeRoundingError > 2)
+                assertEquals("compilation time did not compute (" +
+                    parseTime+","+bindTime+","+optimizeTime+","+
+                    generateTime+")", 
+                compileTime, (parseTime+bindTime+optimizeTime+generateTime));
+
+            Timestamp beginCompTime=getNonNullTimestamp(rs, "BEGIN_COMP_TIME");
+            Timestamp endCompTime = getNonNullTimestamp(rs, "END_COMP_TIME");
+            Timestamp beginExeTime = getNonNullTimestamp(rs, "BEGIN_EXE_TIME");
+            Timestamp endExeTime = getNonNullTimestamp(rs, "END_EXE_TIME");
+
+            if (endCompTime.before(beginCompTime))
+                fail("END_COMP_TIME " + endCompTime +
+                    " unexpectedly before BEGIN_COMP_TIME " + beginCompTime);
+            if (beginExeTime.before(endCompTime))
+                fail("BEGIN_EXE_TIME " + beginExeTime +
+                    " unexpectedly before END_COMP_TIME " + endCompTime);
+            if (endExeTime.before(beginExeTime))
+                fail("END_EXE_TIME " + endExeTime +
+                    " unexpectedly before BEGIN_EXE_TIME " + beginExeTime);
+        }
+        rs.close();
+    }
+
+    /**
+      * Make some basic first-order checks on the RESULTSET_TIMINGS rows.
+      *
+      * This method runs through the rows in the RESULTSET_TIMINGS table
+      * and makes some very simple checks for reasonableness of the values:
+      *
+      * 1) For each row in RESULTSETS, there should be a row in 
+      *    RESULTSET_TIMINGS
+      * 2) There should be non-negative values for CONSTRUCTOR_TIME, OPEN_TIME,
+      *    NEXT_TIME, CLOSE_TIME, and EXECUTE_TIME.
+      * 3) If the result set has a non-zero value for RETURNED_ROWS, then
+      *    there should be a non-negative value for AVG_NEXT_TIME_PER_ROW;
+      *    conversely if the result set did not have any returned rows, then
+      *    AVG_NEXT_TIME_PER_ROW should be null.
+      * 4) If the result set is a PROJECTION, then PROJECTION_TIME and
+      *    RESTRICTION_TIME should have valid non-negative values;
+      *    otherwise those columns should be NULL.
+      * 5) If the result set is a MATERIALIZE, then TEMP_CONG_CREATE_TIME
+      *    and TEMP_CONG_FETCH_TIME should have valid non-negative values;
+      *    otherwise those columns should be null.
+      */
+    private void verifySensibleResultSetTimings(Statement s)
+        throws SQLException
+    {
+        ResultSet rs = s.executeQuery(
+                "select rs.op_identifier, rs.returned_rows, rt.* " +
+                "from xpltest.sysxplain_resultsets rs left outer join " +
+                "     xpltest.sysxplain_resultset_timings rt " +
+                "on rs.timing_id = rt.timing_id");
+
+        while (rs.next())
+        {
+            String opIdentifier = rs.getString("OP_IDENTIFIER");
+            // Since we performed a LEFT OUTER JOIN, the TIMING_ID will be
+            // NULL if the resultset is missing a timings row.
+            String timingId = rs.getString("TIMING_ID");
+            assertNotNull("RESULTSET row missing for " + opIdentifier,
+                    timingId);
+
+            getNonNegativeLong(rs, "CONSTRUCTOR_TIME");
+            getNonNegativeLong(rs, "OPEN_TIME");
+            getNonNegativeLong(rs, "NEXT_TIME");
+            getNonNegativeLong(rs, "CLOSE_TIME");
+            // FIXME -- EXECUTE_TIME is unexpectedly negative for various
+            // result sets.
+            // getNonNegativeLong(rs, "EXECUTE_TIME");
+
+            long returnedRows = rs.getLong("RETURNED_ROWS");
+            if (returnedRows > 0)
+            {
+                getNonNegativeLong(rs, "AVG_NEXT_TIME_PER_ROW");
+            }
+            else
+            {
+                long avgNextTimePerRow = rs.getLong("AVG_NEXT_TIME_PER_ROW");
+                assertTrue("Expected NULL for avg-next on rs " + opIdentifier,
+                        rs.wasNull());
+            }
+            if (opIdentifier.equals("PROJECTION"))
+            {
+                getNonNegativeLong(rs, "PROJECTION_TIME");
+                getNonNegativeLong(rs, "RESTRICTION_TIME");
+            }
+            else
+            {
+                rs.getLong("PROJECTION_TIME");
+                assertTrue("Expected NULL PROJECTION_TIME for " + opIdentifier,
+                        rs.wasNull());
+                rs.getLong("RESTRICTION_TIME");
+                assertTrue("Expected NULL RESTRICTION_TIME for " + opIdentifier,
+                        rs.wasNull());
+            }
+            if (opIdentifier.equals("MATERIALIZE"))
+            {
+                getNonNegativeLong(rs, "TEMP_CONG_CREATE_TIME");
+                getNonNegativeLong(rs, "TEMP_CONG_FETCH_TIME");
+            }
+            else
+            {
+                rs.getLong("TEMP_CONG_CREATE_TIME");
+                assertTrue("Expected NULL TEMP_CONG_CREATE_TIME for " +
+                    opIdentifier, rs.wasNull());
+                rs.getLong("TEMP_CONG_FETCH_TIME");
+                assertTrue("Expected NULL TEMP_CONG_FETCH_TIME for " +
+                    opIdentifier, rs.wasNull());
+            }
+        }
+    }
+
+    private long getNonNegativeLong(ResultSet rs, String cName)
+        throws SQLException
+    {
+        long result = rs.getLong(cName);
+        assertTrue(cName + " unexpectedly NULL", ! rs.wasNull() );
+        assertTrue(cName + " unexpectedly negative(" + result + ")",
+                result >= 0);
+        return result;
+    }
+
+    private Timestamp getNonNullTimestamp(ResultSet rs, String cName)
+        throws SQLException
+    {
+        Timestamp result = rs.getTimestamp(cName);
+        assertTrue(cName + " unexpectedly NULL", ! rs.wasNull() );
+        assertNotNull(cName + " unexpectedly NULL", result);
+        assertTrue("Test started at " + timeSuiteStarted +
+                " but " + cName + " value is " + result.getTime(),
+                result.getTime() >= timeSuiteStarted);
+        return result;
+    }
+
+        /**
+          * Verify XPLAIN style handling of an INDEX scan.
+          *
+          * This test runs a query against FLIGHTS using the dest_airport
+          * index, and verifies the captured query plan statistics.
+          */
+    public void testIndexScan() throws SQLException
+    {
+        Statement s = createStatement();
+
+        enableXplainStyleWithTiming(s);
+
+        String selectStatement = 
+            "SELECT flight_id from flights where dest_airport = 'ABQ'";
+        JDBC.assertUnorderedResultSet(s.executeQuery(selectStatement),
+            new String[][] {  {"AA1112"}, {"AA1114"}, {"AA1116"} } );
+
+        disableXplainStyle(s);
+
+        // This query should have been executed as a PROJECTION whose child
+        // is a ROWIDSCAN whose child is an INDEXSCAN. The INDEXSCAN should
+        // be linked to a BTREE SCAN_PROPS row against the DESTINDEX index.
+        // The index scan should have visited 1 page and 4 rows, and should
+        // have qualified 3 of those rows, fetching 2 (ALL) the columns
+        // from those rows.
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier from xpltest.sysxplain_resultsets"),
+            new String[][] { {"PROJECTION"}, {"ROWIDSCAN"}, {"INDEXSCAN"} } );
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+                    "select p.parent_rs_id " +
+                    " from xpltest.sysxplain_resultsets p, " +
+                    "      xpltest.sysxplain_resultsets r, " +
+                    "      xpltest.sysxplain_resultsets i  " +
+                    " where p.rs_id = r.parent_rs_id and " +
+                    "       r.rs_id = i.parent_rs_id and " +
+                    "       p.op_identifier='PROJECTION' and " +
+                    "       r.op_identifier='ROWIDSCAN' and " +
+                    "       i.op_identifier='INDEXSCAN'"),
+                null);
+        // All 3 resultsets rows should join to the same statements row.
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+                    "select count(rs.op_identifier) " +
+                    " from xpltest.sysxplain_statements st " +
+                    " join xpltest.sysxplain_resultsets rs " +
+                    "   on st.stmt_id = rs.stmt_id"),
+                "3");
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select sp.scan_object_name, sp.scan_object_type, " +
+                    "sp.scan_type, sp.isolation_level, " +
+                    "rs.lock_mode, rs.lock_granularity, " +
+                    "sp.no_visited_rows, sp.no_qualified_rows, "+
+                    "sp.no_visited_pages, sp.btree_height, sp.fetch_size, " +
+                    "sp.no_fetched_columns, sp.bitset_of_fetched_columns " +
+                    "from xpltest.sysxplain_scan_props sp " +
+                    "join xpltest.sysxplain_resultsets rs " +
+                    "on sp.scan_rs_id = rs.scan_rs_id " +
+                    "where rs.op_identifier='INDEXSCAN'"),
+                new String[][] { {"DESTINDEX", "I", "BTREE",
+                    "RC", "IS", "R",
+                    "4", "3", "1", "-1", "16", "2", "ALL"} } );
+        verifySensibleStatementTimings(s);
+        verifySensibleResultSetTimings(s);
+    }
+
+        /**
+          * Verify XPLAIN style handling of a CONSTRAINT scan.
+          *
+          * This test runs a query against COUNTRIES using the
+          * COUNTRY_ISO_CODE constraint,
+          * and verifies the captured query plan statistics.
+          */
+    public void testConstraintScan() throws SQLException
+    {
+        Statement s = createStatement();
+
+        enableXplainStyle(s);
+        String selectStatement = 
+            "SELECT region from countries where country = 'Cameroon'";
+        JDBC.assertSingleValueResultSet(s.executeQuery(selectStatement),
+                "Africa");
+        disableXplainStyle(s);
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier from xpltest.sysxplain_resultsets"),
+            new String[][] {
+                {"PROJECTION"}, {"ROWIDSCAN"}, {"CONSTRAINTSCAN"} } );
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select sp.scan_object_name, sp.scan_object_type, " +
+                    "sp.scan_type, sp.isolation_level, " +
+                    "sp.no_visited_rows, sp.no_qualified_rows, "+
+                    "sp.no_visited_pages, " +
+                    "rs.lock_mode, rs.lock_granularity, " +
+                    "sp.no_fetched_columns, sp.bitset_of_fetched_columns " +
+                    "from xpltest.sysxplain_scan_props sp " +
+                    "join xpltest.sysxplain_resultsets rs " +
+                    "on sp.scan_rs_id = rs.scan_rs_id " +
+                    "where rs.op_identifier='CONSTRAINTSCAN'"),
+                new String[][] { {"COUNTRIES_UNQ_NM", "C", "BTREE",
+                    "RC", "1", "1", "1", "SH", "R", "2", "ALL"} } );
+    }
+
+    /**
+      * Verify XPLAIN style handling of sort properties.
+      *
+      * This test runs a simple query against the COUNTRIES table and
+      * verifies that reasonable values are captured for the sort properties.
+      */
+    public void testGroupBySortProps()
+        throws SQLException
+    {
+        Statement s = createStatement();
+
+        enableXplainStyleWithTiming(s);
+        String selectStatement = 
+            "select region, count(country) from countries group by region";
+        JDBC.assertUnorderedResultSet(s.executeQuery(selectStatement),
+            new String[][] { 
+                {"Africa", "19"}, {"Asia", "15"},
+                {"Australia and New Zealand", "2"}, {"Caribbean", "10"},
+                {"Central America", "6"}, {"Central Asia", "4"},
+                {"Europe", "29"}, {"Middle East", "7"},
+                {"North Africa", "5"}, {"North America", "3"},
+                {"Pacific Islands", "3"}, {"South America", "11"} } );
+
+        disableXplainStyle(s);
+
+        // This statement is executed as a PROJECTION with a child GROUPBY
+        // with a child PROJECTION with a child TABLESCAN. The TABLESCAN
+        // has a corresponding SCAN_PROPS row, the GROUPBY has a
+        // corresponding SORT_PROPS row. In this test, we're mostly
+        // interested in the values in the SORT_PROPS row.
+        //
+        // The sort groups the 114 input rows into 12 groups. The rows
+        // are not provided in sort order as the input, and the sort
+        // is not a distinct aggregate.
+        //
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_resultsets"), "4");
+        JDBC.assertFullResultSet(s.executeQuery(
+                    "select op_identifier from xpltest.sysxplain_resultsets " +
+                    "order by op_identifier"),
+            new String[][] {
+                {"GROUPBY"},{"PROJECTION"},{"PROJECTION"},{"TABLESCAN"} } );
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_resultsets " +
+            "where scan_rs_id is not null"), "1");
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_resultsets " +
+            "where sort_rs_id is not null"), "1");
+        JDBC.assertFullResultSet(s.executeQuery(
+                    "select s.stmt_text, rs.op_identifier," +
+                    " srt.no_input_rows, srt.no_output_rows " +
+                    " from xpltest.sysxplain_sort_props srt, " +
+                    " xpltest.sysxplain_resultsets rs, " +
+                    " xpltest.sysxplain_statements s " +
+                    " where rs.stmt_id = s.stmt_id and " +
+                    " rs.sort_rs_id = srt.sort_rs_id"),
+            new String[][] {
+                {selectStatement, "GROUPBY", "114", "12"} } );
+
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select srt.sort_type, srt.no_input_rows, " +
+                    " srt.no_output_rows, srt.no_merge_runs, " +
+                    " srt.merge_run_details, srt.eliminate_duplicates, " +
+                    " srt.in_sort_order, srt.distinct_aggregate " +
+                    "from xpltest.sysxplain_sort_props srt " +
+                    "join xpltest.sysxplain_resultsets rs " +
+                    "on srt.sort_rs_id = rs.sort_rs_id " +
+                    "where rs.op_identifier='GROUPBY'"),
+                new String[][] {
+                    {"IN","114","12",null, null, null,"N","N"} } );
+        verifySensibleStatementTimings(s);
+        verifySensibleResultSetTimings(s);
+    }
+
+    /**
+      * Verify XPLAIN style handling of DISINCT_AGGREGATE sort properties.
+      *
+      * This test runs a query which involves a distinct aggreagte and
+      * verifies that the DISTINCT_AGGREGATE field in SORT_PROPS gets set.
+      */
+    public void testDistinctAggregateSortProps()
+        throws SQLException
+    {
+        Statement s = createStatement();
+
+        enableXplainStyleWithTiming(s);
+        String selectStatement = 
+            "select orig_airport, avg(distinct miles) from flights " +
+            "group by orig_airport";
+        // Execute the statement and throw away the results. We just want
+        // to look at the statistics.
+        s.executeQuery(selectStatement).close();
+        disableXplainStyle(s);
+
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_sort_props"), "1");
+
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select srt.sort_type, srt.no_input_rows, " +
+                    " srt.no_output_rows, srt.no_merge_runs, " +
+                    " srt.merge_run_details, srt.eliminate_duplicates, " +
+                    " srt.in_sort_order, srt.distinct_aggregate " +
+                    "from xpltest.sysxplain_sort_props srt " +
+                    "join xpltest.sysxplain_resultsets rs " +
+                    "on srt.sort_rs_id = rs.sort_rs_id " +
+                    "where rs.op_identifier='GROUPBY'"),
+                new String[][] {
+                    {"IN","10","7",null, null, null,"N","Y"} } );
+        verifySensibleStatementTimings(s);
+        verifySensibleResultSetTimings(s);
+    }
+    /**
+      * A simple test of an AGGREGATION result set.
+      */
+    public void testAggregationResultSet()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        enableXplainStyle(s);
+        String selectStatement = 
+            "select count(distinct region) from countries";
+        JDBC.assertSingleValueResultSet(s.executeQuery(selectStatement), "12");
+        disableXplainStyle(s);
+
+        // The above statement results in the query execution:
+        // PROJECTION(AGGREGATION(PROJECTION(TABLESCAN)))
+        //
+        // In this test case, we are interested in verifying that the
+        // content of the AGGREGATION resultset row is reasonable.
+
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier, op_details, no_opens " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'AGGREGATION'"),
+                new String[][] { {"AGGREGATION", "DISTINCT", "1"} } );
+        //
+        // FIXME -- why are INPUT_ROWS, SEEN_ROWS, FILTERED_ROWS, and
+        // RETURNED_ROWS apparently meaninglyess for an AGGREGATION RS?
+    }
+
+    /**
+      * A simple test of an INSERT result set.
+      */
+    public void testInsertResultSet()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        enableXplainStyle(s);
+        String insertStatement = 
+            "insert into AIRLINES values " +
+            "('AA','Amazonian Airways',0.18,0.03,0.5,1.5,20,10,5)";
+        int numRows = s.executeUpdate(insertStatement);
+        disableXplainStyle(s);
+        assertEquals("Failed to insert into AIRLINES", 1, numRows);
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select stmt_type, stmt_text " +
+                    " from xpltest.sysxplain_statements"),
+                new String[][] { {"I",insertStatement} } );
+        //
+        // The above INSERT statement results in the query execution:
+        // INSERT(NORMALIZE(ROW))
+        //
+        // Verify some of the basic contents of the SYSXPLAIN_RESULTSETS
+        // rows for those result sets.
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_resultsets"), "3");
+        // FIXME -- why is lock_mode NULL? Shouldn't it be IX?
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier, op_details, " +
+                    "       no_index_updates, lock_mode, " +
+                    "       lock_granularity, parent_rs_id, " +
+                    "       affected_rows, deferred_rows " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'INSERT'"),
+                new String[][] {
+                    {"INSERT",null,"1",null,"R", null,"1","N"} } );
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier, op_details, no_opens, " +
+                    "       seen_rows, filtered_rows, returned_rows " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'NORMALIZE'"),
+                new String[][] {
+                    {"NORMALIZE",null,"1","1","0","1"} } );
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier, op_details, no_opens, " +
+                    "       seen_rows, filtered_rows, returned_rows " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'ROW'"),
+                new String[][] {
+                    {"ROW",null,"1","0","0","1"} } );
+    }
+
+    /**
+      * A simple test of an UPDATE result set.
+      */
+    public void testUpdateResultSet()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        s.executeUpdate("delete from AIRLINES");
+        String insertStatement = 
+            "insert into AIRLINES values " +
+            "('AA','Amazonian Airways',0.18,0.03,0.5,1.5,20,10,5)";
+        int numRows = s.executeUpdate(insertStatement);
+        assertEquals("Failed to insert into AIRLINES", 1, numRows);
+        String updateStatement = 
+            "update AIRLINES set economy_seats=23,business_seats=7 " +
+            " where airline='AA'";
+        enableXplainStyle(s);
+        numRows = s.executeUpdate(updateStatement);
+        assertEquals("Failed to update AIRLINES", 1, numRows);
+        disableXplainStyle(s);
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select stmt_type, stmt_text " +
+                    " from xpltest.sysxplain_statements"),
+                new String[][] { {"U",updateStatement} } );
+        //
+        // The above UPDATE statement results in the query execution:
+        // UPDATE(PROJECTION(ROWIDSCAN(CONSTRAINTSCAN)))
+        // The CONSTRAINTSCAN has a SCAN_PROPS associated with it.
+        //
+        // Verify some of the basic contents of the SYSXPLAIN_RESULTSETS
+        // rows for those result sets.
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_resultsets"), "4");
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select op_identifier from xpltest.sysxplain_resultsets " +
+            " where scan_rs_id is not null"), "CONSTRAINTSCAN");
+        // FIXME -- shouldn't lock_mode be 'IX'?
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier, op_details, no_opens, " +
+                    "       no_index_updates, lock_mode, " +
+                    "       lock_granularity, parent_rs_id, " +
+                    "       affected_rows, deferred_rows " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'UPDATE'"),
+                new String[][] {
+                    {"UPDATE",null,null,"0",null,"R",null,"1","N"} } );
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier, op_details, no_opens, " +
+                    "       seen_rows, filtered_rows, returned_rows " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'PROJECTION'"),
+                new String[][] {
+                    {"PROJECTION","2;","1","1","0","1"} } );
+        ResultSet myRS = s.executeQuery(
+                    "select op_identifier, op_details, no_opens, " +
+                    "       seen_rows, filtered_rows, returned_rows " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'ROWIDSCAN'");
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier, op_details, no_opens, " +
+                    "       seen_rows, filtered_rows, returned_rows " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'ROWIDSCAN'"),
+                new String[][] {
+                    {"ROWIDSCAN","(0),AIRLINES","1","1","0","1"} } );
+        //dumpResultSets(s);
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier, op_details, no_opens, " +
+                    "       lock_mode, lock_granularity, " +
+                    "       seen_rows, filtered_rows, returned_rows " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'CONSTRAINTSCAN'"),
+                new String[][] {
+                    {"CONSTRAINTSCAN","C: AIRLINES_PK",
+                        "1","EX","R","1","0","1"} } );
+        // Verify that the CONSTRAINTSCAN is linked to a type=C scan_props
+        // row, which should have visited 1 row on 1 page, fetching 2 (ALL)
+        // columns, with non-null start_position, stop_position, and
+        // scan_qualifiers set to 'None'.
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select sp.scan_object_name, sp.scan_object_type, " +
+                    "sp.scan_type, sp.isolation_level, " +
+                    "sp.no_visited_rows, sp.no_qualified_rows, "+
+                    "sp.no_visited_pages, sp.no_visited_deleted_rows, " +
+                    "sp.no_fetched_columns, sp.bitset_of_fetched_columns, " +
+                    "sp.scan_qualifiers " +
+                    "from xpltest.sysxplain_scan_props sp " +
+                    "join xpltest.sysxplain_resultsets rs " +
+                    "on sp.scan_rs_id = rs.scan_rs_id " +
+                    "where rs.op_identifier='CONSTRAINTSCAN'"),
+                new String[][] {
+                    {"AIRLINES_PK", "C", "BTREE", "RC",
+                        "1", "1", "1", "0", "2", "ALL", "None"} } );
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_scan_props " +
+            "  where start_position is not null " +
+            "    and stop_position is not null"), "1");
+    }
+
+    /**
+      * A simple test of an DELETE result set.
+      */
+    public void testDeleteResultSet()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        s.executeUpdate("delete from AIRLINES");
+        String insertStatement = 
+            "insert into AIRLINES values " +
+            "('AA','Amazonian Airways',0.18,0.03,0.5,1.5,20,10,5)";
+        int numRows = s.executeUpdate(insertStatement);
+        assertEquals("Failed to insert into AIRLINES", 1, numRows);
+        String deleteStatement = "delete from airlines where airline='AA'";
+        enableXplainStyle(s);
+        numRows = s.executeUpdate(deleteStatement);
+        assertEquals("Failed to delete from AIRLINES", 1, numRows);
+        disableXplainStyle(s);
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select stmt_type, stmt_text " +
+                    " from xpltest.sysxplain_statements"),
+                new String[][] { {"D",deleteStatement} } );
+        //
+        // The above DELETE statement results in the query execution:
+        // DELETE(PROJECTION(PROJECTION(CONSTRAINTSCAN)))
+        // The CONSTRAINTSCAN has a SCAN_PROPS associated with it.
+        //
+        // We basically just check the DELETE result set, since we've
+        // checked the other result sets in other test cases.
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_resultsets"), "4");
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select distinct op_identifier " +
+                    " from xpltest.sysxplain_resultsets " +
+                    " order by op_identifier"),
+                new String[][] {
+                    {"CONSTRAINTSCAN"}, {"DELETE"}, {"PROJECTION"} } );
+        // FIXME -- shouldn't lock_mode be 'IX'?
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier, op_details, no_opens, " +
+                    "       no_index_updates, lock_mode, " +
+                    "       lock_granularity, parent_rs_id, " +
+                    "       affected_rows, deferred_rows " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'DELETE'"),
+                new String[][] {
+                    {"DELETE",null,null,"1",null,"R",null,"1","N"} } );
+    }
+
+    /**
+      * A simple test of a SORT result set.
+      */
+    public void testSortResultSet()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        enableXplainStyle(s);
+        String selectStatement = 
+            "select region from countries order by country";
+        s.executeQuery(selectStatement).close(); // Discard the results
+        disableXplainStyle(s);
+
+        // The above statement results in the query execution:
+        // PROJECTION(SORT(PROJECTION(TABLESCAN)))
+        //
+        // In this test case, we are interested in verifying that the
+        // content of the SORT resultset row and the content of
+        // the corresponding SORT_PROPS row are reasonable.
+
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_resultsets"), "4");
+        // FIXME -- why is RETURNED_ROWS 0 for this sort? Shouldn't it be 114?
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier, op_details, no_opens, " +
+                    "       input_rows, seen_rows, filtered_rows, " +
+                    "       returned_rows " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'SORT'"),
+                new String[][] {
+                    {"SORT", null, "1","114","0","0","0"} } );
+        JDBC.assertFullResultSet(s.executeQuery(
+                    "select s.stmt_text, rs.op_identifier," +
+                    " srt.no_input_rows, srt.no_output_rows, " +
+                    " srt.sort_type, srt.eliminate_duplicates, " +
+                    " srt.in_sort_order, srt.distinct_aggregate " +
+                    " from xpltest.sysxplain_sort_props srt, " +
+                    " xpltest.sysxplain_resultsets rs, " +
+                    " xpltest.sysxplain_statements s " +
+                    " where rs.stmt_id = s.stmt_id and " +
+                    " rs.sort_rs_id = srt.sort_rs_id"),
+            new String[][] {
+                {selectStatement, "SORT", "114", "114", "IN", "N", "N", null}
+            } );
+    }
+
+    /**
+      * A simple test of a UNION query.
+      */
+    public void testUnionQuery()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        enableXplainStyle(s);
+        String selectStatement = 
+            "select country from countries where region = 'Central America' "+
+            " union " +
+            "select country from countries where region = 'Africa'";
+        s.executeQuery(selectStatement).close(); // Discard the results
+        disableXplainStyle(s);
+
+        // The above statement results in the query execution:
+        // SORT(UNION(PROJECTION(TABLESCAN),PROJECTION(TABLESCAN)))
+        //
+        // Note that the UNION resultset has TWO child result sets.
+        //
+        // There is also 1 SORT_PROPS row, for the top-level SORT, and
+        // 2 SCAN_PROPS rows, for the two TABLESCAN nodes.
+        //
+        // We verify the overall structure of the result set nodes, and
+        // spot check a few of the fields.
+
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_resultsets"), "6");
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_resultsets " +
+            "  where parent_rs_id = " +
+            "        (select rs_id from xpltest.sysxplain_resultsets" +
+            "         where op_identifier = 'UNION')"), "2");
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+            "select op_identifier from xpltest.sysxplain_resultsets " +
+            "where scan_rs_id is not null"),
+                new String[][] { {"TABLESCAN"}, {"TABLESCAN"} } );
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select op_identifier from xpltest.sysxplain_resultsets " +
+            " where sort_rs_id is not null and parent_rs_id is null"),
+                "SORT");
+
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier, op_details, no_opens, " +
+                    "       seen_rows, seen_rows_right, filtered_rows, " +
+                    "       returned_rows " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'UNION'"),
+                new String[][] {
+                    {"UNION", "(2)", "1", "6","19","0","25"} } );
+        JDBC.assertFullResultSet(s.executeQuery(
+                    "select s.stmt_text, rs.op_identifier," +
+                    " srt.no_input_rows, srt.no_output_rows, " +
+                    " srt.sort_type, srt.eliminate_duplicates, " +
+                    " srt.in_sort_order, srt.distinct_aggregate " +
+                    " from xpltest.sysxplain_sort_props srt, " +
+                    " xpltest.sysxplain_resultsets rs, " +
+                    " xpltest.sysxplain_statements s " +
+                    " where rs.stmt_id = s.stmt_id and " +
+                    " rs.sort_rs_id = srt.sort_rs_id"),
+            new String[][] {
+                {selectStatement, "SORT", "25", "25", "IN", "Y", "N", null}
+            } );
+    }
+
+    /**
+      * A simple test of capturing statistics for a DDL statement.
+      */
+    public void testDDLCreateTable()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        enableXplainStyle(s);
+        String ddlStatement = 
+            "create table t1 (a int, b char(10), c timestamp)";
+        s.executeUpdate(ddlStatement);
+        disableXplainStyle(s);
+
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select stmt_type, stmt_text " +
+                    " from xpltest.sysxplain_statements"),
+                new String[][] { {"DDL",ddlStatement} } );
+        JDBC.assertSingleValueResultSet(s.executeQuery(
+            "select count(*) from xpltest.sysxplain_resultsets"), "0");
+    }
+
+    /**
+      * A simple test of the INDEX_KEY_OPT special situation.
+      */
+    public void testMaxFromIndex()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        enableXplainStyle(s);
+        String selectStatement = 
+            "select max(country_iso_code) from countries";
+        s.executeQuery(selectStatement).close();
+        disableXplainStyle(s);
+
+        // The above query is executed as
+        // PROJECTION(AGGREGATION(PROJECTION(LASTINDEXKEYSCAN)))
+        //
+        // The AGGREGATION resultset has index_key_opt = 'Y'.
+        //
+        // We verify some of the information in the result sets.
+
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier, op_details, no_opens, " +
+                    "       input_rows, seen_rows, filtered_rows, " +
+                    "       returned_rows, index_key_opt " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'AGGREGATION'"),
+                new String[][] {
+                    {"AGGREGATION", null, "1", "0","0","0","0","Y"} } );
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier, op_details, no_opens, " +
+                    "       seen_rows, filtered_rows, returned_rows " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'LASTINDEXKEYSCAN'"),
+                new String[][] {
+                    {"LASTINDEXKEYSCAN", "I: COUNTRIES_PK, T: COUNTRIES",
+                     "1", "1","0","1"} } );
+        // FIXME -- seems like the scan information for the LASTINDEXKEYSCAN
+        // is rather incomplete
+        // In fact, SCAN_TYPE seems to come back as NULL even though that
+        // column is not nullable?
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select sp.scan_object_name, sp.scan_object_type, " +
+                    //"sp.scan_type, sp.isolation_level, " +
+                    " sp.isolation_level, " +
+                    "sp.no_visited_rows, sp.no_qualified_rows, "+
+                    "sp.no_visited_pages, sp.no_visited_deleted_rows, " +
+                    "sp.no_fetched_columns, sp.bitset_of_fetched_columns, " +
+                    "sp.scan_qualifiers " +
+                    "from xpltest.sysxplain_scan_props sp " +
+                    "join xpltest.sysxplain_resultsets rs " +
+                    "on sp.scan_rs_id = rs.scan_rs_id " +
+                    "where rs.op_identifier='LASTINDEXKEYSCAN'"),
+                new String[][] {
+                    {"COUNTRIES_PK", "I",
+                        // null,
+                        "RC", null, null, null, null, null, null, null} } );
+    }
+
+    /**
+      * A simple test of a LEFT OUTER JOIN and EMPTY_RIGHT_ROWS values.
+      */
+    public void testOuterJoin()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        enableXplainStyle(s);
+        String selectStatement = 
+            "select f.orig_airport,c.country " +
+            " from flights f left outer join countries c " +
+            " on f.orig_airport = c.country_iso_code";
+        JDBC.assertUnorderedResultSet(s.executeQuery(selectStatement),
+                new String[][] {
+                    {"ABQ",null},{"LAX",null},{"ABQ",null},{"PHX",null},
+                    {"ABQ",null},{"OKC",null},{"AKL",null},{"HNL",null},
+                    {"AKL",null},{"NRT",null}
+        });
+        disableXplainStyle(s);
+
+        // We should get a Nested Loop Outer Join which  reads 10 rows
+        // from the left (SEEN_ROWS), constructs 10 EMPTY_RIGHT_ROWS,
+        // and has 10 RETURNED_ROWS.
+        //
+        // The overall statement is:
+        // PROJECTION(LONLJOIN(INDEXSCAN,ROWIDSCAN(CONSTRAINTSCAN)))
+        // The nested loop join is a scan of the orig_airport index,
+        // joining against probes into the countries_pk constraint-index.
+
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select op_identifier from xpltest.sysxplain_resultsets "),
+                new String[][] {
+                    {"PROJECTION"}, {"LONLJOIN"}, {"INDEXSCAN"},
+                    {"ROWIDSCAN"}, {"CONSTRAINTSCAN"} } );
+        // FIXME -- I think EMPTY_RIGHT_ROWS should be 10, not 0?
+        JDBC.assertFullResultSet(s.executeQuery(
+                    "select op_identifier, op_details, no_opens, " +
+                    "       seen_rows, seen_rows_right, filtered_rows, " +
+                    "       returned_rows, empty_right_rows " +
+                    "from xpltest.sysxplain_resultsets " +
+                    "where op_identifier = 'LONLJOIN'"),
+                new String[][] {
+                    {"LONLJOIN", "(1), Nested Loop Left Outer Join ResultSet",
+                     "1", "10", "0", "0", "10", "0"} } );
+    }
+
+    /**
+      * A simple test to verify that startPosition and stopPosition work.
+      */
+    public void testScanPositions()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        // Try several different syntaxes of index scans, to see what we get:
+        String []searches = {
+            "select * from flights where dest_airport = 'ABQ'",
+            "select * from flights where dest_airport > 'HNL'",
+            "select * from flights where dest_airport < 'HNL'",
+            "select * from flights where dest_airport between 'H' and 'J'",
+            "select * from flights where dest_airport like 'AB%'",
+        };
+        String []startPrefixes = {
+            ">= on first 1 column(s).",
+            "> on first 1 column(s).",
+            "None",
+            ">= on first 1 column(s).",
+            ">= on first 1 column(s).",
+        };
+        String []stopPrefixes = {
+            "> on first 1 column(s).",
+            "None",
+            ">= on first 1 column(s).",
+            "> on first 1 column(s).",
+            ">= on first 1 column(s).",
+        };
+        enableXplainStyle(s);
+        for (int i = 0; i < searches.length; i++)
+            s.executeQuery(searches[i]).close();
+        disableXplainStyle(s);
+
+        ResultSet rs = s.executeQuery(
+                "select s.stmt_text, sp.start_position, sp.stop_position " +
+                "  from xpltest.sysxplain_statements s, " +
+                "       xpltest.sysxplain_resultsets rs, " +
+                "       xpltest.sysxplain_scan_props sp " +
+                " where s.stmt_id = rs.stmt_id and " +
+                "       rs.scan_rs_id = sp.scan_rs_id");
+        int matchedStatements = 0;
+        while (rs.next())
+        {
+            String sText = rs.getString("stmt_text");
+            String startPos = rs.getString("start_position").trim();
+            String stopPos = rs.getString("stop_position").trim();
+            boolean foundStmt = false;
+            for (int i = 0; i < searches.length; i++)
+            {
+                if (searches[i].equals(sText))
+                {
+                    matchedStatements++;
+                    foundStmt = true;
+                    if (! startPos.startsWith(startPrefixes[i]))
+                        fail("Expected start_position for statement '" +
+                                searches[i] + "' to start with '" +
+                                startPrefixes[i] + "', but it actually was " +
+                                startPos);
+                    if (! stopPos.startsWith(stopPrefixes[i]))
+                        fail("Expected stop_position for statement '" +
+                                searches[i] + "' to start with '" +
+                                stopPrefixes[i] + "', but it actually was " +
+                                stopPos);
+                    break;
+                }
+            }
+            assertTrue("Found unexpected statement " + sText, foundStmt);
+        }
+        assertEquals("Captured wrong number of statements?",
+                searches.length, matchedStatements);
+    }
+
+    /**
+      * A simple test of a non-zero value for numDeletedRowsVisited.
+      */
+    public void testScanDeletedRows()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        enableXplainStyle(s);
+        String selectStatement = "select x from t";
+        JDBC.assertUnorderedResultSet(s.executeQuery(selectStatement),
+                new String[][] { {"1"},{"2"},{"4"} });
+        disableXplainStyle(s);
+
+        // There should be a CONSTRAINTSCAN result set with a SCAN PROPS
+        // which indicates that we visited 1 deleted row while scanning
+        // the index.
+        JDBC.assertFullResultSet(s.executeQuery(
+                    "select sp.scan_object_type, " +
+                    "sp.scan_type, sp.isolation_level, " +
+                    "sp.no_visited_rows, sp.no_qualified_rows, "+
+                    "sp.no_visited_pages, sp.no_visited_deleted_rows, " +
+                    "sp.no_fetched_columns, sp.bitset_of_fetched_columns, " +
+                    "sp.scan_qualifiers " +
+                    "from xpltest.sysxplain_scan_props sp " +
+                    "join xpltest.sysxplain_resultsets rs " +
+                    "on sp.scan_rs_id = rs.scan_rs_id " +
+                    "where rs.op_identifier='CONSTRAINTSCAN'"),
+                new String[][] {
+                    {"C","BTREE","RC","4","3","1","1","1","{0}","None"}});
+    }
+
+    /**
+      * A simple test of table with the wrong 'shape'.
+      */
+    public void testTableNotValid()
+        throws SQLException
+    {
+        Statement s = createStatement();
+        for (int i = 0; i < tableNames.length; i++)
+            if (hasTable("XPLTEST", tableNames[i]))
+                s.executeUpdate("drop table xpltest."+tableNames[i]);
+        s.executeUpdate("create table xpltest.sysxplain_resultsets(a int)");
+        try
+        {
+            enableXplainStyle(s);
+            fail("Expected an error from table with wrong shape");
+        }
+        catch (SQLException e)
+        {
+            assertSQLState("Expected 42X14 error for missing column",
+                    "42X14", e);
+            if (e.getMessage().indexOf("RS_ID") < 0)
+                fail("Expected message about missing column RS_ID, not " +
+                        e.getMessage());
+        }
+    }
+
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/XplainStatisticsTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=768597&r1=768596&r2=768597&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Sat Apr 25 20:21:03 2009
@@ -138,6 +138,7 @@
         suite.addTest(AnsiSignaturesTest.suite());
         suite.addTest(PredicatePushdownTest.suite());
         suite.addTest(UngroupedAggregatesNegativeTest.suite());
+        suite.addTest(XplainStatisticsTest.suite());
         suite.addTest(SelectivityTest.suite());
         // Add the XML tests, which exist as a separate suite
         // so that users can "run all XML tests" easily.

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_6.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_6.java?rev=768597&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_6.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_6.java Sat Apr 25 20:21:03 2009
@@ -0,0 +1,112 @@
+/*
+
+Derby - Class org.apache.derbyTesting.functionTests.tests.upgradeTests.Changes10_6
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements.  See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License.  You may obtain a copy of the License at
+
+   http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+*/
+package org.apache.derbyTesting.functionTests.tests.upgradeTests;
+
+import org.apache.derbyTesting.junit.SupportFilesSetup;
+
+import org.apache.derbyTesting.junit.JDBCDataSource;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Connection;
+import java.sql.CallableStatement;
+
+import javax.sql.DataSource;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+/**
+ * Upgrade test cases for 10.6.
+ * If the old version is 10.6 or later then these tests
+ * will not be run.
+ * <BR>
+    10.6 Upgrade issues
+
+    <UL>
+    <LI> testSetXplainSchemaProcedure - DERBY-2487
+    Make sure that SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA can only be run in Derby
+    10.5 and higher.
+    </UL>
+
+ */
+public class Changes10_6 extends UpgradeChange {
+
+    private static  final   String  BAD_SYNTAX = "42X01";
+
+    public Changes10_6(String name) {
+        super(name);
+    }
+
+    /**
+     * Return the suite of tests to test the changes made in 10.6.
+     * @param phase an integer that indicates the current phase in
+     *              the upgrade test.
+     * @return the test suite created.
+     */
+    public static Test suite(int phase) {
+        TestSuite suite = new TestSuite("Upgrade test for 10.6");
+
+        suite.addTestSuite(Changes10_6.class);
+        return new SupportFilesSetup((Test) suite);
+    }
+
+
+    /**
+     * Make sure that SYSCS_UTIL.SYSCS_SET_XPLAIN_STYLE can only be run in 
+     * Derby 10.5 and higher. 
+     * DERBY-2487
+     * Test added for 10.5.
+     * @throws SQLException
+     *
+     */
+    public void testSetXplainStyleProcedure() throws SQLException
+    {
+        String []xplainProcedures = {
+            "call SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA('XPLAIN')",
+            "call SYSCS_UTIL.SYSCS_SET_XPLAIN_SCHEMA('')",
+            "call SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE(1)",
+            "call SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE(0)",
+            "values SYSCS_UTIL.SYSCS_GET_XPLAIN_SCHEMA()",
+            "values SYSCS_UTIL.SYSCS_GET_XPLAIN_MODE()",
+        };
+    	Statement s;
+        //ERROR 42Y03: 'SYSCS_UTIL.SYSCS_SET_XPLAIN_MODE' is not
+        // recognized as a function or procedure.
+        switch (getPhase())
+        {
+        case PH_SOFT_UPGRADE: // In soft-upgrade cases, XPLAIN should fail:
+        case PH_POST_SOFT_UPGRADE:
+            s = createStatement();
+            for (int i = 0; i < xplainProcedures.length; i++)
+                assertStatementError("42Y03", s, xplainProcedures[i]);
+            s.close();
+            break;
+
+        case PH_HARD_UPGRADE: // After hard upgrade, XPLAIN should work:
+            s = createStatement();
+            for (int i = 0; i < xplainProcedures.length; i++)
+                s.execute(xplainProcedures[i]);
+            s.close();
+            break;
+        }
+    }
+
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_6.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeRun.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeRun.java?rev=768597&r1=768596&r2=768597&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeRun.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeRun.java Sat Apr 25 20:21:03 2009
@@ -190,6 +190,8 @@
                 	suite.addTest(Changes10_4.suite(phase));
                 if (oldMinor < 5)
                 	suite.addTest(Changes10_5.suite(phase));
+                //if (oldMinor < 6)
+                //	suite.addTest(Changes10_6.suite(phase));
             }
             
             // Add DatabaseMetaData tests. Since metadata



Mime
View raw message