Changeset: 0c76a0e6a2df for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/0c76a0e6a2df
Modified Files:
sql/server/sql_parser.y
sql/test/odbc-escape-sequences/Tests/convert-func-hge.test
sql/test/odbc-escape-sequences/Tests/convert-functions.test
Branch: Jun2023
Log Message:
Improved and extended tests of {fn convert(expr, SQL_ODBC_type)} functions.
Found issues with conversion to SQL_CHAR and SQL_WCHAR. Corrected them in the
parser.
diffs (288 lines):
diff --git a/sql/server/sql_parser.y b/sql/server/sql_parser.y
--- a/sql/server/sql_parser.y
+++ b/sql/server/sql_parser.y
@@ -6575,7 +6575,7 @@ odbc_data_type:
| SQL_BIT
{ sql_find_subtype(&$$, "boolean", 0, 0); }
| SQL_CHAR
- { sql_find_subtype(&$$, "char", 1, 0); }
+ { sql_find_subtype(&$$, "char", 0, 0); }
| SQL_DATE
{ sql_find_subtype(&$$, "date", 0, 0); }
| SQL_DECIMAL
@@ -6593,7 +6593,7 @@ odbc_data_type:
}
sql_init_subtype(&$$, t, 0, 0);
}
- | SQL_HUGEINT
+ | SQL_HUGEINT /* Note: SQL_HUGEINT is not part of or defined in ODBC. It
is a MonetDB extension. */
{ sql_find_subtype(&$$, "hugeint", 0, 0); }
| SQL_INTEGER
{ sql_find_subtype(&$$, "int", 0, 0); }
@@ -6642,13 +6642,13 @@ odbc_data_type:
| SQL_VARBINARY
{ sql_find_subtype(&$$, "blob", 0, 0); }
| SQL_VARCHAR
- { sql_find_subtype(&$$, "clob", 0, 0); }
+ { sql_find_subtype(&$$, "varchar", 0, 0); }
| SQL_WCHAR
- { sql_find_subtype(&$$, "char", 1, 0); }
+ { sql_find_subtype(&$$, "char", 0, 0); }
| SQL_WLONGVARCHAR
{ sql_find_subtype(&$$, "clob", 0, 0); }
| SQL_WVARCHAR
- { sql_find_subtype(&$$, "clob", 0, 0); }
+ { sql_find_subtype(&$$, "varchar", 0, 0); }
;
odbc_tsi_qualifier:
diff --git a/sql/test/odbc-escape-sequences/Tests/convert-func-hge.test
b/sql/test/odbc-escape-sequences/Tests/convert-func-hge.test
--- a/sql/test/odbc-escape-sequences/Tests/convert-func-hge.test
+++ b/sql/test/odbc-escape-sequences/Tests/convert-func-hge.test
@@ -1,4 +1,6 @@
+# Note: SQL_HUGEINT is not defined in ODBC. This is a MonetDB extension.
+
query I rowsort
-select { fn convert(1, SQL_HUGEINT) }
+select { fn convert('-2345678901234567890', SQL_HUGEINT) }
----
-1
+-2345678901234567890
diff --git a/sql/test/odbc-escape-sequences/Tests/convert-functions.test
b/sql/test/odbc-escape-sequences/Tests/convert-functions.test
--- a/sql/test/odbc-escape-sequences/Tests/convert-functions.test
+++ b/sql/test/odbc-escape-sequences/Tests/convert-functions.test
@@ -1,11 +1,12 @@
+# ref:
https://learn.microsoft.com/en-us/sql/odbc/reference/appendixes/explicit-data-type-conversion-function
query I rowsort
-select { fn convert(1, SQL_BIGINT) }
+select { fn CONVERT('-2345678901', SQL_BIGINT) }
----
-1
+-2345678901
query T rowsort
-select { fn convert(x'1234', SQL_BINARY) }
+select { fn convert('1234', SQL_BINARY) }
----
1234
@@ -17,7 +18,12 @@ 1
query T rowsort
select { fn convert('abc', SQL_CHAR) }
----
-a
+abc
+
+query T rowsort
+select convert('abc', CHAR(5))
+----
+abc
query T rowsort
select { fn convert('2022-11-10', SQL_DATE) }
@@ -25,86 +31,109 @@ select { fn convert('2022-11-10', SQL_DA
2022-11-10
query T rowsort
-select { fn convert(3.33333, SQL_DECIMAL) }
+select { fn convert('3.12345', SQL_DECIMAL) }
----
-3.333
+3.123
query T rowsort
-select { fn convert(3.33333, SQL_DOUBLE) }
+select { fn convert('3.12345', SQL_DOUBLE) }
----
-3.33333
+3.12345
query T rowsort
-select { fn convert(3.33333, SQL_FLOAT) }
+select { fn convert('3.12345', SQL_FLOAT) }
----
-3.33333
+3.12345
query T rowsort
select { fn convert('a338a15d-0422-4242-a7f0-65316edae19a', SQL_GUID) }
----
a338a15d-0422-4242-a7f0-65316edae19a
+query I rowsort
+select { fn convert('+123456789', SQL_INTEGER) }
+----
+123456789
query I rowsort
-select { fn convert(1, SQL_INTEGER) }
-----
-1
-
-query I rowsort
-select { fn convert(interval '1' year, SQL_INTERVAL_YEAR) }
+select { fn convert('1', SQL_INTERVAL_YEAR) }
----
12
+query T rowsort
+select { fn convert('1-2', SQL_INTERVAL_YEAR_TO_MONTH) }
+----
+14
+
query I rowsort
-select { fn convert(interval '1' year, SQL_INTERVAL_YEAR_TO_MONTH) }
+select { fn convert('12', SQL_INTERVAL_MONTH) }
----
12
-query I rowsort
-select { fn convert(interval '1' year, SQL_INTERVAL_MONTH) }
+query T rowsort
+select { fn convert('1', SQL_INTERVAL_DAY) }
----
-12
+1
query I rowsort
select { fn convert(date'2022-10-8' - date'2022-10-7', SQL_INTERVAL_DAY) }
----
1
-query T rowsort
-select { fn convert(interval '1' day, SQL_INTERVAL_DAY_TO_HOUR) }
+query I rowsort
+select { fn convert({d'2022-10-8'} - {d'2022-10-7'}, SQL_INTERVAL_DAY) }
----
-1 day, 0:00:00
+1
+query T rowsort
+select { fn convert('1 4', SQL_INTERVAL_DAY_TO_HOUR) }
+----
+1 day, 4:00:00
query T rowsort
-select { fn convert(interval '1' day, SQL_INTERVAL_DAY_TO_MINUTE) }
+select { fn convert('2 0:4', SQL_INTERVAL_DAY_TO_MINUTE) }
----
-1 day, 0:00:00
+2 days, 0:04:00
query T rowsort
-select { fn convert(interval '1' day, SQL_INTERVAL_DAY_TO_SECOND) }
+select { fn convert('3 0:0:5', SQL_INTERVAL_DAY_TO_SECOND) }
+----
+3 days, 0:00:05
+
+query T rowsort
+select { fn convert('23', SQL_INTERVAL_HOUR) }
+----
+23:00:00
+
+query T rowsort
+select { fn convert('24', SQL_INTERVAL_HOUR) }
----
1 day, 0:00:00
query T rowsort
-select { fn convert(interval '1' hour, SQL_INTERVAL_HOUR) }
+select { fn convert( {fn convert(2*4*5, SQL_CHAR)}, SQL_INTERVAL_HOUR) }
----
-1:00:00
+1 day, 16:00:00
+
+query T rowsort
+select { fn convert('1:02', SQL_INTERVAL_HOUR_TO_MINUTE) }
+----
+1:02:00
query T rowsort
-select { fn convert(interval '1' hour, SQL_INTERVAL_HOUR_TO_MINUTE) }
+select { fn convert('1:03:06', SQL_INTERVAL_HOUR_TO_SECOND) }
----
-1:00:00
+1:03:06
query T rowsort
-select { fn convert(interval '1' hour, SQL_INTERVAL_HOUR_TO_SECOND) }
+select { fn convert('1', SQL_INTERVAL_MINUTE) }
----
-1:00:00
+0:01:00
query T rowsort
-select { fn convert(interval '1' minute, SQL_INTERVAL_MINUTE) }
+select { fn convert('1:30', SQL_INTERVAL_MINUTE_TO_SECOND) }
----
-0:01:00
+0:01:30
query T rowsort
select { fn convert((interval '1' minute - interval '30' second),
SQL_INTERVAL_MINUTE_TO_SECOND) }
@@ -112,7 +141,7 @@ select { fn convert((interval '1' minute
0:00:30
query T rowsort
-select { fn convert(x'1234', SQL_LONGVARBINARY) }
+select { fn convert('1234', SQL_LONGVARBINARY) }
----
1234
@@ -122,37 +151,37 @@ select { fn convert('abc', SQL_LONGVARCH
abc
query T rowsort
-select { fn convert(3.333333, SQL_NUMERIC) }
+select { fn convert('3.12345', SQL_NUMERIC) }
----
-3.333
+3.123
query T rowsort
-select { fn convert(3.333333, SQL_REAL) }
+select { fn convert('3.123456', SQL_REAL) }
----
-3.333333
+3.123456
query I rowsort
-select { fn convert(3, SQL_SMALLINT) }
+select { fn convert('3', SQL_SMALLINT) }
----
3
query T rowsort
-select { fn convert(time'00:01:45', SQL_TIME) }
+select { fn convert('00:01:45', SQL_TIME) }
----
00:01:45
query T rowsort
-select { fn convert(timestamp'2022-11-10 00:01:45', SQL_TIMESTAMP) }
+select { fn convert('2022-11-10 00:01:45', SQL_TIMESTAMP) }
----
2022-11-10 00:01:45
query I rowsort
-select { fn convert(1, SQL_TINYINT) }
+select { fn convert('1', SQL_TINYINT) }
----
1
query T rowsort
-select { fn convert(x'1234', SQL_VARBINARY) }
+select { fn convert('1234', SQL_VARBINARY) }
----
1234
@@ -164,7 +193,7 @@ abc
query T rowsort
select { fn convert('abc', SQL_WCHAR) }
----
-a
+abc
query T rowsort
select { fn convert('abc', SQL_WVARCHAR) }
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
