db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r805275 [2/3] - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/ suites/ tests/lang/
Date Tue, 18 Aug 2009 04:38:57 GMT
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InbetweenTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InbetweenTest.java?rev=805275&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InbetweenTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InbetweenTest.java Tue Aug 18 04:38:57 2009
@@ -0,0 +1,4504 @@
+
+/*
+Derby - Class org.apache.derbyTesting.functionTests.tests.lang.InbetweenTest
+
+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.ResultSetMetaData;
+import java.sql.Statement;
+import java.sql.CallableStatement;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.SQLWarning;
+
+
+import java.sql.Timestamp;
+import java.sql.Types;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
+import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+public final class InbetweenTest extends BaseJDBCTestCase {
+
+    /**
+     * Public constructor required for running test as standalone JUnit.
+     */
+    public InbetweenTest(String name)
+    {
+        super(name);
+    }
+
+    public static Test suite()
+    {
+        TestSuite suite = new TestSuite("InbetweenTest Test");
+        suite.addTest(DatabasePropertyTestSetup.singleProperty(
+                TestConfiguration.defaultSuite(InbetweenTest.class),
+                "derby.language.statementCacheSize", "0",true));
+        return suite;
+    }
+
+    ResultSet rs = null;
+    ResultSetMetaData rsmd;
+    SQLWarning sqlWarn = null;
+    PreparedStatement pSt;
+    CallableStatement cSt;
+    Statement st;
+    Connection conn;
+    String[][] expRS;
+    String[] expColNames;
+    
+    private void createTestObjects(Statement st) throws SQLException {
+        conn = getConnection();
+        conn.setAutoCommit(false);
+        CleanDatabaseTestSetup.cleanDatabase(conn, false);
+        
+        st.executeUpdate("set isolation to rr");
+        
+        st.executeUpdate(
+            " CREATE FUNCTION ConsistencyChecker() RETURNS VARCHAR(2000)"
+                + "EXTERNAL NAME "
+                + "'org.apache.derbyTesting.functionTests.util.T_Consis"
+                + "tencyChecker.runConsistencyChecker'"
+                + "LANGUAGE JAVA PARAMETER STYLE JAVA");
+        
+        // Create the tables
+       
+        st.executeUpdate(
+            "create table s (i int)");
+        
+        st.executeUpdate(
+            " create table t (i int, s smallint, c char(10), v "
+                + "varchar(50), "
+                + "d double precision, r real, e date, t time, p timestamp)");
+        
+        st.executeUpdate(
+                " create table test (i int, d double precision)");
+        
+        st.executeUpdate(
+            "create table big(i int, c char(10))");
+        
+        st.executeUpdate(
+            "create table bt1 (i int, c char(5), de decimal(4, 1))");
+        
+        st.executeUpdate(
+            " create table bt2 (i int, d double, da date, t "
+                + "time, tp timestamp, vc varchar(10))");
+        
+        // Populate the tables
+        
+        st.executeUpdate("insert into s values (1)");        
+        st.executeUpdate(" insert into s values (1)");                    
+        st.executeUpdate(" insert into s values (2)");
+        
+        st.executeUpdate(
+            " insert into t values (null, null, null, null, "
+                + "null, null, null, null, null)");
+        
+        st.executeUpdate(
+            " insert into t values (0, 100, 'hello', 'everyone "
+                + "is here', 200.0e0,"
+                + "	300.0e0, '1992-01-01','12:30:30',"
+                +"'"+Timestamp.valueOf("1992-01-01 12:30:30")+"')");
+        
+        st.executeUpdate(
+            "insert into t values (-1, -100, 'goodbye', "
+                + "'everyone is there', -200.0e0,"
+                + "	-300.0e0, '1992-01-02', '12:30:59',"
+                +"'"+Timestamp.valueOf("1992-01-02 12:30:59")+"')");
+        
+        st.executeUpdate(" insert into test values (2, 4.0)");                    
+        st.executeUpdate(" insert into test values (3, 10.0)");            
+        st.executeUpdate(" insert into test values (4, 12.0)");                    
+        st.executeUpdate(" insert into test values (5, 25.0)");                   
+        st.executeUpdate(" insert into test values (10, 100.0)");      
+        st.executeUpdate(" insert into test values (-6, 36)");
+        
+        st.executeUpdate(
+            " insert into big values "
+                + "	(1, '1'), (2, '2'), (3, '3'), (4, '4'), (5, '5'), "
+                + "(6, '6'), (7, '7'), (8, '8'), (9, '9'), (10, '10'), "
+                + "	(11, '11'), (12, '12'), (13, '13'), (14, '14'), "
+                + "(15, '15'), (16, '16'), (17, '17'), (18, '18'), "
+                + "(19, '19'), (20, '20'), "
+                + "	(21, '21'), (22, '22'), (23, '23'), (24, '24'), "
+                + "(25, '25'), (26, '26'), (27, '27'), (28, '28'), "
+                + "(29, '29'), (30, '30')");        
+        
+        st.executeUpdate(
+            " insert into bt1 values (1, 'one', null), (2, "
+            + "'two', 22.2), (3, 'three', null),"
+            + "  (7, 'seven', null), (8, 'eight', 2.8), (9, "
+            + "'nine', null), (3, 'trois', 21.2)");
+        
+        st.executeUpdate(
+            " insert into bt1 (i) values 10, 11, 12, 13, 14, 15, "
+            + "16, 17, 18, 19, 20");
+        
+        assertUpdateCount(st, 11,
+            " update bt1 set c = cast (i as char(5)) where i >= 10");
+        
+        assertUpdateCount(st, 6,
+            " update bt1 set de = cast (i/2.8 as decimal(4,1)) "
+            + "where i >= 10 and 2 * (cast (i as double) / 2.0) - "
+            + "(i / 2) = i / 2");
+        
+        st.executeUpdate(
+            " insert into bt2 values (8, -800.0, '1992-03-22', "
+            + "'03:22:28', '"+Timestamp.valueOf("1992-03-22 03:22:28.0")+"',"
+            +"'2992-01-02')");
+        
+        st.executeUpdate(
+            " insert into bt2 values (1, 200.0, '1998-03-22',"
+            + "'13:22:28', '"+Timestamp.valueOf("1998-03-22 03:22:28.0")+"',"
+            +"'3999-08-08')");
+        
+        st.executeUpdate(
+            " insert into bt2 values (-8, 800, '3999-08-08', "
+            + "'02:28:22', '"+Timestamp.valueOf("3999-08-08 02:28:22.0")+"',"
+            +"'1992-01-02')");
+        
+        st.executeUpdate(
+            " insert into bt2 values (18, 180.00, '2007-02-23', "
+            + "'15:47:27', null, null)");
+        
+        st.executeUpdate(
+            " insert into bt2 values (22, 202.010, '2007-02-23', "
+            + "'15:47:27', null, null)");
+        
+        st.executeUpdate(
+            " insert into bt2 values (23, 322.002, null, "
+            + "'15:47:28', null, null)");
+        
+        st.executeUpdate(
+            " insert into bt2 values (28, 82, null, '15:47:28', "
+            + "'"+Timestamp.valueOf("0000-00-00 15:47:28.0")+"', null)");
+        
+        //create indexes
+        
+        st.executeUpdate(" create index ix_big_i on big (i)");       
+        st.executeUpdate(" create index bt1_ixi on bt1 (i)");       
+        st.executeUpdate(" create index bt1_ixde on bt1 (de)");       
+        st.executeUpdate(" create index bt1_ixic on bt1 (i, c)");       
+        st.executeUpdate(" create index bt2_ixd on bt2 (d)");        
+        st.executeUpdate(" create index bt2_ixda on bt2 (da)");        
+        st.executeUpdate(" create index bt2_ixvc on bt2 (vc)");
+             
+    }
+    
+    public void testBetween() throws Exception {
+        
+        st = createStatement();        
+        createTestObjects(st);
+                
+        // BETWEEN negative tests type mismatches
+        
+        //Comparisons between 'INTEGER' and 'DATE' are not supported
+        assertStatementError("42818", st,
+            "select * from t where i between i and e");
+        
+        //Comparisons between 'INTEGER' and 'TIME' are not supported
+        assertStatementError("42818", st,
+            " select * from t where i between i and t");
+        
+        //Comparisons between 'INTEGER' and 'TIMESTAMP' are not supported
+        assertStatementError("42818", st,
+            " select * from t where i between i and p");
+        
+        //Comparisons between 'DATE' and 'TIMESTAMP' are not supported
+        assertStatementError("42818", st,
+            " select * from t where e between p and p");
+        
+        //Comparisons between 'INTEGER' and 'DATE' are not supported
+        assertStatementError("42818", st,
+            " select * from t where 1 between e and p");
+        
+        // between null and i
+        
+        assertStatementError("42X01", st,
+            "select * from t where i between null and i");
+        
+        // cardinality violation on a subquery
+        //Scalar subquery is only allowed to return a single row
+        assertStatementError("21000", st,
+            "select * from t where i between i and (select i from s)");
+        
+        // all parameters
+        //It is not allowed for both operands of 'BETWEEN' to be ? parameters
+        assertStatementError("42X35", st,
+            "select * from t where ? between ? and ?");
+        
+        // positive tests type comparisons
+        
+        rs = st.executeQuery(
+            "select i from t where i between s and r");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where i between r and d");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where s between i and r");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select i from t where s between r and d");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where r between s and i");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where d between s and i");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where i between 40e1 and 50e1");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where s between 40e1 and 50e1");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where c between c and v");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where 40e1 between i and s");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where 'goodbye' between c and c");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"-1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select i from t where "
+            + "'"+Timestamp.valueOf("1992-01-02 12:30:59")+"'"
+            +"between p and p");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"-1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        //between 2 and 1
+        
+        rs = st.executeQuery(
+            "select * from t where i between 2 and 1");
+        
+        expColNames = new String [] {"I","S","C","V","D","R","E","T","P"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]{};
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select * from t where not i not between 2 and 1");
+        
+        expColNames = new String [] {"I","S","C","V","D","R","E","T","P"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]{};
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select * from t where not i between 2 and 1");
+        
+        expColNames = new String [] {"I","S","C","V","D","R","E","T","P"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"0","100","hello","everyone is here","200.0","300.0",
+                     "1992-01-01","12:30:30","1992-01-01 12:30:30.0"},
+            {"-1","-100","goodbye","everyone is there","-200.0","-300.0",
+                     "1992-01-02","12:30:59","1992-01-02 12:30:59.0"},       
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select * from t where i not between 2 and 1");
+        
+        expColNames = new String [] {"I","S","C","V","D","R","E","T","P"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"0","100","hello","everyone is here","200.0","300.0",
+                     "1992-01-01","12:30:30","1992-01-01 12:30:30.0"},
+            {"-1","-100","goodbye","everyone is there","-200.0","-300.0",
+                     "1992-01-02","12:30:59","1992-01-02 12:30:59.0"}      
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        //between arbitrary expressions
+        
+        rs = st.executeQuery(
+            "select * from test where sqrt(d) between 5 and 10");
+        
+        expColNames = new String [] {"I","D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"5","25.0"},
+            {"10","100.0"},
+            {"-6","36.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select * from test where (i+d) between 20 and 50");
+        
+        expColNames = new String [] {"I","D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"5","25.0"},
+            {"-6","36.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select * from test where {fn abs (i)} between 5 and 8");
+        
+        expColNames = new String [] {"I","D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"5","25.0"},
+            {"-6","36.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+   
+        rs = st.executeQuery(
+            "select * from test where (i+d) not between 20 and 50");
+        
+        expColNames = new String [] {"I","D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"2","4.0"},
+            {"3","10.0"},
+            {"4","12.0"},
+            {"10","100.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select * from test where sqrt(d) not between 5 and 20");
+        
+        expColNames = new String [] {"I","D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"2","4.0"},
+            {"3","10.0"},
+            {"4","12.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+  
+        //not (test clone() once its implemented)
+        
+        rs = st.executeQuery(
+            "select i from t where i not between i and i");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]{};
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select i from t where s not between s and s");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]{};
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select i from t where c not between c and c");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]{};
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select i from t where v not between v and v");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]{};
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select i from t where d not between d and d");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]{};
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select i from t where r not between r and r");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]{};
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select i from t where e not between e and e");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]{};
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select i from t where t not between t and t");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]{};
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select i from t where p not between p and p");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]{};
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+         
+        // between complex expressions
+        
+        rs = st.executeQuery(
+            "select i from t where s between (select i from s where i = 2)"
+            +"and (select 100 from s where i = 2)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select * from t where i between i and (select max(i) from s)");
+        
+        expColNames = new String [] {"I","S","C","V","D","R","E","T","P"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"0","100","hello","everyone is here","200.0","300.0",
+                     "1992-01-01","12:30:30","1992-01-01 12:30:30.0"},
+            {"-1","-100","goodbye","everyone is there","-200.0","-300.0",
+                     "1992-01-02","12:30:59","1992-01-02 12:30:59.0"}      
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+
+        //subquery between
+        
+        rs = st.executeQuery(
+            "select i from t where (select i from s where i = 2) between 1 and 2");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"0"},
+            {"-1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        //parameters
+        
+        PreparedStatement q1 = prepareStatement(
+                "select i from t where ? between 2 and 3");
+        PreparedStatement q2 = prepareStatement(
+                "select i from t where ? between ? and 3");
+        PreparedStatement q3 = prepareStatement(
+                "select i from t where ? between 2 and ?");
+        PreparedStatement q4 = prepareStatement(
+                "select i from t where i between ? and ?");
+        
+        rs = st.executeQuery("values (2)");
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1;
+                i <= rsmd.getColumnCount(); i++) {
+            q1.setObject(i, rs.getObject(i));
+        }
+        rs = q1.executeQuery();
+        expColNames = new String[]{"I"};
+
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"0"},
+            {"-1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery("values (2, 2)");
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1;
+                i <= rsmd.getColumnCount(); i++) {
+            q2.setObject(i, rs.getObject(i));
+        }
+        rs = q2.executeQuery();
+        expColNames = new String[]{"I"};
+
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"0"},
+            {"-1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery("values (2, 3)");
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1;
+                i <= rsmd.getColumnCount(); i++) {
+            q3.setObject(i, rs.getObject(i));
+        }
+        rs = q3.executeQuery();
+        expColNames = new String[]{"I"};
+
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"0"},
+            {"-1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery("values (0, 1)");
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1;
+                i <= rsmd.getColumnCount(); i++) {
+            q4.setObject(i, rs.getObject(i));
+        }
+        rs = q4.executeQuery();
+        expColNames = new String[]{"I"};
+
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        q1.close();
+        q2.close();
+        q3.close();
+        q4.close();
+      
+        assertUpdateCount(st, 1, "update s set i = 5 where i between 2 and 3");
+
+        rs = st.executeQuery(
+            "select * from s");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"},
+            {"1"},
+            {"5"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        //delete - where clause 
+        
+        assertUpdateCount(st, 1, "delete from s where i between 3 and 5");
+
+        rs = st.executeQuery(
+            "select * from s");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"},
+            {"1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+
+        //check consistency of scans, etc
+        
+        if(usingEmbedded()){
+            rs = st.executeQuery("values ConsistencyChecker()");
+
+            assertTrue("Consistency checker returned no data", rs.next());
+            String line1 = rs.getString(1);
+            assertTrue("Expected 'No open scans, etc.', not: " + line1,
+                    line1.startsWith( "No open scans, etc.") );
+        } 
+        
+       assertUpdateCount(st, 0, "drop table s"); 
+        
+       st.close();
+    }
+
+    public void testInList() throws SQLException {
+
+        st = createStatement();
+        createTestObjects(st);     
+        
+        //recreate s as ss
+        
+        st.executeUpdate("create table ss (i int)");
+        st.executeUpdate("insert into ss values (1)");
+        st.executeUpdate("insert into ss values (1)");
+        st.executeUpdate("insert into ss values (2)");
+            
+        //negative tests
+        
+        //empty list  
+        assertStatementError("42X01",st,
+                "select i from t where i in ()");    
+        //null in list
+        assertStatementError("42X01",st,
+                "select i from t where i in (null)");
+        //cardinality violation from subquery
+        //subquery is only allowed to return a single row
+        assertStatementError("21000",st,
+                "select i from t where i in (1, 3, 5, 6, (select i from ss))");
+        //type mismatches
+        //Comparisons between 'INTEGER' and 'DATE' are not supported
+        assertStatementError("42818",st,
+                "select i from t where i in (i, i, e)");
+        //Comparisons between 'INTEGER' and 'TIME' are not supported
+        assertStatementError("42818",st,
+                "select i from t where i in (i, i, t)");
+        //Comparisons between 'INTEGER' and 'TIMESTAMP' are not supported
+        assertStatementError("42818",st,
+                "select i from t where i in (i, i, p)");
+        //Comparisons between 'DATE' and 'TIMESTAMP' are not supported
+        assertStatementError("42818",st,
+                "select i from t where e in (e, p, e)");
+        //Comparisons between 'INTEGER' and 'TIMESTAMP' are not supported
+        assertStatementError("42818",st,
+                "select i from t where 1 in (p, 2, 1)");
+
+        //positive tests
+        //type comparisons
+        
+        rs = st.executeQuery(
+            "select i from t where i in (s, r, i, d, 40e1)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"0"},
+            {"-1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select s from t where s in (s, r, i, d, 40e1)");
+        
+        expColNames = new String [] {"S"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"100"},
+            {"-100"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rs = st.executeQuery(
+            "select r from t where r in (s, r, i, d, 40e1)");
+        
+        expColNames = new String [] {"R"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"300.0"},
+            {"-300.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select d from t where d in (s, r, i, d, 40e1)");
+        
+        expColNames = new String [] {"D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"200.0"},
+            {"-200.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select i from t where 40e1 in (s, r, i, d, 40e1)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"0"},
+            {"-1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select c from t where c in (c, v, 'goodbye')");
+        
+        expColNames = new String [] {"C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"hello"},
+            {"goodbye"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select v from t where v in (c, v, 'goodbye')");
+        
+        expColNames = new String [] {"V"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"everyone is here"},
+            {"everyone is there"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+       
+        rs = st.executeQuery(
+            "select i from t where 'goodbye' in (c, v, 'goodbye')");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"0"},
+            {"-1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select i from t where '"+Timestamp.valueOf("1992-01-01 12:30:30.0")+
+            "'in (p,'"+Timestamp.valueOf("1992-01-01 12:30:30.0")+"')");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"0"},
+            {"-1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "select p from t where p in (p, "
+            +"'"+Timestamp.valueOf("1992-01-02 12:30:59")+"')");
+        
+        expColNames = new String [] {"P"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1992-01-01 12:30:30.0"},
+            {"1992-01-02 12:30:59.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // not (test clone() once its implemented)
+        
+        rs = st.executeQuery(
+            "select i from t where i not in (i, i)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where s not in (s, s)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where c not in (c, c)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where v not in (v, v)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where d not in (d, d)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where r not in (r, r)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where e not in (e, e)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where t not in (t, t)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        rs = st.executeQuery(
+            " select i from t where p not in (p, p)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        JDBC.assertDrainResults(rs, 0);
+        
+        // more nots
+        
+        rs = st.executeQuery(
+            "select i from t where i not in (0, 9, 8, 2, 7)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"-1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select i from t where not i not in (0, 9, 8, 2, 7)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // 1 element list
+        
+        rs = st.executeQuery(
+            "select s from t where s in (100)");
+        
+        expColNames = new String [] {"S"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"100"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // left side of expression
+        
+        rs = st.executeQuery(
+            "select s from t where (s in (100))");
+        
+        expColNames = new String [] {"S"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"100"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // complex expressions
+        
+        rs = st.executeQuery(
+            "select i from t where i in (1, 3, 5, 6, (select i "
+            + "from ss where i = 2) - 2)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from test where i in (sqrt(d),{fn abs (i)}, -6)");
+        
+        expColNames = new String [] {"I", "D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"2", "4.0"},
+            {"3", "10.0"},
+            {"4", "12.0"},
+            {"5", "25.0"},
+            {"10", "100.0"},
+            {"-6", "36.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from test where sqrt(d) in (i, 4)");
+        
+        expColNames = new String [] {"I", "D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"2", "4.0"},
+            {"5", "25.0"},
+            {"10", "100.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from test where (i+d) in (6, 30)");
+        
+        expColNames = new String [] {"I", "D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"2", "4.0"},
+            {"5", "25.0"},
+            {"-6", "36.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from test where sqrt(d) in (i)");
+        
+        expColNames = new String [] {"I", "D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"2", "4.0"},
+            {"5", "25.0"},
+            {"10", "100.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from test where {fn abs (i)} in (i)");
+        
+        expColNames = new String [] {"I", "D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"2", "4.0"},
+            {"3", "10.0"},
+            {"4", "12.0"},
+            {"5", "25.0"},
+            {"10", "100.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from test where {fn abs (i)} not in (i)");
+        
+        expColNames = new String [] {"I", "D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"-6", "36.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from test where (i+d) not in (6, 30)");
+        
+        expColNames = new String [] {"I", "D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"3", "10.0"},
+            {"4", "12.0"},
+            {"10", "100.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from test where sqrt(d) not in (5, 10, 2)");
+        
+        expColNames = new String [] {"I", "D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"3", "10.0"},
+            {"4", "12.0"},
+            {"-6", "36.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // subquery in
+        
+        rs = st.executeQuery(
+            "select i from t where (select i from ss where i = "
+            + "2) in (1, 2)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"0"},
+            {"-1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // derived table
+        
+        rs = st.executeQuery(
+            "select * from (select * from t "
+            + "where i in (1, 3, 5, 6, (select i from ss where i = "
+            + "2) - 2)) a");
+        
+        expColNames = new String [] {"I", "S", "C", "V", "D", "R", "E", "T", "P"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"0", "100", "hello", "everyone is here", "200.0", "300.0", 
+                     "1992-01-01", "12:30:30", "1992-01-01 12:30:30.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        assertUpdateCount(st, 1,
+            " update ss set i = 5 where i in (2, 3, 40e1)");
+        
+        rs = st.executeQuery(
+            " select * from ss");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"},
+            {"1"},
+            {"5"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // delete - where clause
+        
+        assertUpdateCount(st, 2,
+            "delete from ss where i not in (5, 3)");
+        
+        rs = st.executeQuery(
+            " select * from ss");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"5"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        st.close();
+    }
+
+    public void testInBetween() throws SQLException {
+
+        st = createStatement();
+        createTestObjects(st);
+        
+        st.executeUpdate("create table u (c1 integer)");      
+        st.executeUpdate(" insert into u values null");       
+        st.executeUpdate(" insert into u values 1");        
+        st.executeUpdate(" insert into u values null");     
+        st.executeUpdate(" insert into u values 2");
+        
+        rs = st.executeQuery(
+            " select * from u where c1 between 2 and 3");
+        
+        expColNames = new String [] {"C1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from u where c1 in (2, 3, 0, 1)");
+        
+        expColNames = new String [] {"C1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"},
+            {"2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // add some more rows before testing static in list xform
+        
+        st.executeUpdate(
+            "insert into t values (20, 200, 'maybe', 'noone is "
+            + "here', 800.0e0,"
+            + "	1000.0e0, '1892-01-01', '07:30:30', "
+            + "'"+Timestamp.valueOf("1892-01-01 07:30:30")+"')");
+        
+        st.executeUpdate(
+            " insert into t values (-50, -200, 'never', 'noone "
+            + "is there', -800.0e0,"
+            + "	-10300.0e0, '2992-01-02', '19:30:59', "
+            + "'"+Timestamp.valueOf("2992-01-02 19:30:59")+"')");
+        
+        // test the static in list xform for the various types
+        
+        rs = st.executeQuery(
+            "select i from t");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"0"},
+            {"-1"},
+            {"20"},
+            {"-50"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select i from t where i in (80, 20, -60, -1)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"-1"},
+            {"20"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select s from t");
+        
+        expColNames = new String [] {"S"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"100"},
+            {"-100"},
+            {"200"},
+            {"-200"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select s from t where s in (100, -200, -400)");
+        
+        expColNames = new String [] {"S"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"100"},
+            {"-200"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select c from t");
+        
+        expColNames = new String [] {"C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"hello"},
+            {"goodbye"},
+            {"maybe"},
+            {"never"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select c from t where c in ('a', 'goodbye', '')");
+        
+        expColNames = new String [] {"C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"goodbye"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select v from t");
+        
+        expColNames = new String [] {"V"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"everyone is here"},
+            {"everyone is there"},
+            {"noone is here"},
+            {"noone is there"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select v from t where v in ('noone is there', "
+            + "'everyone is here', '')");
+        
+        expColNames = new String [] {"V"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"everyone is here"},
+            {"noone is there"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select d from t");
+        
+        expColNames = new String [] {"D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"200.0"},
+            {"-200.0"},
+            {"800.0"},
+            {"-800.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select d from t where d in (200, -800)");
+        
+        expColNames = new String [] {"D"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"200.0"},
+            {"-800.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select r from t");
+        
+        expColNames = new String [] {"R"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"300.0"},
+            {"-300.0"},
+            {"1000.0"},
+            {"-10300.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select r from t where r in (300.0, -10300.0)");
+        
+        expColNames = new String [] {"R"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"300.0"},
+            {"-10300.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select e from t");
+        
+        expColNames = new String [] {"E"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"1992-01-01"},
+            {"1992-01-02"},
+            {"1892-01-01"},
+            {"2992-01-02"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select e from t where e in ('2992-01-02', "
+            + "'3999-08-08', '1992-01-02')");
+        
+        expColNames = new String [] {"E"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1992-01-02"},
+            {"2992-01-02"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select t from t");
+        
+        expColNames = new String [] {"T"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"12:30:30"},
+            {"12:30:59"},
+            {"07:30:30"},
+            {"19:30:59"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select t from t where t in ('12:30:58', "
+            + "'07:20:20', '07:30:30')");
+        
+        expColNames = new String [] {"T"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"07:30:30"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // verify that added predicates getting pushed down
+        
+        rs = st.executeQuery(
+            "select p from t");
+        
+        expColNames = new String [] {"P"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {null},
+            {"1992-01-01 12:30:30.0"},
+            {"1992-01-02 12:30:59.0"},
+            {"1892-01-01 07:30:30.0"},
+            {"2992-01-02 19:30:59.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select p from t where p in "
+            + "('"+Timestamp.valueOf("1992-01-02 12:30:59")+"',"
+            + "'"+Timestamp.valueOf("1992-01-02 12:35:59")+"',"
+            + "'"+Timestamp.valueOf("1992-05-02 12:38:59")+"')");
+        
+        expColNames = new String [] {"P"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1992-01-02 12:30:59.0"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        cSt = prepareCall(
+            " call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+        assertUpdateCount(cSt, 0);
+        
+        rs=st.executeQuery("SELECT R FROM t");
+        while(rs.next()){}
+        rs.close();
+        
+        rs = st.executeQuery(
+            " values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+        rs.next();
+
+        if(usingEmbedded()){
+            RuntimeStatisticsParser rtsp = new RuntimeStatisticsParser(rs.getString(1));
+            rs.close();
+            assertTrue(rtsp.usedTableScan());
+            assertFalse(rtsp.usedDistinctScan());
+        }
+        
+        st.close();
+    }
+
+    public void testBigInList() throws SQLException {
+        // big in lists (test binary search)
+        
+        st = createStatement();
+        createTestObjects(st);                
+        
+        rs = st.executeQuery(
+            " select * from big where i in (1, 3, 5, 7, 9, 11, "
+            + "13, 15, 17, 19, 21, 23, 25, 27, 29, 31)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"3", "3"},
+            {"5", "5"},
+            {"7", "7"},
+            {"9", "9"},
+            {"11", "11"},
+            {"13", "13"},
+            {"15", "15"},
+            {"17", "17"},
+            {"19", "19"},
+            {"21", "21"},
+            {"23", "23"},
+            {"25", "25"},
+            {"27", "27"},
+            {"29", "29"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (31, 32, 5, 7, 9, 11, "
+            + "13, 15, 17, 19, 21, 23, 25, 27, 29, 1)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"5", "5"},
+            {"7", "7"},
+            {"9", "9"},
+            {"11", "11"},
+            {"13", "13"},
+            {"15", "15"},
+            {"17", "17"},
+            {"19", "19"},
+            {"21", "21"},
+            {"23", "23"},
+            {"25", "25"},
+            {"27", "27"},
+            {"29", "29"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (1, 5, 7, 9, 11, 13, "
+            + "15, 17, 19, 21, 23, 25, 27, 29, 31)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"5", "5"},
+            {"7", "7"},
+            {"9", "9"},
+            {"11", "11"},
+            {"13", "13"},
+            {"15", "15"},
+            {"17", "17"},
+            {"19", "19"},
+            {"21", "21"},
+            {"23", "23"},
+            {"25", "25"},
+            {"27", "27"},
+            {"29", "29"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (1, 5, 7, 9, 13, 15, "
+            + "17, 19, 21, 23, 25, 27, 29, 31)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"5", "5"},
+            {"7", "7"},
+            {"9", "9"},
+            {"13", "13"},
+            {"15", "15"},
+            {"17", "17"},
+            {"19", "19"},
+            {"21", "21"},
+            {"23", "23"},
+            {"25", "25"},
+            {"27", "27"},
+            {"29", "29"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (1, 5, 7, 9, 13, 15, "
+            + "17, 19, 23, 25, 27, 29, 31)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"5", "5"},
+            {"7", "7"},
+            {"9", "9"},
+            {"13", "13"},
+            {"15", "15"},
+            {"17", "17"},
+            {"19", "19"},
+            {"23", "23"},
+            {"25", "25"},
+            {"27", "27"},
+            {"29", "29"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (3, 3, 3, 3)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"3", "3"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (4, 4, 4, 4)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"4", "4"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        //Comparisons between 'CHAR (UCS_BASIC)' and 'INTEGER' are not supported
+        
+        assertStatementError("42818", st,
+            " select * from big where c in (1, 3, 5, 7, 9, 11, "
+            + "13, 15, 17, 19, 21, 23, 25, 27, 29, 31)");
+        
+        assertStatementError("42818", st,
+            " select * from big where c in (31, 32, 5, 7, 9, 11, "
+            + "13, 15, 17, 19, 21, 23, 25, 27, 29, 1)");
+        
+        assertStatementError("42818", st,
+            " select * from big where c in (1, 5, 7, 9, 11, 13, "
+            + "15, 17, 19, 21, 23, 25, 27, 29, 31)");
+        
+        assertStatementError("42818", st,
+            " select * from big where c in (1, 5, 7, 9, 13, 15, "
+            + "17, 19, 21, 23, 25, 27, 29, 31)");
+        
+        rs = st.executeQuery(
+            " select * from big where c in ('1', '5', '7', '9', "
+            + "'13', '15', '17', '19', '21', '23', '25', '27', '29', '31')");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"5", "5"},
+            {"7", "7"},
+            {"9", "9"},
+            {"13", "13"},
+            {"15", "15"},
+            {"17", "17"},
+            {"19", "19"},
+            {"21", "21"},
+            {"23", "23"},
+            {"25", "25"},
+            {"27", "27"},
+            {"29", "29"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (1, 5, 7, 9, 13, 15, "
+            + "17, 19, 23, 25, 27, 29, 31)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"5", "5"},
+            {"7", "7"},
+            {"9", "9"},
+            {"13", "13"},
+            {"15", "15"},
+            {"17", "17"},
+            {"19", "19"},
+            {"23", "23"},
+            {"25", "25"},
+            {"27", "27"},
+            {"29", "29"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // check consistency of scans, etc.
+        if(usingEmbedded()){
+            rs = st.executeQuery("values ConsistencyChecker()");
+
+            assertTrue("Consistency checker returned no data", rs.next());
+            String line1 = rs.getString(1);
+            assertTrue("Expected 'No open scans, etc.', not: " + line1,
+                    line1.startsWith( "No open scans, etc.") );
+        }      
+        
+        st.close();
+    }
+     
+    public void testCheckQueries() throws SQLException{
+        // Check various queries for which left column is part of 
+        // an index.
+        
+        st=createStatement();
+        createTestObjects(st);      
+                
+        // Simple cases, small table with index on IN col.
+        
+        rs = st.executeQuery(
+            "select * from bt1 where i in (9, 2, 8)");
+        
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"2", "two", "22.2"},
+            {"8", "eight", "2.8"},
+            {"9", "nine", null}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select i from bt1 where i in (9, 2, 8)");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"2"},
+            {"8"},
+            {"9"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // Simple cases, small table, IN col is part of index but 
+        // is not a leading column.
+        
+        rs = st.executeQuery(
+            "select * from bt1 where c in ('a', 'two', 'three')");
+        
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"2", "two", "22.2"},
+            {"3", "three", null}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select c from bt1 where c in ('a', 'two', 'three')");
+        
+        expColNames = new String [] {"C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"two"},
+            {"three"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // Multiple rows matching a single IN value; make sure we 
+        // get two rows for "3".
+        
+        rs = st.executeQuery(
+            "select * from bt1 where i in (1, 2, 3)");
+        
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "one", null},
+            {"2", "two", "22.2"},
+            {"3", "three", null},
+            {"3", "trois", "21.2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from bt1 where i in (8, 3)");
+        
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"3", "three", null},
+            {"3", "trois", "21.2"},
+            {"8", "eight", "2.8"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select i from bt1 where i in (8, 3) order by i");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"3"},
+            {"3"},
+            {"8"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from bt1 where i in (8, 3) order by i");
+        
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"3", "three", null},
+            {"3", "trois", "21.2"},
+            {"8", "eight", "2.8"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // No row for minimum value; make sure we still get the rest.
+        
+        rs = st.executeQuery(
+            "select * from bt1 where i in (-1, 1, 2, 3)");
+        
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "one", null},
+            {"2", "two", "22.2"},
+            {"3", "three", null},
+            {"3", "trois", "21.2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from bt1 where i in (0, 1, 2, 3)");
+        
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "one", null},
+            {"2", "two", "22.2"},
+            {"3", "three", null},
+            {"3", "trois", "21.2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from bt1 where i in (1, 2, -1, 3)");
+        
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "one", null},
+            {"2", "two", "22.2"},
+            {"3", "three", null},
+            {"3", "trois", "21.2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // Various examples with larger table and multiple IN 
+        // lists on same column in single table.
+        
+        rs = st.executeQuery(
+            "select * from big where i in (1, 2)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"2", "2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (1, 30)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"30", "30"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (1, 30) and i = 1");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (1, 30) or i in (2, 29)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"2", "2"},
+            {"29", "29"},
+            {"30", "30"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (1, 30) and i in (1, 2, 29)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (1, 30) and i in (1, "
+            + "2, 29, 30)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"30", "30"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (1, 2, 29, 30) and i "
+            + "in (1, 30)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"30", "30"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (1, 30) and (i = 30 or i = 1)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"30", "30"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big where i in (1, 30) and (i = 30 or i = 2)");
+        
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"30", "30"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // Multiple IN lists on different tables, plus join predicate.
+        
+        rs = st.executeQuery(
+            "select count(*) from big, bt1 where big.i in (1, 3, "
+            + "30) or bt1.i in (-1, 2, 3) and big.i = bt1.i");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"55"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big, bt1 where (big.i in (1, 3, 30) "
+            + "or bt1.i in (-1, 2, 3)) and big.i = bt1.i");
+        
+        expColNames = new String [] {"I", "C", "I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1", "1", "one", null},
+            {"2", "2", "2", "two", "22.2"},
+            {"3", "3", "3", "three", null},
+            {"3", "3", "3", "trois", "21.2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big, bt1 where big.i in (1, 3, 30) "
+            + "and bt1.i in (-1, 2, 3) and big.i = bt1.i");
+        
+        expColNames = new String [] {"I", "C", "I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"3", "3", "3", "three", null},
+            {"3", "3", "3", "trois", "21.2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big, bt1 where big.i in (1, 3, 30) "
+            + "and bt1.i in (2, 3) and big.i = bt1.i");
+        
+        expColNames = new String [] {"I", "C", "I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"3", "3", "3", "three", null},
+            {"3", "3", "3", "trois", "21.2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // Multiple IN lists for different cols in same table; 
+        // we'll only use one as a "probe predicate"; the other 
+        // ones should be enforced as regular restrictions.
+        
+        rs = st.executeQuery(
+            "select * from bt1 where i in (2, 4, 6, 8) and de in "
+            + "(22.3, 2.8) and c in ('seven', 'eight', 'nine')");
+        
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"8", "eight", "2.8"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // Multiple IN lists on different tables, no join 
+        // predicate, count only.
+        
+        rs = st.executeQuery(
+            "select count(*) from big, bt1 where big.i in (1, 3, "
+            + "30) or bt1.i in (-1, 2, 3)");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"135"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select count(*) from big, bt1 where big.i in (1, "
+            + "3, 30) and bt1.i in (-1, 2, 3)");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"9"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select count(*) from big, bt1 where big.i in (1, "
+            + "3, 30) and bt1.i in (2, 3)");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"9"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select count(*) from big b1, big b2 where b1.i in "
+            + "(1, 3, 30) and b2.i in (2, 3)");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"6"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select count(*) from big b1, big b2 where b1.i in "
+            + "(1, 3, 30) and b2.i in (-1,2, 3)");
+        
+        expColNames = new String [] {"1"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"6"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // Multiple IN lists on different tables, no join 
+        // predicate, show rows.
+        
+        rs = st.executeQuery(
+            "select * from big, bt1 where big.i in (1, 3, 30) "
+            + "and bt1.i in (-1, 2, 3) order by big.i, bt1.c");
+        
+        expColNames = new String [] {"I", "C", "I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1", "3", "three", null},
+            {"1", "1", "3", "trois", "21.2"},
+            {"1", "1", "2", "two", "22.2"},
+            {"3", "3", "3", "three", null},
+            {"3", "3", "3", "trois", "21.2"},
+            {"3", "3", "2", "two", "22.2"},
+            {"30", "30", "3", "three", null},
+            {"30", "30", "3", "trois", "21.2"},
+            {"30", "30", "2", "two", "22.2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big, bt1 where big.i in (1, 3, 30) "
+            + "and bt1.i in (2, 3) order by big.i, bt1.c");
+        
+        expColNames = new String [] {"I", "C", "I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1", "3", "three", null},
+            {"1", "1", "3", "trois", "21.2"},
+            {"1", "1", "2", "two", "22.2"},
+            {"3", "3", "3", "three", null},
+            {"3", "3", "3", "trois", "21.2"},
+            {"3", "3", "2", "two", "22.2"},
+            {"30", "30", "3", "three", null},
+            {"30", "30", "3", "trois", "21.2"},
+            {"30", "30", "2", "two", "22.2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big b1, big b2 where b1.i in (1, 3, "
+            + "30) and b2.i in (2, 3) order by b1.i, b2.i");
+        
+        expColNames = new String [] {"I", "C", "I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1", "2", "2"},
+            {"1", "1", "3", "3"},
+            {"3", "3", "2", "2"},
+            {"3", "3", "3", "3"},
+            {"30", "30", "2", "2"},
+            {"30", "30", "3", "3"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from big b1, big b2 where b1.i in (1, 3, "
+            + "30) and b2.i in (-1,2, 3) order by b1.i, b2.i");
+        
+        expColNames = new String [] {"I", "C", "I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1", "2", "2"},
+            {"1", "1", "3", "3"},
+            {"3", "3", "2", "2"},
+            {"3", "3", "3", "3"},
+            {"30", "30", "2", "2"},
+            {"30", "30", "3", "3"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // IN lists with ORDER BY.
+        
+        rs = st.executeQuery(
+            "select * from bt1 where i in (1, 8, 3, 3) order by i");
+        
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "one", null},
+            {"3", "three", null},
+            {"3", "trois", "21.2"},
+            {"8", "eight", "2.8"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select * from bt1 where i in (1, 8, 3, 3) order by i desc");
+        
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"8", "eight", "2.8"},
+            {"3", "trois", "21.2"},
+            {"3", "three", null},
+            {"1", "one", null}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select i from bt1 where i in (1, 29, 8, 3, 3) order by i");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"},
+            {"3"},
+            {"3"},
+            {"8"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select i from bt1 where i in (1, 29, 8, 3, 3) "
+            + "order by i desc");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"8"},
+            {"3"},
+            {"3"},
+            {"1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select i from bt1 where i in (1, 8, 3, 3, 4, 5, 6, "
+            + "7, 8, 9, 0) order by i");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"},
+            {"3"},
+            {"3"},
+            {"7"},
+            {"8"},
+            {"9"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select c from bt1 where c in ('abc', 'de', 'fg', "
+            + "'two', 'or', 'not', 'one', 'thre', 'zour', 'three') "
+            + "order by c");
+        
+        expColNames = new String [] {"C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"one"},
+            {"three"},
+            {"two"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select i from big where i in (1, 29, 3, 8) order by i");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"},
+            {"3"},
+            {"8"},
+            {"29"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            " select i from big where i in (1, 29, 3, 8) order by i desc");
+        
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"29"},
+            {"8"},
+            {"3"},
+            {"1"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // Prepared statement checks. Mix of constants and params.
+        
+        pSt = prepareStatement(
+            "select * from bt1 where i in (1, 8, 3, ?) order by i, c");
+        
+        rs = st.executeQuery(
+            "values 3");
+        
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+        
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "one", null},
+            {"3", "three", null},
+            {"3", "trois", "21.2"},
+            {"8", "eight", "2.8"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        pSt.close();
+        
+        pSt = prepareStatement(
+            "select * from big where i in (1, ?, 30)");
+        
+        rs = st.executeQuery(
+            "values (2)");
+        
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+        
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"I", "C"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1"},
+            {"2", "2"},
+            {"30", "30"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // Execute statement more than once to make sure params 
+        // are correctly assigned in subsequent executions.
+        
+        pSt = prepareStatement(
+            "select i from bt1 where i in (?, 9, ?) order by i desc");
+        
+        rs = st.executeQuery(
+            "values (5, 2)");
+        
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+        
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"9"},
+            {"2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "values (3, 2)");
+        
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+        
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"9"},
+            {"3"},
+            {"3"},
+            {"2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "values (3, 3)");
+        
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+        
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"9"},
+            {"3"},
+            {"3"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        pSt = prepareStatement(
+            "select i from bt1 where i in (?, ?, 1)");
+        
+        rs = st.executeQuery(
+            "values (4, 3)");
+        
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+        
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"I"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1"},
+            {"3"},
+            {"3"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        pSt = prepareStatement(
+            "select * from bt1 where i in (?, ?, 1)");
+        
+        rs = st.executeQuery(
+            "values (4, 3)");
+        
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+        
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "one", null},
+            {"3", "three", null},
+            {"3", "trois", "21.2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "values (34, 39)");
+        
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+        
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "one", null}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // Null as a parameter.
+            
+        rs = st.executeQuery("values (3, cast (null as int))");
+        
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1;i <= rsmd.getColumnCount(); i++) {
+             pSt.setObject(i, rs.getObject(i), Types.INTEGER);
+        }
+
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "one", null},
+            {"3", "three", null},
+            {"3", "trois", "21.2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // Multiple IN lists, one with constants, other with 
+        // parameter.
+        
+        pSt = prepareStatement(
+            "select * from big, bt1 where big.i in (1, 3, 30) "
+            + "and bt1.i in (?, 2, 3) and big.i = bt1.i");
+        
+        rs = st.executeQuery(
+            "values -1");
+        
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+        
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"I", "C", "I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"3", "3", "3", "three", null},
+            {"3", "3", "3", "trois", "21.2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "values 1");
+        
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+        
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"I", "C", "I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"1", "1", "1", "one", null},
+            {"3", "3", "3", "three", null},
+            {"3", "3", "3", "trois", "21.2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        // Only parameter markers (no constants).
+        
+        pSt = prepareStatement(
+            "select * from bt1 where i in (?, ?)");
+        
+        rs = st.executeQuery(
+            "values (2, 4)");
+        
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+        
+        rs = pSt.executeQuery();
+        expColNames = new String [] {"I", "C", "DE"};
+        JDBC.assertColumnNames(rs, expColNames);
+        
+        expRS = new String [][]
+        {
+            {"2", "two", "22.2"}
+        };
+        
+        JDBC.assertFullResultSet(rs, expRS, true);
+        
+        rs = st.executeQuery(
+            "values (-2, -4)");
+        
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)

[... 1614 lines stripped ...]


Mime
View raw message