0001: /*
0002: * $Id: TestDQLMisc.java,v 1.15 2005/07/06 18:47:40 ahimanikya Exp $
0003: * =======================================================================
0004: * Copyright (c) 2002-2005 Axion Development Team. All rights reserved.
0005: *
0006: * Redistribution and use in source and binary forms, with or without
0007: * modification, are permitted provided that the following conditions
0008: * are met:
0009: *
0010: * 1. Redistributions of source code must retain the above
0011: * copyright notice, this list of conditions and the following
0012: * disclaimer.
0013: *
0014: * 2. Redistributions in binary form must reproduce the above copyright
0015: * notice, this list of conditions and the following disclaimer in
0016: * the documentation and/or other materials provided with the
0017: * distribution.
0018: *
0019: * 3. The names "Tigris", "Axion", nor the names of its contributors may
0020: * not be used to endorse or promote products derived from this
0021: * software without specific prior written permission.
0022: *
0023: * 4. Products derived from this software may not be called "Axion", nor
0024: * may "Tigris" or "Axion" appear in their names without specific prior
0025: * written permission.
0026: *
0027: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
0028: * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
0029: * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
0030: * PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
0031: * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
0032: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
0033: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
0034: * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
0035: * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
0036: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
0037: * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
0038: * =======================================================================
0039: */
0040:
0041: package org.axiondb.functional;
0042:
0043: import java.sql.SQLException;
0044:
0045: import junit.framework.Test;
0046: import junit.framework.TestSuite;
0047:
0048: /**
0049: * Database Query Language tests.
0050: *
0051: * @version $Revision: 1.15 $ $Date: 2005/07/06 18:47:40 $
0052: * @author Amrish Lal
0053: * @author Chris Johnston
0054: * @author Ritesh Adval
0055: * @author Jonathan Giron
0056: * @author Ahimanikya Satapathy
0057: */
0058: public class TestDQLMisc extends AbstractFunctionalTest {
0059:
0060: public static Test suite() {
0061: return new TestSuite(TestDQLMisc.class);
0062: }
0063:
0064: //------------------------------------------------------------ Conventional
0065:
0066: public TestDQLMisc(String testName) {
0067: super (testName);
0068: }
0069:
0070: //--------------------------------------------------------------- Lifecycle
0071:
0072: public void setUp() throws Exception {
0073: super .setUp();
0074: }
0075:
0076: public void tearDown() throws Exception {
0077: super .tearDown();
0078: }
0079:
0080: public void test_select_str_from_x_inner_y_inner_z()
0081: throws Exception {
0082:
0083: try {
0084: createTableX();
0085: createTableY();
0086: createTableZ();
0087: populateTableX();
0088: populateTableY();
0089: populateTableZ();
0090:
0091: String sql = "SELECT * FROM x INNER JOIN y ON(x.a=y.a) INNER JOIN z ON(x.a=z.a)";
0092: _rset = _stmt.executeQuery(sql);
0093: assertNotNull("Should have been able to create ResultSet",
0094: _rset);
0095: int rslt = 3;
0096: int count = 0;
0097: Integer xa_col1 = null;
0098: Integer xb_col2 = null;
0099: Integer ya_col3 = null;
0100: Integer yb_col4 = null;
0101: Integer za_col5 = null;
0102: Integer zb_col6 = null;
0103:
0104: while (_rset.next()) {
0105:
0106: xa_col1 = (Integer) _rset.getObject(1);
0107: xb_col2 = (Integer) _rset.getObject(2);
0108: ya_col3 = (Integer) _rset.getObject(3);
0109: yb_col4 = (Integer) _rset.getObject(4);
0110: za_col5 = (Integer) _rset.getObject(5);
0111: zb_col6 = (Integer) _rset.getObject(6);
0112:
0113: switch (ya_col3.intValue()) {
0114: case 3:
0115: assertEquals(xa_col1.intValue(), 3);
0116: assertEquals(xb_col2.intValue(), 3);
0117: assertEquals(ya_col3.intValue(), 3);
0118: assertEquals(yb_col4.intValue(), 30);
0119: assertEquals(za_col5.intValue(), 3);
0120: assertEquals(zb_col6.intValue(), 300);
0121: break;
0122: default:
0123: assertTrue("Unknown row " + za_col5.intValue(),
0124: false);
0125: break;
0126: }
0127: rslt++;
0128: count++;
0129: }
0130: } finally {
0131: dropTableX();
0132: dropTableY();
0133: dropTableZ();
0134: }
0135: }
0136:
0137: public void test_select_str_from_x_inner_y_loj_z() throws Exception {
0138: int rslt = 3;
0139: int count = 0;
0140: Integer xa_col1 = null;
0141: Integer xb_col2 = null;
0142: Integer ya_col3 = null;
0143: Integer yb_col4 = null;
0144: Integer za_col5 = null;
0145: Integer zb_col6 = null;
0146:
0147: try {
0148: createTableX();
0149: createTableY();
0150: createTableZ();
0151: populateTableX();
0152: populateTableY();
0153: populateTableZ();
0154: String sql = "SELECT * FROM x INNER JOIN y ON(x.a=y.a) LEFT OUTER JOIN z ON(x.a=z.a)";
0155: _rset = _stmt.executeQuery(sql);
0156: assertNotNull("Should have been able to create ResultSet",
0157: _rset);
0158: while (_rset.next()) {
0159:
0160: xa_col1 = (Integer) _rset.getObject(1);
0161: xb_col2 = (Integer) _rset.getObject(2);
0162: ya_col3 = (Integer) _rset.getObject(3);
0163: yb_col4 = (Integer) _rset.getObject(4);
0164: za_col5 = (Integer) _rset.getObject(5);
0165: zb_col6 = (Integer) _rset.getObject(6);
0166:
0167: switch (xa_col1.intValue()) {
0168: case 2:
0169: assertEquals(xa_col1.intValue(), 2);
0170: assertEquals(xb_col2.intValue(), 2);
0171: assertEquals(ya_col3.intValue(), 2);
0172: assertEquals(yb_col4.intValue(), 20);
0173: assertEquals(za_col5, null);
0174: assertEquals(zb_col6, null);
0175: break;
0176: case 3:
0177: assertEquals(xa_col1.intValue(), 3);
0178: assertEquals(xb_col2.intValue(), 3);
0179: assertEquals(ya_col3.intValue(), 3);
0180: assertEquals(yb_col4.intValue(), 30);
0181: assertEquals(za_col5.intValue(), 3);
0182: assertEquals(zb_col6.intValue(), 300);
0183: break;
0184: default:
0185: assertTrue("Unknown row " + za_col5.intValue(),
0186: false);
0187: break;
0188: }
0189: rslt++;
0190: count++;
0191: }
0192: } finally {
0193: dropTableX();
0194: dropTableY();
0195: dropTableZ();
0196: }
0197: }
0198:
0199: public void test_select_str_from_x_inner_y_roj_z() throws Exception {
0200: int rslt = 3;
0201: int count = 0;
0202: Integer xa_col1 = null;
0203: Integer xb_col2 = null;
0204: Integer ya_col3 = null;
0205: Integer yb_col4 = null;
0206: Integer za_col5 = null;
0207: Integer zb_col6 = null;
0208:
0209: try {
0210: createTableX();
0211: createTableY();
0212: createTableZ();
0213: populateTableX();
0214: populateTableY();
0215: populateTableZ();
0216: String sql = "SELECT * FROM x INNER JOIN y ON(x.a=y.a) RIGHT OUTER JOIN z ON(x.a=z.a)";
0217: _rset = _stmt.executeQuery(sql);
0218: assertNotNull("Should have been able to create ResultSet",
0219: _rset);
0220: while (_rset.next()) {
0221:
0222: xa_col1 = (Integer) _rset.getObject(1);
0223: xb_col2 = (Integer) _rset.getObject(2);
0224: ya_col3 = (Integer) _rset.getObject(3);
0225: yb_col4 = (Integer) _rset.getObject(4);
0226: za_col5 = (Integer) _rset.getObject(5);
0227: zb_col6 = (Integer) _rset.getObject(6);
0228:
0229: switch (za_col5.intValue()) {
0230: case 3:
0231: assertEquals(xa_col1.intValue(), 3);
0232: assertEquals(xb_col2.intValue(), 3);
0233: assertEquals(ya_col3.intValue(), 3);
0234: assertEquals(yb_col4.intValue(), 30);
0235: assertEquals(za_col5.intValue(), 3);
0236: assertEquals(zb_col6.intValue(), 300);
0237: break;
0238: case 4:
0239: assertEquals(xa_col1, null);
0240: assertEquals(xb_col2, null);
0241: assertEquals(ya_col3, null);
0242: assertEquals(yb_col4, null);
0243: assertEquals(za_col5.intValue(), 4);
0244: assertEquals(zb_col6.intValue(), 400);
0245: break;
0246: case 5:
0247: assertEquals(xa_col1, null);
0248: assertEquals(xb_col2, null);
0249: assertEquals(ya_col3, null);
0250: assertEquals(yb_col4, null);
0251: assertEquals(za_col5.intValue(), 5);
0252: assertEquals(zb_col6.intValue(), 500);
0253: break;
0254: default:
0255: assertTrue("Unknown row " + za_col5.intValue(),
0256: false);
0257: break;
0258: }
0259: rslt++;
0260: count++;
0261: }
0262: } finally {
0263: dropTableX();
0264: dropTableY();
0265: dropTableZ();
0266: }
0267: }
0268:
0269: public void test_select_str_from_x_loj_y_inner_z() throws Exception {
0270: int rslt = 3;
0271: int count = 0;
0272: Integer xa_col1 = null;
0273: Integer xb_col2 = null;
0274: Integer ya_col3 = null;
0275: Integer yb_col4 = null;
0276: Integer za_col5 = null;
0277: Integer zb_col6 = null;
0278:
0279: try {
0280: createTableX();
0281: createTableY();
0282: createTableZ();
0283: populateTableX();
0284: populateTableY();
0285: populateTableZ();
0286: String sql = "SELECT * FROM x LEFT OUTER JOIN y ON(x.a=y.a) INNER JOIN z ON(x.a=z.a)";
0287: _rset = _stmt.executeQuery(sql);
0288: assertNotNull("Should have been able to create ResultSet",
0289: _rset);
0290: while (_rset.next()) {
0291:
0292: xa_col1 = (Integer) _rset.getObject(1);
0293: xb_col2 = (Integer) _rset.getObject(2);
0294: ya_col3 = (Integer) _rset.getObject(3);
0295: yb_col4 = (Integer) _rset.getObject(4);
0296: za_col5 = (Integer) _rset.getObject(5);
0297: zb_col6 = (Integer) _rset.getObject(6);
0298:
0299: switch (xa_col1.intValue()) {
0300: case 3:
0301: assertEquals(xa_col1.intValue(), 3);
0302: assertEquals(xb_col2.intValue(), 3);
0303: assertEquals(ya_col3.intValue(), 3);
0304: assertEquals(yb_col4.intValue(), 30);
0305: assertEquals(za_col5.intValue(), 3);
0306: assertEquals(zb_col6.intValue(), 300);
0307: break;
0308: case 4:
0309: assertEquals(xa_col1.intValue(), 4);
0310: assertEquals(xb_col2.intValue(), 4);
0311: assertEquals(ya_col3, null);
0312: assertEquals(yb_col4, null);
0313: assertEquals(za_col5.intValue(), 4);
0314: assertEquals(zb_col6.intValue(), 400);
0315: break;
0316: default:
0317: assertTrue("Unknown row " + za_col5.intValue(),
0318: false);
0319: break;
0320: }
0321: rslt++;
0322: count++;
0323: }
0324: } finally {
0325: dropTableX();
0326: dropTableY();
0327: dropTableZ();
0328: }
0329: }
0330:
0331: public void test_select_str_from_x_loj_y_loj_z() throws Exception {
0332: createTableX();
0333: createTableY();
0334: createTableZ();
0335: populateTableX();
0336: populateTableY();
0337: populateTableZ();
0338:
0339: String sql = "SELECT * FROM x LEFT OUTER JOIN y ON(y.a=x.a) LEFT OUTER JOIN Z ON(x.a=z.a)";
0340: _rset = _stmt.executeQuery(sql);
0341: assertNotNull("Should have been able to create ResultSet",
0342: _rset);
0343: int rslt = 2;
0344: int count = 0;
0345: while (_rset.next()) {
0346: assertEquals("Should have " + rslt + " in row-" + count
0347: + " 1st column.", rslt, _rset.getInt(1));
0348: assertEquals("Should have " + rslt + " in row-" + count
0349: + " 2st column.", rslt, _rset.getInt(2));
0350: if (count == 0 || count == 1) {
0351: assertEquals("Should have " + rslt + " in row-" + count
0352: + " 3rd column.", rslt, _rset.getInt(3));
0353: assertEquals("Should have " + (rslt * 10) + " in row-"
0354: + count + " 4rd column.", rslt * 10, _rset
0355: .getInt(4));
0356: } else {
0357: assertEquals("Should have NULL in row-" + count
0358: + " 3rd column.", null, _rset.getObject(3));
0359: assertEquals("Should have NULL in row-" + count
0360: + " 4rd column.", null, _rset.getObject(4));
0361: }
0362:
0363: if (count == 0) {
0364: assertEquals("Should have NULL in row-" + count
0365: + " 5th column.", null, _rset.getObject(5));
0366: assertEquals("Should have NULL in row-" + count
0367: + " 6th column.", null, _rset.getObject(6));
0368: } else {
0369: assertEquals("Should have " + (rslt) + " in row-"
0370: + count + " 5th column.", (rslt), _rset
0371: .getInt(5));
0372: assertEquals("Should have " + (rslt * 100) + " in row-"
0373: + count + " 6th column.", (rslt * 100), _rset
0374: .getInt(6));
0375: }
0376:
0377: rslt++;
0378: count++;
0379: }
0380: assertEquals("Number of rows selected should be 3", 3, count);
0381: }
0382:
0383: public void test_select_str_from_x_loj_y_roj_z() throws Exception {
0384: createTableX();
0385: createTableY();
0386: createTableZ();
0387: populateTableX();
0388: populateTableY();
0389: populateTableZ();
0390:
0391: String sql = "SELECT * FROM x LEFT OUTER JOIN y ON(x.a=y.a) RIGHT OUTER JOIN Z ON(x.a=z.a)";
0392: _rset = _stmt.executeQuery(sql);
0393: assertNotNull("Should have been able to create ResultSet",
0394: _rset);
0395: int rslt = 2;
0396: int count = 0;
0397: Integer xa_col1 = null;
0398: Integer xb_col2 = null;
0399: Integer ya_col3 = null;
0400: Integer yb_col4 = null;
0401: Integer za_col5 = null;
0402: Integer zb_col6 = null;
0403: while (_rset.next()) {
0404:
0405: xa_col1 = (Integer) _rset.getObject(1);
0406: xb_col2 = (Integer) _rset.getObject(2);
0407: ya_col3 = (Integer) _rset.getObject(3);
0408: yb_col4 = (Integer) _rset.getObject(4);
0409: za_col5 = (Integer) _rset.getObject(5);
0410: zb_col6 = (Integer) _rset.getObject(6);
0411:
0412: switch (za_col5.intValue()) {
0413: case 3:
0414: assertEquals(xa_col1.intValue(), 3);
0415: assertEquals(xb_col2.intValue(), 3);
0416: assertEquals(ya_col3.intValue(), 3);
0417: assertEquals(yb_col4.intValue(), 30);
0418: assertEquals(za_col5.intValue(), 3);
0419: assertEquals(zb_col6.intValue(), 300);
0420: break;
0421: case 4:
0422: assertEquals(xa_col1.intValue(), 4);
0423: assertEquals(xb_col2.intValue(), 4);
0424: assertEquals(ya_col3, null);
0425: assertEquals(yb_col4, null);
0426: assertEquals(za_col5.intValue(), 4);
0427: assertEquals(zb_col6.intValue(), 400);
0428: break;
0429: case 5:
0430: assertEquals(xa_col1, null);
0431: assertEquals(xb_col2, null);
0432: assertEquals(ya_col3, null);
0433: assertEquals(yb_col4, null);
0434: assertEquals(za_col5.intValue(), 5);
0435: assertEquals(zb_col6.intValue(), 500);
0436: break;
0437: default:
0438: assertTrue("Unknown row " + za_col5.intValue(), false);
0439: break;
0440: }
0441: rslt++;
0442: count++;
0443: }
0444: assertEquals("Number of rows selected should be 3", 3, count);
0445: }
0446:
0447: public void test_select_str_from_x_roj_y_inner_z() throws Exception {
0448:
0449: int rslt = 3;
0450: int count = 0;
0451: Integer xa_col1 = null;
0452: Integer xb_col2 = null;
0453: Integer ya_col3 = null;
0454: Integer yb_col4 = null;
0455: Integer za_col5 = null;
0456: Integer zb_col6 = null;
0457:
0458: try {
0459: createTableX();
0460: createTableY();
0461: createTableZ();
0462: populateTableX();
0463: populateTableY();
0464: populateTableZ();
0465:
0466: String sql = "SELECT * FROM x RIGHT OUTER JOIN y ON(x.a=y.a) INNER JOIN z ON(x.a=z.a)";
0467: _rset = _stmt.executeQuery(sql);
0468: assertNotNull("Should have been able to create ResultSet",
0469: _rset);
0470: while (_rset.next()) {
0471:
0472: xa_col1 = (Integer) _rset.getObject(1);
0473: xb_col2 = (Integer) _rset.getObject(2);
0474: ya_col3 = (Integer) _rset.getObject(3);
0475: yb_col4 = (Integer) _rset.getObject(4);
0476: za_col5 = (Integer) _rset.getObject(5);
0477: zb_col6 = (Integer) _rset.getObject(6);
0478:
0479: switch (ya_col3.intValue()) {
0480: case 3:
0481: assertEquals(xa_col1.intValue(), 3);
0482: assertEquals(xb_col2.intValue(), 3);
0483: assertEquals(ya_col3.intValue(), 3);
0484: assertEquals(yb_col4.intValue(), 30);
0485: assertEquals(za_col5.intValue(), 3);
0486: assertEquals(zb_col6.intValue(), 300);
0487: break;
0488: default:
0489: assertTrue("Unknown row " + za_col5.intValue(),
0490: false);
0491: break;
0492: }
0493: rslt++;
0494: count++;
0495: }
0496: } finally {
0497: dropTableX();
0498: dropTableY();
0499: dropTableZ();
0500: }
0501: }
0502:
0503: public void test_select_str_from_x_roj_y_loj_z() throws Exception {
0504: createTableX();
0505: createTableY();
0506: createTableZ();
0507: populateTableX();
0508: populateTableY();
0509: populateTableZ();
0510:
0511: String sql = "SELECT * FROM x RIGHT OUTER JOIN y ON(x.a=y.a) LEFT OUTER JOIN z ON(x.a=z.a)";
0512: _rset = _stmt.executeQuery(sql);
0513: assertNotNull("Should have been able to create ResultSet",
0514: _rset);
0515: int rslt = 3;
0516: int count = 0;
0517: Integer xa_col1 = null;
0518: Integer xb_col2 = null;
0519: Integer ya_col3 = null;
0520: Integer yb_col4 = null;
0521: Integer za_col5 = null;
0522: Integer zb_col6 = null;
0523: while (_rset.next()) {
0524:
0525: xa_col1 = (Integer) _rset.getObject(1);
0526: xb_col2 = (Integer) _rset.getObject(2);
0527: ya_col3 = (Integer) _rset.getObject(3);
0528: yb_col4 = (Integer) _rset.getObject(4);
0529: za_col5 = (Integer) _rset.getObject(5);
0530: zb_col6 = (Integer) _rset.getObject(6);
0531:
0532: switch (ya_col3.intValue()) {
0533: case 1:
0534: assertEquals(xa_col1, null);
0535: assertEquals(xb_col2, null);
0536: assertEquals(ya_col3.intValue(), 1);
0537: assertEquals(yb_col4.intValue(), 10);
0538: assertEquals(za_col5, null);
0539: assertEquals(zb_col6, null);
0540: break;
0541: case 2:
0542: assertEquals(xa_col1.intValue(), 2);
0543: assertEquals(xb_col2.intValue(), 2);
0544: assertEquals(ya_col3.intValue(), 2);
0545: assertEquals(yb_col4.intValue(), 20);
0546: assertEquals(za_col5, null);
0547: assertEquals(zb_col6, null);
0548: break;
0549: case 3:
0550: assertEquals(xa_col1.intValue(), 3);
0551: assertEquals(xb_col2.intValue(), 3);
0552: assertEquals(ya_col3.intValue(), 3);
0553: assertEquals(yb_col4.intValue(), 30);
0554: assertEquals(za_col5.intValue(), 3);
0555: assertEquals(zb_col6.intValue(), 300);
0556: break;
0557: default:
0558: assertTrue("Unknown row " + za_col5.intValue(), false);
0559: break;
0560: }
0561: rslt++;
0562: count++;
0563: }
0564: }
0565:
0566: public void test_select_str_from_x_roj_y_roj_z() throws Exception {
0567: createTableX();
0568: createTableY();
0569: createTableZ();
0570: populateTableX();
0571: populateTableY();
0572: populateTableZ();
0573:
0574: String sql = "SELECT * FROM x RIGHT OUTER JOIN y ON(x.a=y.a) RIGHT OUTER JOIN z ON(x.a=z.a)";
0575: _rset = _stmt.executeQuery(sql);
0576: assertNotNull("Should have been able to create ResultSet",
0577: _rset);
0578: int rslt = 3;
0579: int count = 0;
0580: while (_rset.next()) {
0581: if (count != 0) {
0582: assertEquals("Should have NULL in row-" + count
0583: + " 1st column.", null, _rset.getObject(1));
0584: assertEquals("Should have NULL in row-" + count
0585: + " 2nd column.", null, _rset.getObject(2));
0586: assertEquals("Should have NULL in row-" + count
0587: + " 3rd column.", null, _rset.getObject(3));
0588: assertEquals("Should have NULL in row-" + count
0589: + " 4rd column.", null, _rset.getObject(4));
0590: } else {
0591: assertEquals("Should have " + rslt + " in row-" + count
0592: + " 1st column.", rslt, _rset.getInt(1));
0593: assertEquals("Should have " + rslt + " in row-" + count
0594: + " 2nd column.", rslt, _rset.getInt(2));
0595: assertEquals("Should have " + rslt + " in row-" + count
0596: + " 3rd column.", rslt, _rset.getInt(3));
0597: assertEquals("Should have " + (rslt * 10) + " in row-"
0598: + count + " 4th column.", (rslt * 10), _rset
0599: .getInt(4));
0600: }
0601: assertEquals("Should have " + (rslt) + " in row-" + count
0602: + " 5th column.", (rslt), _rset.getInt(5));
0603: assertEquals("Should have " + (rslt * 100) + " in row-"
0604: + count + " 6th column.", (rslt * 100), _rset
0605: .getInt(6));
0606: rslt++;
0607: count++;
0608: }
0609: }
0610:
0611: public void testAliasAsColumnName() throws SQLException {
0612: _stmt
0613: .execute("create table test ( test_id int, name varchar(20) )");
0614: _stmt
0615: .execute("insert into test ( test_id, name) values (1, 'SOME_TEST')");
0616: _rset = _stmt
0617: .executeQuery("select test_id as my_id, name as my_name from test");
0618:
0619: assertTrue(_rset.next());
0620: assertEquals(1, _rset.getInt("my_id"));
0621: assertEquals(1, _rset.getInt("test_id"));
0622: assertEquals("SOME_TEST", _rset.getString("my_name"));
0623: assertEquals("SOME_TEST", _rset.getString("name"));
0624: _rset.close();
0625:
0626: }
0627:
0628: public void testAnsiInnerJoinWithIndexBug1() throws Exception {
0629: _stmt
0630: .execute("create table a ( one varchar(10), two varchar(10) )");
0631: _stmt.execute("create index aone on a ( one )");
0632: _stmt
0633: .execute("create table b ( one varchar(10), two varchar(10) )");
0634: _stmt.execute("create index bone on b ( one )");
0635: _stmt.execute("insert into a values ( 'one', 'two' )");
0636: _stmt.execute("insert into b values ( 'one', 'two' )");
0637:
0638: _rset = _stmt
0639: .executeQuery("select * from A inner join B on A.ONE = B.ONE");
0640: assertNotNull(_rset);
0641: assertTrue(_rset.next());
0642: assertTrue(!_rset.next());
0643: _rset.close();
0644: }
0645:
0646: public void testAnsiInnerJoinWithIndexBug2() throws Exception {
0647: _stmt
0648: .execute("create table a ( one varchar(10), two varchar(10) )");
0649: _stmt.execute("create index aone on a ( one )");
0650: _stmt
0651: .execute("create table b ( one varchar(10), two varchar(10) )");
0652: _stmt.execute("create index bone on b ( one )");
0653: _stmt
0654: .execute("create table c ( one varchar(10), two varchar(10) )");
0655: _stmt.execute("create index cone on c ( one )");
0656: _stmt
0657: .execute("create table d ( one varchar(10), two varchar(10) )");
0658: _stmt.execute("create index done on d ( one )");
0659: _stmt.execute("insert into a values ( 'one', 'two' )");
0660: _stmt.execute("insert into b values ( 'one', 'two' )");
0661: _stmt.execute("insert into c values ( 'one', 'two' )");
0662: _stmt.execute("insert into d values ( 'one', 'two' )");
0663:
0664: _rset = _stmt
0665: .executeQuery("select * from A inner join B on A.ONE = B.ONE inner join C on B.ONE = C.ONE inner join D on C.ONE = D.ONE");
0666: assertNotNull(_rset);
0667: assertTrue(_rset.next());
0668: assertTrue(!_rset.next());
0669: _rset.close();
0670: }
0671:
0672: // s (a, b, c), t (d, e, f)
0673: // select * from s where exists (select * from t s where s.c = a)
0674: // will not find a match for s.c, which is the expected ANSI behavior.
0675: public void testColumnBindingInCorrelatedQuery() throws Exception {
0676: _stmt.execute("create table s ( a int, b int, c int)");
0677: _stmt.execute("create table t ( d int, e int, f int)");
0678:
0679: try {
0680: _stmt
0681: .execute("select * from s where exists (select * from t s where s.c = a)");
0682: // FIXME: fail("Expected column not found exception");
0683: } catch (SQLException e) {
0684: // expected
0685: }
0686: }
0687:
0688: public void testFourTableJoinWithRightAsNestedTableView()
0689: throws Exception {
0690: _stmt.execute("create table a ( id int, sid int )");
0691: _stmt.execute("create table b ( id int, sid int )");
0692: _stmt.execute("create table c ( id int, sid int )");
0693: _stmt.execute("create table d ( id int, sid int )");
0694:
0695: _stmt.execute("insert into a values ( 1, 51 )");
0696: _stmt.execute("insert into a values ( 2, 52 )");
0697:
0698: _stmt.execute("insert into b values ( 1, 53 )");
0699: _stmt.execute("insert into b values ( 2, 54 )");
0700:
0701: _stmt.execute("insert into c values ( 1, 55 )");
0702:
0703: _stmt.execute("insert into d values ( 1, 56 )");
0704:
0705: //inner - (inner - inner) join expects one row
0706: _rset = _stmt.executeQuery("select * from a s1 inner join b s2"
0707: + " inner join c s3 inner join d s4 on (s3.id= s4.id)"
0708: + " on(s2.id = s3.id) on s1.id = s2.id");
0709: assertNotNull(_rset);
0710: assertTrue(_rset.next());
0711: assertEquals(_rset.getInt(1), 1);
0712: assertEquals(_rset.getInt(2), 51);
0713: assertEquals(_rset.getInt(3), 1);
0714: assertEquals(_rset.getInt(4), 53);
0715: assertEquals(_rset.getInt(5), 1);
0716: assertEquals(_rset.getInt(6), 55);
0717: assertEquals(_rset.getInt(7), 1);
0718: assertEquals(_rset.getInt(8), 56);
0719: assertTrue(!_rset.next());
0720: _rset.close();
0721:
0722: //inner -(left - inner) join expects two rows
0723: _rset = _stmt
0724: .executeQuery("select * from a s1 inner join b s2"
0725: + " left outer join c s3 inner join d s4"
0726: + " on (s3.id= s4.id) on(s2.id = s3.id) on s1.id = s2.id");
0727: assertNotNull(_rset);
0728: assertTrue(_rset.next());
0729: assertEquals(_rset.getInt(1), 1);
0730: assertEquals(_rset.getInt(2), 51);
0731: assertEquals(_rset.getInt(3), 1);
0732: assertEquals(_rset.getInt(4), 53);
0733: assertEquals(_rset.getInt(5), 1);
0734: assertEquals(_rset.getInt(6), 55);
0735: assertEquals(_rset.getInt(7), 1);
0736: assertEquals(_rset.getInt(8), 56);
0737:
0738: assertTrue(_rset.next());
0739: assertEquals(_rset.getInt(1), 2);
0740: assertEquals(_rset.getInt(2), 52);
0741: assertEquals(_rset.getInt(3), 2);
0742: assertEquals(_rset.getInt(4), 54);
0743: assertNull(_rset.getObject(5));
0744: assertNull(_rset.getObject(6));
0745: assertNull(_rset.getObject(7));
0746: assertNull(_rset.getObject(8));
0747: _rset.close();
0748:
0749: //inner -(right - inner) join expects one row
0750: _rset = _stmt
0751: .executeQuery("select * from a s1 inner join b s2"
0752: + " right outer join c s3 inner join d s4"
0753: + " on (s3.id= s4.id) on(s2.id = s3.id) on s1.id = s2.id");
0754: assertNotNull(_rset);
0755: assertTrue(_rset.next());
0756: assertEquals(_rset.getInt(1), 1);
0757: assertEquals(_rset.getInt(2), 51);
0758: assertEquals(_rset.getInt(3), 1);
0759: assertEquals(_rset.getInt(4), 53);
0760: assertEquals(_rset.getInt(5), 1);
0761: assertEquals(_rset.getInt(6), 55);
0762: assertEquals(_rset.getInt(7), 1);
0763: assertEquals(_rset.getInt(8), 56);
0764: assertTrue(!_rset.next());
0765: _rset.close();
0766:
0767: //left -(inner - inner) join expects two row
0768: _rset = _stmt
0769: .executeQuery("select * from a s1 left outer join b"
0770: + " s2 inner join c s3 inner join d s4 on (s3.id= s4.id)"
0771: + " on(s2.id = s3.id) on s1.id = s2.id");
0772: assertNotNull(_rset);
0773: assertTrue(_rset.next());
0774: assertEquals(_rset.getInt(1), 1);
0775: assertEquals(_rset.getInt(2), 51);
0776: assertEquals(_rset.getInt(3), 1);
0777: assertEquals(_rset.getInt(4), 53);
0778: assertEquals(_rset.getInt(5), 1);
0779: assertEquals(_rset.getInt(6), 55);
0780: assertEquals(_rset.getInt(7), 1);
0781: assertEquals(_rset.getInt(8), 56);
0782: assertTrue(_rset.next());
0783:
0784: assertEquals(_rset.getInt(1), 2);
0785: assertEquals(_rset.getInt(2), 52);
0786: assertNull(_rset.getObject(3));
0787: assertNull(_rset.getObject(4));
0788: assertNull(_rset.getObject(5));
0789: assertNull(_rset.getObject(6));
0790: assertNull(_rset.getObject(7));
0791: assertNull(_rset.getObject(8));
0792: assertTrue(!_rset.next());
0793: _rset.close();
0794:
0795: //left -(left - inner) join expects two row
0796: _rset = _stmt
0797: .executeQuery("select * from a s1 left outer join b s2"
0798: + " left outer join c s3 inner join d s4"
0799: + " on (s3.id= s4.id) on(s2.id = s3.id) on s1.id = s2.id");
0800: assertNotNull(_rset);
0801: assertTrue(_rset.next());
0802: assertEquals(_rset.getInt(1), 1);
0803: assertEquals(_rset.getInt(2), 51);
0804: assertEquals(_rset.getInt(3), 1);
0805: assertEquals(_rset.getInt(4), 53);
0806: assertEquals(_rset.getInt(5), 1);
0807: assertEquals(_rset.getInt(6), 55);
0808: assertEquals(_rset.getInt(7), 1);
0809: assertEquals(_rset.getInt(8), 56);
0810:
0811: assertTrue(_rset.next());
0812: assertEquals(_rset.getInt(1), 2);
0813: assertEquals(_rset.getInt(2), 52);
0814: assertEquals(_rset.getInt(3), 2);
0815: assertEquals(_rset.getInt(4), 54);
0816: assertNull(_rset.getObject(5));
0817: assertNull(_rset.getObject(6));
0818: assertNull(_rset.getObject(7));
0819: assertNull(_rset.getObject(8));
0820: _rset.close();
0821:
0822: //right -(left - inner) join expects two row
0823: _rset = _stmt
0824: .executeQuery("select * from a s1 right outer join b s2"
0825: + " left outer join c s3 inner join d s4"
0826: + " on (s3.id= s4.id) on(s2.id = s3.id) on s1.id = s2.id");
0827: assertNotNull(_rset);
0828: assertTrue(_rset.next());
0829: assertEquals(_rset.getInt(1), 1);
0830: assertEquals(_rset.getInt(2), 51);
0831: assertEquals(_rset.getInt(3), 1);
0832: assertEquals(_rset.getInt(4), 53);
0833: assertEquals(_rset.getInt(5), 1);
0834: assertEquals(_rset.getInt(6), 55);
0835: assertEquals(_rset.getInt(7), 1);
0836: assertEquals(_rset.getInt(8), 56);
0837:
0838: assertTrue(_rset.next());
0839: assertEquals(_rset.getInt(1), 2);
0840: assertEquals(_rset.getInt(2), 52);
0841: assertEquals(_rset.getInt(3), 2);
0842: assertEquals(_rset.getInt(4), 54);
0843: assertNull(_rset.getObject(5));
0844: assertNull(_rset.getObject(6));
0845: assertNull(_rset.getObject(7));
0846: assertNull(_rset.getObject(8));
0847: _rset.close();
0848:
0849: }
0850:
0851: public void testIsNullUsingIntBtreeIndex() throws Exception {
0852: _stmt
0853: .execute("create table null_test ( id int, name varchar(10) )");
0854: _stmt.execute("create btree index int_idx on null_test (id)");
0855: _stmt.execute("insert into null_test values ( 1, 'Amy' )");
0856: _stmt.execute("insert into null_test values ( NULL, 'Mike' )");
0857: _stmt.execute("insert into null_test values ( 3, 'Teresa' )");
0858: _stmt.execute("insert into null_test values ( NULL, 'James' )");
0859:
0860: //is not null test
0861: _rset = _stmt
0862: .executeQuery("select id, name from null_test where id is not null");
0863: assertNotNull(_rset);
0864: assertTrue(_rset.next());
0865: assertNotNull(_rset.getObject(1));
0866: assertTrue(_rset.next());
0867: assertNotNull(_rset.getObject(1));
0868: assertTrue(!_rset.next());
0869: _rset.close();
0870:
0871: //is null test
0872: _rset = _stmt
0873: .executeQuery("select id, name from null_test where id is null");
0874: assertNotNull(_rset);
0875: assertTrue(_rset.next());
0876: assertNull(_rset.getObject(1));
0877: assertTrue(_rset.next());
0878: assertNull(_rset.getObject(1));
0879: assertTrue(!_rset.next());
0880: _rset.close();
0881:
0882: _rset = _stmt
0883: .executeQuery("explain select id, name from null_test where id is null");
0884: assertNotNull(_rset);
0885: assertTrue(_rset.next());
0886: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
0887: "Unmod(LazyRow("));
0888: assertTrue(!_rset.next());
0889: _rset.close();
0890:
0891: _rset = _stmt
0892: .executeQuery("explain select id, name from null_test where id = 2 and id is null");
0893: assertNotNull(_rset);
0894: assertTrue(_rset.next());
0895: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
0896: "Unmod(LazyRow("));
0897: assertTrue(_rset.next());
0898: assertEquals(_rset.getString(1),
0899: "Filtering(ISNULL((NULL_TEST).ID))");
0900: assertTrue(!_rset.next());
0901: _rset.close();
0902:
0903: //test deleting one null row to test index deletion and test above again
0904: int updated = _stmt
0905: .executeUpdate("delete from null_test where id is null and name like 'Mike'");
0906: assertNotNull(_rset);
0907: assertEquals(updated, 1);
0908:
0909: //is not null test
0910: _rset = _stmt
0911: .executeQuery("select id, name from null_test where id is not null");
0912: assertNotNull(_rset);
0913: assertTrue(_rset.next());
0914: assertNotNull(_rset.getObject(1));
0915: assertTrue(_rset.next());
0916: assertNotNull(_rset.getObject(1));
0917: assertTrue(!_rset.next());
0918: _rset.close();
0919:
0920: //is null test
0921: _rset = _stmt
0922: .executeQuery("select id, name from null_test where id is null");
0923: assertNotNull(_rset);
0924: assertTrue(_rset.next());
0925: assertNull(_rset.getObject(1));
0926: //name should be 'James'
0927: assertEquals(_rset.getObject(2), "James");
0928: assertTrue(!_rset.next());
0929: _rset.close();
0930:
0931: }
0932:
0933: public void testIsNullUsingStringBtreeIndex() throws Exception {
0934: _stmt
0935: .execute("create table null_test ( id int, name varchar(10) )");
0936: _stmt.execute("insert into null_test values ( 1, 'Amy' )");
0937: _stmt.execute("insert into null_test values ( 2, NULL )");
0938: _stmt.execute("insert into null_test values ( 3, 'Teresa' )");
0939: _stmt.execute("insert into null_test values ( 4, NULL )");
0940:
0941: _rset = _stmt
0942: .executeQuery("explain select id, name from null_test where id > 2 and name is null and name <> 'bogus'");
0943: assertNotNull(_rset);
0944: assertTrue(_rset.next());
0945: assertEquals(_rset.getString(1),
0946: "Unmod(MemoryTable(NULL_TEST))");
0947: assertTrue(_rset.next());
0948: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
0949: "Filtering"));
0950: assertTrue(!_rset.next());
0951: _rset.close();
0952:
0953: _stmt.execute("create btree index str_idx on null_test (name)");
0954:
0955: //is not null test
0956: _rset = _stmt
0957: .executeQuery("select id, name from null_test where name is not null");
0958: assertNotNull(_rset);
0959: assertTrue(_rset.next());
0960: assertNotNull(_rset.getObject(2));
0961: assertTrue(_rset.next());
0962: assertNotNull(_rset.getObject(2));
0963: assertTrue(!_rset.next());
0964: _rset.close();
0965:
0966: //is null test
0967: _rset = _stmt
0968: .executeQuery("select id, name from null_test where name is null");
0969: assertNotNull(_rset);
0970: assertTrue(_rset.next());
0971: assertNull(_rset.getObject(2));
0972: assertTrue(_rset.next());
0973: assertNull(_rset.getObject(2));
0974: assertTrue(!_rset.next());
0975: _rset.close();
0976:
0977: _rset = _stmt
0978: .executeQuery("explain select id, name from null_test where name is null");
0979: assertNotNull(_rset);
0980: assertTrue(_rset.next());
0981: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
0982: "Unmod(LazyRow("));
0983: assertTrue(!_rset.next());
0984: _rset.close();
0985:
0986: //test deleting one null row to test index deletion and test above again
0987: int updated = _stmt
0988: .executeUpdate("delete from null_test where name is null and id = 2");
0989: assertNotNull(_rset);
0990: assertEquals(updated, 1);
0991:
0992: // testing again (is not null test)
0993: _rset = _stmt
0994: .executeQuery("select id, name from null_test where name is not null");
0995: assertNotNull(_rset);
0996: assertTrue(_rset.next());
0997: assertNotNull(_rset.getObject(2));
0998: assertTrue(_rset.next());
0999: assertNotNull(_rset.getObject(2));
1000: assertTrue(!_rset.next());
1001: _rset.close();
1002:
1003: //(testing again) is null test
1004: _rset = _stmt
1005: .executeQuery("select id, name from null_test where name is null");
1006: assertNotNull(_rset);
1007: assertTrue(_rset.next());
1008: //id should be 4
1009: assertEquals(_rset.getInt(1), 4);
1010: assertNull(_rset.getObject(2));
1011: assertTrue(!_rset.next());
1012: _rset.close();
1013:
1014: }
1015:
1016: public void testLeftOuterJoinHavingRightTableColumnIndexed()
1017: throws Exception {
1018: _stmt.execute("create table emp ( id int, name varchar(10) )");
1019: _stmt
1020: .execute("create table salary ( id int, base_salary int, bonus int )");
1021: _stmt.execute("create btree index salary_idx on salary ( id )");
1022: _stmt.execute("insert into emp values ( 1, 'Amy' )");
1023: _stmt.execute("insert into emp values ( 2, 'Mike' )");
1024: _stmt.execute("insert into emp values ( 3, 'Teresa' )");
1025: _stmt.execute("insert into salary values ( 1, 1000, 100 )");
1026: _stmt.execute("insert into salary values ( 2, 2000, 200 )");
1027:
1028: //inner join
1029: _rset = _stmt
1030: .executeQuery("select * from emp inner join salary on emp.id = salary.id");
1031: assertNotNull(_rset);
1032: assertTrue(_rset.next());
1033: assertTrue(_rset.next());
1034: assertTrue(!_rset.next());
1035: _rset.close();
1036:
1037: //left outer join
1038: _rset = _stmt
1039: .executeQuery("select * from emp left outer join salary on emp.id = salary.id");
1040: assertNotNull(_rset);
1041: assertTrue(_rset.next());
1042: assertTrue(_rset.next());
1043: assertTrue(_rset.next());
1044: assertTrue(!_rset.next());
1045: _rset.close();
1046:
1047: // make sure for left outer join right table column values are null for non
1048: // matching key from emp
1049: _rset = _stmt
1050: .executeQuery("select base_salary from emp left outer join salary on emp.id = salary.id where emp.id = 3");
1051: assertTrue(_rset.next());
1052: assertNull(_rset.getObject(1));
1053:
1054: }
1055:
1056: public void testLikeWithFilePath() throws Exception {
1057: _stmt.execute("create table table1 (field1 varchar(40))");
1058: _stmt
1059: .execute("insert into table1 values ('C:\\documents\\java\\docs\\index.html')");
1060: _rset = _stmt
1061: .executeQuery("SELECT * FROM table1 WHERE field1 LIKE 'C:\\documents\\java\\docs%' ");
1062: assertNotNull(_rset);
1063: assertTrue(_rset.next());
1064: assertTrue(!_rset.next());
1065:
1066: _rset = _stmt
1067: .executeQuery("SELECT * FROM table1 WHERE field1 LIKE 'C:\\documents\\java\\docs\\index.html' ");
1068: assertNotNull(_rset);
1069: assertTrue(_rset.next());
1070: assertTrue(!_rset.next());
1071: }
1072:
1073: public void testLikeWithFilePathAndEscapeChar() throws Exception {
1074: _stmt.execute("create table like_test (field varchar(40))");
1075: _stmt
1076: .execute("insert into like_test values ('C:\\documents\\java\\docs\\index.html')");
1077: _stmt
1078: .execute("insert into like_test values ('C:\\documents\\java\\docs\\')");
1079: _stmt
1080: .execute("insert into like_test values ('C:\\documents\\java\\docs.html')");
1081: //test $
1082: _stmt
1083: .execute("insert into like_test values ('C:\\documents\\java\\docs\\index.html$')");
1084: //test %
1085: _stmt
1086: .execute("insert into like_test values ('C:\\documents\\java\\docs\\%')");
1087:
1088: //insert all special chars
1089: //insert []
1090: _stmt
1091: .execute("insert into like_test values ('C:\\documents[aa]\\java\\docs\\')");
1092: //insert {}
1093: _stmt
1094: .execute("insert into like_test values ('C:\\documents{aa}\\java\\docs\\')");
1095: //insert |
1096: _stmt
1097: .execute("insert into like_test values ('C:\\|documents{aa}|\\java\\%docs\\')");
1098: //insert ^
1099: _stmt
1100: .execute("insert into like_test values ('C:\\^documents^\\java\\docs\\')");
1101: //insert +
1102: _stmt
1103: .execute("insert into like_test values ('C:\\+documents+\\java\\docs\\')");
1104: //test _
1105: _stmt
1106: .execute("insert into like_test values ('C:\\_documents_\\java\\docs\\')");
1107:
1108: //test (
1109: _stmt
1110: .execute("insert into like_test values ('C:\\java\\doc(2\\%')");
1111: //test ()
1112: _stmt
1113: .execute("insert into like_test values ('C:\\java\\doc(2)\\%')");
1114:
1115: //
1116: _stmt.execute("insert into like_test values ('aaabb')");
1117:
1118: _stmt.execute("insert into like_test values ('aaa%bb%cc')");
1119:
1120: _stmt.execute("insert into like_test values ('aaa%dd%cc')");
1121:
1122: _stmt.execute("insert into like_test values ('aaa%bb%dd')");
1123:
1124: _stmt.execute("insert into like_test values ('aaa$bb$cc')");
1125:
1126: _stmt.execute("insert into like_test values ('aaa$bb%cc')");
1127:
1128: _stmt.execute("insert into like_test values ('1122')");
1129:
1130: _rset = _stmt
1131: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents\\java\\docs%' ");
1132: assertNotNull(_rset);
1133: assertTrue(_rset.next());
1134: assertTrue(_rset.next());
1135: assertTrue(_rset.next());
1136: assertTrue(_rset.next());
1137: assertTrue(_rset.next());
1138: assertTrue(!_rset.next());
1139:
1140: try {
1141: _rset = _stmt
1142: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents\\java\\docs\\index.html' ESCAPE '' ");
1143: assertNotNull(_rset);
1144: assertTrue(_rset.next());
1145: fail("Expected SQLException(22019): invalid escape character");
1146: } catch (SQLException expected) {
1147: if (!"22019".equals(expected.getSQLState())) {
1148: fail("Expected SQLException(22019): invalid escape character");
1149: }
1150: }
1151:
1152: _rset = _stmt
1153: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents\\java\\docs\\%%' ESCAPE '%' ");
1154: assertNotNull(_rset);
1155: assertTrue(_rset.next());
1156: assertEquals("C:\\documents\\java\\docs\\%", _rset.getString(1));
1157: assertTrue(!_rset.next());
1158:
1159: _rset = _stmt
1160: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents\\java\\docs\\$%' ESCAPE '$' ");
1161: assertNotNull(_rset);
1162: assertTrue(_rset.next());
1163: assertEquals("C:\\documents\\java\\docs\\%", _rset.getString(1));
1164: assertTrue(!_rset.next());
1165:
1166: _rset = _stmt
1167: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents\\java\\docs\\|%' ESCAPE '|' ");
1168: assertNotNull(_rset);
1169: assertTrue(_rset.next());
1170: assertEquals("C:\\documents\\java\\docs\\%", _rset.getString(1));
1171: assertTrue(!_rset.next());
1172:
1173: try {
1174: _rset = _stmt
1175: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents\\java\\docs\\|%' ESCAPE 's' ");
1176: assertNotNull(_rset);
1177: assertTrue(_rset.next());
1178: fail("Expected invalid escaping");
1179: } catch (Exception e) {
1180: // expected
1181: }
1182:
1183: _rset = _stmt
1184: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents\\java\\docs\\0%' ESCAPE '0' ");
1185: assertNotNull(_rset);
1186: assertTrue(_rset.next());
1187: assertEquals("C:\\documents\\java\\docs\\%", _rset.getString(1));
1188: assertTrue(!_rset.next());
1189:
1190: _rset = _stmt
1191: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents\\java\\docs\\index.html$$' ESCAPE '$' ");
1192: assertNotNull(_rset);
1193: assertTrue(_rset.next());
1194: assertEquals("C:\\documents\\java\\docs\\index.html$", _rset
1195: .getString(1));
1196: assertTrue(!_rset.next());
1197:
1198: try {
1199: _rset = _stmt
1200: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents\\java\\docs\\index.html$' ESCAPE '$' ");
1201: assertNotNull(_rset);
1202: assertTrue(_rset.next());
1203: fail("Expected SQLException(22025) - invalid escape sequence");
1204: } catch (SQLException expected) {
1205: if (!"22025".equals(expected.getSQLState())) {
1206: fail("Expected SQLException(22025) - invalid escape sequence");
1207: }
1208: }
1209:
1210: try {
1211: _rset = _stmt
1212: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents\\java\\docs\\index.html$a' ESCAPE '$' ");
1213: assertNotNull(_rset);
1214: assertTrue(_rset.next());
1215: fail("Expected SQLException");
1216: } catch (SQLException expected) {
1217: if (!"22025".equals(expected.getSQLState())) {
1218: fail("Expected SQLException(22025) - invalid escape sequence");
1219: }
1220: }
1221:
1222: try {
1223: _rset = _stmt
1224: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents\\java\\docs\\index.html1$' ESCAPE '1' ");
1225: assertNotNull(_rset);
1226: assertTrue(_rset.next());
1227: fail("Expected SQLException");
1228: } catch (SQLException expected) {
1229: if (!"22025".equals(expected.getSQLState())) {
1230: fail("Expected SQLException(22025) - invalid escape sequence");
1231: }
1232: }
1233:
1234: //more than one escape char
1235: try {
1236: _rset = _stmt
1237: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents\\java\\docs\\index.html$' ESCAPE '11' ");
1238: fail("Expected SQLException");
1239: } catch (SQLException expected) {
1240: if (!"22019".equals(expected.getSQLState())) {
1241: fail("Expected SQLException(22019) - invalid escape character");
1242: }
1243: }
1244:
1245: //no row expected
1246: _rset = _stmt
1247: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents\\java\\docs\\index.html$_' ESCAPE '$' ");
1248: assertNotNull(_rset);
1249: assertTrue(!_rset.next());
1250:
1251: _rset = _stmt
1252: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents\\java\\docs\\index.html$' ");
1253: assertNotNull(_rset);
1254: assertTrue(_rset.next());
1255: assertEquals("C:\\documents\\java\\docs\\index.html$", _rset
1256: .getString(1));
1257: assertTrue(!_rset.next());
1258:
1259: //test []
1260: _rset = _stmt
1261: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents[aa]\\java\\docs\\' ");
1262: assertNotNull(_rset);
1263: assertTrue(_rset.next());
1264: assertEquals("C:\\documents[aa]\\java\\docs\\", _rset
1265: .getString(1));
1266: assertTrue(!_rset.next());
1267:
1268: //test {}
1269: _rset = _stmt
1270: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\documents{aa}\\java\\docs\\' ");
1271: assertNotNull(_rset);
1272: assertTrue(_rset.next());
1273: assertEquals("C:\\documents{aa}\\java\\docs\\", _rset
1274: .getString(1));
1275: assertTrue(!_rset.next());
1276:
1277: //test (
1278: _rset = _stmt
1279: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\java\\doc(2\\%' ");
1280: assertNotNull(_rset);
1281: assertTrue(_rset.next());
1282: assertEquals("C:\\java\\doc(2\\%", _rset.getString(1));
1283: assertTrue(!_rset.next());
1284:
1285: //test ( with escape
1286: _rset = _stmt
1287: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\java\\doc(2%$%' ESCAPE '$' ");
1288: assertNotNull(_rset);
1289: assertTrue(_rset.next());
1290: assertEquals("C:\\java\\doc(2\\%", _rset.getString(1));
1291: assertTrue(_rset.next());
1292: assertEquals("C:\\java\\doc(2)\\%", _rset.getString(1));
1293: assertTrue(!_rset.next());
1294:
1295: //test ()
1296: _rset = _stmt
1297: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\java\\doc(2)\\%' ");
1298: assertNotNull(_rset);
1299: assertTrue(_rset.next());
1300: assertEquals("C:\\java\\doc(2)\\%", _rset.getString(1));
1301: assertTrue(!_rset.next());
1302:
1303: //test |
1304: _rset = _stmt
1305: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\|documents{aa}|\\java\\%docs\\' ");
1306: assertNotNull(_rset);
1307: assertTrue(_rset.next());
1308: assertEquals("C:\\|documents{aa}|\\java\\%docs\\", _rset
1309: .getString(1));
1310: assertTrue(!_rset.next());
1311:
1312: //test escape with pipe % {}
1313: _rset = _stmt
1314: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\||documents{aa}||\\java\\|%docs\\' ESCAPE '|'");
1315: assertNotNull(_rset);
1316: assertTrue(_rset.next());
1317: assertEquals("C:\\|documents{aa}|\\java\\%docs\\", _rset
1318: .getString(1));
1319: assertTrue(!_rset.next());
1320:
1321: //test ^
1322: _rset = _stmt
1323: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\^documents^\\java\\docs\\' ");
1324: assertNotNull(_rset);
1325: assertTrue(_rset.next());
1326: assertEquals("C:\\^documents^\\java\\docs\\", _rset
1327: .getString(1));
1328: assertTrue(!_rset.next());
1329:
1330: //test +
1331: _rset = _stmt
1332: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\+documents+\\java\\docs\\' ");
1333: assertNotNull(_rset);
1334: assertTrue(_rset.next());
1335: assertEquals("C:\\+documents+\\java\\docs\\", _rset
1336: .getString(1));
1337: assertTrue(!_rset.next());
1338:
1339: //test _ it should match two rows
1340: _rset = _stmt
1341: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\_documents_\\java\\docs\\' ");
1342: assertNotNull(_rset);
1343: assertTrue(_rset.next());
1344: assertEquals("C:\\^documents^\\java\\docs\\", _rset
1345: .getString(1));
1346: assertTrue(_rset.next());
1347: assertEquals("C:\\+documents+\\java\\docs\\", _rset
1348: .getString(1));
1349: assertTrue(_rset.next());
1350: assertEquals("C:\\_documents_\\java\\docs\\", _rset
1351: .getString(1));
1352: assertTrue(!_rset.next());
1353:
1354: //test _ with escape
1355: _rset = _stmt
1356: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'C:\\|_documents|_\\java\\docs\\' ESCAPE '|'");
1357: assertNotNull(_rset);
1358: assertTrue(_rset.next());
1359: assertEquals("C:\\_documents_\\java\\docs\\", _rset
1360: .getString(1));
1361: assertTrue(!_rset.next());
1362:
1363: //test multiple character match using escape
1364: _rset = _stmt
1365: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'aaaaaabb' ESCAPE 'a'");
1366: assertNotNull(_rset);
1367: assertTrue(_rset.next());
1368: assertEquals("aaabb", _rset.getString(1));
1369: assertTrue(!_rset.next());
1370:
1371: //test missing right number of escape characters missing one 'a;
1372: try {
1373: _rset = _stmt
1374: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'aaaaabb' ESCAPE 'a'");
1375: assertNotNull(_rset);
1376: assertTrue(_rset.next());
1377: fail("Expected SQLException(22025) - invalid escape sequence");
1378: } catch (SQLException expected) {
1379: if (!"22025".equals(expected.getSQLState())) {
1380: fail("Expected SQLException(22025) - invalid escape sequence");
1381: }
1382: }
1383:
1384: //test extra right number of escape characters extra one 'a;
1385: try {
1386: _rset = _stmt
1387: .executeQuery("SELECT * FROM like_test WHERE field LIKE 'aaaaaaabb' ESCAPE 'a'");
1388: assertNotNull(_rset);
1389: assertTrue(_rset.next());
1390: fail("Expected SQLException(22025) - invalid escape sequence");
1391: } catch (SQLException expected) {
1392: if (!"22025".equals(expected.getSQLState())) {
1393: fail("Expected SQLException(22025) - invalid escape sequence");
1394: }
1395: }
1396:
1397: //test multiple %
1398: _rset = _stmt
1399: .executeQuery("select * from like_test where field like 'aaa%%bb%cc'");
1400: assertNotNull(_rset);
1401: assertTrue(_rset.next());
1402: assertEquals("aaa%bb%cc", _rset.getString(1));
1403: assertTrue(_rset.next());
1404: assertEquals("aaa$bb$cc", _rset.getString(1));
1405: assertTrue(_rset.next());
1406: assertEquals("aaa$bb%cc", _rset.getString(1));
1407: assertTrue(!_rset.next());
1408:
1409: //test multiple % with escape (each escape characters should be followed by
1410: //either escape character it self or % or _
1411: try {
1412: _rset = _stmt
1413: .executeQuery("select * from like_test where field like 'aaa%%bb%cc' escape '%'");
1414: assertNotNull(_rset);
1415: assertTrue(_rset.next());
1416: fail("Expected SQLException(22025) - invalid escape sequence");
1417: } catch (SQLException expected) {
1418: if (!"22025".equals(expected.getSQLState())) {
1419: fail("Expected SQLException(22025) - invalid escape sequence");
1420: }
1421: }
1422:
1423: try {
1424: _rset = _stmt
1425: .executeQuery("select * from like_test where field like 'aaa%bb%%cc' escape '%'");
1426: assertNotNull(_rset);
1427: assertTrue(_rset.next());
1428: fail("Expected SQLException(22025) - invalid escape sequence");
1429: } catch (SQLException expected) {
1430: if (!"22025".equals(expected.getSQLState())) {
1431: fail("Expected SQLException(22025) - invalid escape sequence");
1432: }
1433: }
1434:
1435: _rset = _stmt
1436: .executeQuery("select * from like_test where field like 'aaa$%bb%cc' escape '$'");
1437: assertNotNull(_rset);
1438: assertTrue(_rset.next());
1439: assertEquals("aaa%bb%cc", _rset.getString(1));
1440: assertTrue(!_rset.next());
1441:
1442: //should not return any row
1443: _rset = _stmt
1444: .executeQuery("select * from like_test where field like '1122' escape '1'");
1445: assertNotNull(_rset);
1446: assertTrue(!_rset.next());
1447:
1448: // 1122 with 1 as escape then correct pattern should be 111122 not 11122
1449: try {
1450: _rset = _stmt
1451: .executeQuery("select * from like_test where field like '11122' escape '1'");
1452: assertNotNull(_rset);
1453: assertTrue(_rset.next());
1454: fail("Expected SQLException(22025) - invalid escape sequence");
1455: } catch (SQLException expected) {
1456: if (!"22025".equals(expected.getSQLState())) {
1457: fail("Expected SQLException(22025) - invalid escape sequence");
1458: }
1459: }
1460:
1461: //1122 with 1 as escape then correct pattern should be 111122 not 1111122
1462: try {
1463: _rset = _stmt
1464: .executeQuery("select * from like_test where field like '1111122' escape '1'");
1465: assertNotNull(_rset);
1466: assertTrue(_rset.next());
1467: fail("Expected SQLException(22025) - invalid escape sequence");
1468: } catch (SQLException expected) {
1469: if (!"22025".equals(expected.getSQLState())) {
1470: fail("Expected SQLException(22025) - invalid escape sequence");
1471: }
1472: }
1473:
1474: _rset = _stmt
1475: .executeQuery("select * from like_test where field like '111122' escape '1'");
1476: assertNotNull(_rset);
1477: assertTrue(_rset.next());
1478: assertEquals("1122", _rset.getString(1));
1479: assertTrue(!_rset.next());
1480: }
1481:
1482: public void testQueryOptimizerProcessWhereTree() throws Exception {
1483: _stmt.execute("create table x ( id int, name varchar(10) )");
1484: _stmt.execute("create table y ( id int, name varchar(10) )");
1485: _stmt.execute("create table z ( id int, name varchar(10) )");
1486: _stmt.execute("insert into x values ( 1, 'Amy' )");
1487: _stmt.execute("insert into x values ( 2, 'Mike' )");
1488: _stmt.execute("insert into x values ( 3, 'Teresa' )");
1489: _stmt.execute("insert into y values ( 2, 'James' )");
1490: _stmt.execute("insert into z values ( 5, NULL )");
1491: _stmt.execute("insert into x values ( 5, NULL )");
1492:
1493: //inner join
1494: _rset = _stmt
1495: .executeQuery("select * from x inner join y on x.id = y.id where y.id > 1");
1496: assertNotNull(_rset);
1497: assertTrue(_rset.next());
1498: assertEquals(2, _rset.getInt(1));
1499: assertTrue(!_rset.next());
1500: _rset.close();
1501:
1502: _rset = _stmt
1503: .executeQuery("select * from x inner join y on x.id = y.id where 3 > y.id ");
1504: assertNotNull(_rset);
1505: assertTrue(_rset.next());
1506: assertEquals(2, _rset.getInt(1));
1507: assertTrue(!_rset.next());
1508: _rset.close();
1509:
1510: _rset = _stmt
1511: .executeQuery("select * from x inner join y on x.id = y.id where 3 > x.id ");
1512: assertNotNull(_rset);
1513: assertTrue(_rset.next());
1514: assertEquals(2, _rset.getInt(1));
1515: assertTrue(!_rset.next());
1516: _rset.close();
1517:
1518: _rset = _stmt
1519: .executeQuery("select * from x inner join y on x.id = y.id where y.id <= 2");
1520: assertNotNull(_rset);
1521: assertTrue(_rset.next());
1522: assertEquals(2, _rset.getInt(1));
1523: assertTrue(!_rset.next());
1524: _rset.close();
1525:
1526: _rset = _stmt
1527: .executeQuery("select * from x inner join y on x.id = y.id where 1 <= y.id");
1528: assertNotNull(_rset);
1529: assertTrue(_rset.next());
1530: assertEquals(2, _rset.getInt(1));
1531: assertTrue(!_rset.next());
1532: _rset.close();
1533:
1534: _rset = _stmt
1535: .executeQuery("select * from x inner join y on x.id = y.id where y.id != 1");
1536: assertNotNull(_rset);
1537: assertTrue(_rset.next());
1538: assertEquals(2, _rset.getInt(1));
1539: assertTrue(!_rset.next());
1540: _rset.close();
1541:
1542: _rset = _stmt
1543: .executeQuery("select * from x inner join y on x.id > y.id and x.id = y.id where y.id != 1");
1544: assertNotNull(_rset);
1545: assertTrue(!_rset.next());
1546: _rset.close();
1547:
1548: _rset = _stmt
1549: .executeQuery("select * from y inner join x on x.id > y.id and x.id = y.id where y.id != 1");
1550: assertNotNull(_rset);
1551: assertTrue(!_rset.next());
1552: _rset.close();
1553:
1554: _rset = _stmt
1555: .executeQuery("select * from z inner join y on z.id > y.id and z.id < y.id where y.id != 1");
1556: assertNotNull(_rset);
1557: assertTrue(!_rset.next());
1558: _rset.close();
1559:
1560: _rset = _stmt
1561: .executeQuery("select * from x inner join y on x.id = y.id where 1 != y.id ");
1562: assertNotNull(_rset);
1563: assertTrue(_rset.next());
1564: assertEquals(2, _rset.getInt(1));
1565: assertTrue(!_rset.next());
1566: _rset.close();
1567:
1568: _rset = _stmt
1569: .executeQuery("select * from x inner join y on x.id = y.id where y.id IS NOT NULL");
1570: assertNotNull(_rset);
1571: assertTrue(_rset.next());
1572: assertEquals(2, _rset.getInt(1));
1573: assertTrue(!_rset.next());
1574: _rset.close();
1575:
1576: _rset = _stmt
1577: .executeQuery("select * from x inner join y on x.id = y.id where x.id IS NOT NULL");
1578: assertNotNull(_rset);
1579: assertTrue(_rset.next());
1580: assertEquals(2, _rset.getInt(1));
1581: assertTrue(!_rset.next());
1582: _rset.close();
1583:
1584: _rset = _stmt
1585: .executeQuery("select * from x inner join z on x.id = z.id where z.name IS NULL");
1586: assertNotNull(_rset);
1587: assertTrue(_rset.next());
1588: assertEquals(5, _rset.getInt(1));
1589: assertTrue(!_rset.next());
1590: _rset.close();
1591:
1592: _rset = _stmt
1593: .executeQuery("select * from x inner join z on x.id = z.id where x.name IS NULL");
1594: assertNotNull(_rset);
1595: assertTrue(_rset.next());
1596: assertEquals(5, _rset.getInt(1));
1597: assertTrue(!_rset.next());
1598: _rset.close();
1599:
1600: }
1601:
1602: public void testQueryOptimizerProcessWhereTreeWithBTreeIndex()
1603: throws Exception {
1604: _stmt.execute("create table x ( id int, name varchar(10) )");
1605: _stmt.execute("create table y ( id int, name varchar(10) )");
1606: _stmt.execute("create table z ( id int, name varchar(10) )");
1607:
1608: _stmt.execute("create btree index xidx on x(id)");
1609: _stmt.execute("create btree index yidx on y(id)");
1610:
1611: _stmt.execute("insert into x values ( 1, 'Amy' )");
1612: _stmt.execute("insert into x values ( 2, 'Mike' )");
1613: _stmt.execute("insert into x values ( 3, 'Teresa' )");
1614: _stmt.execute("insert into y values ( 2, 'James' )");
1615: _stmt.execute("insert into z values ( 5, NULL )");
1616: _stmt.execute("insert into x values ( 5, NULL )");
1617:
1618: //inner join
1619: _rset = _stmt
1620: .executeQuery("select * from x inner join y on x.id = y.id where y.id > 1");
1621: assertNotNull(_rset);
1622: assertTrue(_rset.next());
1623: assertEquals(2, _rset.getInt(1));
1624: assertTrue(!_rset.next());
1625: _rset.close();
1626:
1627: _rset = _stmt
1628: .executeQuery("select * from x inner join y on x.id = y.id where 3 > y.id ");
1629: assertNotNull(_rset);
1630: assertTrue(_rset.next());
1631: assertEquals(2, _rset.getInt(1));
1632: assertTrue(!_rset.next());
1633: _rset.close();
1634:
1635: _rset = _stmt
1636: .executeQuery("select * from x inner join y on x.id = y.id where 3 > x.id ");
1637: assertNotNull(_rset);
1638: assertTrue(_rset.next());
1639: assertEquals(2, _rset.getInt(1));
1640: assertTrue(!_rset.next());
1641: _rset.close();
1642:
1643: _rset = _stmt
1644: .executeQuery("select * from x inner join y on x.id = y.id where y.id <= 2");
1645: assertNotNull(_rset);
1646: assertTrue(_rset.next());
1647: assertEquals(2, _rset.getInt(1));
1648: assertTrue(!_rset.next());
1649: _rset.close();
1650:
1651: _rset = _stmt
1652: .executeQuery("select * from x inner join y on x.id = y.id where 1 <= y.id");
1653: assertNotNull(_rset);
1654: assertTrue(_rset.next());
1655: assertEquals(2, _rset.getInt(1));
1656: assertTrue(!_rset.next());
1657: _rset.close();
1658:
1659: _rset = _stmt
1660: .executeQuery("select * from x inner join y on x.id > y.id and x.id = y.id where y.id != 1");
1661: assertNotNull(_rset);
1662: assertTrue(!_rset.next());
1663: _rset.close();
1664:
1665: _rset = _stmt
1666: .executeQuery("select * from y inner join x on x.id > y.id and x.id = y.id where y.id != 1");
1667: assertNotNull(_rset);
1668: assertTrue(!_rset.next());
1669: _rset.close();
1670:
1671: _rset = _stmt
1672: .executeQuery("select * from y inner join z on z.id > y.id and z.id < y.id where y.id != 1");
1673: assertNotNull(_rset);
1674: assertTrue(!_rset.next());
1675: _rset.close();
1676:
1677: _rset = _stmt
1678: .executeQuery("select * from x inner join y on x.id = y.id where 1 != y.id ");
1679: assertNotNull(_rset);
1680: assertTrue(_rset.next());
1681: assertEquals(2, _rset.getInt(1));
1682: assertTrue(!_rset.next());
1683: _rset.close();
1684:
1685: _rset = _stmt
1686: .executeQuery("select * from x inner join y on x.id = y.id where y.id IS NOT NULL");
1687: assertNotNull(_rset);
1688: assertTrue(_rset.next());
1689: assertEquals(2, _rset.getInt(1));
1690: assertTrue(!_rset.next());
1691: _rset.close();
1692:
1693: _rset = _stmt
1694: .executeQuery("select * from x inner join y on x.id = y.id where x.id IS NOT NULL");
1695: assertNotNull(_rset);
1696: assertTrue(_rset.next());
1697: assertEquals(2, _rset.getInt(1));
1698: assertTrue(!_rset.next());
1699: _rset.close();
1700:
1701: _stmt.execute("create btree index zidx on z(id)");
1702:
1703: _rset = _stmt
1704: .executeQuery("select * from x inner join z on x.id = z.id where z.name IS NULL");
1705: assertNotNull(_rset);
1706: assertTrue(_rset.next());
1707: assertEquals(5, _rset.getInt(1));
1708: assertTrue(!_rset.next());
1709: _rset.close();
1710:
1711: _rset = _stmt
1712: .executeQuery("select * from x inner join z on x.id = z.id where x.name IS NULL");
1713: assertNotNull(_rset);
1714: assertTrue(_rset.next());
1715: assertEquals(5, _rset.getInt(1));
1716: assertTrue(!_rset.next());
1717: _rset.close();
1718:
1719: _stmt.execute("insert into z values ( 2, 'Mike' )");
1720: _stmt.execute("create btree index xname on x(name)");
1721: _stmt.execute("create btree index zname on z(name)");
1722:
1723: _rset = _stmt
1724: .executeQuery("select * from x inner join z on x.id = z.id where x.name IS NOT NULL");
1725: assertNotNull(_rset);
1726: assertTrue(_rset.next());
1727: assertEquals(2, _rset.getInt(1));
1728: assertTrue(!_rset.next());
1729: _rset.close();
1730:
1731: }
1732:
1733: public void testResolveColumnWithTableAlias() throws SQLException {
1734: _stmt.execute("create table testsession (id number)");
1735: _stmt.execute("insert into testsession values (1)");
1736:
1737: // should throw column not found exception : fixed it already enbale this
1738: try {
1739: _stmt
1740: .executeQuery("SELECT i2.id, i3.answerscalevalue FROM testsession i2 left outer join testsession i3 on i2.id= i3.testSessionId");
1741: fail("Expected exception");
1742: } catch (SQLException ex) {
1743: // expected
1744: }
1745: _rset = _stmt
1746: .executeQuery("SELECT i2.id, i3.id FROM testsession i2 left outer join testsession i3 on i2.id= i3.id");
1747: assertTrue(_rset.next());
1748: assertEquals(1, _rset.getInt("id"));
1749: assertTrue(!_rset.next());
1750: _rset.close();
1751: }
1752:
1753: public void testRightOuterJoinIndexonLeft() throws Exception {
1754: _stmt.execute("create table emp ( id int, name varchar(10) )");
1755: _stmt
1756: .execute("create table salary ( id int, base_salary int, bonus int )");
1757: _stmt.execute("create index emp_idx on emp ( id )");
1758: _stmt
1759: .execute("create table emp_target ( id int, name varchar(10) )");
1760: _stmt.execute("insert into emp values ( 1, 'Amy' )");
1761: _stmt.execute("insert into emp values ( 2, 'Mike' )");
1762: _stmt.execute("insert into emp values ( 3, 'Teresa' )");
1763: _stmt.execute("insert into salary values ( 1, 1000, 100 )");
1764: _stmt.execute("insert into salary values ( 2, 2000, 200 )");
1765: _stmt.execute("insert into salary values ( 3, 3000, 300 )");
1766: _stmt.execute("insert into emp_target values ( 2, 'Teresa' )");
1767: _stmt.execute("insert into emp_target values ( 3, 'Teresa' )");
1768:
1769: // right outer - right outer join expects one row
1770: _rset = _stmt.executeQuery("select s1.id, s2.id from emp"
1771: + " s1 right outer join salary s2 on (s1.id = s2.id)");
1772: assertNotNull(_rset);
1773: assertTrue(_rset.next());
1774: assertEquals(1, _rset.getInt(1));
1775: assertEquals(1, _rset.getInt(2));
1776:
1777: assertTrue(_rset.next());
1778: assertEquals(2, _rset.getInt(1));
1779: assertEquals(2, _rset.getInt(2));
1780:
1781: assertTrue(_rset.next());
1782: assertEquals(3, _rset.getInt(1));
1783: assertEquals(3, _rset.getInt(2));
1784: assertTrue(!_rset.next());
1785:
1786: _rset.close();
1787:
1788: _rset = _stmt
1789: .executeQuery("select s1.id s1id, s2.id s2id from"
1790: + " (select emp.id, emp.name from emp inner join salary on emp.id = salary.id) s1"
1791: + " right outer join emp_target s2 on s1.id = s2.id");
1792:
1793: assertNotNull(_rset);
1794:
1795: assertTrue(_rset.next());
1796: assertEquals(2, _rset.getInt(1));
1797: assertEquals(2, _rset.getInt(2));
1798:
1799: assertTrue(_rset.next());
1800: assertEquals(3, _rset.getInt(1));
1801: assertEquals(3, _rset.getInt(2));
1802:
1803: assertTrue(!_rset.next());
1804: _rset.close();
1805:
1806: // right outer - right outer join expects one row
1807: _rset = _stmt
1808: .executeQuery("select s1.id, s2.id, s3.id from emp"
1809: + " s1 inner join salary s2 on(s1.id = s2.id) right outer join emp_target s3 "
1810: + " on (s1.id = s3.id)");
1811: assertNotNull(_rset);
1812: assertTrue(_rset.next());
1813: assertEquals(2, _rset.getInt(1));
1814: assertEquals(2, _rset.getInt(2));
1815: assertEquals(2, _rset.getInt(3));
1816:
1817: assertTrue(_rset.next());
1818: assertEquals(3, _rset.getInt(1));
1819: assertEquals(3, _rset.getInt(2));
1820: assertEquals(3, _rset.getInt(3));
1821:
1822: assertTrue(!_rset.next());
1823: _rset.close();
1824:
1825: // right outer - right outer join expects one row
1826: _rset = _stmt
1827: .executeQuery("select s1.id, s2.id, s3.id from emp s1 right outer join salary s2 inner join emp_target s3 "
1828: + " on(s2.id = s3.id) on (s1.id = s3.id)");
1829: assertNotNull(_rset);
1830: assertTrue(_rset.next());
1831: assertEquals(2, _rset.getInt(1));
1832: assertEquals(2, _rset.getInt(2));
1833: assertEquals(2, _rset.getInt(3));
1834:
1835: assertTrue(_rset.next());
1836: assertEquals(3, _rset.getInt(1));
1837: assertEquals(3, _rset.getInt(2));
1838: assertEquals(3, _rset.getInt(3));
1839:
1840: assertTrue(!_rset.next());
1841: _rset.close();
1842:
1843: _rset = _stmt
1844: .executeQuery("select count(*) from emp s1 right outer join salary s2 inner join emp_target s3 "
1845: + " on(s2.id = s3.id) on (s2.id = s3.id)");
1846: assertNotNull(_rset);
1847: assertTrue(_rset.next());
1848: assertEquals(6, _rset.getInt(1));
1849: assertTrue(!_rset.next());
1850: _rset.close();
1851:
1852: _rset = _stmt
1853: .executeQuery("explain select count(*) from emp s1 right outer join salary s2 inner join emp_target s3 "
1854: + " on(s2.id = s3.id) on (s2.id = s3.id)");
1855: assertTrue(_rset.next());
1856: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
1857: "Unmod"));
1858: assertTrue(_rset.next());
1859: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
1860: "ChangingIndexed"));
1861: assertTrue(_rset.next());
1862: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
1863: "Unmod"));
1864: assertTrue(_rset.next());
1865: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
1866: "IndexNestedLoop"));
1867: assertTrue(_rset.next());
1868: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
1869: "Nested"));
1870: assertTrue(_rset.next());
1871: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
1872: "Grouped"));
1873: assertTrue(!_rset.next());
1874: _rset.close();
1875:
1876: _rset = _stmt
1877: .executeQuery("select s1.id, s2.id, s3.id from emp s1 right outer join salary s2 inner join emp_target s3 "
1878: + " on(s2.id = s3.id) on (s2.id = s3.id)");
1879: assertNotNull(_rset);
1880:
1881: assertTrue(_rset.next());
1882: assertEquals(1, _rset.getInt(1));
1883: assertEquals(2, _rset.getInt(2));
1884: assertEquals(2, _rset.getInt(3));
1885:
1886: assertTrue(_rset.next());
1887: assertEquals(2, _rset.getInt(1));
1888: assertEquals(2, _rset.getInt(2));
1889: assertEquals(2, _rset.getInt(3));
1890:
1891: assertTrue(_rset.next());
1892: assertEquals(3, _rset.getInt(1));
1893: assertEquals(2, _rset.getInt(2));
1894: assertEquals(2, _rset.getInt(3));
1895:
1896: assertTrue(_rset.next());
1897: assertEquals(1, _rset.getInt(1));
1898: assertEquals(3, _rset.getInt(2));
1899: assertEquals(3, _rset.getInt(3));
1900:
1901: assertTrue(_rset.next());
1902: assertEquals(2, _rset.getInt(1));
1903: assertEquals(3, _rset.getInt(2));
1904: assertEquals(3, _rset.getInt(3));
1905:
1906: assertTrue(_rset.next());
1907: assertEquals(3, _rset.getInt(1));
1908: assertEquals(3, _rset.getInt(2));
1909: assertEquals(3, _rset.getInt(3));
1910:
1911: assertTrue(!_rset.next());
1912: _rset.close();
1913:
1914: }
1915:
1916: public void testRightOuterJoinIndexonLeft2() throws Exception {
1917: _stmt.execute("create table emp ( id int)");
1918: _stmt.execute("create table salary ( id int)");
1919: _stmt.execute("create index emp_idx on emp ( id )");
1920: _stmt.execute("create table emp_target ( id int)");
1921: _stmt.execute("insert into emp values ( 1)");
1922: _stmt.execute("insert into emp values ( 2)");
1923: _stmt.execute("insert into emp values ( 3)");
1924: _stmt.execute("insert into salary values ( 1)");
1925: _stmt.execute("insert into salary values ( 2)");
1926: _stmt.execute("insert into salary values ( 3)");
1927: _stmt.execute("insert into emp_target values ( 2)");
1928: _stmt.execute("insert into emp_target values ( 3)");
1929:
1930: // right outer - right outer join expects one row
1931: _rset = _stmt
1932: .executeQuery("select s1.id, s2.id, s3.id from emp"
1933: + " s1 right outer join salary s2 inner join emp_target s3 "
1934: + " on(s2.id = s3.id) on (s3.id = s1.id)");
1935: assertNotNull(_rset);
1936: assertTrue(_rset.next());
1937: assertEquals(2, _rset.getInt(1));
1938: assertEquals(2, _rset.getInt(2));
1939: assertEquals(2, _rset.getInt(3));
1940:
1941: assertTrue(_rset.next());
1942: assertEquals(3, _rset.getInt(1));
1943: assertEquals(3, _rset.getInt(2));
1944: assertEquals(3, _rset.getInt(3));
1945:
1946: assertTrue(!_rset.next());
1947: _rset.close();
1948:
1949: }
1950:
1951: public void testThreeTableJoinHavingTwoTableColumnIndexed()
1952: throws Exception {
1953: _stmt.execute("create table emp ( id int, name varchar(10) )");
1954: _stmt
1955: .execute("create table salary ( id int, base_salary int, bonus int )");
1956: _stmt.execute("create btree index salary_idx on salary ( id )");
1957: _stmt
1958: .execute("create table emp_target ( id int, name varchar(10) )");
1959: _stmt.execute("insert into emp values ( 1, 'Amy' )");
1960: _stmt.execute("insert into emp values ( 2, 'Mike' )");
1961: _stmt.execute("insert into emp values ( 3, 'Teresa' )");
1962: _stmt.execute("insert into salary values ( 1, 1000, 100 )");
1963: _stmt.execute("insert into salary values ( 2, 2000, 200 )");
1964: _stmt.execute("insert into emp_target values ( 3, 'Teresa' )");
1965:
1966: //inner join
1967: _rset = _stmt
1968: .executeQuery("select * from emp s1 inner join salary"
1969: + " s2 on (s1.id = s2.id) inner join emp_target s3"
1970: + " on (s2.id = s3.id)");
1971: assertNotNull(_rset);
1972: assertTrue(!_rset.next());
1973: _rset.close();
1974:
1975: //left outer join expect no rows as we compare s2.id with s3.id (s2.id will be
1976: // null)
1977: _rset = _stmt
1978: .executeQuery("select * from emp s1 left outer join salary"
1979: + " s2 on s1.id = s2.id inner join emp_target s3"
1980: + " on(s2.id = s3.id)");
1981: assertNotNull(_rset);
1982: assertTrue(!_rset.next());
1983: _rset.close();
1984:
1985: //left outer join expect one rows as we compare s1.id with s3.id
1986: _rset = _stmt
1987: .executeQuery("select * from emp s1 left outer join salary s2"
1988: + " on s1.id = s2.id inner join emp_target s3"
1989: + " on(s1.id = s3.id)");
1990: assertNotNull(_rset);
1991: assertTrue(_rset.next());
1992:
1993: }
1994:
1995: public void testThreeTableJoinWithComparisonConditionAppliedAtJoinAndAfterJoin()
1996: throws Exception {
1997: _stmt.execute("create table emp ( id int, name varchar(10))");
1998: _stmt
1999: .execute("create table salary ( sid int, base int, bonus int)");
2000: //empty table
2001: _stmt
2002: .execute("create table salary_src ( sid int, base int, bonus int)");
2003: _stmt
2004: .execute("create table manager ( id int, isManager varchar(30))");
2005: _stmt.execute("insert into emp values ( 1, 'Amy')");
2006: _stmt.execute("insert into emp values ( 2, 'Mike')");
2007: _stmt.execute("insert into emp values ( 3, 'Teresa')");
2008: _stmt.execute("insert into salary values ( 1, 1000, 100)");
2009: _stmt.execute("insert into salary values ( 4, 4000, 400 )");
2010: _stmt.execute("insert into manager values ( 2, 'Yes' )");
2011:
2012: //(1)(a)
2013: //test that comparision function is appled at join level
2014: //s1.id < s3.id and s1.id = s2.sid are applied together at join level
2015: //and we get a nested join
2016: _rset = _stmt
2017: .executeQuery("explain select * from emp s1 inner join salary s2 on (s1.id = s2.sid) inner join manager s3 on(s1.id < s3.id and s1.id = s2.sid)");
2018: assertNotNull(_rset);
2019: assertTrue(_rset.next());
2020: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2021: "Changing"));
2022: assertTrue(_rset.next());
2023: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2024: "Unmod"));
2025: assertTrue(_rset.next());
2026: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2027: "IndexNestedLoop"));
2028: assertTrue(_rset.next());
2029: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2030: "Unmod"));
2031: assertTrue(_rset.next());
2032: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2033: "Nested"));
2034: assertTrue(_rset.next());
2035: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2036: "Filtering"));
2037: assertTrue(!_rset.next());
2038: _rset.close();
2039:
2040: //(1)(b)
2041: //expects one row
2042: _rset = _stmt
2043: .executeQuery("select * from emp s1 inner join salary s2 on (s1.id = s2.sid) inner join manager s3 on(s1.id < s3.id and s1.id = s2.sid)");
2044: assertNotNull(_rset);
2045: assertTrue(_rset.next());
2046: assertEquals(1, _rset.getInt(1));
2047: assertEquals("Amy", _rset.getString(2));
2048: assertEquals(1, _rset.getInt(3));
2049: assertEquals(1000, _rset.getInt(4));
2050: assertEquals(100, _rset.getInt(5));
2051: assertEquals(2, _rset.getInt(6));
2052: assertEquals("Yes", _rset.getString(7));
2053:
2054: assertTrue(!_rset.next());
2055: _rset.close();
2056:
2057: //(2)(a)
2058: //test that comparision function is appled after join
2059: //s1.id < s3.id is applied after join
2060: //and we get a nested join
2061: _rset = _stmt
2062: .executeQuery("explain select * from emp s1 inner join salary s2 on (s1.id = s2.sid) inner join manager s3 on(s1.id < s3.id)");
2063: assertNotNull(_rset);
2064: assertTrue(_rset.next());
2065: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2066: "Changing"));
2067: assertTrue(_rset.next());
2068: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2069: "Unmod"));
2070: assertTrue(_rset.next());
2071: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2072: "IndexNestedLoop"));
2073: assertTrue(_rset.next());
2074: assertTrue(_rset.getString(1).startsWith("Unmod"));
2075: assertTrue(_rset.next());
2076: assertTrue(_rset.getString(1).startsWith("Nested"));
2077: assertTrue(!_rset.next());
2078: _rset.close();
2079:
2080: //(2)(b)
2081: //expects one row same as (1)(b) only difference is in the query planner strategy
2082: // see the diff between (1)(a) and (2)(a)
2083: _rset = _stmt
2084: .executeQuery("select * from emp s1 inner join salary s2 on (s1.id = s2.sid) inner join manager s3 on(s1.id < s3.id)");
2085: assertNotNull(_rset);
2086: assertTrue(_rset.next());
2087: assertEquals(1, _rset.getInt(1));
2088: assertEquals("Amy", _rset.getString(2));
2089: assertEquals(1, _rset.getInt(3));
2090: assertEquals(1000, _rset.getInt(4));
2091: assertEquals(100, _rset.getInt(5));
2092: assertEquals(2, _rset.getInt(6));
2093: assertEquals("Yes", _rset.getString(7));
2094:
2095: assertTrue(!_rset.next());
2096: _rset.close();
2097:
2098: //test two table left outer join
2099: _rset = _stmt
2100: .executeQuery("select * from emp s1 left outer join manager s3 on (s1.id < s3.id)");
2101: assertNotNull(_rset);
2102: assertTrue(_rset.next());
2103: assertEquals(1, _rset.getInt(1));
2104: assertEquals("Amy", _rset.getString(2));
2105: assertEquals(2, _rset.getInt(3));
2106: assertEquals("Yes", _rset.getString(4));
2107:
2108: assertTrue(_rset.next());
2109: assertEquals(2, _rset.getInt(1));
2110: assertEquals("Mike", _rset.getString(2));
2111: assertEquals(null, _rset.getObject(3));
2112: assertEquals(null, _rset.getString(4));
2113:
2114: assertTrue(_rset.next());
2115: assertEquals(3, _rset.getInt(1));
2116: assertEquals("Teresa", _rset.getString(2));
2117: assertEquals(null, _rset.getObject(3));
2118: assertEquals(null, _rset.getString(4));
2119:
2120: assertTrue(!_rset.next());
2121: _rset.close();
2122:
2123: //test two table left outer join with or condition
2124: _rset = _stmt
2125: .executeQuery("select * from emp s1 left outer join manager s3 on (s1.id < s3.id or s1.id = s3.id)");
2126: assertNotNull(_rset);
2127: assertTrue(_rset.next());
2128: assertEquals(1, _rset.getInt(1));
2129: assertEquals("Amy", _rset.getString(2));
2130: assertEquals(2, _rset.getInt(3));
2131: assertEquals("Yes", _rset.getString(4));
2132:
2133: assertTrue(_rset.next());
2134: assertEquals(2, _rset.getInt(1));
2135: assertEquals("Mike", _rset.getString(2));
2136: assertEquals(2, _rset.getInt(3));
2137: assertEquals("Yes", _rset.getString(4));
2138:
2139: assertTrue(_rset.next());
2140: assertEquals(3, _rset.getInt(1));
2141: assertEquals("Teresa", _rset.getString(2));
2142: assertEquals(null, _rset.getObject(3));
2143: assertEquals(null, _rset.getString(4));
2144:
2145: assertTrue(!_rset.next());
2146: _rset.close();
2147:
2148: //test two table left outer join with and condition
2149: _rset = _stmt
2150: .executeQuery("select * from emp s1 left outer join manager s3 on (s1.id < s3.id and s1.id = 1)");
2151: assertNotNull(_rset);
2152: assertTrue(_rset.next());
2153: assertEquals(1, _rset.getInt(1));
2154: assertEquals("Amy", _rset.getString(2));
2155: assertEquals(2, _rset.getInt(3));
2156: assertEquals("Yes", _rset.getString(4));
2157:
2158: assertTrue(_rset.next());
2159: assertEquals(2, _rset.getInt(1));
2160: assertEquals("Mike", _rset.getString(2));
2161: assertEquals(null, _rset.getObject(3));
2162: assertEquals(null, _rset.getString(4));
2163:
2164: assertTrue(_rset.next());
2165: assertEquals(3, _rset.getInt(1));
2166: assertEquals("Teresa", _rset.getString(2));
2167: assertEquals(null, _rset.getObject(3));
2168: assertEquals(null, _rset.getString(4));
2169:
2170: assertTrue(!_rset.next());
2171: _rset.close();
2172:
2173: //test three table join
2174: _rset = _stmt
2175: .executeQuery("select * from emp s1 left outer join manager s3 on (s1.id < s3.id) left outer join salary_src s2 on (s1.id = s2.sid)");
2176: assertNotNull(_rset);
2177: assertTrue(_rset.next());
2178: assertEquals(1, _rset.getInt(1));
2179: assertEquals("Amy", _rset.getString(2));
2180: assertEquals(2, _rset.getInt(3));
2181: assertEquals("Yes", _rset.getString(4));
2182: assertEquals(null, _rset.getObject(5));
2183: assertEquals(null, _rset.getString(6));
2184: assertEquals(null, _rset.getObject(7));
2185:
2186: assertTrue(_rset.next());
2187: assertEquals(2, _rset.getInt(1));
2188: assertEquals("Mike", _rset.getString(2));
2189: assertEquals(null, _rset.getObject(3));
2190: assertEquals(null, _rset.getString(4));
2191: assertEquals(null, _rset.getObject(5));
2192: assertEquals(null, _rset.getString(6));
2193: assertEquals(null, _rset.getObject(7));
2194:
2195: assertTrue(_rset.next());
2196: assertEquals(3, _rset.getInt(1));
2197: assertEquals("Teresa", _rset.getString(2));
2198: assertEquals(null, _rset.getObject(3));
2199: assertEquals(null, _rset.getString(4));
2200: assertEquals(null, _rset.getObject(5));
2201: assertEquals(null, _rset.getString(6));
2202: assertEquals(null, _rset.getObject(7));
2203:
2204: assertTrue(!_rset.next());
2205: _rset.close();
2206:
2207: //test three table join with and condition
2208: _rset = _stmt
2209: .executeQuery("select * from emp s1 left outer join manager s3 on (s1.id < s3.id and s1.id = 1) left outer join salary_src s2 on (s1.id = s2.sid)");
2210: assertNotNull(_rset);
2211: assertTrue(_rset.next());
2212: assertEquals(1, _rset.getInt(1));
2213: assertEquals("Amy", _rset.getString(2));
2214: assertEquals(2, _rset.getInt(3));
2215: assertEquals("Yes", _rset.getString(4));
2216: assertEquals(null, _rset.getObject(5));
2217: assertEquals(null, _rset.getString(6));
2218: assertEquals(null, _rset.getObject(7));
2219:
2220: assertTrue(_rset.next());
2221: assertEquals(2, _rset.getInt(1));
2222: assertEquals("Mike", _rset.getString(2));
2223: assertEquals(null, _rset.getObject(3));
2224: assertEquals(null, _rset.getString(4));
2225: assertEquals(null, _rset.getObject(5));
2226: assertEquals(null, _rset.getString(6));
2227: assertEquals(null, _rset.getObject(7));
2228:
2229: assertTrue(_rset.next());
2230: assertEquals(3, _rset.getInt(1));
2231: assertEquals("Teresa", _rset.getString(2));
2232: assertEquals(null, _rset.getObject(3));
2233: assertEquals(null, _rset.getString(4));
2234: assertEquals(null, _rset.getObject(5));
2235: assertEquals(null, _rset.getString(6));
2236: assertEquals(null, _rset.getObject(7));
2237:
2238: assertTrue(!_rset.next());
2239: _rset.close();
2240:
2241: }
2242:
2243: public void testThreeTableJoinWithExplainCommand() throws Exception {
2244: _stmt.execute("create table emp ( id int, name varchar(10) )");
2245: _stmt
2246: .execute("create table salary ( id int, base_salary int, bonus int )");
2247: _stmt
2248: .execute("create table emp_target ( id int, salary int, name varchar(10) )");
2249:
2250: _stmt.execute("insert into emp values ( 1, 'Amy' )");
2251: _stmt.execute("insert into emp values ( 2, 'Mike' )");
2252: _stmt.execute("insert into emp values ( 3, 'Teresa' )");
2253:
2254: _stmt.execute("insert into salary values ( 1, 1000, 100 )");
2255: _stmt.execute("insert into salary values ( 2, 2000, 200 )");
2256: _stmt.execute("insert into salary values ( 3, 3000, 300 )");
2257:
2258: _stmt
2259: .execute("insert into emp_target values ( 3, 3300, 'Teresa' )");
2260:
2261: _rset = _stmt
2262: .executeQuery("select s1.id, s1.name, s2.base_salary+s2.bonus from emp_target t1 right outer join emp s1 inner join salary s2 "
2263: + " on(s1.id = s2.id) on (s1.id = t1.id) where t1.id is null");
2264:
2265: assertNotNull(_rset);
2266: assertTrue(_rset.next());
2267: assertEquals(1, _rset.getInt(1));
2268: assertEquals(_rset.getString(2), "Amy");
2269: assertEquals(_rset.getInt(3), 1100);
2270:
2271: assertTrue(_rset.next());
2272: assertEquals(2, _rset.getInt(1));
2273: assertEquals(_rset.getString(2), "Mike");
2274: assertEquals(2200, _rset.getInt(3));
2275:
2276: assertTrue(!_rset.next());
2277: _rset.close();
2278:
2279: _rset = _stmt
2280: .executeQuery("explain select s1.id, s1.name, s2.base_salary+s2.bonus from emp_target t1 right outer join emp s1 inner join salary s2 "
2281: + " on(s1.id = s2.id) on (s1.id = t1.id) where t1.id is null");
2282:
2283: assertNotNull(_rset);
2284: assertTrue(_rset.next());
2285: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2286: "ChangingIndex"));
2287: assertTrue(_rset.next());
2288: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2289: "ChangingIndex"));
2290: assertTrue(_rset.next());
2291: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2292: "Unmod"));
2293: assertTrue(_rset.next());
2294: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2295: "IndexNestedLoop"));
2296: assertTrue(_rset.next());
2297: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2298: "IndexNestedLoop"));
2299: assertTrue(_rset.next());
2300: assertTrue(_rset.getString(1), _rset.getString(1).startsWith(
2301: "Filtering"));
2302:
2303: assertTrue(!_rset.next());
2304: _rset.close();
2305:
2306: }
2307:
2308: public void testThreeTableJoinWithRightTableViewHavingTwoTableColumnIndexed()
2309: throws Exception {
2310: _stmt.execute("create table emp ( id int, name varchar(10) )");
2311: _stmt
2312: .execute("create table salary ( id int, base_salary int, bonus int )");
2313: _stmt.execute("create btree index salary_idx on salary ( id )");
2314: _stmt
2315: .execute("create table emp_target ( id int, name varchar(10) )");
2316: _stmt.execute("insert into emp values ( 1, 'Amy' )");
2317: _stmt.execute("insert into emp values ( 2, 'Mike' )");
2318: _stmt.execute("insert into emp values ( 3, 'Teresa' )");
2319: _stmt.execute("insert into salary values ( 1, 1000, 100 )");
2320: _stmt.execute("insert into salary values ( 2, 2000, 200 )");
2321: _stmt.execute("insert into emp_target values ( 3, 'Teresa' )");
2322:
2323: //inner-inner join we expect no rows
2324: _rset = _stmt
2325: .executeQuery("select s1.id s1id, s2.id s2id, s3.id s3id from emp s1"
2326: + " inner join salary s2 inner join emp_target s3"
2327: + " on (s2.id = s3.id) on (s1.id = s2.id)");
2328: assertNotNull(_rset);
2329: assertTrue(!_rset.next());
2330: _rset.close();
2331:
2332: // left outer- inner join, expect three rows as we compare s1.id with s3.id (s3.id
2333: // will always be null) left outer condition s1.id = s3.id
2334: _rset = _stmt
2335: .executeQuery("select s1.id, s2.id, s3.id from emp s1"
2336: + " left outer join salary s2 inner join emp_target s3"
2337: + " on(s2.id = s3.id) on s1.id = s3.id");
2338: assertNotNull(_rset);
2339: assertTrue(_rset.next());
2340: assertEquals(_rset.getInt(1), 1);
2341: assertNull(_rset.getObject(2));
2342: assertNull(_rset.getObject(3));
2343:
2344: assertTrue(_rset.next());
2345: assertEquals(_rset.getInt(1), 2);
2346: assertNull(_rset.getObject(2));
2347: assertNull(_rset.getObject(3));
2348:
2349: assertTrue(_rset.next());
2350: assertEquals(_rset.getInt(1), 3);
2351: assertNull(_rset.getObject(2));
2352: assertNull(_rset.getObject(3));
2353:
2354: assertTrue(!_rset.next());
2355: _rset.close();
2356:
2357: // left outer- join inner, expect three rows as we compare s1.id with s2.id (s2.id
2358: // will always be null) left outer condition s1.id = s2.id
2359: _rset = _stmt
2360: .executeQuery("select s1.id, s2.id, s3.id from emp s1 left"
2361: + " outer join salary s2 inner join emp_target s3"
2362: + " on(s2.id = s3.id) on s1.id = s2.id");
2363: assertNotNull(_rset);
2364: assertTrue(_rset.next());
2365: assertEquals(_rset.getInt(1), 1);
2366: assertNull(_rset.getObject(2));
2367: assertNull(_rset.getObject(3));
2368:
2369: assertTrue(_rset.next());
2370: assertEquals(_rset.getInt(1), 2);
2371: assertNull(_rset.getObject(2));
2372: assertNull(_rset.getObject(3));
2373:
2374: assertTrue(_rset.next());
2375: assertEquals(_rset.getInt(1), 3);
2376: assertNull(_rset.getObject(2));
2377: assertNull(_rset.getObject(3));
2378:
2379: assertTrue(!_rset.next());
2380: _rset.close();
2381:
2382: // nested inner join between table s2 and s3 but join condition is s1.id = s3.id,
2383: // s1 is not in this join 's scope should throw exception.
2384: try {
2385: _rset = _stmt
2386: .executeQuery("select * from emp s1 left outer join salary s2 "
2387: + " inner join emp_target s3 on(s1.id = s3.id) on s1.id = s2.id");
2388: fail("Expected SQLException: out of scope column");
2389: } catch (SQLException e) {
2390: // expected
2391: }
2392:
2393: try {
2394: _rset = _stmt
2395: .executeQuery("select s1.id sid, s2.id, s3.id from emp s1 left outer join salary s2 "
2396: + " inner join emp_target s3 on(sid = s3.id) on s1.id = s2.id");
2397: fail("Expected SQLException: sid is not in scope");
2398: } catch (SQLException e) {
2399: // expected
2400: }
2401:
2402: try {
2403: _rset = _stmt
2404: .executeQuery("select s1.id sid, s2.id, s3.id from emp s1 left outer join salary s2 "
2405: + " inner join emp_target s3 on(s2.id = s3.id) on s1.id = s2.id where max(sid) = 3");
2406: fail("Expected SQLException : group function not allowed here");
2407: } catch (SQLException e) {
2408: // expected
2409: }
2410:
2411: try {
2412: _rset = _stmt
2413: .executeQuery("select s1.id, s2.id, s3.id + 2,"
2414: + " (s2.id + 2 + s1.id) as newid "
2415: + " from emp s1 left outer join salary s2 "
2416: + " inner join emp_target s3 on(newid = s3.id) on s1.id = s2.id");
2417: fail("Expected SQLException: newid is using out of scope column");
2418: } catch (SQLException e) {
2419: // expected
2420: }
2421:
2422: //left outer - right outer join expects three rows
2423: _rset = _stmt
2424: .executeQuery("select s1.id, s2.id, s3.id from emp s1 "
2425: + "left outer join salary s2 right join emp_target s3"
2426: + " on(s2.id = s3.id) on s1.id = s3.id");
2427: assertNotNull(_rset);
2428: assertTrue(_rset.next());
2429: assertEquals(_rset.getInt(1), 1);
2430: assertNull(_rset.getObject(2));
2431: assertNull(_rset.getObject(3));
2432:
2433: assertTrue(_rset.next());
2434: assertEquals(_rset.getInt(1), 2);
2435: assertNull(_rset.getObject(2));
2436: assertNull(_rset.getObject(3));
2437:
2438: assertTrue(_rset.next());
2439: assertEquals(_rset.getInt(1), 3);
2440: assertNull(_rset.getObject(2));
2441: assertEquals(_rset.getInt(1), 3);
2442:
2443: assertTrue(!_rset.next());
2444: _rset.close();
2445:
2446: // right outer - inner outer join expects no rows.
2447: // right outer condition s1.id = s3.id
2448: _rset = _stmt
2449: .executeQuery("select s1.id, s2.id, s3.id from emp"
2450: + " s1 right outer join salary s2 inner join emp_target s3"
2451: + " on(s2.id = s3.id) on s1.id = s3.id");
2452: assertNotNull(_rset);
2453: assertTrue(!_rset.next());
2454: _rset.close();
2455:
2456: // right outer - inner outer join expects no rows.
2457: // right outer condition s1.id = s2.id
2458: _rset = _stmt
2459: .executeQuery("select s1.id, s2.id, s3.id from emp s1"
2460: + " right outer join salary s2 inner join emp_target"
2461: + " s3 on(s2.id = s3.id) on s1.id = s2.id");
2462: assertNotNull(_rset);
2463: assertTrue(!_rset.next());
2464: _rset.close();
2465:
2466: // right outer - right outer join expects one row
2467: _rset = _stmt
2468: .executeQuery("select s1.id, s2.id, s3.id from emp"
2469: + " s1 right outer join salary s2 right join emp_target s3"
2470: + " on(s2.id = s3.id) on s1.id = s2.id");
2471: assertNotNull(_rset);
2472: assertTrue(_rset.next());
2473: assertNull(_rset.getObject(1));
2474: assertNull(_rset.getObject(2));
2475: assertEquals(_rset.getInt(3), 3);
2476:
2477: assertTrue(!_rset.next());
2478: _rset.close();
2479:
2480: //insert one more row in emp_target
2481: _stmt
2482: .execute("insert into emp_target values ( 1, 'Jennifer' )");
2483:
2484: //right outer - right outer join expects two row
2485: _rset = _stmt
2486: .executeQuery("select s1.id, s2.id, s3.id from emp s1 right"
2487: + " outer join salary s2 right join emp_target s3"
2488: + " on(s3.id = s2.id) on s1.id = s2.id");
2489: assertNotNull(_rset);
2490: assertTrue(_rset.next());
2491: assertNull(_rset.getObject(1));
2492: assertNull(_rset.getObject(2));
2493: assertEquals(_rset.getInt(3), 3);
2494:
2495: assertTrue(_rset.next());
2496: assertEquals(_rset.getInt(1), 1);
2497: assertEquals(_rset.getInt(2), 1);
2498: assertEquals(_rset.getInt(3), 1);
2499:
2500: assertTrue(!_rset.next());
2501: _rset.close();
2502:
2503: }
2504:
2505: public void testTwoTableAnsiJoinWithCompositeCondition()
2506: throws Exception {
2507: _stmt.execute("create table x ( id int, name varchar(10) )");
2508: _stmt.execute("create table y ( id int, name varchar(10) )");
2509: _stmt.execute("create table z ( id int, name varchar(10) )");
2510: _stmt.execute("insert into x values ( 1, 'Amy' )");
2511: _stmt.execute("insert into x values ( 2, 'Mike' )");
2512: _stmt.execute("insert into x values ( 3, 'Teresa' )");
2513: _stmt.execute("insert into y values ( 2, 'James' )");
2514: _stmt.execute("insert into z values ( 2, NULL )");
2515:
2516: //inner join
2517: _rset = _stmt
2518: .executeQuery("select * from x inner join y on x.id = y.id and y.id > 1");
2519: assertNotNull(_rset);
2520: assertTrue(_rset.next());
2521: assertTrue(!_rset.next());
2522: _rset.close();
2523:
2524: _rset = _stmt
2525: .executeQuery("select * from x inner join y on x.id = y.id and y.id <= 2");
2526: assertNotNull(_rset);
2527: assertTrue(_rset.next());
2528: assertTrue(!_rset.next());
2529: _rset.close();
2530:
2531: _rset = _stmt
2532: .executeQuery("select * from x inner join y on x.id = y.id and y.id != 1");
2533: assertNotNull(_rset);
2534: assertTrue(_rset.next());
2535: assertTrue(!_rset.next());
2536: _rset.close();
2537:
2538: _rset = _stmt
2539: .executeQuery("select * from x inner join y on x.id = y.id and y.id IS NOT NULL");
2540: assertNotNull(_rset);
2541: assertTrue(_rset.next());
2542: assertTrue(!_rset.next());
2543: _rset.close();
2544:
2545: _rset = _stmt
2546: .executeQuery("select * from x inner join z on x.id = z.id and z.name IS NULL");
2547: assertNotNull(_rset);
2548: assertTrue(_rset.next());
2549: assertTrue(!_rset.next());
2550: _rset.close();
2551:
2552: _rset = _stmt
2553: .executeQuery("select * from x inner join z on x.id = z.id and z.name IS NULL");
2554: assertNotNull(_rset);
2555: assertTrue(_rset.next());
2556: assertTrue(!_rset.next());
2557: _rset.close();
2558:
2559: //left outer join
2560: _rset = _stmt
2561: .executeQuery("select * from x left outer join y on x.id = y.id and y.id > 1");
2562: assertNotNull(_rset);
2563: assertTrue(_rset.next());
2564: assertTrue(_rset.next());
2565: assertTrue(_rset.next());
2566: assertTrue(!_rset.next());
2567: _rset.close();
2568:
2569: //left outer join make sure for left outer join right table
2570: //column values are null for non matching key from emp
2571: _rset = _stmt
2572: .executeQuery("select y.id from x left outer join y on x.id = y.id and y.id > 1");
2573: assertNotNull(_rset);
2574: assertTrue(_rset.next());
2575: assertNull(_rset.getObject(1));
2576: assertTrue(_rset.next());
2577: assertEquals(_rset.getInt(1), 2);
2578: assertTrue(_rset.next());
2579: assertNull(_rset.getObject(1));
2580: assertTrue(!_rset.next());
2581: _rset.close();
2582:
2583: }
2584:
2585: public void testTwoTableAnsiJoinWithCompositeOnAndWhereConditions()
2586: throws Exception {
2587: _stmt.execute("create table x ( id int, name varchar(10) )");
2588: _stmt.execute("create table y ( id int, name varchar(10) )");
2589: _stmt.execute("insert into x values ( 1, 'Amy' )");
2590: _stmt.execute("insert into x values ( 2, 'Mike' )");
2591: _stmt.execute("insert into x values ( 3, 'Teresa' )");
2592: _stmt.execute("insert into y values ( 2, 'James' )");
2593:
2594: //inner join
2595: _rset = _stmt
2596: .executeQuery("select * from x inner join y on x.id = y.id and y.id > 1");
2597: assertNotNull(_rset);
2598: assertTrue(_rset.next());
2599: assertTrue(!_rset.next());
2600: _rset.close();
2601:
2602: //left outer join
2603: _rset = _stmt
2604: .executeQuery("select * from x left outer join y on x.id = y.id and y.id > 1");
2605: assertNotNull(_rset);
2606: assertTrue(_rset.next());
2607: assertTrue(_rset.next());
2608: assertTrue(_rset.next());
2609: assertTrue(!_rset.next());
2610: _rset.close();
2611:
2612: //left outer join make sure for left outer join right table
2613: //column values are null for non matching key from emp
2614: _rset = _stmt
2615: .executeQuery("select y.id from x left outer join y on x.id = y.id and y.id > 1");
2616: assertNotNull(_rset);
2617: assertTrue(_rset.next());
2618: assertNull(_rset.getObject(1));
2619: assertTrue(_rset.next());
2620: assertEquals(_rset.getInt(1), 2);
2621: assertTrue(_rset.next());
2622: assertNull(_rset.getObject(1));
2623: assertTrue(!_rset.next());
2624: _rset.close();
2625:
2626: }
2627:
2628: public void testTwoTableInnerJoinWithComparisionCondition()
2629: throws Exception {
2630: _stmt.execute("create table emp ( id int, name varchar(10))");
2631: _stmt
2632: .execute("create table salary ( sid int, base int, bonus int)");
2633: _stmt.execute("insert into emp values ( 1, 'Amy')");
2634: _stmt.execute("insert into emp values ( 2, 'Mike')");
2635: _stmt.execute("insert into emp values ( 3, 'Teresa')");
2636: _stmt.execute("insert into salary values ( 1, 1000, 100)");
2637: _stmt.execute("insert into salary values ( 4, 4000, 400 )");
2638:
2639: _rset = _stmt
2640: .executeQuery("select * from emp s1 inner join salary s2 on s1.id < s2.sid");
2641: assertNotNull(_rset);
2642: assertTrue(_rset.next());
2643: assertEquals(1, _rset.getInt(1));
2644: assertEquals("Amy", _rset.getString(2));
2645: assertEquals(4, _rset.getInt(3));
2646: assertEquals(4000, _rset.getInt(4));
2647: assertEquals(400, _rset.getInt(5));
2648:
2649: assertTrue(_rset.next());
2650: assertEquals(2, _rset.getInt(1));
2651: assertEquals("Mike", _rset.getString(2));
2652: assertEquals(4, _rset.getInt(3));
2653: assertEquals(4000, _rset.getInt(4));
2654: assertEquals(400, _rset.getInt(5));
2655:
2656: assertTrue(_rset.next());
2657: assertEquals(3, _rset.getInt(1));
2658: assertEquals("Teresa", _rset.getString(2));
2659: assertEquals(4, _rset.getInt(3));
2660: assertEquals(4000, _rset.getInt(4));
2661: assertEquals(400, _rset.getInt(5));
2662:
2663: assertTrue(!_rset.next());
2664: _rset.close();
2665: }
2666:
2667: public void testTwoTableInnerJoinWithCompositeOnConditions()
2668: throws Exception {
2669: _stmt
2670: .execute("create table x ( id int, name varchar(10), dept int )");
2671: _stmt
2672: .execute("create table y ( id int, name varchar(10), dept int)");
2673: _stmt.execute("insert into x values ( 1, 'Amy', 101)");
2674: _stmt.execute("insert into x values ( 2, 'Mike', 102 )");
2675:
2676: _stmt.execute("insert into y values ( 1, 'Amy', 101)");
2677: _stmt.execute("insert into y values ( 2, 'James', 102 )");
2678:
2679: _rset = _stmt
2680: .executeQuery("select x.id, x.name, y.name from x inner join y on y.id = x.id and x.name = y. name and y.dept = x.dept");
2681: assertNotNull(_rset);
2682: assertTrue(_rset.next());
2683: assertTrue(!_rset.next());
2684: _rset.close();
2685:
2686: }
2687:
2688: public void testTwoTableJoinWithComparisionWhereCondition()
2689: throws Exception {
2690: _stmt.execute("create table emp ( id int, name varchar(10))");
2691: _stmt
2692: .execute("create table salary ( sid int, base int, bonus int)");
2693: _stmt.execute("insert into emp values ( 1, 'Amy')");
2694: _stmt.execute("insert into emp values ( 2, 'Mike')");
2695: _stmt.execute("insert into emp values ( 3, 'Teresa')");
2696: _stmt.execute("insert into salary values ( 1, 100, 100)");
2697: _stmt.execute("insert into salary values ( 4, 4000, 400 )");
2698:
2699: _rset = _stmt
2700: .executeQuery("select * from emp s1, salary s2 where s1.id < s2.sid");
2701: assertNotNull(_rset);
2702: assertTrue(_rset.next());
2703: assertEquals(1, _rset.getInt(1));
2704: assertEquals("Amy", _rset.getString(2));
2705: assertEquals(4, _rset.getInt(3));
2706: assertEquals(4000, _rset.getInt(4));
2707: assertEquals(400, _rset.getInt(5));
2708:
2709: assertTrue(_rset.next());
2710: assertEquals(2, _rset.getInt(1));
2711: assertEquals("Mike", _rset.getString(2));
2712: assertEquals(4, _rset.getInt(3));
2713: assertEquals(4000, _rset.getInt(4));
2714: assertEquals(400, _rset.getInt(5));
2715:
2716: assertTrue(_rset.next());
2717: assertEquals(3, _rset.getInt(1));
2718: assertEquals("Teresa", _rset.getString(2));
2719: assertEquals(4, _rset.getInt(3));
2720: assertEquals(4000, _rset.getInt(4));
2721: assertEquals(400, _rset.getInt(5));
2722:
2723: assertTrue(!_rset.next());
2724: _rset.close();
2725:
2726: }
2727:
2728: public void testTwoTableLeftOuterJoinWithCompositeOnAndWhereConditions()
2729: throws Exception {
2730: _stmt
2731: .execute("create table x ( id int, name varchar(10), dept int )");
2732: _stmt
2733: .execute("create table y ( id int, name varchar(10), dept int)");
2734: _stmt.execute("insert into x values ( 1, 'Amy', 101)");
2735: _stmt.execute("insert into x values ( 2, 'Mike', 102 )");
2736: _stmt.execute("insert into x values ( 3, 'Teresa', 103 )");
2737: _stmt.execute("insert into y values ( 2, 'James', 102 )");
2738: _stmt.execute("insert into y values ( 3, 'Mathew', 101 )");
2739:
2740: //left outer join
2741: _rset = _stmt
2742: .executeQuery("select x.id, x.name, y.name from x left outer join y on x.id = y.id where x.id > 1 and x.dept = y.dept");
2743: assertNotNull(_rset);
2744: assertTrue(_rset.next());
2745: assertEquals(_rset.getInt(1), 2);
2746: assertEquals(_rset.getString(2), "Mike");
2747: assertEquals(_rset.getString(3), "James");
2748: assertTrue(!_rset.next());
2749: _rset.close();
2750: }
2751:
2752: public void testTwoTableRightOuterJoinWithCompositeOnAndWhereConditions()
2753: throws Exception {
2754: _stmt
2755: .execute("create table x ( id int, name varchar(10), dept int )");
2756: _stmt
2757: .execute("create table y ( id int, name varchar(10), dept int)");
2758: _stmt.execute("insert into x values ( 1, 'Amy', 101)");
2759: _stmt.execute("insert into x values ( 2, 'Mike', 102 )");
2760: _stmt.execute("insert into x values ( 3, 'Teresa', 103 )");
2761: _stmt.execute("insert into y values ( 2, 'James', 102 )");
2762: _stmt.execute("insert into y values ( 3, 'Mathew', 101 )");
2763:
2764: //left outer join
2765: _rset = _stmt
2766: .executeQuery("select x.id, x.name, y.name, 2, 'test' from x right outer join y on y.id = x.id where y.id > 2 and y.dept = x.dept");
2767: assertNotNull(_rset);
2768: assertTrue(!_rset.next());
2769: _rset.close();
2770:
2771: }
2772:
2773: public void testTwoTableRightOuterJoinWithNonEqualJoin()
2774: throws Exception {
2775: _stmt.execute("create table emp ( id int, name varchar(10))");
2776: _stmt
2777: .execute("create table salary ( sid int, base int, bonus int)");
2778: _stmt.execute("insert into emp values ( 3, 'Teresa')");
2779: _stmt.execute("insert into emp values ( 4, 'xxx')");
2780: _stmt.execute("insert into emp values ( 1, 'Amy')");
2781: _stmt.execute("insert into emp values ( 2, 'Mike')");
2782:
2783: _stmt.execute("insert into salary values ( 1, 1000, 100)");
2784: _stmt.execute("insert into salary values ( 4, 4000, 400 )");
2785:
2786: _rset = _stmt
2787: .executeQuery("select id, name, base+bonus from emp s1 right outer join salary s2 on s1.id < s2.sid");
2788: assertNotNull(_rset);
2789:
2790: assertTrue(_rset.next());
2791: assertNull(_rset.getObject(1));
2792: assertNull(_rset.getObject(2));
2793: assertEquals(1100, _rset.getInt(3));
2794:
2795: assertTrue(_rset.next());
2796: assertEquals(3, _rset.getInt(1));
2797: assertEquals("Teresa", _rset.getString(2));
2798: assertEquals(4400, _rset.getInt(3));
2799:
2800: assertTrue(_rset.next());
2801: assertEquals(1, _rset.getInt(1));
2802: assertEquals("Amy", _rset.getString(2));
2803: assertEquals(4400, _rset.getInt(3));
2804:
2805: assertTrue(_rset.next());
2806: assertEquals(2, _rset.getInt(1));
2807: assertEquals("Mike", _rset.getString(2));
2808: assertEquals(4400, _rset.getInt(3));
2809:
2810: assertTrue(!_rset.next());
2811: _rset.close();
2812: }
2813:
2814: public void testMultiStatementInsertWithWhenClauseEvaluatingToNull()
2815: throws Exception {
2816: _stmt
2817: .execute("create table emp (id int, name varchar(25), manager_id int)");
2818: _stmt
2819: .execute("create table subordinates (id int, manager_id int)");
2820: _stmt
2821: .execute("create table leaders (id int, name varchar(25))");
2822:
2823: _stmt
2824: .execute("insert into emp values (1, 'Tom President', null)");
2825: _stmt
2826: .execute("insert into emp values (2, 'Suzy Secretary', 1)");
2827: _stmt.execute("insert into emp values (3, 'Dick Manager', 1)");
2828: _stmt.execute("insert into emp values (4, 'Harry Peon', 3)");
2829:
2830: _stmt
2831: .execute("insert first when matches(col4, '[A-Za-z ]*') then "
2832: + "into subordinates (id, manager_id) values (col1, col3) "
2833: + "else into leaders (id, name) values (col1, col2) "
2834: + "(select s1.id as col1, s1.name as col2, s1.manager_id as col3, s2.name as col4 "
2835: + "from emp s1 left outer join emp s2 on (s1.manager_id = s2.id))");
2836:
2837: _rset = _stmt.executeQuery("select id from leaders");
2838: assertTrue(_rset.next());
2839: assertEquals(1, _rset.getInt(1));
2840:
2841: _rset = _stmt
2842: .executeQuery("select id, manager_id from subordinates order by id");
2843:
2844: assertTrue(_rset.next());
2845: assertEquals(2, _rset.getInt(1));
2846: assertEquals(1, _rset.getInt(2));
2847:
2848: assertTrue(_rset.next());
2849: assertEquals(3, _rset.getInt(1));
2850: assertEquals(1, _rset.getInt(2));
2851:
2852: assertTrue(_rset.next());
2853: assertEquals(4, _rset.getInt(1));
2854: assertEquals(3, _rset.getInt(2));
2855:
2856: assertFalse(_rset.next());
2857: }
2858: }
|