Changeset: b43718b0cfe2 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/b43718b0cfe2
Modified Files:
clients/odbc/driver/SQLGetInfo.c
sql/test/odbc-escape-sequences/Tests/time-date-interval-functions.test
Branch: default
Log Message:
Update the returned ODBC SQLGetInfo() on SQL_TIMEDATE_ADD_INTERVALS,
SQL_TIMEDATE_DIFF_INTERVALS and SQL_TIMEDATE_FUNCTIONS.
With the implementation of the ODBC escape sequences in the parser in servers
11.46 and higher, more functions are now supported.
Also extended tests for these new functions.
This now reports that some functions do not work correctly and return wrong
results:
timestampadd(SQL_TSI_SECOND, 11, timestamp'2022-11-14 14:13:12') adds msec
instead of seconds
timestampadd(SQL_TSI_SECOND, 11.234, timestamp'2022-11-14 14:13:12') is not
accepted. The second argument must be an integer, not a decimal. It also may
not be an expression. Would be nice if expressions were allowed.
timestampadd(SQL_TSI_DAY, 28, timestamp'2022-11-14 14:13:12') instead of adding
it seems to subtract days, strange behavior.
timestampadd(SQL_TSI_WEEK, 4, timestamp'2022-11-14 14:13:12') instead of adding
4 weeks (or 28 days) it seems to subtract days, strange behavior.
timestampadd(SQL_TSI_YEAR, 5, timestamp'2022-11-14 14:13:12') instead of 5 year
it adds 15 years!
Also timestampadd(SQL_TSI_FRAC_SECOND, 123, timestamp'2022-11-14 14:13:12')
does not seem to be implemented. It returns a syntax error.
diffs (truncated from 384 to 300 lines):
diff --git a/clients/odbc/driver/SQLGetInfo.c b/clients/odbc/driver/SQLGetInfo.c
--- a/clients/odbc/driver/SQLGetInfo.c
+++ b/clients/odbc/driver/SQLGetInfo.c
@@ -1037,15 +1037,16 @@ MNDBGetInfo(ODBCDbc *dbc,
break;
case SQL_TIMEDATE_ADD_INTERVALS:
case SQL_TIMEDATE_DIFF_INTERVALS:
- /* SQL_FN_TSI_FRAC_SECOND |
- * SQL_FN_TSI_SECOND |
- * SQL_FN_TSI_MINUTE |
- * SQL_FN_TSI_HOUR |
- * SQL_FN_TSI_DAY |
- * SQL_FN_TSI_WEEK |
- * SQL_FN_TSI_MONTH |
- * SQL_FN_TSI_QUARTER |
- * SQL_FN_TSI_YEAR */
+ /* when server is 11.46 or higher */
+ nValue = SQL_FN_TSI_SECOND |
+ SQL_FN_TSI_MINUTE |
+ SQL_FN_TSI_HOUR |
+ SQL_FN_TSI_DAY |
+ SQL_FN_TSI_WEEK |
+ SQL_FN_TSI_MONTH |
+ SQL_FN_TSI_QUARTER |
+ SQL_FN_TSI_YEAR;
+ /* SQL_FN_TSI_FRAC_SECOND | */
break;
case SQL_TIMEDATE_FUNCTIONS:
nValue = SQL_FN_TD_CURRENT_DATE |
@@ -1053,7 +1054,7 @@ MNDBGetInfo(ODBCDbc *dbc,
SQL_FN_TD_CURRENT_TIMESTAMP |
SQL_FN_TD_CURDATE |
SQL_FN_TD_CURTIME |
- /* SQL_FN_TD_DAYNAME | */
+ SQL_FN_TD_DAYNAME | /* when server is 11.46 or
higher */
SQL_FN_TD_DAYOFMONTH |
SQL_FN_TD_DAYOFWEEK |
SQL_FN_TD_DAYOFYEAR |
@@ -1061,12 +1062,12 @@ MNDBGetInfo(ODBCDbc *dbc,
SQL_FN_TD_HOUR |
SQL_FN_TD_MINUTE |
SQL_FN_TD_MONTH |
- /* SQL_FN_TD_MONTHNAME | */
+ SQL_FN_TD_MONTHNAME | /* when server is 11.46 or
higher */
SQL_FN_TD_NOW |
SQL_FN_TD_QUARTER |
SQL_FN_TD_SECOND |
- /* SQL_FN_TD_TIMESTAMPADD | */
- /* SQL_FN_TD_TIMESTAMPDIFF | */
+ SQL_FN_TD_TIMESTAMPADD | /* when server is 11.46
or higher */
+ SQL_FN_TD_TIMESTAMPDIFF | /* when server is 11.46
or higher */
SQL_FN_TD_WEEK |
SQL_FN_TD_YEAR;
break;
diff --git
a/sql/test/odbc-escape-sequences/Tests/time-date-interval-functions.test
b/sql/test/odbc-escape-sequences/Tests/time-date-interval-functions.test
--- a/sql/test/odbc-escape-sequences/Tests/time-date-interval-functions.test
+++ b/sql/test/odbc-escape-sequences/Tests/time-date-interval-functions.test
@@ -1,3 +1,8 @@
+query I rowsort
+select { fn current_date() } > date'2023-01-02'
+----
+1
+
query I rowsort
select length((select { fn current_date() })) > 0
----
@@ -9,6 +14,11 @@ select length((select { fn curdate() }))
1
query I rowsort
+select { fn current_time() } >= time'00:00:00'
+----
+1
+
+query I rowsort
select length(cast((select { fn current_time() }) as varchar(100))) > 0
----
1
@@ -18,42 +28,137 @@ select length(cast((select { fn curtime(
----
1
+query I rowsort
+select { fn current_timestamp() } > timestamp'2023-01-02 00:00:00'
+----
+1
+
+query I rowsort
+select length(cast((select { fn current_timestamp() }) as varchar(100))) > 0
+----
+1
+
query T rowsort
-select { fn dayname('2022-11-11') }
+select { fn dayname(date'2022-11-11') }
----
Friday
query I rowsort
-select { fn dayofmonth('2022-11-11') }
+select { fn dayofmonth(date'2022-11-11') }
----
11
query I rowsort
-select { fn dayofweek('2022-11-11') }
+select { fn dayofweek(date'2022-11-11') }
----
5
query I rowsort
-select { fn dayofyear('2022-01-01') }
+select { fn dayofyear(date'2022-01-31') }
+----
+31
+
+query I rowsort
+select { fn extract(day from date'2022-01-02') }
+----
+2
+
+query I rowsort
+select { fn extract(month from date'2022-07-02') }
+----
+7
+
+query I rowsort
+select { fn extract(year from date'2023-01-02') }
----
-1
+2023
+
+query I rowsort
+select { fn extract(week from date'2023-10-02') }
+----
+40
+
+query I rowsort
+select { fn extract(quarter from date'2023-07-02') }
+----
+3
+
+query I rowsort
+select { fn extract(hour from time'15:45:59') }
+----
+15
+
+query I rowsort
+select { fn extract(minute from time'15:45:59') }
+----
+45
query I rowsort
-select { fn extract(day from date'2022-01-01') }
+select { fn extract(second from time'15:45:59') }
+----
+59
+
+query I rowsort
+select { fn extract(day from timestamp'2022-01-02 15:45:59') }
+----
+2
+
+query I rowsort
+select { fn extract(month from timestamp'2022-07-02 15:45:59') }
+----
+7
+
+query I rowsort
+select { fn extract(year from timestamp'2022-01-02 15:45:59') }
+----
+2022
+
+query I rowsort
+select { fn extract(week from timestamp'2023-10-02') }
----
-1
+40
+
+query I rowsort
+select { fn extract(quarter from timestamp'2022-12-02 15:45:59') }
+----
+4
+
+query I rowsort
+select { fn extract(hour from timestamp'2022-01-02 15:45:59') }
+----
+15
+
+query I rowsort
+select { fn extract(minute from timestamp'2022-01-02 15:45:59') }
+----
+45
+
+query I rowsort
+select { fn extract(second from timestamp'2022-01-02 15:45:59') }
+----
+59
query T rowsort
select { fn hour(time'12:00:00') }
----
12
+query T rowsort
+select { fn hour(timestamp'2022-01-02 15:45:59') }
+----
+15
+
query I rowsort
select { fn minute(time'12:15:00') }
----
15
query I rowsort
+select { fn minute(timestamp'2022-01-02 15:45:59') }
+----
+45
+
+query I rowsort
select { fn month(date'2022-11-14') }
----
11
@@ -64,6 +169,11 @@ select { fn monthname(date'2022-11-14')
November
query I rowsort
+select {fn now()} > timestamp'2022-01-02 15:45:59'
+----
+1
+
+query I rowsort
select length(cast((select { fn now() }) as varchar(100))) > 0
----
1
@@ -74,23 +184,24 @@ select { fn quarter(date'2022-11-14') }
4
query I rowsort
+select { fn quarter(timestamp'2022-06-02 15:45:59') }
+----
+2
+
+query I rowsort
select { fn second(time'12:30:33') }
----
33
-
-# TODO not implemented
-# query T rowsort
-# select { fn timestampadd(hour, 1, timestamp'2022-11-14 09:26:30') }
-# ----
-# 2022-11-14 10:26:30
+query I rowsort
+select { fn second(timestamp'2022-01-02 15:45:59') }
+----
+59
-
-# TODO not implemented
-# query I rowsort
-# select { fn timestampdiff(hour, timestamp'2022-11-14 10:26:30',
timestamp'2022-11-14 09:26:30') }
-# ----
-# 1
+query I rowsort
+select { fn second(timestamp'2022-11-14 12:30:34') }
+----
+34
query I rowsort
select { fn week(date'2022-11-14') }
@@ -98,14 +209,32 @@ select { fn week(date'2022-11-14') }
46
query I rowsort
+select { fn week(timestamp'2022-11-14 12:30:34') }
+----
+46
+
+query I rowsort
select { fn year(date'2022-11-14') }
----
2022
+query I rowsort
+select { fn year(timestamp'2222-11-14 12:30:34') }
+----
+2222
+
+# timestampadd() tests
+
+# TODO not implemented
+# query T rowsort
+# select { fn timestampadd(hour, 10, timestamp'2022-11-14 09:26:30') }
+# ----
+# 2022-11-14 10:26:30
+
query T rowsort
-select { fn timestampadd(SQL_TSI_HOUR, 1, timestamp'2022-11-14 12:12:12') }
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
