This is an automated email from the ASF dual-hosted git repository.
zykkk pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 7ce746654a1 [test](jdbc) add doris and sqlserver jdbc catalog test
case (#26656)
7ce746654a1 is described below
commit 7ce746654a1321a9210ce99d2d1d4129c80298ed
Author: zy-kkk <[email protected]>
AuthorDate: Fri Nov 10 10:32:09 2023 +0800
[test](jdbc) add doris and sqlserver jdbc catalog test case (#26656)
---
.../sqlserver/init/03-create-table.sql | 33 ++++++++++++++
.../docker-compose/sqlserver/init/04-insert.sql | 34 ++++++++++++++
.../jdbc/test_doris_jdbc_catalog.out | 53 ++++++++++++++++++++++
.../jdbc/test_sqlserver_jdbc_catalog.out | 43 ++++++++++++++++++
.../jdbc/test_doris_jdbc_catalog.groovy | 12 +++++
.../jdbc/test_sqlserver_jdbc_catalog.groovy | 6 ++-
6 files changed, 180 insertions(+), 1 deletion(-)
diff --git
a/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
b/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
index a491647e785..cddd38f7863 100644
--- a/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
+++ b/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
@@ -101,3 +101,36 @@ CREATE TABLE dbo.t_id (
Name nvarchar(100)
);
+CREATE TABLE dbo.all_type (
+ id int PRIMARY KEY NOT NULL,
+ name varchar(10) NULL,
+ age int NULL,
+ tinyint_value tinyint NULL,
+ smallint_value smallint NULL,
+ bigint_value bigint NULL,
+ real_value real NULL,
+ float_value float NULL,
+ floatn_value float(5) NULL,
+ decimal_value decimal(38,0) NULL,
+ numeric_value numeric(38,0) NULL,
+ decimal_value2 decimal(38,10) NULL,
+ numeric_value2 numeric(38,10) NULL,
+ char_value char(20) NULL,
+ varchar_value varchar(20) NULL,
+ varcharmax_value varchar(max) NULL,
+ nchar_value nchar(20) NULL,
+ nvarchar_value nvarchar(20) NULL,
+ nvarcharmax_value nvarchar(max) NULL,
+ date_value date NULL,
+ time_value time NULL,
+ datetime_value datetime NULL,
+ datetime2_value datetime2 NULL,
+ smalldatetime_value smalldatetime NULL,
+ datetimeoffset_value datetimeoffset NULL,
+ text_value text NULL,
+ ntext_value ntext NULL,
+ money_value money NULL,
+ smallmoney_value smallmoney NULL,
+ bit_value bit NULL
+);
+
diff --git a/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
b/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
index f4f67523dfb..c18f629707d 100644
--- a/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
@@ -63,3 +63,37 @@ VALUES (
INSERT INTO dbo.t_id (ID, Name) VALUES (NEWID(), 'Data 1');
INSERT INTO dbo.t_id (ID, Name) VALUES (NEWID(), 'Data 2');
+Insert into dbo.all_type values
+(
+1,
+'doris',
+18,
+0,
+1,
+1,
+123.123,
+123.123,
+123.123,
+12345678901234567890123456789012345678,
+12345678901234567890123456789012345678,
+1234567890123456789012345678.0123456789,
+1234567890123456789012345678.0123456789,
+'Make Doris Great!',
+'Make Doris Great!',
+'Make Doris Great!',
+'Make Doris Great!',
+'Make Doris Great!',
+'Make Doris Great!',
+'2023-01-17',
+'16:49:05.1234567',
+'2023-01-17 16:49:05',
+'2023-01-17 16:49:05.1234567',
+'2023-01-17 16:49:05',
+'2023-01-17 16:49:05+08:00',
+'Make Doris Great!',
+'Make Doris Great!',
+922337203685477.5807,
+214748.3647,
+0
+),
+(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
diff --git
a/regression-test/data/external_table_p0/jdbc/test_doris_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_doris_jdbc_catalog.out
index 011a1f2a4b5..2e686056d40 100644
--- a/regression-test/data/external_table_p0/jdbc/test_doris_jdbc_catalog.out
+++ b/regression-test/data/external_table_p0/jdbc/test_doris_jdbc_catalog.out
@@ -28,10 +28,12 @@ doris_jdbc_catalog
6 doris6
-- !base1 --
+\N \N \N \N \N \N \N \N \N \N
\N \N \N \N \N
true 1 1 1 1 1 1.0 1.0 1.00000
1.0000000000 2021-01-01 2021-01-01T00:00 a a {"a":1}
-- !arr1 --
1 [1] [1] [1] [1] [1] [1] [1] [1]
[1.00000] [1.0000000000] ["2021-01-01"] ["2021-01-01 00:00:00.000"]
["a"] ["a"] ["a"]
+2 \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N
-- !tb1 --
1 1
@@ -56,10 +58,61 @@ doris_jdbc_catalog
6 1
-- !base2 --
+\N \N \N \N \N \N \N \N \N \N
\N \N \N \N \N
true 1 1 1 1 1 1.0 1.0 1.00000
1.0000000000 2021-01-01 2021-01-01T00:00 a a {"a":1}
-- !arr2 --
1 [1] [1] [1] [1] [1] [1] [1] [1]
[1.00000] [1.0000000000] ["2021-01-01"] ["2021-01-01 00:00:00.000"]
["a"] ["a"] ["a"]
+2 \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N
+
+-- !ctas_base --
+2
+
+-- !ctas_arr --
+2
+
+-- !desc_ctas_base --
+bool_col BOOLEAN Yes true \N
+tinyint_col TINYINT Yes true \N
+smallint_col SMALLINT Yes true \N
+int_col INT Yes false \N NONE
+bigint_col BIGINT Yes false \N NONE
+largeint_col LARGEINT Yes false \N NONE
+float_col FLOAT Yes false \N NONE
+double_col DOUBLE Yes false \N NONE
+decimal_col DECIMAL(10, 5) Yes false \N NONE
+decimal_col2 DECIMAL(30, 10) Yes false \N NONE
+date_col DATE Yes false \N NONE
+datetime_col DATETIME(3) Yes false \N NONE
+char_col CHAR(10) Yes false \N NONE
+varchar_col VARCHAR(10) Yes false \N NONE
+json_col JSON Yes false \N NONE
+
+-- !desc_ctas_arr --
+int_col INT Yes true \N
+arr_bool_col ARRAY<BOOLEAN> Yes false [] NONE
+arr_tinyint_col ARRAY<TINYINT> Yes false [] NONE
+arr_smallint_col ARRAY<SMALLINT> Yes false [] NONE
+arr_int_col ARRAY<INT> Yes false [] NONE
+arr_bigint_col ARRAY<BIGINT> Yes false [] NONE
+arr_largeint_col ARRAY<LARGEINT> Yes false [] NONE
+arr_float_col ARRAY<FLOAT> Yes false [] NONE
+arr_double_col ARRAY<DOUBLE> Yes false [] NONE
+arr_decimal1_col ARRAY<DECIMALV3(10, 5)> Yes false [] NONE
+arr_decimal2_col ARRAY<DECIMALV3(30, 10)> Yes false []
NONE
+arr_date_col ARRAY<DATEV2> Yes false [] NONE
+arr_datetime_col ARRAY<DATETIMEV2(3)> Yes false [] NONE
+arr_char_col ARRAY<CHAR(10)> Yes false [] NONE
+arr_varchar_col ARRAY<VARCHAR(10)> Yes false [] NONE
+arr_string_col ARRAY<TEXT> Yes false [] NONE
+
+-- !query_ctas_base --
+\N \N \N \N \N \N \N \N \N \N
\N \N \N \N \N
+true 1 1 1 1 1 1.0 1.0 1.00000
1.0000000000 2021-01-01 2021-01-01T00:00 a a {"a":1}
+
+-- !query_ctas_arr --
+1 [1] [1] [1] [1] [1] [1] [1] [1]
[1.00000] [1.0000000000] ["2021-01-01"] ["2021-01-01 00:00:00.000"]
["a"] ["a"] ["a"]
+2 \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N
-- !sql --
doris_jdbc_catalog
diff --git
a/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
index 17ddfbfee81..9906bb10f55 100644
---
a/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
+++
b/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
@@ -56,6 +56,49 @@
-- !id --
2
+-- !all_type --
+1 doris 18 0 1 1 123.123 123.123 123.123
12345678901234567890123456789012345678 12345678901234567890123456789012345678
1234567890123456789012345678.0123456789 1234567890123456789012345678.0123456789
Make Doris Great! Make Doris Great! Make Doris Great! Make
Doris Great! Make Doris Great! Make Doris Great! 2023-01-17
16:49:05.123 2023-01-17T16:49:05 2023-01-17T16:49:05.123456
2023-01-17T16:49 2023-01-17 16:49:05 +08:00 Make Doris Great!
Make Doris Great! 922337203685477.5807 214748. [...]
+2 \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N \N
+
+-- !ctas --
+2
+
+-- !desc_query_ctas --
+id INT No true \N
+name TEXT Yes false \N NONE
+age INT Yes false \N NONE
+tinyint_value SMALLINT Yes false \N NONE
+smallint_value SMALLINT Yes false \N NONE
+bigint_value BIGINT Yes false \N NONE
+real_value FLOAT Yes false \N NONE
+float_value DOUBLE Yes false \N NONE
+floatn_value FLOAT Yes false \N NONE
+decimal_value DECIMAL Yes false \N NONE
+numeric_value DECIMAL Yes false \N NONE
+decimal_value2 DECIMAL(38, 10) Yes false \N NONE
+numeric_value2 DECIMAL(38, 10) Yes false \N NONE
+char_value TEXT Yes false \N NONE
+varchar_value TEXT Yes false \N NONE
+varcharmax_value TEXT Yes false \N NONE
+nchar_value TEXT Yes false \N NONE
+nvarchar_value TEXT Yes false \N NONE
+nvarcharmax_value TEXT Yes false \N NONE
+date_value DATE Yes false \N NONE
+time_value TEXT Yes false \N NONE
+datetime_value DATETIME(3) Yes false \N NONE
+datetime2_value DATETIME(6) Yes false \N NONE
+smalldatetime_value DATETIME Yes false \N NONE
+datetimeoffset_value TEXT Yes false \N NONE
+text_value TEXT Yes false \N NONE
+ntext_value TEXT Yes false \N NONE
+money_value DECIMAL(19, 4) Yes false \N NONE
+smallmoney_value DECIMAL(10, 4) Yes false \N NONE
+bit_value BOOLEAN Yes false \N NONE
+
+-- !query_ctas --
+1 doris 18 0 1 1 123.123 123.123 123.123
12345678901234567890123456789012345678 12345678901234567890123456789012345678
1234567890123456789012345678.0123456789 1234567890123456789012345678.0123456789
Make Doris Great! Make Doris Great! Make Doris Great! Make
Doris Great! Make Doris Great! Make Doris Great! 2023-01-17
16:49:05.123 2023-01-17T16:49:05 2023-01-17T16:49:05.123456
2023-01-17T16:49 2023-01-17 16:49:05 +08:00 Make Doris Great!
Make Doris Great! 922337203685477.5807 214748. [...]
+2 \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N \N
+
-- !sql --
INFORMATION_SCHEMA
db_accessadmin
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_doris_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_doris_jdbc_catalog.groovy
index 280d30614de..c92f285972d 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_doris_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_doris_jdbc_catalog.groovy
@@ -122,6 +122,8 @@ suite("test_doris_jdbc_catalog",
"p0,external,doris,external_docker,external_doc
);
"""
sql """insert into ${base_table} values (true, 1, 1, 1, 1, 1, 1.0, 1.0,
1.0, 1.0, '2021-01-01', '2021-01-01 00:00:00.000', 'a', 'a', '{\"a\": 1}');"""
+ // insert NULL
+ sql """insert into ${base_table} values (null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null);"""
order_qt_base1 """ select * from ${base_table} order by int_col; """
sql """drop table if exists ${arr_table}"""
@@ -152,6 +154,8 @@ suite("test_doris_jdbc_catalog",
"p0,external,doris,external_docker,external_doc
"""
sql """insert into ${arr_table} values (1, array(true), array(1),
array(1), array(1), array(1), array(1), array(1.0), array(1.0), array(1.0),
array(1.0), array('2021-01-01'), array('2021-01-01 00:00:00.000'), array('a'),
array('a'), array('a'));"""
+ // insert NULL
+ sql """insert into ${arr_table} values (2, null, null, null, null, null,
null, null, null, null, null, null, null, null, null, null);"""
order_qt_arr1 """ select * from ${arr_table} order by int_col; """
@@ -167,6 +171,14 @@ suite("test_doris_jdbc_catalog",
"p0,external,doris,external_docker,external_doc
order_qt_tb2 """ select pin_id, hll_union_agg(user_log_acct) from
${catalog_name}.${internal_db_name}.${hllTable} group by pin_id; """
order_qt_base2 """ select * from
${catalog_name}.${internal_db_name}.${base_table} order by int_col; """
order_qt_arr2 """ select * from
${catalog_name}.${internal_db_name}.${arr_table} order by int_col; """
+ sql """ drop table if exists internal.${internal_db_name}.ctas_base; """
+ sql """ drop table if exists internal.${internal_db_name}.ctas_arr; """
+ order_qt_ctas_base """ create table internal.${internal_db_name}.ctas_base
PROPERTIES("replication_num" = "1") as select * from
${catalog_name}.${internal_db_name}.${base_table} order by int_col; """
+ order_qt_ctas_arr """ create table internal.${internal_db_name}.ctas_arr
PROPERTIES("replication_num" = "1") as select * from
${catalog_name}.${internal_db_name}.${arr_table} order by int_col; """
+ qt_desc_ctas_base """ desc internal.${internal_db_name}.ctas_base; """
+ qt_desc_ctas_arr """ desc internal.${internal_db_name}.ctas_arr; """
+ order_qt_query_ctas_base """ select * from
internal.${internal_db_name}.ctas_base order by int_col; """
+ order_qt_query_ctas_arr """ select * from
internal.${internal_db_name}.ctas_arr order by int_col; """
//clean
qt_sql """select current_catalog()"""
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
index 65a85a8fd3a..234c27749cb 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
@@ -71,7 +71,11 @@ suite("test_sqlserver_jdbc_catalog",
"p0,external,sqlserver,external_docker,exte
order_qt_filter2 """ select * from test_char where 1 = 1 and id = 1
order by id; """
order_qt_filter3 """ select * from test_char where id = 1 order by
id; """
order_qt_id """ select count(*) from (select * from t_id) as a; """
-
+ order_qt_all_type """ select * from all_type order by id; """
+ sql """ drop table if exists internal.${internal_db_name}.all_type; """
+ order_qt_ctas """ create table
internal.${internal_db_name}.ctas_all_type PROPERTIES("replication_num" = "1")
as select * from all_type; """
+ qt_desc_query_ctas """ desc
internal.${internal_db_name}.ctas_all_type; """
+ order_qt_query_ctas """ select * from
internal.${internal_db_name}.ctas_all_type order by id; """
sql """ drop catalog if exists ${catalog_name} """
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
