Examples with AssertThrows org.h2.test.utils.AssertThrows used on opensource projects

Example 46 with AssertThrows

use of org.h2.test.utils.AssertThrows in project h2database by h2database.

the class TestRowLocks method testCases.

private void testCases() throws Exception {
    deleteDb(getTestName());
    c1 = getConnection(getTestName() + ";MVCC=TRUE");
    s1 = c1.createStatement();
    s1.execute("SET LOCK_TIMEOUT 10000");
    s1.execute("CREATE TABLE TEST AS " + "SELECT X ID, 'Hello' NAME FROM SYSTEM_RANGE(1, 3)");
    c1.commit();
    c1.setAutoCommit(false);
    s1.execute("UPDATE TEST SET NAME='Hallo' WHERE ID=1");
    c2 = getConnection(getTestName());
    c2.setAutoCommit(false);
    s2 = c2.createStatement();
    assertEquals("Hallo", getSingleValue(s1, "SELECT NAME FROM TEST WHERE ID=1"));
    assertEquals("Hello", getSingleValue(s2, "SELECT NAME FROM TEST WHERE ID=1"));
    s2.execute("UPDATE TEST SET NAME='Hallo' WHERE ID=2");
    assertThrows(ErrorCode.LOCK_TIMEOUT_1, s2).executeUpdate("UPDATE TEST SET NAME='Hi' WHERE ID=1");
    c1.commit();
    c2.commit();
    assertEquals("Hallo", getSingleValue(s1, "SELECT NAME FROM TEST WHERE ID=1"));
    assertEquals("Hallo", getSingleValue(s2, "SELECT NAME FROM TEST WHERE ID=1"));
    s2.execute("UPDATE TEST SET NAME='H1' WHERE ID=1");
    Task task = new Task() {

        @Override
        public void call() throws SQLException {
            s1.execute("UPDATE TEST SET NAME='H2' WHERE ID=1");
        }
    };
    task.execute();
    Thread.sleep(100);
    c2.commit();
    task.get();
    c1.commit();
    assertEquals("H2", getSingleValue(s1, "SELECT NAME FROM TEST WHERE ID=1"));
    assertEquals("H2", getSingleValue(s2, "SELECT NAME FROM TEST WHERE ID=1"));
    c1.close();
    c2.close();
}

Example 47 with AssertThrows

use of org.h2.test.utils.AssertThrows in project h2database by h2database.

the class TestFunctions method testNvl2.

private void testNvl2() throws SQLException {
    Connection conn = getConnection("functions");
    Statement stat = conn.createStatement();
    String createSQL = "CREATE TABLE testNvl2(id BIGINT, txt1 " + "varchar, txt2 varchar, num number(9, 0));";
    stat.execute(createSQL);
    stat.execute("insert into testNvl2(id, txt1, txt2, num) " + "values(1, 'test1', 'test2', null)");
    stat.execute("insert into testNvl2(id, txt1, txt2, num) " + "values(2, null, 'test4', null)");
    stat.execute("insert into testNvl2(id, txt1, txt2, num) " + "values(3, 'test5', null, null)");
    stat.execute("insert into testNvl2(id, txt1, txt2, num) " + "values(4, null, null, null)");
    stat.execute("insert into testNvl2(id, txt1, txt2, num) " + "values(5, '2', null, 1)");
    stat.execute("insert into testNvl2(id, txt1, txt2, num) " + "values(6, '2', null, null)");
    stat.execute("insert into testNvl2(id, txt1, txt2, num) " + "values(7, 'test2', null, null)");
    String query = "SELECT NVL2(txt1, txt1, txt2), txt1 " + "FROM testNvl2 order by id asc";
    ResultSet rs = stat.executeQuery(query);
    rs.next();
    String actual = rs.getString(1);
    assertEquals("test1", actual);
    rs.next();
    actual = rs.getString(1);
    assertEquals("test4", actual);
    rs.next();
    actual = rs.getString(1);
    assertEquals("test5", actual);
    rs.next();
    actual = rs.getString(1);
    assertEquals(null, actual);
    assertEquals(rs.getMetaData().getColumnType(2), rs.getMetaData().getColumnType(1));
    rs.close();
    rs = stat.executeQuery("SELECT NVL2(num, num, txt1), num " + "FROM testNvl2 where id in(5, 6) order by id asc");
    rs.next();
    assertEquals(rs.getMetaData().getColumnType(2), rs.getMetaData().getColumnType(1));
    assertThrows(ErrorCode.DATA_CONVERSION_ERROR_1, stat).executeQuery("SELECT NVL2(num, num, txt1), num " + "FROM testNvl2 where id = 7 order by id asc");
    // nvl2 should return expr2's datatype, if expr2 is character data.
    rs = stat.executeQuery("SELECT NVL2(1, 'test', 123), 'test' FROM dual");
    rs.next();
    actual = rs.getString(1);
    assertEquals("test", actual);
    assertEquals(rs.getMetaData().getColumnType(2), rs.getMetaData().getColumnType(1));
    conn.close();
}

Example 48 with AssertThrows

use of org.h2.test.utils.AssertThrows in project h2database by h2database.

the class TestFunctions method testTruncate.

private void testTruncate() throws SQLException {
    deleteDb("functions");
    Connection conn = getConnection("functions");
    Statement stat = conn.createStatement();
    ResultSet rs = stat.executeQuery("SELECT TRUNCATE(1.234, 2) FROM dual");
    rs.next();
    assertEquals(1.23d, rs.getDouble(1));
    rs = stat.executeQuery("SELECT CURRENT_TIMESTAMP(), " + "TRUNCATE(CURRENT_TIMESTAMP()) FROM dual");
    rs.next();
    Calendar c = DateTimeUtils.createGregorianCalendar();
    c.setTime(rs.getTimestamp(1));
    c.set(Calendar.HOUR_OF_DAY, 0);
    c.set(Calendar.MINUTE, 0);
    c.set(Calendar.SECOND, 0);
    c.set(Calendar.MILLISECOND, 0);
    java.util.Date nowDate = c.getTime();
    assertEquals(nowDate, rs.getTimestamp(2));
    assertThrows(SQLException.class, stat).executeQuery("SELECT TRUNCATE('bad', 1) FROM dual");
    // check for passing wrong data type
    rs = assertThrows(SQLException.class, stat).executeQuery("SELECT TRUNCATE('bad') FROM dual");
    // check for too many parameters
    rs = assertThrows(SQLException.class, stat).executeQuery("SELECT TRUNCATE(1,2,3) FROM dual");
    conn.close();
}

Example 49 with AssertThrows

use of org.h2.test.utils.AssertThrows in project h2database by h2database.

the class TestFunctions method testGenerateSeries.

private void testGenerateSeries() throws SQLException {
    Connection conn = getConnection("functions");
    Statement stat = conn.createStatement();
    ResultSet rs = stat.executeQuery("select * from system_range(1,3)");
    rs.next();
    assertEquals(1, rs.getInt(1));
    rs.next();
    assertEquals(2, rs.getInt(1));
    rs.next();
    assertEquals(3, rs.getInt(1));
    rs = stat.executeQuery("select * from system_range(2,2)");
    assertTrue(rs.next());
    assertEquals(2, rs.getInt(1));
    rs = stat.executeQuery("select * from system_range(2,1)");
    assertFalse(rs.next());
    rs = stat.executeQuery("select * from system_range(1,2,-1)");
    assertFalse(rs.next());
    assertThrows(ErrorCode.STEP_SIZE_MUST_NOT_BE_ZERO, stat).executeQuery("select * from system_range(1,2,0)");
    rs = stat.executeQuery("select * from system_range(2,1,-1)");
    assertTrue(rs.next());
    assertEquals(2, rs.getInt(1));
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    rs = stat.executeQuery("select * from system_range(1,5,2)");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertTrue(rs.next());
    assertEquals(3, rs.getInt(1));
    assertTrue(rs.next());
    assertEquals(5, rs.getInt(1));
    rs = stat.executeQuery("select * from system_range(1,6,2)");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertTrue(rs.next());
    assertEquals(3, rs.getInt(1));
    assertTrue(rs.next());
    assertEquals(5, rs.getInt(1));
    conn.close();
}

Example 50 with AssertThrows

use of org.h2.test.utils.AssertThrows in project h2database by h2database.

the class TestFunctions method testToCharFromDateTime.

private void testToCharFromDateTime() throws SQLException {
    deleteDb("functions");
    Connection conn = getConnection("functions");
    Statement stat = conn.createStatement();
    TimeZone tz = TimeZone.getDefault();
    final Timestamp timestamp1979 = Timestamp.valueOf("1979-11-12 08:12:34.560");
    boolean daylight = tz.inDaylightTime(timestamp1979);
    String tzShortName = tz.getDisplayName(daylight, TimeZone.SHORT);
    String tzLongName = tz.getID();
    stat.executeUpdate("CREATE TABLE T (X TIMESTAMP(6))");
    stat.executeUpdate("INSERT INTO T VALUES " + "(TIMESTAMP '" + timestamp1979.toString() + "')");
    stat.executeUpdate("CREATE TABLE U (X TIMESTAMP(6))");
    stat.executeUpdate("INSERT INTO U VALUES " + "(TIMESTAMP '-100-01-15 14:04:02.120')");
    assertResult("1979-11-12 08:12:34.56", stat, "SELECT X FROM T");
    assertResult("-100-01-15 14:04:02.12", stat, "SELECT X FROM U");
    String expected = String.format("%tb", timestamp1979).toUpperCase();
    expected = stripTrailingPeriod(expected);
    assertResult("12-" + expected + "-79 08.12.34.560000000 AM", stat, "SELECT TO_CHAR(X) FROM T");
    assertResult("- / , . ; : text - /", stat, "SELECT TO_CHAR(X, '- / , . ; : \"text\" - /') FROM T");
    assertResult("1979-11-12", stat, "SELECT TO_CHAR(X, 'YYYY-MM-DD') FROM T");
    assertResult("1979/11/12", stat, "SELECT TO_CHAR(X, 'YYYY/MM/DD') FROM T");
    assertResult("1979,11,12", stat, "SELECT TO_CHAR(X, 'YYYY,MM,DD') FROM T");
    assertResult("1979.11.12", stat, "SELECT TO_CHAR(X, 'YYYY.MM.DD') FROM T");
    assertResult("1979;11;12", stat, "SELECT TO_CHAR(X, 'YYYY;MM;DD') FROM T");
    assertResult("1979:11:12", stat, "SELECT TO_CHAR(X, 'YYYY:MM:DD') FROM T");
    assertResult("year 1979!", stat, "SELECT TO_CHAR(X, '\"year \"YYYY\"!\"') FROM T");
    assertResult("1979 AD", stat, "SELECT TO_CHAR(X, 'YYYY AD') FROM T");
    assertResult("1979 A.D.", stat, "SELECT TO_CHAR(X, 'YYYY A.D.') FROM T");
    assertResult("0100 B.C.", stat, "SELECT TO_CHAR(X, 'YYYY A.D.') FROM U");
    assertResult("1979 AD", stat, "SELECT TO_CHAR(X, 'YYYY BC') FROM T");
    assertResult("100 BC", stat, "SELECT TO_CHAR(X, 'YYY BC') FROM U");
    assertResult("00 BC", stat, "SELECT TO_CHAR(X, 'YY BC') FROM U");
    assertResult("0 BC", stat, "SELECT TO_CHAR(X, 'Y BC') FROM U");
    assertResult("1979 A.D.", stat, "SELECT TO_CHAR(X, 'YYYY B.C.') FROM T");
    assertResult("2013", stat, "SELECT TO_CHAR(DATE '2013-12-30', 'YYYY') FROM DUAL");
    assertResult("013", stat, "SELECT TO_CHAR(DATE '2013-12-30', 'YYY') FROM DUAL");
    assertResult("13", stat, "SELECT TO_CHAR(DATE '2013-12-30', 'YY') FROM DUAL");
    assertResult("3", stat, "SELECT TO_CHAR(DATE '2013-12-30', 'Y') FROM DUAL");
    // ISO week year
    assertResult("2014", stat, "SELECT TO_CHAR(DATE '2013-12-30', 'IYYY') FROM DUAL");
    assertResult("014", stat, "SELECT TO_CHAR(DATE '2013-12-30', 'IYY') FROM DUAL");
    assertResult("14", stat, "SELECT TO_CHAR(DATE '2013-12-30', 'IY') FROM DUAL");
    assertResult("4", stat, "SELECT TO_CHAR(DATE '2013-12-30', 'I') FROM DUAL");
    assertResult("0001", stat, "SELECT TO_CHAR(DATE '-0001-01-01', 'IYYY') FROM DUAL");
    assertResult("0005", stat, "SELECT TO_CHAR(DATE '-0004-01-01', 'IYYY') FROM DUAL");
    assertResult("08:12 AM", stat, "SELECT TO_CHAR(X, 'HH:MI AM') FROM T");
    assertResult("08:12 A.M.", stat, "SELECT TO_CHAR(X, 'HH:MI A.M.') FROM T");
    assertResult("02:04 P.M.", stat, "SELECT TO_CHAR(X, 'HH:MI A.M.') FROM U");
    assertResult("08:12 AM", stat, "SELECT TO_CHAR(X, 'HH:MI PM') FROM T");
    assertResult("02:04 PM", stat, "SELECT TO_CHAR(X, 'HH:MI PM') FROM U");
    assertResult("08:12 A.M.", stat, "SELECT TO_CHAR(X, 'HH:MI P.M.') FROM T");
    assertResult("12 PM", stat, "SELECT TO_CHAR(TIME '12:00:00', 'HH AM')");
    assertResult("12 AM", stat, "SELECT TO_CHAR(TIME '00:00:00', 'HH AM')");
    assertResult("A.M.", stat, "SELECT TO_CHAR(X, 'P.M.') FROM T");
    assertResult("a.m.", stat, "SELECT TO_CHAR(X, 'p.M.') FROM T");
    assertResult("a.m.", stat, "SELECT TO_CHAR(X, 'p.m.') FROM T");
    assertResult("AM", stat, "SELECT TO_CHAR(X, 'PM') FROM T");
    assertResult("Am", stat, "SELECT TO_CHAR(X, 'Pm') FROM T");
    assertResult("am", stat, "SELECT TO_CHAR(X, 'pM') FROM T");
    assertResult("am", stat, "SELECT TO_CHAR(X, 'pm') FROM T");
    assertResult("2", stat, "SELECT TO_CHAR(X, 'D') FROM T");
    assertResult("2", stat, "SELECT TO_CHAR(X, 'd') FROM T");
    expected = String.format("%tA", timestamp1979);
    expected = expected.substring(0, 1).toUpperCase() + expected.substring(1);
    String spaces = "         ";
    String first9 = (expected + spaces).substring(0, 9);
    assertResult(StringUtils.toUpperEnglish(first9), stat, "SELECT TO_CHAR(X, 'DAY') FROM T");
    assertResult(first9, stat, "SELECT TO_CHAR(X, 'Day') FROM T");
    assertResult(first9.toLowerCase(), stat, "SELECT TO_CHAR(X, 'day') FROM T");
    assertResult(first9.toLowerCase(), stat, "SELECT TO_CHAR(X, 'dAY') FROM T");
    assertResult(expected, stat, "SELECT TO_CHAR(X, 'fmDay') FROM T");
    assertResult("12", stat, "SELECT TO_CHAR(X, 'DD') FROM T");
    assertResult("316", stat, "SELECT TO_CHAR(X, 'DDD') FROM T");
    assertResult("316", stat, "SELECT TO_CHAR(X, 'DdD') FROM T");
    assertResult("316", stat, "SELECT TO_CHAR(X, 'dDD') FROM T");
    assertResult("316", stat, "SELECT TO_CHAR(X, 'ddd') FROM T");
    expected = String.format("%1$tA, %1$tB %1$te, %1$tY", timestamp1979);
    assertResult(expected, stat, "SELECT TO_CHAR(X, 'DL') FROM T");
    assertResult("11/12/1979", stat, "SELECT TO_CHAR(X, 'DS') FROM T");
    assertResult("11/12/1979", stat, "SELECT TO_CHAR(X, 'Ds') FROM T");
    assertResult("11/12/1979", stat, "SELECT TO_CHAR(X, 'dS') FROM T");
    assertResult("11/12/1979", stat, "SELECT TO_CHAR(X, 'ds') FROM T");
    expected = String.format("%1$ta", timestamp1979);
    assertResult(expected.toUpperCase(), stat, "SELECT TO_CHAR(X, 'DY') FROM T");
    assertResult(Capitalization.CAPITALIZE.apply(expected), stat, "SELECT TO_CHAR(X, 'Dy') FROM T");
    assertResult(expected.toLowerCase(), stat, "SELECT TO_CHAR(X, 'dy') FROM T");
    assertResult(expected.toLowerCase(), stat, "SELECT TO_CHAR(X, 'dY') FROM T");
    assertResult("08:12:34.560000000", stat, "SELECT TO_CHAR(X, 'HH:MI:SS.FF') FROM T");
    assertResult("08:12:34.5", stat, "SELECT TO_CHAR(X, 'HH:MI:SS.FF1') FROM T");
    assertResult("08:12:34.56", stat, "SELECT TO_CHAR(X, 'HH:MI:SS.FF2') FROM T");
    assertResult("08:12:34.560", stat, "SELECT TO_CHAR(X, 'HH:MI:SS.FF3') FROM T");
    assertResult("08:12:34.5600", stat, "SELECT TO_CHAR(X, 'HH:MI:SS.FF4') FROM T");
    assertResult("08:12:34.56000", stat, "SELECT TO_CHAR(X, 'HH:MI:SS.FF5') FROM T");
    assertResult("08:12:34.560000", stat, "SELECT TO_CHAR(X, 'HH:MI:SS.FF6') FROM T");
    assertResult("08:12:34.5600000", stat, "SELECT TO_CHAR(X, 'HH:MI:SS.FF7') FROM T");
    assertResult("08:12:34.56000000", stat, "SELECT TO_CHAR(X, 'HH:MI:SS.FF8') FROM T");
    assertResult("08:12:34.560000000", stat, "SELECT TO_CHAR(X, 'HH:MI:SS.FF9') FROM T");
    assertResult("012345678", stat, "SELECT TO_CHAR(TIME '0:00:00.012345678', 'FF') FROM T");
    assertResult("00", stat, "SELECT TO_CHAR(TIME '0:00:00.000', 'FF2') FROM T");
    assertResult("08:12", stat, "SELECT TO_CHAR(X, 'HH:MI') FROM T");
    assertResult("08:12", stat, "SELECT TO_CHAR(X, 'HH12:MI') FROM T");
    assertResult("08:12", stat, "SELECT TO_CHAR(X, 'HH24:MI') FROM T");
    assertResult("46", stat, "SELECT TO_CHAR(X, 'IW') FROM T");
    assertResult("46", stat, "SELECT TO_CHAR(X, 'WW') FROM T");
    assertResult("2", stat, "SELECT TO_CHAR(X, 'W') FROM T");
    assertResult("9", stat, "SELECT TO_CHAR(X, 'I') FROM T");
    assertResult("79", stat, "SELECT TO_CHAR(X, 'IY') FROM T");
    assertResult("979", stat, "SELECT TO_CHAR(X, 'IYY') FROM T");
    assertResult("1979", stat, "SELECT TO_CHAR(X, 'IYYY') FROM T");
    assertResult("2444190", stat, "SELECT TO_CHAR(X, 'J') FROM T");
    assertResult("12", stat, "SELECT TO_CHAR(X, 'MI') FROM T");
    assertResult("11", stat, "SELECT TO_CHAR(X, 'MM') FROM T");
    assertResult("11", stat, "SELECT TO_CHAR(X, 'Mm') FROM T");
    assertResult("11", stat, "SELECT TO_CHAR(X, 'mM') FROM T");
    assertResult("11", stat, "SELECT TO_CHAR(X, 'mm') FROM T");
    expected = String.format("%1$tb", timestamp1979);
    expected = stripTrailingPeriod(expected);
    expected = expected.substring(0, 1).toUpperCase() + expected.substring(1);
    assertResult(expected.toUpperCase(), stat, "SELECT TO_CHAR(X, 'MON') FROM T");
    assertResult(expected, stat, "SELECT TO_CHAR(X, 'Mon') FROM T");
    assertResult(expected.toLowerCase(), stat, "SELECT TO_CHAR(X, 'mon') FROM T");
    expected = String.format("%1$tB", timestamp1979);
    expected = (expected + "        ").substring(0, 9);
    assertResult(expected.toUpperCase(), stat, "SELECT TO_CHAR(X, 'MONTH') FROM T");
    assertResult(Capitalization.CAPITALIZE.apply(expected), stat, "SELECT TO_CHAR(X, 'Month') FROM T");
    assertResult(expected.toLowerCase(), stat, "SELECT TO_CHAR(X, 'month') FROM T");
    assertResult(Capitalization.CAPITALIZE.apply(expected.trim()), stat, "SELECT TO_CHAR(X, 'fmMonth') FROM T");
    assertResult("4", stat, "SELECT TO_CHAR(X, 'Q') FROM T");
    assertResult("XI", stat, "SELECT TO_CHAR(X, 'RM') FROM T");
    assertResult("xi", stat, "SELECT TO_CHAR(X, 'rm') FROM T");
    assertResult("Xi", stat, "SELECT TO_CHAR(X, 'Rm') FROM T");
    assertResult("79", stat, "SELECT TO_CHAR(X, 'RR') FROM T");
    assertResult("1979", stat, "SELECT TO_CHAR(X, 'RRRR') FROM T");
    assertResult("34", stat, "SELECT TO_CHAR(X, 'SS') FROM T");
    assertResult("29554", stat, "SELECT TO_CHAR(X, 'SSSSS') FROM T");
    expected = new SimpleDateFormat("h:mm:ss aa").format(timestamp1979);
    if (Locale.getDefault().getLanguage().equals(Locale.ENGLISH.getLanguage())) {
        assertEquals("8:12:34 AM", expected);
    }
    assertResult(expected, stat, "SELECT TO_CHAR(X, 'TS') FROM T");
    assertResult(tzLongName, stat, "SELECT TO_CHAR(X, 'TZR') FROM T");
    assertResult(tzShortName, stat, "SELECT TO_CHAR(X, 'TZD') FROM T");
    assertResult("GMT+10:30", stat, "SELECT TO_CHAR(TIMESTAMP WITH TIME ZONE '2010-01-01 0:00:00+10:30', 'TZR')");
    assertResult("GMT+10:30", stat, "SELECT TO_CHAR(TIMESTAMP WITH TIME ZONE '2010-01-01 0:00:00+10:30', 'TZD')");
    expected = String.format("%f", 1.1).substring(1, 2);
    assertResult(expected, stat, "SELECT TO_CHAR(X, 'X') FROM T");
    expected = String.format("%,d", 1979);
    assertResult(expected, stat, "SELECT TO_CHAR(X, 'Y,YYY') FROM T");
    assertResult("1979", stat, "SELECT TO_CHAR(X, 'YYYY') FROM T");
    assertResult("1979", stat, "SELECT TO_CHAR(X, 'SYYYY') FROM T");
    assertResult("-0100", stat, "SELECT TO_CHAR(X, 'SYYYY') FROM U");
    assertResult("979", stat, "SELECT TO_CHAR(X, 'YYY') FROM T");
    assertResult("79", stat, "SELECT TO_CHAR(X, 'YY') FROM T");
    assertResult("9", stat, "SELECT TO_CHAR(X, 'Y') FROM T");
    assertResult("7979", stat, "SELECT TO_CHAR(X, 'yyfxyy') FROM T");
    assertThrows(ErrorCode.INVALID_TO_CHAR_FORMAT, stat, "SELECT TO_CHAR(X, 'A') FROM T");
    // check a bug we had when the month or day of the month is 1 digit
    stat.executeUpdate("TRUNCATE TABLE T");
    stat.executeUpdate("INSERT INTO T VALUES (TIMESTAMP '1985-01-01 08:12:34.560')");
    assertResult("19850101", stat, "SELECT TO_CHAR(X, 'YYYYMMDD') FROM T");
    conn.close();
}