001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.bug4356
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.lang;
023:
024: import java.sql.Connection;
025: import java.sql.DriverManager;
026: import java.sql.DatabaseMetaData;
027: import java.sql.ResultSet;
028: import java.sql.PreparedStatement;
029: import java.sql.Statement;
030: import java.sql.SQLException;
031: import java.sql.Types;
032: import java.util.BitSet;
033:
034: import org.apache.derby.tools.ij;
035: import org.apache.derby.tools.JDBCDisplayUtil;
036:
037: /**
038: * Test BackingStoreHashtable spilling to disk.
039: * BackingStoreHashtable is used to implement hash joins, distinct, scroll insensitive cursors,
040: * outer joins, and the HAVING clause.
041: */
042: public class SpillHash {
043: private static PreparedStatement joinStmt;
044: private static PreparedStatement distinctStmt;
045: private static final int LOTS_OF_ROWS = 10000;
046: private static int errorCount = 0;
047:
048: public static void main(String args[]) {
049: try {
050: /* Load the JDBC Driver class */
051: // use the ij utility to read the property file and
052: // make the initial connection.
053: ij.getPropertyArg(args);
054: Connection conn = ij.startJBMS();
055: Statement stmt = conn.createStatement();
056:
057: for (int i = 0; i < prep.length; i++)
058: stmt.executeUpdate(prep[i]);
059: PreparedStatement insA = conn
060: .prepareStatement("insert into ta(ca1,ca2) values(?,?)");
061: PreparedStatement insB = conn
062: .prepareStatement("insert into tb(cb1,cb2) values(?,?)");
063: insertDups(insA, insB, initDupVals);
064:
065: joinStmt = conn
066: .prepareStatement("select ta.ca1, ta.ca2, tb.cb2 from ta, tb where ca1 = cb1");
067: distinctStmt = conn
068: .prepareStatement("select distinct ca1 from ta");
069:
070: runStatements(conn, 0, new String[][][] { initDupVals });
071:
072: System.out.println("Growing database.");
073:
074: // Add a lot of rows so that the hash tables have to spill to disk
075: conn.setAutoCommit(false);
076: for (int i = 1; i <= LOTS_OF_ROWS; i++) {
077: insA.setInt(1, i);
078: insA.setString(2, ca2Val(i));
079: insA.executeUpdate();
080: insB.setInt(1, i);
081: insB.setString(2, cb2Val(i));
082: insB.executeUpdate();
083:
084: if ((i & 0xff) == 0)
085: conn.commit();
086: }
087: conn.commit();
088: insertDups(insA, insB, spillDupVals);
089: conn.commit();
090:
091: conn.setAutoCommit(true);
092: runStatements(conn, LOTS_OF_ROWS, new String[][][] {
093: initDupVals, spillDupVals });
094:
095: conn.close();
096: } catch (Exception e) {
097: System.out.println("FAIL -- unexpected exception " + e);
098: JDBCDisplayUtil.ShowException(System.out, e);
099: e.printStackTrace();
100: errorCount++;
101: }
102: if (errorCount == 0) {
103: System.out.println("PASSED.");
104: System.exit(0);
105: } else {
106: System.out.println("FAILED: " + errorCount
107: + ((errorCount == 1) ? " error" : " errors"));
108: System.exit(1);
109: }
110: } // end of main
111:
112: private static final String[] prep = {
113: "create table ta (ca1 integer, ca2 char(200))",
114: "create table tb (cb1 integer, cb2 char(200))",
115: "insert into ta(ca1,ca2) values(null, 'Anull')",
116: "insert into tb(cb1,cb2) values(null, 'Bnull')" };
117:
118: private static final String[][] initDupVals = { { "0a", "0b" },
119: { "1a", "1b" }, { "2a" } };
120: private static final String[][] spillDupVals = { {}, { "1c" },
121: { "2b" }, { "3a", "3b", "3c" } };
122:
123: private static int expectedMincc2(int cc1) {
124: return 4 * cc1;
125: }
126:
127: private static int expectedMaxcc2(int cc1) {
128: return expectedMincc2(cc1) + (cc1 & 0x3);
129: }
130:
131: private static void insertDups(PreparedStatement insA,
132: PreparedStatement insB, String[][] dupVals)
133: throws SQLException {
134: for (int i = 0; i < dupVals.length; i++) {
135: insA.setInt(1, -i);
136: insB.setInt(1, -i);
137: String[] vals = dupVals[i];
138: for (int j = 0; j < vals.length; j++) {
139: insA.setString(2, "A" + vals[j]);
140: insA.executeUpdate();
141: insB.setString(2, "B" + vals[j]);
142: insB.executeUpdate();
143: }
144: }
145: } // end of insertDups
146:
147: private static String ca2Val(int col1Val) {
148: return "A" + col1Val;
149: }
150:
151: private static String cb2Val(int col1Val) {
152: return "B" + col1Val;
153: }
154:
155: private static void runStatements(Connection conn, int maxColValue,
156: String[][][] dupVals) throws SQLException {
157: runJoin(conn, maxColValue, dupVals);
158: runDistinct(conn, maxColValue, dupVals);
159: runCursor(conn, maxColValue, dupVals);
160: }
161:
162: private static void runJoin(Connection conn, int maxColValue,
163: String[][][] dupVals) throws SQLException {
164: System.out.println("Running join");
165: int expectedRowCount = maxColValue; // plus expected duplicates, to be counted below
166: ResultSet rs = joinStmt.executeQuery();
167: BitSet joinRowFound = new BitSet(maxColValue);
168: int dupKeyCount = 0;
169: for (int i = 0; i < dupVals.length; i++) {
170: if (dupVals[i].length > dupKeyCount)
171: dupKeyCount = dupVals[i].length;
172: }
173: BitSet[] dupsFound = new BitSet[dupKeyCount];
174: int[] dupCount = new int[dupKeyCount];
175: for (int i = 0; i < dupKeyCount; i++) {
176: // count the number of rows with column(1) == -i
177: dupCount[i] = 0;
178: for (int j = 0; j < dupVals.length; j++) {
179: if (i < dupVals[j].length)
180: dupCount[i] += dupVals[j][i].length;
181: }
182: dupsFound[i] = new BitSet(dupCount[i] * dupCount[i]);
183: expectedRowCount += dupCount[i] * dupCount[i];
184: }
185:
186: int count;
187: for (count = 0; rs.next(); count++) {
188: int col1Val = rs.getInt(1);
189: if (rs.wasNull()) {
190: System.out.println("Null in join column.");
191: errorCount++;
192: continue;
193: }
194: if (col1Val > maxColValue) {
195: System.out
196: .println("Invalid value in first join column.");
197: errorCount++;
198: continue;
199: }
200: if (col1Val > 0) {
201: if (joinRowFound.get(col1Val - 1)) {
202: System.out.println("Multiple rows for value "
203: + col1Val);
204: errorCount++;
205: }
206: joinRowFound.set(col1Val - 1);
207: String col2Val = trim(rs.getString(2));
208: String col3Val = trim(rs.getString(3));
209: if (!(ca2Val(col1Val).equals(col2Val) && cb2Val(col1Val)
210: .equals(col3Val))) {
211: System.out
212: .println("Incorrect value in column 2 or 3 of join.");
213: errorCount++;
214: }
215: } else // col1Val <= 0, there are duplicates in the source tables
216: {
217: int dupKeyIdx = -col1Val;
218: int col2Idx = findDupVal(rs, 2, 'A', dupKeyIdx, dupVals);
219: int col3Idx = findDupVal(rs, 3, 'B', dupKeyIdx, dupVals);
220: if (col2Idx < 0 || col3Idx < 0)
221: continue;
222:
223: int idx = col2Idx + dupCount[dupKeyIdx] * col3Idx;
224: if (dupsFound[dupKeyIdx].get(idx)) {
225: System.out
226: .println("Repeat of row with key value 0");
227: errorCount++;
228: }
229: dupsFound[dupKeyIdx].set(idx);
230: }
231: }
232: ;
233: if (count != expectedRowCount) {
234: System.out.println("Incorrect number of rows in join.");
235: errorCount++;
236: }
237: rs.close();
238: } // end of runJoin
239:
240: private static int findDupVal(ResultSet rs, int col, char prefix,
241: int keyIdx, String[][][] dupVals) throws SQLException {
242: String colVal = rs.getString(col);
243: if (colVal != null && colVal.length() > 1
244: || colVal.charAt(0) == prefix) {
245: colVal = trim(colVal.substring(1));
246: int dupIdx = 0;
247: for (int i = 0; i < dupVals.length; i++) {
248: if (keyIdx < dupVals[i].length) {
249: for (int j = 0; j < dupVals[i][keyIdx].length; j++, dupIdx++) {
250: if (colVal.equals(dupVals[i][keyIdx][j]))
251: return dupIdx;
252: }
253: }
254: }
255: }
256: System.out.println("Incorrect value in column " + col
257: + " of join with duplicate keys.");
258: errorCount++;
259: return -1;
260: } // end of findDupVal
261:
262: private static String trim(String str) {
263: if (str == null)
264: return str;
265: return str.trim();
266: }
267:
268: private static void runDistinct(Connection conn, int maxColValue,
269: String[][][] dupVals) throws SQLException {
270: System.out.println("Running distinct");
271: ResultSet rs = distinctStmt.executeQuery();
272: checkAllCa1(rs, false, false, maxColValue, dupVals, "DISTINCT");
273: }
274:
275: private static void checkAllCa1(ResultSet rs, boolean expectDups,
276: boolean holdOverCommit, int maxColValue,
277: String[][][] dupVals, String label) throws SQLException {
278: int dupKeyCount = 0;
279: for (int i = 0; i < dupVals.length; i++) {
280: if (dupVals[i].length > dupKeyCount)
281: dupKeyCount = dupVals[i].length;
282: }
283: int[] expectedDupCount = new int[dupKeyCount];
284: int[] dupFoundCount = new int[dupKeyCount];
285: for (int i = 0; i < dupKeyCount; i++) {
286:
287: dupFoundCount[i] = 0;
288: if (!expectDups)
289: expectedDupCount[i] = 1;
290: else {
291: expectedDupCount[i] = 0;
292: for (int j = 0; j < dupVals.length; j++) {
293: if (i < dupVals[j].length)
294: expectedDupCount[i] += dupVals[j][i].length;
295: }
296: }
297: }
298: BitSet found = new BitSet(maxColValue);
299: int count = 0;
300: boolean nullFound = false;
301: try {
302: for (count = 0; rs.next();) {
303: int col1Val = rs.getInt(1);
304: if (rs.wasNull()) {
305: if (nullFound) {
306: System.out
307: .println("Too many nulls returned by "
308: + label);
309: errorCount++;
310: continue;
311: }
312: nullFound = true;
313: continue;
314: }
315: if (col1Val <= -dupKeyCount || col1Val > maxColValue) {
316: System.out.println("Invalid value returned by "
317: + label);
318: errorCount++;
319: continue;
320: }
321: if (col1Val <= 0) {
322: dupFoundCount[-col1Val]++;
323: if (!expectDups) {
324: if (dupFoundCount[-col1Val] > 1) {
325: System.out.println(label
326: + " returned a duplicate.");
327: errorCount++;
328: continue;
329: }
330: } else if (dupFoundCount[-col1Val] > expectedDupCount[-col1Val]) {
331: System.out.println(label
332: + " returned too many duplicates.");
333: errorCount++;
334: continue;
335: }
336: } else {
337: if (found.get(col1Val)) {
338: System.out.println(label
339: + " returned a duplicate.");
340: errorCount++;
341: continue;
342: }
343: found.set(col1Val);
344: count++;
345: }
346: if (holdOverCommit) {
347: rs.getStatement().getConnection().commit();
348: holdOverCommit = false;
349: }
350: }
351: if (count != maxColValue) {
352: System.out.println("Incorrect number of rows in "
353: + label);
354: errorCount++;
355: }
356: for (int i = 0; i < dupFoundCount.length; i++) {
357: if (dupFoundCount[i] != expectedDupCount[i]) {
358: System.out
359: .println("A duplicate key row is missing in "
360: + label);
361: errorCount++;
362: break;
363: }
364: }
365: } finally {
366: rs.close();
367: }
368: } // End of checkAllCa1
369:
370: private static void runCursor(Connection conn, int maxColValue,
371: String[][][] dupVals) throws SQLException {
372: System.out.println("Running scroll insensitive cursor");
373: DatabaseMetaData dmd = conn.getMetaData();
374: boolean holdOverCommit = dmd.supportsOpenCursorsAcrossCommit();
375: Statement stmt;
376: if (holdOverCommit)
377: stmt = conn.createStatement(
378: ResultSet.TYPE_SCROLL_INSENSITIVE,
379: ResultSet.CONCUR_READ_ONLY,
380: ResultSet.HOLD_CURSORS_OVER_COMMIT);
381: else
382: stmt = conn.createStatement(
383: ResultSet.TYPE_SCROLL_INSENSITIVE,
384: ResultSet.CONCUR_READ_ONLY);
385: ResultSet rs = stmt.executeQuery("SELECT ca1 FROM ta");
386: checkAllCa1(rs, true, holdOverCommit, maxColValue, dupVals,
387: "scroll insensitive cursor");
388: }
389: }
|