mirror of
https://github.com/featurebasedb/featurebase.git
synced 2026-10-08 20:07:51 +00:00
652 lines
14 KiB
Go
652 lines
14 KiB
Go
package defs
|
|
|
|
import (
|
|
"fmt"
|
|
"time"
|
|
)
|
|
|
|
// datepart tests
|
|
var datePartTests = TableTest{
|
|
|
|
Table: tbl(
|
|
"dateparttests",
|
|
srcHdrs(
|
|
srcHdr("_id", fldTypeID),
|
|
srcHdr("a", fldTypeInt, "min 0", "max 1000"),
|
|
srcHdr("b", fldTypeInt, "min 0", "max 1000"),
|
|
srcHdr("ts", fldTypeTimestamp, "timeunit 'ns'"),
|
|
),
|
|
srcRows(
|
|
srcRow(int64(1), int64(10), int64(100), knownSubSecondTimestamp()),
|
|
),
|
|
),
|
|
SQLTests: []SQLTest{
|
|
{
|
|
name: "DatePartIncorrectParamsCount",
|
|
SQLs: sqls(
|
|
"select datepart()",
|
|
),
|
|
ExpErr: "count of formal parameters (2) does not match count of actual parameters (0)",
|
|
},
|
|
{
|
|
name: "DatePartIntError",
|
|
SQLs: sqls(
|
|
"select datepart(1, 2)",
|
|
),
|
|
ExpErr: "an expression of type 'int' cannot be passed to a parameter of type 'string'",
|
|
},
|
|
{
|
|
name: "DatePartInvalidParam",
|
|
SQLs: sqls(
|
|
"select datepart('1', current_timestamp)",
|
|
),
|
|
ExpErr: "invalid value '1' for parameter 'interval'",
|
|
},
|
|
{
|
|
name: "ToTimestampWrongParamsCount",
|
|
SQLs: sqls(
|
|
"select totimestamp()",
|
|
),
|
|
ExpErr: "count of formal parameters (2) does not match count of actual parameters (0)",
|
|
},
|
|
{
|
|
name: "ToTimestampStringError",
|
|
SQLs: sqls(
|
|
"select totimestamp('a')",
|
|
),
|
|
ExpErr: "an expression of type 'string' cannot be passed to a parameter of type 'int'",
|
|
},
|
|
{
|
|
name: "ToTimestampIntError",
|
|
SQLs: sqls(
|
|
"select totimestamp(1, 2)",
|
|
),
|
|
ExpErr: "an expression of type 'int' cannot be passed to a parameter of type 'string'",
|
|
},
|
|
{
|
|
name: "ToTimestampInvalid",
|
|
SQLs: sqls(
|
|
"select totimestamp(1, 'x')",
|
|
),
|
|
ExpErr: "invalid value 'x' for parameter 'timeunit'",
|
|
},
|
|
{
|
|
name: "DATEPARTYY",
|
|
SQLs: sqls(
|
|
"select _id, datepart('yy', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(2012)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
name: "DATEPARTYD",
|
|
SQLs: sqls(
|
|
"select _id, datepart('yd', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(306)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
name: "DATEPARTM",
|
|
SQLs: sqls(
|
|
"select _id, datepart('m', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(11)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
name: "DATEPARTD",
|
|
SQLs: sqls(
|
|
"select _id, datepart('d', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(1)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
name: "DATEPARTW",
|
|
SQLs: sqls(
|
|
"select _id, datepart('w', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(4)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
name: "DATEPARTWK",
|
|
SQLs: sqls(
|
|
"select _id, datepart('wk', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(44)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
name: "DATEPARTHH",
|
|
SQLs: sqls(
|
|
"select _id, datepart('hh', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(22)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
name: "DatePartMI",
|
|
SQLs: sqls(
|
|
"select _id, datepart('mi', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(8)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
name: "DatePartS",
|
|
SQLs: sqls(
|
|
"select _id, datepart('s', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(41)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
name: "DatePartMS",
|
|
SQLs: sqls(
|
|
"select _id, datepart('ms', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(100)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datepart('us', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(100200)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
name: "DatePartNS",
|
|
SQLs: sqls(
|
|
"select _id, datepart('ns', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(100200300)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
//test datepart(timestamp, part) for implicit conversion of integer value passed as argument to timestamp param
|
|
name: "DatePartImplicitIntConversion",
|
|
SQLs: sqls(
|
|
"select datepart('yy', 0) as \"yy\", datepart('m', 0) as \"m\", datepart('d', 0) as \"d\"",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("yy", fldTypeInt),
|
|
hdr("m", fldTypeInt),
|
|
hdr("d", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1970), int64(1), int64(1)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
},
|
|
}
|
|
|
|
// toTimestamp tests
|
|
var toTimestampTests = TableTest{
|
|
|
|
Table: tbl(
|
|
"",
|
|
nil,
|
|
nil,
|
|
),
|
|
SQLTests: []SQLTest{
|
|
{
|
|
SQLs: sqls(
|
|
"select totimestamp()",
|
|
),
|
|
ExpErr: "count of formal parameters (2) does not match count of actual parameters (0)",
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select totimestamp('a')",
|
|
),
|
|
ExpErr: "an expression of type 'string' cannot be passed to a parameter of type 'int'",
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select totimestamp(1, 2)",
|
|
),
|
|
ExpErr: "an expression of type 'int' cannot be passed to a parameter of type 'string'",
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select totimestamp(1, 'x')",
|
|
),
|
|
ExpErr: "invalid value 'x' for parameter 'timeunit'",
|
|
},
|
|
{
|
|
name: "ToTimestampAllPossibleValues",
|
|
SQLs: sqls(
|
|
"select totimestamp(1000) as \"default\", totimestamp(1000, 's') as \"s\", totimestamp(1000000, 'ms') as \"ms\", totimestamp(1000000000, 'us') as \"us\", totimestamp(1000000000, 'µs') as \"µs\", totimestamp(1000000000000, 'ns') as \"ns\"",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("default", fldTypeTimestamp),
|
|
hdr("s", fldTypeTimestamp),
|
|
hdr("ms", fldTypeTimestamp),
|
|
hdr("us", fldTypeTimestamp),
|
|
hdr("µs", fldTypeTimestamp),
|
|
hdr("ns", fldTypeTimestamp),
|
|
),
|
|
ExpRows: rows(
|
|
row(time.Unix(1000, 0).UTC(),
|
|
time.Unix(1000, 0).UTC(),
|
|
time.UnixMilli(1000000).UTC(),
|
|
time.UnixMicro(1000000000).UTC(),
|
|
time.UnixMicro(1000000000).UTC(),
|
|
time.Unix(0, 1000000000000).UTC()),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
name: "DateTimeFromPartsParamsCountMismatch",
|
|
SQLs: sqls(
|
|
"select datetimefromparts(12,32,43,34,34,34)",
|
|
),
|
|
ExpErr: "count of formal parameters (7) does not match count of actual parameters (6)",
|
|
},
|
|
{
|
|
name: "DateTimeFromPartsParamsTypeMismatch",
|
|
SQLs: sqls(
|
|
"select datetimefromparts(12,32,43,34,34,34,'foo')",
|
|
),
|
|
ExpErr: "an expression of type 'string' cannot be passed to a parameter of type 'int'",
|
|
},
|
|
{
|
|
name: "DateTimeFromPartsYearOutOfRange",
|
|
SQLs: sqls(
|
|
"select datetimefromparts(10000,1,1,1,1,1,1)",
|
|
),
|
|
ExpErr: "[0:0] year '10000' out of range [0,9999]",
|
|
},
|
|
{
|
|
name: "DateTimeFromPartsKnownTimestamp",
|
|
SQLs: sqls(
|
|
fmt.Sprintf("select datetimefromparts(%d,%d,%d,%d,%d,%d,%d) as datetime", knownTimestamp().Year(),
|
|
knownTimestamp().Month(), knownTimestamp().Day(), knownTimestamp().Hour(), knownTimestamp().Minute(),
|
|
knownTimestamp().Second(), knownTimestamp().Nanosecond()/(1000*1000)),
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("datetime", fldTypeTimestamp),
|
|
),
|
|
ExpRows: rows(
|
|
row(knownTimestamp()),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
name: "DateTimeFromPartsAllZeros",
|
|
SQLs: sqls(
|
|
fmt.Sprintf("select datetimefromparts(%d,%d,%d,%d,%d,%d,%d) as datetime", 0, 1, 1, 0, 0, 0, 0),
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("datetime", fldTypeTimestamp),
|
|
),
|
|
ExpRows: rows(
|
|
row(time.Date(0, 1, 1, 0, 0, 0, 0, time.UTC)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
},
|
|
}
|
|
|
|
var dateTimeNameTests = TableTest{
|
|
|
|
Table: tbl(
|
|
"datetimenametests",
|
|
srcHdrs(
|
|
srcHdr("_id", fldTypeID),
|
|
srcHdr("ts", fldTypeTimestamp),
|
|
),
|
|
srcRows(
|
|
srcRow(int64(1), knownTimestamp()),
|
|
),
|
|
),
|
|
SQLTests: []SQLTest{
|
|
// dateTimeName tests
|
|
// make sure returning a string as the year still works
|
|
// if this works then the other parts converted to strings of digits should also work
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datetimename('yy', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeString),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), "2012"),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
//check to make sure it gets a month name correctly
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datetimename('m', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeString),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), "November"),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
// check to make sure it gets a day of the week correctly
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datetimename('w', ts) from dateparttests",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeString),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), "Thursday"),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
},
|
|
}
|
|
|
|
// datetimeAdd tests
|
|
var datetimeAddTests = TableTest{
|
|
|
|
Table: tbl(
|
|
"datetimeadd",
|
|
srcHdrs(
|
|
srcHdr("_id", fldTypeID),
|
|
srcHdr("ts", fldTypeTimestamp, "timeunit 'ns'"),
|
|
),
|
|
srcRows(
|
|
srcRow(int64(1), knownSubSecondTimestamp()),
|
|
),
|
|
),
|
|
SQLTests: []SQLTest{
|
|
{
|
|
SQLs: sqls(
|
|
"select datetimeadd()",
|
|
),
|
|
ExpErr: "count of formal parameters (3) does not match count of actual parameters (0)",
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select datetimeadd(1,1,current_timestamp)",
|
|
),
|
|
ExpErr: "an expression of type 'int' cannot be passed to a parameter of type 'string'",
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select datetimeadd('yy', '2',current_timestamp)",
|
|
),
|
|
ExpErr: "an expression of type 'string' cannot be passed to a parameter of type 'int'",
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select datetimeadd('yy', 2, true)",
|
|
),
|
|
ExpErr: "an expression of type 'bool' cannot be passed to a parameter of type 'timestamp'",
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select datetimeadd('x',1,current_timestamp)",
|
|
),
|
|
ExpErr: "invalid value 'x' for parameter 'timeunit'",
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select datetimeadd('ms',7000,'YYYY-MM-DDTHH:MM:SS')",
|
|
),
|
|
ExpErr: "unable to convert 'YYYY-MM-DDTHH:MM:SS' to type 'timestamp'",
|
|
},
|
|
//Test datetimeadd() for all possible time units
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datepart('YY',datetimeadd('YY', 1, ts)) from datetimeadd",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(2013)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datepart('M',datetimeadd('M', 1, ts)) from datetimeadd",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(12)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datepart('D',datetimeadd('D', 1, ts)) from datetimeadd",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(2)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datepart('HH',datetimeadd('HH', 1, ts)) from datetimeadd",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(23)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datepart('MI',datetimeadd('MI', 1, ts)) from datetimeadd",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(9)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datepart('S',datetimeadd('S', 1, ts)) from datetimeadd",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(42)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datepart('MS',datetimeadd('MS', 1, ts)) from datetimeadd",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(101)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datepart('US',datetimeadd('US', 1, ts)) from datetimeadd",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(100201)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datepart('NS',datetimeadd('NS', 1, ts)) from datetimeadd",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(100200301)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
//test datetimeadd() for subtraction
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datepart('YY',datetimeadd('YY', -1, ts)) from datetimeadd",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(2011)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
//test datetimeadd() for transition
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datepart('NS',datetimeadd('NS', 700, ts)) as a, datepart('US',datetimeadd('NS', 700, ts)) as b from datetimeadd",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("a", fldTypeInt),
|
|
hdr("b", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(100201000), int64(100201)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
//test datetimeadd() for literals
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datepart('YY',datetimeadd('YY', 1, 0)) from datetimeadd",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(1971)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
{
|
|
SQLs: sqls(
|
|
"select _id, datepart('YY',datetimeadd('YY', 1, '2023-03-03T00:00:00Z')) from datetimeadd",
|
|
),
|
|
ExpHdrs: hdrs(
|
|
hdr("_id", fldTypeID),
|
|
hdr("", fldTypeInt),
|
|
),
|
|
ExpRows: rows(
|
|
row(int64(1), int64(2024)),
|
|
),
|
|
Compare: CompareExactUnordered,
|
|
},
|
|
},
|
|
}
|