Field<Double> f2d = round(-2.0, 2);
Field<Double> f3d = floor(-2.0);
Field<Double> f4d = ceil(-2.0);
// Some arbitrary checks on having multiple select clauses
Record record =
create().select(f1a)
.select(f2a, f3a)
.select(f4a)
.select(f5a, f6a, f7a)
.select(f1b, f2b, f3b, f4b, f6b, f6b, f7b)
.select(f1c, f2c, f3c, f4c)
.select(f1d, f2d, f3d, f4d).fetchOne();
assertNotNull(record);
assertEquals("1.0", record.getValueAsString(f1a));
assertEquals("1.11", record.getValueAsString(f2a));
assertEquals("1.0", record.getValueAsString(f3a));
assertEquals("2.0", record.getValueAsString(f4a));
assertEquals("1.0", record.getValueAsString(f5a));
assertEquals("1.11", record.getValueAsString(f6a));
assertEquals("10.0", record.getValueAsString(f7a));
assertEquals("-1.0", record.getValueAsString(f1b));
assertEquals("-1.11", record.getValueAsString(f2b));
assertEquals("-2.0", record.getValueAsString(f3b));
assertEquals("-1.0", record.getValueAsString(f4b));
assertEquals("1.0", record.getValueAsString(f5b));
assertEquals("1.11", record.getValueAsString(f6b));
assertEquals("10.0", record.getValueAsString(f7b));
assertEquals("2.0", record.getValueAsString(f1c));
assertEquals("2.0", record.getValueAsString(f2c));
assertEquals("2.0", record.getValueAsString(f3c));
assertEquals("2.0", record.getValueAsString(f4c));
assertEquals("-2.0", record.getValueAsString(f1d));
assertEquals("-2.0", record.getValueAsString(f2d));
assertEquals("-2.0", record.getValueAsString(f3d));
assertEquals("-2.0", record.getValueAsString(f4d));
// Greatest and least
record = create().select(
greatest(1, 2, 3, 4),
least(1, 2, 3),
greatest("1", "2", "3", "4"),
least("1", "2", "3")).fetchOne();
assertEquals(Integer.valueOf(4), record.getValue(0));
assertEquals(Integer.valueOf(1), record.getValue(1));
assertEquals("4", record.getValue(2));
assertEquals("1", record.getValue(3));
// Greatest and least with tables. If they're simulated using subqueries
// there is a risk of breaking this functionality due to limited support
// for subqueries and derived tables...
Result<Record> result = create()
.select(TBook_ID(),
greatest(TBook_ID(),
TBook_AUTHOR_ID(),
TBook_LANGUAGE_ID()),
least(TBook_ID(),
TBook_AUTHOR_ID(),
TBook_LANGUAGE_ID()))
.from(TBook())
.orderBy(TBook_ID())
.fetch();
assertEquals(4, result.size());
assertEquals(BOOK_IDS, result.getValues(TBook_ID()));
assertEquals(asList(1, 2, 4, 4), result.getValues(1));
assertEquals(asList(1, 1, 2, 2), result.getValues(2));
// Mathematical functions
switch (getDialect()) {
case SQLITE:
log.info("SKIPPING", "Tests for mathematical functions");
break;
default: {
// Exponentials, logarithms and roots
// ----------------------------------
Field<BigDecimal> m1 = sqrt(2);
Field<BigDecimal> m2 = round(sqrt(4));
Field<BigDecimal> m3 = exp(2);
Field<BigDecimal> m4 = round(exp(0));
Field<BigDecimal> m5 = exp(-2);
Field<BigDecimal> m6 = ln(2);
Field<BigDecimal> m7 = round(log(16, 4));
Field<BigDecimal> m8 = round(power(2, 4));
Field<BigDecimal> m9 = round(power(sqrt(power(sqrt(2), 2)), 2));
record = create().select(m1, m2, m3, m4, m5, m6, m7, m8, m9).fetchOne();
// Rounding issues are circumvented by using substring()
assertNotNull(record);
assertEquals("1.414", record.getValueAsString(m1).substring(0, 5));
assertEquals("2", record.getValueAsString(m2).substring(0, 1));
assertEquals("7.389", record.getValueAsString(m3).substring(0, 5));
assertEquals("1", record.getValueAsString(m4).substring(0, 1));
assertEquals("0.135", record.getValueAsString(m5).substring(0, 5));
assertEquals("0.693", record.getValueAsString(m6).substring(0, 5));
assertEquals("2", record.getValueAsString(m7).substring(0, 1));
assertEquals("16", record.getValueAsString(m8).substring(0, 2));
assertEquals("2", record.getValueAsString(m9).substring(0, 1));
// Trigonometry
// ------------
Field<BigDecimal> t1 = sin(Math.PI / 6 + 0.00001);
Field<BigDecimal> t2 = cos(Math.PI / 6);
Field<BigDecimal> t3 = tan(Math.PI / 6);
Field<BigDecimal> t4 = cot(Math.PI / 6);
Field<BigDecimal> t6 = rad(deg(1.1));
Field<BigDecimal> t7 = asin(Math.PI / 6);
Field<BigDecimal> t8 = acos(Math.PI / 6);
Field<BigDecimal> t9 = atan(Math.PI / 6);
Field<BigDecimal> ta = round(deg(atan2(1, 1)));
// Hyperbolic functions
// --------------------
Field<BigDecimal> tb = sinh(1.0)
.div(cosh(1.0))
.mul(tanh(1.0))
.mul(power(coth(1.0), 2).add(0.1));
record = create().select(t1, t2, t3, t4, t6, t7, t8, t9, ta, tb).fetchOne();
// Rounding issues are circumvented by using substring()
assertNotNull(record);
assertEquals("0.5", record.getValueAsString(t1).substring(0, 3));
assertEquals("0.866", record.getValueAsString(t2).substring(0, 5));
assertEquals("0.577", record.getValueAsString(t3).substring(0, 5));
assertEquals("1.732", record.getValueAsString(t4).substring(0, 5));
assertEquals("1", record.getValueAsString(t6).substring(0, 1));
assertEquals("0.551", record.getValueAsString(t7).substring(0, 5));
assertEquals("1.019", record.getValueAsString(t8).substring(0, 5));
assertEquals("0.482", record.getValueAsString(t9).substring(0, 5));
assertEquals("45", record.getValueAsString(ta).substring(0, 2));
assertEquals("1", record.getValueAsString(tb).substring(0, 1));
break;
}
}
// The sign function
record = create().select(
sign(2),
sign(1),
sign(0),
sign(-1),
sign(-2)).fetchOne();
assertNotNull(record);
assertEquals(Integer.valueOf(1), record.getValue(0));
assertEquals(Integer.valueOf(1), record.getValue(1));
assertEquals(Integer.valueOf(0), record.getValue(2));
assertEquals(Integer.valueOf(-1), record.getValue(3));
assertEquals(Integer.valueOf(-1), record.getValue(4));
// The abs function
record = create().select(
abs(2),
abs(1),
abs(0),
abs(-1),
abs(-2)).fetchOne();
assertNotNull(record);
assertEquals(Integer.valueOf(2), record.getValue(0));
assertEquals(Integer.valueOf(1), record.getValue(1));
assertEquals(Integer.valueOf(0), record.getValue(2));
assertEquals(Integer.valueOf(1), record.getValue(3));
assertEquals(Integer.valueOf(2), record.getValue(4));
}