This is an automated email from the ASF dual-hosted git repository.
morningman 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 02372ca2ea [test](jdbc external table) add new jdbc mysql external
table (#14323)
02372ca2ea is described below
commit 02372ca2ead005c64a377a013e9a543aa247b5bd
Author: lsy3993 <[email protected]>
AuthorDate: Sat Nov 19 09:46:48 2022 +0800
[test](jdbc external table) add new jdbc mysql external table (#14323)
---
.../docker-compose/mysql/init/03-create-table.sql | 58 ++++-
.../docker-compose/mysql/init/04-insert.sql | 29 ++-
.../data/jdbc_p0/test_jdbc_query_mysql.out | 47 ++++
.../suites/jdbc_p0/test_jdbc_query_mysql.groovy | 245 +++++++++++++++++++++
4 files changed, 374 insertions(+), 5 deletions(-)
diff --git a/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
b/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
index 574dd36f38..84f06ccab3 100644
--- a/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
+++ b/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
@@ -44,7 +44,7 @@ CREATE TABLE doris_test.ex_tb2 (
count_value varchar(20)
);
-CREATE TABLE `doris_test.ex_tb3` (
+CREATE TABLE doris_test.ex_tb3 (
`game_code` varchar(20) NOT NULL,
`plat_code` varchar(20) NOT NULL,
`account` varchar(100) NOT NULL,
@@ -56,7 +56,7 @@ CREATE TABLE `doris_test.ex_tb3` (
PRIMARY KEY (`game_code`,`plat_code`,`account`,`login_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-CREATE TABLE `doris_test.ex_tb4` (
+CREATE TABLE doris_test.ex_tb4 (
`products_id` int(11) NOT NULL AUTO_INCREMENT,
`orders_id` int(11) NOT NULL,
`sales_add_time` datetime NOT NULL COMMENT '领款时间',
@@ -66,7 +66,7 @@ CREATE TABLE `doris_test.ex_tb4` (
UNIQUE KEY `idx_orders_id` (`orders_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1985724 DEFAULT CHARSET=utf8;
-CREATE TABLE `doris_test.ex_tb5` (
+CREATE TABLE doris_test.ex_tb5 (
`id` int(10) unsigned not null AUTO_INCREMENT comment "主建",
`apply_id` varchar(32) Default null,
`begin_value` mediumtext,
@@ -80,3 +80,55 @@ CREATE TABLE `doris_test.ex_tb5` (
KEY `idx_apply_id` (`apply_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3732465 DEFAULT CHARSET=utf8mb4;
+CREATE TABLE doris_test.ex_tb6 (
+ `id` bigint(20) PRIMARY KEY,
+ `t_id` bigint(20) NULL,
+ `name` text NULL
+);
+
+CREATE TABLE doris_test.ex_tb7 (
+ `id` varchar(32) NULL DEFAULT "",
+ `user_name` varchar(32) NULL DEFAULT "",
+ `member_list` DECIMAL(10,3)
+);
+
+CREATE TABLE doris_test.ex_tb8 (
+ `date` date NOT NULL COMMENT "",
+ `uid` varchar(64) NOT NULL,
+ `stat_type` int(11) NOT NULL COMMENT "",
+ `price` varchar(255) NULL COMMENT "price"
+);
+
+CREATE TABLE doris_test.ex_tb9 (
+ c_date date null
+);
+
+CREATE TABLE doris_test.ex_tb10 (
+ `aa` varchar(200) NULL,
+ `bb` int NULL,
+ `cc` bigint NULL
+);
+
+CREATE TABLE doris_test.ex_tb11 (
+ `aa` varchar(200) PRIMARY KEY,
+ `bb` int NULL
+);
+
+CREATE TABLE doris_test.ex_tb12 (
+ `cc` varchar(200) PRIMARY KEY,
+ `dd` int NULL
+);
+
+CREATE TABLE doris_test.ex_tb13 (
+ name varchar(128),
+ age INT,
+ idCode varchar(128),
+ cardNo varchar(128),
+ number varchar(128),
+ birthday DATETIME,
+ country varchar(128),
+ gender varchar(128),
+ covid BOOLEAN
+);
+
+
diff --git a/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
b/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
index 6c71488da9..5067a546ab 100644
--- a/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
@@ -1059,9 +1059,34 @@ insert into doris_test.ex_tb4 values
(1, 111, '2021-09-01 07:01:01', '2021-09-01 08:01:01', 1),
(2, 112, '2021-09-02 07:01:01', '2021-09-02 08:01:01', 1),
(3, 113, '0000-01-01 00:00:00', '2021-12-01 08:01:01', 2),
-(4, 114, '0000-00-00 00:00:00', '2021-12-01 09:01:02', 3),
(5, 115, '2021-09-01 07:02:01', '2021-09-01 08:01:04', 4),
(6, 116, '2021-10-01 07:03:01', '2022-09-01 08:02:05', 5);
-insert into doris_test.ex_tb5 values ('test_apply_id', '123321', 'zhangsan',
'zhangsan', 'ready', 'ok', 2, '2022-01-01 02:03:04');
+insert into doris_test.ex_tb5 values (1, 'test_apply_id', '123321',
'zhangsan', 'zhangsan', 'ready', 'ok', 2, '2022-01-01 02:03:04');
+
+insert into doris_test.ex_tb6 values
(639215401565159424,1143681147589283841,'test'),(639237839376089088,1143681147589283841,"test123");
+
+INSERT INTO doris_test.ex_tb7 VALUES ('2','sim',1.000), ('2','sim',1.001),
('2','sim',1.002);
+
+insert into doris_test.ex_tb8 values ('2022-07-15', '2222', 1, NULL),
('2022-07-15', 'ddddd', 2, '0.5');
+
+insert into doris_test.ex_tb9 values ('2022-01-01'), (null);
+
+insert into doris_test.ex_tb10 values ('a', 1, 2), ('b', 1, 2), ('c', 1, 2),
('d', 3, 2);
+
+insert into doris_test.ex_tb11 values ('a', 1), ('b', 1), ('c', 1);
+
+insert into doris_test.ex_tb12 values ('a', 1), ('b', 1), ('c', 1);
+
+insert into doris_test.ex_tb13 values
+('张三0',11,'1234567','123','321312','1999-02-13','中国','男',false),
+('张三1',11,'12345678','123','321312','1999-02-13','中国','男',false),
+('张三2',11,'12345671','123','321312','1999-02-13','中国','男',false),
+('张三3',11,'12345673','123','321312','1999-02-13','中国','男',false),
+('张三4',11,'123456711','123','321312','1999-02-13','中国','男',false),
+('张三5',11,'1232134567','123','321312','1999-02-13','中国','男',false),
+('张三6',11,'124314567','123','321312','1999-02-13','中国','男',false),
+('张三7',11,'123445167','123','321312','1998-02-13','中国','男',false);
+
+
diff --git a/regression-test/data/jdbc_p0/test_jdbc_query_mysql.out
b/regression-test/data/jdbc_p0/test_jdbc_query_mysql.out
index 78aef06d5a..b288469dde 100644
--- a/regression-test/data/jdbc_p0/test_jdbc_query_mysql.out
+++ b/regression-test/data/jdbc_p0/test_jdbc_query_mysql.out
@@ -1044,3 +1044,50 @@ true abc efg 2022-10-01 3.4 1
2 99 100000 1.2 2022-10-02T12:59:01 24.000
-- !sql --
6 6 0
+-- !sql --
+639215401565159424 1143681147589283841 test
+
+-- !sql --
+639215401565159424 1143681147589283841 test
+
+-- !sql --
+639215401565159424 1143681147589283841 test
+
+-- !sql --
+2 sim 1.000
+2 sim 1.001
+2 sim 1.002
+
+-- !sql --
+2022-07-15 1
+
+-- !sql --
+\N
+2022-02-01
+
+-- !sql --
+0
+
+-- !sql --
+\N 112
+111 \N
+
+-- !sql --
+\N
+2
+
+-- !sql --
+ddddd ddddd ddddd 6
+汇总 汇总 汇总 6
+
+-- !sql --
+a 1
+b 1
+c 1
+d 0
+
+-- !sql --
+
+-- !sql --
+8
+
diff --git a/regression-test/suites/jdbc_p0/test_jdbc_query_mysql.groovy
b/regression-test/suites/jdbc_p0/test_jdbc_query_mysql.groovy
index f7058cd1a4..8f20a14eb3 100644
--- a/regression-test/suites/jdbc_p0/test_jdbc_query_mysql.groovy
+++ b/regression-test/suites/jdbc_p0/test_jdbc_query_mysql.groovy
@@ -317,7 +317,252 @@ suite("test_jdbc_query_mysql", "p0") {
SELECT min(LENGTH(begin_value)), max(LENGTH(begin_value)),
sum(case when begin_value is null then 1 else 0 end)
from $exMysqlTable ;
"""
+
+
+ // test for quotation marks in int
+ sql """ drop table if exists ${exMysqlTable1} """
+ sql """ CREATE EXTERNAL TABLE `${exMysqlTable1}` (
+ `id` bigint(20) NULL,
+ `t_id` bigint(20) NULL,
+ `name` text NULL
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb6",
+ "table_type"="mysql"
+ );
+ """
+ order_qt_sql """ select * from $exMysqlTable1 where id in
('639215401565159424') and id='639215401565159424'; """
+ order_qt_sql """ select * from $exMysqlTable1 where id in
(639215401565159424) and id=639215401565159424; """
+ order_qt_sql """ select * from $exMysqlTable1 where id in
('639215401565159424') ; """
+
+
+ // test for decimal
+ sql """ drop table if exists ${exMysqlTable2} """
+ sql """ CREATE EXTERNAL TABLE `${exMysqlTable2}` (
+ `id` varchar(32) NULL DEFAULT "",
+ `user_name` varchar(32) NULL DEFAULT "",
+ `member_list` DECIMAL(10,3)
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb7",
+ "table_type"="mysql"
+ );
+ """
+ order_qt_sql """ select * from ${exMysqlTable2} order by member_list;
"""
+
+
+ // test for 'With in in select smt and group by will cause missing
from GROUP BY'
+ sql """ drop table if exists ${exMysqlTable} """
+ sql """ CREATE EXTERNAL TABLE `${exMysqlTable}` (
+ `date` date NOT NULL COMMENT "",
+ `uid` varchar(64) NOT NULL,
+ `stat_type` int(11) NOT NULL COMMENT "",
+ `price` varchar(255) NULL COMMENT "price"
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb8",
+ "table_type"="mysql"
+ );
+ """
+ order_qt_sql """ select date, sum(if(stat_type in (1), 1, 0)) from
${exMysqlTable} group by date; """
+
+
+ // test for DATE_ADD
+ sql """ drop table if exists ${exMysqlTable1} """
+ sql """ CREATE EXTERNAL TABLE `${exMysqlTable1}` (
+ c_date date NULL
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb9",
+ "table_type"="mysql"
+ );
+ """
+ order_qt_sql """ select DATE_ADD(c_date, INTERVAL 1 month) as c from
${exMysqlTable1} order by c; """
+
+
+ // test for count(1) of subquery
+ // this external table will use doris_test.ex_tb2
+ sql """ drop table if exists ${exMysqlTable2} """
+ sql """
+ CREATE EXTERNAL TABLE ${exMysqlTable2} (
+ `id` int(11) NOT NULL,
+ `count_value` varchar(20) NULL
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb2",
+ "table_type"="mysql"
+ );
+ """
+ order_qt_sql """ select count(1) from (select '2022' as dt, sum(id)
from ${exMysqlTable2}) a; """
+
+
+
+ // test for 'select * from (select 1 as a) b full outer join (select
2 as a) c using(a)'
+ // this external table will use doris_test.ex_tb0
+ sql """ drop table if exists ${exMysqlTable} """
+ sql """
+ CREATE EXTERNAL TABLE ${exMysqlTable} (
+ `id` int(11) NOT NULL COMMENT "主键id",
+ `name` string NULL COMMENT "名字"
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb0",
+ "table_type"="mysql"
+ );
+ """
+ order_qt_sql """
+ select * from
+ (select id as a from ${exMysqlTable} where id = 111) b
+ full outer join
+ (select id as a from ${exMysqlTable} where id = 112) c
+ using(a);
+ """
+
+
+ // test for 'select CAST(NULL AS CHAR(1))'
+ // this external table will use doris_test.ex_tb9
+ sql """ drop table if exists ${exMysqlTable1} """
+ sql """
+ CREATE EXTERNAL TABLE ${exMysqlTable1} (
+ c_date date NULL
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb9",
+ "table_type"="mysql"
+ );
+ """
+ order_qt_sql """
+ select CAST(c_date AS CHAR(1)) as a from ${exMysqlTable1} order by
a;
+ """
+
+
+ // test for string sort
+ // this external table will use doris_test.ex_tb7
+ sql """ drop table if exists ${exMysqlTable2} """
+ sql """
+ CREATE EXTERNAL TABLE ${exMysqlTable2} (
+ `date` date NOT NULL COMMENT "",
+ `uid` varchar(64) NOT NULL,
+ `stat_type` int(11) NOT NULL COMMENT "",
+ `price` varchar(255) NULL COMMENT "price"
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb8",
+ "table_type"="mysql"
+ );
+ """
+ order_qt_sql """
+ select * from
+ (select uid as a, uid as b, uid as c, 6 from ${exMysqlTable2}
where stat_type = 2
+ union all
+ select '汇总' as a, '汇总' as b, '汇总' as c, 6) a
+ order by 1,2,3,4;
+ """
+
+
+ // test for query int without quotation marks
+ sql """ drop table if exists ${exMysqlTable} """
+ sql """
+ CREATE EXTERNAL TABLE ${exMysqlTable} (
+ `aa` varchar(200) NULL COMMENT "",
+ `bb` int NULL COMMENT "",
+ `cc` bigint NULL COMMENT ""
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb10",
+ "table_type"="mysql"
+ );
+ """
+ order_qt_sql """
+ select t.aa, count(if(t.bb in (1,2) ,true ,null)) as c from
${exMysqlTable} t group by t.aa order by c;
+ """
+
+
+ // test for wrong result
+ sql """ drop table if exists ${exMysqlTable1} """
+ sql """ drop table if exists ${exMysqlTable2} """
+ sql """
+ CREATE EXTERNAL TABLE ${exMysqlTable1} (
+ `aa` varchar(200) NULL COMMENT "",
+ `bb` int NULL COMMENT ""
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb11",
+ "table_type"="mysql"
+ );
+ """
+ sql """
+ CREATE EXTERNAL TABLE ${exMysqlTable2} (
+ `cc` varchar(200) NULL COMMENT "",
+ `dd` int NULL COMMENT ""
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb12",
+ "table_type"="mysql"
+ );
+ """
+ order_qt_sql """
+ select t.* from ( select * from ${exMysqlTable1} t1 left join
${exMysqlTable2} t2 on t1.aa=t2.cc ) t
+ where dayofweek(current_date())=2 order by aa;
+ """
+
+
+ // test for crash be sql
+ sql """ drop table if exists ${exMysqlTable} """
+ sql """
+ CREATE EXTERNAL TABLE ${exMysqlTable} (
+ name varchar(128),
+ age INT,
+ idCode varchar(128),
+ cardNo varchar(128),
+ number varchar(128),
+ birthday DATETIME,
+ country varchar(128),
+ gender varchar(128),
+ covid BOOLEAN
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb13",
+ "table_type"="mysql"
+ );
+ """
+ order_qt_sql """
+ SELECT count(1) FROM (WITH t1 AS ( WITH t AS ( SELECT * FROM
${exMysqlTable})
+ SELECT idCode, COUNT(1) as dataAmount,ROUND(COUNT(1) /
tableWithSum.sumResult,4) as proportion,
+ MD5(idCode) as virtuleUniqKey FROM t,(SELECT COUNT(1) as
sumResult from t) tableWithSum
+ GROUP BY idCode ,tableWithSum.sumResult )
+ SELECT idCode,dataAmount, (CASE WHEN t1.virtuleUniqKey =
tableWithMaxId.max_virtuleUniqKey THEN
+ ROUND(proportion + calcTheTail, 4) ELSE proportion END)
proportion FROM t1,
+ (SELECT (1 - sum(t1.proportion)) as calcTheTail FROM t1 )
tableWithTail,
+ (SELECT virtuleUniqKey as max_virtuleUniqKey FROM t1 ORDER BY
proportion DESC LIMIT 1 ) tableWithMaxId
+ ORDER BY idCode) t_aa;
+ """
}
}
+
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
