001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: import java.sql.Connection;
034: import java.sql.PreparedStatement;
035: import java.sql.ResultSet;
036: import java.sql.Statement;
037:
038: import junit.framework.TestCase;
039: import junit.framework.TestResult;
040:
041: /**
042: * HSQLDB TestINPredicate Junit test case. <p>
043: *
044: * @author boucherb@users
045: * @version 1.7.2
046: * @since 1.7.2
047: */
048: public class TestINPredicateParameterizationAndCorrelation extends
049: TestBase {
050:
051: public TestINPredicateParameterizationAndCorrelation(String name) {
052: super (name);
053: }
054:
055: /* Implements the TestINPredicate test */
056: public void test() throws Exception {
057:
058: Connection conn = newConnection();
059: Statement stmt = conn.createStatement();
060: PreparedStatement pstmt;
061: ResultSet rs;
062: int actualCount;
063: int expectedCount;
064: String sql;
065:
066: stmt.execute("drop table test if exists");
067:
068: sql = "create table test(id int)";
069:
070: stmt.execute(sql);
071:
072: sql = "insert into test values(?)";
073: pstmt = conn.prepareStatement(sql);
074:
075: for (int i = 0; i < 10; i++) {
076: pstmt.setInt(1, i);
077: pstmt.addBatch();
078: }
079:
080: pstmt.executeBatch();
081:
082: sql = "select count(*) from test where id in(?,?)";
083: pstmt = conn.prepareStatement(sql);
084:
085: pstmt.setInt(1, 0);
086: pstmt.setInt(2, 9);
087:
088: rs = pstmt.executeQuery();
089:
090: rs.next();
091:
092: expectedCount = 2;
093: actualCount = rs.getInt(1);
094: sql = "\"select count(*) from test where id in(0,9)\"";
095:
096: assertEquals(sql, expectedCount, actualCount);
097:
098: sql = "select count(*) from test a, test b where 0 in(a.id, b.id)";
099: rs = stmt.executeQuery(sql);
100:
101: rs.next();
102:
103: expectedCount = rs.getInt(1);
104: sql = "select count(*) from test a, test b where ? in (a.id, b.id)";
105: pstmt = conn.prepareStatement(sql);
106:
107: pstmt.setInt(1, 0);
108:
109: rs = pstmt.executeQuery();
110:
111: rs.next();
112:
113: actualCount = rs.getInt(1);
114: sql = "\"select count(*) from test a, test b where 0 in (a.id, b.id)\"";
115:
116: assertEquals(sql, expectedCount, actualCount);
117:
118: try {
119: sql = "select count(*) from test a, test b where ? in(?, b.id)";
120: pstmt = conn.prepareStatement(sql);
121:
122: assertTrue("expected exception preparing \"" + sql + "\"",
123: false);
124: } catch (Exception e) {
125:
126: // this is the expected result
127: assertTrue(e.toString(), true);
128: }
129:
130: try {
131: sql = "select count(*) from test a, test b where a.id in(?, ?)";
132: pstmt = conn.prepareStatement(sql);
133: } catch (Exception e) {
134: assertTrue("unexpected exception preparing \"" + sql
135: + "\":" + e, false);
136: }
137:
138: sql = "select count(*) from "
139: + "(select * from test where id in (1,2)) a,"
140: + "(select * from test where id in (3,4)) b "
141: + "where a.id < 2 and b.id < 4";
142: rs = stmt.executeQuery(sql);
143:
144: rs.next();
145:
146: expectedCount = rs.getInt(1);
147: sql = "select count(*) from "
148: + "(select * from test where id in (?,?)) a,"
149: + "(select * from test where id in (?,?)) b "
150: + "where a.id < ? and b.id < ?";
151: pstmt = conn.prepareStatement(sql);
152:
153: pstmt.setInt(1, 1);
154: pstmt.setInt(2, 2);
155: pstmt.setInt(3, 3);
156: pstmt.setInt(4, 4);
157: pstmt.setInt(5, 2);
158: pstmt.setInt(6, 4);
159:
160: rs = pstmt.executeQuery();
161:
162: rs.next();
163:
164: actualCount = rs.getInt(1);
165:
166: assertEquals("row count: ", expectedCount, actualCount);
167: }
168:
169: /* Runs TestINPredicate test from the command line*/
170: public static void main(String[] args) throws Exception {
171:
172: TestResult result;
173: TestCase test;
174: java.util.Enumeration failures;
175: int count;
176:
177: result = new TestResult();
178: test = new TestINPredicateParameterizationAndCorrelation("test");
179:
180: test.run(result);
181:
182: count = result.failureCount();
183:
184: System.out
185: .println("TestINPredicateParameterizationAndCorrelation failure count: "
186: + count);
187:
188: failures = result.failures();
189:
190: while (failures.hasMoreElements()) {
191: System.out.println(failures.nextElement());
192: }
193: }
194: }
|