SubqueriesRegressionTest


1   /*
2    Copyright (C) 2002-2004 MySQL AB
3   
4    This program is free software; you can redistribute it and/or modify
5    it under the terms of version 2 of the GNU General Public License as 
6    published by the Free Software Foundation.
7   
8    There are special exceptions to the terms and conditions of the GPL 
9    as it is applied to this software. View the full text of the 
10   exception in file EXCEPTIONS-CONNECTOR-J in the directory of this 
11   software distribution.
12  
13   This program is distributed in the hope that it will be useful,
14   but WITHOUT ANY WARRANTY; without even the implied warranty of
15   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
16   GNU General Public License for more details.
17  
18   You should have received a copy of the GNU General Public License
19   along with this program; if not, write to the Free Software
20   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
21  
22  
23  
24   */
25  package testsuite.regression;
26  
27  import testsuite.BaseTestCase;
28  import testsuite.simple.BlobTest;
29  
30  /**
31   * Tests SubQueries on MySQL > 4.1
32   * 
33   * @author Mark Matthews
34   * @version $Id: SubqueriesRegressionTest.java,v 1.1.2.1 2005/05/13 18:58:38
35   *          mmatthews Exp $
36   */
37  public class SubqueriesRegressionTest extends BaseTestCase {
38      private final static int REPETITIONS = 100;
39  
40      /**
41       * 
42       */
43      public SubqueriesRegressionTest(String   name) {
44          super(name);
45      }
46  
47      /*
48       * (non-Javadoc)
49       * 
50       * @see junit.framework.TestCase#setUp()
51       */
52      public void setUp() throws Exception   {
53          // TODO Auto-generated method stub
54          super.setUp();
55      }
56  
57      /*
58       * (non-Javadoc)
59       * 
60       * @see junit.framework.TestCase#tearDown()
61       */
62      public void tearDown() throws Exception   {
63          // TODO Auto-generated method stub
64          super.tearDown();
65      }
66  
67      /**
68       * Runs all test cases in this test suite
69       * 
70       * @param args
71       */
72      public static void main(String  [] args) {
73          junit.textui.TestRunner.run(SubqueriesRegressionTest.class);
74      }
75  
76      /**
77       * DOCUMENT ME!
78       * 
79       * @throws Exception
80       *             DOCUMENT ME!
81       */
82      public void testSubQuery1() throws Exception   {
83          if (versionMeetsMinimum(4, 1)) {
84              for (int i = 0; i < REPETITIONS; i++) {
85                  createTables();
86  
87                  try {
88                      this.rs = this.stmt
89                              .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)");
90                      assertTrue(this.rs.next());
91                      assertTrue("bbbb".equals(this.rs.getString(1)));
92                      assertTrue(!this.rs.next());
93                  } finally {
94                      try {
95                          if (this.rs != null) {
96                              this.rs.close();
97                          }
98                      } finally {
99                          dropTables();
100                     }
101                 }
102             }
103         }
104     }
105 
106     /**
107      * DOCUMENT ME!
108      * 
109      * @throws Exception
110      *             DOCUMENT ME!
111      */
112     public void testSubQuery2() throws Exception   {
113         if (versionMeetsMinimum(4, 1)) {
114             for (int i = 0; i < REPETITIONS; i++) {
115                 createTables();
116 
117                 try {
118                     this.rs = this.stmt
119                             .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)");
120                     assertTrue(this.rs.next());
121                     assertTrue("bbbb".equals(this.rs.getString(1)));
122                     assertTrue(!this.rs.next());
123                 } finally {
124                     try {
125                         if (this.rs != null) {
126                             this.rs.close();
127                         }
128                     } finally {
129                         dropTables();
130                     }
131                 }
132             }
133         }
134     }
135 
136     /**
137      * DOCUMENT ME!
138      * 
139      * @throws Exception
140      *             DOCUMENT ME!
141      */
142     public void testSubQuery3() throws Exception   {
143         if (versionMeetsMinimum(4, 1)) {
144             for (int i = 0; i < REPETITIONS; i++) {
145                 createTables();
146 
147                 try {
148                     this.rs = this.stmt
149                             .executeQuery("select * from t1 where t1.colA = 'efgh' and exists (select 'X' from t2 where t2.colB = t1.colB)");
150                     assertTrue(this.rs.next());
151                     assertTrue("efgh".equals(this.rs.getString(1)));
152                     assertTrue("2".equals(this.rs.getString(2)));
153                     assertTrue(!this.rs.next());
154                 } finally {
155                     try {
156                         if (this.rs != null) {
157                             this.rs.close();
158                         }
159                     } finally {
160                         dropTables();
161                     }
162                 }
163             }
164         }
165     }
166 
167     /**
168      * DOCUMENT ME!
169      * 
170      * @throws Exception
171      *             DOCUMENT ME!
172      */
173     public void testSubQuery4() throws Exception   {
174         // not really a subquery, but we want to have this in our testsuite
175         if (versionMeetsMinimum(4, 1)) {
176             for (int i = 0; i < REPETITIONS; i++) {
177                 createTables();
178 
179                 try {
180                     this.rs = this.stmt
181                             .executeQuery("select colA, '' from t2 union select colA, colB from t3");
182 
183                     assertTrue(this.rs.next());
184                     assertTrue("type1".equals(this.rs.getString(1)));
185                     assertTrue("".equals(this.rs.getString(2)));
186 
187                     assertTrue(this.rs.next());
188                     assertTrue("type2".equals(this.rs.getString(1)));
189                     assertTrue("".equals(this.rs.getString(2)));
190 
191                     assertTrue(this.rs.next());
192                     assertTrue("type3".equals(this.rs.getString(1)));
193                     assertTrue("".equals(this.rs.getString(2)));
194 
195                     assertTrue(this.rs.next());
196                     assertTrue("aaaa".equals(this.rs.getString(1)));
197                     assertTrue("'" + this.rs.getString(2)
198                             + "' != expected of 'abcd'", "abcd".equals(this.rs
199                             .getString(2)));
200 
201                     assertTrue(this.rs.next());
202                     assertTrue("bbbb".equals(this.rs.getString(1)));
203                     assertTrue("efgh".equals(this.rs.getString(2)));
204 
205                     assertTrue(this.rs.next());
206                     assertTrue("cccc".equals(this.rs.getString(1)));
207                     assertTrue("'" + this.rs.getString(2)
208                             + "' != expected of 'ijkl'", "ijkl".equals(this.rs
209                             .getString(2)));
210 
211                     assertTrue(!this.rs.next());
212                 } finally {
213                     try {
214                         if (this.rs != null) {
215                             this.rs.close();
216                         }
217                     } finally {
218                         dropTables();
219                     }
220                 }
221             }
222         }
223     }
224 
225     /**
226      * DOCUMENT ME!
227      * 
228      * @throws Exception
229      *             DOCUMENT ME!
230      */
231     public void testSubQuery5() throws Exception   {
232         if (versionMeetsMinimum(4, 1)) {
233             for (int i = 0; i < REPETITIONS; i++) {
234                 createTables();
235 
236                 try {
237                     this.rs = this.stmt
238                             .executeQuery("select t1.colA from t1, t4 where t4.colA = t1.colA and exists (select 'X' from t2 where t2.colA = t4.colB)");
239                     assertTrue(this.rs.next());
240                     assertTrue("abcd".equals(this.rs.getString(1)));
241                     assertTrue(this.rs.next());
242                     assertTrue("efgh".equals(this.rs.getString(1)));
243                     assertTrue(this.rs.next());
244                     assertTrue("ijkl".equals(this.rs.getString(1)));
245                     assertTrue(!this.rs.next());
246                 } finally {
247                     try {
248                         if (this.rs != null) {
249                             this.rs.close();
250                         }
251                     } finally {
252                         dropTables();
253                     }
254                 }
255             }
256         }
257     }
258 
259     private void createTables() throws Exception   {
260         this.stmt.executeUpdate("drop table if exists t1");
261         this.stmt.executeUpdate("drop table if exists t1");
262         this.stmt.executeUpdate("drop table if exists t2");
263         this.stmt.executeUpdate("drop table if exists t3");
264         this.stmt.executeUpdate("drop table if exists t4");
265         this.stmt
266                 .executeUpdate("create table t1(colA varchar(10), colB decimal(3,0))");
267         this.stmt
268                 .executeUpdate("create table t2(colA varchar(10), colB varchar(10))");
269         this.stmt
270                 .executeUpdate("create table t3(colA varchar(10), colB varchar(10))");
271         this.stmt
272                 .executeUpdate("create table t4(colA varchar(10), colB varchar(10))");
273         this.stmt
274                 .executeUpdate("insert into t1 values ('abcd', 1), ('efgh', 2), ('ijkl', 3)");
275         this.stmt
276                 .executeUpdate("insert into t2 values ('type1', '1'), ('type2', '2'), ('type3', '3')");
277         this.stmt
278                 .executeUpdate("insert into t3 values ('aaaa', 'abcd'), ('bbbb', 'efgh'), ('cccc', 'ijkl')");
279         this.stmt
280                 .executeUpdate("insert into t4 values ('abcd', 'type1'), ('efgh', 'type2'), ('ijkl', 'type3')");
281     }
282 
283     private void dropTables() throws Exception   {
284         this.stmt.executeUpdate("drop table if exists t1");
285         this.stmt.executeUpdate("drop table if exists t1");
286         this.stmt.executeUpdate("drop table if exists t2");
287         this.stmt.executeUpdate("drop table if exists t3");
288         this.stmt.executeUpdate("drop table if exists t4");
289     }
290 }
291
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags