This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
commit cf6b22c621a3a91a17b9184b35aedd1cc033f5e1
Author: zy-kkk <zhongy...@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 ae28cc6ed59..dde24e1eb53 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"
)
```
@@ -629,7 +629,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"
)
```
@@ -903,12 +903,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-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
