📄 testfunctions.java
字号:
/* =============================================================
* SmallSQL : a free Java DBMS library for the Java(tm) platform
* =============================================================
*
* (C) Copyright 2004-2007, by Volker Berlin.
*
* Project Info: http://www.smallsql.de/
*
* 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("~1" , new Integer(-2)),
a("abs(-5)" , new Integer(5)),
a("abs(aint)" , new Integer(120)),
a("abs("+table+".aint)" , new Integer(120)),
a("abs(null)" , null),
a("abs(cast(5 as money))" , new BigDecimal("5.0000")),
a("abs(cast(-5 as money))" , new BigDecimal("5.0000")),
a("abs(cast(-5 as numeric(4,2)))" , new BigDecimal("5.00")),
a("abs(cast(5 as real))" , new Float(5)),
a("abs(cast(-5 as real))" , new Float(5)),
a("abs(cast(-5 as float))" , new Double(5)),
a("abs(cast(5 as double))" , new Double(5)),
a("abs(cast(5 as smallint))",new Integer(5)),
a("abs(cast(-5 as bigint))", new Long(5)),
a("abs(cast(5 as bigint))", new Long(5)),
a("convert(money, abs(-5))", new BigDecimal("5.0000")),
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("cast({ts '1907-06-05 04:03:02.1'} as smalldatetime)", Timestamp.valueOf("1907-06-05 04:03:00.0")),
a("cast({ts '2007-06-05 04:03:02.1'} as smalldatetime)", Timestamp.valueOf("2007-06-05 04:03:00.0")),
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")), //scale = Max(left scale+5, right scale +4)
a("convert(decimal(12,0), 11) / convert(decimal(12,2), 1)" , new BigDecimal("11.000000")), //scale = Max(left scale+5, right scale +4)
a("convert(money, -10000 / 10000.0)" , new BigDecimal("-1.0000")), //scale = Max(left scale+5, right scale +4)
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(SQL_TSI_YEAR, 1, {d '2004-10-17'})}" , Timestamp.valueOf("2005-10-17 00:00:00.0")),
a("{fn TimestampAdd(SQL_TSI_QUARTER, 1, {d '2004-10-17'})}" , Timestamp.valueOf("2005-01-17 00:00:00.0")),
a("{fn TimestampAdd(SQL_TSI_MONTH, 1, {d '2004-10-17'})}" , Timestamp.valueOf("2004-11-17 00:00:00.0")),
a("{fn TimestampAdd(SQL_TSI_WEEK, 1, {d '2004-10-17'})}" , Timestamp.valueOf("2004-10-24 00:00:00.0")),
a("{fn TimestampAdd(SQL_TSI_HOUR, 1, {d '2004-10-17'})}" , Timestamp.valueOf("2004-10-17 01:00:00.0")),
a("{fn TimestampAdd(SQL_TSI_MINUTE, 61, {d '2004-10-17'})}" , Timestamp.valueOf("2004-10-17 01:01:00.0")),
a("{fn TimestampAdd(SQL_TSI_SECOND, 61, {d '2004-10-17'})}" , Timestamp.valueOf("2004-10-17 00:01:01.0")),
a("{fn TimestampAdd(SQL_TSI_FRAC_SECOND,1,{d '2004-10-17'})}" , Timestamp.valueOf("2004-10-17 00:00:00.001")),
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -