001: /*
002: * Licensed to the Apache Software Foundation (ASF) under one or more
003: * contributor license agreements. See the NOTICE file distributed with
004: * this work for additional information regarding copyright ownership.
005: * The ASF licenses this file to you under the Apache License, Version 2.0
006: * (the "License"); you may not use this file except in compliance with
007: * the License. You may obtain a copy of the License at
008: *
009: * http://www.apache.org/licenses/LICENSE-2.0
010: *
011: * Unless required by applicable law or agreed to in writing, software
012: * distributed under the License is distributed on an "AS IS" BASIS,
013: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: * See the License for the specific language governing permissions and
015: * limitations under the License.
016: */
017:
018: package org.apache.derbyTesting.functionTests.tests.lang;
019:
020: import java.sql.*;
021: import java.util.Random;
022:
023: import junit.framework.Test;
024: import junit.framework.TestSuite;
025:
026: import org.apache.derbyTesting.junit.BaseJDBCTestCase;
027: import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
028: import org.apache.derbyTesting.junit.JDBC;
029:
030: // This test tries to push byte code generation to the limit.
031: // It has to be run with a large amount of memory which is set with jvmflags in
032: // largeCodeGen_app.properties
033: // There are only a few types of cases now. Other areas need to be tested such as large in clauses, etc.
034: //
035:
036: public class largeCodeGen extends BaseJDBCTestCase {
037: private static boolean TEST_QUERY_EXECUTION = true;
038:
039: public largeCodeGen(String name) {
040: super (name);
041: }
042:
043: public static Test suite() {
044: TestSuite suite = new TestSuite();
045:
046: // Code generation test, just invoke on embedded
047: // as the main function is to test the byte code compiler.
048: if (usingEmbedded()) {
049: suite.addTestSuite(largeCodeGen.class);
050: return new CleanDatabaseTestSetup(suite);
051: }
052: return suite;
053: }
054:
055: protected void setUp() throws SQLException {
056: getConnection().setAutoCommit(false);
057: Statement stmt = createStatement();
058:
059: String createSQL = "create table t0 "
060: + "(si smallint,i int, bi bigint, r real, f float, d double precision, n5_2 numeric(5,2), dec10_3 decimal(10,3), ch20 char(3),vc varchar(20), lvc long varchar)";
061: stmt.executeUpdate(createSQL);
062: stmt
063: .executeUpdate("insert into t0 values(2,3,4,5.3,5.3,5.3,31.13,123456.123, 'one','one','one')");
064: stmt.close();
065: commit();
066: }
067:
068: protected void tearDown() throws Exception {
069: Statement stmt = createStatement();
070: stmt.execute("DROP TABLE T0");
071: stmt.close();
072: commit();
073: super .tearDown();
074: }
075:
076: /**
077: * Prepares and executes query against table t0 with n parameters
078: * The assumption is that the query will always return our one row
079: * of data inserted into the t0 table.
080: *
081: * @param testName
082: * @param sqlBuffer - StringBuffer with SQL Text
083: * @param numParams - Number of parameters
084: * @param paramValue - Parameter value
085: * @return true if the check fails
086: */
087: private boolean checkT0Query(String testName,
088: StringBuffer sqlBuffer, int numParams, int paramValue) {
089: PreparedStatement ps;
090: try {
091: ps = prepareStatement(sqlBuffer.toString());
092: if (TEST_QUERY_EXECUTION) {
093: for (int i = 1; i <= numParams; i++) {
094: ps.setInt(i, paramValue);
095: }
096: ResultSet rs = ps.executeQuery();
097: rs.next();
098: checkRowData(rs);
099: rs.close();
100: }
101: ps.close();
102: commit();
103: return false;
104: } catch (SQLException e) {
105: // The top level exception is expected to be
106: // the "user-friendly" query is too complex
107: // rather than some linkage error.
108: assertSQLState("42ZA0", e);
109: return true;
110:
111: }
112: }
113:
114: /**
115: * Test many logical operators in the where clause.
116: */
117: public void testLogicalOperators() throws SQLException {
118:
119: int passCount = 0;
120: for (int count = 700; count <= 10000; count += 100) {
121: // keep testing until it fails
122: if (logicalOperators(count))
123: break;
124:
125: passCount = count;
126: }
127:
128: // svn 372388 trunk - passed @ 400
129: // Fix to DERBY-921 - passed @ 800
130: // DERBY-921 - support 32bit branch offsets
131: assertEquals("logical operators change from previous limit",
132: 800, passCount);
133:
134: // 10,000 causes Stack overflow and database corruption
135: //testLogicalOperators(con, 10000);
136: }
137:
138: /**
139: * Tests numParam parameter markers in a where clause
140: *
141: * @param numOperands
142: */
143: private boolean logicalOperators(int numOperands)
144: throws SQLException {
145:
146: // First with parameters
147: String pred = "(si = ? AND si = ? )";
148: String testName = "Logical operators with " + numOperands
149: + " parameters";
150: StringBuffer sqlBuffer = new StringBuffer(
151: (numOperands * 20) + 512);
152: sqlBuffer.append("SELECT * FROM T0 WHERE " + pred);
153: for (int i = 2; i < numOperands; i += 2) {
154: sqlBuffer.append(" OR " + pred);
155: }
156: return checkT0Query(testName, sqlBuffer, numOperands, 2);
157:
158: }
159:
160: public void testInClause() throws SQLException {
161:
162: // DERBY-739 raised number of parameters from 2700 to 3400
163: // svn 372388 trunk - passed @ 3400
164: // So perform a quick check there.
165: assertFalse("IN clause with 3400 parameters ", inClause(3400));
166:
167: int passCount = 0;
168: for (int count = 97000; count <= 200000; count += 1000) {
169: // keep testing until it fails.
170: if (inClause(count))
171: break;
172: passCount = count;
173: }
174:
175: // fixes for DERBY-766 to split methods with individual statements
176: // bumps the limit to 98,000 parameters.
177: assertEquals("IN clause change from previous limit", 98000,
178: passCount);
179: }
180:
181: /**
182: * Test in clause with many parameters
183: *
184: * @param con
185: * @param numParams - Number of parameters to test
186: * @return true if the test fails
187: * @throws SQLException
188: */
189: private boolean inClause(int numParams) throws SQLException {
190: String testName = "IN clause with " + numParams + " parameters";
191: StringBuffer sqlBuffer = new StringBuffer(
192: (numParams * 20) + 512);
193: sqlBuffer.append("SELECT * FROM T0 WHERE SI IN (");
194: for (int i = 1; i < numParams; i++) {
195: sqlBuffer.append("?, ");
196: }
197: sqlBuffer.append("?)");
198: return checkT0Query(testName, sqlBuffer, numParams, 2);
199: }
200:
201: public void testUnions() throws SQLException {
202: String viewName = "v0";
203: Statement stmt = createStatement();
204:
205: StringBuffer createView = new StringBuffer("create view "
206: + viewName + " as select * from t0 ");
207: for (int i = 1; i < 100; i++) {
208: createView.append(" UNION ALL (SELECT * FROM t0 )");
209: }
210: //System.out.println(createViewString);
211: stmt.executeUpdate(createView.toString());
212: commit();
213:
214: int passCount = 0;
215: for (int count = 1000; count <= 1000; count += 1000) {
216: // keep testing until it fails
217: if (largeUnionSelect(viewName, count))
218: break;
219: passCount = count;
220:
221: }
222:
223: // 10000 gives a different constant pool error
224: // DERBY-1315 gives out of memory error.
225: //assertTrue("10000 UNION passed!",
226: // largeUnionSelect(viewName, 10000));
227:
228: createStatement().executeUpdate("DROP VIEW " + viewName);
229:
230: // svn 372388 trunk - passed @ 900
231: // trunk now back to 700
232: //
233: assertEquals("UNION operators change from previous limit",
234: 1000, passCount);
235:
236: }
237:
238: private boolean largeUnionSelect(String viewName, int numUnions)
239: throws SQLException {
240:
241: // There are 100 unions in each view so round to the nearest 100
242:
243: String unionClause = " UNION ALL (SELECT * FROM " + viewName
244: + ")";
245:
246: StringBuffer selectSQLBuffer = new StringBuffer(
247: ((numUnions / 100) * unionClause.length()) + 512);
248:
249: selectSQLBuffer.append("select * from t0 ");
250:
251: for (int i = 1; i < numUnions / 100; i++) {
252: selectSQLBuffer.append(unionClause);
253: }
254:
255: try {
256: // Ready to execute the problematic query
257: String selectSQL = selectSQLBuffer.toString();
258: //System.out.println(selectSQL);
259: PreparedStatement pstmt = prepareStatement(selectSQL);
260: if (largeCodeGen.TEST_QUERY_EXECUTION) {
261: ResultSet rs = pstmt.executeQuery();
262: int numRows = 0;
263: while (rs.next()) {
264: numRows++;
265: if ((numRows % 100) == 0)
266: checkRowData(rs);
267: }
268: rs.close();
269: commit();
270: }
271: pstmt.close();
272: return false;
273:
274: } catch (SQLException sqle) {
275: // The top level exception is expected to be
276: // the "user-friendly" query is too complex
277: // rather than some linkage error.
278: assertSQLState("42ZA0", sqle);
279:
280: return true;
281:
282: }
283:
284: }
285:
286: // Check the data on the positioned row against what we inserted.
287: private static void checkRowData(ResultSet rs) throws SQLException {
288: //" values(2,3,4,5.3,5.3,5.3,31.13,123456.123, 'one','one','one')");
289: String[] values = { "2", "3", "4", "5.3", "5.3", "5.3",
290: "31.13", "123456.123", "one", "one", "one" };
291: for (int i = 1; i <= 11; i++) {
292: assertEquals("Result set data value: ", values[i - 1], rs
293: .getString(i));
294: }
295: }
296:
297: /**
298: * Test an INSERT statement with a large number of rows in the VALUES clause.
299: * Reported as DERBY-1714.
300: * @throws SQLException
301: *
302: */
303: public void testInsertValues() throws SQLException {
304: int passCount = 0;
305: for (int count = 1500; count <= 1700; count += 200) {
306: // keep testing until it fails
307: if (insertValues(count))
308: break;
309: passCount = count;
310:
311: }
312:
313: // Final fixes for DERBY-766 pushed the limit to 1700
314: // Beyond that a StackOverflow occurs.
315: assertEquals("INSERT VALUES change from previous limit", 1700,
316: passCount);
317: }
318:
319: /**
320: * Create a large insert statement with rowCount rows all with
321: * constants. Prepare and execute it and then rollback to leave
322: * the table unchanged.
323: * @param rowCount
324: * @return
325: * @throws SQLException
326: */
327: private boolean insertValues(int rowCount) throws SQLException {
328: Random r = new Random(3457245435L);
329:
330: StringBuffer insertSQL = new StringBuffer(
331: "INSERT INTO T0(SI,I,BI,R,F,D,N5_2,DEC10_3,CH20,VC,LVC) VALUES\n");
332:
333: for (int i = 0; i < rowCount; i++) {
334: if (i != 0)
335: insertSQL.append(',');
336:
337: insertSQL.append('(');
338:
339: insertSQL.append(((short) r.nextInt()));
340: insertSQL.append(',');
341: insertSQL.append(i);
342: insertSQL.append(',');
343: insertSQL.append(r.nextLong());
344: insertSQL.append(',');
345:
346: insertSQL.append(r.nextFloat());
347: insertSQL.append(',');
348: insertSQL.append(r.nextFloat());
349: insertSQL.append(',');
350: insertSQL.append(r.nextDouble());
351: insertSQL.append(',');
352:
353: insertSQL.append("462.54");
354: insertSQL.append(',');
355: insertSQL.append("9324324.34");
356: insertSQL.append(',');
357:
358: insertSQL.append('\'');
359: insertSQL.append("c");
360: insertSQL.append(r.nextInt() % 10);
361: insertSQL.append('\'');
362: insertSQL.append(',');
363:
364: insertSQL.append('\'');
365: insertSQL.append("vc");
366: insertSQL.append(r.nextInt() % 1000000);
367: insertSQL.append('\'');
368: insertSQL.append(',');
369:
370: insertSQL.append('\'');
371: insertSQL.append("lvc");
372: insertSQL.append(r.nextInt());
373: insertSQL.append('\'');
374:
375: insertSQL.append(')');
376:
377: insertSQL.append('\n');
378: }
379:
380: try {
381: PreparedStatement ps = prepareStatement(insertSQL
382: .toString());
383: assertEquals("Incorrect update count", rowCount, ps
384: .executeUpdate());
385: ps.close();
386: rollback();
387: return false;
388: } catch (SQLException e) {
389: // The top level exception is expected to be
390: // the "user-friendly" query is too complex
391: // rather than some linkage error.
392: assertSQLState("42ZA0", e);
393: }
394:
395: return true;
396: }
397: }
|