*
* This library is free software; you can redistribute it and/or modify it
* under the terms of the GNU Lesser General Public License as published by
* the Free Software Foundation; either version 2.1 of the License, or
* (at your option) any later version.
*
* This library is distributed in the hope that it will be useful, but
* WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
* or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public
* License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301,
* USA.
*
* [Java is a trademark or registered trademark of Sun Microsystems, Inc.
* in the United States and other countries.]
*
* ---------------
* TestFunktions.java
* ---------------
* Author: Volker Berlin
*
*/
package smallsql.junit;
import junit.framework.*;
import java.math.*;
import java.sql.*;
public class TestFunctions extends BasicTestCase{
private TestValue testValue;
private static final String table = "table_functions";
private static final TestValue[] TESTS = new TestValue[]{
a("$3" , new BigDecimal("3.0000")),
a("$-3.1" , new BigDecimal("-3.1000")),
a("-$3.2" , new BigDecimal("-3.2000")),
a("1 + 2" , new Integer(3)),
a("3 * 2" , new Integer(6)),
a("Top 1 4 / 2" , new Integer(2)),
a("7/3" , new Integer(2)),
a("5 - 2" , new Integer(3)),
a("- aint" , new Integer(120)),
a("5 - - 2" , new Integer(7)),
a("5 - - - 2" , new Integer(3)),
a("-.123E-1" , new Double("-0.0123")),
a(".123E-1" , new Double("0.0123")),
a("123e-1" , new Double("12.3")),
a("123E1" , new Double("1230")),
a("2*5+2" , new Integer("12")),
a("'a''b'" , "a'b"),
a("'a\"b'" , "a\"b"),
a("abs(5)" , new Integer(5)),
a("abs(aint)" , new Integer(120)),
a("abs("+table+".aint)" , new Integer(120)),
a("abs(null)" , null),
a("convert(varchar(30), 11)" , "11"),
a("convert(varchar(30), null)" , null),
a("convert(varchar(1), 12)" , "1"),
a("convert(char(5), 11)" , "11 "),
a("convert(longvarchar, {d '1999-10-12'})" , "1999-10-12"),
a("convert(binary(5), '11')" , new byte[]{'1','1',0,0,0}),
a("convert(binary(5), null)" , null),
a("convert(varbinary(5), 11)" , new byte[]{0,0,0,11}),
a("convert(longvarbinary, '11')", new byte[]{'1','1'}),
a("convert(bit, 1)" , Boolean.TRUE),
a("convert(bit, false)" , Boolean.FALSE),
a("convert(boolean, 0)" , Boolean.FALSE),
a("convert(varchar(30), convert(bit, false))" , "0"),
a("convert(varchar(30), convert(boolean, 0))" , "false"),
a("convert(bigint, 11)" , new Long(11)),
a("convert(int, 11)" , new Integer(11)),
a("{fn convert(11, Sql_integer)}" , new Integer(11)),
a("convert(integer, 11)" , new Integer(11)),
a("convert(smallint, 123456789)", new Integer((short)123456789)),
a("convert(tinyint, 123456789)" , new Integer(123456789 & 0xFF)),
a("convert(date, '1909-10-12')" , Date.valueOf("1909-10-12")),
a("convert(date, null)" , null),
a("convert(date, {ts '1999-10-12 15:14:13.123456'})" , Date.valueOf("1999-10-12")),
a("convert(date, now())" , Date.valueOf( new Date(System.currentTimeMillis()).toString()) ),
a("curdate()" , Date.valueOf( new Date(System.currentTimeMillis()).toString()) ),
a("hour(curtime())" , new Integer(new Time(System.currentTimeMillis()).getHours()) ),
a("minute({t '10:11:12'})" , new Integer(11) ),
a("month( {ts '1899-10-12 15:14:13.123456'})" , new Integer(10)),
a("convert(time, '15:14:13')" , Time.valueOf("15:14:13")),
a("convert(time, null)" , null),
a("convert(timestamp, '1999-10-12 15:14:13.123456')" , Timestamp.valueOf("1999-10-12 15:14:13.123")),
a("convert(varchar(30), {d '1399-10-12 3:14:13'}, -1)" , "1399-10-12"),
a("convert(varchar(30), {ts '1999-10-12 3:14:13.12'}, 99)" , "1999-10-12 03:14:13.12"),
a("convert(varchar(30), {ts '1999-10-12 0:14:13.123456'}, 0)" , "Okt 12 1999 12:14AM"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 1)" , "10/12/99"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 2)" , "99.10.12"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 3)" , "12/10/99"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 4)" , "12.10.99"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 5)" , "12-10-99"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 6)" , "12 Okt 99"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 7)" , "Okt 12, 99"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 8)" , "15:14:13"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 9)" , "Okt 12 1999 03:14:13:123PM"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 10)" , "10-12-99"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 11)" , "99/10/12"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 12)" , "991012"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 13)" , "12 Okt 1999 15:14:13:123"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 14)" , "15:14:13:123"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 20)" , "1999-10-12 15:14:13"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 21)" , "1999-10-12 15:14:13.123"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 100)" , "Okt 12 1999 03:14PM"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 101)" , "10/12/1999"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 102)" , "1999.10.12"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 103)" , "12/10/1999"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 104)" , "12.10.1999"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 105)" , "12-10-1999"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 106)" , "12 Okt 1999"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 107)" , "Okt 12, 1999"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 108)" , "15:14:13"),
a("convert(varchar(30), {ts '1999-10-12 3:14:13.123456'}, 109)" , "Okt 12 1999 03:14:13:123AM"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 109)" , "Okt 12 1999 03:14:13:123PM"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 110)" , "10-12-1999"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 111)" , "1999/10/12"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 112)" , "19991012"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 113)" , "12 Okt 1999 15:14:13:123"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 114)" , "15:14:13:123"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 120)" , "1999-10-12 15:14:13"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 121)" , "1999-10-12 15:14:13.123"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 126)" , "1999-10-12T15:14:13.123"),
a("convert(varchar(30), {ts '1999-10-12 3:14:13.123456'}, 130)" , "12 Okt 1999 03:14:13:123AM"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 130)" , "12 Okt 1999 03:14:13:123PM"),
a("convert(varchar(30), {ts '1999-10-12 15:14:13.123456'}, 131)" , "12/10/99 15:14:13:123"),
a("convert(timestamp, null)" , null),
a("convert(real, 11)" , new Float(11)),
a("convert(real, null)" , null),
a("convert(float, 11.0)" , new Double(11)),
a("convert(double, '11')" , new Double(11)),
a("-convert(decimal, '11.123456')" , new BigDecimal("-11")),
a("-convert(decimal(38,6), '11.123456')" , new BigDecimal("-11.123456")),
a("convert(decimal(38,6), '11.123456') + 1" , new BigDecimal("12.123456")),
a("convert(decimal(38,6), '11.123456') - 1" , new BigDecimal("10.123456")),
a("convert(decimal(12,2), '11.0000') * 1" , new BigDecimal("11.00")),
a("convert(decimal(12,2), '11.0000') * convert(decimal(12,2), 1)" , new BigDecimal("11.0000")),
a("convert(decimal(12,2), '11.0000') / 1" , new BigDecimal("11.0000000")), a("convert(decimal(12,0), 11) / convert(decimal(12,2), 1)" , new BigDecimal("11.000000")), a("convert(money, -10000 / 10000.0)" , new BigDecimal("-1.0000")), a("-convert(money, '11.123456')" , new BigDecimal("-11.1235")),
a("-convert(smallmoney, '11.123456')" , new BigDecimal("-11.1235")),
a("convert(uniqueidentifier, 0x12345678901234567890)" , "78563412-1290-5634-7890-000000000000"),
a("convert(uniqueidentifier, '78563412-1290-5634-7890-000000000000')" , "78563412-1290-5634-7890-000000000000"),
a("convert(binary(16), convert(uniqueidentifier, 0x12345678901234567890))" , new byte[]{0x12,0x34,0x56,0x78,(byte)0x90,0x12,0x34,0x56,0x78,(byte)0x90,0,0,0,0,0,0}),
a("Timestampdiff(day, {d '2004-10-12'}, {d '2004-10-14'})" , new Integer(2)),
a("Timestampdiff(SQL_TSI_DAY, {d '2004-10-12'}, {d '2004-10-15'})" , new Integer(3)),
a("Timestampdiff(d, {d '2004-10-12'}, {d '2004-10-16'})" , new Integer(4)),
a("Timestampdiff(dd, {d '2004-10-12'}, {d '2004-10-17'})" , new Integer(5)),
a("Timestampdiff(SQL_TSI_YEAR,{d '2000-10-12'}, {d '2005-10-17'})" , new Integer(5)),
a("Timestampdiff(year, {d '2000-10-12'}, {d '2005-10-17'})" , new Integer(5)),
a("Timestampdiff(SQL_TSI_QUARTER,{d '2000-10-12'}, {d '2005-10-17'})" , new Integer(20)),
a("Timestampdiff(quarter, {d '2000-10-12'}, {d '2005-10-17'})" , new Integer(20)),
a("Timestampdiff(SQL_TSI_MONTH, {d '2004-10-12'}, {d '2005-11-17'})" , new Integer(13)),
a("Timestampdiff(month, {d '2004-10-12'}, {d '2005-11-17'})" , new Integer(13)),
a("Timestampdiff(SQL_TSI_WEEK, {d '2004-10-09'}, {d '2004-10-12'})" , new Integer(1)),
a("Timestampdiff(week, {d '2004-10-09'}, {d '2004-10-12'})" , new Integer(1)),
a("Timestampdiff(SQL_TSI_HOUR, {d '2004-10-12'}, {d '2004-10-13'})" , new Integer(24)),
a("Timestampdiff(hour, {d '2004-10-12'}, {d '2004-10-13'})" , new Integer(24)),
a("Timestampdiff(SQL_TSI_MINUTE,{t '10:10:10'}, {t '11:11:11'})" , new Integer(61)),
a("Timestampdiff(minute, {t '10:10:10'}, {t '11:11:11'})" , new Integer(61)),
a("Timestampdiff(SQL_TSI_SECOND,{t '00:00:10'}, {t '00:10:11'})" , new Integer(601)),
a("Timestampdiff(second, {t '00:00:10'}, {t '00:10:11'})" , new Integer(601)),
a("Timestampdiff(SQL_TSI_FRAC_SECOND,{ts '2004-10-12 00:00:10.1'}, {ts '2004-10-12 00:00:10.2'})" , new Integer(100)),
a("Timestampdiff(millisecond,{ts '2004-10-12 00:00:10.1'}, {ts '2004-10-12 00:00:10.2'})" , new Integer(100)),
a("{fn TimestampAdd(year, 1, {d '2004-10-17'})}" , Timestamp.valueOf("2005-10-17 00:00:00.0")),
a("Timestampdiff(second, null, {t '00:10:11'})" , null),
a("Timestampdiff(second, {t '00:10:11'}, null)" , null),
a("TimestampAdd(year, 1, null)" , null),
a("DayOfWeek({d '2006-02-16'})" , new Integer(4)),
a("DayOfWeek({d '2006-02-19'})" , new Integer(7)),
a("DayOfYear({d '2004-01-01'})" , new Integer(1)),
a("DayOfYear({d '2004-02-29'})" , new Integer(60)),
a("DayOfYear({d '2004-03-01'})" , new Integer(61)),
a("DayOfYear({d '2004-12-31'})" , new Integer(366)),
a("DayOfMonth({d '1904-07-17'})" , new Integer(17)),
a("locate('ae', 'QWAERAE')" , new Integer(3)),
a("locate('ae', 'QWAERAE', 3)" , new Integer(3)),
a("locate('ae', 'QWAERAE', 4)" , new Integer(6)),
a("locate('ae', 'QWAERAE', null)" , new Integer(3)),
a("locate(null, 'QWAERAE', 4)" , null),
a("locate('ae', null, 4)" , null),
a("{d '2004-10-12'}" , java.sql.Date.valueOf("2004-10-12")),
a("{ts '1999-10-12 15:14:13.123'}" , Timestamp.valueOf("1999-10-12 15:14:13.123")),
a("{t '15:14:13'}" , Time.valueOf("15:14:13")),
a("{fn length('abc')}", new Integer(3)),
a("{fn length('abc ')}", new Integer(3)),
a("{fn length(null)}", null),
a("{fn Right('qwertzu', 3)}", "tzu"),
a("{fn Right('qwertzu', 13)}", "qwertzu"),
a("cast( Right('1234', 2) as real)", new Float(34)),
a("cast( Right('1234', 2) as smallint)",new Integer(34)),
a("cast( Right('1234', 2) as boolean)", Boolean.TRUE),
a("right(0x1234567890, 2)", new byte[]{0x78,(byte)0x90}),
a("right(null, 2)", null),
a("cast({fn SubString('ab2.3qw', 3, 3)} as double)", new Double(2.3)),
a("subString('qwert', 99, 2)", ""),
a("{fn SubString(0x1234567890, 0, 99)}",new byte[]{0x12,0x34,0x56,0x78,(byte)0x90}),
a("{fn SubString(0x1234567890, 2, 2)}", new byte[]{0x34, 0x56}),
a("{fn SubString(0x1234567890, 99, 2)}", new byte[]{}),
a("SubString(null, 99, 2)", null),
a("{fn IfNull(null, 'abc')}", "abc"),
a("{fn IfNull('asd', 'abc')}", "asd"),
a("iif(true, 1, 2)", new Integer(1)),
a("iif(false, 1, 2)", new Integer(2)),
a("CASE aVarchar WHEN 'qwert' THEN 25 WHEN 'asdfg' THEN 26 ELSE null END", new Integer(25)),
a("CASE WHEN aVarchar='qwert' THEN 'uu' WHEN aVarchar='bb' THEN 'gg' ELSE 'nn' END", "uu"),
a("{fn Ascii('')}", null),
a("{fn Ascii(null)}", null),
a("Ascii('abc')", new Integer(97)),
a("{fn Char(97)}", "a"),
a("Char(null)", null),
a("Exp(null)", null),
a("exp(0)", new Double(1)),
a("log(exp(2.4))", new Double(2.4)),
a("log10(10)", new Double(1)),
a("cos(null)", null),
a("cos(0)", new Double(1)),
a("acos(1)", new Double(0)),
a("sin(0)", new Double(0)),
a("cos(pi())", new Double(-1)),
a("asin(0)", new Double(0)),
a("asin(sin(0.5))", new Double(0.5)),
a("tan(0)", new Double(0)),
a("atan(tan(0.5))", new Double(0.5)),
a("atan2(0,3)", new Double(0)),
a("atan2(0,-3)", new Double(Math.PI)),
a("atn2(0,null)", null),
a("cot(0)", new Double(Double.POSITIVE_INFINITY)),
a("tan(0)", new Double(0)),
a("degrees(pi())", new Double(180)),
a("degrees(radians(50))", new Double(50)),
a("ceiling(123.45)", new Double(124)),
a("ceiling(-123.45)", new Double(-123)),
a("power(2, 3)", new Double(8)),
a("5.0 % 2", new Double(1)),
a("5 % 2", new Integer(1)),
a("mod(5, 2)", new Integer(1)),
a("FLOOR(123.45)", new Double(123)),
a("FLOOR('123.45')", new Double(123)),
a("FLOOR(-123.45)", new Double(-124)),
a("FLOOR($123.45)", new BigDecimal("123.0000")),
a("Rand(0)", new Double(0.730967787376657)),
a("ROUND(748.58, -4)", new Double(0)),
a("ROUND(-748.58, -2)", new Double(-700)),
a("ROUND('748.5876', 2)", new Double(748.59)),
a("Sign('748.5876')", new Integer(1)),
a("Sign(-2)", new Integer(-1)),
a("Sign(0.0)", new Integer(0)),
a("Sign(null)", null),
a("sqrt(9)", new Double(3)),
a("Truncate(748.58, -4)", new Double(0)),
a("Truncate(-748.58, -2)", new Double(-700)),
a("Truncate('748.5876', 2)", new Double(748.58)),
};
private static TestValue a(String function, Object result){
TestValue value = new TestValue();
value.function = function;
value.result = result;
return value;
}
TestFunctions(TestValue testValue){
super(testValue.function);
this.testValue = testValue;
}
public void tearDown(){
try{
Connection con = AllTests.getConnection();
Statement st = con.createStatement();
st.execute("drop table " + table);
st.close();
}catch(Throwable e){
}
}
public void setUp(){
tearDown();
try{
Connection con = AllTests.getConnection();
Statement st = con.createStatement();
st.execute("create table " + table + "(aInt int, aVarchar varchar(100))");
st.execute("Insert into " + table + "(aInt, aVarchar) Values(-120,'qwert')");
st.close();
}catch(Throwable e){
e.printStackTrace();
}
}
public void runTest() throws Exception{
assertEqualsRsValue( testValue.result, "Select " + testValue.function + ",5 from " +table);
}
public static Test suite() throws Exception{
TestSuite theSuite = new TestSuite("Functions");
for(int i=0; i<TESTS.length; i++){
theSuite.addTest(new TestFunctions( TESTS[i] ) );
}
return theSuite;
}
private static class TestValue{
String function;
Object result;
}
}