0001: /*
0002: * $Id: TestBugs.java,v 1.46 2005/06/18 01:03:44 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.io.File;
0044: import java.math.BigDecimal;
0045: import java.sql.Connection;
0046: import java.sql.DriverManager;
0047: import java.sql.PreparedStatement;
0048: import java.sql.ResultSet;
0049: import java.sql.SQLException;
0050: import java.sql.Statement;
0051:
0052: import org.axiondb.jdbc.AxionConnection;
0053:
0054: import junit.framework.Test;
0055: import junit.framework.TestSuite;
0056:
0057: /**
0058: * @version $Revision: 1.46 $ $Date: 2005/06/18 01:03:44 $
0059: * @author Rodney Waldhoff
0060: * @author Ahimanikya Satapathy
0061: * @author Jonathan Giron
0062: */
0063: public class TestBugs extends AbstractFunctionalTest {
0064:
0065: //------------------------------------------------------------ Conventional
0066:
0067: public TestBugs(String testName) {
0068: super (testName);
0069: }
0070:
0071: public static Test suite() {
0072: return new TestSuite(TestBugs.class);
0073: }
0074:
0075: //--------------------------------------------------------------- Lifecycle
0076:
0077: public void setUp() throws Exception {
0078: super .setUp();
0079: }
0080:
0081: public void tearDown() throws Exception {
0082: super .tearDown();
0083: }
0084:
0085: protected String getConnectString() {
0086: return "jdbc:axiondb:testdb:testdb";
0087: }
0088:
0089: protected File getDatabaseDirectory() {
0090: return new File(new File("."), "testdb");
0091: }
0092:
0093: //------------------------------------------------------------------- Tests
0094:
0095: public void testSelfJoinBug() throws Exception {
0096: _stmt
0097: .execute("create table ALPHA ( ID int, DESCR varchar(10) )");
0098: _stmt.execute("insert into ALPHA values ( 1, 'one' )");
0099: _stmt.execute("insert into ALPHA values ( 2, 'two' )");
0100:
0101: _stmt
0102: .execute("create table BETA ( ID int, DESCR varchar(10) )");
0103: _stmt.execute("insert into BETA values ( 1, 'one' )");
0104: _stmt.execute("insert into BETA values ( 2, 'two' )");
0105:
0106: // works when joined another
0107: {
0108: ResultSet rset = _stmt
0109: .executeQuery("select A.id, B.descr from alpha A, beta B where A.id = B.id order by A.id");
0110: assertTrue(rset.next());
0111: assertEquals(1, rset.getInt(1));
0112: assertEquals("one", rset.getString(2));
0113: assertTrue(rset.next());
0114: assertEquals(2, rset.getInt(1));
0115: assertEquals("two", rset.getString(2));
0116: assertTrue(!rset.next());
0117: rset.close();
0118: }
0119:
0120: // but not with self
0121: {
0122: ResultSet rset = _stmt
0123: .executeQuery("select A.id, B.descr from alpha A, alpha B where A.id = B.id order by A.id");
0124: assertTrue(rset.next());
0125: assertEquals(1, rset.getInt(1));
0126: assertEquals("one", rset.getString(2));
0127: assertTrue(rset.next());
0128: assertEquals(2, rset.getInt(1));
0129: assertEquals("two", rset.getString(2));
0130: assertTrue(!rset.next());
0131: rset.close();
0132: }
0133: }
0134:
0135: // ISSUE #XX
0136: // See http://axion.tigris.org/servlets/ReadMsg?list=users&msgNo=215
0137: public void testUniqueBTreeIndexBug1() throws Exception {
0138: _stmt.execute("CREATE TABLE TEST_TABLE (ID VARCHAR);");
0139: _stmt
0140: .execute("CREATE UNIQUE INDEX TEST_INDEX ON TEST_TABLE (ID);");
0141: _stmt.execute("INSERT INTO TEST_TABLE VALUES ('b');");
0142: _stmt.execute("INSERT INTO TEST_TABLE VALUES ('a');");
0143:
0144: assertEquals(
0145: 1,
0146: _stmt
0147: .executeUpdate("DELETE FROM TEST_TABLE WHERE ID = 'a';"));
0148: assertEquals(
0149: 1,
0150: _stmt
0151: .executeUpdate("DELETE FROM TEST_TABLE WHERE ID = 'b';"));
0152: }
0153:
0154: // ISSUE #XX
0155: // See http://axion.tigris.org/servlets/ReadMsg?list=dev&msgNo=825
0156: public void testUniqueBTreeIndexBug2() throws Exception {
0157: _stmt.execute("CREATE TABLE TEST_TABLE (ID VARCHAR);");
0158: _stmt
0159: .execute("CREATE UNIQUE BTREE INDEX TEST_INDEX ON TEST_TABLE (ID);");
0160: _stmt.execute("INSERT INTO TEST_TABLE VALUES ('b');");
0161: assertResult("b", "SELECT id FROM TEST_TABLE where ID='b'");
0162: assertEquals(
0163: 1,
0164: _stmt
0165: .executeUpdate("DELETE FROM TEST_TABLE WHERE ID = 'b';"));
0166: _stmt.execute("INSERT INTO TEST_TABLE VALUES ('b');");
0167: assertResult("b", "SELECT id FROM TEST_TABLE where ID='b'");
0168:
0169: _stmt.execute("SHUTDOWN");
0170: _stmt.close();
0171: _conn.close();
0172: super .setUp();
0173: assertResult("b", "SELECT id FROM TEST_TABLE where ID='b'");
0174: }
0175:
0176: // ISSUE #XX
0177: // See http://axion.tigris.org/servlets/ReadMsg?list=users&msgNo=215
0178: public void testUniqueArrayIndexBug1() throws Exception {
0179: _stmt.execute("CREATE TABLE TEST_TABLE (ID VARCHAR);");
0180: _stmt
0181: .execute("CREATE UNIQUE ARRAY INDEX TEST_INDEX ON TEST_TABLE (ID);");
0182: _stmt.execute("INSERT INTO TEST_TABLE VALUES ('b');");
0183: _stmt.execute("INSERT INTO TEST_TABLE VALUES ('a');");
0184:
0185: assertEquals(
0186: 1,
0187: _stmt
0188: .executeUpdate("DELETE FROM TEST_TABLE WHERE ID = 'a';"));
0189: assertEquals(
0190: 1,
0191: _stmt
0192: .executeUpdate("DELETE FROM TEST_TABLE WHERE ID = 'b';"));
0193: }
0194:
0195: // ISSUE #XX
0196: // See http://axion.tigris.org/servlets/ReadMsg?list=dev&msgNo=825
0197: public void testUniqueArrayIndexBug2() throws Exception {
0198: _stmt.execute("CREATE TABLE TEST_TABLE (ID VARCHAR);");
0199: _stmt
0200: .execute("CREATE UNIQUE ARRAY INDEX TEST_INDEX ON TEST_TABLE (ID);");
0201: _stmt.execute("INSERT INTO TEST_TABLE VALUES ('b');");
0202: assertEquals(
0203: 1,
0204: _stmt
0205: .executeUpdate("DELETE FROM TEST_TABLE WHERE ID = 'b';"));
0206: _stmt.execute("INSERT INTO TEST_TABLE VALUES ('b');");
0207:
0208: _stmt.execute("SHUTDOWN");
0209: _stmt.close();
0210: _conn.close();
0211: super .setUp();
0212: assertResult("b", "SELECT id FROM TEST_TABLE where ID='b'");
0213: }
0214:
0215: public void testInWithNullValuesBug() throws Exception {
0216: _stmt.execute("create table FOO ( id int, parent_id int )");
0217: _stmt.execute("insert into FOO values ( 1, null )");
0218: _stmt.execute("insert into FOO values ( 2, 1 )");
0219: _stmt.execute("insert into FOO values ( 3, 1 )");
0220: _stmt.execute("insert into FOO values ( 4, 2 )");
0221: assertResult(1, "select id from FOO where parent_id is null");
0222: assertResult(4, "select id from FOO where parent_id = 2");
0223: assertResult(4, "select id from FOO where parent_id in ( 2 )");
0224: }
0225:
0226: // ISSUE #XX
0227: // See http://axion.tigris.org/servlets/ReadMsg?list=dev&msgNo=208
0228: public void test_IssueXX_BooleanBug() throws Exception {
0229: _stmt.execute("create table BOOL_BUG ( B boolean )");
0230: PreparedStatement psmt = _conn
0231: .prepareStatement("insert into BOOL_BUG values ( ? )");
0232: psmt.setBoolean(1, true);
0233: psmt.executeUpdate();
0234: psmt.close();
0235: _rset = _stmt.executeQuery("select B from BOOL_BUG");
0236: assertTrue(_rset.next());
0237: assertTrue(_rset.getBoolean(1));
0238: }
0239:
0240: public void test_AggregationBug_CountStar() throws Exception {
0241: _stmt
0242: .execute("create table COUNT_BUG ( \"DATA\" varchar(10) )");
0243: _conn.setAutoCommit(false);
0244: _stmt.execute("insert into COUNT_BUG values ( 'ABC' )");
0245: _stmt.execute("insert into COUNT_BUG values ( 'XYZ' )");
0246: _stmt.execute("insert into COUNT_BUG values ( 'XYZ' )");
0247: _conn.commit();
0248: PreparedStatement psmt = _conn
0249: .prepareStatement("select count(*) from COUNT_BUG where \"DATA\" = ?");
0250: psmt.setString(1, "xyzzy");
0251: assertResult(0, psmt.executeQuery());
0252: psmt.setString(1, "ABC");
0253: assertResult(1, psmt.executeQuery());
0254: psmt.setString(1, "XYZ");
0255: assertResult(2, psmt.executeQuery());
0256: psmt.setString(1, "xyzzy");
0257: assertResult(0, psmt.executeQuery());
0258: }
0259:
0260: public void test_AggregationBug_GroupBy() throws Exception {
0261: _stmt
0262: .execute("create table COUNT_BUG ( \"DATA\" varchar(10) )");
0263: _conn.setAutoCommit(false);
0264: _stmt.execute("insert into COUNT_BUG values ( 'ABC' )");
0265: _stmt.execute("insert into COUNT_BUG values ( 'XYZ' )");
0266: _stmt.execute("insert into COUNT_BUG values ( 'XYZ' )");
0267: _conn.commit();
0268: PreparedStatement psmt = _conn
0269: .prepareStatement("select \"DATA\" from COUNT_BUG where \"DATA\" = ? group by \"DATA\"");
0270: psmt.setString(1, "xyzzy");
0271: assertNoRows(psmt.executeQuery());
0272: psmt.setString(1, "ABC");
0273: assertResult("ABC", psmt.executeQuery());
0274: psmt.setString(1, "XYZ");
0275: assertResult("XYZ", psmt.executeQuery());
0276: psmt.setString(1, "xyzzy");
0277: assertNoRows(psmt.executeQuery());
0278: }
0279:
0280: // ISSUE #1
0281: // See http://axion.tigris.org/issues/show_bug.cgi?id=1
0282: // Issue #1 was a bug against the old code base, it is no longer valid.
0283:
0284: // ISSUE #2
0285: // See http://axion.tigris.org/issues/show_bug.cgi?id=2
0286: // Issue #2 was a bug against the old code base, it is no longer valid.
0287:
0288: // ISSUE #3
0289: // See http://axion.tigris.org/issues/show_bug.cgi?id=3
0290: // Issue #3 is invalid, a classpath error on the part of the user.
0291:
0292: // ISSUE #4
0293: // See http://axion.tigris.org/issues/show_bug.cgi?id=4
0294: // Issue #4 reports a file missing from the source distribution. Fixed, but not
0295: // testable here.
0296:
0297: // ISSUE #5
0298: // See http://axion.tigris.org/issues/show_bug.cgi?id=5
0299: public void test_Issue5_IndexOnBooleanColumn_default()
0300: throws Exception {
0301: booleanTableTest("");
0302: }
0303:
0304: // ISSUE #5
0305: // See http://axion.tigris.org/issues/show_bug.cgi?id=5
0306: public void test_Issue5_IndexOnBooleanColumn_array()
0307: throws Exception {
0308: booleanTableTest("array");
0309: }
0310:
0311: // ISSUE #5
0312: // See http://axion.tigris.org/issues/show_bug.cgi?id=5
0313: public void test_Issue5_IndexOnBooleanColumn_btree()
0314: throws Exception {
0315: booleanTableTest("btree");
0316: }
0317:
0318: // ISSUE #5
0319: // See http://axion.tigris.org/issues/show_bug.cgi?id=5
0320: private void booleanTableTest(String indextype) throws SQLException {
0321: _stmt.execute("create table t ( b boolean )");
0322: _stmt.execute("create " + indextype + " index i on t ( b )");
0323:
0324: assertEquals(1, _stmt
0325: .executeUpdate("insert into t values ( true )"));
0326: _rset = _stmt.executeQuery("select * from t");
0327: assertNotNull(_rset);
0328: assertTrue(_rset.next());
0329: assertTrue(_rset.getBoolean(1));
0330: assertTrue(!_rset.next());
0331:
0332: assertEquals(1, _stmt
0333: .executeUpdate("insert into t values ( true )"));
0334: assertEquals(1, _stmt
0335: .executeUpdate("insert into t values ( false )"));
0336: _rset = _stmt.executeQuery("select count(*) from t");
0337: assertTrue(_rset.next());
0338: assertEquals(3, _rset.getInt(1));
0339: assertTrue(!_rset.next());
0340: }
0341:
0342: // ISSUE #6
0343: // See http://axion.tigris.org/issues/show_bug.cgi?id=6
0344: public void test_Issue6_CantCompareIntAndBigDecimal()
0345: throws Exception {
0346: _stmt.execute("create table x1 ( id int )");
0347: _stmt.execute("create table x2 ( id number )");
0348: _stmt.executeUpdate("insert into x1 values ( 1 )");
0349: _stmt.executeUpdate("insert into x2 values ( 1 )");
0350: assertOneRow("select * from x1 left outer join x2 on (x1.id = x2.id)");
0351: assertOneRow("select * from x1, x2 where x1.id = x2.id");
0352: }
0353:
0354: // ISSUE #7
0355: // See http://axion.tigris.org/issues/show_bug.cgi?id=7
0356: public void test_Issue7_ParserAcceptsGibberishAfterStatement()
0357: throws Exception {
0358: _stmt.execute("create table X ( id int )");
0359: try {
0360: _stmt.executeQuery("select * from X Y xyzzy");
0361: fail("Expected SQLException");
0362: } catch (SQLException e) {
0363: // expected
0364: }
0365: try {
0366: _stmt.executeQuery("select * from X where true xyzzy");
0367: fail("Expected SQLException");
0368: } catch (SQLException e) {
0369: // expected
0370: }
0371: }
0372:
0373: // ISSUE #8
0374: // See http://axion.tigris.org/issues/show_bug.cgi?id=8
0375: // TODO: Issue #8 (Insufficient Column Metadata) test me.
0376:
0377: // ISSUE #9
0378: // See http://axion.tigris.org/issues/show_bug.cgi?id=9
0379: // See org.axiondb.engine.TestDiskDatabase.testMetaFile
0380:
0381: // ISSUE #10
0382: // See http://axion.tigris.org/issues/show_bug.cgi?id=10
0383: public void test_Issue10_ColumnResolutionWithInBug()
0384: throws Exception {
0385: _stmt
0386: .execute("create table product ( product_id int, sku varchar(10) )");
0387: _stmt
0388: .executeUpdate("insert into product values ( 1000, 'sku0' )");
0389: _stmt
0390: .execute("create table category_product ( category_id int, product_id int )");
0391: _stmt
0392: .executeUpdate("insert into category_product values ( 100, 1000 )");
0393:
0394: assertOneRow("select * from category_product where category_product.category_id in ( 100 )");
0395:
0396: assertOneRow("select * from category_product, product where category_product.category_id in ( 100 ) and product.product_id = category_product.product_id");
0397: assertOneRow("select * from category_product, product where product.product_id = category_product.product_id and category_product.category_id in ( 100 )");
0398:
0399: assertOneRow("select * from product, category_product where category_product.category_id = 100 and product.product_id = category_product.product_id");
0400: assertOneRow("select * from product, category_product where product.product_id = category_product.product_id and category_product.category_id = 100");
0401:
0402: assertOneRow("select * from product, category_product where category_product.category_id in ( 100 ) and product.product_id = category_product.product_id");
0403: assertOneRow("select * from product, category_product where product.product_id = category_product.product_id and category_product.category_id in ( 100 )");
0404: }
0405:
0406: // ISSUE #11
0407: // See http://axion.tigris.org/issues/show_bug.cgi?id=11
0408: public void test_Issue11_ParensRequiredAroundJoinCondition()
0409: throws Exception {
0410: _stmt.execute("create table X ( id int )");
0411: _stmt.execute("create table Y ( id int )");
0412: _stmt.executeUpdate("insert into X values ( 1 )");
0413: _stmt.executeUpdate("insert into Y values ( 1 )");
0414: assertOneRow("select * from x, y where x.id = y.id");
0415:
0416: assertOneRow("select * from x left outer join y on ( x.id = y.id )");
0417: assertOneRow("select * from y left outer join x on ( x.id = y.id )");
0418: assertOneRow("select * from x right outer join y on ( x.id = y.id )");
0419: assertOneRow("select * from y right outer join x on ( x.id = y.id )");
0420:
0421: assertOneRow("select * from x left outer join y on (( x.id = y.id ))");
0422: assertOneRow("select * from x left outer join y on ((( x.id = y.id )))");
0423:
0424: assertException("select * from y right outer join x on ( x.id = y.id ");
0425: assertException("select * from y right outer join x on x.id = y.id )");
0426:
0427: assertOneRow("select * from x left outer join y on x.id = y.id");
0428: assertOneRow("select * from y left outer join x on x.id = y.id");
0429: assertOneRow("select * from x right outer join y on x.id = y.id");
0430: assertOneRow("select * from y right outer join x on x.id = y.id");
0431: }
0432:
0433: // ISSUE #12
0434: // See http://axion.tigris.org/issues/show_bug.cgi?id=12
0435: // TODO: Issue #12 (System tables should be read only) test me.
0436:
0437: // ISSUE #13
0438: // See http://axion.tigris.org/issues/show_bug.cgi?id=13
0439: public void test_Issue13_PlacementOfDefaultClauseInCreateTable()
0440: throws Exception {
0441: _stmt.execute("create table A ( id varchar default 'xyzzy' )");
0442: _stmt
0443: .execute("create table B ( id varchar(100) default 'xyzzy' )");
0444: _stmt
0445: .execute("create table C ( id varchar(100) default 'xyzzy' PRIMARY KEY )");
0446: _stmt
0447: .execute("create table D ( id varchar(100) default 'xyzzy' UNIQUE NOT NULL )");
0448: }
0449:
0450: // ISSUE #14
0451: // See http://axion.tigris.org/issues/show_bug.cgi?id=14
0452: public void test_Issue14_ExpressionParsing_1() throws Exception {
0453: _stmt.execute("create table foo ( a int, b int, c int )");
0454: _stmt.executeUpdate("insert into foo values ( 0, 0, 0 )");
0455: _stmt.executeUpdate("insert into foo values ( 1, 0, 1 )");
0456: _stmt.executeUpdate("insert into foo values ( 2, 1, 1 )");
0457: _stmt.executeUpdate("insert into foo values ( 3, 1, 2 )");
0458: _stmt.executeUpdate("insert into foo values ( 4, 2, 2 )");
0459: _stmt.executeUpdate("insert into foo values ( 5, 2, 3 )");
0460: for (int i = 0; i < 6; i++) {
0461: assertOneRow("select * from foo where b+c=a AND a = " + i);
0462: assertOneRow("select * from foo where b+c = " + i);
0463: assertOneRow("select * from foo where b + c = " + i);
0464: assertResult(i, "select b + c from foo where a = " + i);
0465: assertResult(i, "select b + c from foo where a = " + i);
0466: assertResult(0, "select b + c - a from foo where a = " + i);
0467: assertResult(0, "select b+c-a from foo where a = " + i);
0468: assertException("select b + c - a = 0 from foo where a = "
0469: + i);
0470: assertException("select b+c-a = 0 from foo where a = " + i);
0471: }
0472: }
0473:
0474: // ISSUE #14
0475: // See http://axion.tigris.org/issues/show_bug.cgi?id=14
0476: public void test_Issue14_ExpressionParsing_2() throws Exception {
0477: assertResult(1, "select 1");
0478: assertResult(-1, "select -1");
0479: assertResult(2, "select 1+1");
0480: assertResult(2, "select 1 + 1");
0481: assertResult(2, "select 1-(-1)");
0482: assertResult(2, "select 1 - -1");
0483: assertResult(2, "select 1 --1");
0484: assertResult(0, "select 1-1");
0485: assertResult(0, "select 1 - 1");
0486: assertResult(0, "select 1 + -1");
0487: assertResult(0, "select 1+-1");
0488: assertResult(0, "select 1 + (-1)");
0489: assertResult(0, "select 1 + 0 - 1");
0490: assertResult(0, "select 1+0-1");
0491: assertResult(0, "select (1+0) - 1");
0492: assertResult(0, "select (1+0)-1");
0493: assertResult(0, "select 1+(0 - 1)");
0494: assertResult(0, "select 1+(0-1)");
0495: }
0496:
0497: // ISSUE #14
0498: // See http://axion.tigris.org/issues/show_bug.cgi?id=14
0499: public void test_Issue14_ExpressionParsing_3() throws Exception {
0500: _stmt.execute("create table foo ( a int, b int, c int )");
0501: _stmt.executeUpdate("insert into foo values ( 3, 1, 2 )");
0502: assertResult(3, "select a from foo where true");
0503:
0504: ResultSet rset = _stmt
0505: .executeQuery("select a from foo where false");
0506: assertFalse(rset.next());
0507:
0508: rset = _stmt
0509: .executeQuery("select a from foo where true or false");
0510: assertTrue(rset.next());
0511:
0512: rset = _stmt
0513: .executeQuery("select a from foo where true and false");
0514: assertFalse(rset.next());
0515:
0516: rset = _stmt
0517: .executeQuery("select a from foo where (true or false) and false");
0518: assertFalse(rset.next());
0519:
0520: rset = _stmt
0521: .executeQuery("select a from foo where true or (false and false)");
0522: assertTrue(rset.next());
0523:
0524: rset = _stmt
0525: .executeQuery("select a from foo where true or false and false");
0526: assertTrue(rset.next());
0527:
0528: assertException("select true or false");
0529: assertException("select true and false");
0530: assertException("select 1=1");
0531: assertException("select 1>1");
0532: assertException("select 1<1");
0533: assertException("select 1<=1");
0534: assertException("select 1>=1");
0535: assertException("select 1!=1");
0536: assertException("select 1<>1");
0537: }
0538:
0539: // ISSUE #14
0540: // See http://axion.tigris.org/issues/show_bug.cgi?id=14
0541: public void test_Issue14_ExpressionParsing_4() throws Exception {
0542: _stmt.execute("create table foo ( a int, b int, c int )");
0543: _stmt.executeUpdate("insert into foo values ( 3, 1, 2 )");
0544:
0545: ResultSet rset = _stmt
0546: .executeQuery("select a from foo where 1 + 1 = 2");
0547: assertTrue(rset.next());
0548:
0549: rset = _stmt.executeQuery("select a from foo where 2 + 4 < 6");
0550: assertFalse(rset.next());
0551:
0552: rset = _stmt
0553: .executeQuery("select a from foo where 1 + 1 = 2 or 2 + 4 < 6");
0554: assertTrue(rset.next());
0555:
0556: rset = _stmt
0557: .executeQuery("select a from foo where 1 + 1 = 2 and 2 + 4 < 6");
0558: assertFalse(rset.next());
0559:
0560: rset = _stmt
0561: .executeQuery("select a from foo where 1 + 1 = 2 or (2 + 4 < 6 and 2 + 4 < 6)");
0562: assertTrue(rset.next());
0563:
0564: rset = _stmt
0565: .executeQuery("select a from foo where 1 + 1 = 2 or 2 + 4 < 6 and 2 + 4 < 6");
0566: assertTrue(rset.next());
0567:
0568: rset = _stmt
0569: .executeQuery("select a from foo where (1 + 1 = 2 or 2 + 4 < 6) and 2 + 4 < 6");
0570: assertFalse(rset.next());
0571: }
0572:
0573: // ISSUE #14
0574: // See http://axion.tigris.org/issues/show_bug.cgi?id=14
0575: public void test_Issue14_ExpressionParsing_5() throws Exception {
0576: _stmt.execute("create table foo ( a int, b int, c int )");
0577: _stmt.executeUpdate("insert into foo values ( 3, 1, 2 )");
0578:
0579: assertExceptionOnRead("select a from foo where 1 AND 2");
0580: assertExceptionOnRead("select a from foo where true AND 0");
0581: assertExceptionOnRead("select a from foo where true AND 0");
0582:
0583: // since the first part is true, we don't evaluate the second
0584: ResultSet rset = _stmt
0585: .executeQuery("select a from foo where 1+1 > 1 OR 0");
0586: assertTrue(rset.next());
0587:
0588: // since the first part is true, we do evaluate the second
0589: assertExceptionOnRead("select a from foo where 1+1 > 1 AND 0");
0590:
0591: // since the first part is false, we evaluate the second
0592: assertExceptionOnRead("select a from foo where 1+1 > 2 OR 0");
0593: }
0594:
0595: // ISSUE #14
0596: // See http://axion.tigris.org/issues/show_bug.cgi?id=14
0597: public void test_Issue14_ExpressionParsing_6() throws Exception {
0598: assertResult(3, "select 3");
0599: assertResult(-3, "select -3");
0600: assertResult(3.1f, "select 3.1");
0601: assertResult(-3.1f, "select -3.1");
0602: assertResult(3.14f, "select 3.14");
0603: assertResult(-3.14f, "select -3.14");
0604: }
0605:
0606: // ISSUE #15
0607: // See http://axion.tigris.org/issues/show_bug.cgi?id=15
0608: public void test_Issue15_InlineConcatOperator() throws Exception {
0609: assertResult("ab", "select 'a' || 'b'");
0610: assertResult("abc", "select 'a' || 'b' || 'c'");
0611: assertResult("abc", "select 'a'||'b'||'c'");
0612: assertResult("a||b||c", "select 'a||b' || '||c'");
0613: assertResult("a||b||c", "select 'a||b'||'||c'");
0614: assertResult("ab", "select ('a' || 'b')");
0615: assertResult("ab", "select ('a') || ('b')");
0616: assertResult("ab", "select 'a' || (('b'))");
0617: assertResult("abc", "select ('a' || 'b') || 'c'");
0618: assertResult("abc", "select 'a' || ('b' || 'c')");
0619: assertResult("abc", "select ('a' || ('b' || 'c'))");
0620: assertNullResult("select null || null");
0621: assertNullResult("select 'a' || null");
0622: assertNullResult("select null || 'a'");
0623: }
0624:
0625: // ISSUE #16
0626: // See http://axion.tigris.org/issues/show_bug.cgi?id=16
0627: public void test_Issue16_MinThrowsNPE() throws Exception {
0628: _stmt.execute("create table foo ( num int )");
0629: assertNullResult("select min(num) from foo");
0630: for (int i = 0; i < 10; i++) {
0631: assertEquals(1, _stmt
0632: .executeUpdate("insert into foo values ( " + i
0633: + ")"));
0634: }
0635: assertResult(0, "select min(num) from foo");
0636: assertEquals(1, _stmt
0637: .executeUpdate("insert into foo values ( null )"));
0638: assertResult(0, "select min(num) from foo");
0639: assertResult(0,
0640: "select min(num) from foo where num is not null");
0641: }
0642:
0643: // ISSUE #16
0644: // See http://axion.tigris.org/issues/show_bug.cgi?id=16
0645: public void test_Issue16_MaxThrowsNPE() throws Exception {
0646: _stmt.execute("create table foo ( num int )");
0647: assertNullResult("select max(num) from foo");
0648: for (int i = 0; i < 10; i++) {
0649: assertEquals(1, _stmt
0650: .executeUpdate("insert into foo values ( " + i
0651: + ")"));
0652: }
0653: assertResult(9, "select max(num) from foo");
0654: assertEquals(1, _stmt
0655: .executeUpdate("insert into foo values ( null )"));
0656: assertResult(9, "select max(num) from foo");
0657: assertResult(9,
0658: "select max(num) from foo where num is not null");
0659: }
0660:
0661: // ISSUE #16
0662: // See http://axion.tigris.org/issues/show_bug.cgi?id=16
0663: public void test_Issue16_SumThrowsNPE() throws Exception {
0664: _stmt.execute("create table foo ( num int )");
0665: assertNullResult("select sum(num) from foo");
0666: int sum = 0;
0667: for (int i = 0; i < 10; i++) {
0668: sum += i;
0669: assertEquals(1, _stmt
0670: .executeUpdate("insert into foo values ( " + i
0671: + ")"));
0672: }
0673: assertResult(sum, "select sum(num) from foo");
0674: assertEquals(1, _stmt
0675: .executeUpdate("insert into foo values ( null )"));
0676: assertResult(sum, "select sum(num) from foo");
0677: assertResult(sum,
0678: "select sum(num) from foo where num is not null");
0679: }
0680:
0681: // ISSUE #17
0682: // See http://axion.tigris.org/issues/show_bug.cgi?id=17
0683: public void test_Issue17_FloatingPointScale() throws Exception {
0684: {
0685: ResultSet rset = _stmt.executeQuery("select (1.5 + 1.0)");
0686: assertTrue(rset.next());
0687: assertEquals(2.5f, rset.getFloat(1), 0.0f);
0688: }
0689: {
0690: ResultSet rset = _stmt
0691: .executeQuery("select (1.53333 + 1.0)");
0692: assertTrue(rset.next());
0693: assertEquals(2.53333f, rset.getFloat(1), 0.0f);
0694: }
0695: {
0696: ResultSet rset = _stmt.executeQuery("select (1.5 + 1.00)");
0697: assertTrue(rset.next());
0698: assertEquals(2.5f, rset.getFloat(1), 0.0f);
0699: }
0700: {
0701: ResultSet rset = _stmt
0702: .executeQuery("select (1.50009 + 0.000000000001)");
0703: assertTrue(rset.next());
0704: BigDecimal expected = new BigDecimal("1.500090000001");
0705: BigDecimal found = rset.getBigDecimal(1);
0706: assertTrue("Expected " + expected + ", found " + found,
0707: 0 == expected.compareTo(found));
0708: }
0709: {
0710: ResultSet rset = _stmt
0711: .executeQuery("select (1.50009 + 1.000000000001)");
0712: assertTrue(rset.next());
0713: BigDecimal expected = new BigDecimal("2.500090000001");
0714: BigDecimal found = rset.getBigDecimal(1);
0715: assertTrue("Expected " + expected + ", found " + found,
0716: 0 == expected.compareTo(found));
0717: }
0718: }
0719:
0720: // ISSUE #18
0721: // See http://axion.tigris.org/issues/show_bug.cgi?id=18
0722: public void test_Issue18() throws Exception {
0723: _stmt.execute("create table employee ( id int, salary int)");
0724: assertEquals(
0725: 1,
0726: _stmt
0727: .executeUpdate("insert into employee values ( 1, 200000 )"));
0728: assertEquals(
0729: 1,
0730: _stmt
0731: .executeUpdate("insert into employee values ( 2, 100000 )"));
0732:
0733: {
0734: ResultSet rset = _stmt
0735: .executeQuery("select max(salary/4) from employee");
0736: assertTrue(rset.next());
0737: assertEquals(200000 / 4, rset.getInt(1));
0738: assertFalse(rset.next());
0739: rset.close();
0740: }
0741: {
0742: ResultSet rset = _stmt
0743: .executeQuery("select max(salary)/4 from employee");
0744: assertTrue(rset.next());
0745: assertEquals(200000 / 4, rset.getInt(1));
0746: assertFalse(rset.next());
0747: rset.close();
0748: }
0749:
0750: {
0751: ResultSet rset = _stmt
0752: .executeQuery("select 400000/max(salary/2) from employee");
0753: assertTrue(rset.next());
0754: assertEquals(400000 / (200000 / 2), rset.getInt(1));
0755: assertFalse(rset.next());
0756: rset.close();
0757: }
0758:
0759: {
0760: ResultSet rset = _stmt.executeQuery("select 4/max(4/2)");
0761: assertTrue(rset.next());
0762: assertEquals(2, rset.getInt(1));
0763: assertFalse(rset.next());
0764: rset.close();
0765: }
0766:
0767: }
0768:
0769: // ISSUE #18
0770: // Group By should also allow scalar function in select which have literal
0771: // and column on which we are grouping is used.
0772: public void test_Issue18_1() throws Exception {
0773: _stmt
0774: .execute("create table employee ( id int, salary int, name varchar(60))");
0775: assertEquals(
0776: 1,
0777: _stmt
0778: .executeUpdate("insert into employee values ( 1, 10000, 'A' )"));
0779: assertEquals(
0780: 1,
0781: _stmt
0782: .executeUpdate("insert into employee values ( 2, 5000 , 'B' )"));
0783: assertEquals(
0784: 1,
0785: _stmt
0786: .executeUpdate("insert into employee values ( 1, 10000, 'A' )"));
0787: assertEquals(
0788: 1,
0789: _stmt
0790: .executeUpdate("insert into employee values ( 2, 5000, 'B' )"));
0791:
0792: {
0793: ResultSet rset = _stmt
0794: .executeQuery("select id, 'Mr. ' || name, sum(salary) from employee group by id, name order by id ");
0795: assertTrue(rset.next());
0796: assertEquals(1, rset.getInt(1));
0797: assertEquals("Mr. A", rset.getString(2));
0798: assertEquals(20000, rset.getInt(3));
0799: assertTrue(rset.next());
0800: assertEquals(2, rset.getInt(1));
0801: assertEquals("Mr. B", rset.getString(2));
0802: assertEquals(10000, rset.getInt(3));
0803: assertFalse(rset.next());
0804: rset.close();
0805: }
0806:
0807: {
0808: ResultSet rset = _stmt
0809: .executeQuery("select id, 'Mr. ' || name, sum(salary)+5000 from employee group by id, name order by id ");
0810: assertTrue(rset.next());
0811: assertEquals(1, rset.getInt(1));
0812: assertEquals("Mr. A", rset.getString(2));
0813: assertEquals(25000, rset.getInt(3));
0814: assertTrue(rset.next());
0815: assertEquals(2, rset.getInt(1));
0816: assertEquals("Mr. B", rset.getString(2));
0817: assertEquals(15000, rset.getInt(3));
0818: assertFalse(rset.next());
0819: rset.close();
0820: }
0821: }
0822:
0823: // ISSUE #21
0824: // See http://axion.tigris.org/issues/show_bug.cgi?id=21
0825: public void test_Issue21_OuterJoin() throws Exception {
0826: _stmt.execute("create table x ( id int, name varchar(10) )");
0827: _stmt.execute("create table y ( id int, name varchar(10) )");
0828: assertEquals(1, _stmt
0829: .executeUpdate("insert into x values ( 1, 'AAA' )"));
0830: assertEquals(1, _stmt
0831: .executeUpdate("insert into x values ( 2, 'BBB' )"));
0832:
0833: ResultSet rset = _stmt
0834: .executeQuery("select * from x left join y on x.id = y.id");
0835:
0836: assertTrue(rset.next());
0837: assertEquals(1, rset.getInt(1));
0838: assertEquals("AAA", rset.getString(2));
0839: assertEquals(0, rset.getInt(3));
0840: assertTrue(rset.wasNull());
0841: assertTrue(null == rset.getString(4));
0842: assertTrue(rset.wasNull());
0843:
0844: assertTrue(rset.next());
0845: assertEquals(2, rset.getInt(1));
0846: assertEquals("BBB", rset.getString(2));
0847: assertEquals(0, rset.getInt(3));
0848: assertTrue(rset.wasNull());
0849: assertTrue(null == rset.getString(4));
0850: assertTrue(rset.wasNull());
0851:
0852: assertFalse(rset.next());
0853:
0854: rset.close();
0855: }
0856:
0857: // ISSUE #23
0858: // See http://axion.tigris.org/issues/show_bug.cgi?id=23
0859: public void test_Issue23() throws Exception {
0860: _stmt
0861: .execute("create table emp(id int, name varchar(5), deptcd int, emptype int)");
0862: assertEquals(
0863: 1,
0864: _stmt
0865: .executeUpdate("insert into emp values(1, 'ahi', 1, 3)"));
0866: assertEquals(
0867: 1,
0868: _stmt
0869: .executeUpdate("insert into emp values(2, 'Jon', 2, 4)"));
0870:
0871: _stmt
0872: .execute("create table code(code int, codename varchar(8))");
0873: assertEquals(1, _stmt
0874: .executeUpdate("insert into code values(1, 'RAD')"));
0875: assertEquals(1, _stmt
0876: .executeUpdate("insert into code values(2, 'HR')"));
0877: assertEquals(1, _stmt
0878: .executeUpdate("insert into code values(3, 'Perm')"));
0879: assertEquals(
0880: 1,
0881: _stmt
0882: .executeUpdate("insert into code values(4, 'Contract')"));
0883:
0884: {
0885: ResultSet rset = _stmt
0886: .executeQuery("select emp.id, emp.name, t1.codename, t2.codename from emp, code t1, code t2 where emp.deptcd = t1.code and emp.emptype = t2.code");
0887:
0888: assertTrue(rset.next());
0889: assertEquals(1, rset.getInt(1));
0890: assertEquals("ahi", rset.getString(2));
0891: assertEquals("RAD", rset.getString(3));
0892: assertEquals("Perm", rset.getString(4));
0893:
0894: assertTrue(rset.next());
0895: assertEquals(2, rset.getInt(1));
0896: assertEquals("Jon", rset.getString(2));
0897: assertEquals("HR", rset.getString(3));
0898: assertEquals("Contract", rset.getString(4));
0899:
0900: assertFalse(rset.next());
0901: rset.close();
0902: }
0903:
0904: {
0905: ResultSet rset = _stmt
0906: .executeQuery("select emp.id, emp.name, t1.codename, t2.codename from emp inner join code t1 on deptcd = t1.code inner join code t2 on emp.emptype = t2.code");
0907:
0908: assertTrue(rset.next());
0909: assertEquals(1, rset.getInt(1));
0910: assertEquals("ahi", rset.getString(2));
0911: assertEquals("RAD", rset.getString(3));
0912: assertEquals("Perm", rset.getString(4));
0913:
0914: assertTrue(rset.next());
0915: assertEquals(2, rset.getInt(1));
0916: assertEquals("Jon", rset.getString(2));
0917: assertEquals("HR", rset.getString(3));
0918: assertEquals("Contract", rset.getString(4));
0919:
0920: assertFalse(rset.next());
0921: rset.close();
0922: }
0923: }
0924:
0925: // ISSUE #??
0926: // Flatfile insert-select (using join) returning incorrect rows: fixed-width test
0927: public void test_Issue_InsertSelectJoin_FW() throws Exception {
0928: final String ahi = "asatapathy@seebeyond.com";
0929: final String jon = "jgiron@seebeyond.com";
0930:
0931: final String compaq = "Compaq Presario 6100 Laptop Computer";
0932: final String flatscreen = "Dell Flatscreen Monitor A605";
0933: final String phone = "Samsung A600 Cellular Phone";
0934: final String latitude = "Dell Latitude D600 Latop Computer";
0935:
0936: File dataDir = new File("testdb");
0937: Connection fileConn = (AxionConnection) (DriverManager
0938: .getConnection("jdbc:axiondb:testdb:"
0939: + dataDir.getName()));
0940: Statement stmt = fileConn.createStatement();
0941:
0942: try {
0943: stmt.execute("drop table if exists ORDERS");
0944: stmt
0945: .execute("create external table ORDERS (ORDER_ID integer, EMAIL varchar(50)) Organization(loadtype='fixedwidth')");
0946: stmt.execute("drop table if exists ORDERDETAILS");
0947: stmt
0948: .execute("create external table ORDERDETAILS (ORDER_ID integer, ITEM varchar(100)) Organization(loadtype='fixedwidth')");
0949: stmt.execute("drop table if exists ORDER_TARGET");
0950: stmt
0951: .execute("create external table ORDER_TARGET (ORDER_ID integer, EMAIL varchar(50), ITEM varchar(100)) Organization(loadtype='fixedwidth')");
0952:
0953: stmt
0954: .execute("insert into ORDERS values (1, '" + ahi
0955: + "')");
0956: stmt
0957: .execute("insert into ORDERS values (2, '" + jon
0958: + "')");
0959:
0960: stmt.execute("insert into ORDERDETAILS values (1, '"
0961: + compaq + "')");
0962: stmt.execute("insert into ORDERDETAILS values (1, '"
0963: + flatscreen + "')");
0964: stmt.execute("insert into ORDERDETAILS values (2, '"
0965: + phone + "')");
0966: stmt.execute("insert into ORDERDETAILS values (2, '"
0967: + latitude + "')");
0968:
0969: assertEquals(
0970: 4,
0971: stmt
0972: .executeUpdate("insert into ORDER_TARGET select a.order_id, a.email, b.item from ORDERS a "
0973: + "inner join ORDERDETAILS b on (a.order_id = b.order_id)"));
0974:
0975: ResultSet rs = stmt
0976: .executeQuery("select distinct email from ORDER_TARGET where email = '"
0977: + ahi + "'");
0978: assertResult(ahi, rs);
0979:
0980: rs = stmt
0981: .executeQuery("select distinct email from ORDER_TARGET where email = '"
0982: + jon + "'");
0983: assertResult(jon, rs);
0984:
0985: rs = stmt
0986: .executeQuery("select count(*) from ORDER_TARGET where email = '"
0987: + ahi + "'");
0988: assertResult(2, rs);
0989:
0990: rs = stmt
0991: .executeQuery("select count(*) from ORDER_TARGET where email = '"
0992: + jon + "'");
0993: assertResult(2, rs);
0994:
0995: rs = stmt
0996: .executeQuery("select count(*) from ORDER_TARGET where item = '"
0997: + compaq + "'");
0998: assertResult(1, rs);
0999:
1000: rs = stmt
1001: .executeQuery("select count(*) from ORDER_TARGET where item = '"
1002: + flatscreen + "'");
1003: assertResult(1, rs);
1004:
1005: rs = stmt
1006: .executeQuery("select count(*) from ORDER_TARGET where item = '"
1007: + phone + "'");
1008: assertResult(1, rs);
1009:
1010: rs = stmt
1011: .executeQuery("select count(*) from ORDER_TARGET where item = '"
1012: + latitude + "'");
1013: assertResult(1, rs);
1014: } finally {
1015: if (stmt != null) {
1016: stmt.execute("shutdown");
1017: }
1018:
1019: if (fileConn != null) {
1020: fileConn.close();
1021: }
1022:
1023: deleteFile(dataDir);
1024: }
1025: }
1026:
1027: // ISSUE #??
1028: // Flatfile insert-select (using join) returning incorrect rows: delimited test
1029: public void test_Issue_InsertSelectJoin_Delim() throws Exception {
1030: final String ahi = "asatapathy@seebeyond.com";
1031: final String jon = "jgiron@seebeyond.com";
1032:
1033: final String compaq = "Compaq Presario 6100 Laptop Computer";
1034: final String flatscreen = "Dell Flatscreen Monitor A605";
1035: final String phone = "Samsung A600 Cellular Phone";
1036: final String latitude = "Dell Latitude D600 Latop Computer";
1037:
1038: File dataDir = new File("testdb");
1039: Connection fileConn = (AxionConnection) (DriverManager
1040: .getConnection("jdbc:axiondb:testdb:"
1041: + dataDir.getName()));
1042: Statement stmt = fileConn.createStatement();
1043: String eol = System.getProperty("line.separator");
1044:
1045: try {
1046: stmt.execute("drop table if exists ORDERS");
1047: stmt
1048: .execute("create external table ORDERS (ORDER_ID integer, EMAIL varchar(50)) Organization(loadtype='delimited' qualifier='\"' recorddelimiter='"
1049: + eol + "')");
1050: stmt.execute("drop table if exists ORDERDETAILS");
1051: stmt
1052: .execute("create external table ORDERDETAILS (ORDER_ID integer, ITEM varchar(100)) Organization(loadtype='delimited' recorddelimiter='"
1053: + eol + "')");
1054: stmt.execute("drop table if exists ORDER_TARGET");
1055: stmt
1056: .execute("create external table ORDER_TARGET (ORDER_ID integer, EMAIL varchar(50), ITEM varchar(100)) Organization(loadtype='delimited' recorddelimiter='"
1057: + eol + "')");
1058:
1059: stmt
1060: .execute("insert into ORDERS values (1, '" + ahi
1061: + "')");
1062: stmt
1063: .execute("insert into ORDERS values (2, '" + jon
1064: + "')");
1065:
1066: stmt.execute("insert into ORDERDETAILS values (1, '"
1067: + compaq + "')");
1068: stmt.execute("insert into ORDERDETAILS values (1, '"
1069: + flatscreen + "')");
1070: stmt.execute("insert into ORDERDETAILS values (2, '"
1071: + phone + "')");
1072: stmt.execute("insert into ORDERDETAILS values (2, '"
1073: + latitude + "')");
1074:
1075: assertEquals(
1076: 4,
1077: stmt
1078: .executeUpdate("insert into ORDER_TARGET select a.order_id, a.email, b.item from ORDERS a "
1079: + "inner join ORDERDETAILS b on (a.order_id = b.order_id)"));
1080:
1081: ResultSet rs = stmt
1082: .executeQuery("select distinct email from ORDER_TARGET where email = '"
1083: + ahi + "'");
1084: assertResult(ahi, rs);
1085:
1086: rs = stmt
1087: .executeQuery("select distinct email from ORDER_TARGET where email = '"
1088: + jon + "'");
1089: assertResult(jon, rs);
1090:
1091: rs = stmt
1092: .executeQuery("select count(*) from ORDER_TARGET where email = '"
1093: + ahi + "'");
1094: assertResult(2, rs);
1095:
1096: rs = stmt
1097: .executeQuery("select count(*) from ORDER_TARGET where email = '"
1098: + jon + "'");
1099: assertResult(2, rs);
1100:
1101: rs = stmt
1102: .executeQuery("select count(*) from ORDER_TARGET where item = '"
1103: + compaq + "'");
1104: assertResult(1, rs);
1105:
1106: rs = stmt
1107: .executeQuery("select count(*) from ORDER_TARGET where item = '"
1108: + flatscreen + "'");
1109: assertResult(1, rs);
1110:
1111: rs = stmt
1112: .executeQuery("select count(*) from ORDER_TARGET where item = '"
1113: + phone + "'");
1114: assertResult(1, rs);
1115:
1116: rs = stmt
1117: .executeQuery("select count(*) from ORDER_TARGET where item = '"
1118: + latitude + "'");
1119: assertResult(1, rs);
1120: } finally {
1121: if (stmt != null) {
1122: stmt.execute("shutdown");
1123: }
1124:
1125: if (fileConn != null) {
1126: fileConn.close();
1127: }
1128:
1129: deleteFile(dataDir);
1130: }
1131: }
1132:
1133: // ISSUE #27
1134: // See http://axion.tigris.org/issues/show_bug.cgi?id=27
1135: // Outer Join with where condition does not work
1136: public void test_Issue_OuterJoin_WithWhereCondition()
1137: throws Exception {
1138: _stmt.execute("create table x ( id int, name varchar(3) )");
1139: _stmt.execute("create table y ( id int, name varchar(3) )");
1140: assertEquals(1, _stmt
1141: .executeUpdate("insert into x values ( 1, 'AAA' )"));
1142: assertEquals(1, _stmt
1143: .executeUpdate("insert into x values ( 2, 'BBB' )"));
1144: assertEquals(1, _stmt
1145: .executeUpdate("insert into x values ( 3, 'CCC' )"));
1146:
1147: assertEquals(1, _stmt
1148: .executeUpdate("insert into y values ( 2, 'XXX' )"));
1149:
1150: ResultSet rset = _stmt
1151: .executeQuery("select * from x left outer join y on x.id = y.id where y.id is not null");
1152:
1153: assertTrue(rset.next());
1154: assertEquals(2, rset.getInt(1));
1155: assertEquals("BBB", rset.getString(2));
1156: assertEquals(2, rset.getInt(3));
1157: assertEquals("XXX", rset.getString(4));
1158: assertFalse(rset.next());
1159:
1160: rset = _stmt
1161: .executeQuery("select * from x left outer join y on x.id = y.id where y.id is null");
1162:
1163: assertTrue(rset.next());
1164: assertEquals(1, rset.getInt(1));
1165: assertEquals("AAA", rset.getString(2));
1166: assertEquals(0, rset.getInt(3));
1167: assertTrue(rset.wasNull());
1168: assertTrue(null == rset.getString(4));
1169: assertTrue(rset.wasNull());
1170:
1171: assertTrue(rset.next());
1172: assertEquals(3, rset.getInt(1));
1173: assertEquals("CCC", rset.getString(2));
1174: assertEquals(0, rset.getInt(3));
1175: assertTrue(rset.wasNull());
1176: assertTrue(null == rset.getString(4));
1177: assertTrue(rset.wasNull());
1178:
1179: assertFalse(rset.next());
1180:
1181: rset.close();
1182: }
1183:
1184: // [Ahi] To avoid out of Memory at this point we don't
1185: // allow the transaction go over 5000 rows and as soon as we commit
1186: // they are again available to RowIterator, since we are holding a live iterator.
1187: // This leads to an infinite loop. I have put a fix for this by checking
1188: // whether target is part of sub-query in that I just let it fall thru
1189: // instead of committing at every 5000 rows which is better than before.
1190:
1191: // But this will still fail with by running out of memory at some point.
1192: // To avoid OOM , user could choose to use limit option in the subquery.
1193: // e.g insert into PERSON select * from PERSON limit <n> offset <position>
1194:
1195: // ISSUE #28
1196: // See http://axion.tigris.org/issues/show_bug.cgi?id=28
1197: public void testInsertIntoSelectFromBug() throws Exception {
1198: _stmt.execute("create table PERSON ( NAME varchar(10) )");
1199:
1200: _stmt.execute("insert into PERSON values ( 'Paul' )");
1201: int expectedRows = 1;
1202: for (int i = 0; i < 15; i++) {
1203: _stmt.execute("insert into PERSON select * from PERSON");
1204: expectedRows *= 2;
1205: assertResult(expectedRows, "select count(*) from person");
1206: }
1207: }
1208:
1209: // ISSUE: ?? Problem with ResultSet.beforeFirst()
1210: // See http://axion.tigris.org/servlets/ReadMsg?list=users&msgNo=256
1211: public void test_Issue_ResultSet_BeforeFirst() throws Exception {
1212: _stmt.execute("create table x ( id int, name varchar(3) )");
1213: assertEquals(1, _stmt
1214: .executeUpdate("insert into x values ( 1, 'AAA' )"));
1215: assertEquals(1, _stmt
1216: .executeUpdate("insert into x values ( 2, 'BBB' )"));
1217: assertEquals(1, _stmt
1218: .executeUpdate("insert into x values ( 3, 'CCC' )"));
1219: ResultSet rset = _stmt.executeQuery("select * from x");
1220:
1221: // recalculate the size of the current query
1222: rset.beforeFirst();
1223: int size = 0;
1224: while (rset.next()) {
1225: size++;
1226: }
1227: assertEquals(3, size);
1228:
1229: // do it again
1230: size = 0;
1231: rset.beforeFirst();
1232: while (rset.next()) {
1233: size++;
1234: }
1235: assertEquals(3, size);
1236: }
1237:
1238: // ISSUE: 29 join condition results in ClassCastException
1239: // See http://axion.tigris.org/issues/show_bug.cgi?id=29
1240: public void test_Issue29_Outer_Non_Key_Join() throws Exception {
1241: _stmt.execute("create table a (id int, fname varchar(20));");
1242: _stmt.execute("create table b (id int, lname varchar(20));");
1243: assertEquals(1, _stmt
1244: .executeUpdate("insert into a values (1, 'Jon');"));
1245: assertEquals(1, _stmt
1246: .executeUpdate("insert into a values (2, 'Arnold');"));
1247: assertEquals(1, _stmt
1248: .executeUpdate("insert into b values (1, 'Giron');"));
1249: assertEquals(
1250: 1,
1251: _stmt
1252: .executeUpdate("insert into b values (2, 'Schwarzenegger');"));
1253: ResultSet rset = _stmt
1254: .executeQuery("select a.id, a.fname, b.lname from a inner join b on (a.id = 2);");
1255:
1256: assertTrue(rset.next());
1257: assertEquals(2, rset.getInt(1));
1258: assertEquals("Arnold", rset.getString(2));
1259: assertEquals("Giron", rset.getString(3));
1260:
1261: assertTrue(rset.next());
1262: assertEquals(2, rset.getInt(1));
1263: assertEquals("Arnold", rset.getString(2));
1264: assertEquals("Schwarzenegger", rset.getString(3));
1265:
1266: assertFalse(rset.next());
1267:
1268: rset.close();
1269: }
1270:
1271: // ISSUE: 31
1272: // See http://axion.tigris.org/issues/show_bug.cgi?id=31
1273: public void test_Issue31_LikeEmptyString() throws Exception {
1274: _stmt.execute("create table foo (id int, val varchar(10))");
1275: _stmt.execute("insert into foo values (0, null)");
1276: _stmt.execute("insert into foo values (1, '')");
1277: _stmt.execute("insert into foo values (2, 'test')");
1278: ResultSet rset = _stmt
1279: .executeQuery("select id, val from foo where val like ''");
1280:
1281: assertTrue(rset.next());
1282: assertEquals(1, rset.getInt(1));
1283: assertEquals("", rset.getString(2));
1284:
1285: assertFalse(rset.next());
1286: rset.close();
1287:
1288: // Null like clause should evaluate to null per ISO/ANSI SQL standard, which should
1289: // result in an empty ResultSet.
1290: rset = _stmt
1291: .executeQuery("select id, val from foo where val like null");
1292: assertFalse(rset.next());
1293:
1294: rset.close();
1295: }
1296:
1297: // ISSUE: 35
1298: // See http://axion.tigris.org/issues/show_bug.cgi?id=35
1299: public void test_Issue35_LeftAndRightOuterJoin() throws Exception {
1300: _stmt
1301: .execute("create table store_info (store_name varchar(50), sales int);");
1302: _stmt
1303: .execute("create table geography (region_name varchar(50), store_name varchar(50));");
1304: _stmt
1305: .execute("insert into store_info values ('Los Angeles', 1500);");
1306: _stmt
1307: .execute("insert into store_info values ('San Diego', 250);");
1308: _stmt
1309: .execute("insert into store_info values ('Los Angeles', 300);");
1310: _stmt.execute("insert into store_info values ('Boston', 700);");
1311: _stmt
1312: .execute("insert into geography values ('East', 'Boston' );");
1313: _stmt
1314: .execute("insert into geography values ('East', 'New York' );");
1315: _stmt
1316: .execute("insert into geography values ('West', 'Los Angeles' );");
1317: _stmt
1318: .execute("insert into geography values ('West', 'San Diego' );");
1319:
1320: //left outer join test
1321: _rset = _stmt
1322: .executeQuery("select * from geography a left join store_info b on (a.store_name = b.store_name and a.store_name = 'Boston')");
1323: assertNotNull(_rset);
1324:
1325: assertTrue(_rset.next());
1326: assertEquals(_rset.getString(1), "East");
1327: assertEquals(_rset.getString(2), "Boston");
1328: assertEquals(_rset.getString(3), "Boston");
1329: assertEquals(_rset.getInt(4), 700);
1330:
1331: assertTrue(_rset.next());
1332: assertEquals(_rset.getString(1), "East");
1333: assertEquals(_rset.getString(2), "New York");
1334: assertEquals(_rset.getString(3), null);
1335: assertEquals(_rset.getObject(4), null);
1336:
1337: assertTrue(_rset.next());
1338: assertEquals(_rset.getString(1), "West");
1339: assertEquals(_rset.getString(2), "Los Angeles");
1340: assertEquals(_rset.getString(3), null);
1341: assertEquals(_rset.getObject(4), null);
1342:
1343: assertTrue(_rset.next());
1344: assertEquals(_rset.getString(1), "West");
1345: assertEquals(_rset.getString(2), "San Diego");
1346: assertEquals(_rset.getString(3), null);
1347: assertEquals(_rset.getObject(4), null);
1348:
1349: assertTrue(!_rset.next());
1350:
1351: //right outer join test
1352: _rset = _stmt
1353: .executeQuery("select * from geography a right join store_info b on (a.store_name = b.store_name and a.store_name = 'Boston')");
1354: assertNotNull(_rset);
1355:
1356: assertTrue(_rset.next());
1357: assertEquals(_rset.getString(1), null);
1358: assertEquals(_rset.getString(2), null);
1359: assertEquals(_rset.getString(3), "Los Angeles");
1360: assertEquals(_rset.getInt(4), 1500);
1361:
1362: assertTrue(_rset.next());
1363: assertEquals(_rset.getString(1), null);
1364: assertEquals(_rset.getString(2), null);
1365: assertEquals(_rset.getString(3), "San Diego");
1366: assertEquals(_rset.getInt(4), 250);
1367:
1368: assertTrue(_rset.next());
1369: assertEquals(_rset.getString(1), null);
1370: assertEquals(_rset.getString(2), null);
1371: assertEquals(_rset.getString(3), "Los Angeles");
1372: assertEquals(_rset.getInt(4), 300);
1373:
1374: assertTrue(_rset.next());
1375: assertEquals(_rset.getString(1), "East");
1376: assertEquals(_rset.getString(2), "Boston");
1377: assertEquals(_rset.getString(3), "Boston");
1378: assertEquals(_rset.getInt(4), 700);
1379:
1380: assertTrue(!_rset.next());
1381: }
1382:
1383: public void test_IssueXX_JoinTableWithSelf() throws Exception {
1384: _stmt
1385: .execute("create table tree ( id number, label varchar(20), parent_id number )");
1386: _stmt.execute("insert into tree values ( 1, 'root', null )");
1387: _stmt.execute("insert into tree values ( 2, 'child', 1 )");
1388: _stmt
1389: .execute("insert into tree values ( 3, 'grand-child', 2 )");
1390: assertResult(
1391: new Object[] { new Integer(1), "root" },
1392: "select parent.id, parent.label from tree parent, tree child where child.id = 2 and parent.id = child.parent_id");
1393: }
1394:
1395: // ISSUE: 36 if we have index on the group by column it always skip one row
1396: // See http://axion.tigris.org/issues/show_bug.cgi?id=36
1397: public void test_Issue36_UpdateSyntaxIssue() throws Exception {
1398: _stmt
1399: .execute("CREATE TABLE address (address_id NUMBER NOT NULL,"
1400: + "address_1 VARCHAR2(10) NOT NULL, city VARCHAR2(10) NOT NULL, state VARCHAR2(2) NOT NULL)");
1401: _stmt
1402: .execute("insert into address (address_id, address_1, city, state) values (1,'SOMESTREET','CITY','ST')");
1403:
1404: try {
1405: _conn.prepareStatement("update address set city=?state=?");
1406: fail("Expected SQL Exception, missing comma in update");
1407: } catch (SQLException e) {
1408: //expected
1409: }
1410:
1411: try {
1412: _stmt.execute("update address set city='second'state='22'");
1413: fail("Expected SQL Exception, missing comma in update");
1414: } catch (SQLException e) {
1415: //expected
1416: }
1417: }
1418:
1419: // ISSUE: 37 if we have index on the group by column it always skip one row
1420: // See http://axion.tigris.org/issues/show_bug.cgi?id=37
1421: public void test_Issue37_AggregateWithBtreeIndexCol()
1422: throws Exception {
1423: _stmt
1424: .execute("create table x ( xid varchar(5), id int, name varchar(10) )");
1425: _stmt.execute("insert into x values ( '1Amy', 1, 'Amy' )");
1426: _stmt.execute("insert into x values ( '2Mike', 2, 'Mike' )");
1427: _stmt.execute("create btree index xid_index on x(xid)");
1428:
1429: ResultSet rset = _stmt
1430: .executeQuery("select xid, max(id) from x group by xid");
1431: assertNotNull(rset);
1432: assertTrue(rset.next());
1433: assertEquals("1Amy", rset.getString(1));
1434: assertEquals(1, rset.getInt(2));
1435:
1436: assertTrue(rset.next());
1437: assertEquals("2Mike", rset.getString(1));
1438: assertEquals(2, rset.getInt(2));
1439:
1440: assertTrue(!rset.next());
1441: }
1442:
1443: // ISSUE: 38 if we have index on the group by column it always skip one row
1444: // See http://axion.tigris.org/issues/show_bug.cgi?id=38
1445: public void test_Issue38_MixedCrossProductJoinWithAnsiJoin()
1446: throws Exception {
1447: _stmt
1448: .execute("create table PEOPLE (NAME VARCHAR2(15), ID VARCHAR2(4))");
1449: _stmt
1450: .execute("insert into PEOPLE VALUES ('Clark Kent', '0003')");
1451: _stmt
1452: .execute("create table SUPERHEROES (NAME VARCHAR2(15), ID VARCHAR2(4))");
1453: _stmt
1454: .execute("insert into SUPERHEROES VALUES ('Superman', '0003')");
1455: _stmt
1456: .execute("insert into SUPERHEROES VALUES ('Spiderman', '0004')");
1457: _stmt
1458: .execute("create table ADDRESS (TOWN VARCHAR2(15), ID VARCHAR2(4))");
1459: _stmt
1460: .execute("insert into ADDRESS VALUES ('Metropolis', '0003')");
1461: _stmt
1462: .execute("insert into ADDRESS VALUES ('New York', '0004')");
1463:
1464: String sql = "select * from ADDRESS A, SUPERHEROES S LEFT OUTER JOIN PEOPLE P ON P.ID=S.ID where S.ID=A.ID and A.TOWN='New York'";
1465: ResultSet rset = _stmt.executeQuery(sql);
1466:
1467: assertNotNull(rset);
1468: assertTrue(rset.next());
1469: assertEquals("New York", rset.getString(1));
1470: assertEquals("0004", rset.getString(2));
1471: assertEquals("Spiderman", rset.getString(3));
1472: assertEquals("0004", rset.getString(4));
1473: assertEquals(null, rset.getString(5));
1474: assertEquals(null, rset.getString(6));
1475:
1476: assertTrue(!rset.next());
1477:
1478: sql = "select * from SUPERHEROES S LEFT OUTER JOIN PEOPLE P ON P.ID=S.ID, ADDRESS A where S.ID=A.ID and A.TOWN='New York'";
1479: rset = _stmt.executeQuery(sql);
1480:
1481: assertNotNull(rset);
1482: assertTrue(rset.next());
1483: assertEquals("Spiderman", rset.getString(1));
1484: assertEquals("0004", rset.getString(2));
1485: assertEquals(null, rset.getString(3));
1486: assertEquals(null, rset.getString(4));
1487: assertEquals("New York", rset.getString(5));
1488: assertEquals("0004", rset.getString(6));
1489:
1490: assertTrue(!rset.next());
1491:
1492: }
1493:
1494: // ISSUE: 39 Problem with OUTER JOIN and SUBSELECT
1495: // See http://axion.tigris.org/issues/show_bug.cgi?id=38
1496: // See http://axion.tigris.org/servlets/ReadMsg?list=users&msgNo=356
1497: public void test_Issue39_OuterJoinSubSelectBug() throws Exception {
1498: _stmt
1499: .execute("create table answer (id number, testsessionid number, answerscalevalue number, itemno number)");
1500: _stmt.execute("create table testsession (id number)");
1501: _stmt
1502: .execute("insert into answer (id, testsessionid, answerscalevalue, itemno) values (1,1,5,3)");
1503: _stmt.execute("insert into testsession values (1)");
1504:
1505: String query = "SELECT testsession.id, i3.answerscalevalue FROM testsession "
1506: + "left outer join (select id, testsessionid, answerscalevalue, itemno "
1507: + "from Answer where itemno=1) i3 on TestSession.id = i3.testSessionId";
1508: ResultSet rset = _stmt.executeQuery(query);
1509: assertTrue(rset.next());
1510: assertEquals(1, rset.getInt(1));
1511: assertFalse(rset.next());
1512: rset.close();
1513:
1514: query = "SELECT testsession.id, i3.answerscalevalue FROM (select id, testsessionid, answerscalevalue, itemno "
1515: + "from Answer where itemno=1) i3 right outer join testsession on TestSession.id = i3.testSessionId";
1516: rset = _stmt.executeQuery(query);
1517: assertTrue(rset.next());
1518: assertEquals(1, rset.getInt(1));
1519: assertFalse(rset.next());
1520: rset.close();
1521:
1522: _stmt.execute("create table table3 (id number)");
1523:
1524: query = "SELECT testsession.id, i3.answerscalevalue FROM testsession "
1525: + "left outer join (select id, testsessionid, answerscalevalue, itemno "
1526: + "from Answer where itemno=1) i3 on TestSession.id = i3.testSessionId left outer join table3 t3 on t3.id = i3.id";
1527: rset = _stmt.executeQuery(query);
1528: assertTrue(rset.next());
1529: assertEquals(1, rset.getInt(1));
1530: assertFalse(rset.next());
1531: rset.close();
1532:
1533: query = "SELECT testsession.id, i3.answerscalevalue FROM "
1534: + "table3 t3 right outer join (select id, testsessionid, answerscalevalue, itemno "
1535: + "from Answer where itemno=1) i3 right outer join testsession "
1536: + "on TestSession.id = i3.testSessionId on t3.id = testsession.id ";
1537: rset = _stmt.executeQuery(query);
1538: assertTrue(rset.next());
1539: assertEquals(1, rset.getInt(1));
1540: assertFalse(rset.next());
1541: rset.close();
1542: }
1543:
1544: // ISSUE: 40 Axion allows two columns with the same name
1545: // See http://axion.tigris.org/issues/show_bug.cgi?id=40
1546: public void test_Issue40_AlterTableAddDuplicateColumn()
1547: throws Exception {
1548: _stmt.execute("create table x(y varchar)");
1549: try {
1550: _stmt.execute("alter table x add y varchar");
1551: fail("Expected SQL Exception, column already exists");
1552: } catch (SQLException e) {
1553: //expected
1554: }
1555: }
1556:
1557: // ISSUE: 41 Axion allows two columns with the same name
1558: // See http://axion.tigris.org/issues/show_bug.cgi?id=41
1559: public void test_Issue41_AggregateFunctionWithSubSelect()
1560: throws Exception {
1561: _stmt.execute("create table x (x varchar)");
1562: _stmt.execute("insert into x values ('A')");
1563: _stmt.execute("insert into x values ('B')");
1564: _stmt.execute("select count(*) from x");
1565: _stmt.execute("select 2 / (select count(*) from x) from x");
1566: _stmt.execute("select count(*) / 2 from x");
1567: _stmt
1568: .execute("select count(*) / (select count(*) from x) from x");
1569: }
1570:
1571: }
|