You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by zy...@apache.org on 2024/03/10 10:17:04 UTC

(doris) branch master updated: [fix](jdbc catalog) fix type conversion error in MySQL JDBC Driver 5.x (#31880)

This is an automated email from the ASF dual-hosted git repository.

zykkk pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git


The following commit(s) were added to refs/heads/master by this push:
     new 340427c78d8 [fix](jdbc catalog) fix type conversion error in MySQL JDBC Driver 5.x (#31880)
340427c78d8 is described below

commit 340427c78d85fada84823b4c5567d165a32d5779
Author: zy-kkk <zh...@gmail.com>
AuthorDate: Sun Mar 10 18:16:56 2024 +0800

    [fix](jdbc catalog) fix type conversion error in MySQL JDBC Driver 5.x (#31880)
---
 docs/en/docs/lakehouse/multi-catalog/jdbc.md       |   8 +-
 docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md    |   8 +-
 .../org/apache/doris/jdbc/MySQLJdbcExecutor.java   |  35 +-
 .../jdbc/test_mysql_jdbc_driver5_catalog.out       | 446 +++++++++++++++
 .../jdbc/test_mysql_jdbc_driver5_catalog.groovy    | 606 +++++++++++++++++++++
 5 files changed, 1090 insertions(+), 13 deletions(-)

diff --git a/docs/en/docs/lakehouse/multi-catalog/jdbc.md b/docs/en/docs/lakehouse/multi-catalog/jdbc.md
index e0585cd13e1..6b3682c61fc 100644
--- a/docs/en/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/en/docs/lakehouse/multi-catalog/jdbc.md
@@ -407,7 +407,7 @@ CREATE CATALOG jdbc_mysql PROPERTIES (
     "user" = "root",
     "password" = "123456",
     "jdbc_url" = "jdbc:mysql://127.0.0.1:3306/demo",
-    "driver_url" = "mysql-connector-java-5.1.47.jar",
+    "driver_url" = "mysql-connector-java-5.1.49.jar",
     "driver_class" = "com.mysql.jdbc.Driver"
 )
 ```
@@ -625,7 +625,7 @@ CREATE CATALOG jdbc_doris PROPERTIES (
     "user" = "root",
     "password" = "123456",
     "jdbc_url" = "jdbc:mysql://127.0.0.1:9030?useSSL=false",
-    "driver_url" = "mysql-connector-java-5.1.47.jar",
+    "driver_url" = "mysql-connector-java-5.1.49.jar",
     "driver_class" = "com.mysql.jdbc.Driver"
 )
 ```
@@ -899,12 +899,12 @@ It is recommended to use the following versions of Driver to connect to the corr
 
 |    Source    |                        JDBC Driver Version                        |
 |:------------:|:-----------------------------------------------------------------:|
-| MySQL 5.x    |                  mysql-connector-java-5.1.47.jar                  |
+| MySQL 5.x    |                  mysql-connector-java-5.1.49.jar                  |
 |  MySQL 8.x   |                  mysql-connector-java-8.0.25.jar                  |
 |  PostgreSQL  |                       postgresql-42.5.1.jar                       |
 |    Oracle    |                            ojdbc8.jar                             |
 |  SQLServer   |                    mssql-jdbc-11.2.3.jre8.jar                     |
-|    Doris     | mysql-connector-java-5.1.47.jar / mysql-connector-java-8.0.25.jar |
+|    Doris     | mysql-connector-java-5.1.49.jar / mysql-connector-java-8.0.25.jar |
 |  Clickhouse  |                   clickhouse-jdbc-0.4.2-all.jar                   |
 |   SAP HAHA   |                             ngdbc.jar                             |
 | Trino/Presto |            trino-jdbc-389.jar / presto-jdbc-0.280.jar             |
diff --git a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
index 0f2f9985746..c43ad7fe61e 100644
--- a/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
+++ b/docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md
@@ -409,7 +409,7 @@ CALL EXECUTE_STMT(jdbc_catalog", "create table dbl1.tbl2 (k1 int)");
         "user"="root",
         "password"="123456",
         "jdbc_url" = "jdbc:mysql://127.0.0.1:3306/demo",
-        "driver_url" = "mysql-connector-java-5.1.47.jar",
+        "driver_url" = "mysql-connector-java-5.1.49.jar",
         "driver_class" = "com.mysql.jdbc.Driver"
     )
     ```
@@ -628,7 +628,7 @@ CREATE CATALOG jdbc_doris PROPERTIES (
     "user"="root",
     "password"="123456",
     "jdbc_url" = "jdbc:mysql://127.0.0.1:9030?useSSL=false",
-    "driver_url" = "mysql-connector-java-5.1.47.jar",
+    "driver_url" = "mysql-connector-java-5.1.49.jar",
     "driver_class" = "com.mysql.jdbc.Driver"
 )
 ```
@@ -902,12 +902,12 @@ CREATE CATALOG `jdbc_db2` PROPERTIES (
 
 |    Source    |                        JDBC Driver Version                        |
 |:------------:|:-----------------------------------------------------------------:|
-|  MySQL 5.x   |                  mysql-connector-java-5.1.47.jar                  |
+|  MySQL 5.x   |                  mysql-connector-java-5.1.49.jar                  |
 |  MySQL 8.x   |                  mysql-connector-java-8.0.25.jar                  |
 |  PostgreSQL  |                       postgresql-42.5.1.jar                       |
 |    Oracle    |                            ojdbc8.jar                             |
 |  SQLServer   |                    mssql-jdbc-11.2.3.jre8.jar                     |
-|    Doris     | mysql-connector-java-5.1.47.jar / mysql-connector-java-8.0.25.jar |
+|    Doris     | mysql-connector-java-5.1.49.jar / mysql-connector-java-8.0.25.jar |
 |  Clickhouse  |                   clickhouse-jdbc-0.4.2-all.jar                   |
 |   SAP HAHA   |                             ngdbc.jar                             |
 | Trino/Presto |            trino-jdbc-389.jar / presto-jdbc-0.280.jar             |
diff --git a/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/MySQLJdbcExecutor.java b/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/MySQLJdbcExecutor.java
index bd5297836a6..6a7e2affd58 100644
--- a/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/MySQLJdbcExecutor.java
+++ b/fe/be-java-extensions/jdbc-scanner/src/main/java/org/apache/doris/jdbc/MySQLJdbcExecutor.java
@@ -84,7 +84,10 @@ public class MySQLJdbcExecutor extends BaseJdbcExecutor {
                 block.add(new byte[batchSizeNum][]);
             } else if (outputTable.getColumnType(i).getType() == Type.ARRAY) {
                 block.add(new String[batchSizeNum]);
-            } else if (outputTable.getColumnType(i).getType() == Type.STRING) {
+            } else if (outputTable.getColumnType(i).getType() == Type.TINYINT
+                    || outputTable.getColumnType(i).getType() == Type.SMALLINT
+                    || outputTable.getColumnType(i).getType() == Type.LARGEINT
+                    || outputTable.getColumnType(i).getType() == Type.STRING) {
                 block.add(new Object[batchSizeNum]);
             } else {
                 block.add(outputTable.getColumn(i).newObjectContainerArray(batchSizeNum));
@@ -105,15 +108,13 @@ public class MySQLJdbcExecutor extends BaseJdbcExecutor {
                 case BOOLEAN:
                     return resultSet.getObject(columnIndex + 1, Boolean.class);
                 case TINYINT:
-                    return resultSet.getObject(columnIndex + 1, Byte.class);
                 case SMALLINT:
-                    return resultSet.getObject(columnIndex + 1, Short.class);
+                case LARGEINT:
+                    return resultSet.getObject(columnIndex + 1);
                 case INT:
                     return resultSet.getObject(columnIndex + 1, Integer.class);
                 case BIGINT:
                     return resultSet.getObject(columnIndex + 1, Long.class);
-                case LARGEINT:
-                    return resultSet.getObject(columnIndex + 1, BigInteger.class);
                 case FLOAT:
                     return resultSet.getObject(columnIndex + 1, Float.class);
                 case DOUBLE:
@@ -144,6 +145,30 @@ public class MySQLJdbcExecutor extends BaseJdbcExecutor {
     @Override
     protected ColumnValueConverter getOutputConverter(ColumnType columnType, String replaceString) {
         switch (columnType.getType()) {
+            case TINYINT:
+                return createConverter(input -> {
+                    if (input instanceof Integer) {
+                        return ((Integer) input).byteValue();
+                    } else {
+                        return input;
+                    }
+                }, Byte.class);
+            case SMALLINT:
+                return createConverter(input -> {
+                    if (input instanceof Integer) {
+                        return ((Integer) input).shortValue();
+                    } else {
+                        return input;
+                    }
+                }, Short.class);
+            case LARGEINT:
+                return createConverter(input -> {
+                    if (input instanceof String) {
+                        return new BigInteger((String) input);
+                    } else {
+                        return input;
+                    }
+                }, BigInteger.class);
             case STRING:
                 if (replaceString.equals("bitmap") || replaceString.equals("hll")) {
                     return null;
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
new file mode 100644
index 00000000000..339ddaf9eba
--- /dev/null
+++ b/regression-test/data/external_table_p0/jdbc/test_mysql_jdbc_driver5_catalog.out
@@ -0,0 +1,446 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !sql --
+internal
+
+-- !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 --
+character_sets
+collations
+column_privileges
+column_statistics
+columns
+engines
+events
+files
+global_variables
+key_column_usage
+metadata_name_ids
+parameters
+partitions
+profiling
+referential_constraints
+routines
+rowsets
+schema_privileges
+schemata
+session_variables
+statistics
+table_constraints
+table_privileges
+tables
+triggers
+user_privileges
+views
+
+-- !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_filter_not_old_plan --
+张三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
+
+-- !specified_database_2 --
+doris_test
+information_schema
+
+-- !specified_database_3 --
+DORIS
+Doris
+doris
+information_schema
+init_db
+mysql
+performance_schema
+show_test_do_not_modify
+sys
+
+-- !specified_database_4 --
+information_schema
+
+-- !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
+performance_schema
+show_test_do_not_modify
+sys
+
+-- !sql --
+doris_1
+doris_2
+doris_3
+
+-- !sql --
+DORIS
+
+-- !sql --
+Doris
+
+-- !sql --
+doris
+
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
new file mode 100644
index 00000000000..b6b80952737
--- /dev/null
+++ b/regression-test/suites/external_table_p0/jdbc/test_mysql_jdbc_driver5_catalog.groovy
@@ -0,0 +1,606 @@
+// 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_jdbc_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"
+        );"""
+
+        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()"""
+        def res_dbs_log = sql "show databases;"
+		for(int i = 0;i < res_dbs_log.size();i++) {
+			def tbs = sql "show tables from  `${res_dbs_log[i][0]}`"
+			log.info( "database = ${res_dbs_log[i][0]} => tables = "+tbs.toString())
+		}
+        try {
+        
+            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; """
+            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; """
+            order_qt_test_filter_not_old_plan """select /*+ SET_VAR(enable_nereids_planner=false) */ * 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}' """
+        } finally {
+			res_dbs_log = sql "show databases;"
+			for(int i = 0;i < res_dbs_log.size();i++) {
+				def tbs = sql "show tables from  `${res_dbs_log[i][0]}`"
+				log.info( "database = ${res_dbs_log[i][0]} => tables = "+tbs.toString())
+			}
+		}
+        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} """
+        
+        res_dbs_log = sql "show databases;"
+		for(int i = 0;i < res_dbs_log.size();i++) {
+			def tbs = sql "show tables from  `${res_dbs_log[i][0]}`"
+			log.info( "database = ${res_dbs_log[i][0]} => tables = "+tbs.toString())
+		}
+        try {
+            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};"""
+        } finally {
+			res_dbs_log = sql "show databases;"
+			for(int i = 0;i < res_dbs_log.size();i++) {
+				def tbs = sql "show tables from  `${res_dbs_log[i][0]}`"
+				log.info( "database = ${res_dbs_log[i][0]} => tables = "+tbs.toString())
+			}
+		}
+        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"""
+        res_dbs_log = sql "show databases;"
+		for(int i = 0;i < res_dbs_log.size();i++) {
+			def tbs = sql "show tables from  `${res_dbs_log[i][0]}`"
+			log.info( "database = ${res_dbs_log[i][0]} => tables = "+tbs.toString())
+		}
+        try {
+            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
+            sql """ set disable_nereids_rules='NORMALIZE_REWRITE_RULES'; """
+            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 (date_add(`d_z`, INTERVAL 1 year) = '2023-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 (date_add(`d_z`, INTERVAL 1 month) = '2022-02-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 (date_add(`d_z`, INTERVAL 1 week) = '2022-01-08')"
+            }
+            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 (date_add(`d_z`, INTERVAL 1 day) = '2022-01-02')"
+            }
+            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 (date_add(`d_z`, INTERVAL 1 hour) = '2022-01-01 01:00:00')"
+            }
+            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 (date_add(`d_z`, INTERVAL 1 minute) = '2022-01-01 00:01:00')"
+            }
+            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 (date_add(`d_z`, INTERVAL 1 second) = '2022-01-01 00:00: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 (date_sub(`d_z`, INTERVAL 1 year) = '2021-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 (date_sub(`d_z`, INTERVAL 1 month) = '2021-12-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 (date_sub(`d_z`, INTERVAL 1 week) = '2021-12-25')"
+            }
+            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 (date_sub(`d_z`, INTERVAL 1 day) = '2021-12-31')"
+            }
+            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 (date_sub(`d_z`, INTERVAL 1 hour) = '2021-12-31 23:00:00')"
+            }
+            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 (date_sub(`d_z`, INTERVAL 1 minute) = '2021-12-31 23:59:00')"
+            }
+            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 (date_sub(`d_z`, INTERVAL 1 second) = '2021-12-31 23:59:59')"
+            }
+            sql """ set disable_nereids_rules=''; """
+
+        } finally {
+			res_dbs_log = sql "show databases;"
+			for(int i = 0;i < res_dbs_log.size();i++) {
+				def tbs = sql "show tables from  `${res_dbs_log[i][0]}`"
+				log.info( "database = ${res_dbs_log[i][0]} => tables = "+tbs.toString())
+			}
+		}
+        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: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org