Skip to content

Commit 41581e5

Browse files
authored
[fix](streaming-job) drop neighbour-table rows leaked by JDBC LIKE wildcards in JdbcPostgreSQLClient (apache#63402)
### What problem does this PR solve? `JdbcPostgreSQLClient.getJdbcColumnsInfo` calls `DatabaseMetaData.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern)`. Per the JDBC spec the 3rd argument is a **SQL LIKE pattern**, so literal `_` / `%` characters in the requested table name are interpreted as wildcards by the Postgres driver. When a streaming job is created with `include_tables = "user_info_pg_normal1"` and a neighbour table like `userXinfo_pg_normal1` happens to coexist in the same schema, the metadata query returns columns from **both** tables. The combined result then trips `CREATE TABLE` on the Doris side with errors such as `errCode = 2, detailMessage = Duplicate column name 'name'`, or pollutes the auto-created table schema with stray columns. The repro is trivial: in the same Postgres schema create - `user_info_pg_normal1(name varchar, age int2)` — the table we want to capture - `userXinfo_pg_normal1(name varchar, weight float8)` — a decoy whose name only differs from the target by a single character that `_` matches then run `CREATE JOB ... include_tables = "user_info_pg_normal1"`. Without the fix the schema fetched for the target leaks `weight` (or `Duplicate column name 'name'`, depending on column order). Fix: after fetching the `ResultSet`, drop rows whose `TABLE_NAME` does not exactly equal the requested `remoteTableName`. We deliberately do **not** escape `_` / `%` at the source — relying on `DatabaseMetaData.getSearchStringEscape()` is driver-version dependent (older Oracle drivers don't honour escape sequences in `getTables`), while filtering on the consumer side is deterministic and driver-agnostic. Scope: - Only `JdbcPostgreSQLClient` is patched. This is the path used by Postgres streaming jobs (the failing case). MySQL streaming jobs were checked against the same decoy pattern and do not reproduce the bug because MySQL Connector/J doesn't pull neighbour rows here in practice — so `JdbcMySQLClient` is left untouched in this PR. - The JDBC catalog path lives in a separate module (`fe-connector-jdbc/.../JdbcConnectorClient`) and is **not** part of this PR. It already does partial escape but intentionally skips `_` / `%` for driver-compatibility reasons; a follow-up can apply the same after-the-fact filter there.
1 parent db27399 commit 41581e5

2 files changed

Lines changed: 32 additions & 0 deletions

File tree

‎fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcPostgreSQLClient.java‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -63,6 +63,11 @@ public List<JdbcFieldSchema> getJdbcColumnsInfo(String remoteDbName, String remo
6363
String catalogName = getCatalogName(conn);
6464
rs = getRemoteColumns(databaseMetaData, catalogName, remoteDbName, remoteTableName);
6565
while (rs.next()) {
66+
// getColumns treats schema/table as LIKE patterns; drop rows pulled in via `_`/`%`.
67+
if (!remoteDbName.equals(rs.getString("TABLE_SCHEM"))
68+
|| !remoteTableName.equals(rs.getString("TABLE_NAME"))) {
69+
continue;
70+
}
6671
int dataType = rs.getInt("DATA_TYPE");
6772
int arrayDimensions = 0;
6873
if (dataType == Types.ARRAY) {

‎regression-test/suites/job_p0/streaming_job/cdc/test_streaming_postgres_job.groovy‎

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -75,6 +75,29 @@ suite("test_streaming_postgres_job", "p0,external,pg,external_docker,external_do
7575
sql """INSERT INTO ${pgDB}.${pgSchema}.${table2} (name, age) VALUES ('A2', 1);"""
7676
sql """INSERT INTO ${pgDB}.${pgSchema}.${table2} (name, age) VALUES ('B2', 2);"""
7777

78+
// Decoy table whose name differs from table1 by a single char that JDBC LIKE
79+
// matches via `_`. Different column shape (weight) so any schema leak surfaces
80+
// as a Duplicate-column error on CREATE TABLE or a stray `weight` column.
81+
sql """DROP TABLE IF EXISTS ${pgDB}.${pgSchema}.userXinfo_pg_normal1"""
82+
sql """CREATE TABLE ${pgDB}.${pgSchema}.userXinfo_pg_normal1 (
83+
"name" varchar(200),
84+
"weight" float8,
85+
PRIMARY KEY ("name")
86+
)"""
87+
sql """INSERT INTO ${pgDB}.${pgSchema}.userXinfo_pg_normal1 (name, weight) VALUES ('decoy1', 1.5);"""
88+
89+
// Decoy schema whose name differs from pgSchema (cdc_test) by a single char that
90+
// JDBC LIKE matches via `_`. A same-named table with a different column shape
91+
// (height) lives inside, so any schema-level LIKE leak surfaces as a stray
92+
// `height` column or a Duplicate-column error.
93+
sql """DROP SCHEMA IF EXISTS cdcXtest CASCADE"""
94+
sql """CREATE SCHEMA cdcXtest"""
95+
sql """CREATE TABLE ${pgDB}.cdcXtest.${table1} (
96+
"name" varchar(200),
97+
"height" float8,
98+
PRIMARY KEY ("name")
99+
)"""
100+
sql """INSERT INTO ${pgDB}.cdcXtest.${table1} (name, height) VALUES ('schema_decoy', 9.9);"""
78101
}
79102

80103
sql """CREATE JOB ${jobName}
@@ -108,6 +131,10 @@ suite("test_streaming_postgres_job", "p0,external,pg,external_docker,external_do
108131
assert createTalInfo.contains("`age` smallint");
109132
assert createTalInfo.contains("UNIQUE KEY(`name`)");
110133
assert createTalInfo.contains("DISTRIBUTED BY HASH(`name`) BUCKETS AUTO");
134+
// Guard: decoy table userXinfo_pg_normal1 must not leak its `weight` column.
135+
assert !createTalInfo.contains("`weight`");
136+
// Guard: decoy schema cdcXtest must not leak its `height` column either.
137+
assert !createTalInfo.contains("`height`");
111138

112139
// check job running
113140
try {

0 commit comments

Comments
 (0)