This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new 02110dc38a5 branch-4.0: [fix](jdbc) Fix PG array null detection and
SQL Server date format pushdown #60954 (#60972)
02110dc38a5 is described below
commit 02110dc38a58fb05f4c824692ad3ddb463c6bc50
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Tue Mar 3 17:46:40 2026 +0800
branch-4.0: [fix](jdbc) Fix PG array null detection and SQL Server date
format pushdown #60954 (#60972)
Cherry-picked from #60954
Co-authored-by: Mingyu Chen (Rayner) <[email protected]>
---
.../postgresql/init/02-create-table.sql | 7 +-
.../docker-compose/postgresql/init/04-insert.sql | 5 +
.../sqlserver/init/03-create-table.sql | 7 ++
.../docker-compose/sqlserver/init/04-insert.sql | 9 ++
.../jdbc/client/JdbcPostgreSQLClient.java | 6 +-
.../doris/datasource/jdbc/source/JdbcScanNode.java | 19 +++
.../datasource/jdbc/source/JdbcScanNodeTest.java | 133 +++++++++++++++++++++
.../jdbc/test_pg_jdbc_catalog.out | 33 ++---
.../jdbc/test_sqlserver_jdbc_catalog.out | 26 +++-
.../jdbc/test_pg_jdbc_catalog.groovy | 10 +-
.../jdbc/test_sqlserver_jdbc_catalog.groovy | 24 ++++
11 files changed, 250 insertions(+), 29 deletions(-)
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 b3ba617d6c1..ac35f2847d2 100644
--- a/docker/thirdparties/docker-compose/postgresql/init/02-create-table.sql
+++ b/docker/thirdparties/docker-compose/postgresql/init/02-create-table.sql
@@ -437,4 +437,9 @@ CREATE TABLE test_timestamp_tz_db.ts_test (
ts_ntz timestamp NULL
);
-
+CREATE TABLE catalog_pg_test.test_array_null (
+ id int4 NOT NULL,
+ text_arr text[] NULL,
+ int_arr int4[] NULL,
+ varchar_arr varchar[] NULL
+);
diff --git a/docker/thirdparties/docker-compose/postgresql/init/04-insert.sql
b/docker/thirdparties/docker-compose/postgresql/init/04-insert.sql
index 14d3321c86e..c6b4fafe896 100644
--- a/docker/thirdparties/docker-compose/postgresql/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/postgresql/init/04-insert.sql
@@ -3026,3 +3026,8 @@ SET TIME ZONE 'Asia/Shanghai';
INSERT INTO test_timestamp_tz_db.ts_test VALUES (1,'2025-01-01
12:00:00+08','2025-01-01 12:00:00');
INSERT INTO test_timestamp_tz_db.ts_test VALUES (2,NULL,NULL);
SET TIME ZONE DEFAULT;
+
+-- Test data for array null handling (first row has NULL arrays, second has
data)
+INSERT INTO catalog_pg_test.test_array_null VALUES (1, NULL, NULL, NULL);
+INSERT INTO catalog_pg_test.test_array_null VALUES (2, ARRAY['hello',
'world'], ARRAY[1, 2, 3], ARRAY['a', 'b']);
+INSERT INTO catalog_pg_test.test_array_null VALUES (3, ARRAY['foo', NULL],
ARRAY[NULL, 4], ARRAY[NULL, 'c']);
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 8d62085b1b3..d0b1989ce30 100644
--- a/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
+++ b/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
@@ -277,3 +277,10 @@ CREATE TABLE dbo.test_identity_decimal (
id decimal(18,0) IDENTITY(1,1),
col int
);
+
+CREATE TABLE dbo.test_date_filter (
+ id int PRIMARY KEY NOT NULL,
+ date_value date NULL,
+ datetime_value datetime NULL,
+ datetime2_value datetime2 NULL
+);
diff --git a/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
b/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
index aadacd884a3..894773761c6 100644
--- a/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
@@ -123,3 +123,12 @@ insert into dbo.extreme_test_multi_block select * from
dbo.extreme_test_multi_bl
insert into dbo.extreme_test_multi_block select * from dbo.extreme_test;
INSERT INTO dbo.test_identity_decimal(col) select 1;
+
+-- Data for testing handleSQLServerDateFormat predicate pushdown
+-- Rows have different date and datetime values to verify filter correctness
+Insert into dbo.test_date_filter values
+(1, '2023-01-17', '2023-01-17 10:30:00', '2023-01-17 10:30:00.123'),
+(2, '2023-06-25', '2023-06-25 14:30:45', '2023-06-25 14:30:45.456789'),
+(3, '2024-12-31', '2024-12-31 23:59:59', '2024-12-31 23:59:59.999'),
+(4, '2023-01-17', '2023-01-17 08:00:00', '2023-01-17 08:00:00'),
+(5, '2025-03-15', '2025-03-15 12:00:00', '2025-03-15 12:00:00.500');
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcPostgreSQLClient.java
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcPostgreSQLClient.java
index 5505c434004..8d449ad33f1 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcPostgreSQLClient.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcPostgreSQLClient.java
@@ -69,8 +69,10 @@ public class JdbcPostgreSQLClient extends JdbcClient {
ResultSet arrayRs = null;
try {
pstmt = conn.prepareStatement(
- String.format("SELECT array_ndims(%s) FROM
%s.%s LIMIT 1",
- columnName, remoteDbName,
remoteTableName));
+ String.format("SELECT array_ndims(\"%s\") FROM
\"%s\".\"%s\""
+ + " WHERE \"%s\" IS NOT NULL
LIMIT 1",
+ columnName, remoteDbName,
remoteTableName,
+ columnName));
arrayRs = pstmt.executeQuery();
if (arrayRs.next()) {
arrayDimensions = arrayRs.getInt(1);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/source/JdbcScanNode.java
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/source/JdbcScanNode.java
index 50f19821d03..793dc352fee 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/source/JdbcScanNode.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/source/JdbcScanNode.java
@@ -346,6 +346,8 @@ public class JdbcScanNode extends ExternalScanNode {
filter += handleOracleDateFormat(children.get(1), tbl);
} else if (tableType.equals(TOdbcTableType.TRINO) ||
tableType.equals(TOdbcTableType.PRESTO)) {
filter += handleTrinoDateFormat(children.get(1), tbl);
+ } else if (tableType.equals(TOdbcTableType.SQLSERVER)) {
+ filter += handleSQLServerDateFormat(children.get(1), tbl);
} else {
filter +=
children.get(1).toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
}
@@ -370,6 +372,8 @@ public class JdbcScanNode extends ExternalScanNode {
inItemStrings.add(handleOracleDateFormat(inItem, tbl));
} else if (tableType.equals(TOdbcTableType.TRINO) ||
tableType.equals(TOdbcTableType.PRESTO)) {
inItemStrings.add(handleTrinoDateFormat(inItem, tbl));
+ } else if (tableType.equals(TOdbcTableType.SQLSERVER)) {
+ inItemStrings.add(handleSQLServerDateFormat(inItem, tbl));
} else {
inItemStrings.add(inItem.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl));
}
@@ -433,6 +437,21 @@ public class JdbcScanNode extends ExternalScanNode {
return expr.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
}
+ private static String handleSQLServerDateFormat(Expr expr, TableIf tbl) {
+ if (expr.isConstant()) {
+ if (expr.getType().isDatetime() || expr.getType().isDatetimeV2()) {
+ // Use CONVERT with style 121 (ODBC canonical: yyyy-mm-dd
hh:mi:ss.mmm)
+ // which is language-independent and handles fractional seconds
+ return "CONVERT(DATETIME, '" + expr.getStringValue() + "',
121)";
+ } else if (expr.getType().isDate() || expr.getType().isDateV2()) {
+ // Use CONVERT with style 23 (ISO8601: yyyy-mm-dd)
+ // which is language-independent
+ return "CONVERT(DATE, '" + expr.getStringValue() + "', 23)";
+ }
+ }
+ return expr.toExternalSql(TableType.JDBC_EXTERNAL_TABLE, tbl);
+ }
+
private static boolean containsNullLiteral(Expr expr) {
List<NullLiteral> nullExprList = Lists.newArrayList();
expr.collect(NullLiteral.class, nullExprList);
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/source/JdbcScanNodeTest.java
b/fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/source/JdbcScanNodeTest.java
index ac78a2c6e29..09cbb2786d7 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/source/JdbcScanNodeTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/source/JdbcScanNodeTest.java
@@ -22,13 +22,16 @@ import org.apache.doris.analysis.BinaryPredicate.Operator;
import org.apache.doris.analysis.BoolLiteral;
import org.apache.doris.analysis.CompoundPredicate;
import org.apache.doris.analysis.DateLiteral;
+import org.apache.doris.analysis.Expr;
import org.apache.doris.analysis.FloatLiteral;
+import org.apache.doris.analysis.InPredicate;
import org.apache.doris.analysis.IntLiteral;
import org.apache.doris.analysis.IsNullPredicate;
import org.apache.doris.analysis.LikePredicate;
import org.apache.doris.analysis.SlotRef;
import org.apache.doris.analysis.StringLiteral;
import org.apache.doris.catalog.JdbcTable;
+import org.apache.doris.catalog.ScalarType;
import org.apache.doris.catalog.Type;
import org.apache.doris.thrift.TOdbcTableType;
@@ -37,6 +40,9 @@ import mockit.Mocked;
import org.junit.Assert;
import org.junit.Test;
+import java.util.Arrays;
+import java.util.List;
+
public class JdbcScanNodeTest {
@Mocked
@@ -284,6 +290,11 @@ public class JdbcScanNodeTest {
String mysqlResult =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, outerComp, mockTable);
Assert.assertTrue(mysqlResult.contains("'2023-01-01 00:00:00'"));
Assert.assertTrue(mysqlResult.contains("'2023-12-31 23:59:59'"));
+
+ // Test for SQL Server (CONVERT with style 121)
+ String sqlserverResult =
JdbcScanNode.conjunctExprToString(TOdbcTableType.SQLSERVER, outerComp,
mockTable);
+ Assert.assertTrue(sqlserverResult.contains("CONVERT(DATETIME,
'2023-01-01 00:00:00', 121)"));
+ Assert.assertTrue(sqlserverResult.contains("CONVERT(DATETIME,
'2023-12-31 23:59:59', 121)"));
}
@Test
@@ -493,4 +504,126 @@ public class JdbcScanNodeTest {
String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.MYSQL, outerComp, mockTable);
Assert.assertEquals("((\"ID\" = 1) OR ((\"NAME\" = 'test') AND
((\"AGE\" > 18) OR (\"DEPT\" = 'HR'))))", result);
}
+
+ @Test
+ public void testDateLiteralSQLServerDatetime() throws Exception {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // Test DATETIME type with SQL Server
+ DateLiteral dateLiteral = new DateLiteral("2026-02-28 12:30:12",
Type.DATETIME);
+
+ SlotRef dateSlot = new SlotRef(null, "data_time");
+ BinaryPredicate datePred = new BinaryPredicate(Operator.GT, dateSlot,
dateLiteral);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.SQLSERVER, datePred,
mockTable);
+ Assert.assertTrue(result.contains("CONVERT(DATETIME, '2026-02-28
12:30:12', 121)"));
+ Assert.assertTrue(result.startsWith("\"data_time\" > "));
+ }
+
+ @Test
+ public void testDateLiteralSQLServerDate() throws Exception {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // Test DATE type with SQL Server
+ DateLiteral dateLiteral = new DateLiteral("2026-02-28", Type.DATEV2);
+
+ SlotRef dateSlot = new SlotRef(null, "create_date");
+ BinaryPredicate datePred = new BinaryPredicate(Operator.GE, dateSlot,
dateLiteral);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.SQLSERVER, datePred,
mockTable);
+ Assert.assertTrue(result.contains("CONVERT(DATE, '2026-02-28', 23)"));
+ Assert.assertTrue(result.startsWith("\"create_date\" >= "));
+ }
+
+ @Test
+ public void testDateLiteralSQLServerDatetimeV2WithFractionalSeconds()
throws Exception {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // Test DATETIMEV2 type with fractional seconds (as SQL Server
datetime maps to DATETIMEV2)
+ DateLiteral dateLiteral = new DateLiteral("2026-02-28 12:30:12.123",
+ ScalarType.createDatetimeV2Type(3));
+
+ SlotRef dateSlot = new SlotRef(null, "data_time");
+ BinaryPredicate datePred = new BinaryPredicate(Operator.GT, dateSlot,
dateLiteral);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.SQLSERVER, datePred,
mockTable);
+ Assert.assertTrue(result.contains("CONVERT(DATETIME, '2026-02-28
12:30:12.123', 121)"));
+ Assert.assertTrue(result.startsWith("\"data_time\" > "));
+ }
+
+ @Test
+ public void testDateLiteralSQLServerInPredicate() throws Exception {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // Test IN predicate with datetime literals for SQL Server
+ SlotRef dateSlot = new SlotRef(null, "data_time");
+ DateLiteral date1 = new DateLiteral("2026-01-01 00:00:00",
Type.DATETIME);
+ DateLiteral date2 = new DateLiteral("2026-06-15 12:00:00",
Type.DATETIME);
+ DateLiteral date3 = new DateLiteral("2026-12-31 23:59:59",
Type.DATETIME);
+
+ List<Expr> inList = Arrays.asList(date1, date2, date3);
+ InPredicate inPred = new InPredicate(dateSlot, inList, false);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.SQLSERVER, inPred, mockTable);
+ Assert.assertTrue(result.contains("CONVERT(DATETIME, '2026-01-01
00:00:00', 121)"));
+ Assert.assertTrue(result.contains("CONVERT(DATETIME, '2026-06-15
12:00:00', 121)"));
+ Assert.assertTrue(result.contains("CONVERT(DATETIME, '2026-12-31
23:59:59', 121)"));
+ Assert.assertTrue(result.contains("IN ("));
+ }
+
+ @Test
+ public void testDateLiteralSQLServerCompoundPredicate() throws Exception {
+ new Expectations() {{
+ mockTable.getProperRemoteColumnName((TOdbcTableType) any,
anyString);
+ result = new mockit.Delegate() {
+ String getProperColumnName(TOdbcTableType tableType,
String colName) {
+ return "\"" + colName + "\"";
+ }
+ };
+ }};
+
+ // Test compound predicate: ID = 1 AND data_time > '2026-02-28
12:30:12'
+ SlotRef idSlot = new SlotRef(null, "ID");
+ IntLiteral intLiteral = new IntLiteral(1);
+ BinaryPredicate idPred = new BinaryPredicate(Operator.EQ, idSlot,
intLiteral);
+
+ DateLiteral dateLiteral = new DateLiteral("2026-02-28 12:30:12",
Type.DATETIME);
+ SlotRef dateSlot = new SlotRef(null, "data_time");
+ BinaryPredicate datePred = new BinaryPredicate(Operator.GT, dateSlot,
dateLiteral);
+
+ CompoundPredicate compPred = new
CompoundPredicate(CompoundPredicate.Operator.AND, idPred, datePred);
+
+ String result =
JdbcScanNode.conjunctExprToString(TOdbcTableType.SQLSERVER, compPred,
mockTable);
+ Assert.assertTrue(result.contains("CONVERT(DATETIME, '2026-02-28
12:30:12', 121)"));
+ Assert.assertTrue(result.contains("\"ID\" = 1"));
+ Assert.assertTrue(result.contains(" AND "));
+ }
}
diff --git
a/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
index 7950b410c58..7ad356af7c7 100644
--- a/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
+++ b/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
@@ -1,14 +1,4 @@
-- This file is automatically generated. You should know what you did if you
want to edit this
--- !show_db --
-catalog_pg_test
-cdc_test
-doris_test
-information_schema
-mysql
-pg_catalog
-public
-test_timestamp_tz_db
-
-- !test0 --
123 abc
123 abc
@@ -2149,9 +2139,6 @@ true abc def 2022-10-11 1.234 1
2 99 2022-10-22T10:59:59 34.123
1 980dd890-f7fe-4fff-999d-873516108b2e
2 980dd890-f7fe-4fff-999d-873516108b2e
--- !wkb_test --
-1
\\x01030000000100000005000000000000000000000000000000000000000000000000000000000000000000f03f000000000000f03f000000000000f03f000000000000f03f000000000000000000000000000000000000000000000000
-
-- !dt_test --
2023-06-16T12:34:56.123 2023-06-16T12:34:56.123
@@ -2251,6 +2238,17 @@ doris3 20
3 [["A", null], ["C", "D"]] [["Hello", null], ["Foo", "Bar"]]
[["Text1", null], ["Text3", "Text4"]] [["2024-01-01", null], ["2024-02-01",
"2024-02-02"]] [[10, null], [30, 40]] [[100, null], [300, 400]]
[[1000, null], [3000, 4000]] [["2024-01-01 10:00:00.000000", null],
["2024-03-01 10:00:00.000000", "2024-03-02 12:00:00.000000"]] [["2024-01-01
18:00:00.000000", null], ["2024-03-01 18:00:00.000000", "2024-03-02
20:00:00.000000"]] [[1, null], [0, 1]] [[1.1, null], [3.3, 4.4]]
[[5.5, null], [...]
4 [["A", "B"], [null, null]] [["Hello", "World"], [null, null]]
[["Text1", "Text2"], [null, null]] [["2024-01-01", "2024-01-02"], [null,
null]] [[10, 20], [null, null]] [[100, 200], [null, null]]
[[1000, 2000], [null, null]] [["2024-01-01 10:00:00.000000", "2024-01-02
12:00:00.000000"], [null, null]] [["2024-01-01 18:00:00.000000", "2024-01-02
20:00:00.000000"], [null, null]] [[1, 0], [null, null]] [[1.1, 2.2], [null,
null]] [[5.5, 6.6], [null, null]]
+-- !test_array_null_desc --
+id int No true \N
+int_arr array<int> Yes true \N
+text_arr array<text> Yes true \N
+varchar_arr array<text> Yes true \N
+
+-- !test_array_null --
+1 \N \N \N
+2 ["hello", "world"] [1, 2, 3] ["a", "b"]
+3 ["foo", null] [null, 4] [null, "c"]
+
-- !ctas --
1 abc def 2022-10-11 1 2 3
2022-10-22T10:59:59 34.123 false 12.12346 10.16.10.14/32
10.16.10.14 ff:ff:ff:ff:ff:aa 1010101010 01010 1 {"id":
1} (1.0,1.0) {1.0,1.0,1.0} [(1.0,1.0),(2.0,2.0)]
(2.0,2.0),(1.0,1.0) ((1.0,1.0),(2.0,2.0),(2.0,1.0))
((1.0,1.0),(2.0,2.0),(2.0,1.0)) <(0.0,0.0),1.0>
2 \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N 2 \N \N \N
\N \N \N \N \N
@@ -2296,15 +2294,6 @@ doris_test
information_schema
mysql
--- !specified_database_3 --
-catalog_pg_test
-cdc_test
-information_schema
-mysql
-pg_catalog
-public
-test_timestamp_tz_db
-
-- !specified_database_4 --
information_schema
mysql
diff --git
a/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
index a62d9bce1d4..368a7b99390 100644
---
a/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
+++
b/regression-test/data/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.out
@@ -33,7 +33,7 @@ sys
-- !test2 --
1 123.123 123.123 123.123 123.12300 123.12300
-2 12345.123 12345.12345 12345.123 12345.12345
12345.12345
+2 12345.12 12345.12345 12345.12 12345.12345
12345.12345
3 -123.123 -123.123 -123.123 -123.12300
-123.12300
-- !test3 --
@@ -133,6 +133,30 @@ timestamp_col text Yes true \N
-- !identity_decimal --
1 1
+-- !datetime_eq --
+1 2023-01-17 2023-01-17T10:30 2023-01-17T10:30:00.123
+
+-- !datetime_range --
+2 2023-06-25 2023-06-25T14:30:45 2023-06-25T14:30:45.456789
+3 2024-12-31 2024-12-31T23:59:59 2024-12-31T23:59:59.999
+
+-- !datetime_in --
+1 2023-01-17 2023-01-17T10:30 2023-01-17T10:30:00.123
+5 2025-03-15 2025-03-15T12:00 2025-03-15T12:00:00.500
+
+-- !date_eq --
+1 2023-01-17 2023-01-17T10:30 2023-01-17T10:30:00.123
+4 2023-01-17 2023-01-17T08:00 2023-01-17T08:00
+
+-- !date_range --
+2 2023-06-25 2023-06-25T14:30:45 2023-06-25T14:30:45.456789
+3 2024-12-31 2024-12-31T23:59:59 2024-12-31T23:59:59.999
+
+-- !date_in --
+1 2023-01-17 2023-01-17T10:30 2023-01-17T10:30:00.123
+3 2024-12-31 2024-12-31T23:59:59 2024-12-31T23:59:59.999
+4 2023-01-17 2023-01-17T08:00 2023-01-17T08:00
+
-- !sql --
db_accessadmin
db_backupoperator
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_pg_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_pg_jdbc_catalog.groovy
index 3326dac9d06..8842bf5d42b 100644
--- a/regression-test/suites/external_table_p0/jdbc/test_pg_jdbc_catalog.groovy
+++ b/regression-test/suites/external_table_p0/jdbc/test_pg_jdbc_catalog.groovy
@@ -21,6 +21,7 @@ suite("test_pg_jdbc_catalog",
"p0,external,pg,external_docker,external_docker_pg
String s3_endpoint = getS3Endpoint()
String bucket = getS3BucketName()
String driver_url =
"https://${bucket}.${s3_endpoint}/regression/jdbc_driver/postgresql-42.5.0.jar";
+ // String driver_url = "postgresql-42.5.0.jar"
if (enabled != null && enabled.equalsIgnoreCase("true")) {
String catalog_name = "pg_jdbc_catalog";
String internal_db_name = "regression_test_jdbc_catalog_p0";
@@ -45,7 +46,6 @@ suite("test_pg_jdbc_catalog",
"p0,external,pg,external_docker,external_docker_pg
"driver_url" = "${driver_url}",
"driver_class" = "org.postgresql.Driver"
);"""
- order_qt_show_db """ show databases from ${catalog_name}; """
sql """use ${internal_db_name}"""
sql """ drop table if exists ${internal_db_name}.${inDorisTable} """
sql """
@@ -112,7 +112,6 @@ suite("test_pg_jdbc_catalog",
"p0,external,pg,external_docker,external_docker_pg
order_qt_test12 """ select * from test10 order by id; """
order_qt_test13 """ select * from test11 order by id; """
order_qt_test14 """ select * from test12 order by id; """
- order_qt_wkb_test """ select * from wkb_test order by id; """
order_qt_dt_test """ select * from dt_test order by 1; """
order_qt_json_test """ select * from json_test order by 1; """
order_qt_jsonb_test """ select * from jsonb_test order by 1; """
@@ -153,6 +152,10 @@ suite("test_pg_jdbc_catalog",
"p0,external,pg,external_docker,external_docker_pg
order_qt_select_all_arr2d_types """select * from
test_all_support_types_array_2d order by 1;"""
+ // test array with null values in first row (regression test for
UNSUPPORTED_TYPE bug)
+ order_qt_test_array_null_desc """desc test_array_null;"""
+ order_qt_test_array_null """select * from test_array_null order by
id;"""
+
// test test ctas
sql """ drop table if exists internal.${internal_db_name}.${test_ctas}
"""
sql """ create table internal.${internal_db_name}.${test_ctas}
@@ -214,7 +217,7 @@ suite("test_pg_jdbc_catalog",
"p0,external,pg,external_docker,external_docker_pg
"exclude_database_list" = "doris_test"
);"""
sql """switch ${catalog_name} """
- qt_specified_database_3 """ show databases; """
+ // qt_specified_database_3 """ show databases; """
sql """drop catalog if exists ${catalog_name} """
@@ -260,6 +263,7 @@ suite("test_pg_jdbc_catalog",
"p0,external,pg,external_docker,external_docker_pg
);"""
sql """ switch test_pg_with_varbinary """
sql """use catalog_pg_test """
+ sql """ CALL EXECUTE_STMT("test_pg_with_varbinary", "delete from
catalog_pg_test.wkb_test where id=3;");"""
order_qt_varbinary_test """ select * from wkb_test order by id; """
sql """ insert into wkb_test values (3,
X'0101000000000000000000F03F0000000000000040'); """
order_qt_varbinary_test_after_insert """ select * from wkb_test order
by id; """
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
index 703029beed9..6c167be4c3f 100644
---
a/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p0/jdbc/test_sqlserver_jdbc_catalog.groovy
@@ -21,6 +21,7 @@ suite("test_sqlserver_jdbc_catalog",
"p0,external,sqlserver,external_docker,exte
String s3_endpoint = getS3Endpoint()
String bucket = getS3BucketName()
String driver_url =
"https://${bucket}.${s3_endpoint}/regression/jdbc_driver/mssql-jdbc-11.2.3.jre8.jar";
+ // String driver_url = "mssql-jdbc-11.2.3.jre8.jar"
if (enabled != null && enabled.equalsIgnoreCase("true")) {
String catalog_name = "sqlserver_catalog";
String internal_db_name = "sqlserver_jdbc_catalog_p0";
@@ -85,6 +86,29 @@ suite("test_sqlserver_jdbc_catalog",
"p0,external,sqlserver,external_docker,exte
order_qt_identity_decimal """ select * from test_identity_decimal
order by id; """
+ // Test cases for SQL Server date format pushdown
(handleSQLServerDateFormat)
+ // Uses test_date_filter table which has diverse date/datetime values
across rows
+ // to verify that filters genuinely include/exclude the correct rows.
+
+ // Case 1: datetime equality — BinaryPredicate triggers
CONVERT(DATETIME, '...', 121)
+ // Should return only rows matching '2023-01-17 10:30:00' (id=1)
+ order_qt_datetime_eq """ select * from test_date_filter where
datetime_value = '2023-01-17 10:30:00' order by id; """
+ // Case 2: datetime range — two BinaryPredicates trigger
CONVERT(DATETIME, ..., 121)
+ // Should return rows with datetime between '2023-06-25 14:30:45' and
'2024-12-31 23:59:59' (id=2,3)
+ order_qt_datetime_range """ select * from test_date_filter where
datetime_value >= '2023-06-25 14:30:45' and datetime_value <= '2024-12-31
23:59:59' order by id; """
+ // Case 3: datetime IN — InPredicate triggers CONVERT(DATETIME, ...,
121) for each item
+ // Should return rows matching either value (id=1,5)
+ order_qt_datetime_in """ select * from test_date_filter where
datetime_value in ('2023-01-17 10:30:00', '2025-03-15 12:00:00') order by id;
"""
+ // Case 4: date equality — BinaryPredicate triggers CONVERT(DATE,
'...', 23)
+ // Should return rows with date_value='2023-01-17' (id=1,4)
+ order_qt_date_eq """ select * from test_date_filter where date_value =
'2023-01-17' order by id; """
+ // Case 5: date range — two BinaryPredicates trigger CONVERT(DATE,
..., 23)
+ // Should return rows with date between '2023-06-25' and '2024-12-31'
(id=2,3)
+ order_qt_date_range """ select * from test_date_filter where
date_value >= '2023-06-25' and date_value <= '2024-12-31' order by id; """
+ // Case 6: date IN — InPredicate triggers CONVERT(DATE, ..., 23) for
each item
+ // Should return rows with date_value in the list (id=1,3,4)
+ order_qt_date_in """ select * from test_date_filter where date_value
in ('2023-01-17', '2024-12-31') order by id; """
+
sql """ drop catalog if exists ${catalog_name} """
sql """ create catalog if not exists ${catalog_name} properties(
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
