fskorgen opened a new issue, #8226:
URL: https://github.com/apache/hop/issues/8226
### Apache Hop version?
2.19
### Java version?
21
### Operating system
Windows
### What happened?
2.19 taught `getQuotedSchemaTableCombination` to handle a schema name that
carries its catalog, so
that a three-part `catalog.schema.table` can be built by passing
`"catalog.schema"` as the schema:
```java
private String quoteSchema(String schemaName) {
if (supportsCatalogs()) {
int separatorIndex = schemaName.indexOf('.');
if (separatorIndex > 0 && separatorIndex < schemaName.length() - 1) {
String catalogName = schemaName.substring(0, separatorIndex);
String schemaPart = schemaName.substring(separatorIndex + 1);
return quoteField(catalogName) + "." + quoteField(schemaPart);
}
}
return quoteField(schemaName);
}
```
The split is gated on `supportsCatalogs()`. When that returns false the
whole string falls through to
`quoteField`, which quotes anything containing a dot as a **single**
identifier:
```java
if (iDatabase.isQuoteAllFields()
|| hasSpacesInField(field)
|| hasSpecialCharInField(field)
|| hasDotInField(field)) {
return getStartQuote() + field + getEndQuote();
}
```
So `("m3_staging_tst.dbo", "m3_tables_active")` yields:
```sql
[m3_staging_tst.dbo].[m3_tables_active]
```
— one bracketed identifier with a dot inside it, which no SQL Server
instance resolves. The
`hasDotInField` branch turns what would have been a merely wrong name into a
syntactically valid
reference to an object that cannot exist.
### The two SQL Server drivers disagree
The gate makes this driver-dependent for the same database engine:
| Driver | `isSupportsCatalogs()` | Composite schema |
|---|---|---|
| `MsSqlServerNativeDatabaseMeta:207` | `true` | split correctly |
| `MsSqlServerDatabaseMeta:90` | `false` | collapsed into one identifier |
Both target SQL Server, and `MsSqlServerNativeDatabaseMeta extends
MsSqlServerDatabaseMeta`. SQL
Server plainly has catalogs — `USE <database>`, three-part names,
`sys.databases` — so `false` on the
non-native driver looks like a statement about something else (perhaps that
catalogs are not usable
as a *browsing* dimension there), yet it is what decides whether a caller's
composite schema is
honoured.
The practical effect is that a caller cannot rely on the 2.19 feature at
all: the same code, against
the same server, produces a valid three-part name or a broken one depending
on which of the two
SQL Server drivers the connection happens to use.
### Steps to reproduce
1. Create two connections to the same SQL Server instance, one using **MS
SQL Server** and one using
**MS SQL Server (Native)**.
2. For each, call
`databaseMeta.getQuotedSchemaTableCombination(variables,
"<somedatabase>.dbo", "<sometable>")`.
**Expected:** `[somedatabase].[dbo].[sometable]` from both — a composite
schema was passed
deliberately, and 2.19 added the support for exactly that.
**Actual:** the native driver returns `[somedatabase].[dbo].[sometable]`;
the other returns
`[somedatabase.dbo].[sometable]`, which fails to resolve.
### Suggested fix
Split whenever the caller actually passed a composite name, rather than
asking the dialect for
permission:
```java
private String quoteSchema(String schemaName) {
int separatorIndex = schemaName.indexOf('.');
if (separatorIndex > 0 && separatorIndex < schemaName.length() - 1) {
String catalogName = schemaName.substring(0, separatorIndex);
String schemaPart = schemaName.substring(separatorIndex + 1);
return quoteField(catalogName) + "." + quoteField(schemaPart);
}
return quoteField(schemaName);
}
```
A dot in a schema name is not otherwise meaningful — the alternative
reading, a single schema whose
name contains a literal dot, is exactly what the current code produces and
is vanishingly rare next
to the `catalog.schema` case the feature was built for.
If the gate is deliberate, then `isSupportsCatalogs()` returning `false` for
`MsSqlServerDatabaseMeta` is worth revisiting on its own: whatever it was
meant to express, it now
also silently disables a quoting feature, and the two SQL Server drivers
give different answers for
the same server.
### Issue Priority
Priority: 2
### Issue Component
Component: Database
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
