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 be904132b60 [fix](test) fix some unstable external p0 test cases
(#42685)
be904132b60 is described below
commit be904132b6023a7076c035b057a661c62fe884c5
Author: daidai <[email protected]>
AuthorDate: Tue Oct 29 22:19:28 2024 +0800
[fix](test) fix some unstable external p0 test cases (#42685)
## Proposed changes
1. external_table_p0/hive/ddl/test_hive_ddl.groovy
2. remove test_mysql_jdbc_driver5_catalog.groovy and merge it into
test_mysql_jdbc_catalog.groovy
---------
Co-authored-by: morningman <[email protected]>
---
.../org/apache/doris/datasource/CatalogMgr.java | 11 +-
.../datasource/hive/event/AlterDatabaseEvent.java | 4 +-
.../datasource/hive/event/CreateDatabaseEvent.java | 2 +-
.../datasource/hive/event/DropDatabaseEvent.java | 2 +-
.../external_table_p0/hive/ddl/test_hive_ddl.out | 1 +
.../jdbc/test_mysql_jdbc_catalog.out | 445 +++++++++++++++-
.../jdbc/test_mysql_jdbc_driver5_catalog.out | 420 ---------------
.../jdbc/test_pg_jdbc_catalog.out | 3 -
.../hive/ddl/test_hive_ddl.groovy | 7 +-
.../jdbc/test_mysql_jdbc_catalog.groovy | 53 +-
.../jdbc/test_mysql_jdbc_driver5_catalog.groovy | 566 ---------------------
11 files changed, 479 insertions(+), 1035 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogMgr.java
b/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogMgr.java
index 3b8551da144..93a4c1b19e6 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogMgr.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/datasource/CatalogMgr.java
@@ -695,7 +695,7 @@ public class CatalogMgr implements Writable,
GsonPostProcessable {
}
}
- public void unregisterExternalDatabase(String dbName, String catalogName,
boolean ignoreIfNotExists)
+ public void unregisterExternalDatabase(String dbName, String catalogName)
throws DdlException {
CatalogIf catalog = nameToCatalog.get(catalogName);
if (catalog == null) {
@@ -704,17 +704,10 @@ public class CatalogMgr implements Writable,
GsonPostProcessable {
if (!(catalog instanceof ExternalCatalog)) {
throw new DdlException("Only support drop ExternalCatalog
databases");
}
- DatabaseIf db = catalog.getDbNullable(dbName);
- if (db == null) {
- if (!ignoreIfNotExists) {
- throw new DdlException("Database " + dbName + " does not exist
in catalog " + catalog.getName());
- }
- return;
- }
((HMSExternalCatalog) catalog).unregisterDatabase(dbName);
}
- public void registerExternalDatabaseFromEvent(String dbName, String
catalogName, boolean ignoreIfExists)
+ public void registerExternalDatabaseFromEvent(String dbName, String
catalogName)
throws DdlException {
CatalogIf catalog = nameToCatalog.get(catalogName);
if (catalog == null) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/event/AlterDatabaseEvent.java
b/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/event/AlterDatabaseEvent.java
index 334930861b6..8f293260058 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/event/AlterDatabaseEvent.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/event/AlterDatabaseEvent.java
@@ -87,8 +87,8 @@ public class AlterDatabaseEvent extends MetastoreEvent {
catalogName, dbAfter.getName());
return;
}
-
Env.getCurrentEnv().getCatalogMgr().unregisterExternalDatabase(dbBefore.getName(),
catalogName, true);
-
Env.getCurrentEnv().getCatalogMgr().registerExternalDatabaseFromEvent(dbAfter.getName(),
catalogName, true);
+
Env.getCurrentEnv().getCatalogMgr().unregisterExternalDatabase(dbBefore.getName(),
catalogName);
+
Env.getCurrentEnv().getCatalogMgr().registerExternalDatabaseFromEvent(dbAfter.getName(),
catalogName);
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/event/CreateDatabaseEvent.java
b/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/event/CreateDatabaseEvent.java
index b11a9eaa072..2d81377f4b6 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/event/CreateDatabaseEvent.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/event/CreateDatabaseEvent.java
@@ -55,7 +55,7 @@ public class CreateDatabaseEvent extends MetastoreEvent {
protected void process() throws MetastoreNotificationException {
try {
logInfo("catalogName:[{}],dbName:[{}]", catalogName, dbName);
-
Env.getCurrentEnv().getCatalogMgr().registerExternalDatabaseFromEvent(dbName,
catalogName, true);
+
Env.getCurrentEnv().getCatalogMgr().registerExternalDatabaseFromEvent(dbName,
catalogName);
} catch (DdlException e) {
throw new MetastoreNotificationException(
getMsgWithEventInfo("Failed to process event"), e);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/event/DropDatabaseEvent.java
b/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/event/DropDatabaseEvent.java
index 0649106661e..6ab089232b9 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/event/DropDatabaseEvent.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/datasource/hive/event/DropDatabaseEvent.java
@@ -55,7 +55,7 @@ public class DropDatabaseEvent extends MetastoreEvent {
try {
logInfo("catalogName:[{}],dbName:[{}]", catalogName, dbName);
Env.getCurrentEnv().getCatalogMgr()
- .unregisterExternalDatabase(dbName, catalogName, true);
+ .unregisterExternalDatabase(dbName, catalogName);
} catch (DdlException e) {
throw new MetastoreNotificationException(
getMsgWithEventInfo("Failed to process event"), e);
diff --git a/regression-test/data/external_table_p0/hive/ddl/test_hive_ddl.out
b/regression-test/data/external_table_p0/hive/ddl/test_hive_ddl.out
index eab813cf5f3..96d17545c9b 100644
--- a/regression-test/data/external_table_p0/hive/ddl/test_hive_ddl.out
+++ b/regression-test/data/external_table_p0/hive/ddl/test_hive_ddl.out
@@ -58,3 +58,4 @@ false 1 1000 \N
true 1 1000 2.3
true 1 1000 2.3
true 1 1000 2.3
+
diff --git
a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out
index 4e250143aba..1caeec713e2 100644
--- a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out
+++ b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_catalog.out
@@ -243,22 +243,13 @@ information_schema
mysql
-- !specified_database_3 --
-DORIS
-Doris
-doris
-information_schema
-init_db
-mysql
-show_test_do_not_modify
-- !specified_database_4 --
-information_schema
-mysql
-- !ex_tb1 --
{"k1":"v1", "k2":"v2"}
--- !mysql_all_types_count--
+-- !mysql_all_types_count --
4
-- !mysql_all_types --
@@ -445,3 +436,437 @@ t2 text Yes false \N NONE
varchar varchar(65533) Yes true \N
int_u bigint Yes false \N NONE
+-- !sql --
+internal
+
+-- !sql --
+mysql_jdbc_catalog
+
+-- !ex_tb0 --
+111 abc
+112 abd
+113 abe
+114 abf
+115 abg
+
+-- !in_tb --
+111 abc
+112 abd
+113 abe
+114 abf
+115 abg
+
+-- !ex_tb1 --
+{"k1":"v1", "k2":"v2"}
+
+-- !ex_tb2 --
+123 10
+123 15
+123 20
+
+-- !ex_tb3 --
+mus plat_code 1001169339 1590381433914 1590420872639 11
1006061 beijing
+mus plat_code 1001169339 1590402594411 1590420872639 11
1006061 beijing
+mus plat_code 1001169339 1590406790026 1590420872639 11
1006061 beijing
+mus plat_code 1001169339 1590420482288 1590420872639 11
1006061 beijing
+mus plat_code 1001169339 1590420872639 1590420872639 11
1006061 beijing
+
+-- !ex_tb4 --
+1 111 2021-09-01T07:01:01 2021-09-01T08:01:01 1
+2 112 2021-09-02T07:01:01 2021-09-02T08:01:01 1
+3 113 0001-01-01T00:00 2021-12-01T08:01:01 2
+5 115 2021-09-01T07:02:01 2021-09-01T08:01:04 4
+6 116 2021-10-01T07:03:01 2022-09-01T08:02:05 5
+
+-- !ex_tb5 --
+1 test_apply_id 123321 zhangsan zhangsan ready ok
2 2022-01-01T02:03:04
+
+-- !ex_tb6 --
+639215401565159424 1143681147589283841 test
+639237839376089088 1143681147589283841 test123
+
+-- !ex_tb7 --
+2 sim 1.000
+2 sim 1.001
+2 sim 1.002
+
+-- !ex_tb8 --
+2022-07-15 2222 1 \N
+2022-07-15 ddddd 2 0.5
+
+-- !ex_tb9 --
+\N
+2022-01-01
+
+-- !ex_tb10 --
+a 1 2
+b 1 2
+c 1 2
+d 3 2
+
+-- !ex_tb11 --
+a 1
+b 1
+c 1
+
+-- !ex_tb12 --
+a 1
+b 1
+c 1
+
+-- !ex_tb13 --
+张三0 11 1234567 123 321312 1999-02-13T00:00 中国 男 0
+张三1 11 12345678 123 321312 1999-02-13T00:00 中国
男 0
+张三2 11 12345671 123 321312 1999-02-13T00:00 中国
男 0
+张三3 11 12345673 123 321312 1999-02-13T00:00 中国
男 0
+张三4 11 123456711 123 321312 1999-02-13T00:00 中国
男 0
+张三5 11 1232134567 123 321312 1999-02-13T00:00 中国
男 0
+张三6 11 124314567 123 321312 1999-02-13T00:00 中国
男 0
+张三7 11 123445167 123 321312 1998-02-13T00:00 中国
男 0
+
+-- !ex_tb14 --
+123 2022-11-02 2022-11-02 8011 oppo
+abc 2022-11-02 2022-11-02 8011 agdtb
+bca 2022-11-02 2022-11-02 8012 vivo
+
+-- !ex_tb15 --
+2022-11-04 2022-10-31 2022-11-04 62 5.4103451446E9
7.211386993606482E10 21 10 16 - -
2022-11-04T17:40:19
+
+-- !ex_tb16 --
+1 a 0 4 3 6 8
+1 b 0 4 4 8 8
+1 c 0 9 9 5 4
+1 d 0 7 6 1 7
+1 e 0 7 5 6 3
+2 a 0 3 4 1 6
+2 b 0 1 5 4 5
+2 c 0 5 7 9 1
+2 d 0 4 4 8 4
+2 e 0 6 4 7 8
+3 a 0 7 9 4 8
+3 b 0 4 9 8 1
+3 d 0 2 7 1 5
+3 e 0 2 4 3 4
+4 a 0 5 7 4 1
+4 b 0 3 4 2 7
+4 c 0 3 9 3 7
+4 d 0 1 5 6 4
+5 a 0 1 2 2 1
+5 b 0 6 6 2 9
+5 c 0 8 5 7 6
+5 d 0 6 2 7 7
+5 e 0 5 7 9 2
+6 a 0 1 1 8 8
+6 b 0 3 9 1 6
+6 c 0 3 1 3 8
+6 d 0 1 2 4 7
+6 e 0 1 9 7 6
+7 a 0 1 1 3 8
+7 b 0 3 2 8 1
+7 c 0 3 7 7 1
+7 d 0 6 1 5 6
+7 e 0 6 1 3 7
+8 a 0 3 2 8 2
+8 b 0 4 9 4 9
+8 c 0 1 7 1 5
+8 e 0 4 4 5 4
+9 a 0 8 3 9 1
+9 b 0 2 1 4 2
+9 c 0 8 3 9 8
+9 d 0 6 6 5 3
+9 e 0 9 1 9 7
+
+-- !ex_tb17 --
+1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
+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
+4 3 7 5 2449.00 6 3 91359059.28
64743145.92 e 7 8 0 b d 8 4
+5 6 4 5 9137.82 2 7 26526675.70
90098303.36 a 6 7 0 d e 4 1
+6 3 6 8 7601.25 4 9 49117098.47
46499188.80 c 3 3 0 c d 4 8
+7 3 2 8 5297.81 9 3 23753694.20
96930000.64 c 7 2 0 b e 1 5
+8 3 6 7 3683.85 5 7 26056250.91
1127755.43 b 7 6 0 d b 4 7
+9 3 9 1 4785.38 1 5 95199488.12
94869703.42 a 4 4 0 c d 2 4
+
+-- !ex_tb18 --
+-128 255 -32768 65535 -8388608 16777215
-9223372036854775808 -2147483648 2147483647 4294967295 33.14
422113.141 2342.23 aa asdawdasdaasdasd aaa bbbbbbbb
xaqwdqwdqwdqd asdas
+1 1 1 1 1 1 1 1 1 1
3.14 13.141 2342.23 aa asdawdasdaasdasd aaa bbbbbbbb
xaqwdqwdqwdqdwqwdqwdqd asdadwqdqwddqwdsadqwdas
+127 255 32767 65535 8388607 16777215 9223372036854775807
-2147483648 2147483647 4294967295 33.14 422113.141 2342.23
aa asdawdasdaasdasd aaa bbbbbbbb xaqwdqwdqwdqd
asdadwqdqwdsadqwdas
+
+-- !ex_tb19 --
+2022-11-27 07:09:51 2022 2022-11-27T07:09:51
2022-11-27T07:09:51
+
+-- !ex_tb20 --
+1.12345 1.12345 1.12345 1.12345 1.12345 1.12345
+123456789012345678901234567890123.12345
12345678901234567890123456789012.12345
1234567890123456789012345678901234.12345
123456789012345678901234567890123.12345
123456789012345678901234567890123456789012345678901234567890.12345
123456789012345678901234567890123456789012345678901234567890.12345
+
+-- !ex_tb21_1 --
+2 2
+
+-- !ex_tb21_2 --
+2 2
+
+-- !ex_tb21_3 --
+1 1
+2 2
+
+-- !ex_tb21_4 --
+2 2
+
+-- !ex_tb21_5 --
+1 1
+2 2
+
+-- !ex_tb21_6 --
+1 1
+
+-- !ex_tb21_7 --
+2 1
+
+-- !ex_tb21_8 --
+2 2
+
+-- !information_schema --
+processlist
+
+-- !dt --
+2023-06-17T10:00 2023-06-17T10:00:01 2023-06-17T10:00:02
2023-06-17T10:00:03 2023-06-17T10:00:04 2023-06-17T10:00:05
2023-06-17T10:00:06
+
+-- !dt_null --
+\N
+0001-01-01T00:00
+2023-06-17T10:00
+
+-- !test_dz --
+1 \N
+2 2022-01-01
+3 0001-01-01
+
+-- !test_filter_not --
+张三1 11 12345678 123 321312 1999-02-13T00:00 中国
男 0
+张三2 11 12345671 123 321312 1999-02-13T00:00 中国
男 0
+张三3 11 12345673 123 321312 1999-02-13T00:00 中国
男 0
+张三4 11 123456711 123 321312 1999-02-13T00:00 中国
男 0
+张三5 11 1232134567 123 321312 1999-02-13T00:00 中国
男 0
+张三6 11 124314567 123 321312 1999-02-13T00:00 中国
男 0
+张三7 11 123445167 123 321312 1998-02-13T00:00 中国
男 0
+
+-- !test_insert1 --
+doris1 18
+
+-- !test_insert2 --
+doris2 19
+doris3 20
+
+-- !test_insert3 --
+doris2 19
+doris2 19
+doris3 20
+doris3 20
+
+-- !test_insert4 --
+1 abcHa1.12345 1.123450xkalowadawd 2022-10-01 3.14159 1
2 0 100000 1.2345678 24.000 07:09:51 2022
2022-11-27T07:09:51 2022-11-27T07:09:51
+
+-- !specified_database_1 --
+doris_test
+information_schema
+mysql
+
+-- !specified_database_2 --
+doris_test
+information_schema
+mysql
+
+-- !specified_database_3 --
+
+-- !specified_database_4 --
+
+-- !ex_tb1 --
+{"k1":"v1", "k2":"v2"}
+
+-- !mysql_all_types_count --
+4
+
+-- !mysql_all_types --
+\N 302 0 502 602 4.14159 0.0 6.14159 \N -124
-302 2013 -402 -502 -602 \N 2012-10-26T02:08:39
2013-10-26T08:09:18 -5.14145 0.0 -7.1400 row2 \N
09:11:09 text2 0xE86F6C6C6F20576F726C67 \N \N 0x2F
\N 0x88656C6C9F Value3
+201 301 401 501 601 3.14159 4.1415926 5.14159 1
-123 -301 2012 -401 -501 -601 2012-10-30
2012-10-25T12:05:36 2012-10-25T08:08:08 -4.14145 -5.1400000001
-6.1400 row1 line1 09:09:09 text1 0x48656C6C6F20576F726C64
{"age": 30, "city": "London", "name": "Alice"} Option1,Option3 0x2A
0x48656C6C6F00000000000000 0x48656C6C6F Value2
+202 302 402 502 602 4.14159 5.1415926 6.14159 0
-124 -302 2013 -402 -502 -602 2012-11-01
2012-10-26T02:08:39 2013-10-26T08:09:18 -5.14145 -6.1400000001
-7.1400 row2 line2 09:11:09 text2 0xE86F6C6C6F20576F726C67
{"age": 18, "city": "ChongQing", "name": "Gaoxin"} Option1,Option2 0x2F
0x58676C6C6F00000000000000 0x88656C6C9F Value3
+203 303 403 503 603 7.14159 8.1415926 9.14159 0
\N -402 2017 -602 -902 -1102 2012-11-02 \N
2013-10-27T08:11:18 -5.14145 -6.1400000000001 -7.1400 row3
line3 09:11:09 text3 0xE86F6C6C6F20576F726C67 {"age": 24,
"city": "ChongQing", "name": "ChenQi"} Option2 0x2F
0x58676C6C6F00000000000000 \N Value1
+
+-- !select_insert_all_types --
+\N 302 0 502 602 4.14159 0.0 6.14159 \N -124
-302 2013 -402 -502 -602 \N 2012-10-26T02:08:39.345700
2013-10-26T08:09:18 -5.14145 0.0 -7.1400 row2 \N
09:11:09 text2 0xE86F6C6C6F20576F726C67 \N \N 0x2F
\N 0x88656C6C9F Value3
+201 301 401 501 601 3.14159 4.1415926 5.14159 1
-123 -301 2012 -401 -501 -601 2012-10-30
2012-10-25T12:05:36.345700 2012-10-25T08:08:08 -4.14145
-5.1400000001 -6.1400 row1 line1 09:09:09 text1
0x48656C6C6F20576F726C64 {"age":30,"city":"London","name":"Alice"}
Option1,Option3 0x2A 0x48656C6C6F00000000000000 0x48656C6C6F Value2
+202 302 402 502 602 4.14159 5.1415926 6.14159 0
-124 -302 2013 -402 -502 -602 2012-11-01
2012-10-26T02:08:39.345700 2013-10-26T08:09:18 -5.14145
-6.1400000001 -7.1400 row2 line2 09:11:09 text2
0xE86F6C6C6F20576F726C67 {"age":18,"city":"ChongQing","name":"Gaoxin"}
Option1,Option2 0x2F 0x58676C6C6F00000000000000 0x88656C6C9F Value3
+203 303 403 503 603 7.14159 8.1415926 9.14159 0
\N -402 2017 -602 -902 -1102 2012-11-02 \N
2013-10-27T08:11:18 -5.14145 -6.1400000000001 -7.1400 row3
line3 09:11:09 text3 0xE86F6C6C6F20576F726C67
{"age":24,"city":"ChongQing","name":"ChenQi"} Option2 0x2F
0x58676C6C6F00000000000000 \N Value1
+
+-- !ctas --
+\N 302 0 502 602 4.14159 0.0 6.14159 \N -124
-302 2013 -402 -502 -602 \N 2012-10-26T02:08:39
2013-10-26T08:09:18 -5.14145 0.0 -7.1400 row2 \N
09:11:09 text2 0xE86F6C6C6F20576F726C67 \N \N 0x2F
\N 0x88656C6C9F Value3
+201 301 401 501 601 3.14159 4.1415926 5.14159 1
-123 -301 2012 -401 -501 -601 2012-10-30
2012-10-25T12:05:36 2012-10-25T08:08:08 -4.14145 -5.1400000001
-6.1400 row1 line1 09:09:09 text1 0x48656C6C6F20576F726C64
{"age": 30, "city": "London", "name": "Alice"} Option1,Option3 0x2A
0x48656C6C6F00000000000000 0x48656C6C6F Value2
+202 302 402 502 602 4.14159 5.1415926 6.14159 0
-124 -302 2013 -402 -502 -602 2012-11-01
2012-10-26T02:08:39 2013-10-26T08:09:18 -5.14145 -6.1400000001
-7.1400 row2 line2 09:11:09 text2 0xE86F6C6C6F20576F726C67
{"age": 18, "city": "ChongQing", "name": "Gaoxin"} Option1,Option2 0x2F
0x58676C6C6F00000000000000 0x88656C6C9F Value3
+203 303 403 503 603 7.14159 8.1415926 9.14159 0
\N -402 2017 -602 -902 -1102 2012-11-02 \N
2013-10-27T08:11:18 -5.14145 -6.1400000000001 -7.1400 row3
line3 09:11:09 text3 0xE86F6C6C6F20576F726C67 {"age": 24,
"city": "ChongQing", "name": "ChenQi"} Option2 0x2F
0x58676C6C6F00000000000000 \N Value1
+
+-- !ctas_desc --
+bigint bigint Yes false \N NONE
+bigint_u largeint Yes false \N NONE
+binary text Yes false \N NONE
+bit text Yes false \N NONE
+blob text Yes false \N NONE
+boolean tinyint Yes false \N NONE
+char text Yes false \N NONE
+date date Yes false \N NONE
+datetime datetime Yes false \N NONE
+decimal decimal(12,4) Yes false \N NONE
+decimal_u decimal(19,5) Yes false \N NONE
+double double Yes false \N NONE
+double_u double Yes false \N NONE
+enum text Yes false \N NONE
+float float Yes false \N NONE
+float_u float Yes false \N NONE
+int int Yes false \N NONE
+int_u bigint Yes false \N NONE
+json text Yes false \N NONE
+mediumint int Yes false \N NONE
+mediumint_u int Yes true \N
+set text Yes false \N NONE
+smallint smallint Yes false \N NONE
+smallint_u int Yes true \N
+text text Yes false \N NONE
+time text Yes false \N NONE
+timestamp datetime Yes false \N NONE
+tinyint tinyint Yes false \N NONE
+tinyint_u smallint Yes true \N
+varbinary text Yes false \N NONE
+varchar text Yes false \N NONE
+year smallint Yes false \N NONE
+
+-- !mysql_view --
+10086 4294967295 201
+
+-- !filter1 --
+1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
+
+-- !filter2 --
+1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
+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
+4 3 7 5 2449.00 6 3 91359059.28
64743145.92 e 7 8 0 b d 8 4
+5 6 4 5 9137.82 2 7 26526675.70
90098303.36 a 6 7 0 d e 4 1
+6 3 6 8 7601.25 4 9 49117098.47
46499188.80 c 3 3 0 c d 4 8
+7 3 2 8 5297.81 9 3 23753694.20
96930000.64 c 7 2 0 b e 1 5
+8 3 6 7 3683.85 5 7 26056250.91
1127755.43 b 7 6 0 d b 4 7
+9 3 9 1 4785.38 1 5 95199488.12
94869703.42 a 4 4 0 c d 2 4
+
+-- !filter3 --
+1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
+
+-- !date_trunc --
+2023-06-17T10:00
+
+-- !money_format --
+1
+
+-- !date_add_year --
+2 2022-01-01
+
+-- !date_add_month --
+2 2022-01-01
+
+-- !date_add_week --
+2 2022-01-01
+
+-- !date_add_day --
+2 2022-01-01
+
+-- !date_add_hour --
+2 2022-01-01
+
+-- !date_add_min --
+2 2022-01-01
+
+-- !date_add_sec --
+2 2022-01-01
+
+-- !date_sub_year --
+2 2022-01-01
+
+-- !date_sub_month --
+2 2022-01-01
+
+-- !date_sub_week --
+2 2022-01-01
+
+-- !date_sub_day --
+2 2022-01-01
+
+-- !date_sub_hour --
+2 2022-01-01
+
+-- !date_sub_min --
+2 2022-01-01
+
+-- !date_sub_sec --
+2 2022-01-01
+
+-- !auto_default_t1 --
+0
+
+-- !auto_default_t2 --
+0
+
+-- !sql --
+doris_1
+doris_2
+doris_3
+doris_test
+information_schema
+init_db
+mysql
+show_test_do_not_modify
+
+-- !sql --
+doris_1
+doris_2
+doris_3
+
+-- !sql --
+DORIS
+
+-- !sql --
+Doris
+
+-- !sql --
+doris
+
+-- !sql --
+1
+
+-- !sql --
+1
+
+-- !sql --
+10 0 7744
+11 0 -94
+12 16970 95
+13 0 7023
+14 0 1
+15 3679 -11
+16 0 -1079
+17 -22 0
+18 30995 0
+19 0 -79
+5 0 -127
+6 14680 -26424
+7 -22270 12722
+8 0 0
+9 0 0
+
+-- !sql --
+
+-- !sql --
+int_u bigint Yes true \N
+text varchar(65533) Yes true \N
+t2 text Yes false \N NONE
+
+-- !sql --
+varchar varchar(65533) Yes true \N
+int_u bigint Yes false \N NONE
+
diff --git
a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_driver5_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_driver5_catalog.out
deleted file mode 100644
index 736f2b57b4a..00000000000
---
a/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_driver5_catalog.out
+++ /dev/null
@@ -1,420 +0,0 @@
--- This file is automatically generated. You should know what you did if you
want to edit this
--- !sql --
-internal
-
--- !show_db --
-DORIS
-Doris
-doris
-doris_test
-information_schema
-init_db
-mysql
-show_test_do_not_modify
-
--- !sql --
-internal
-
--- !sql --
-mysql_jdbc5_catalog
-
--- !ex_tb0 --
-111 abc
-112 abd
-113 abe
-114 abf
-115 abg
-
--- !in_tb --
-111 abc
-112 abd
-113 abe
-114 abf
-115 abg
-
--- !ex_tb1 --
-{"k1":"v1", "k2":"v2"}
-
--- !ex_tb2 --
-123 10
-123 15
-123 20
-
--- !ex_tb3 --
-mus plat_code 1001169339 1590381433914 1590420872639 11
1006061 beijing
-mus plat_code 1001169339 1590402594411 1590420872639 11
1006061 beijing
-mus plat_code 1001169339 1590406790026 1590420872639 11
1006061 beijing
-mus plat_code 1001169339 1590420482288 1590420872639 11
1006061 beijing
-mus plat_code 1001169339 1590420872639 1590420872639 11
1006061 beijing
-
--- !ex_tb4 --
-1 111 2021-09-01T07:01:01 2021-09-01T08:01:01 1
-2 112 2021-09-02T07:01:01 2021-09-02T08:01:01 1
-3 113 0001-01-01T00:00 2021-12-01T08:01:01 2
-5 115 2021-09-01T07:02:01 2021-09-01T08:01:04 4
-6 116 2021-10-01T07:03:01 2022-09-01T08:02:05 5
-
--- !ex_tb5 --
-1 test_apply_id 123321 zhangsan zhangsan ready ok
2 2022-01-01T02:03:04
-
--- !ex_tb6 --
-639215401565159424 1143681147589283841 test
-639237839376089088 1143681147589283841 test123
-
--- !ex_tb7 --
-2 sim 1.000
-2 sim 1.001
-2 sim 1.002
-
--- !ex_tb8 --
-2022-07-15 2222 1 \N
-2022-07-15 ddddd 2 0.5
-
--- !ex_tb9 --
-\N
-2022-01-01
-
--- !ex_tb10 --
-a 1 2
-b 1 2
-c 1 2
-d 3 2
-
--- !ex_tb11 --
-a 1
-b 1
-c 1
-
--- !ex_tb12 --
-a 1
-b 1
-c 1
-
--- !ex_tb13 --
-张三0 11 1234567 123 321312 1999-02-13T00:00 中国 男 0
-张三1 11 12345678 123 321312 1999-02-13T00:00 中国
男 0
-张三2 11 12345671 123 321312 1999-02-13T00:00 中国
男 0
-张三3 11 12345673 123 321312 1999-02-13T00:00 中国
男 0
-张三4 11 123456711 123 321312 1999-02-13T00:00 中国
男 0
-张三5 11 1232134567 123 321312 1999-02-13T00:00 中国
男 0
-张三6 11 124314567 123 321312 1999-02-13T00:00 中国
男 0
-张三7 11 123445167 123 321312 1998-02-13T00:00 中国
男 0
-
--- !ex_tb14 --
-123 2022-11-02 2022-11-02 8011 oppo
-abc 2022-11-02 2022-11-02 8011 agdtb
-bca 2022-11-02 2022-11-02 8012 vivo
-
--- !ex_tb15 --
-2022-11-04 2022-10-31 2022-11-04 62 5.4103451446E9
7.211386993606482E10 21 10 16 - -
2022-11-04T17:40:19
-
--- !ex_tb16 --
-1 a 0 4 3 6 8
-1 b 0 4 4 8 8
-1 c 0 9 9 5 4
-1 d 0 7 6 1 7
-1 e 0 7 5 6 3
-2 a 0 3 4 1 6
-2 b 0 1 5 4 5
-2 c 0 5 7 9 1
-2 d 0 4 4 8 4
-2 e 0 6 4 7 8
-3 a 0 7 9 4 8
-3 b 0 4 9 8 1
-3 d 0 2 7 1 5
-3 e 0 2 4 3 4
-4 a 0 5 7 4 1
-4 b 0 3 4 2 7
-4 c 0 3 9 3 7
-4 d 0 1 5 6 4
-5 a 0 1 2 2 1
-5 b 0 6 6 2 9
-5 c 0 8 5 7 6
-5 d 0 6 2 7 7
-5 e 0 5 7 9 2
-6 a 0 1 1 8 8
-6 b 0 3 9 1 6
-6 c 0 3 1 3 8
-6 d 0 1 2 4 7
-6 e 0 1 9 7 6
-7 a 0 1 1 3 8
-7 b 0 3 2 8 1
-7 c 0 3 7 7 1
-7 d 0 6 1 5 6
-7 e 0 6 1 3 7
-8 a 0 3 2 8 2
-8 b 0 4 9 4 9
-8 c 0 1 7 1 5
-8 e 0 4 4 5 4
-9 a 0 8 3 9 1
-9 b 0 2 1 4 2
-9 c 0 8 3 9 8
-9 d 0 6 6 5 3
-9 e 0 9 1 9 7
-
--- !ex_tb17 --
-1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
-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
-4 3 7 5 2449.00 6 3 91359059.28
64743145.92 e 7 8 0 b d 8 4
-5 6 4 5 9137.82 2 7 26526675.70
90098303.36 a 6 7 0 d e 4 1
-6 3 6 8 7601.25 4 9 49117098.47
46499188.80 c 3 3 0 c d 4 8
-7 3 2 8 5297.81 9 3 23753694.20
96930000.64 c 7 2 0 b e 1 5
-8 3 6 7 3683.85 5 7 26056250.91
1127755.43 b 7 6 0 d b 4 7
-9 3 9 1 4785.38 1 5 95199488.12
94869703.42 a 4 4 0 c d 2 4
-
--- !ex_tb18 --
--128 255 -32768 65535 -8388608 16777215
-9223372036854775808 -2147483648 2147483647 4294967295 33.14
422113.141 2342.23 aa asdawdasdaasdasd aaa bbbbbbbb
xaqwdqwdqwdqd asdas
-1 1 1 1 1 1 1 1 1 1
3.14 13.141 2342.23 aa asdawdasdaasdasd aaa bbbbbbbb
xaqwdqwdqwdqdwqwdqwdqd asdadwqdqwddqwdsadqwdas
-127 255 32767 65535 8388607 16777215 9223372036854775807
-2147483648 2147483647 4294967295 33.14 422113.141 2342.23
aa asdawdasdaasdasd aaa bbbbbbbb xaqwdqwdqwdqd
asdadwqdqwdsadqwdas
-
--- !ex_tb19 --
-2022-11-27 07:09:51 2022 2022-11-27T07:09:51
2022-11-27T07:09:51
-
--- !ex_tb20 --
-1.12345 1.12345 1.12345 1.12345 1.12345 1.12345
-123456789012345678901234567890123.12345
12345678901234567890123456789012.12345
1234567890123456789012345678901234.12345
123456789012345678901234567890123.12345
123456789012345678901234567890123456789012345678901234567890.12345
123456789012345678901234567890123456789012345678901234567890.12345
-
--- !ex_tb21_1 --
-2 2
-
--- !ex_tb21_2 --
-2 2
-
--- !ex_tb21_3 --
-1 1
-2 2
-
--- !ex_tb21_4 --
-2 2
-
--- !ex_tb21_5 --
-1 1
-2 2
-
--- !ex_tb21_6 --
-1 1
-
--- !ex_tb21_7 --
-2 1
-
--- !ex_tb21_8 --
-2 2
-
--- !information_schema --
-processlist
-
--- !dt --
-2023-06-17T10:00 2023-06-17T10:00:01 2023-06-17T10:00:02
2023-06-17T10:00:03 2023-06-17T10:00:04 2023-06-17T10:00:05
2023-06-17T10:00:06
-
--- !dt_null --
-\N
-0001-01-01T00:00
-2023-06-17T10:00
-
--- !test_dz --
-1 \N
-2 2022-01-01
-3 0001-01-01
-
--- !test_filter_not --
-张三1 11 12345678 123 321312 1999-02-13T00:00 中国
男 0
-张三2 11 12345671 123 321312 1999-02-13T00:00 中国
男 0
-张三3 11 12345673 123 321312 1999-02-13T00:00 中国
男 0
-张三4 11 123456711 123 321312 1999-02-13T00:00 中国
男 0
-张三5 11 1232134567 123 321312 1999-02-13T00:00 中国
男 0
-张三6 11 124314567 123 321312 1999-02-13T00:00 中国
男 0
-张三7 11 123445167 123 321312 1998-02-13T00:00 中国
男 0
-
--- !test_insert1 --
-doris1 18
-
--- !test_insert2 --
-doris2 19
-doris3 20
-
--- !test_insert3 --
-doris2 19
-doris2 19
-doris3 20
-doris3 20
-
--- !test_insert4 --
-1 abcHa1.12345 1.123450xkalowadawd 2022-10-01 3.14159 1
2 0 100000 1.2345678 24.000 07:09:51 2022
2022-11-27T07:09:51 2022-11-27T07:09:51
-
--- !specified_database_1 --
-doris_test
-information_schema
-mysql
-
--- !specified_database_2 --
-doris_test
-information_schema
-mysql
-
--- !specified_database_3 --
-DORIS
-Doris
-doris
-information_schema
-init_db
-mysql
-show_test_do_not_modify
-
--- !specified_database_4 --
-information_schema
-mysql
-
--- !ex_tb1 --
-{"k1":"v1", "k2":"v2"}
-
--- !mysql_all_types --
-\N 302 0 502 602 4.14159 0.0 6.14159 \N -124
-302 2013 -402 -502 -602 \N 2012-10-26T02:08:39
2013-10-26T08:09:18 -5.14145 0.0 -7.1400 row2 \N
09:11:09 text2 0xE86F6C6C6F20576F726C67 \N \N 0x2F
\N 0x88656C6C9F Value3
-201 301 401 501 601 3.14159 4.1415926 5.14159 1
-123 -301 2012 -401 -501 -601 2012-10-30
2012-10-25T12:05:36 2012-10-25T08:08:08 -4.14145 -5.1400000001
-6.1400 row1 line1 09:09:09 text1 0x48656C6C6F20576F726C64
{"age": 30, "city": "London", "name": "Alice"} Option1,Option3 0x2A
0x48656C6C6F00000000000000 0x48656C6C6F Value2
-202 302 402 502 602 4.14159 5.1415926 6.14159 0
-124 -302 2013 -402 -502 -602 2012-11-01
2012-10-26T02:08:39 2013-10-26T08:09:18 -5.14145 -6.1400000001
-7.1400 row2 line2 09:11:09 text2 0xE86F6C6C6F20576F726C67
{"age": 18, "city": "ChongQing", "name": "Gaoxin"} Option1,Option2 0x2F
0x58676C6C6F00000000000000 0x88656C6C9F Value3
-203 303 403 503 603 7.14159 8.1415926 9.14159 0
\N -402 2017 -602 -902 -1102 2012-11-02 \N
2013-10-27T08:11:18 -5.14145 -6.1400000000001 -7.1400 row3
line3 09:11:09 text3 0xE86F6C6C6F20576F726C67 {"age": 24,
"city": "ChongQing", "name": "ChenQi"} Option2 0x2F
0x58676C6C6F00000000000000 \N Value1
-
--- !select_insert_all_types --
-\N 302 0 502 602 4.14159 0.0 6.14159 \N -124
-302 2013 -402 -502 -602 \N 2012-10-26T02:08:39.345700
2013-10-26T08:09:18 -5.14145 0.0 -7.1400 row2 \N
09:11:09 text2 0xE86F6C6C6F20576F726C67 \N \N 0x2F
\N 0x88656C6C9F Value3
-201 301 401 501 601 3.14159 4.1415926 5.14159 1
-123 -301 2012 -401 -501 -601 2012-10-30
2012-10-25T12:05:36.345700 2012-10-25T08:08:08 -4.14145
-5.1400000001 -6.1400 row1 line1 09:09:09 text1
0x48656C6C6F20576F726C64 {"age":30,"city":"London","name":"Alice"}
Option1,Option3 0x2A 0x48656C6C6F00000000000000 0x48656C6C6F Value2
-202 302 402 502 602 4.14159 5.1415926 6.14159 0
-124 -302 2013 -402 -502 -602 2012-11-01
2012-10-26T02:08:39.345700 2013-10-26T08:09:18 -5.14145
-6.1400000001 -7.1400 row2 line2 09:11:09 text2
0xE86F6C6C6F20576F726C67 {"age":18,"city":"ChongQing","name":"Gaoxin"}
Option1,Option2 0x2F 0x58676C6C6F00000000000000 0x88656C6C9F Value3
-203 303 403 503 603 7.14159 8.1415926 9.14159 0
\N -402 2017 -602 -902 -1102 2012-11-02 \N
2013-10-27T08:11:18 -5.14145 -6.1400000000001 -7.1400 row3
line3 09:11:09 text3 0xE86F6C6C6F20576F726C67
{"age":24,"city":"ChongQing","name":"ChenQi"} Option2 0x2F
0x58676C6C6F00000000000000 \N Value1
-
--- !ctas --
-\N 302 0 502 602 4.14159 0.0 6.14159 \N -124
-302 2013 -402 -502 -602 \N 2012-10-26T02:08:39
2013-10-26T08:09:18 -5.14145 0.0 -7.1400 row2 \N
09:11:09 text2 0xE86F6C6C6F20576F726C67 \N \N 0x2F
\N 0x88656C6C9F Value3
-201 301 401 501 601 3.14159 4.1415926 5.14159 1
-123 -301 2012 -401 -501 -601 2012-10-30
2012-10-25T12:05:36 2012-10-25T08:08:08 -4.14145 -5.1400000001
-6.1400 row1 line1 09:09:09 text1 0x48656C6C6F20576F726C64
{"age": 30, "city": "London", "name": "Alice"} Option1,Option3 0x2A
0x48656C6C6F00000000000000 0x48656C6C6F Value2
-202 302 402 502 602 4.14159 5.1415926 6.14159 0
-124 -302 2013 -402 -502 -602 2012-11-01
2012-10-26T02:08:39 2013-10-26T08:09:18 -5.14145 -6.1400000001
-7.1400 row2 line2 09:11:09 text2 0xE86F6C6C6F20576F726C67
{"age": 18, "city": "ChongQing", "name": "Gaoxin"} Option1,Option2 0x2F
0x58676C6C6F00000000000000 0x88656C6C9F Value3
-203 303 403 503 603 7.14159 8.1415926 9.14159 0
\N -402 2017 -602 -902 -1102 2012-11-02 \N
2013-10-27T08:11:18 -5.14145 -6.1400000000001 -7.1400 row3
line3 09:11:09 text3 0xE86F6C6C6F20576F726C67 {"age": 24,
"city": "ChongQing", "name": "ChenQi"} Option2 0x2F
0x58676C6C6F00000000000000 \N Value1
-
--- !ctas_desc --
-bigint bigint Yes false \N NONE
-bigint_u largeint Yes false \N NONE
-binary text Yes false \N NONE
-bit text Yes false \N NONE
-blob text Yes false \N NONE
-boolean tinyint Yes false \N NONE
-char text Yes false \N NONE
-date date Yes false \N NONE
-datetime datetime Yes false \N NONE
-decimal decimal(12,4) Yes false \N NONE
-decimal_u decimal(19,5) Yes false \N NONE
-double double Yes false \N NONE
-double_u double Yes false \N NONE
-enum text Yes false \N NONE
-float float Yes false \N NONE
-float_u float Yes false \N NONE
-int int Yes false \N NONE
-int_u bigint Yes false \N NONE
-json text Yes false \N NONE
-mediumint int Yes false \N NONE
-mediumint_u int Yes true \N
-set text Yes false \N NONE
-smallint smallint Yes false \N NONE
-smallint_u int Yes true \N
-text text Yes false \N NONE
-time text Yes false \N NONE
-timestamp datetime Yes false \N NONE
-tinyint tinyint Yes false \N NONE
-tinyint_u smallint Yes true \N
-varbinary text Yes false \N NONE
-varchar text Yes false \N NONE
-year smallint Yes false \N NONE
-
--- !mysql_view --
-10086 4294967295 201
-
--- !filter1 --
-1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
-
--- !filter2 --
-1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
-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
-4 3 7 5 2449.00 6 3 91359059.28
64743145.92 e 7 8 0 b d 8 4
-5 6 4 5 9137.82 2 7 26526675.70
90098303.36 a 6 7 0 d e 4 1
-6 3 6 8 7601.25 4 9 49117098.47
46499188.80 c 3 3 0 c d 4 8
-7 3 2 8 5297.81 9 3 23753694.20
96930000.64 c 7 2 0 b e 1 5
-8 3 6 7 3683.85 5 7 26056250.91
1127755.43 b 7 6 0 d b 4 7
-9 3 9 1 4785.38 1 5 95199488.12
94869703.42 a 4 4 0 c d 2 4
-
--- !filter3 --
-1 6 1 1 2099.18 3 8 1554296.82
68781940.49 d 8 5 0 d a 7 9
-
--- !date_trunc --
-2023-06-17T10:00
-
--- !money_format --
-1
-
--- !date_add_year --
-2 2022-01-01
-
--- !date_add_month --
-2 2022-01-01
-
--- !date_add_week --
-2 2022-01-01
-
--- !date_add_day --
-2 2022-01-01
-
--- !date_add_hour --
-2 2022-01-01
-
--- !date_add_min --
-2 2022-01-01
-
--- !date_add_sec --
-2 2022-01-01
-
--- !date_sub_year --
-2 2022-01-01
-
--- !date_sub_month --
-2 2022-01-01
-
--- !date_sub_week --
-2 2022-01-01
-
--- !date_sub_day --
-2 2022-01-01
-
--- !date_sub_hour --
-2 2022-01-01
-
--- !date_sub_min --
-2 2022-01-01
-
--- !date_sub_sec --
-2 2022-01-01
-
--- !auto_default_t1 --
-0
-
--- !auto_default_t2 --
-0
-
--- !sql --
-doris_1
-doris_2
-doris_3
-doris_test
-information_schema
-init_db
-mysql
-show_test_do_not_modify
-
--- !sql --
-doris_1
-doris_2
-doris_3
-
--- !sql --
-DORIS
-
--- !sql --
-Doris
-
--- !sql --
-doris
-
diff --git
a/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
index 892d6a8e382..19502357457 100644
--- a/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
+++ b/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
@@ -2130,9 +2130,6 @@ true abc def 2022-10-11 1.234 1
2 99 2022-10-22T10:59:59 34.123
-- !filter4 --
234 bcd
--- !filter4_old --
-234 bcd
-
-- !test12 --
1 false 12.123456 10.16.10.14/32 10.16.10.14
ff:ff:ff:ff:ff:aa 1010101010 01010 1 {"id": 1}
2 false 12.123456 10.16.10.14/32 10.16.10.14
ff:ff:ff:ff:ff:ff 0000001010 0000001010 2 {"id": 1}
diff --git
a/regression-test/suites/external_table_p0/hive/ddl/test_hive_ddl.groovy
b/regression-test/suites/external_table_p0/hive/ddl/test_hive_ddl.groovy
index 626f6b2bfbf..ed605f15d08 100644
--- a/regression-test/suites/external_table_p0/hive/ddl/test_hive_ddl.groovy
+++ b/regression-test/suites/external_table_p0/hive/ddl/test_hive_ddl.groovy
@@ -96,8 +96,10 @@ suite("test_hive_ddl",
"p0,external,hive,external_docker,external_docker_hive")
// create and insert default value is supported on hive3, we can
test default hive version 2.3
sql """switch ${catalog_name}"""
- sql """ create database if not exists `test_hive_default_val`
- """
+ sql """ drop database if exists `test_hive_default_val` """
+
+ sql """ create database if not exists `test_hive_default_val` """
+
sql """use `test_hive_default_val`"""
test {
sql """
@@ -111,7 +113,6 @@ suite("test_hive_ddl",
"p0,external,hive,external_docker,external_docker_hive")
"""
exception "failed to create table from hms client. reason:
java.lang.UnsupportedOperationException: Table with default values is not
supported if the hive version is less than 3.0. Can set 'hive.version' to 3.0
in properties."
}
- sql """DROP DATABASE `test_hive_default_val`"""
test {
sql """
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
index ce23a4f658c..a88ba550eea 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_catalog.groovy
@@ -24,7 +24,16 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
String bucket = getS3BucketName()
String driver_url =
"https://${bucket}.${s3_endpoint}/regression/jdbc_driver/mysql-connector-java-8.0.25.jar";
// String driver_url = "mysql-connector-java-8.0.25.jar"
- if (enabled != null && enabled.equalsIgnoreCase("true")) {
+ if (enabled == null || !enabled.equalsIgnoreCase("true")) {
+ return;
+ }
+
+ for (String driver_class :
["com.mysql.cj.jdbc.Driver","com.mysql.jdbc.Driver" ]) {
+ if (driver_class.equals("com.mysql.jdbc.Driver")) {
+ driver_url =
"https://${bucket}.${s3_endpoint}/regression/jdbc_driver/mysql-connector-java-5.1.49.jar";
+ } else {
+ driver_url =
"https://${bucket}.${s3_endpoint}/regression/jdbc_driver/mysql-connector-java-8.0.25.jar";
+ }
String user = "test_jdbc_user";
String pwd = '123456';
def tokens = context.config.jdbcUrl.split('/')
@@ -78,7 +87,7 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"password"="123456",
"jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false&zeroDateTimeBehavior=convertToNull",
"driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.cj.jdbc.Driver"
+ "driver_class" = "${driver_class}"
);"""
sql """use ${internal_db_name}"""
@@ -226,13 +235,13 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"password"="123456",
"jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
"driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.cj.jdbc.Driver",
+ "driver_class" = "${driver_class}",
"only_specified_database" = "true"
);"""
sql """switch ${catalog_name}"""
- qt_specified_database_1 """ show databases; """
+ qt_specified_database_1 """ show databases; """ // only has
doris_test
sql """ drop catalog if exists ${catalog_name} """
@@ -243,14 +252,14 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"password"="123456",
"jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}?useSSL=false",
"driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.cj.jdbc.Driver",
+ "driver_class" = "${driver_class}",
"only_specified_database" = "true",
"include_database_list" = "doris_test"
);"""
sql """switch ${catalog_name}"""
- qt_specified_database_2 """ show databases; """
+ qt_specified_database_2 """ show databases; """ // only has
doris_test
sql """ drop catalog if exists ${catalog_name} """
@@ -261,14 +270,16 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"password"="123456",
"jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}?useSSL=false",
"driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.cj.jdbc.Driver",
+ "driver_class" = "${driver_class}",
"only_specified_database" = "true",
"exclude_database_list" = "doris_test"
);"""
sql """switch ${catalog_name}"""
- qt_specified_database_3 """ show databases; """
+ List<List<Object>> show_result3 = sql "show databases"
+ assertTrue(show_result3.size() > 0) // has other databases
+ qt_specified_database_3 """ show databases like "doris_test"; """ //
does not have doris_test
sql """ drop catalog if exists ${catalog_name} """
@@ -279,7 +290,7 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"password"="123456",
"jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}?useSSL=false",
"driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.cj.jdbc.Driver",
+ "driver_class" = "${driver_class}",
"only_specified_database" = "true",
"include_database_list" = "doris_test",
"exclude_database_list" = "doris_test"
@@ -287,7 +298,9 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
sql """switch ${catalog_name}"""
- qt_specified_database_4 """ show databases; """
+ List<List<Object>> show_result4 = sql "show databases"
+ assertTrue(show_result4.size() > 0) // has other databases
+ qt_specified_database_4 """ show databases like "doris_test"; """ //
does not have doris_test
sql """ drop catalog if exists ${catalog_name} """
@@ -298,7 +311,7 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"jdbc.password"="123456",
"jdbc.jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
"jdbc.driver_url" = "${driver_url}",
- "jdbc.driver_class" = "com.mysql.cj.jdbc.Driver");
+ "jdbc.driver_class" = "${driver_class}");
"""
sql """ switch ${catalog_name} """
@@ -335,7 +348,7 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"jdbc.password"="123456",
"jdbc.jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
"jdbc.driver_url" = "${driver_url}",
- "jdbc.driver_class" = "com.mysql.cj.jdbc.Driver");
+ "jdbc.driver_class" = "${driver_class}");
"""
qt_mysql_view """ select * from view_catalog.doris_test.mysql_view
order by col_1;"""
sql """ drop catalog if exists view_catalog; """
@@ -347,7 +360,7 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"jdbc.password"="123456",
"jdbc.jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
"jdbc.driver_url" = "${driver_url}",
- "jdbc.driver_class" = "com.mysql.cj.jdbc.Driver");
+ "jdbc.driver_class" = "${driver_class}");
"""
sql """switch mysql_fun_push_catalog"""
@@ -494,7 +507,7 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"password"="123456",
"jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
"driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.cj.jdbc.Driver"
+ "driver_class" = "${driver_class}"
);"""
sql """switch ${catalog_name}"""
@@ -525,7 +538,7 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"password"="123456",
"jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
"driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.cj.jdbc.Driver",
+ "driver_class" = "${driver_class}",
"lower_case_meta_names" = "true",
"meta_names_mapping" = '{"databases": [{"remoteDatabase":
"DORIS","mapping": "doris_1"},{"remoteDatabase": "Doris","mapping":
"doris_2"},{"remoteDatabase": "doris","mapping": "doris_3"}],"tables":
[{"remoteDatabase": "Doris","remoteTable": "DORIS","mapping":
"doris_1"},{"remoteDatabase": "Doris","remoteTable": "Doris","mapping":
"doris_2"},{"remoteDatabase": "Doris","remoteTable": "doris","mapping":
"doris_3"}]}'
);
@@ -546,7 +559,7 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"password"="123456",
"jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
"driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.cj.jdbc.Driver",
+ "driver_class" = "${driver_class}",
"lower_case_table_names" = "true",
"meta_names_mapping" = '{"databases":
[{"remoteDatabase": "DORIS","mapping": "doris_1"},{"remoteDatabase":
"Doris","mapping": "doris_2"},{"remoteDatabase": "doris","mapping":
"doris_3"}],"tables": [{"remoteDatabase": "Doris","remoteTable":
"DORIS","mapping": "doris_1"},{"remoteDatabase": "Doris","remoteTable":
"Doris","mapping": "doris_2"},{"remoteDatabase": "Doris","remoteTable":
"doris","mapping": "doris_3"}]}'
);
@@ -561,7 +574,7 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"password"="123456",
"jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
"driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.cj.jdbc.Driver",
+ "driver_class" = "${driver_class}",
"lower_case_meta_names" = "true",
"meta_names_mapping" = "{\\\"databases\\\":
[{\\\"remoteDatabase\\\": \\\"DORIS\\\",\\\"mapping\\\":
\\\"doris_1\\\"},{\\\"remoteDatabase\\\": \\\"Doris\\\",\\\"mapping\\\":
\\\"doris_2\\\"},{\\\"remoteDatabase\\\": \\\"doris\\\",\\\"mapping\\\":
\\\"doris_3\\\"}],\\\"tables\\\": [{\\\"remoteDatabase\\\":
\\\"Doris\\\",\\\"remoteTable\\\": \\\"DORIS\\\",\\\"mapping\\\":
\\\"doris_1\\\"},{\\\"remoteDatabase\\\": \\\"Doris\\\",\\\"remoteTable\\\":
\\\"Doris\\\",\\\"mapp [...]
);
@@ -576,7 +589,7 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"password"="123456",
"jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false&zeroDateTimeBehavior=convertToNull",
"driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.cj.jdbc.Driver",
+ "driver_class" = "${driver_class}",
"metadata_refresh_interval_sec" = "5"
);"""
@@ -588,7 +601,7 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"password"="123456",
"jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false&zeroDateTimeBehavior=convertToNull",
"driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.cj.jdbc.Driver"
+ "driver_class" = "${driver_class}"
);"""
qt_sql """select count(*) from mysql_rename1.doris_test.ex_tb1;"""
@@ -607,7 +620,7 @@ suite("test_mysql_jdbc_catalog",
"p0,external,mysql,external_docker,external_doc
"password"="123456",
"jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false&zeroDateTimeBehavior=convertToNull",
"driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.cj.jdbc.Driver"
+ "driver_class" = "${driver_class}"
);"""
order_qt_sql """SELECT * FROM
mysql_conjuncts.doris_test.compoundpredicate_test WHERE (pk > 4) OR ((pk < 6 OR
pk > 7) AND col_int_undef_signed < 1);"""
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_driver5_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_driver5_catalog.groovy
deleted file mode 100644
index c82c3aafcd8..00000000000
---
a/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_driver5_catalog.groovy
+++ /dev/null
@@ -1,566 +0,0 @@
-// Licensed to the Apache Software Foundation (ASF) under one
-// or more contributor license agreements. See the NOTICE file
-// distributed with this work for additional information
-// regarding copyright ownership. The ASF licenses this file
-// to you under the Apache License, Version 2.0 (the
-// "License"); you may not use this file except in compliance
-// with the License. You may obtain a copy of the License at
-//
-// http://www.apache.org/licenses/LICENSE-2.0
-//
-// Unless required by applicable law or agreed to in writing,
-// software distributed under the License is distributed on an
-// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-// KIND, either express or implied. See the License for the
-// specific language governing permissions and limitations
-// under the License.
-
-suite("test_mysql_jdbc_driver5_catalog",
"p0,external,mysql,external_docker,external_docker_mysql") {
- qt_sql """select current_catalog()"""
-
- String enabled = context.config.otherConfigs.get("enableJdbcTest")
- String externalEnvIp = context.config.otherConfigs.get("externalEnvIp")
- String s3_endpoint = getS3Endpoint()
- String bucket = getS3BucketName()
- String driver_url =
"https://${bucket}.${s3_endpoint}/regression/jdbc_driver/mysql-connector-java-5.1.49.jar";
- if (enabled != null && enabled.equalsIgnoreCase("true")) {
- String user = "test_jdbc_user";
- String pwd = '123456';
- def tokens = context.config.jdbcUrl.split('/')
- def url = tokens[0] + "//" + tokens[2] + "/" + "information_schema" +
"?"
- String catalog_name = "mysql_jdbc5_catalog";
- String internal_db_name = "regression_test_jdbc_catalog_p0";
- String ex_db_name = "doris_test";
- String mysql_port = context.config.otherConfigs.get("mysql_57_port");
- String inDorisTable = "test_mysql_jdbc_doris_in_tb";
- String ex_tb0 = "ex_tb0";
- String ex_tb1 = "ex_tb1";
- String ex_tb2 = "ex_tb2";
- String ex_tb3 = "ex_tb3";
- String ex_tb4 = "ex_tb4";
- String ex_tb5 = "ex_tb5";
- String ex_tb6 = "ex_tb6";
- String ex_tb7 = "ex_tb7";
- String ex_tb8 = "ex_tb8";
- String ex_tb9 = "ex_tb9";
- String ex_tb10 = "ex_tb10";
- String ex_tb11 = "ex_tb11";
- String ex_tb12 = "ex_tb12";
- String ex_tb13 = "ex_tb13";
- String ex_tb14 = "ex_tb14";
- String ex_tb15 = "ex_tb15";
- String ex_tb16 = "ex_tb16";
- String ex_tb17 = "ex_tb17";
- String ex_tb18 = "ex_tb18";
- String ex_tb19 = "ex_tb19";
- String ex_tb20 = "ex_tb20";
- String ex_tb21 = "test_key_word";
- String test_insert = "test_insert";
- String test_insert2 = "test_insert2";
- String test_insert_all_types = "test_mysql_insert_all_types";
- String test_ctas = "test_mysql_ctas";
- String auto_default_t = "auto_default_t";
- String dt = "dt";
- String dt_null = "dt_null";
- String test_zd = "test_zd"
-
- try_sql("DROP USER ${user}")
- sql """CREATE USER '${user}' IDENTIFIED BY '${pwd}'"""
-
- sql """create database if not exists ${internal_db_name}; """
-
- sql """drop catalog if exists ${catalog_name} """
-
- sql """create catalog if not exists ${catalog_name} properties(
- "type"="jdbc",
- "user"="root",
- "password"="123456",
- "jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false&zeroDateTimeBehavior=convertToNull",
- "driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.jdbc.Driver"
- );"""
- order_qt_show_db """ show databases from ${catalog_name}; """
-
- sql """use ${internal_db_name}"""
- sql """ drop table if exists ${internal_db_name}.${inDorisTable} """
- sql """
- CREATE TABLE ${internal_db_name}.${inDorisTable} (
- `id` INT NULL COMMENT "主键id",
- `name` string NULL COMMENT "名字"
- ) DISTRIBUTED BY HASH(id) BUCKETS 10
- PROPERTIES("replication_num" = "1");
- """
-
- // used for testing all types
- sql """ drop table if exists
${internal_db_name}.${test_insert_all_types} """
- sql """
- CREATE TABLE ${internal_db_name}.${test_insert_all_types} (
- `tinyint_u` SMALLINT,
- `smallint_u` INT,
- `mediumint_u` INT,
- `int_u` BIGINT,
- `bigint_u` LARGEINT,
- `decimal_u` DECIMAL(18, 5),
- `double_u` DOUBLE,
- `float_u` FLOAT,
- `boolean` TINYINT,
- `tinyint` TINYINT,
- `smallint` SMALLINT,
- `year` SMALLINT,
- `mediumint` INT,
- `int` INT,
- `bigint` BIGINT,
- `date` DATE,
- `timestamp` DATETIME(4) null,
- `datetime` DATETIME,
- `float` FLOAT,
- `double` DOUBLE,
- `decimal` DECIMAL(12, 4),
- `char` CHAR(5),
- `varchar` VARCHAR(10),
- `time` STRING,
- `text` STRING,
- `blob` STRING,
- `json` JSON,
- `set` STRING,
- `bit` STRING,
- `binary` STRING,
- `varbinary` STRING,
- `enum` STRING
- ) DISTRIBUTED BY HASH(tinyint_u) BUCKETS 10
- PROPERTIES("replication_num" = "1");
- """
-
- qt_sql """select current_catalog()"""
- sql """switch ${catalog_name}"""
- qt_sql """select current_catalog()"""
- sql """ use ${ex_db_name}"""
-
- order_qt_ex_tb0 """ select id, name from ${ex_tb0} order by id; """
- sql """ insert into internal.${internal_db_name}.${inDorisTable}
select id, name from ${ex_tb0}; """
- order_qt_in_tb """ select id, name from
internal.${internal_db_name}.${inDorisTable} order by id; """
-
- order_qt_ex_tb1 """ select * from ${ex_tb1} order by id; """
- order_qt_ex_tb2 """ select * from ${ex_tb2} order by id; """
- order_qt_ex_tb3 """ select * from ${ex_tb3} order by game_code; """
- order_qt_ex_tb4 """ select * from ${ex_tb4} order by products_id; """
- order_qt_ex_tb5 """ select * from ${ex_tb5} order by id; """
- order_qt_ex_tb6 """ select * from ${ex_tb6} order by id; """
- order_qt_ex_tb7 """ select * from ${ex_tb7} order by id; """
- order_qt_ex_tb8 """ select * from ${ex_tb8} order by uid; """
- order_qt_ex_tb9 """ select * from ${ex_tb9} order by c_date; """
- order_qt_ex_tb10 """ select * from ${ex_tb10} order by aa; """
- order_qt_ex_tb11 """ select * from ${ex_tb11} order by aa; """
- order_qt_ex_tb12 """ select * from ${ex_tb12} order by cc; """
- order_qt_ex_tb13 """ select * from ${ex_tb13} order by name; """
- order_qt_ex_tb14 """ select * from ${ex_tb14} order by tid; """
- order_qt_ex_tb15 """ select * from ${ex_tb15} order by col1; """
- order_qt_ex_tb16 """ select * from ${ex_tb16} order by id; """
- order_qt_ex_tb17 """ select * from ${ex_tb17} order by id; """
- order_qt_ex_tb18 """ select * from ${ex_tb18} order by num_tinyint;
"""
- order_qt_ex_tb19 """ select * from ${ex_tb19} order by date_value; """
- order_qt_ex_tb20 """ select * from ${ex_tb20} order by
decimal_normal; """
- order_qt_ex_tb21_1 """ select `key`, `id` from ${ex_tb21} where `key`
= 2 order by id;"""
- order_qt_ex_tb21_2 """ select `key`, `id` from ${ex_tb21} where `key`
like 2 order by id;"""
- order_qt_ex_tb21_3 """ select `key`, `id` from ${ex_tb21} where `key`
in (1,2) order by id;"""
- order_qt_ex_tb21_4 """ select `key`, `id` from ${ex_tb21} where
abs(`key`) = 2 order by id;"""
- order_qt_ex_tb21_5 """ select `key`, `id` from ${ex_tb21} where `key`
between 1 and 2 order by id;"""
- order_qt_ex_tb21_6 """ select `key`, `id` from ${ex_tb21} where `key`
= case when id = 1 then 1 else 0 end order by id;"""
- order_qt_ex_tb21_7 """ select (`key` +1) as k, `id` from ${ex_tb21}
having abs(k) = 2 order by id;"""
- order_qt_ex_tb21_8 """ select `key` as k, `id` from ${ex_tb21} having
abs(k) = 2 order by id;"""
- order_qt_information_schema """ show tables from information_schema
like "processlist"; """
- order_qt_dt """select * from ${dt}; """
- order_qt_dt_null """select * from ${dt_null} order by 1; """
- order_qt_test_dz """select * from ${test_zd} order by 1; """
- order_qt_test_filter_not """select * from ${ex_tb13} where name not
like '%张三0%' order by 1; """
- explain {
- sql("select `datetime` from all_types where to_date(`datetime`) =
'2012-10-25';")
- contains """ SELECT `datetime` FROM `doris_test`.`all_types` WHERE
(date(`datetime`) = '2012-10-25')"""
- }
-
- explain {
- sql("select /*+ SET_VAR(enable_ext_func_pred_pushdown = false) */
`datetime` from all_types where to_date(`datetime`) = '2012-10-25';")
- contains """SELECT `datetime` FROM `doris_test`.`all_types`"""
- }
-
- // test insert
- String uuid1 = UUID.randomUUID().toString();
- connect(user=user, password="${pwd}", url=url) {
- try {
- sql """ insert into
${catalog_name}.${ex_db_name}.${test_insert} values ('${uuid1}', 'doris1', 18)
"""
- fail()
- } catch (Exception e) {
- log.info(e.getMessage())
- }
- }
-
- sql """GRANT LOAD_PRIV ON ${catalog_name}.${ex_db_name}.${test_insert}
TO ${user}"""
-
- connect(user=user, password="${pwd}", url=url) {
- try {
- sql """ insert into
${catalog_name}.${ex_db_name}.${test_insert} values ('${uuid1}', 'doris1', 18)
"""
- } catch (Exception e) {
- fail();
- }
- }
- order_qt_test_insert1 """ select name, age from ${test_insert} where
id = '${uuid1}' order by age """
-
- String uuid2 = UUID.randomUUID().toString();
- sql """ insert into ${test_insert} values ('${uuid2}', 'doris2', 19),
('${uuid2}', 'doris3', 20) """
- order_qt_test_insert2 """ select name, age from ${test_insert} where
id = '${uuid2}' order by age """
-
- sql """ insert into ${test_insert} select * from ${test_insert} where
id = '${uuid2}' """
- order_qt_test_insert3 """ select name, age from ${test_insert} where
id = '${uuid2}' order by age """
-
- String uuid3 = UUID.randomUUID().toString();
- sql """ INSERT INTO ${test_insert2} VALUES
- ('${uuid3}', true, 'abcHa1.12345', '1.123450xkalowadawd',
'2022-10-01', 3.14159, 1, 2, 0, 100000, 1.2345678, 24.000, '07:09:51', '2022',
'2022-11-27 07:09:51', '2022-11-27 07:09:51'); """
- order_qt_test_insert4 """ select
k1,k2,k3,k4,k5,k6,k7,k8,k9,k10,k11,k12,k13,k14,k15 from ${test_insert2} where
id = '${uuid3}' """
-
- sql """ drop catalog if exists ${catalog_name} """
-
- // test only_specified_database argument
- sql """create catalog if not exists ${catalog_name} properties(
- "type"="jdbc",
- "user"="root",
- "password"="123456",
- "jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
- "driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.jdbc.Driver",
- "only_specified_database" = "true"
- );"""
-
- sql """switch ${catalog_name}"""
-
- qt_specified_database_1 """ show databases; """
-
- sql """ drop catalog if exists ${catalog_name} """
-
- // test only_specified_database and include_database_list argument
- sql """create catalog if not exists ${catalog_name} properties(
- "type"="jdbc",
- "user"="root",
- "password"="123456",
- "jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}?useSSL=false",
- "driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.jdbc.Driver",
- "only_specified_database" = "true",
- "include_database_list" = "doris_test"
- );"""
-
- sql """switch ${catalog_name}"""
-
- qt_specified_database_2 """ show databases; """
-
- sql """ drop catalog if exists ${catalog_name} """
-
- // test only_specified_database and exclude_database_list argument
- sql """create catalog if not exists ${catalog_name} properties(
- "type"="jdbc",
- "user"="root",
- "password"="123456",
- "jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}?useSSL=false",
- "driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.jdbc.Driver",
- "only_specified_database" = "true",
- "exclude_database_list" = "doris_test"
- );"""
-
- sql """switch ${catalog_name}"""
-
- qt_specified_database_3 """ show databases; """
-
- sql """ drop catalog if exists ${catalog_name} """
-
- // test include_database_list and exclude_database_list have
overlapping items case
- sql """create catalog if not exists ${catalog_name} properties(
- "type"="jdbc",
- "user"="root",
- "password"="123456",
- "jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}?useSSL=false",
- "driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.jdbc.Driver",
- "only_specified_database" = "true",
- "include_database_list" = "doris_test",
- "exclude_database_list" = "doris_test"
- );"""
-
- sql """switch ${catalog_name}"""
-
- qt_specified_database_4 """ show databases; """
-
- sql """ drop catalog if exists ${catalog_name} """
-
- // test old create-catalog syntax for compatibility
- sql """ CREATE CATALOG ${catalog_name} PROPERTIES (
- "type"="jdbc",
- "jdbc.user"="root",
- "jdbc.password"="123456",
- "jdbc.jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
- "jdbc.driver_url" = "${driver_url}",
- "jdbc.driver_class" = "com.mysql.jdbc.Driver");
- """
- sql """ switch ${catalog_name} """
-
- sql """ use ${ex_db_name} """
- order_qt_ex_tb1 """ select * from ${ex_tb1} order by id; """
-
- // test all types supported by MySQL
- sql """use doris_test;"""
- qt_mysql_all_types """select * from all_types order by tinyint_u;"""
-
- // test insert into internal.db.table select * from all_types
- sql """ insert into
internal.${internal_db_name}.${test_insert_all_types} select * from all_types;
"""
- order_qt_select_insert_all_types """ select * from
internal.${internal_db_name}.${test_insert_all_types} order by tinyint_u; """
-
- // test CTAS
- sql """ drop table if exists
internal.${internal_db_name}.${test_ctas} """
- sql """ create table internal.${internal_db_name}.${test_ctas}
- PROPERTIES("replication_num" = "1")
- AS select * from all_types;
- """
-
- order_qt_ctas """select * from
internal.${internal_db_name}.${test_ctas} order by tinyint_u;"""
-
- order_qt_ctas_desc """desc
internal.${internal_db_name}.${test_ctas};"""
-
- sql """ drop catalog if exists ${catalog_name} """
-
- // test mysql view
- sql """ drop catalog if exists view_catalog """
- sql """ CREATE CATALOG view_catalog PROPERTIES (
- "type"="jdbc",
- "jdbc.user"="root",
- "jdbc.password"="123456",
- "jdbc.jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
- "jdbc.driver_url" = "${driver_url}",
- "jdbc.driver_class" = "com.mysql.jdbc.Driver");
- """
- qt_mysql_view """ select * from view_catalog.doris_test.mysql_view
order by col_1;"""
- sql """ drop catalog if exists view_catalog; """
-
- sql """ drop catalog if exists mysql_fun_push_catalog """
- sql """ CREATE CATALOG mysql_fun_push_catalog PROPERTIES (
- "type"="jdbc",
- "jdbc.user"="root",
- "jdbc.password"="123456",
- "jdbc.jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
- "jdbc.driver_url" = "${driver_url}",
- "jdbc.driver_class" = "com.mysql.jdbc.Driver");
- """
-
- sql """switch mysql_fun_push_catalog"""
- sql """ use ${ex_db_name}"""
- sql """ set enable_ext_func_pred_pushdown = "true"; """
- order_qt_filter1 """select * from ${ex_tb17} where id = 1; """
- order_qt_filter2 """select * from ${ex_tb17} where 1=1 order by 1; """
- order_qt_filter3 """select * from ${ex_tb17} where id = 1 and 1 = 1;
"""
- order_qt_date_trunc """ SELECT timestamp0 from dt where
DATE_TRUNC(date_sub(timestamp0,INTERVAL 9 HOUR),'hour') > '2011-03-03
17:39:05'; """
- order_qt_money_format """ select k8 from test1 where money_format(k8)
= '1.00'; """
- explain {
- sql("select k8 from test1 where money_format(k8) = '1.00';")
-
- contains "QUERY: SELECT `k8` FROM `doris_test`.`test1`"
- }
- explain {
- sql ("SELECT timestamp0 from dt where
DATE_TRUNC(date_sub(timestamp0,INTERVAL 9 HOUR),'hour') > '2011-03-03
17:39:05';")
-
- contains "QUERY: SELECT `timestamp0` FROM `doris_test`.`dt`"
- }
- explain {
- sql ("SELECT timestamp0 from dt where
DATE_TRUNC(date_sub(timestamp0,INTERVAL 9 HOUR),'hour') > '2011-03-03 17:39:05'
and timestamp0 > '2022-01-01';")
-
- contains "QUERY: SELECT `timestamp0` FROM `doris_test`.`dt` WHERE
(`timestamp0` > '2022-01-01 00:00:00')"
- }
- explain {
- sql ("select k6, k8 from test1 where nvl(k6, null) = 1;")
-
- contains "QUERY: SELECT `k6`, `k8` FROM `doris_test`.`test1` WHERE
((ifnull(`k6`, NULL) = 1))"
- }
- explain {
- sql ("select k6, k8 from test1 where nvl(nvl(k6, null),null) = 1;")
-
- contains "QUERY: SELECT `k6`, `k8` FROM `doris_test`.`test1` WHERE
((ifnull(ifnull(`k6`, NULL), NULL) = 1))"
- }
- sql """ set enable_ext_func_pred_pushdown = "false"; """
- explain {
- sql ("select k6, k8 from test1 where nvl(k6, null) = 1 and k8 =
1;")
-
- contains "QUERY: SELECT `k6`, `k8` FROM `doris_test`.`test1` WHERE
((`k8` = 1))"
- }
- sql """ set enable_ext_func_pred_pushdown = "true"; """
- // test date_add
- order_qt_date_add_year """ select * from test_zd where
date_add(d_z,interval 1 year) = '2023-01-01' order by 1; """
- explain {
- sql("select * from test_zd where date_add(d_z,interval 1 year) =
'2023-01-01' order by 1;")
-
- contains " QUERY: SELECT `id`, `d_z` FROM `doris_test`.`test_zd`
WHERE (`d_z` = '2022-01-01')"
- }
- order_qt_date_add_month """ select * from test_zd where
date_add(d_z,interval 1 month) = '2022-02-01' order by 1; """
- explain {
- sql("select * from test_zd where date_add(d_z,interval 1 month) =
'2022-02-01' order by 1;")
-
- contains " QUERY: SELECT `id`, `d_z` FROM `doris_test`.`test_zd`
WHERE (`d_z` = '2022-01-01')"
- }
- order_qt_date_add_week """ select * from test_zd where
date_add(d_z,interval 1 week) = '2022-01-08' order by 1; """
- explain {
- sql("select * from test_zd where date_add(d_z,interval 1 week) =
'2022-01-08' order by 1;")
-
- contains " QUERY: SELECT `id`, `d_z` FROM `doris_test`.`test_zd`
WHERE (`d_z` = '2022-01-01')"
- }
- order_qt_date_add_day """ select * from test_zd where
date_add(d_z,interval 1 day) = '2022-01-02' order by 1; """
- explain {
- sql("select * from test_zd where date_add(d_z,interval 1 day) =
'2022-01-02' order by 1;")
-
- contains " QUERY: SELECT `id`, `d_z` FROM `doris_test`.`test_zd`
WHERE (`d_z` = '2022-01-01')"
- }
- order_qt_date_add_hour """ select * from test_zd where
date_add(d_z,interval 1 hour) = '2022-01-01 01:00:00' order by 1; """
- explain {
- sql("select * from test_zd where date_add(d_z,interval 1 hour) =
'2022-01-01 01:00:00' order by 1;")
-
- contains " QUERY: SELECT `id`, `d_z` FROM `doris_test`.`test_zd`
WHERE (`d_z` = '2022-01-01')"
- }
- order_qt_date_add_min """ select * from test_zd where
date_add(d_z,interval 1 minute) = '2022-01-01 00:01:00' order by 1; """
- explain {
- sql("select * from test_zd where date_add(d_z,interval 1 minute) =
'2022-01-01 00:01:00' order by 1;")
-
- contains " QUERY: SELECT `id`, `d_z` FROM `doris_test`.`test_zd`
WHERE (`d_z` = '2022-01-01')"
- }
- order_qt_date_add_sec """ select * from test_zd where
date_add(d_z,interval 1 second) = '2022-01-01 00:00:01' order by 1; """
- explain {
- sql("select * from test_zd where date_add(d_z,interval 1 second) =
'2022-01-01 00:00:01' order by 1;")
-
- contains " QUERY: SELECT `id`, `d_z` FROM `doris_test`.`test_zd`
WHERE (`d_z` = '2022-01-01')"
- }
- // date_sub
- order_qt_date_sub_year """ select * from test_zd where
date_sub(d_z,interval 1 year) = '2021-01-01' order by 1; """
- explain {
- sql("select * from test_zd where date_sub(d_z,interval 1 year) =
'2021-01-01' order by 1;")
-
- contains " QUERY: SELECT `id`, `d_z` FROM `doris_test`.`test_zd`
WHERE (`d_z` = '2022-01-01')"
- }
- order_qt_date_sub_month """ select * from test_zd where
date_sub(d_z,interval 1 month) = '2021-12-01' order by 1; """
- explain {
- sql("select * from test_zd where date_sub(d_z,interval 1 month) =
'2021-12-01' order by 1;")
-
- contains " QUERY: SELECT `id`, `d_z` FROM `doris_test`.`test_zd`
WHERE (`d_z` = '2022-01-01')"
- }
- order_qt_date_sub_week """ select * from test_zd where
date_sub(d_z,interval 1 week) = '2021-12-25' order by 1; """
- explain {
- sql("select * from test_zd where date_sub(d_z,interval 1 week) =
'2021-12-25' order by 1;")
-
- contains " QUERY: SELECT `id`, `d_z` FROM `doris_test`.`test_zd`
WHERE (`d_z` = '2022-01-01')"
- }
- order_qt_date_sub_day """ select * from test_zd where
date_sub(d_z,interval 1 day) = '2021-12-31' order by 1; """
- explain {
- sql("select * from test_zd where date_sub(d_z,interval 1 day) =
'2021-12-31' order by 1;")
-
- contains " QUERY: SELECT `id`, `d_z` FROM `doris_test`.`test_zd`
WHERE (`d_z` = '2022-01-01')"
- }
- order_qt_date_sub_hour """ select * from test_zd where
date_sub(d_z,interval 1 hour) = '2021-12-31 23:00:00' order by 1; """
- explain {
- sql("select * from test_zd where date_sub(d_z,interval 1 hour) =
'2021-12-31 23:00:00' order by 1;")
-
- contains " QUERY: SELECT `id`, `d_z` FROM `doris_test`.`test_zd`
WHERE (`d_z` = '2022-01-01')"
- }
- order_qt_date_sub_min """ select * from test_zd where
date_sub(d_z,interval 1 minute) = '2021-12-31 23:59:00' order by 1; """
- explain {
- sql("select * from test_zd where date_sub(d_z,interval 1 minute) =
'2021-12-31 23:59:00' order by 1;")
-
- contains " QUERY: SELECT `id`, `d_z` FROM `doris_test`.`test_zd`
WHERE (`d_z` = '2022-01-01')"
- }
- order_qt_date_sub_sec """ select * from test_zd where
date_sub(d_z,interval 1 second) = '2021-12-31 23:59:59' order by 1; """
- explain {
- sql("select * from test_zd where date_sub(d_z,interval 1 second) =
'2021-12-31 23:59:59' order by 1;")
-
- contains " QUERY: SELECT `id`, `d_z` FROM `doris_test`.`test_zd`
WHERE (`d_z` = '2022-01-01')"
- }
-
- sql """ drop catalog if exists mysql_fun_push_catalog; """
-
- // test insert null
-
- sql """drop catalog if exists ${catalog_name} """
-
- sql """create catalog if not exists ${catalog_name} properties(
- "type"="jdbc",
- "user"="root",
- "password"="123456",
- "jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
- "driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.jdbc.Driver"
- );"""
-
- sql """switch ${catalog_name}"""
- sql """ use ${ex_db_name}"""
-
- order_qt_auto_default_t1 """insert into ${auto_default_t}(name)
values('a'); """
- test {
- sql "insert into ${auto_default_t}(name,dt) values('a', null);"
- exception "Column `dt` is not nullable, but the inserted value is
nullable."
- }
- test {
- sql "insert into ${auto_default_t}(name,dt) select '1', null;"
- exception "Column `dt` is not nullable, but the inserted value is
nullable."
- }
- explain {
- sql "insert into ${auto_default_t}(name,dt) select col1,col12 from
ex_tb15;"
- contains "PreparedStatement SQL: INSERT INTO
`doris_test`.`auto_default_t`(`name`,`dt`) VALUES (?, ?)"
- }
- order_qt_auto_default_t2 """insert into ${auto_default_t}(name,dt)
select col1, coalesce(col12,'2022-01-01 00:00:00') from ex_tb15 limit 1;"""
- sql """drop catalog if exists ${catalog_name} """
-
- // test lower_case_meta_names
-
- sql """ drop catalog if exists mysql_lower_case_catalog """
- sql """ CREATE CATALOG mysql_lower_case_catalog PROPERTIES (
- "type"="jdbc",
- "user"="root",
- "password"="123456",
- "jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
- "driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.jdbc.Driver",
- "lower_case_meta_names" = "true",
- "meta_names_mapping" = '{"databases": [{"remoteDatabase":
"DORIS","mapping": "doris_1"},{"remoteDatabase": "Doris","mapping":
"doris_2"},{"remoteDatabase": "doris","mapping": "doris_3"}],"tables":
[{"remoteDatabase": "Doris","remoteTable": "DORIS","mapping":
"doris_1"},{"remoteDatabase": "Doris","remoteTable": "Doris","mapping":
"doris_2"},{"remoteDatabase": "Doris","remoteTable": "doris","mapping":
"doris_3"}]}'
- );
- """
-
- qt_sql "show databases from mysql_lower_case_catalog;"
- qt_sql "show tables from mysql_lower_case_catalog.doris_2;"
- qt_sql "select * from mysql_lower_case_catalog.doris_2.doris_1 order
by id;"
- qt_sql "select * from mysql_lower_case_catalog.doris_2.doris_2 order
by id;"
- qt_sql "select * from mysql_lower_case_catalog.doris_2.doris_3 order
by id;"
-
- sql """ drop catalog if exists mysql_lower_case_catalog; """
- sql """ drop catalog if exists mysql_lower_case_catalog2; """
- test {
- sql """ CREATE CATALOG mysql_lower_case_catalog2 PROPERTIES (
- "type"="jdbc",
- "user"="root",
- "password"="123456",
- "jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
- "driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.jdbc.Driver",
- "lower_case_table_names" = "true",
- "meta_names_mapping" = '{"databases":
[{"remoteDatabase": "DORIS","mapping": "doris_1"},{"remoteDatabase":
"Doris","mapping": "doris_2"},{"remoteDatabase": "doris","mapping":
"doris_3"}],"tables": [{"remoteDatabase": "Doris","remoteTable":
"DORIS","mapping": "doris_1"},{"remoteDatabase": "Doris","remoteTable":
"Doris","mapping": "doris_2"},{"remoteDatabase": "Doris","remoteTable":
"doris","mapping": "doris_3"}]}'
- );
- """
- exception "Jdbc catalog property lower_case_table_names is not
supported, please use lower_case_meta_names instead"
- }
- sql """ drop catalog if exists mysql_lower_case_catalog2; """
- sql """ drop catalog if exists mysql_lower_case_catalog3; """
- sql """ CREATE CATALOG mysql_lower_case_catalog3 PROPERTIES (
- "type"="jdbc",
- "user"="root",
- "password"="123456",
- "jdbc_url" =
"jdbc:mysql://${externalEnvIp}:${mysql_port}/doris_test?useSSL=false",
- "driver_url" = "${driver_url}",
- "driver_class" = "com.mysql.jdbc.Driver",
- "lower_case_meta_names" = "true",
- "meta_names_mapping" = "{\\\"databases\\\":
[{\\\"remoteDatabase\\\": \\\"DORIS\\\",\\\"mapping\\\":
\\\"doris_1\\\"},{\\\"remoteDatabase\\\": \\\"Doris\\\",\\\"mapping\\\":
\\\"doris_2\\\"},{\\\"remoteDatabase\\\": \\\"doris\\\",\\\"mapping\\\":
\\\"doris_3\\\"}],\\\"tables\\\": [{\\\"remoteDatabase\\\":
\\\"Doris\\\",\\\"remoteTable\\\": \\\"DORIS\\\",\\\"mapping\\\":
\\\"doris_1\\\"},{\\\"remoteDatabase\\\": \\\"Doris\\\",\\\"remoteTable\\\":
\\\"Doris\\\",\\\"mapp [...]
- );
- """
- sql """ drop catalog if exists mysql_lower_case_catalog3; """
- }
-}
-
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
