001: /*
002: Copyright (C) 2002-2004 MySQL AB
003:
004: This program is free software; you can redistribute it and/or modify
005: it under the terms of version 2 of the GNU General Public License as
006: published by the Free Software Foundation.
007:
008: There are special exceptions to the terms and conditions of the GPL
009: as it is applied to this software. View the full text of the
010: exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
011: software distribution.
012:
013: This program is distributed in the hope that it will be useful,
014: but WITHOUT ANY WARRANTY; without even the implied warranty of
015: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
016: GNU General Public License for more details.
017:
018: You should have received a copy of the GNU General Public License
019: along with this program; if not, write to the Free Software
020: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
021:
022:
023:
024: */
025: package testsuite.regression;
026:
027: import testsuite.BaseTestCase;
028:
029: /**
030: * Tests SubQueries on MySQL > 4.1
031: *
032: * @author Mark Matthews
033: * @version $Id: SubqueriesRegressionTest.java,v 1.1.2.1 2005/05/13 18:58:38
034: * mmatthews Exp $
035: */
036: public class SubqueriesRegressionTest extends BaseTestCase {
037: private final static int REPETITIONS = 100;
038:
039: /**
040: *
041: */
042: public SubqueriesRegressionTest(String name) {
043: super (name);
044: }
045:
046: /*
047: * (non-Javadoc)
048: *
049: * @see junit.framework.TestCase#setUp()
050: */
051: public void setUp() throws Exception {
052: super .setUp();
053:
054: createTables();
055: }
056:
057: /*
058: * (non-Javadoc)
059: *
060: * @see junit.framework.TestCase#tearDown()
061: */
062: public void tearDown() throws Exception {
063: dropTables();
064:
065: super .tearDown();
066: }
067:
068: /**
069: * Runs all test cases in this test suite
070: *
071: * @param args
072: */
073: public static void main(String[] args) {
074: junit.textui.TestRunner.run(SubqueriesRegressionTest.class);
075: }
076:
077: /**
078: * DOCUMENT ME!
079: *
080: * @throws Exception
081: * DOCUMENT ME!
082: */
083: public void testSubQuery1() throws Exception {
084: if (versionMeetsMinimum(4, 1)) {
085: for (int i = 0; i < REPETITIONS; i++) {
086:
087: try {
088: this .rs = this .stmt
089: .executeQuery("select t3.colA from t3, t1 where t3.colA = 'bbbb' and t3.colB = t1.colA and exists (select 'X' from t2 where t2.colB = t1.colB)");
090: assertTrue(this .rs.next());
091: assertTrue("bbbb".equals(this .rs.getString(1)));
092: assertTrue(!this .rs.next());
093: } finally {
094: if (this .rs != null) {
095: this .rs.close();
096: }
097: }
098: }
099: }
100: }
101:
102: /**
103: * DOCUMENT ME!
104: *
105: * @throws Exception
106: * DOCUMENT ME!
107: */
108: public void testSubQuery2() throws Exception {
109: if (versionMeetsMinimum(4, 1)) {
110: for (int i = 0; i < REPETITIONS; i++) {
111: try {
112: this .rs = this .stmt
113: .executeQuery("select t3.colA from t3, t1 where t3.colA = 'bbbb' and t3.colB = t1.colA and exists (select 'X' from t2 where t2.colB = 2)");
114: assertTrue(this .rs.next());
115: assertTrue("bbbb".equals(this .rs.getString(1)));
116: assertTrue(!this .rs.next());
117: } finally {
118: if (this .rs != null) {
119: this .rs.close();
120: }
121: }
122: }
123: }
124: }
125:
126: /**
127: * DOCUMENT ME!
128: *
129: * @throws Exception
130: * DOCUMENT ME!
131: */
132: public void testSubQuery3() throws Exception {
133: if (versionMeetsMinimum(4, 1)) {
134: for (int i = 0; i < REPETITIONS; i++) {
135: try {
136: this .rs = this .stmt
137: .executeQuery("select * from t1 where t1.colA = 'efgh' and exists (select 'X' from t2 where t2.colB = t1.colB)");
138: assertTrue(this .rs.next());
139: assertTrue("efgh".equals(this .rs.getString(1)));
140: assertTrue("2".equals(this .rs.getString(2)));
141: assertTrue(!this .rs.next());
142: } finally {
143: if (this .rs != null) {
144: this .rs.close();
145: }
146:
147: }
148: }
149: }
150: }
151:
152: /**
153: * DOCUMENT ME!
154: *
155: * @throws Exception
156: * DOCUMENT ME!
157: */
158: public void testSubQuery4() throws Exception {
159: // not really a subquery, but we want to have this in our testsuite
160: if (versionMeetsMinimum(4, 1)) {
161: for (int i = 0; i < REPETITIONS; i++) {
162: try {
163: this .rs = this .stmt
164: .executeQuery("select colA, '' from t2 union select colA, colB from t3");
165:
166: assertTrue(this .rs.next());
167: assertTrue("type1".equals(this .rs.getString(1)));
168: assertTrue("".equals(this .rs.getString(2)));
169:
170: assertTrue(this .rs.next());
171: assertTrue("type2".equals(this .rs.getString(1)));
172: assertTrue("".equals(this .rs.getString(2)));
173:
174: assertTrue(this .rs.next());
175: assertTrue("type3".equals(this .rs.getString(1)));
176: assertTrue("".equals(this .rs.getString(2)));
177:
178: assertTrue(this .rs.next());
179: assertTrue("aaaa".equals(this .rs.getString(1)));
180: assertTrue("'" + this .rs.getString(2)
181: + "' != expected of 'abcd'", "abcd"
182: .equals(this .rs.getString(2)));
183:
184: assertTrue(this .rs.next());
185: assertTrue("bbbb".equals(this .rs.getString(1)));
186: assertTrue("efgh".equals(this .rs.getString(2)));
187:
188: assertTrue(this .rs.next());
189: assertTrue("cccc".equals(this .rs.getString(1)));
190: assertTrue("'" + this .rs.getString(2)
191: + "' != expected of 'ijkl'", "ijkl"
192: .equals(this .rs.getString(2)));
193:
194: assertTrue(!this .rs.next());
195: } finally {
196: if (this .rs != null) {
197: this .rs.close();
198: }
199: }
200: }
201: }
202: }
203:
204: /**
205: * DOCUMENT ME!
206: *
207: * @throws Exception
208: * DOCUMENT ME!
209: */
210: public void testSubQuery5() throws Exception {
211: if (versionMeetsMinimum(4, 1)) {
212: for (int i = 0; i < REPETITIONS; i++) {
213:
214: try {
215: this .rs = this .stmt
216: .executeQuery("select t1.colA from t1, t4 where t4.colA = t1.colA and exists (select 'X' from t2 where t2.colA = t4.colB)");
217: assertTrue(this .rs.next());
218: assertTrue("abcd".equals(this .rs.getString(1)));
219: assertTrue(this .rs.next());
220: assertTrue("efgh".equals(this .rs.getString(1)));
221: assertTrue(this .rs.next());
222: assertTrue("ijkl".equals(this .rs.getString(1)));
223: assertTrue(!this .rs.next());
224: } finally {
225: if (this .rs != null) {
226: this .rs.close();
227: }
228: }
229: }
230: }
231: }
232:
233: private void createTables() throws Exception {
234: this .stmt.executeUpdate("drop table if exists t1");
235: this .stmt.executeUpdate("drop table if exists t1");
236: this .stmt.executeUpdate("drop table if exists t2");
237: this .stmt.executeUpdate("drop table if exists t3");
238: this .stmt.executeUpdate("drop table if exists t4");
239: this .stmt
240: .executeUpdate("create table t1(colA varchar(10), colB decimal(3,0))");
241: this .stmt
242: .executeUpdate("create table t2(colA varchar(10), colB varchar(10))");
243: this .stmt
244: .executeUpdate("create table t3(colA varchar(10), colB varchar(10))");
245: this .stmt
246: .executeUpdate("create table t4(colA varchar(10), colB varchar(10))");
247: this .stmt
248: .executeUpdate("insert into t1 values ('abcd', 1), ('efgh', 2), ('ijkl', 3)");
249: this .stmt
250: .executeUpdate("insert into t2 values ('type1', '1'), ('type2', '2'), ('type3', '3')");
251: this .stmt
252: .executeUpdate("insert into t3 values ('aaaa', 'abcd'), ('bbbb', 'efgh'), ('cccc', 'ijkl')");
253: this .stmt
254: .executeUpdate("insert into t4 values ('abcd', 'type1'), ('efgh', 'type2'), ('ijkl', 'type3')");
255: }
256:
257: private void dropTables() throws Exception {
258: this .stmt.executeUpdate("drop table if exists t1");
259: this .stmt.executeUpdate("drop table if exists t1");
260: this .stmt.executeUpdate("drop table if exists t2");
261: this .stmt.executeUpdate("drop table if exists t3");
262: this .stmt.executeUpdate("drop table if exists t4");
263: }
264: }
|