This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 2b5c9c0616b [fix](jdbc catalog) Get statistics using remote name
(#46040)
2b5c9c0616b is described below
commit 2b5c9c0616b50ada9fc106c016459cb97af397d3
Author: zy-kkk <[email protected]>
AuthorDate: Fri Dec 27 17:41:01 2024 +0800
[fix](jdbc catalog) Get statistics using remote name (#46040)
### What problem does this PR solve?
Related PR: #41510
Problem Summary:
We should use remote name to get statistics
---
.../docker-compose/mysql/init/04-insert.sql | 4 +
.../postgresql/init/02-create-table.sql | 5 +
.../docker-compose/postgresql/init/04-insert.sql | 3 +
.../sqlserver/init/03-create-table.sql | 5 +
.../docker-compose/sqlserver/init/04-insert.sql | 3 +
.../doris/datasource/jdbc/JdbcExternalTable.java | 4 +-
.../jdbc/test_jdbc_row_count.groovy | 108 +++++++++++++++++++++
7 files changed, 130 insertions(+), 2 deletions(-)
diff --git a/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
b/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
index 677a041258d..4c1be96caf3 100644
--- a/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/mysql/init/04-insert.sql
@@ -1208,3 +1208,7 @@ INSERT INTO doris_test.t_char (char_col) VALUES
(REPEAT('δΈ', 255));
INSERT INTO doris_test.`test_cast` VALUES (1, '1', '2022-01-01', '2022-01-01
00:00:01');
INSERT INTO doris_test.`test_cast` VALUES (2, '2', '2022-01-02', '2022-01-02
00:00:01');
+
+ANALYZE TABLE Doris.doris;
+ANALYZE TABLE Doris.DORIS;
+ANALYZE TABLE Doris.Doris;
\ No newline at end of file
diff --git
a/docker/thirdparties/docker-compose/postgresql/init/02-create-table.sql
b/docker/thirdparties/docker-compose/postgresql/init/02-create-table.sql
index bafdd615839..8c13fa02e61 100644
--- a/docker/thirdparties/docker-compose/postgresql/init/02-create-table.sql
+++ b/docker/thirdparties/docker-compose/postgresql/init/02-create-table.sql
@@ -351,3 +351,8 @@ CREATE TABLE
catalog_pg_test.test_all_support_types_array_2d (
float8_value float8[][] NULL
);
+create table doris_test.TEST_LOWER (
+ k1 bit,
+ k2 char(100)
+);
+
diff --git a/docker/thirdparties/docker-compose/postgresql/init/04-insert.sql
b/docker/thirdparties/docker-compose/postgresql/init/04-insert.sql
index b2a14148ead..87f1f6c0d58 100644
--- a/docker/thirdparties/docker-compose/postgresql/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/postgresql/init/04-insert.sql
@@ -2940,3 +2940,6 @@ INSERT INTO
catalog_pg_test.test_all_support_types_array_2d (
ARRAY[ARRAY[5.5, 6.6], ARRAY[NULL::double precision, NULL::double
precision]]
);
+
+INSERT INTO doris_test.TEST_LOWER(k2) VALUES ('a');
+analyze doris_test.TEST_LOWER;
diff --git
a/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
b/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
index b09a2ca47a6..ea3abff2d6e 100644
--- a/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
+++ b/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
@@ -138,3 +138,8 @@ CREATE TABLE dbo.test_timestamp (
id_col int PRIMARY KEY NOT NULL,
timestamp_col timestamp NULL
);
+
+CREATE TABLE dbo.TEST_LOWER (
+ id int PRIMARY KEY NOT NULL,
+ name varchar(10) NOT NULL
+);
diff --git a/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
b/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
index f671bd23050..1ba9ca593e7 100644
--- a/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
@@ -100,3 +100,6 @@ Insert into dbo.all_type values
(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
insert into dbo.test_timestamp(id_col) values(1);
+
+Insert into dbo.TEST_LOWER values (1, 'doris');
+UPDATE STATISTICS dbo.TEST_LOWER;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/JdbcExternalTable.java
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/JdbcExternalTable.java
index 4bad19b8a69..36378e80ac3 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/JdbcExternalTable.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/JdbcExternalTable.java
@@ -201,8 +201,8 @@ public class JdbcExternalTable extends ExternalTable {
public long fetchRowCount() {
Map<String, String> params = new HashMap<>();
params.put("ctlName", catalog.getName());
- params.put("dbName", dbName);
- params.put("tblName", name);
+ params.put("dbName", this.db.getRemoteName());
+ params.put("tblName", this.remoteName);
switch (((JdbcExternalCatalog) catalog).getDatabaseTypeName()) {
case JdbcResource.MYSQL:
params.put("sql", MYSQL_ROW_COUNT_SQL);
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_jdbc_row_count.groovy
b/regression-test/suites/external_table_p0/jdbc/test_jdbc_row_count.groovy
index c8220ce13dd..0d31ab37081 100644
--- a/regression-test/suites/external_table_p0/jdbc/test_jdbc_row_count.groovy
+++ b/regression-test/suites/external_table_p0/jdbc/test_jdbc_row_count.groovy
@@ -49,6 +49,34 @@ suite("test_jdbc_row_count",
"p0,external,mysql,external_docker,external_docker_
assertEquals("5", result[0][2])
sql """drop catalog ${catalog_name}"""
+ // Test mysql lower
+ catalog_name = "test_mysql_lower_jdbc_row_count";
+ 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",
+ "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"}]}'
+ );"""
+
+ sql """use ${catalog_name}.doris_test"""
+ result = sql """show table stats doris_2.doris_1"""
+ Thread.sleep(1000)
+ for (int i = 0; i < 60; i++) {
+ result = sql """show table stats doris_2.doris_1""";
+ if (result[0][2] != "-1") {
+ break;
+ }
+ logger.info("Table row count not ready yet. Wait 1 second.")
+ Thread.sleep(1000)
+ }
+ assertEquals("1", result[0][2])
+ sql """drop catalog ${catalog_name}"""
+
// Test pg
catalog_name = "test_pg_jdbc_row_count";
driver_url =
"https://${bucket}.${s3_endpoint}/regression/jdbc_driver/postgresql-42.5.0.jar";
@@ -76,6 +104,32 @@ suite("test_jdbc_row_count",
"p0,external,mysql,external_docker,external_docker_
assertEquals("1026", result[0][2])
sql """drop catalog ${catalog_name}"""
+ // Test pg lower
+ catalog_name = "test_pg_lower_jdbc_row_count";
+ sql """drop catalog if exists ${catalog_name} """
+ sql """create catalog if not exists ${catalog_name} properties(
+ "type"="jdbc",
+ "user"="postgres",
+ "password"="123456",
+ "jdbc_url" =
"jdbc:postgresql://${externalEnvIp}:${pg_port}/postgres?currentSchema=doris_test&useSSL=false",
+ "driver_url" = "${driver_url}",
+ "driver_class" = "org.postgresql.Driver",
+ "lower_case_meta_names" = "true"
+ );"""
+ sql """use ${catalog_name}.doris_test"""
+ result = sql """show table stats test_lower"""
+ Thread.sleep(1000)
+ for (int i = 0; i < 60; i++) {
+ result = sql """show table stats test_lower""";
+ if (result[0][2] != "-1") {
+ break;
+ }
+ logger.info("Table row count not ready yet. Wait 1 second.")
+ Thread.sleep(1000)
+ }
+ assertEquals("1", result[0][2])
+ sql """drop catalog ${catalog_name}"""
+
// Test sqlserver
catalog_name = "test_sqlserver_jdbc_row_count";
driver_url =
"https://${bucket}.${s3_endpoint}/regression/jdbc_driver/mssql-jdbc-11.2.3.jre8.jar";
@@ -103,6 +157,32 @@ suite("test_jdbc_row_count",
"p0,external,mysql,external_docker,external_docker_
assertEquals("3", result[0][2])
sql """drop catalog ${catalog_name}"""
+ // Test sqlserver lower
+ catalog_name = "test_sqlserver_lower_jdbc_row_count";
+ sql """drop catalog if exists ${catalog_name} """
+ sql """ create catalog if not exists ${catalog_name} properties(
+ "type"="jdbc",
+ "user"="sa",
+ "password"="Doris123456",
+ "jdbc_url" =
"jdbc:sqlserver://${externalEnvIp}:${sqlserver_port};encrypt=false;databaseName=doris_test;",
+ "driver_url" = "${driver_url}",
+ "driver_class" =
"com.microsoft.sqlserver.jdbc.SQLServerDriver",
+ "lower_case_meta_names" = "true"
+ );"""
+ sql """use ${catalog_name}.dbo"""
+ result = sql """show table stats test_lower"""
+ Thread.sleep(1000)
+ for (int i = 0; i < 60; i++) {
+ result = sql """show table stats test_lower""";
+ if (result[0][2] != "-1") {
+ break;
+ }
+ logger.info("Table row count not ready yet. Wait 1 second.")
+ Thread.sleep(1000)
+ }
+ assertEquals("1", result[0][2])
+ sql """drop catalog ${catalog_name}"""
+
// Test oracle
catalog_name = "test_oracle_jdbc_row_count";
String oracle_port = context.config.otherConfigs.get("oracle_11_port");
@@ -132,6 +212,34 @@ suite("test_jdbc_row_count",
"p0,external,mysql,external_docker,external_docker_
}
assertTrue("4".equals(result[0][2]) || "-1".equals(result[0][2]))
sql """drop catalog ${catalog_name}"""
+
+ // Test oracle lower
+ catalog_name = "test_oracle_lower_jdbc_row_count";
+ sql """drop catalog if exists ${catalog_name} """
+ sql """
+ create catalog if not exists ${catalog_name} properties(
+ "type"="jdbc",
+ "user"="doris_test",
+ "password"="123456",
+ "jdbc_url" =
"jdbc:oracle:thin:@${externalEnvIp}:${oracle_port}:${SID}",
+ "driver_url" = "${driver_url}",
+ "driver_class" = "oracle.jdbc.driver.OracleDriver",
+ "lower_case_meta_names" = "true"
+ );
+ """
+ sql """use ${catalog_name}.doris_test"""
+ result = sql """show table stats student"""
+ Thread.sleep(1000)
+ for (int i = 0; i < 30; i++) {
+ result = sql """show table stats student""";
+ if (result[0][2] != "-1") {
+ break;
+ }
+ logger.info("Table row count not ready yet. Wait 1 second.")
+ Thread.sleep(1000)
+ }
+ assertTrue("4".equals(result[0][2]) || "-1".equals(result[0][2]))
+ sql """drop catalog ${catalog_name}"""
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
