Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 83730 invoked from network); 13 Aug 2008 01:12:47 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 13 Aug 2008 01:12:47 -0000 Received: (qmail 47355 invoked by uid 500); 13 Aug 2008 01:12:46 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 47336 invoked by uid 500); 13 Aug 2008 01:12:46 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 47327 invoked by uid 99); 13 Aug 2008 01:12:46 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 12 Aug 2008 18:12:46 -0700 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 13 Aug 2008 01:11:57 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 64840238898F; Tue, 12 Aug 2008 18:12:25 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r685408 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/ master/DerbyNet/ master/DerbyNetClient/ suites/ tests/lang/ Date: Wed, 13 Aug 2008 01:12:24 -0000 To: derby-commits@db.apache.org From: myrnavl@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20080813011225.64840238898F@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: myrnavl Date: Tue Aug 12 18:12:24 2008 New Revision: 685408 URL: http://svn.apache.org/viewvc?rev=685408&view=rev Log: DERBY-3802; convert lang.optimizerOverrides.sql to junit test. Patch contributed by Junjie Peng Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptimizerOverridesTest.java (with props) Removed: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/optimizerOverrides.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClientUseprocess.exclude db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetUseprocess.exclude db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClientUseprocess.exclude URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClientUseprocess.exclude?rev=685408&r1=685407&r2=685408&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClientUseprocess.exclude (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetClientUseprocess.exclude Tue Aug 12 18:12:24 2008 @@ -12,7 +12,6 @@ # derbynet/sysinfo.java - needs other properties # derbynet/sysinfo_withproperties.java - needs other properties # following fail for various reasons, many related to not being able to connect: -# lang/optimizerOverrides.sql # lang/wisconsin.java # jdbcapi/derbyStress.java # jdbcapi/maxfieldsize.java Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetUseprocess.exclude URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetUseprocess.exclude?rev=685408&r1=685407&r2=685408&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetUseprocess.exclude (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/DerbyNetUseprocess.exclude Tue Aug 12 18:12:24 2008 @@ -10,7 +10,6 @@ # derbynet/runtimeinfo.java - fails with already existing threads (maybe intermittent) # derbynet/sysinfo.java - fails because not access to java.class.path # derbynet/sysinfo_withproperties.java - fails because not access to java.class.path -# lang/optimizerOverrides.sql - fails... # lang/wisconsin.java - checks depend on dir structure # jdbcapi/maxfieldsize.java - fails...??? (but not when run by itself) # Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?rev=685408&r1=685407&r2=685408&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Tue Aug 12 18:12:24 2008 @@ -30,7 +30,6 @@ lang/maxMemPerTab.java lang/nestedCommit.sql lang/openScans.sql -lang/optimizerOverrides.sql lang/orderbyElimination.sql lang/outerjoin.sql lang/outparams.java Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall?rev=685408&r1=685407&r2=685408&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall Tue Aug 12 18:12:24 2008 @@ -8,7 +8,6 @@ derbynet/testProtocol.java derbynet/timeslice.java jdbcapi/metadataMultiConn.java -lang/optimizerOverrides.sql lang/holdCursorIJ.sql lang/wisconsin.java lang/ShutdownDatabase.java Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptimizerOverridesTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptimizerOverridesTest.java?rev=685408&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptimizerOverridesTest.java (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptimizerOverridesTest.java Tue Aug 12 18:12:24 2008 @@ -0,0 +1,546 @@ +/* + + Derby - Class org.apache.derbyTesting.functionTests.tests.lang.OptimizerOverridingTest + + 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.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; + +import junit.framework.Test; + +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; +import org.apache.derbyTesting.junit.TestConfiguration; + +/** + * Test the optimizer overrides. + */ +public class OptimizerOverridesTest extends BaseJDBCTestCase { + private static final String[][] FULL_TABLE = + new String[][]{ + {"1", "1", "1"}, + {"2", "2", "2"}, + {"3", "3", "3"}, + {"4", "4", "4"}, + }; + + public OptimizerOverridesTest(String name) { + super(name); + } + + public static Test suite(){ + Test suite = TestConfiguration.defaultSuite( + OptimizerOverridesTest.class, false); + suite = new CleanDatabaseTestSetup(suite){ + + /* Create tables, indices and views. + * @see org.apache.derbyTesting.junit.CleanDatabaseTestSetup#decorateSQL(java.sql.Statement) + */ + protected void decorateSQL(Statement st) throws SQLException { + st.getConnection().setAutoCommit(false); + + st.addBatch("create table t1 (c1 int, " + + "c2 int, c3 int," + + " constraint cons1 primary key(c1, c2))"); + st.addBatch("create table t2 (c1 int not null, " + + "c2 int not null, c3 int, " + + "constraint cons2 unique(c1, c2))"); + + st.addBatch("insert into t1 values (1, 1, 1), " + + "(2, 2, 2), (3, 3, 3), (4, 4, 4)");; + st.addBatch("insert into t2 values (1, 1, 1), " + + "(2, 2, 2), (3, 3, 3), (4, 4, 4)"); + + st.addBatch("create index t1_c1c2c3 on t1(c1, c2, c3)"); + st.addBatch("create index t1_c3c2c1 on t1(c3, c2, c1)"); + st.addBatch("create index t1_c1 on t1(c1)"); + st.addBatch("create index t1_c2 on t1(c2)"); + st.addBatch("create index t1_c3 on t1(c3)"); + st.addBatch("create index \"t1_c2c1\" on t1(c2, c1)"); + st.addBatch("create index t2_c1c2c3 on t2(c1, c2, c3)"); + st.addBatch("create index t2_c3c2c1 on t2(c3, c2, c1)"); + st.addBatch("create index t2_c1 on t2(c1)"); + st.addBatch("create index t2_c2 on t2(c2)"); + st.addBatch("create index t2_c3 on t2(c3)"); + + st.addBatch("create view v1 as select * from t1 " + + "--derby-properties index = t1_c1"); + st.addBatch("create view v2 as select t1.* from t1, t2"); + st.addBatch("create view v3 as select * from v1"); + st.addBatch("create view neg_v1 as select * from t1" + + " --derby-properties asdf = fdsa"); + + st.executeBatch(); + } + }; + + return suite; + } + + /** + * Negative tests for bad formats. + */ + public void testBadFormats() throws SQLException{ + Statement st = createStatement(); + + assertStatementError("42X01", st, + "select \n" + + "-- derby-properties index = t1_c1 \n" + + "* from t1"); + + assertStatementError("42X01", st, + "select * -- derby-properties index = t1_c1 \n" + + "from t1"); + + assertStatementError("42X01", st, + "select * -- derby-properties\n" + + " index = t1_c1 from t1"); + + st.close(); + } + + /** + * Test bad properties. + */ + public void testBadProperties() throws SQLException{ + Statement st = createStatement(); + + assertStatementError("42Y44", st, "select * from t1" + + " --derby-properties asdf = i1"); + + assertStatementError("42Y44", st, "select * from t1" + + " exposedname --derby-properties asdf = i1"); + + assertStatementError("42Y44", st, + "select * from neg_v1"); + + assertStatementError("42Y44", st, + "select * from t1 --derby-properties i = a\n" + + "left outer join t2 on 1=1"); + + assertStatementError("42Y44", st, + "select * from t1 left outer join t2 " + + "--derby-properties i = t1_c1\n on 1=1"); + + assertStatementError("42Y46", st, + "select * from t1 left outer join t2 " + + "--derby-properties index = t1_c1\n on 1=1"); + + assertStatementError("42Y46", st, + "select * from t1 right outer join t2 " + + "--derby-properties index = t1_c1\n on 1=1"); + + st.close(); + } + + /** + * Test not existing values of property. + */ + public void testNonExistingPropertyValues() + throws SQLException{ + Statement st = createStatement(); + + assertStatementError("42Y46", st, "select * from t1 " + + "--derby-properties index = t1_notexists"); + + assertStatementError("42Y46", st, + "select * from t1 exposedname " + + "--derby-properties index = t1_notexists"); + + assertStatementError("42Y48", st, "select * from t1 " + + "--derby-properties constraint = t1_notexists"); + + assertStatementError("42Y48", st, + "select * from t1 exposedname " + + "--derby-properties constraint = t1_notexists"); + + assertStatementError("42Y56", st, + "select * from t1 a, t1 b " + + "--derby-properties joinStrategy = asdf"); + + st.close(); + } + + /** + * Make sure following fragments get treated as comments. + */ + public void testFragmentsAsComments() throws SQLException{ + String[] frags = {"--d", "-- de", "-- der", + "--derb", "--derby comment", + "-- derby another comment", "--derby-", + "--derby-p", "--derby-pr", "--derby-pro", + "--derby-prop", "--derby-prope", "--derby-proper", + "-- derby-propert", "-- derby-properti", + "-- derby-propertie", "-- derby-propertiex" + }; + + Statement st = createStatement(); + + for(int i = 0; i < frags.length; i++) + JDBC.assertFullResultSet( + st.executeQuery( + frags[i] + "\n VALUES 1 "), + new String [][] {{"1"}}); + + st.close(); + } + + /** + * Test both index and constraint. + */ + public void testMixedIndexAndConstraint() + throws SQLException{ + Statement st = createStatement(); + + assertStatementError("42Y50", st, "select * from t1" + + " --derby-properties index = t1_c1, " + + "constraint = cons1"); + + assertStatementError("42Y50", st, "select * from t1" + + " exposedname --derby-properties " + + "index = t1_c1, constraint = cons1"); + + st.close(); + } + + /** + * Index which includes columns in for update of list. + */ + public void testPropertyForUpdate() throws SQLException{ + Statement st = createStatement(ResultSet.TYPE_FORWARD_ONLY, + ResultSet.CONCUR_UPDATABLE); + + JDBC.assertFullResultSet(st.executeQuery( + "select * from t1 " + + "--derby-properties index = t1_c1\n" + + "for update"), FULL_TABLE); + + JDBC.assertFullResultSet(st.executeQuery( + "select * from t1 exposedname " + + "--derby-properties index = t1_c1\n" + + "for update"), FULL_TABLE); + + JDBC.assertFullResultSet(st.executeQuery( + "select * from t1 " + + "--derby-properties index = t1_c1\n" + + "for update of c2, c1"), FULL_TABLE); + + JDBC.assertFullResultSet(st.executeQuery( + "select * from t1 exposedname " + + "--derby-properties index = t1_c1\n" + + "for update of c2, c1"), FULL_TABLE); + + JDBC.assertFullResultSet(st.executeQuery( + "select * from t1 " + + "--derby-properties constraint = cons1\n" + + "for update"), FULL_TABLE); + + JDBC.assertFullResultSet(st.executeQuery( + "select * from t1 exposedname " + + "--derby-properties constraint = cons1\n" + + "for update"), FULL_TABLE); + + JDBC.assertFullResultSet(st.executeQuery( + "select * from t1 " + + "--derby-properties constraint = cons1\n" + + "for update of c2, c1"), FULL_TABLE); + + JDBC.assertFullResultSet(st.executeQuery( + "select * from t1 exposedname " + + "--derby-properties constraint = cons1\n" + + "for update of c2, c1"), FULL_TABLE); + + st.close(); + } + + public void testInvalidJoinStrategy() throws SQLException{ + Statement st = createStatement(); + + assertStatementError("42Y50", st, "select * from t1" + + " --derby-properties index = t1_c1, " + + "constraint = cons1"); + + assertStatementError("42Y50", st, "select * from t1" + + " exposedname --derby-properties " + + "index = t1_c1, constraint = cons1"); + + st.close(); + } + + /** + * Verify that statements are dependent + * on specified index. + */ + public void testDependenceOnIndex() throws SQLException{ + PreparedStatement ps = + prepareStatement("select * from t1 " + + "--derby-properties index = t1_c1"); + + JDBC.assertFullResultSet(ps.executeQuery(), + new String[][]{ + {"1", "1", "1"}, + {"2", "2", "2"}, + {"3", "3", "3"}, + {"4", "4", "4"}, + }); + + Statement st = createStatement(); + st.executeUpdate("drop index t1_c1"); + + assertStatementError("42Y46", ps); + + ps.close(); + + //add index to avoid exception when deleted in tearDown(). + st.executeUpdate("create index t1_c1 on t1(c1)"); + + st.close(); + } + + /** + * Verify that statements are dependent + * on specified constraint. + */ + public void testDependenceOnConstraint() + throws SQLException{ + PreparedStatement ps = + prepareStatement("select * from t1 " + + "--derby-properties constraint = cons1"); + + JDBC.assertFullResultSet(ps.executeQuery(), FULL_TABLE); + + Statement st = createStatement(); + st.executeUpdate("alter table t1 drop constraint cons1"); + + assertStatementError("42Y48", ps); + + //add cons1 to restore the test environment. + st.executeUpdate("alter table t1 " + + "add constraint cons1 primary key(c1, c2)"); + + ps.close(); + + st.close(); + } + + /** + * Test case insensitivity, spelling sensitivity + * and delimited index. + */ + public void testSpell() throws SQLException{ + Statement st = createStatement(); + + st.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)"); + + //the token derby-properties is case insensitive. + JDBC.assertFullResultSet( + st.executeQuery("select * from t1" + + " --DeRbY-pRoPeRtIeS index = t1_c1"), + FULL_TABLE); + RuntimeStatisticsParser rtsp = + SQLUtilities.getRuntimeStatisticsParser(st); + assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1")); + + //-- misspell derby-properties and make sure that + //it gets treated as a regular comment + //rather than optimizer override + JDBC.assertFullResultSet( + st.executeQuery("select * from t1 " + + " --DeRbY-pRoPeRtIeAAAA index = t1_c1"), + FULL_TABLE); + rtsp = SQLUtilities.getRuntimeStatisticsParser(st); + assertTrue("not using t1_c1, but what derby thinks is best index.", + rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1C2C3")); + + + //"--DeRbY-pRoPeRtIeSAAAA index = t1_c1" is + //treated as "--DeRbY-pRoPeRtIeS AAAA index = t1_c1" + assertStatementError("42Y44", st, "select * from t1 " + + " --DeRbY-pRoPeRtIeSAAAA index = t1_c1"); + + //-- force index, delimited identifier + JDBC.assertFullResultSet( + st.executeQuery("select * from t1 " + + "--derby-properties index = \"t1_c2c1\""), + FULL_TABLE); + rtsp = SQLUtilities.getRuntimeStatisticsParser(st); + assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "t1_c2c1")); + + //If the property spelled wrong gets treated + //as an optimizer override, the following test will fail. + assertStatementError("42Y46", st, "select * from t1 " + + " --DeRbY-pRoPeRtIeS index = t1_notexisting"); + + st.close(); + } + + public void testNullValue() throws SQLException{ + Statement st = createStatement(); + + st.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)"); + + JDBC.assertFullResultSet( + st.executeQuery("select * from t1 " + + "--derby-properties index = null"), FULL_TABLE); + RuntimeStatisticsParser rtsp = + SQLUtilities.getRuntimeStatisticsParser(st); + assertTrue("force table scan", rtsp.usedTableScan()); + + JDBC.assertFullResultSet( + st.executeQuery("select * from t1 " + + "--derby-properties constraint = null"), FULL_TABLE); + + assertStatementError("42Y56", st, "select * from t1 " + + "--derby-properties joinStrategy = null"); + + st.close(); + } + + public void testJoin() throws SQLException{ + Statement st = createStatement(); + + st.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)"); + JDBC.assertFullResultSet( + st.executeQuery("select 1 from t1 a" + + " --derby-properties index = t1_c1\n" + + ",t2 b --derby-properties index = t2_c2"), + new String[][]{ + {"1"}, {"1"}, {"1"}, {"1"}, + {"1"}, {"1"}, {"1"}, {"1"}, + {"1"}, {"1"}, {"1"}, {"1"}, + {"1"}, {"1"}, {"1"}, {"1"}, + }); + RuntimeStatisticsParser rtsp = + SQLUtilities.getRuntimeStatisticsParser(st); + assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1")); + assertTrue(rtsp.usedSpecificIndexForIndexScan("T2", "T2_C2")); + + JDBC.assertFullResultSet( + st.executeQuery("select 1 from " + + " --derby-properties joinOrder=fixed\n" + + "t1, t2 where t1.c1 = t2.c1"), + new String[][]{{"1"}, {"1"}, {"1"}, {"1"}, } + ); + + JDBC.assertFullResultSet( + st.executeQuery("select * from t1" + + " --derby-properties index = t1_c1\n" + + "left outer join t2 " + + "--derby-properties index = t2_c2\n" + + "on t1.c1 = t2.c1"), + new String[][]{ + {"1", "1", "1", "1", "1", "1"}, + {"2", "2", "2", "2", "2", "2"}, + {"3", "3", "3", "3", "3", "3"}, + {"4", "4", "4", "4", "4", "4"}, + }); + rtsp = + SQLUtilities.getRuntimeStatisticsParser(st); + assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1")); + assertTrue(rtsp.usedSpecificIndexForIndexScan("T2", "T2_C2")); + + st.close(); + } + + /** + * Comparisons that can't get pushed down. + */ + public void testComparision() throws SQLException{ + Statement st = createStatement(); + + st.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)"); + + JDBC.assertFullResultSet( + st.executeQuery("select * from t1" + + " --derby-properties index = t1_c1\n" + + "where c1 = c1"), FULL_TABLE); + RuntimeStatisticsParser rtsp = + SQLUtilities.getRuntimeStatisticsParser(st); + assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1")); + + JDBC.assertFullResultSet( + st.executeQuery("select * from t1" + + " --derby-properties index = t1_c1\n" + + "where c1 = c2"), FULL_TABLE); + rtsp = SQLUtilities.getRuntimeStatisticsParser(st); + assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1")); + + JDBC.assertFullResultSet( + st.executeQuery("select * from t1" + + " --derby-properties index = t1_c1\n" + + "where c1 + 1 = 1 + c1"), FULL_TABLE); + rtsp = SQLUtilities.getRuntimeStatisticsParser(st); + assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1")); + + st.close(); + } + + public void testNestedLoopJoinStrategy() + throws SQLException{ + Statement st = createStatement(); + + st.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)"); + + JDBC.assertFullResultSet( + st.executeQuery("select * from t1 a, t1 b " + + "--derby-properties joinStrategy = nestedloop\n" + + "where a.c1 = b.c1"), + new String[][]{ + {"1", "1", "1", "1", "1", "1"}, + {"2", "2", "2", "2", "2", "2"}, + {"3", "3", "3", "3", "3", "3"}, + {"4", "4", "4", "4", "4", "4"}, + }); +// RuntimeStatisticsParser rtsp = +// SQLUtilities.getRuntimeStatisticsParser(st); +// assertTrue(rtsp.usedHashJoin()); +// assertTrue("not using t1_c1, but what derby thinks is best index.", +// rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1C2C3")); + + st.close(); + } + + /** + *Negative test. insertModeValue is not available to a user + *and hence will give a syntax error. There are some + *undocumented properties which are allowed within Derby + *engine only and insertModeValue is one of them. + */ + public void testInsertModeValue() throws SQLException{ + Statement st = createStatement(); + + st.executeUpdate("create table temp1 (c1 int, c2 int, " + + "c3 int, constraint temp1cons1 primary key(c1, c2))"); + + assertStatementError("42X01", st, + "insert into temp1 (c1,c2,c3)" + + " -- derby-properties insertModeValue=replace\n" + + "select * from t1"); + + dropTable("temp1"); + + st.close(); + } +} Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptimizerOverridesTest.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=685408&r1=685407&r2=685408&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 Tue Aug 12 18:12:24 2008 @@ -174,6 +174,7 @@ suite.addTest(NullsTest.suite()); suite.addTest(ArithmeticTest.suite()); suite.addTest(ConstantExpressionTest.suite()); + suite.addTest(OptimizerOverridesTest.suite()); return suite; }