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 1fe9bced25 [test](jdbc)add more mysql jdbc test case (#14475)
1fe9bced25 is described below
commit 1fe9bced25703a308547f5b067b153a6a05fa5e0
Author: lsy3993 <[email protected]>
AuthorDate: Tue Nov 22 21:14:10 2022 +0800
[test](jdbc)add more mysql jdbc test case (#14475)
---
.../docker-compose/mysql/init/03-create-table.sql | 53 +++++++
.../docker-compose/mysql/init/04-insert.sql | 25 ++++
.../data/jdbc_p0/test_jdbc_query_mysql.out | 15 ++
.../suites/jdbc_p0/test_jdbc_query_mysql.groovy | 154 ++++++++++++++++++++-
4 files changed, 245 insertions(+), 2 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 84f06ccab3..d09fc95f63 100644
--- a/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
+++ b/docker/thirdparties/docker-compose/mysql/init/03-create-table.sql
@@ -131,4 +131,57 @@ CREATE TABLE doris_test.ex_tb13 (
covid BOOLEAN
);
+CREATE TABLE doris_test.ex_tb14 (
+ tid varchar(128),
+ log_time date,
+ dt date,
+ cmd varchar(128),
+ dp_from varchar(128)
+);
+
+CREATE TABLE doris_test.ex_tb15 (
+ col1 varchar(10) NULL ,
+ col2 varchar(10) NULL ,
+ col3 varchar(10) NULL ,
+ col4 int(11) NULL ,
+ col5 double NULL ,
+ col6 double NULL ,
+ col7 int(11) NULL ,
+ col8 int(11) NULL ,
+ col9 int(11) NULL ,
+ col10 varchar(10) NULL ,
+ col11 varchar(10) NULL ,
+ col12 datetime NULL
+);
+
+CREATE TABLE doris_test.ex_tb16 (
+ `id` bigint(20) NOT NULL COMMENT '',
+ `name` varchar(192) NOT NULL COMMENT '',
+ `is_delete` tinyint(4) NULL,
+ `create_uid` bigint(20) NULL,
+ `modify_uid` bigint(20) NULL,
+ `ctime` bigint(20) NULL,
+ `mtime` bigint(20) NULL
+);
+
+CREATE TABLE doris_test.ex_tb17 (
+ `id` bigint(20) NULL,
+ `media_order_id` int(11) NULL,
+ `supplier_id` int(11) NULL,
+ `agent_policy_type` tinyint(4) NULL,
+ `agent_policy` decimal(6, 2) NULL,
+ `capital_type` bigint(20) NULL,
+ `petty_cash_type` tinyint(4) NULL,
+ `recharge_amount` decimal(10, 2) NULL,
+ `need_actual_amount` decimal(10, 2) NULL,
+ `voucher_url` varchar(765) NULL,
+ `ctime` bigint(20) NULL,
+ `mtime` bigint(20) NULL,
+ `is_delete` tinyint(4) NULL,
+ `media_remark` text NULL,
+ `account_number` varchar(765) NULL,
+ `currency_type` tinyint(4) NULL,
+ `order_source` tinyint(4) NULL
+);
+
diff --git a/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
b/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
index dd80c229be..ebe89a15ed 100644
--- a/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
@@ -1090,4 +1090,29 @@ insert into doris_test.ex_tb13 values
('张三6',11,'124314567','123','321312','1999-02-13','中国','男',false),
('张三7',11,'123445167','123','321312','1998-02-13','中国','男',false);
+insert into doris_test.ex_tb14 values
+('abc', '2022-11-02 20:30:00', '2022-11-02', '8011', 'agdtb'),
+('bca', '2022-11-02 20:31:00', '2022-11-02', '8012', 'vivo'),
+('123', '2022-11-02 20:32:00', '2022-11-02', '8011', 'oppo');
+
+INSERT INTO doris_test.ex_tb15 VALUES
+('2022-11-04','2022-10-31','2022-11-04',62,5410345144.6,72113869936.064819,21,10,16,'-','-','2022-11-04
17:40:19');
+
+INSERT INTO doris_test.ex_tb16
(id,name,is_delete,create_uid,modify_uid,ctime,mtime) VALUES
+(1,'e',0,7,5,6,3), (2,'b',0,1,5,4,5), (4,'b',0,3,4,2,7), (4,'c',0,3,9,3,7),
(7,'d',0,6,1,5,6), (8,'c',0,1,7,1,5),
+(2,'a',0,3,4,1,6), (2,'c',0,5,7,9,1), (3,'e',0,2,4,3,4), (5,'e',0,5,7,9,2),
(6,'a',0,1,1,8,8), (3,'d',0,2,7,1,5),
+(6,'b',0,3,9,1,6), (3,'a',0,7,9,4,8), (5,'b',0,6,6,2,9), (7,'a',0,1,1,3,8),
(9,'c',0,8,3,9,8), (5,'c',0,8,5,7,6),
+(6,'e',0,1,9,7,6), (8,'e',0,4,4,5,4), (1,'d',0,7,6,1,7), (4,'a',0,5,7,4,1),
(9,'e',0,9,1,9,7), (1,'b',0,4,4,8,8),
+(1,'c',0,9,9,5,4), (3,'b',0,4,9,8,1), (9,'b',0,2,1,4,2), (2,'d',0,4,4,8,4),
(4,'d',0,1,5,6,4), (5,'a',0,1,2,2,1),
+(7,'b',0,3,2,8,1), (9,'a',0,8,3,9,1), (1,'a',0,4,3,6,8), (2,'e',0,6,4,7,8),
(6,'d',0,1,2,4,7), (7,'c',0,3,7,7,1),
+(5,'d',0,6,2,7,7), (6,'c',0,3,1,3,8), (7,'e',0,6,1,3,7), (8,'a',0,3,2,8,2),
(8,'b',0,4,9,4,9), (9,'d',0,6,6,5,3);
+
+INSERT INTO doris_test.ex_tb17
(id,media_order_id,supplier_id,agent_policy_type,agent_policy,capital_type,petty_cash_type,recharge_amount,need_actual_amount,voucher_url,ctime,mtime,is_delete,media_remark,account_number,currency_type,order_source)
VALUES
+(2,8,9,8,2900.42,1,6,97486621.73,59634489.39,'c',3,2,0,'a','e',7,4),(3,5,7,3,6276.86,8,9,32758730.38,10260499.72,'c',8,1,0,'d','c',9,2),
+(6,3,6,8,7601.25,4,9,49117098.47,46499188.8,'c',3,3,0,'c','d',4,8),
(8,3,6,7,3683.85,5,7,26056250.91,1127755.43,'b',7,6,0,'d','b',4,7),
+(1,6,1,1,2099.18,3,8,1554296.82,68781940.49,'d',8,5,0,'d','a',7,9),
(4,3,7,5,2449,6,3,91359059.28,64743145.92,'e',7,8,0,'b','d',8,4),
+(7,3,2,8,5297.81,9,3,23753694.2,96930000.64,'c',7,2,0,'b','e',1,5),
(9,3,9,1,4785.38,1,5,95199488.12,94869703.42,'a',4,4,0,'c','d',2,4),
+(5,6,4,5,9137.82,2,7,26526675.7,90098303.36,'a',6,7,0,'d','e',4,1);
+
+
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 d4c5108130..9268a51f45 100644
--- a/regression-test/data/jdbc_p0/test_jdbc_query_mysql.out
+++ b/regression-test/data/jdbc_p0/test_jdbc_query_mysql.out
@@ -1094,6 +1094,21 @@ c 1 c 1
-- !sql --
8
+-- !sql --
+0
+
+-- !sql --
+false
+
+-- !sql --
+\N
+
+-- !sql --
+3 1970-01-01 1970-01-01 1427427954.900000000 0E-9 0E-9
0E-9 0E-9 0E-9 6, 7, 2, 9 H, G, E, A 7601.25, 3683.85, 2449,
5297.81, 4785.38 4, 5, 6, 9, A 9, 7, C, 5 a, b, e, d, c
+5 1970-01-01 1970-01-01 163793651.900000000 0E-9 0E-9
0E-9 0E-9 0E-9 7 C 6276.86 8 9 d, a, b, c, e
+6 1970-01-01 1970-01-01 113878186.900000000
106106702.800000000 0E-9 0E-9 0E-9 343909702.450000000 1, 4
A, E 2099.18, 9137.82 C, B 8, 7 e, d, b, c, a
+8 1970-01-01 1970-01-01 487433108.650000000 0E-9 0E-9
0E-9 0E-9 0E-9 9 H 2900.42 A 6 c, e, b, a, d
+
-- !sql1 --
1025
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 892c4e8c0e..9e3ffd8f8b 100644
--- a/regression-test/suites/jdbc_p0/test_jdbc_query_mysql.groovy
+++ b/regression-test/suites/jdbc_p0/test_jdbc_query_mysql.groovy
@@ -524,8 +524,7 @@ suite("test_jdbc_query_mysql", "p0") {
);
"""
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;
+ select t.* from ( select * from ${exMysqlTable1} t1 left join
${exMysqlTable2} t2 on t1.aa=t2.cc ) t order by aa;
"""
@@ -562,6 +561,156 @@ suite("test_jdbc_query_mysql", "p0") {
ORDER BY idCode) t_aa;
"""
+
+ // test for query like
+ sql """ drop table if exists ${exMysqlTable1} """
+ sql """
+ CREATE EXTERNAL TABLE ${exMysqlTable1} (
+ tid varchar(128),
+ log_time date,
+ dt date,
+ cmd varchar(128),
+ dp_from varchar(128)
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb14",
+ "table_type"="mysql"
+ );
+ """
+ order_qt_sql """
+ select APPROX_COUNT_DISTINCT(tid) as counts
+ from ${exMysqlTable1}
+ where log_time >= '2022-11-02 20:00:00' AND log_time <
'2022-11-02 21:00:00'
+ and dt = '2022-11-02'
+ and cmd = '8011' and tid is not null and tid != ''
+ and (dp_from like '%gdt%' or dp_from like '%vivo%' or dp_from
like '%oppo%');
+ """
+
+
+ // test for IFNULL, IFNULL and get_json_str
+ // this external table will use doris_test.ex_tb1
+ sql """ drop table if exists ${exMysqlTable} """
+ sql """
+ CREATE EXTERNAL TABLE ${exMysqlTable} (
+ id varchar(128)
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb1",
+ "table_type"="mysql"
+ );
+ """
+ order_qt_sql """ select IFNULL(get_json_string(id, "\$.k1"),
'SUCCESS')= 'FAIL' from ${exMysqlTable}; """
+ order_qt_sql """ select
CONCAT(SPLIT_PART(reverse(id),'.',1),".",IFNULL(SPLIT_PART(reverse(id),'.',2),'
')) from ${exMysqlTable}; """
+
+
+ // test for complex query cause be core
+ sql """ drop table if exists ${exMysqlTable1} """
+ sql """ drop table if exists ${exMysqlTable2} """
+ sql """
+ CREATE EXTERNAL TABLE ${exMysqlTable1} (
+ `id` bigint(20) NOT NULL COMMENT '',
+ `name` varchar(192) NOT NULL COMMENT '',
+ `is_delete` tinyint(4) NULL,
+ `create_uid` bigint(20) NULL,
+ `modify_uid` bigint(20) NULL,
+ `ctime` bigint(20) NULL,
+ `mtime` bigint(20) NULL
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb16",
+ "table_type"="mysql"
+ );
+ """
+ sql """
+ CREATE EXTERNAL TABLE ${exMysqlTable2} (
+ `id` bigint(20) NULL,
+ `media_order_id` int(11) NULL,
+ `supplier_id` int(11) NULL,
+ `agent_policy_type` tinyint(4) NULL,
+ `agent_policy` decimal(6, 2) NULL,
+ `capital_type` bigint(20) NULL,
+ `petty_cash_type` tinyint(4) NULL,
+ `recharge_amount` decimal(10, 2) NULL,
+ `need_actual_amount` decimal(10, 2) NULL,
+ `voucher_url` varchar(765) NULL,
+ `ctime` bigint(20) NULL,
+ `mtime` bigint(20) NULL,
+ `is_delete` tinyint(4) NULL,
+ `media_remark` text NULL,
+ `account_number` varchar(765) NULL,
+ `currency_type` tinyint(4) NULL,
+ `order_source` tinyint(4) NULL
+ ) ENGINE=JDBC
+ COMMENT "JDBC Mysql 外部表"
+ PROPERTIES (
+ "resource" = "$jdbcResourceMysql57",
+ "table" = "ex_tb17",
+ "table_type"="mysql"
+ );
+ """
+ order_qt_sql """
+ with tmp_media_purchase as (
+ select media_order_id, supplier_id, agent_policy_type,
agent_policy, capital_type, petty_cash_type,
+ recharge_amount, need_actual_amount, voucher_url, m.`ctime`,
m.`mtime`, m.`is_delete`, media_remark,
+ account_number, currency_type, order_source, `name`
+ from ${exMysqlTable2} m left join ${exMysqlTable1} s on s.id =
m.supplier_id where m.is_delete = 0),
+ t1 as (select media_order_id, from_unixtime(MIN(ctime), '%Y-%m-%d') AS
first_payment_date,
+ from_unixtime(max(ctime), '%Y-%m-%d') AS last_payment_date,
+ sum(IFNULL(recharge_amount, 0.00)) recharge_total_amount,
+ sum(case when capital_type = '2' then IFNULL(recharge_amount,
0.00) else 0.00 end) as petty_amount,
+ sum(case when capital_type = '2' and petty_cash_type = '1' then
IFNULL(recharge_amount, 0.00) else 0.00 end) as petty_change_amount,
+ sum(case when capital_type = '2' and petty_cash_type = '2' then
IFNULL(recharge_amount, 0.00) else 0.00 end) as petty_recharge_amount,
+ sum(case when capital_type = '2' and petty_cash_type = '3' then
IFNULL(recharge_amount, 0.00) else 0.00 end) as petty_return_amount,
+ sum(case when capital_type = '3' then IFNULL(need_actual_amount,
0.00) else 0.00 end) as return_goods_amount,
+ GROUP_CONCAT(distinct cast(supplier_id as varchar (12)))
supplier_id_list
+ from tmp_media_purchase group by media_order_id),
+ t2 as (select media_order_id, GROUP_CONCAT(distinct (case
agent_policy_type
+ when '1' then 'A' when '2' then 'B' when '3' then 'C' when '4' then
'D' when '5' then 'E' when '6' then 'F'
+ when '7' then 'G' when '8' then 'H' when '9' then 'I' when '10' then
'J' when '11' then 'K' when '12' then 'L'
+ when '13' then 'M' else agent_policy_type end)) agent_policy_type_list
+ from tmp_media_purchase group by media_order_id),
+ t3 as (select media_order_id, GROUP_CONCAT(distinct cast(agent_policy
as varchar (12))) agent_policy_list
+ from tmp_media_purchase group by media_order_id),
+ t4 as (select media_order_id, GROUP_CONCAT(distinct (case capital_type
+ when '1' then 'A' when '2' then 'B' when '3' then 'C' else
capital_type end)) capital_type_list
+ from tmp_media_purchase group by media_order_id),
+ t5 as (select media_order_id, GROUP_CONCAT(distinct (case
petty_cash_type
+ when '1' then 'A' when '2' then 'B' when '3' then 'C' else
petty_cash_type end)) petty_cash_type_list
+ from tmp_media_purchase group by media_order_id),
+ t6 as (select media_order_id, GROUP_CONCAT(distinct `name`)
company_name_list
+ from tmp_media_purchase group by media_order_id)
+ select distinct tmp_media_purchase.`media_order_id`,
+ first_payment_date,
+ last_payment_date,
+ recharge_total_amount,
+ petty_amount,
+ petty_change_amount,
+ petty_recharge_amount,
+ petty_return_amount,
+ return_goods_amount,
+ supplier_id_list,
+ agent_policy_type_list,
+ agent_policy_list,
+ capital_type_list,
+ petty_cash_type_list,
+ company_name_list
+ from tmp_media_purchase
+ left join t1 on tmp_media_purchase.media_order_id = t1.media_order_id
+ left join t2 on tmp_media_purchase.media_order_id = t2.media_order_id
+ left join t3 on tmp_media_purchase.media_order_id = t3.media_order_id
+ left join t4 on tmp_media_purchase.media_order_id = t4.media_order_id
+ left join t5 on tmp_media_purchase.media_order_id = t5.media_order_id
+ left join t6 on tmp_media_purchase.media_order_id = t6.media_order_id
+ order by tmp_media_purchase.media_order_id
+ """
+
+
// test for aggregate
order_qt_sql1 """ SELECT COUNT(true) FROM $jdbcMysql57Table1 """
order_qt_sql2 """ SELECT COUNT(*) FROM $jdbcMysql57Table1 WHERE k7 <
k8 """
@@ -632,3 +781,4 @@ suite("test_jdbc_query_mysql", "p0") {
+
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
