0001: /*
0002: * $Id: TestGroupByAndOrderBy.java,v 1.5 2005/05/02 22:32:02 ahimanikya Exp $
0003: * =======================================================================
0004: * Copyright (c) 2002-2004 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.ResultSet;
0044: import java.sql.SQLException;
0045:
0046: import junit.framework.Test;
0047: import junit.framework.TestSuite;
0048:
0049: /**
0050: * Test group by and order by.
0051: *
0052: * @version $Revision: 1.5 $ $Date: 2005/05/02 22:32:02 $
0053: * @author Ahimanikya Satapathy
0054: */
0055: public class TestGroupByAndOrderBy extends AbstractFunctionalTest {
0056:
0057: //------------------------------------------------------------ Conventional
0058:
0059: public TestGroupByAndOrderBy(String testName) {
0060: super (testName);
0061: }
0062:
0063: public static Test suite() {
0064: return new TestSuite(TestGroupByAndOrderBy.class);
0065: }
0066:
0067: //--------------------------------------------------------------- Lifecycle
0068:
0069: public void setUp() throws Exception {
0070: super .setUp();
0071: }
0072:
0073: public void tearDown() throws Exception {
0074: super .tearDown();
0075: }
0076:
0077: //------------------------------------------------------------------- Tests
0078: public void testOrderByInsideTransaction() throws Exception {
0079: _conn.setAutoCommit(false);
0080: _stmt.execute("create table foo ( id int, val clob)");
0081: _stmt.execute("create unique index foo_pk on foo (id)");
0082: _stmt.executeUpdate("insert into foo values ( 2, 'two' )");
0083: _stmt.executeUpdate("insert into foo values ( 3, 'three' )");
0084: _stmt.executeUpdate("insert into foo values ( 1, 'one' )");
0085:
0086: {
0087: _rset = _stmt
0088: .executeQuery("select id, val, 'literal' from foo order by id");
0089: assertTrue(_rset.next());
0090: assertEquals(1, _rset.getInt(1));
0091: assertEquals("one", _rset.getString(2));
0092: assertEquals("literal", _rset.getString(3));
0093: assertTrue(_rset.next());
0094: assertEquals(2, _rset.getInt(1));
0095: assertEquals("two", _rset.getString(2));
0096: assertEquals("literal", _rset.getString(3));
0097: assertTrue(_rset.next());
0098: assertEquals(3, _rset.getInt(1));
0099: assertEquals("three", _rset.getString(2));
0100: assertEquals("literal", _rset.getString(3));
0101: assertFalse(_rset.next());
0102: _rset.close();
0103: }
0104:
0105: {
0106: _rset = _stmt
0107: .executeQuery("explain select id, val from foo order by id");
0108: assertTrue(_rset.next());
0109: assertTrue(_rset.getString(1), _rset.getString(1)
0110: .startsWith("Collating"));
0111: assertFalse(_rset.next());
0112: _rset.close();
0113: }
0114: {
0115: _rset = _stmt
0116: .executeQuery("select id, val from foo order by id asc");
0117: assertTrue(_rset.next());
0118: assertEquals(1, _rset.getInt(1));
0119: assertEquals("one", _rset.getString(2));
0120: assertTrue(_rset.next());
0121: assertEquals(2, _rset.getInt(1));
0122: assertEquals("two", _rset.getString(2));
0123: assertTrue(_rset.next());
0124: assertEquals(3, _rset.getInt(1));
0125: assertEquals("three", _rset.getString(2));
0126: assertFalse(_rset.next());
0127: _rset.close();
0128: }
0129: {
0130: _rset = _stmt
0131: .executeQuery("select id, val from foo order by id desc");
0132: assertTrue(_rset.next());
0133: assertEquals(3, _rset.getInt(1));
0134: assertEquals("three", _rset.getString(2));
0135: assertTrue(_rset.next());
0136: assertEquals(2, _rset.getInt(1));
0137: assertEquals("two", _rset.getString(2));
0138: assertTrue(_rset.next());
0139: assertEquals(1, _rset.getInt(1));
0140: assertEquals("one", _rset.getString(2));
0141: assertFalse(_rset.next());
0142: _rset.close();
0143: }
0144:
0145: {
0146: _rset = _stmt
0147: .executeQuery("explain select id, val from foo order by id desc");
0148: assertTrue(_rset.next());
0149: assertTrue(_rset.getString(1), _rset.getString(1)
0150: .startsWith("Collating"));
0151: assertTrue(_rset.next());
0152: assertTrue(_rset.getString(1), _rset.getString(1)
0153: .startsWith("ReverseSorted"));
0154: assertFalse(_rset.next());
0155: _rset.close();
0156: }
0157: _conn.setAutoCommit(true);
0158: }
0159:
0160: public void testOrderBy() throws Exception {
0161: _stmt.execute("create table foo ( id int, val varchar(10))");
0162: _stmt.execute("create unique index foo_pk on foo (id)");
0163: _stmt.executeUpdate("insert into foo values ( 2, 'two' )");
0164: _stmt.executeUpdate("insert into foo values ( 3, 'three' )");
0165: _stmt.executeUpdate("insert into foo values ( 1, 'one' )");
0166:
0167: {
0168: _rset = _stmt
0169: .executeQuery("select id, val from foo order by id");
0170: assertTrue(_rset.next());
0171: assertEquals(1, _rset.getInt(1));
0172: assertEquals("one", _rset.getString(2));
0173: assertTrue(_rset.next());
0174: assertEquals(2, _rset.getInt(1));
0175: assertEquals("two", _rset.getString(2));
0176: assertTrue(_rset.next());
0177: assertEquals(3, _rset.getInt(1));
0178: assertEquals("three", _rset.getString(2));
0179: assertFalse(_rset.next());
0180: _rset.close();
0181: }
0182: {
0183: _rset = _stmt
0184: .executeQuery("select id, val from foo order by id asc");
0185: assertTrue(_rset.next());
0186: assertEquals(1, _rset.getInt(1));
0187: assertEquals("one", _rset.getString(2));
0188: assertTrue(_rset.next());
0189: assertEquals(2, _rset.getInt(1));
0190: assertEquals("two", _rset.getString(2));
0191: assertTrue(_rset.next());
0192: assertEquals(3, _rset.getInt(1));
0193: assertEquals("three", _rset.getString(2));
0194: assertFalse(_rset.next());
0195: _rset.close();
0196: }
0197: {
0198: _rset = _stmt
0199: .executeQuery("select id, val from foo order by id desc");
0200: assertTrue(_rset.next());
0201: assertEquals(3, _rset.getInt(1));
0202: assertEquals("three", _rset.getString(2));
0203: assertTrue(_rset.next());
0204: assertEquals(2, _rset.getInt(1));
0205: assertEquals("two", _rset.getString(2));
0206: assertTrue(_rset.next());
0207: assertEquals(1, _rset.getInt(1));
0208: assertEquals("one", _rset.getString(2));
0209: assertFalse(_rset.next());
0210: _rset.close();
0211: }
0212:
0213: {
0214: _stmt.executeUpdate("insert into foo values ( 5, 'ONE' )");
0215:
0216: _rset = _stmt
0217: .executeQuery("select id, val from foo order by upper(val)");
0218: assertTrue(_rset.next());
0219: assertEquals(1, _rset.getInt(1));
0220: assertEquals("one", _rset.getString(2));
0221: assertTrue(_rset.next());
0222: assertEquals(5, _rset.getInt(1));
0223: assertEquals("ONE", _rset.getString(2));
0224: assertTrue(_rset.next());
0225: assertEquals(3, _rset.getInt(1));
0226: assertEquals("three", _rset.getString(2));
0227: assertTrue(_rset.next());
0228: assertEquals(2, _rset.getInt(1));
0229: assertEquals("two", _rset.getString(2));
0230: assertFalse(_rset.next());
0231: _rset.close();
0232: }
0233: }
0234:
0235: public void testOrderByWithNull() throws Exception {
0236: _stmt.execute("create table xyzzy ( id integer )");
0237: _stmt.executeUpdate("insert into xyzzy values ( null )");
0238: for (int i = 9; i >= 0; i--) {
0239: _stmt
0240: .executeUpdate("insert into xyzzy values ( " + i
0241: + ")");
0242: }
0243: _stmt.executeUpdate("insert into xyzzy values ( null )");
0244:
0245: {
0246: ResultSet rset = _stmt
0247: .executeQuery("select * from xyzzy order by id");
0248: // expect 0 thru 9
0249: for (int i = 0; i < 10; i++) {
0250: assertTrue(rset.next());
0251: assertEquals(i, rset.getInt(1));
0252: assertTrue(!rset.wasNull());
0253: }
0254: // expect 2 nulls
0255: for (int i = 0; i < 2; i++) {
0256: assertTrue(rset.next());
0257: assertEquals(0, rset.getInt(1));
0258: assertTrue(rset.wasNull());
0259: }
0260: // expect no more
0261: assertTrue(!rset.next());
0262: rset.close();
0263: }
0264:
0265: {
0266: ResultSet rset = _stmt
0267: .executeQuery("select * from xyzzy order by id asc");
0268: // expect 0 thru 9
0269: for (int i = 0; i < 10; i++) {
0270: assertTrue(rset.next());
0271: assertEquals(i, rset.getInt(1));
0272: assertTrue(!rset.wasNull());
0273: }
0274: // expect 2 nulls
0275: for (int i = 0; i < 2; i++) {
0276: assertTrue(rset.next());
0277: assertEquals(0, rset.getInt(1));
0278: assertTrue(rset.wasNull());
0279: }
0280: // expect no more
0281: assertTrue(!rset.next());
0282: rset.close();
0283: }
0284:
0285: {
0286: ResultSet rset = _stmt
0287: .executeQuery("select * from xyzzy order by id desc");
0288: // expect 2 nulls
0289: for (int i = 0; i < 2; i++) {
0290: assertTrue(rset.next());
0291: assertEquals(0, rset.getInt(1));
0292: assertTrue(rset.wasNull());
0293: }
0294: // expect 9 thru 0 nulls
0295: for (int i = 9; i >= 0; i--) {
0296: assertTrue(rset.next());
0297: assertEquals(i, rset.getInt(1));
0298: assertTrue(!rset.wasNull());
0299: }
0300: // expect no more
0301: assertTrue(!rset.next());
0302: rset.close();
0303: }
0304:
0305: }
0306:
0307: public void testGroupWithNoTable() throws Exception {
0308: _rset = _stmt.executeQuery("select 2 group by 2");
0309: assertNotNull(_rset);
0310: assertTrue(_rset.next());
0311: assertEquals(_rset.getInt(1), 2);
0312: _rset.close();
0313: }
0314:
0315: public void testAggregateWithArrayInsideTransaction()
0316: throws Exception {
0317: _conn.setAutoCommit(false);
0318: _stmt
0319: .execute("create table x ( xid varchar(5), id int, name varchar(10) )");
0320: _stmt.execute("create array index xid_index on x(xid)");
0321:
0322: _rset = _stmt
0323: .executeQuery("select xid, max(id) from x group by xid");
0324: assertNotNull(_rset);
0325: assertTrue(!_rset.next());
0326:
0327: _stmt.execute("insert into x values ( '1Amy', 1, 'Amy' )");
0328: _stmt.execute("insert into x values ( '2Mike', 2, 'Mike' )");
0329: _stmt.execute("insert into x values ( '1Amy', 1, 'Amy' )");
0330: _stmt.execute("insert into x values ( '2Mike', 2, 'Mike' )");
0331:
0332: _rset = _stmt
0333: .executeQuery("select xid, max(id) from x group by xid having xid is null ");
0334: assertNotNull(_rset);
0335: assertTrue(!_rset.next());
0336:
0337: _rset = _stmt
0338: .executeQuery("select xid, max(id) from x group by xid having xid = '2Mike' ");
0339: assertNotNull(_rset);
0340: assertTrue(_rset.next());
0341: assertEquals("2Mike", _rset.getString(1));
0342: assertEquals(2, _rset.getInt(2));
0343:
0344: _rset = _stmt
0345: .executeQuery("select xid, max(id), name from x where name = 'Mike' group by xid, name");
0346: assertNotNull(_rset);
0347: assertTrue(_rset.next());
0348: assertEquals("2Mike", _rset.getString(1));
0349: assertEquals(2, _rset.getInt(2));
0350: assertEquals("Mike", _rset.getString(3));
0351:
0352: assertTrue(!_rset.next());
0353: }
0354:
0355: public void testAggregateWithArrayIndexUsingHaving()
0356: throws Exception {
0357: _stmt
0358: .execute("create table x ( xid varchar(10), id int, name varchar(10) )");
0359: _stmt.execute("create array index xid_index on x(xid)");
0360:
0361: _rset = _stmt
0362: .executeQuery("select xid, max(id) from x group by xid");
0363: assertNotNull(_rset);
0364: assertTrue(!_rset.next());
0365:
0366: _stmt.execute("insert into x values ( '1Amy', 1, 'Amy' )");
0367: _stmt.execute("insert into x values ( '2Mike', 2, 'Mike' )");
0368: _stmt.execute("insert into x values ( '1Amy', 1, 'Amy' )");
0369: _stmt.execute("insert into x values ( '2Mike', 2, 'Mike' )");
0370:
0371: _rset = _stmt
0372: .executeQuery("select xid, max(id) from x group by xid having xid is null ");
0373: assertNotNull(_rset);
0374: assertTrue(!_rset.next());
0375:
0376: _rset = _stmt
0377: .executeQuery("select xid, max(id) from x group by xid having xid = '2Mike' ");
0378: assertNotNull(_rset);
0379: assertTrue(_rset.next());
0380: assertEquals("2Mike", _rset.getString(1));
0381: assertEquals(2, _rset.getInt(2));
0382:
0383: _rset = _stmt
0384: .executeQuery("select xid, max(id), name from x where name = 'Mike' group by xid, name");
0385: assertNotNull(_rset);
0386: assertTrue(_rset.next());
0387: assertEquals("2Mike", _rset.getString(1));
0388: assertEquals(2, _rset.getInt(2));
0389: assertEquals("Mike", _rset.getString(3));
0390:
0391: assertTrue(!_rset.next());
0392:
0393: _rset = _stmt
0394: .executeQuery("select max(id), name from x where xid = '1Amy' group by id, name");
0395: assertNotNull(_rset);
0396: assertTrue(_rset.next());
0397: assertEquals(1, _rset.getInt(1));
0398: assertEquals("Amy", _rset.getString(2));
0399: assertTrue(!_rset.next());
0400:
0401: _rset = _stmt
0402: .executeQuery("select max(id)+sum(id) total from x group by id order by total");
0403: assertNotNull(_rset);
0404: assertTrue(_rset.next());
0405: assertEquals(3, _rset.getInt(1));
0406:
0407: assertTrue(_rset.next());
0408: assertEquals(6, _rset.getInt(1));
0409:
0410: assertTrue(!_rset.next());
0411:
0412: _rset = _stmt
0413: .executeQuery("select max(id)+sum(id) total, name myname from x group by name having total > 3 order by total");
0414: assertNotNull(_rset);
0415: assertTrue(_rset.next());
0416: assertEquals(6, _rset.getInt(1));
0417:
0418: assertTrue(!_rset.next());
0419:
0420: _rset = _stmt
0421: .executeQuery("select xid, max(id), name from x group by xid, name having max(id) = 2 and name = 'Mike'");
0422: assertNotNull(_rset);
0423: assertTrue(_rset.next());
0424: assertEquals("2Mike", _rset.getString(1));
0425: assertEquals(2, _rset.getInt(2));
0426: assertEquals("Mike", _rset.getString(3));
0427:
0428: assertTrue(!_rset.next());
0429:
0430: }
0431:
0432: public void testAggregateWithBTreeIndexUsingHaving()
0433: throws Exception {
0434: _stmt
0435: .execute("create table x ( xid varchar(10), id int, name varchar(10) )");
0436: _stmt.execute("create btree index xid_index on x(xid)");
0437:
0438: _rset = _stmt
0439: .executeQuery("select xid, max(id) from x group by xid");
0440: assertNotNull(_rset);
0441: assertTrue(!_rset.next());
0442:
0443: _stmt.execute("insert into x values ( '1Amy', 1, 'Amy' )");
0444: _stmt.execute("insert into x values ( '2Mike', 2, 'Mike' )");
0445: _stmt.execute("insert into x values ( '1Amy', 1, 'Amy' )");
0446: _stmt.execute("insert into x values ( '2Mike', 2, 'Mike' )");
0447:
0448: _rset = _stmt
0449: .executeQuery("select xid, max(id) from x group by xid having xid is null ");
0450: assertNotNull(_rset);
0451: assertTrue(!_rset.next());
0452:
0453: _rset = _stmt
0454: .executeQuery("select xid, max(id) from x group by xid having xid = '2Mike' ");
0455: assertNotNull(_rset);
0456: assertTrue(_rset.next());
0457: assertEquals("2Mike", _rset.getString(1));
0458: assertEquals(2, _rset.getInt(2));
0459:
0460: _rset = _stmt
0461: .executeQuery("select xid, max(id), name from x where name = 'Mike' group by xid, name");
0462: assertNotNull(_rset);
0463: assertTrue(_rset.next());
0464: assertEquals("2Mike", _rset.getString(1));
0465: assertEquals(2, _rset.getInt(2));
0466: assertEquals("Mike", _rset.getString(3));
0467:
0468: assertTrue(!_rset.next());
0469:
0470: _rset = _stmt
0471: .executeQuery("select max(id), name from x where xid = '1Amy' group by id, name");
0472: assertNotNull(_rset);
0473: assertTrue(_rset.next());
0474: assertEquals(1, _rset.getInt(1));
0475: assertEquals("Amy", _rset.getString(2));
0476: assertTrue(!_rset.next());
0477:
0478: _rset = _stmt
0479: .executeQuery("select max(id)+sum(id) total, name myname from x group by name order by total");
0480: assertNotNull(_rset);
0481: assertTrue(_rset.next());
0482: assertEquals(3, _rset.getInt(1));
0483:
0484: assertTrue(_rset.next());
0485: assertEquals(6, _rset.getInt(1));
0486:
0487: assertTrue(!_rset.next());
0488:
0489: _rset = _stmt
0490: .executeQuery("select max(id)+sum(id) total, name myname from x group by name having total > 3");
0491: assertNotNull(_rset);
0492: assertTrue(_rset.next());
0493: assertEquals(6, _rset.getInt(1));
0494:
0495: assertTrue(!_rset.next());
0496:
0497: _rset = _stmt
0498: .executeQuery("select xid, max(id), name from x group by xid, name having name = 'Mike'");
0499: assertNotNull(_rset);
0500: assertTrue(_rset.next());
0501: assertEquals("2Mike", _rset.getString(1));
0502: assertEquals(2, _rset.getInt(2));
0503: assertEquals("Mike", _rset.getString(3));
0504:
0505: assertTrue(!_rset.next());
0506:
0507: }
0508:
0509: public void testAggregateWithNoIndexUsingHaving() throws Exception {
0510: _stmt
0511: .execute("create table x ( xid varchar(10), id int, name varchar(10) )");
0512:
0513: _rset = _stmt
0514: .executeQuery("select xid, max(id) from x group by xid");
0515: assertNotNull(_rset);
0516: assertTrue(!_rset.next());
0517:
0518: _stmt.execute("insert into x values ( '1Amy', 1, 'Amy' )");
0519: _stmt.execute("insert into x values ( '2Mike', 2, 'Mike' )");
0520: _stmt.execute("insert into x values ( '1Amy', 1, 'Amy' )");
0521: _stmt.execute("insert into x values ( '2Mike', 2, 'Mike' )");
0522:
0523: _rset = _stmt
0524: .executeQuery("select xid, max(id) from x group by xid having xid is null ");
0525: assertNotNull(_rset);
0526: assertTrue(!_rset.next());
0527:
0528: _rset = _stmt
0529: .executeQuery("select xid, max(id) from x group by xid having xid = '2Mike' ");
0530: assertNotNull(_rset);
0531: assertTrue(_rset.next());
0532: assertEquals("2Mike", _rset.getString(1));
0533: assertEquals(2, _rset.getInt(2));
0534:
0535: _rset = _stmt
0536: .executeQuery("select xid, max(id), name from x where name = 'Mike' group by xid, name");
0537: assertNotNull(_rset);
0538: assertTrue(_rset.next());
0539: assertEquals("2Mike", _rset.getString(1));
0540: assertEquals(2, _rset.getInt(2));
0541: assertEquals("Mike", _rset.getString(3));
0542:
0543: assertTrue(!_rset.next());
0544:
0545: _rset = _stmt
0546: .executeQuery("select max(id), name from x where xid = '1Amy' group by id, name");
0547: assertNotNull(_rset);
0548: assertTrue(_rset.next());
0549: assertEquals(1, _rset.getInt(1));
0550: assertEquals("Amy", _rset.getString(2));
0551: assertTrue(!_rset.next());
0552:
0553: _rset = _stmt
0554: .executeQuery("select max(id)+sum(id) total, name myname from x group by name order by total");
0555: assertNotNull(_rset);
0556: assertTrue(_rset.next());
0557: assertEquals(3, _rset.getInt(1));
0558:
0559: assertTrue(_rset.next());
0560: assertEquals(6, _rset.getInt(1));
0561:
0562: assertTrue(!_rset.next());
0563:
0564: _rset = _stmt
0565: .executeQuery("select max(id)+sum(id) total, name myname from x group by name having total > 3 order by total");
0566: assertNotNull(_rset);
0567: assertTrue(_rset.next());
0568: assertEquals(6, _rset.getInt(1));
0569:
0570: assertTrue(!_rset.next());
0571:
0572: _rset = _stmt
0573: .executeQuery("select xid, max(id), name from x group by xid, name having name = 'Mike'");
0574: assertNotNull(_rset);
0575: assertTrue(_rset.next());
0576: assertEquals("2Mike", _rset.getString(1));
0577: assertEquals(2, _rset.getInt(2));
0578: assertEquals("Mike", _rset.getString(3));
0579:
0580: assertTrue(!_rset.next());
0581:
0582: }
0583:
0584: public void testAggregateWithArrayIndexCol() throws Exception {
0585: _stmt
0586: .execute("create table x ( xid varchar(10), id int, name varchar(10) )");
0587: _stmt.execute("insert into x values ( '1Amy', 1, 'Amy' )");
0588: _stmt.execute("insert into x values ( '2Mike', 2, 'Mike' )");
0589: _stmt.execute("create array index xid_index on x(xid)");
0590:
0591: _rset = _stmt
0592: .executeQuery("select xid, max(id), name from x group by xid, name");
0593: assertNotNull(_rset);
0594: assertTrue(_rset.next());
0595: assertEquals("1Amy", _rset.getString(1));
0596: assertEquals(1, _rset.getInt(2));
0597: assertEquals("Amy", _rset.getString(3));
0598:
0599: assertTrue(_rset.next());
0600: assertEquals("2Mike", _rset.getString(1));
0601: assertEquals(2, _rset.getInt(2));
0602: assertEquals("Mike", _rset.getString(3));
0603:
0604: assertTrue(!_rset.next());
0605:
0606: _rset = _stmt
0607: .executeQuery("select max(id), name from x group by id, name");
0608: assertNotNull(_rset);
0609: assertTrue(_rset.next());
0610: assertEquals(1, _rset.getInt(1));
0611: assertEquals("Amy", _rset.getString(2));
0612:
0613: assertTrue(_rset.next());
0614: assertEquals(2, _rset.getInt(1));
0615: assertEquals("Mike", _rset.getString(2));
0616:
0617: assertTrue(!_rset.next());
0618:
0619: _rset = _stmt.executeQuery("select max(id) from x group by id");
0620: assertNotNull(_rset);
0621: assertTrue(_rset.next());
0622: assertEquals(1, _rset.getInt(1));
0623:
0624: assertTrue(_rset.next());
0625: assertEquals(2, _rset.getInt(1));
0626:
0627: assertTrue(!_rset.next());
0628:
0629: _rset = _stmt
0630: .executeQuery("select xid, max(id) from x group by xid");
0631: assertNotNull(_rset);
0632: assertTrue(_rset.next());
0633: assertEquals("1Amy", _rset.getString(1));
0634: assertEquals(1, _rset.getInt(2));
0635:
0636: assertTrue(_rset.next());
0637: assertEquals("2Mike", _rset.getString(1));
0638: assertEquals(2, _rset.getInt(2));
0639:
0640: assertTrue(!_rset.next());
0641: }
0642:
0643: public void testAggregateWithBtreeIndexCol() throws Exception {
0644: _stmt
0645: .execute("create table x ( xid varchar(10), id int, name varchar(10) )");
0646: _stmt.execute("insert into x values ( '1Amy', 1, 'Amy' )");
0647: _stmt.execute("insert into x values ( '2Mike', 2, 'Mike' )");
0648: _stmt.execute("create btree index xid_index on x(xid)");
0649:
0650: _rset = _stmt
0651: .executeQuery("select xid, max(id), name from x group by xid, name");
0652: assertNotNull(_rset);
0653: assertTrue(_rset.next());
0654: assertEquals("1Amy", _rset.getString(1));
0655: assertEquals(1, _rset.getInt(2));
0656: assertEquals("Amy", _rset.getString(3));
0657:
0658: assertTrue(_rset.next());
0659: assertEquals("2Mike", _rset.getString(1));
0660: assertEquals(2, _rset.getInt(2));
0661: assertEquals("Mike", _rset.getString(3));
0662:
0663: assertTrue(!_rset.next());
0664:
0665: _rset = _stmt
0666: .executeQuery("select max(id), name from x group by id, name");
0667: assertNotNull(_rset);
0668: assertTrue(_rset.next());
0669: assertEquals(1, _rset.getInt(1));
0670: assertEquals("Amy", _rset.getString(2));
0671:
0672: assertTrue(_rset.next());
0673: assertEquals(2, _rset.getInt(1));
0674: assertEquals("Mike", _rset.getString(2));
0675:
0676: assertTrue(!_rset.next());
0677:
0678: _rset = _stmt.executeQuery("select max(id) from x group by id");
0679: assertNotNull(_rset);
0680: assertTrue(_rset.next());
0681: assertEquals(1, _rset.getInt(1));
0682:
0683: assertTrue(_rset.next());
0684: assertEquals(2, _rset.getInt(1));
0685:
0686: assertTrue(!_rset.next());
0687:
0688: _rset = _stmt
0689: .executeQuery("select xid, max(id) from x group by xid");
0690: assertNotNull(_rset);
0691: assertTrue(_rset.next());
0692: assertEquals("1Amy", _rset.getString(1));
0693: assertEquals(1, _rset.getInt(2));
0694:
0695: assertTrue(_rset.next());
0696: assertEquals("2Mike", _rset.getString(1));
0697: assertEquals(2, _rset.getInt(2));
0698:
0699: assertTrue(!_rset.next());
0700: }
0701:
0702: public void testTwoTableInnerJoinWithFilternConditionWithOrderBy()
0703: throws Exception {
0704: _stmt.execute("create table emp ( id int, name varchar(10))");
0705: _stmt
0706: .execute("create table salary ( sid int, base int, bonus int)");
0707: _stmt.execute("insert into emp values ( 3, 'Teresa')");
0708: _stmt.execute("insert into emp values ( 2, 'Mike')");
0709: _stmt.execute("insert into emp values ( 1, 'Amy')");
0710: _stmt.execute("insert into salary values ( 3, 3000, 300 )");
0711: _stmt.execute("insert into salary values ( 1, 1000, 100)");
0712: _stmt.execute("insert into salary values ( 2, 2000, 200 )");
0713:
0714: _stmt.execute("create btree index empidx on emp(id)");
0715: _stmt.execute("create btree index salaryidx on salary(sid)");
0716:
0717: _rset = _stmt
0718: .executeQuery("select s1.id, s1.name, s2.base + s2.bonus from emp s1 inner join salary s2 on s1.id = s2.sid where s1.id > 1 order by s1.id");
0719:
0720: assertTrue(_rset.next());
0721: assertEquals(2, _rset.getInt(1));
0722: assertEquals("Mike", _rset.getString(2));
0723: assertEquals(2200, _rset.getInt(3));
0724:
0725: assertTrue(_rset.next());
0726: assertEquals(3, _rset.getInt(1));
0727: assertEquals("Teresa", _rset.getString(2));
0728: assertEquals(3300, _rset.getInt(3));
0729:
0730: assertTrue(!_rset.next());
0731: _rset.close();
0732: }
0733:
0734: public void testTwoTableJoinWithGroupBy() throws Exception {
0735: _stmt.execute("create table emp ( id int, name varchar(10))");
0736: _stmt
0737: .execute("create table salary ( sid int, base int, bonus int)");
0738:
0739: _stmt.execute("insert into emp values ( 2, 'Mike')");
0740: _stmt.execute("insert into emp values ( 3, 'Teresa')");
0741: _stmt.execute("insert into emp values ( 1, 'Amy')");
0742: _stmt.execute("insert into salary values ( 2, 4000, 400 )");
0743: _stmt.execute("insert into salary values ( 1, 1000, 100)");
0744: _stmt.execute("insert into salary values ( 2, 4000, 400 )");
0745: _stmt.execute("insert into salary values ( 1, 1000, 100)");
0746:
0747: // This query should create a dynamic index on salary and will not be sorted on
0748: // emp.id, so group by has to sort it to apply group by
0749: _rset = _stmt
0750: .executeQuery("select id, sum(base+bonus) total from emp s1 inner join salary s2 on s1.id = s2.sid group by id");
0751: assertNotNull(_rset);
0752:
0753: assertTrue(_rset.next());
0754: assertEquals(1, _rset.getInt(1));
0755: assertEquals(2200, _rset.getInt(2));
0756:
0757: assertTrue(_rset.next());
0758: assertEquals(2, _rset.getInt(1));
0759: assertEquals(8800, _rset.getInt(2));
0760:
0761: assertTrue(!_rset.next());
0762: _rset.close();
0763:
0764: _stmt.execute("create btree index empidx on emp(id)");
0765:
0766: // Now emp will be scanned using index on emp.id
0767: // So group by does not have to sort it to apply group by
0768: _rset = _stmt
0769: .executeQuery("select id, sum(base+bonus) total from emp s1 inner join salary s2 on s1.id = s2.sid group by id");
0770: assertNotNull(_rset);
0771:
0772: assertTrue(_rset.next());
0773: assertEquals(1, _rset.getInt(1));
0774: assertEquals(2200, _rset.getInt(2));
0775:
0776: assertTrue(_rset.next());
0777: assertEquals(2, _rset.getInt(1));
0778: assertEquals(8800, _rset.getInt(2));
0779:
0780: assertTrue(!_rset.next());
0781: _rset.close();
0782:
0783: // Now emp will be scanned using index on emp.id ,since group by is on sid and
0784: // emp.id = salary.sid , it will swap the group by column and hence sorting is not
0785: // required to apply group by
0786: _rset = _stmt
0787: .executeQuery("select sid, sum(base+bonus) total from emp s1 inner join salary s2 on s1.id = s2.sid group by sid");
0788: assertNotNull(_rset);
0789:
0790: assertTrue(_rset.next());
0791: assertEquals(1, _rset.getInt(1));
0792: assertEquals(2200, _rset.getInt(2));
0793:
0794: assertTrue(_rset.next());
0795: assertEquals(2, _rset.getInt(1));
0796: assertEquals(8800, _rset.getInt(2));
0797:
0798: assertTrue(!_rset.next());
0799: _rset.close();
0800:
0801: _rset = _stmt
0802: .executeQuery("select sid, sum(base+bonus) total from emp s1 left outer join salary s2 on s1.id = s2.sid group by sid");
0803: assertNotNull(_rset);
0804:
0805: assertTrue(_rset.next());
0806: assertEquals(1, _rset.getInt(1));
0807: assertEquals(2200, _rset.getInt(2));
0808:
0809: assertTrue(_rset.next());
0810: assertEquals(2, _rset.getInt(1));
0811: assertEquals(8800, _rset.getInt(2));
0812:
0813: assertTrue(_rset.next());
0814: assertNull(_rset.getObject(1));
0815: assertNull(_rset.getObject(2));
0816:
0817: assertTrue(!_rset.next());
0818: _rset.close();
0819:
0820: _rset = _stmt
0821: .executeQuery("select id, sum(base+bonus) total from emp s1 right outer join salary s2 on s1.id = s2.sid group by id");
0822: assertNotNull(_rset);
0823:
0824: assertTrue(_rset.next());
0825: assertEquals(1, _rset.getInt(1));
0826: assertEquals(2200, _rset.getInt(2));
0827:
0828: assertTrue(_rset.next());
0829: assertEquals(2, _rset.getInt(1));
0830: assertEquals(8800, _rset.getInt(2));
0831:
0832: assertTrue(!_rset.next());
0833: _rset.close();
0834:
0835: _rset = _stmt
0836: .executeQuery("select id, sum(base+bonus) total from emp s1 right outer join salary s2 on s1.id = s2.sid group by id order by id");
0837: assertNotNull(_rset);
0838:
0839: assertTrue(_rset.next());
0840: assertEquals(1, _rset.getInt(1));
0841: assertEquals(2200, _rset.getInt(2));
0842:
0843: assertTrue(_rset.next());
0844: assertEquals(2, _rset.getInt(1));
0845: assertEquals(8800, _rset.getInt(2));
0846:
0847: assertTrue(!_rset.next());
0848: _rset.close();
0849:
0850: _rset = _stmt
0851: .executeQuery("select id, sum(base+bonus) total from emp s1 right outer join salary s2 on s1.id = s2.sid group by id order by id desc");
0852: assertNotNull(_rset);
0853:
0854: assertTrue(_rset.next());
0855: assertEquals(2, _rset.getInt(1));
0856: assertEquals(8800, _rset.getInt(2));
0857:
0858: assertTrue(_rset.next());
0859: assertEquals(1, _rset.getInt(1));
0860: assertEquals(2200, _rset.getInt(2));
0861:
0862: assertTrue(!_rset.next());
0863: _rset.close();
0864:
0865: }
0866:
0867: public void testTwoTableInnerJoinWithOrderBy() throws Exception {
0868: _stmt.execute("create table emp ( id int, name varchar(10))");
0869: _stmt
0870: .execute("create table salary ( sid int, base int, bonus int)");
0871:
0872: _stmt.execute("insert into emp values ( 2, 'Mike')");
0873: _stmt.execute("insert into emp values ( 3, 'Teresa')");
0874: _stmt.execute("insert into emp values ( 1, 'Amy')");
0875: _stmt.execute("insert into salary values ( 2, 4000, 400 )");
0876: _stmt.execute("insert into salary values ( 1, 1000, 100)");
0877:
0878: // This query should create a dynamic index on salary and will not be sorted on
0879: // emp.id, So group by has to sort it to apply group by
0880: _rset = _stmt
0881: .executeQuery("select id, name, base+bonus from emp s1 inner join salary s2 on s1.id = s2.sid order by id");
0882: assertNotNull(_rset);
0883:
0884: assertTrue(_rset.next());
0885: assertEquals(1, _rset.getInt(1));
0886: assertEquals("Amy", _rset.getString(2));
0887: assertEquals(1100, _rset.getInt(3));
0888:
0889: assertTrue(_rset.next());
0890: assertEquals(2, _rset.getInt(1));
0891: assertEquals("Mike", _rset.getString(2));
0892: assertEquals(4400, _rset.getInt(3));
0893:
0894: assertTrue(!_rset.next());
0895: _rset.close();
0896:
0897: _rset = _stmt
0898: .executeQuery("select id, name, (base+bonus) from emp s1 inner join salary s2 on s1.id = s2.sid order by sid");
0899: assertNotNull(_rset);
0900:
0901: assertTrue(_rset.next());
0902: assertEquals(1, _rset.getInt(1));
0903: assertEquals("Amy", _rset.getString(2));
0904: assertEquals(1100, _rset.getInt(3));
0905:
0906: assertTrue(_rset.next());
0907: assertEquals(2, _rset.getInt(1));
0908: assertEquals("Mike", _rset.getString(2));
0909: assertEquals(4400, _rset.getInt(3));
0910:
0911: assertTrue(!_rset.next());
0912: _rset.close();
0913:
0914: // Test desc
0915:
0916: _rset = _stmt
0917: .executeQuery("select id, name, (base+bonus) from emp s1 inner join salary s2 on s1.id = s2.sid order by sid desc");
0918: assertNotNull(_rset);
0919:
0920: assertTrue(_rset.next());
0921: assertEquals(2, _rset.getInt(1));
0922: assertEquals("Mike", _rset.getString(2));
0923: assertEquals(4400, _rset.getInt(3));
0924:
0925: assertTrue(_rset.next());
0926: assertEquals(1, _rset.getInt(1));
0927: assertEquals("Amy", _rset.getString(2));
0928: assertEquals(1100, _rset.getInt(3));
0929:
0930: assertTrue(!_rset.next());
0931: _rset.close();
0932:
0933: _stmt.execute("create btree index empidx on emp(id)");
0934:
0935: // Now emp will be scanned using index on emp.id
0936: // So order by does not have to sort it to apply order by
0937: _rset = _stmt
0938: .executeQuery("select id, name, (base+bonus) total from emp s1 inner join salary s2 on s1.id = s2.sid order by id");
0939: assertNotNull(_rset);
0940:
0941: assertTrue(_rset.next());
0942: assertEquals(1, _rset.getInt(1));
0943: assertEquals("Amy", _rset.getString(2));
0944: assertEquals(1100, _rset.getInt(3));
0945:
0946: assertTrue(_rset.next());
0947: assertEquals(2, _rset.getInt(1));
0948: assertEquals("Mike", _rset.getString(2));
0949: assertEquals(4400, _rset.getInt(3));
0950:
0951: assertTrue(!_rset.next());
0952: _rset.close();
0953:
0954: // Now emp will be scanned using index on emp.id ,since order by is on sid and
0955: // emp.id = salary.sid , it will swap the order by column and hence sorting is not
0956: // required to apply order by
0957: _rset = _stmt
0958: .executeQuery("select id, name, (base+bonus) from emp s1 inner join salary s2 on s1.id = s2.sid order by sid");
0959: assertNotNull(_rset);
0960:
0961: assertTrue(_rset.next());
0962: assertEquals(1, _rset.getInt(1));
0963: assertEquals("Amy", _rset.getString(2));
0964: assertEquals(1100, _rset.getInt(3));
0965:
0966: assertTrue(_rset.next());
0967: assertEquals(2, _rset.getInt(1));
0968: assertEquals("Mike", _rset.getString(2));
0969: assertEquals(4400, _rset.getInt(3));
0970:
0971: assertTrue(!_rset.next());
0972: _rset.close();
0973:
0974: // Test desc
0975:
0976: _rset = _stmt
0977: .executeQuery("select id, name, (base+bonus) from emp s1 inner join salary s2 on s1.id = s2.sid order by sid desc");
0978: assertNotNull(_rset);
0979:
0980: assertTrue(_rset.next());
0981: assertEquals(2, _rset.getInt(1));
0982: assertEquals("Mike", _rset.getString(2));
0983: assertEquals(4400, _rset.getInt(3));
0984:
0985: assertTrue(_rset.next());
0986: assertEquals(1, _rset.getInt(1));
0987: assertEquals("Amy", _rset.getString(2));
0988: assertEquals(1100, _rset.getInt(3));
0989:
0990: assertTrue(!_rset.next());
0991: _rset.close();
0992:
0993: // non column order by
0994: _stmt.execute("update emp set name = 'MIKE' where id = 2");
0995: _stmt.execute("update emp set name = 'amy' where id = 1");
0996: _rset = _stmt
0997: .executeQuery("select id, name, (base+bonus) from emp s1 inner join salary s2 on s1.id = s2.sid order by upper(name)");
0998: assertNotNull(_rset);
0999:
1000: assertTrue(_rset.next());
1001: assertEquals(1, _rset.getInt(1));
1002: assertEquals("amy", _rset.getString(2));
1003: assertEquals(1100, _rset.getInt(3));
1004:
1005: assertTrue(_rset.next());
1006: assertEquals(2, _rset.getInt(1));
1007: assertEquals("MIKE", _rset.getString(2));
1008: assertEquals(4400, _rset.getInt(3));
1009:
1010: assertTrue(!_rset.next());
1011: _rset.close();
1012: }
1013:
1014: public void testTwoTableLeftJoinWithOrderBy() throws Exception {
1015: _stmt.execute("create table emp ( id int, name varchar(10))");
1016: _stmt
1017: .execute("create table salary ( sid int, base int, bonus int)");
1018:
1019: _stmt.execute("insert into emp values ( 2, 'Mike')");
1020: _stmt.execute("insert into emp values ( 3, 'Teresa')");
1021: _stmt.execute("insert into emp values ( 1, 'Amy')");
1022: _stmt.execute("insert into salary values ( 2, 4000, 400 )");
1023: _stmt.execute("insert into salary values ( 1, 1000, 100)");
1024:
1025: // This will create index on right table dynamically and require explicit sort
1026: // after join
1027: _rset = _stmt
1028: .executeQuery("select id, name, (base+bonus) from emp s1 left outer join salary s2 on s1.id = s2.sid order by id");
1029: assertNotNull(_rset);
1030:
1031: assertTrue(_rset.next());
1032: assertEquals(1, _rset.getInt(1));
1033: assertEquals("Amy", _rset.getString(2));
1034: assertEquals(1100, _rset.getInt(3));
1035:
1036: assertTrue(_rset.next());
1037: assertEquals(2, _rset.getInt(1));
1038: assertEquals("Mike", _rset.getString(2));
1039: assertEquals(4400, _rset.getInt(3));
1040:
1041: assertTrue(_rset.next());
1042: assertEquals(3, _rset.getInt(1));
1043: assertEquals("Teresa", _rset.getString(2));
1044: assertNull(_rset.getObject(3));
1045:
1046: assertTrue(!_rset.next());
1047: _rset.close();
1048:
1049: // Will swap sid with id, since right table will be scanned using
1050: // ChangingIndexedRowIterator and require explicit sort after join
1051: _rset = _stmt
1052: .executeQuery("select sid, name, (base+bonus) from emp s1 left outer join salary s2 on s1.id = s2.sid order by sid");
1053: assertNotNull(_rset);
1054:
1055: assertTrue(_rset.next());
1056: assertEquals(1, _rset.getInt(1));
1057: assertEquals("Amy", _rset.getString(2));
1058: assertEquals(1100, _rset.getInt(3));
1059:
1060: assertTrue(_rset.next());
1061: assertEquals(2, _rset.getInt(1));
1062: assertEquals("Mike", _rset.getString(2));
1063: assertEquals(4400, _rset.getInt(3));
1064:
1065: assertTrue(_rset.next());
1066: assertNull(_rset.getObject(1));
1067: assertEquals("Teresa", _rset.getString(2));
1068: assertNull(_rset.getObject(3));
1069:
1070: assertTrue(!_rset.next());
1071: _rset.close();
1072:
1073: // Test desc
1074: _rset = _stmt
1075: .executeQuery("select sid, name, (base+bonus) from emp s1 left outer join salary s2 on s1.id = s2.sid order by sid desc");
1076: assertNotNull(_rset);
1077:
1078: assertTrue(_rset.next());
1079: assertNull(_rset.getObject(1));
1080: assertEquals("Teresa", _rset.getString(2));
1081: assertNull(_rset.getObject(3));
1082:
1083: assertTrue(_rset.next());
1084: assertEquals(2, _rset.getInt(1));
1085: assertEquals("Mike", _rset.getString(2));
1086: assertEquals(4400, _rset.getInt(3));
1087:
1088: assertTrue(_rset.next());
1089: assertEquals(1, _rset.getInt(1));
1090: assertEquals("Amy", _rset.getString(2));
1091: assertEquals(1100, _rset.getInt(3));
1092:
1093: assertTrue(!_rset.next());
1094: _rset.close();
1095:
1096: _stmt.execute("create btree index empidx on emp(id)");
1097:
1098: _rset = _stmt
1099: .executeQuery("select sid, name, (base+bonus) from emp s1 left outer join salary s2 on s1.id = s2.sid order by sid");
1100: assertNotNull(_rset);
1101:
1102: assertTrue(_rset.next());
1103: assertEquals(1, _rset.getInt(1));
1104: assertEquals("Amy", _rset.getString(2));
1105: assertEquals(1100, _rset.getInt(3));
1106:
1107: assertTrue(_rset.next());
1108: assertEquals(2, _rset.getInt(1));
1109: assertEquals("Mike", _rset.getString(2));
1110: assertEquals(4400, _rset.getInt(3));
1111:
1112: assertTrue(_rset.next());
1113: assertNull(_rset.getObject(1));
1114: assertEquals("Teresa", _rset.getString(2));
1115: assertNull(_rset.getObject(3));
1116:
1117: assertTrue(!_rset.next());
1118: _rset.close();
1119:
1120: // Test desc
1121: _rset = _stmt
1122: .executeQuery("select sid, name, (base+bonus) from emp s1 left outer join salary s2 on s1.id = s2.sid order by sid desc");
1123: assertNotNull(_rset);
1124:
1125: assertTrue(_rset.next());
1126: assertNull(_rset.getObject(1));
1127: assertEquals("Teresa", _rset.getString(2));
1128: assertNull(_rset.getObject(3));
1129:
1130: assertTrue(_rset.next());
1131: assertEquals(2, _rset.getInt(1));
1132: assertEquals("Mike", _rset.getString(2));
1133: assertEquals(4400, _rset.getInt(3));
1134:
1135: assertTrue(_rset.next());
1136: assertEquals(1, _rset.getInt(1));
1137: assertEquals("Amy", _rset.getString(2));
1138: assertEquals(1100, _rset.getInt(3));
1139:
1140: assertTrue(!_rset.next());
1141: _rset.close();
1142:
1143: }
1144:
1145: public void testTwoTableRightJoinWithOrderBy() throws Exception {
1146: _stmt.execute("create table emp ( id int, name varchar(10))");
1147: _stmt
1148: .execute("create table salary ( sid int, base int, bonus int)");
1149:
1150: _stmt.execute("insert into emp values ( 2, 'Mike')");
1151: _stmt.execute("insert into emp values ( 3, 'Teresa')");
1152: _stmt.execute("insert into emp values ( 1, 'Amy')");
1153: _stmt.execute("insert into salary values ( 2, 4000, 400 )");
1154: _stmt.execute("insert into salary values ( 1, 1000, 100)");
1155:
1156: // This will create index on right table dynamically and require explicit sort
1157: // after join
1158: _rset = _stmt
1159: .executeQuery("select id, name, (base+bonus) from emp s1 right outer join salary s2 on s1.id = s2.sid order by id");
1160: assertNotNull(_rset);
1161:
1162: assertTrue(_rset.next());
1163: assertEquals(1, _rset.getInt(1));
1164: assertEquals("Amy", _rset.getString(2));
1165: assertEquals(1100, _rset.getInt(3));
1166:
1167: assertTrue(_rset.next());
1168: assertEquals(2, _rset.getInt(1));
1169: assertEquals("Mike", _rset.getString(2));
1170: assertEquals(4400, _rset.getInt(3));
1171:
1172: assertTrue(!_rset.next());
1173: _rset.close();
1174:
1175: // Will swap sid with id, since right table will be scanned using
1176: // ChangingIndexedRowIterator and require explicit sort after join
1177: _rset = _stmt
1178: .executeQuery("select sid, name, (base+bonus) from emp s1 right outer join salary s2 on s1.id = s2.sid order by sid");
1179: assertNotNull(_rset);
1180:
1181: assertTrue(_rset.next());
1182: assertEquals(1, _rset.getInt(1));
1183: assertEquals("Amy", _rset.getString(2));
1184: assertEquals(1100, _rset.getInt(3));
1185:
1186: assertTrue(_rset.next());
1187: assertEquals(2, _rset.getInt(1));
1188: assertEquals("Mike", _rset.getString(2));
1189: assertEquals(4400, _rset.getInt(3));
1190:
1191: assertTrue(!_rset.next());
1192: _rset.close();
1193:
1194: // Test desc
1195: _rset = _stmt
1196: .executeQuery("select sid, name, (base+bonus) from emp s1 right outer join salary s2 on s1.id = s2.sid order by sid desc");
1197: assertNotNull(_rset);
1198:
1199: assertTrue(_rset.next());
1200: assertEquals(2, _rset.getInt(1));
1201: assertEquals("Mike", _rset.getString(2));
1202: assertEquals(4400, _rset.getInt(3));
1203:
1204: assertTrue(_rset.next());
1205: assertEquals(1, _rset.getInt(1));
1206: assertEquals("Amy", _rset.getString(2));
1207: assertEquals(1100, _rset.getInt(3));
1208:
1209: assertTrue(!_rset.next());
1210: _rset.close();
1211:
1212: _stmt.execute("create btree index salaryidx on salary(sid)");
1213:
1214: _rset = _stmt
1215: .executeQuery("select sid, name, (base+bonus) from emp s1 right outer join salary s2 on s1.id = s2.sid order by sid");
1216: assertNotNull(_rset);
1217:
1218: assertTrue(_rset.next());
1219: assertEquals(1, _rset.getInt(1));
1220: assertEquals("Amy", _rset.getString(2));
1221: assertEquals(1100, _rset.getInt(3));
1222:
1223: assertTrue(_rset.next());
1224: assertEquals(2, _rset.getInt(1));
1225: assertEquals("Mike", _rset.getString(2));
1226: assertEquals(4400, _rset.getInt(3));
1227:
1228: assertTrue(!_rset.next());
1229: _rset.close();
1230:
1231: // Test desc
1232: _rset = _stmt
1233: .executeQuery("select sid, name, (base+bonus) from emp s1 right outer join salary s2 on s1.id = s2.sid order by sid desc");
1234: assertNotNull(_rset);
1235:
1236: assertTrue(_rset.next());
1237: assertEquals(2, _rset.getInt(1));
1238: assertEquals("Mike", _rset.getString(2));
1239: assertEquals(4400, _rset.getInt(3));
1240:
1241: assertTrue(_rset.next());
1242: assertEquals(1, _rset.getInt(1));
1243: assertEquals("Amy", _rset.getString(2));
1244: assertEquals(1100, _rset.getInt(3));
1245:
1246: assertTrue(!_rset.next());
1247: _rset.close();
1248:
1249: }
1250:
1251: public void testAmbiguousColumnReferenceVisitorForGroupBy()
1252: throws SQLException {
1253: _stmt.execute("create table a (id int)");
1254: _stmt.execute("insert into a values (1)");
1255:
1256: // should throw ambiguous column reference exception
1257: try {
1258: _rset = _stmt
1259: .executeQuery("select id, count(id), id from a group by id");
1260: fail("Expected exception");
1261: } catch (SQLException ex) {
1262: // expected
1263: }
1264:
1265: try {
1266: _stmt
1267: .executeQuery("select id, (select count(*) from a) id from a group by id");
1268: fail("Expected exception");
1269: } catch (SQLException ex) {
1270: // expected
1271: }
1272:
1273: }
1274:
1275: public void testBasicHaving() throws Exception {
1276: _stmt.execute("create table emp ( id int, name varchar(10))");
1277: _stmt
1278: .execute("create table salary ( eid int, base int, bonus int)");
1279:
1280: _stmt.execute("insert into emp values ( 1, 'Amy')");
1281: _stmt.execute("insert into emp values ( 2, 'Mike')");
1282: _stmt.execute("insert into emp values ( 3, 'Teresa')");
1283:
1284: _stmt.execute("insert into salary values ( 1, 1000, 100)");
1285: _stmt.execute("insert into salary values ( 1, 1000, 100)");
1286: _stmt.execute("insert into salary values ( 1, 1000, 100)");
1287: _stmt.execute("insert into salary values ( 2, 4000, 400 )");
1288: _stmt.execute("insert into salary values ( 2, 4000, 400 )");
1289: _stmt.execute("insert into salary values ( 2, 4000, 400 )");
1290:
1291: try {
1292: _stmt
1293: .executeQuery("select s1.id, s1.name, sum(s2.base + s2.bonus) totalSalary "
1294: + "from emp s1 inner join salary s2 on s1.id = s2.eid group by id, name having s2.base = 4000");
1295: fail("Expected Invalid Group By Expression error msg");
1296: } catch (SQLException e) {
1297: // expected
1298: }
1299:
1300: _rset = _stmt
1301: .executeQuery("select s1.id, s1.name, sum(s2.base + s2.bonus) totalSalary "
1302: + "from emp s1 inner join salary s2 on s1.id = s2.eid group by id, name having avg(s2.base) = 4000");
1303:
1304: assertTrue(_rset.next());
1305: assertEquals(2, _rset.getInt("id"));
1306: assertEquals("Mike", _rset.getString("name"));
1307: assertEquals(13200, _rset.getInt("totalSalary"));
1308: assertTrue(!_rset.next());
1309: _rset.close();
1310:
1311: }
1312: }
|